When you’re working with External Data, Excel provides three options that deal with data outside the External Data Range. These options determine how the range is updated when the data changes and can affect how other data in your spreadsheet is handled. The options are on the External Data Range Properties dialog box, the pertinent section is shown below:
To demonstrate how these options work, I’ve set up a table in Access with three records. This the whole table after creating a new query in Excel:
I limit the table to one row by using a parameter and I add some extra data around the table so we can see what happens.
Insert cells for new data, delete unused cells
This option will insert cells (not rows) when the table grows and delete cells (not rows) when it shrinks. When the parameter changes to show all of the data (table grows), the “Extra row data” is unaffected because Excel only inserts cells. Nothing to the left or right of the table will be affected with this option. The “Extra column data” will be pushed down to make room for the inserted cells.
You may notice that the blank row between the external data and the extra data is now gone. When an external data table only has one record, Excel puts an extra row at the bottom. I have no idea why. So the “Extra column data” is still in the next row although it doesn’t look like it.
Insert entire rows for new data, clear unused cells
This option will insert whole rows when the table grows, but only clear the contents of cells that it no longer needs (it doesn’t delete the cells or the rows). That means that both “Extra row data” and “Extra column data” will move down when the table grows.
When the table shrinks, none of the extra data moves. Excel only clears the cells, it doesn’t delete them nor does it delete the rows.
Overwrite existing cells with new data, clear unused cells
With this option, “Extra column data” says bye-bye because it’s in the path of the new data. No, it doesn’t come back when the table shrinks, overwrite means overwrite. Nothing happens to “Extra row data” because nothing happens to the rows, just the cells. When the table shrinks, it acts like the second option.
I think these options are generally ignored (sometimes at great peril), but now that you know they are there and how they work, you can choose the right one for you.