Cathi in Maryland points out one of the effects of a disturbing trend. The trend is that Microsoft is trying to jam online help down our throats. This particular manifestation is the lack of Whats This? (that little question mark at the top of dialog boxes) help on the Data Range Properties dialog.
To be fair, this could just be an oversight on the part of the Office Help developers. It seems strange, though, that features would disappear.
Here’s the information you’ll be missing:
Name: The named range created with the table. When you create an external data range, Excel automatically creates a named range that covers the table. You can type a new name here which changes the named range. The dialog shows spaces in the name, but the defined name box shows underscores in place of them. If you type spaces in the new name, they will be underscores in the range name.
Save Query Definition: The external data table is connected to the underlying data source. That allows you to refresh the data when the source changes. If you uncheck this box, you break the link between the Excel range and the source, meaning you can’t refresh the data anymore. The data doesn’t disappear and the named range stays too. When you uncheck the box, you get a warning that the link is permanently deleted. You can still close the workbook without saving changes, though.
Save Password: If the underlying data requires a password, you can avoid having to type it in every time by checking this box. If you want users to have to type in a password, uncheck this box. Data sources that don’t require a password are unaffected by this.
Enable Background Refresh: Refreshing in the background means that you can do other stuff while the query is refreshing, including continuing executing VBA code. The alternative is that user interface is locked up and VBA code execution is suspended until the refresh is done.
Refresh Every: You can schedule the query to refresh automatically every so many minutes, from 1 to 32, 767 (that’s about every 23 days).
Refresh Data on File Open: She does what she says. If you are so bold as to check this box, you will be pestered with a message about enabling or disabling the automatic refresh when you open the file. Blech.
Remove External Data…Before Saving: You can reduce your file size by removing the data before you save. This clears the data, but retains the query definition so it can be refreshed. Or does it? If you save the workbook without closing the data doesn’t disappear. Nor does it delete the data and refresh it. So actually, it deletes the data on close in the state it was in when you last saved it. At least I think it does. The first time I’ve ever used this feature was about 5 minutes ago.
Include Field Names: If you check this, the first row of your table will be field names. If you don’t, the first row of data will, well, the first row of data.
Include Row Numbers: If you check this, the first column of your table will be sequential numbers, otherwise the first field in your query will the first column.
Adjust Column Width: Checked, this causes the column widths to adjust to the data on refresh. Unchecked, the column widths stay the same.
Preserve Column/Sort/Filter/Layout: If this is checked, then the order of the columns and rows of the Excel range stay the same no matter what you do to the query. For instance, if you add a field to the middle of the underlying query, it will appear at the end. If you want the Excel range to look like the query, uncheck this box.
Preserve Cell Formatting: If you apply formatting to a cell, it will stay formatted. No, not necessarily the data that you formatted, just the cell. That is, if you format cell C5 and the data changes such that a row above is removed, C5 remains formatting. You might expect the formatting to move to C4, but you’d be wrong.
If the number of rows…: See Data Range Properties Options
Fill Down Formulas…: If you put formulas in the column adjacent to the table, the will expand and retract as the range does. You aren’t limited to one column, you get as many as you like (I think) as long as they’re adjacent.