Create a Pivot Table YTD Totals View for Your Excel Report

By Michael Alexander

A useful report view in an Excel pivot table is the YTD totals view. Sometimes it’s useful to capture a running-totals view to analyze the movement of numbers on a year-to-date (YTD) basis. This figure illustrates a pivot table that shows a running total of revenue by month for each year. In this view, you can see where the YTD sales stand at any given month in each year.

For example, you can see that in August 2010, revenues were about a million dollars lower than the same point in 2009.

image0.jpg

To create this type of view, take these actions:

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

    For example, if you want to change the settings for the Sales Amount field, right-click any value under that field.

  2. Select Value Field Settings.

    The Value Field Settings dialog box appears.

  3. Click the Show Values As tab.

  4. Select Running Total In 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 would be a time series such as, in this example, the SalesDate field.

  6. Click OK to apply your change.