How to refresh template.dotx?

How to refresh data in a template

• I use a reporting system that outputs data to two spreadsheets - one is the report parameters, the other the report data (as a list). For one of these reports, I created four pivot tables (each on their own tab) and saved the workbook as a template (.xlt). When saving, I selected the option to refresh data every time the template is opened. My understanding is that when my report has been created I can right click on one of the tabs and 'Insert...', then choose my template. The two tabs that are in the report, I thought, would then replace the data already in the template (they have the same names) but Excel is just adding the tabs from the template with the suffix '(1)' and keeping the dummy data that was saved in the template. I'm guessing that I am doing something wrong, but can anyone explain what please?

• Answer:

  I’ve never found a need for templates (until now!) – or at least have always managed without, so beware. However, some generalities may help. Firstly, pivot tables do not normally update automatically (though see below), they need to be selected and refreshed. Secondly, when creating multiple pivot tables I believe it is recommended these are created as copies to save space (otherwise file size may increase by roughly that for the base data alone each time). Thirdly, when using Excel 2007 it is probably better in most cases to save as the .aaax version. Fourthly, it has been recommended when linking across workbooks all are open at the time. Where say a file of consistent format but frequently changing content requires to be pivoted there are at least two ways to avoid the need to recreate the pivot tables at each change. With a template seems one option but I manage without as below: Ensure all previous versions, if to be kept, have some version indication (say Data1, Data2, Tables1, Tables2 etc) but, to begin with, that the latest is ‘generic’ (say Data.xlsx). Create the pivot tables from Data.xlsx, in a new workbook (say Tables.xlsx). As new data is available, open the file and rename as ‘generic’ if necessary, then save to the same folder as Tables.xlsx. Open Data.xlsx, then Tables.xlsx (when update should be automatic) then save both with version indication. I think that should work but if not, check the sheet names are appropriate and that the pivot tables are using the appropriate source data (Select pivot table, PivotTable Tools, Options, Data, Change Data Source, Change Data Source, Select a table or range, Table/Range:) - or ask again/wait for a better answer!