In this short tutorial, you’ll learn how to save a Pivot Table and Chart as a template for later use and how to update this template with new information using the “Refresh” feature within the PivotTable menu.
- If for example, you have created a great PivotTable and Chart that truly reflects the data relationships that you want topresent to management and you will like use the same template but with completely different raw data. You can create a template out of the current PivotTable and Chart and in essence create a shell that can absorb the new data but present it in the same way as the current PivotTable and chart. So first, open the file with the current PivotTable and Chart and go to File>Save As. Next, in “Save as type” go down to Template (*.xlt) and select it. Give it another name, for example, it would be good to include the term “template” in the actual name to help you find it faster in the future.
- Next, open the workbook containing the new raw data. Select the range with the new data and copy all the data. Then, open the template file, go to the spreadsheet where the original raw data is stored and paste the new data in exactly the same range location. Then click on the PivotTable spreadsheet and click on the “Refresh Data” button and all the data will be refreshed. The row and column headings will remain the same as these were constant and the only thing that will change is the raw data. The PivotTable chart will also be automatically refreshed with all the new data. Finally, you can save this file under a different name.
Great! Now you can appreciate the true value of PivotTables and not fear them any longer.