Microsoft Office Tutorials and References
In Depth Information
Importing Data from SQL Server
Managing external data properties
When you import external data into a table, you can control a few adjustable properties via the Properties
dialog box. You can get to the properties of a particular external data table by clicking the target table
and selecting the Properties icon under the Data tab.
This activates the External Data Properties dialog box. The properties found in this dialog box allow you to
further customize your query tables to suit your needs. Take a moment to familiarize yourself with some
of the useful options in this dialog box.
Include Row Numbers: This property is deselected by default. Selecting this property creates
a dummy column that contains row numbers. The first column of your dataset will be this row
number column upon refresh.
Adjust Column Width: This property is selected by default, telling Excel to adjust the column
widths each time the data is refreshed. Deselecting this option will cause the column widths
to remain the same.
Preserve Column/Sort/Filter/Layout: If this property is selected, the order of the columns
and rows of the Excel range remains unchanged. This way, you can rearrange and sort the
columns and rows of the external data in your worksheet without worrying about blowing away
your formatting each time you refresh. Deselecting this property will make the Excel range
look like the query.
Preserve Cell Formatting: This property is selected by default, telling Excel to keep the
applied cell formatting when you refresh.
Insert Cells for New Data, Delete Unused Cells: This is the default setting for data range
changes. When data rows decrease, you may have errors in adjacent cells that reference your
external range. The cells these formulas referenced are deleted, so you will get an #VALUE
error in your formula cells.
Insert Entire Rows for New Data, Clear Unused Cells: When the unused cells are cleared
instead of deleted, the formula may no longer return an error. Instead, it continues to
reference cells from the original range — even though some of them are blank now. This could still
give you erroneous results.
Overwrite Cells for New Data, Clear Unused Cells: The third option should be the same as
option two when rows decrease as unused cells are cleared.
Importing Data from SQL Server
In the spirit of collaboration, Excel 2013 vastly improves your ability to connect to transactional
databases such as SQL Server. With the connection functionality found in Excel, creating a connected
table or pivot table from SQL Server data is as easy as ever.
Search JabSto ::




Custom Search