Excel Pivot Tables rely on a Data Source to populate the Pivot table. We have established this data source in prior posts when we first make our pivots. The data source can also be changed at any time to include more or fewer rows, and more or fewer columns.
You can find the data source of the pivot table by:
- Click anywhere in the pivot table area. This will open up the Pivot Table tools tabs in your menu
- Click on the “Change Data Source” button
There are two ways you can select your data, Column Selection and Data Selection, both options have pros and cons for why you might use each method.
Selecting an entire column such as
A:F lets your Pivot Table compensate for more or fewer rows in your data. If my data set is 9 rows today and 20 tomorrow, a simple refresh of my pivot will allow my pivot to display that data automatically. Doing this method will also cause a “blank” record to display in your pivot. You can filter this out if you want but you will need to keep an eye out on your filter options as new records such as a new employee may not always be automatically included in the pivot filter.
The below GIF demonstrates how refreshing a pivot table with a column based Data Source will automatically add a new record. The GIF also demonstrates how a filtered Pivot Table will not automatically include new data if records like “blank” are filtered out.
Selecting a specific range of data will only show that data in your Pivot Table. If your record set increases, your pivot table will not automatically adjust. You will need to go into the Pivot Table data source and change it to include the new rows, row 10 in the above example. If the rows decrease, you will want to make sure you adjust your Data Source accordingly otherwise blank records will show.
I personally prefer this method when handing out templates to people. Most of my templates require the user to export data from a query in our system. In my opinion, it’s easier to teach people to manage their data source than to manage blank records, filters, adding new items, etc.