Calling Views and Stored Procedures from Excel - Microsoft Business Intelligence Tools for Excel Analysts - page 215

Managing external data properties

When you import external data into a table, you can control a few adjustable properties, which you can
find in the Properties dialog box. Click the target table and then click the Properties button on the Data
tab to open the External Data Properties dialog box.

Take a moment to familiarize yourself with some 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 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 for-
matting each time you refresh. Deselecting this property will make the Excel range look like
the query.

●

Preserve Cell Formatting: This is selected by default, telling Excel to keep the applied cell for-
matting 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 a #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 refer-
ence 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.

●

Passing Your Own SQL Statements
to External Databases
If you're a seasoned analyst who is proficient at writing your own SQL queries, you can use the con-
nection properties to write your own SQL statements. This gives you more control over the data you
pull into your Excel model, and allows you to perform advanced actions like running SQL Server
stored procedures.