Create a Pivot Table Month-over-Month Variance View for Your Excel Report

By Michael Alexander

A commonly requested report view in an Excel pivot table is a month-over-month variance. How did this month’s sales compare to last month’s sales? The best way to create these types of views is to show the raw number and the percent variance together.

In that light, you can start creating this view by building a pivot table similar to the one shown in this figure. Notice that you bring in the Sales Amount field twice. One of these remains untouched, showing the raw data. The other is changed to show the month-over-month variance.

image0.jpg

This figure illustrates the settings that convert the second Sum of Sales Amount field into a month-over-month variance calculation.

image1.jpg

As you can see, after the settings are applied, the pivot table gives you a nice view of raw sales dollars and the variance over last month. You can obviously change the field names to reflect the appropriate labels for each column.

To create the view in the figure, take these actions:

  1. Right-click any value within the target field.

    In this case, the target field is the second Sum of Sales Amount field.

  2. Select Value Field Settings.

    The Value Field Settings dialog box appears.

  3. Click the Show Values As tab.

  4. Select % Difference From from the drop-down list.

  5. In the Base Field list, select the field that you want the running totals to be calculated against.

    In most cases, this is a time series like, in this example, the SalesDate field.

  6. In the Base Item list, select the item you want to compare against when calculating the percent variance.

    In this example, you want to calculate each month’s variance to the previous month. Therefore, select the (Previous) item.