Pivot Tables are my favorite Excel tool. Their flexibility and ease make them essential in today’s data-driven economy.
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.
Why? Check your data source properties by clicking inside your Pivot Table, then going to Analyze and 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.
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.
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.