Pivot Tables to the Rescue: Formula Finesse

One of the biggest frustrations with pivot tables is the notion that your formulas need to be built outside the main body of the pivot table…the good news is you can put formulas in your pivot tables. Excel 2007 supports a wide range of Excel functions within the pivot table formula wizard. In this article we will show you how to build formulas in your pivot tables and look into some interesting formulas you can use in pursuit of Category Management analyses.

Just to be clear, Excel does not support array formulas which are formulas that affect a range of cells i.e. sum(A3:A34) or countA(B4:B25). However, most any function that deals with a number in each cell can be employed in your pivot table formulas including logical formulas such as If(UNIT>100, UNIT/100,0). Here is how it works:

  1. Create a pivot table by placing your cursor in the cell “A1” position of your data and select insert Pivot Table option. Then and add (drag) at least one field in the Row or Column section and add (drag) a field in the Values section of the pivot table wizard.
  2. With your cursor anywhere in the pivot table (you are in the Options menu), find the Tools section select Formulas to open the Formulas Wizard (Excel 2007); this Wizard can be accessed in the pivot table tool bar in Excel 2003.

  3. Then select the Calculated Field option from the drop down menu under Formulas.

  4. The Formula Wizard consists of the following fields:
    1. Name – Use this field to type in the name of the field. Caution – Do not use the a name that you will eventually use to display in the pivot table as Excel puts the accumulation method as a part of the name i.e. “Pct Chg” will look like “Avg of Pct Chg”. Recommendation – Use a more cryptic name (like $ Pct Chg) in the Formula Wizard so you can reserve the naming description for use in the pivot table (more on this later).
    2. Formula – Enter your formula in this box. For purpose of this example we will build a simple Dollar Percent Change Versus Year Ago calculation. You type the operands, and select the fields. Specifically you type =) then you select Dollar Sales from the Fields selection tool. You type – then you select YAG Dollar. Now you close the parenthesis with )/ then you select YAG Dollar (again). Now your formula will look like the one below.

       

    3. Once the formula is finished click the add button to add the newly developed field to the pivot table field list. Now the field can be added (dragged) to your pivot table Values section.

  5. The newly created field can now be adjusted by creating a more readable name and set the number type etc. from the Value Field Settings option. This can be accessed by right clicking on the Sum of $ Pct Chg tile.

  6. In the Value Field Settings Wizard –

    1. The name can be changed to any unique field name (there can’t be two fields with the same name)
    2. The field summarization method can be set by selecting Sum, Count, and Average etc.
    3. The number format can set by clicking on the Number Format button and selecting how you want numbers to be displayed
  7. Click OK when you have completed your formula and add it to your pivot table.

While we have only explored a simple formula such as Percent Change, but more complex formulas such as the following example can be used as well –
= IF(‘Unit Sales’ >100,’Unit Sales’ /100) to identify stores with unit sales over a certain threshold. The pivot table could be sorted on this calculated field to cluster high or low performing stores.

Insight, Information & Consulting Services, Inc. can help your company develop a data visualization strategy leveraging pivot table technology to display information not just report data. Call Rick Pensa at 770-425-4243 to set up a complementary needs assessment, or visit our website at www.insightinformation.net .

2 thoughts on “Pivot Tables to the Rescue: Formula Finesse”

  1. Hello there !

    Hope you are fine. Well, I am a Finance Manager in an FMCG selling 141 products all together. The management has approved to introduce FIFO for inventory valuation. Can you develop a pivot table which will incorporate cost of sales + closing stock for each line of sale from transaction data ?

    Also, how the sales returns will be processed ?

    Thank you

    1. Shummas:

      I am so sorry, but I have not checked my comments in a while. If you would like to discuss the pivot table you are looking for please call me at 770-425-4243 Ext.301.

      Thanks,
      Rick Pensa

Leave a Reply

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

1 + 13 =