You can use a Pivot Table as a database in the same way that you can use Simple Tables or Excel Tables.
However, I’ve never seen a description of how to do it. That’s too bad, because Excel 2010 gave Excel users the ability to use one or more Pivot Tables as a massive and powerful spreadsheet database.
If you don’t set up your Pivot Table as a database, you typically must use the GETPIVOTDATA function to return data from it. That limits your power, because GETPIVOTDATA is a “screen-scraper” function. That is, it only can return the numbers and text you see on your screen.
On the other hand, if you do set up your Pivot Table as a database, you can use Excel’s more powerful functions with it, functions like SUMIFS, SUMPRODUCT, INDEX, MATCH, and so on. In future posts, I’ll show you how these functions can give you significantly more power to return results from your spreadsheet database.
Using Pivot Tables as a database offers many general advantages. But there’s one specific advantage that’s worth considering…
The PowerPivot Advantage
Microsoft’s introduction of PowerPivot offers a new and significant benefit to using Pivot Tables as a spreadsheet database. [click to continue…]