That will put lots of zeros in cells where the original cells are currently empty, but you could avoid that by using the formula =IF(Sheet1!A1='','',Sheet1!A1) instead. Assuming that the original pivot table could change size whenever it is refreshed, you could copy the formula in Sheet2 to cover the whole of the potential area the original pivot table could ever take.Copy that formula around as many cells in Sheet2 as required to match the size of the original pivot table.In the Recommended PivotTables window, scroll down the list, to see the suggested. Expand and collapse data, and show the underlying details that pertain to the values. In the Tables group, click Recommended PivotTables. After you create a PivotTable by selecting its data source, arranging fields in the PivotTable Field List, and choosing an initial layout, you can perform the following tasks as you work with a PivotTable. Here is a compelete intro, step-by-step tutorial on Excel Pivot Tables - creation, customization, filtering, sorting. Select any cell in the source data table. Pivot tables help you quickly analyze, summarize and visualize your data. First, see which pivot table layouts are suggested by Excel. Fields represent the columns in your data range or Excel table, and will have check. By default, the Task Pane appears at the right side of the window with Fields displayed above Areas. The PivotTable Fields Task Pane comprises of Fields and Areas. Create Sheet2 and put =Sheet1!A1 into Sheet2!A1 After your source data is prepared, you can create a pivot table. PivotTable Fields is a Task Pane associated with a PivotTable.The copy-paste approach isn't very useful when the original pivot table gets refreshed.ДЇor instance, if Sheet1 contains the original pivot table, then: Rather than using copy and then paste values, however, a better way for many purposes is to create some hidden columns or a whole hidden sheet that copies values using simple formulae. I assume that this range is rather static.

Click Next and select the pivot table that you want to use as a source (select starting with the actual headers of the fields). In the list of data source options choose 'Microsoft Excel list of database'. As implies, Excel won't do what you need directly, so you have to copy your data from the pivot table to somewhere else first. In a new sheet (where you want to create a new pivot table) press the key combination (Alt+D+P).

Excel pivot table