Building Excel Pivot Tables for Sales Forecasting

By Conrad Carlberg

Assume that you have a baseline of sales data and are ready to create an Excel pivot table and group the date field to forecast your sales. What now?

Suppose your table looks like the following image.

4701_Sales-Rep
The table shows sales by date and by sales rep.

The problem is that you don’t need to forecast sales by sales rep. And you don’t need to forecast sales by day. In this case, the whole idea is to forecast sales by month. Corporate couldn’t care less who made the sale, or on which day. They don’t want to know how much George Smith sold on February 24. They want you to tell them how much your sales team will sell in January 2017. For that, you don’t need daily sales data and you don’t need salesperson data.

You do need to know monthly totals. With that information, you can generate a forecast that’s rational and credible. You’ll get the monthly totals, but first you need to build the pivot table.

  1. Click one of the cells in your table.

    When Excel creates the pivot table, it needs to know where to find the table that contains the raw data. If you start by selecting a cell in that table, Excel locates its boundaries for you.

  2. Go to the Ribbon’s Insert tab and click the Pivot Table icon in the Tables group.
    The dialog box shown here appears.
    4702_basic-data
    This is where you tell Excel how to find the basic data. In this case, your data is already on the worksheet, in the form of an Excel table.
  3. Select the Existing Worksheet option button if you want the pivot table to appear on the same worksheet as the table.
    Especially during initial development, it’s useful to keep the raw data and the pivot table on the same worksheet.
  4. Click in the Location reference edit box and then click a worksheet cell to the right of or below the table. Click OK. Your worksheet will resemble the one shown here.
    4703_fields-pane
    Finish defining the pivot table with the PivotTable Fields pane.

    When Excel creates the pivot table, it builds it to the right and down from the cell you select. If you start the pivot table to the left of or above your list, Excel will ask you if you really want to overwrite those cells.

  5. Click and drag the Sales Date field from the Fields section to the Rows area.
    Depending on the version of Excel you’re running, Excel might add a Months field along with Sales Date to the Rows area. If so, you’ll see the sales month on the worksheet as the new pivot table’s row field.
  6. Click and drag the Sales Revenue field from the Fields section to the Σ Values area.

You now have a pivot table that shows a baseline of sales revenues by sales date. It should look something like the pivot table shown here.

4704_Pivot
You can display individual days by clicking the expand box next to any month name.

Excel might automatically put Month into the pivot table’s rows. If you don’t want it there, just click Month in the PivotTable Fields pane’s Rows area and drag Month back up to the Fields section.

In the meantime, it’s usually a good idea to format the Values field properly. Here, that’s using the Currency format. Here are the steps:

  1. With the PivotTable Fields pane visible, click on the drop-down arrow next to Sum of Sales Revenue in the Σ Values area.
  2. Choose Value Field Settings from the shortcut menu.
  3. Click the Number Format button.
  4. Choose the Currency format.
  5. Adjust the number of decimal places.
  6. Click OK to return to the Value Field Settings dialog box, and click OK again to return to the worksheet.

If you’re using Excel 2016, which provides automatic time grouping, the pivot table should now appear as shown here. Otherwise the rows will reflect the periods used in the pivot table’s data source. If you still see individual dates, for example, instead of months, refer to the next section for information on arranging individual dates into longer periods of time.

4705_Currency
The Sales Revenue field is now formatted as Currency.