Excel Dashboards and Reports: Keep Your Pivot Table Fresh

By Michael Alexander

As boring as your pivot tables may seem, they’ll eventually become the stars of your Excel reports and dashboards. So it’s important to keep your pivot tables fresh and relevant.

As time goes by, your data may change and grow with newly added rows and columns. The action of updating your pivot table with these changes is refreshing your data.

Your pivot table report can be refreshed by simply right-clicking inside your pivot table report and selecting Refresh, as demonstrated in this figure.

image0.jpg

Sometimes, you’re the data source that feeds your pivot table changes in structure. For example, you may have added or deleted rows or columns from your data table. These types of changes affect the range of your data source, not just a few data items in the table.

In these cases, performing a simple Refresh of your pivot table won’t do. You have to update the range being captured by the pivot table. Here’s how:

  1. Click anywhere inside your pivot table to activate the PivotTable Tools context tab in the Ribbon.

  2. Select the Analyze tab on the Ribbon.

  3. Click Change Data Source, as demonstrated in this figure.

    image1.jpg

    The Change PivotTable Data Source dialog box appears.

  4. Change the range selection to include any new rows or columns, as seen in the figure.

    image2.jpg

  5. Click OK to apply the change.