Adding Annualized column to PP without it dupilcating after each column


Board Regular
Mar 18, 2010
Excel 2010

Hi, I have a simply count of cases in the values of my PP, and have fiscal year then fiscal quarter in the column label. I would like to add a SINGLE column or measure in the last column that annualizes the final year as I update it each quarter. However, if I create a measure it places a new column after every column I have in the pivot table.

Currently I have to build the entire pivot table with measurements to a achive this, but this requires that I manully update formula all the time. Plus I do not want to have to hide columns as this is a user interactive dashboard.

Thanks for any suggestions.


Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December


Board Regular
Apr 29, 2014
You can use a set (Options->Fields, items & sets->Create a set based on columns) to hide the annualized measure in all (pivot) columns beside the last one. This should also work for a user interactive dashboard.

Cubeformulas would be another option if a pivot is not really needed. Using different time periods with Cubeformulas is easier than creating measures for that in a pivot.

Forum statistics

Latest member

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...