Dynamic pivot table column addition


  • Hello, so I'm looking at some data that deals with number of people being worked with and a date period with which this will happen. The problem is that sometimes the period is not sufficient so I want to show how many people would overflow and if so, to which months. I am displaying the current information on how many people per day can be served and how many total will overflow in a pivot chart and am wondering, is it possible to dynamically add columns representing months for the overflow to the pivot table? If so, how?
    Tuesday, June 12, 2012 1:51 AM


  • Hi

    I want to confirm with you that the Pivot Table will show the months which overflow and the how many total will overflow, am I right?

    If what I understand is right, I think you can add a column (named as overflow_month) and use a formula in the table to judge whether the data is overflow. And if the data is overflow, then the column overflow_month will show the month. Then make the Pivot Table show the month.

    Note: the only shortage is that the months which doesn’t overflow will show ”blank” in the Pivot Table.

    The formula in column overflow_number is:

    =IF([@[People_number]]-[@[Max_number]]>0,[@[People_number]]-[@[Max_number]],"No number")

    The formula in column overflow_month is:


    Jaynet Zhang

    TechNet Community Support

    Wednesday, June 13, 2012 5:20 AM