A PivotTable has several layouts that provide a predefined structure to the report, but you cannot customize these layouts. If you need more flexibility in designing the layout of a PivotTable report, you can convert the cells to worksheet formulas, and then change the layout of these cells by taking full advantage of all of the features available in a worksheet. You can either convert the cells to formulas that use Cube functions or use the GETPIVOTDATA function. Converting cells to formulas greatly simplifies the process of creating, updating, and maintaining these customized PivotTables.
When you convert cells to formulas, these formulas access the same data as the PivotTable and can be refreshed to see up-to-date results. However, with the possible exception of report filters, you no longer have access to the interactive features of a PivotTable, such as filtering, sorting, or expanding and collapsing levels.
Note: When you convert an Online Analytical Processing (OLAP) PivotTable, you can continue to refresh the data to get up-to-date measure values, but you cannot update the actual members that are displayed in the report.
Learn about common scenarios for converting PivotTables to worksheet formulas
The following are typical examples of what you can do after you convert PivotTable cells to worksheet formulas to customize the layout of the converted cells.
Rearrange and delete cells
Let's say that you have a periodic report that you need to create each month for your staff. You only need a subset of the report information and you prefer to lay out the data in a customized way. You can just move and arrange cells in a design layout that you want, delete the cells that are not necessary for the monthly staff report, and then format the cells and worksheet to suit your preference.
Insert rows and columns
Let's say that you want to show sales information for the previous two years broken down by region and product group, and that you want to insert extended commentary in additional rows. Just insert a row and enter the text. In addition, you want to add a column that shows sales by region and product group that is not in the original PivotTable. Just insert a column, add a formula to get the results that you want, and then fill the column down to get the results for each row.
Use multiple data sources
Let's say that you want to compare results between a production and test database to ensure that the test database is producing expected results. You can easily copy cell formulas and then change the connection argument to point to the test database to compare these two results.
Use cell references to vary user input
Let's say that you want the entire report to change based on user input. You could change arguments to the Cube formulas to cell references on the worksheet, and then enter different values in those cells to derive different results.
Create a nonuniform row or column layout (also called asymmetric reporting)
Let's say that you need to create a report that contains a 2008 column called Actual Sales, a 2009 column called Projected Sales, but you don't want any other columns. You can create a report that contains just those columns, unlike a PivotTable, which requires symmetric reporting.
Create your own Cube formulas and MDX expressions
Let's say that you want to create a report that shows sales for a particular product by three specific salespeople for the month of July. If you are knowledgeable about MDX expressions and OLAP queries, you can enter the Cube formulas yourself. Although these formulas can become quite elaborate, you can simplify the creation and improve the accuracy of these formulas by using Formula AutoComplete. For more information, see Use Formula AutoComplete.
Note: You can only convert an Online Analytical Processing (OLAP) PivotTable by using this procedure.
-
To save the PivotTable for future use, we recommend that you make a copy of the workbook before you convert the PivotTable by clicking File > Save As. For more information, see Save a file.
-
Prepare the PivotTable so that you can minimize the rearrangement of the cells after conversion by doing the following:
-
Change to a layout that most closely resembles the layout that you want.
-
Interact with the report, such as filtering, sorting, and redesigning the report, to get the results that you want.
-
-
Click the PivotTable.
-
On the Options tab, in the Tools group, click OLAP tools, and then click Convert to Formulas.
If there are no report filters, then the conversion operation completes. If there are one or more report filters, then the Convert to Formulas dialog box is displayed.
-
Decide how you want to convert the PivotTable:
Convert the entire PivotTable
-
Select the Convert Report Filters check box.
This converts all cells to worksheet formulas and deletes the entire PivotTable.
Convert only the PivotTable row labels, column labels, and values area, but keep the Report Filters
-
Make sure that the Convert Report Filters check box is clear. (This is the default.)
This converts all row label, column label, and values area cells to worksheet formulas, and keeps the original PivotTable, but with only the report filters so that you can continue to filter by using the report filters.
Note: If the PivotTable format is version 2000-2003 or earlier, you can only convert the entire PivotTable.
-
-
Click Convert.
The conversion operation first refreshes the PivotTable to ensure that up-to-date data is used.
A message is displayed in the status bar while the conversion operation takes place. If the operation takes a long time and you prefer to convert at another time, press ESC to cancel the operation.
Notes:
-
You cannot convert cells with filters applied to levels that are hidden.
-
You cannot convert cells in which fields have a custom calculation that were created through the Show Values As tab of the Values Field Settings dialog box. (On the Options tab, in the Active Field group, click Active Field, and then click Values Field Settings.)
-
For cells that are converted, cell formatting is preserved, but PivotTable styles are removed because these styles can apply to PivotTables only.
-
You can use the GETPIVOTDATA function in a formula to convert PivotTable cells to worksheet formulas when you want to work with non-OLAP data sources, when you prefer not to upgrade to the new PivotTable version 2007 format right away, or when you want to avoid the complexity of using the Cube functions.
-
Make sure that the Generate GETPIVOTDATA command in the PivotTable group on the Options tab is turned on.
Note: The Generate GETPIVOTDATA command sets or clears the Use GETPIVOTTABLE functions for PivotTable references option in Formulas category of the Working with Formulas section in the Excel Options dialog box.
-
In the PivotTable, make sure the cell that you want to use in each formula is visible.
-
In a worksheet cell outside the PivotTable, type the formula that you want up to the point where you want to include data from the report.
-
Click the cell in the PivotTable that you want to use in your formula in the PivotTable. A GETPIVOTDATA worksheet function is added to your formula that retrieves the data from the PivotTable. This function continues to retrieve the correct data if the report layout changes or if you refresh the data.
-
Finish typing your formula and press ENTER.
Note: If you remove any of the cells referenced in the GETPIVOTDATA formula from the report, the formula returns #REF!.
