Using Excel Pivot Table Column Fields in Sales Forecasting

By Conrad Carlberg

In setting up your sales data to do forecasting, you’re going to have a lot more use for row fields than for column fields, because Excel handles tables so well.

A column field puts different records into different columns — but an Excel table asks you to put different records into different rows. Suppose you have a table that shows the date and the revenue for each date. You want your pivot table to summarize your sales data by month. If you put the date field into the Column area, you’ll wind up with a different month in each column.

That’s inconvenient, especially if you want to use the Data Analysis add-in to create a forecast. (It can also be mildly inconvenient if you want to chart your revenues over time.)

In sales forecasting, column fields really come into their own when you have several product lines or territories that you want to analyze along with sales dates. Pivot tables can have row fields and column fields (and, by the way, filter fields, which in some prior versions of Excel were called page fields). The combination of a row field with a column field is ideal for forecasting sales of different product lines.

Your table could have three fields: date of sale, product line, and sales revenue. Then your pivot table could use the fields like this:

  • Date of sale as a row field: Each row in the pivot table corresponds to a different total of sales during a particular month.
  • Product line as a column field: Each column in the pivot table corresponds to a different product line.
  • Revenue as a value field: Each cell in the pivot table sums the revenue for a particular product line on a particular date.
4501_Column
This pivot table resembles a standard table, and you can use the Data Analysis add-in to create forecasts from it.

Now the pivot table resembles a standard Excel table. Its structure makes it easy to get a forecast for each product line, whether you’re using the Data Analysis add-in to get the forecast or using a trendline in a chart.