inserting custom values in pivot headers

[killjoy1]

I have a table that keeps changing continuously with forecasts for each month. I have a report that has a pivot table in the summary worksheet whose source data is in a different worksheet. Every week, I update the source data table with the latest data and just refresh the pivot for the summary reports.


Problem -


sometimes, my source data has no rows for a particular month (eg. Dec). When I hit refresh, the pivot will remove the Dec column. Is there any way to force the pivot to keep all the 12 months in the column fields even if there are no records (is there any way to enter the columns fields). The report cannot have months missing. If there are no rows, I need the column to be just blank in the pivot.


I tried inserting dummy rows with just the month values, but that will insert a row called "(blank)" and that is not okay.


I know this can be done using sum if. But I dont want to. (in future, I need to change the source to a database with just a pivot in the xls)


I want to use Pivot to do this. Is it possible?

 

[Hui]

KillJoy1


Firstly, Welcome to the Chandoo.org Forums


I don't believe that what you want to do, inserting missing headers automatically, is possible. For the reason that the pivot table has made a list of all the possible values for a Field from the field values and that if a field value is missing it doesn't know about it.

Field values that become Column Headers effectively become labels based on there values, but there is the option to maintain them as values.


So if you want to have blank columns I'd suggest setting up a set of data 12 values from Jan to Dec where each record will have a blank value in all other fields

You may have to put 0's in some fields.

This will work for Sum but may upset Count & Average calculations of the value field

 

[killjoy1]

Thank you Hui for the response.


Yes it does create problems for count.