A Note of Caution When Using FilterDatabase

A handy Excel shortcut comes with a hidden pitfall.

One of my favorite shortcuts when using a single-sheet, filter-enabled XLSX file as a data source is to use the FilterDatabase "sheet" instead of the actual sheet on which the data resides. This isn't really a sheet, of course, but a hidden database that Excel automatically creates whenever a workbook has a sheet with drop-down filters enabled. I like using this DB as a source because it lets me be lazy and not have to worry about changing sheet names. A worksheet may get renamed, but FilterDatabase will always be called FilterDatabase. You also don't have to worry about "garbage" header rows that don't contain meaningful data, as those won't exist in FilterDatabase.
Recently, however, I stumbled on an "undocumented feature" inherent with FilterDatabase. At one of my clients, I came across an anomaly that had me scratching my head for a while. This is what I saw when looking at the worksheet itself as a source:

But when I switched to FilterDatabase, the last row was missing:

What I eventually discovered was that if you do use FilterDatabase, make sure that creating column filters is the very last thing you do before you save the workbook. That's because FilterDatabase gets created at the time that you press the "Filter" button in Excel; any values you enter into the worksheet afterwards will not be included in FilterDatabase. Of course, we are often not the owners of the spreadsheets that our applications consume, so would not (or should not) save them at all. The moral of the story is that unless you're positive that the spreadsheet owner did not enter any values after enabling filters, you're probably better off avoiding FilterDatabase altogether.
This entry was posted in Development, Tips & Tricks and tagged , . Bookmark the permalink.

2 Responses to A Note of Caution When Using FilterDatabase

  1. Michael Henrick says:

    Interesting stuff. I wonder if there is any way to reference a table created in Excel. A lot of the Power Pivot and Power BI all work off these tables with DAX etc. Thanks for the tip.

  2. Mel Pohl says:

    Or turn them off and turn them back on again then re-save, then run your merge (or whatever).

Leave a Reply

Your email address will not be published. Required fields are marked *

Notify via email when new comments are added

Blog Home