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”.
Example
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.
Instructions
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)
Check!
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.
Conclusion
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.
