Pivot Tables To The Rescue

Did you know that you can develop a Microsoft Excel template that you can distribute to your extended users such as the field sales force? You can leverage Pivot Tables within Microsoft Excel to develop impactful tools to drive actionable insights to the ultimate end users in your sales and marketing organizations.

Let me show you how to build an Excel template to distribute syndicated data to your sales team who don't have access to the data. Since most people have Excel on their computers this provides an easy way to get specific issue driven templates and information into the hands of the end users of the data.

The first step is to organize your data in a flat file. A flat file is defined as an unformatted square "chunk" of data as the following example will show:

We can now go to cell A1, from the insert menu select the pivot table option and chose the entire set of data to build a Pivot Table (I am using Excel 2007 to build our example). We can choose to create a new worksheet or we can decide to place the pivot table on the current worksheet. If you select ok, it will automatically define the range it interprets and default to a new sheet within the same Excel file:

We can drag fields from the pivot table field list to the various elements of the pivot table report to build our report layout:

  1. Drag Markets (MKT) and Periods (PER) in the Report Filter section of the report so we can select one or more markets to view and a time frame (4 Weeks, 12 Weeks or 52 Weeks).
  2. Drag the Products (PROD) field to the row labels box and since we want to see dollar shares and the impact of trended dollar sales.
  3. Drag Dollar Volume ($ VOL) and Dollar Volume Change versus year ago (DOLL VOL CHG VS YAG) to the values box.
  4. The Pivot Table will be automatically created.

Our report output needs to be presentation ready so we will want to have two separate graphs populated when we select a market. For example I want a pie chart to show the shares of the brands in a selected market and the bar chart to show the dollar trend changes for each brand versus the same period one year ago. To do this we can build data ranges outside of the pivot table to be charted in our template. First, let's set up the table for our pie chart:

  1. Move to a separate place on your worksheet, beneath or beside your Pivot Table.
  2. Use the "=" function to reference the data from the pivot table and fill the table you are creating.
    1. Place an "=" in the new "pie chart" table, in the product name cell (A15 in my example) and click in the cell of the first product (A6 in my example) in the pivot table.
    2. Repeat until all products are represented.

Next, we will reference the dollar sales fields in the same manner

  1. Place an "=" in the dollar sales value cell (B15) and then clicking on cell B6.
  2. Repeat until all of the product's dollar sales are represented.

Now, let's create the table to be used for the bar chart.

  1. Reference the percent change in dollar sales like we did in the pie chart, starting with the product name.
    1. Place an "=" in the new "bar chart" table, in the product name cell (A23 in my example) and click in the cell of the first product (A6 in my example) in the pivot table.
    2. Repeat until all products are represented.
  2. Now, reference the percent change in dollar sales.

We will then produce each of two graphs (a Pie Chart and a Bar Chart) on one page of our template in the following manner:

  • Highlight the area to be graphed
  • Select the insert menu and pick the pie chart option
  • Format a pie chart as you normally would
  • Create a second chart in the same manner selecting the bar chart option

Now, for with a little cleanup effort such as:

  • Allowing hidden data to display in the graph
    • Right mouse on the graph object and pick "Select Data" for the menu
    • Click on "Hidden and Empty Cells" (bottom left in the dialogue box)
    • Click "On" the "Show Data In Hidden Row & Columns"
  • Align graphs under the pivot table drop downs
  • Hide the rows that show the data set for the pie chart and bar chart
  • Turn off gridlines and headings on the Page Layout Ribbon (Excel 207)

Your template is now completed and you can see market shares and volume trends in one easy to use template that can be printed off and presented to your customers:

The above template is a very simple example of creating an Excel tool to better visualize your data as real and actionable information. Additional levels of sophistication can be added such as intelligent text bullet points, dynamic rank reports, navigation buttons to navigate the user between multiple charts and tabular data visualization charts. In future editions of the Insight newsletter we will demonstrate how to build these enhancements into the template.

Insight, Information & Consulting Services, Inc. is dedicated to delivering better data visualization tools to its clients. We develop data visualization tools for Trade Promotion Analysis, Category Management, Consumer Analysis and sales & marketing business analysis templates. Please contact us at 770-425-4243 or visit our website at www.insightinformation.net.

Leave a Reply

Your email address will not be published. Required fields are marked *