Create your new pivot table based on a range within Sheet2, then hide Sheet2.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.Create Sheet2 and put =Sheet1!A1 into Sheet2!A1.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: For the Power Pivot in Excel 2016, you may try the following steps to enable it: Open the Excel 2016>FILE>Options>Add-ins>In the Manage section, select COM Add-ins>Go>Check Microsoft Power Pivot for Excel>OK>Find the Power Pivot tab in the top ribbon. 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. 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.

Power pivot in excel 2016