Friday, 4 July 2014

Excel - Refresh the data for a pivot table

Perhaps you download a report and build a pivot table to summarise the data. You might have to do this on a regular basis, every week or month perhaps. That way you can show the most up-to-date figures. Every time you download the new data you could re-create the pivot table. It’s easy to make pivot tables of course so maybe this is fine for you? However, if your pivot table is complex or if you just want to make your life easier, why not just change the data that the pivot table uses? That’s what I’m going to explain about in this article, along with a small issue I came across that you should be wary of.

Typically one sheet (tab) in your workbook is for the data and one is for the pivot table. In the example here I will replace the table of data in the ‘DATA’ sheet with new data I’ve downloaded recently. I will then refresh my pivot table (on the ‘PIVOT’) sheet to reflect the new data.

Format as Table
It is not essential but it does help if your data is formatted as a ‘table’ in Excel. To do this click in the sheet where the data is (don’t select a range, don’t select more than one cell) and click Home | Format as Table. Select a format that you like - see the screen shot below:

Make sure 'My table has headers' is enabled:

Click OK and the data will be formatted with an auto-filter on the top and also the rows and columns will be easier to read.

Tables are great, you can do a lot with them including adding a Total Row (click Design). 

This is useful to Sum or Count column data, for example. As you use the filter the total will change. It’s a quick method or reading data, it can be useful when you just need a figure rather than to create an actual report (then a pivot table would be more appropriate).

Once your data is formatted as a ‘table’ it will have a table name. This name can be used to refer to the table data as a convenient alternative to using a range. It’s especially useful when using pivot tables because the pivot table will work from the table name rather than a range. If you update the table with more data, the pivot table will use all the data in the table, there’s no need to adjust it.

To find out what your table is called click on the table data and in the top left it will show you. Typically the default is “Table1”.

There are two sheets; DATA and PIVOT

The data in the DATA sheet is over one month old. I would like to replace it with new data I’ve just downloaded from my Business Intelligence system as a CSV (Comma Separated Values) file.

Open the new data file (in our example the CSV we just downloaded)

Press Ctrl-A to select all the data
(do not use the corner button, it selects everything, even blank cells outside of the data which means you might get 'Excel not responding' when copy/pasting. If you use Ctrl-A it selects the data only)

Press Ctrl-C – this copies all the data to the clipboard

Change to your existing Excel workbook

Change to the sheet where the (old) data is, in our example DATA

Click once on the table

IMPORTANT!!! On the Table Tools Design tab click to disable Total Rows – this is important, if you don’t do this not all the rows of data will come in.

On the DATA sheet click the corner button – this will select all the old data (Table1)

Press Ctrl-V – this will paste the new data, it will replace all the existing data in the table

Change to the PIVOT sheet (in our example where our pivot table is)

Click on the pivot table

Look at the top where it says ‘Pivot Table Tools’, click Options (Excel 2010) or Analyze (Excel 2013)

Click Refresh

The pivot table will update, taking the new data from the DATA sheet (Table1)

I do recommend that you double check that the new data has come in correctly. Check before pasting how many rows of new data you have. As I mentioned in the above, make sure that your table does not have the Total Row enabled when you paste the new data in. If you do then you may not get all the lines of new data! If you turn off the Total Rows the paste will work.

Why this way?
An alternative might be to delete all the old data and paste in the new data. This works fine but you will need to 'Format as Table' again and ensure the table name is the same as the table name the pivot table is looking for! Or you could use Change Data Source to point the pivot table at the new table of data.

Change Data Source
It might be that you want to keep a copy of the old data. In this case create a new worksheet, paste the new data into it. Format as Table – note what the table name is, typically it’ll be Table2. Go to the pivot table, click Options (Excel 2010) or Analyse (Excel 2013) and click Change Data Source, enter Table2. Now your pivot table is pointing to the new data. Follow the same steps again but enter Table1 to point it back to the original data.

With some care and understanding of how the tables and pivot tables work together, refreshing the data is very quick and easy.

Excel Version?
Excel 2013 and 2010, probably earlier versions too - feel free to write in the comments below about your experiences.

Post a Comment