Pivot Tables aren’t merely a way to interact with your data. You also can use them as a rich source of data for regular reports and analyses…without using the weak function, GETPIVOTDATA.
 |
by Charley Kyd, MBA Microsoft Excel MVP |
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, not-very-powerful function 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.
Read Full Article: How to Set Up a Pivot Table as a Spreadsheet Database
Source: ExcelUser