In some cases you might find yourself deleting data, and when applying a PivotTable that deleted data may resurface and cause a number of inaccuracies in your PivotTable. In this week’s tip we use 5 easy steps to ensure that when you apply PivotTables to your data, it will automatically refresh using only the applicable data, and avoid any deleted data from reappearing.
In this tip’s practice exercise, part of the PivotTable’s data has been deleted. We then refresh the PivotTable to show the correct data. But, if you click on one of the drop down arrows on the column headers you will see the deleted data. This could give you the impression that you can filter on data that no longer exists.
Follow the example below as we explain how to display the correct data in a PivotTable filter list.
Applies To: Microsoft® Excel® 2010 and 2013.
Note: You are welcome to download the workbook to practice.
1. Open the practice workbook.
2. Select the Report worksheet.
3. Now select the drop down arrow in cell A3. (This is where all the branch names are displayed.)
4. Select the data worksheet, only North and South Coast’s data are
5. To ensure the correct data is displayed in the PivotTable filter list, follow these steps:
a. Right click on the PivotTable, and select PivotTable Options.
b. Select the Data tab then none under return data deleted from the data source.
c. Select the Options tab (ensure you click on the PivotTable.)
d. Select Refresh, then Refresh.
e. Select the drop arrow in cell A3. (Only the active branch names will be displayed.)
This is an easy tip to follow, and can become quite handy, especially if you frequently use PivotTables. With good practice, this tip will ensure that no deleted data will be displayed in your PivotTable filter list, thus avoiding any confusion and resulting in more accurate data being displayed.
