Never Leave out Data in your Pivot Table

Pivot Tables are my favorite Excel tool.  

Be sure to subscribe to the newsletter for more Excel tips and tricks.

One of the few problems with Pivot Tables is that making sure your source data is up-to-date and complete.  To check for the former, right-click anywhere in the Pivot Table and select Refresh.  This will upload your Pivot Table with the latest data.  The latter part can be a little trickier, and we will discuss a solution.

We have a list of sales by store which we have turned into a Pivot Table.  There is a new group of sales that we have added to our source data, so we want to see it in the Pivot Table.  We go to refresh our Pivot Table.

Nothing happens.

Why?  Check your data source properties by clicking inside your Pivot Table, then going to Analyze and Change Data Source.

Change data source

Here’s the problem.  When you had originally inserted the Pivot Table, Excel chose only the exact range of the original data set.  This meant that when you added new data, it did not get included.

It's not picking up all the data

How to fix this?  You could just lengthen your source data to include the new sales.  But then you will have to check again if you add more sales.  Let’s make a permanent fix.

When selecting your data source, click right on the column labelled “A.”  Drop and drag to Column C.  Hit ok.

Never leave out data

Now you are picking up all the data that will ever go into these columns.  You will still have to refresh to get new data, but you will never miss any of your source data.

Subscribe to my mailing list.

Comments

  1. The pivot source data should be in a Table, so the source data automatically expands when data is added.

Leave a Reply

%d bloggers like this: