You can build formulas that retrieve data from an Excel pivot table. Say that you want to chart some of the data shown in a pivot table. You can also retrieve an entire pivot table.
To retrieve all the information in a pivot table, follow these steps:
Select the pivot table by clicking a cell within it.
Click the Analyze tab’s Select command and choose Entire PivotTable from the menu that appears.
Excel selects the entire pivot table range.
In Excel 2007 or Excel 2010, click the PivotTable Tools Options tab’s Options command, click Select, and choose Entire Table from the Select submenu that appears.
Copy the pivot table.
You can copy the pivot table the same way that you would copy any other text in Excel. For example, you can click the Home tab’s Copy button or by pressing Ctrl+C. Excel places a copy of your selection onto the Clipboard.
Select a location for the copied data by clicking there.
Paste the pivot table into the new range.
You can paste your pivot table data into the new range in the usual ways: by clicking the Paste button on the Home tab or by pressing Ctrl+V. Note, however, that when you paste a pivot table, you get another pivot table. You don't actually get data from the pivot table.
If you want to get just the data and not the pivot table — in other words, you want a range that includes labels and values, not a pivot table with pivot table buttons — you need to use the Paste Special command. (The Paste Special command is available from the menu that appears when you click the down-arrow button beneath the Paste button.)
When you choose the Paste Special command, Excel displays the Paste Special dialog box. In the Paste section of this dialog box, select the Values radio button to indicate that you want to paste just a range of simple labels and values and not a pivot table itself. When you click OK, Excel pastes only the labels and values from the pivot table and not the actual pivot table.
