Forcing pivot table to keep all rows(including those without data)

[JackXcel]

Heya,

I have this dynamic pivot table + normal calculating table + chart- reporting combination, where I choose parameters from the pivot table filters thus making the pivot table change accordingly. In addition my own table below the pivot table calculates some values out of the pivot table in accordance to what's currently being shown there and this calculated data is then shown also in the chart below it all. My problem comes up with my newly acquired skill (thanks to newdoverman) to use conditional formatting in the calculating table to show how the values differ from the average.

When changing the filtering parameters in the pivot table, the pivot table sometimes reduces in size (rows) due to some data lacking regarding to the chosen parameters. This causes my calculating (and conditional formatted) table to change in size as well. However, my reference table for the conditional formatting keeps its form, thus causing the conditional formatting to refer to wrong cells. This could be solved if the pivot table did not change size, but would rather simply show an empty row should the data for that row not exist in the data source. Is this possible to accomplish somehow?

Thank you in advance for any replies.

-JackXcel

[JackXcel]

Self replying because with help from another post in "Similar threads" I managed to find a solution to this:

Excel 2013: Right click the pivot table field - field settings - layout & print - mark "Show items with no data"

[JackXcel]

Decided to post the "new problem" on the same issue on this same post.

So, like said, the solution mentioned above works. Except that if the table is changed somehow, for an example changing the "row field" to another, this option does no longer apply as it was previosly set only for the fields _currently_ showing (=selected). I currently have a number of fields for row data, between which I alternate by having them all as filter possibilities, and taking one at a time from the filters to a row. I change the data I want to show by taking the one field back from the row area to the report filter area and moving another one from the report filter area to row area.

Anyone have an idea how one could do this for the whole pivot table? Or do I have to figure all possible combinations of fields and change this for them manually? This is also possible, in case the setting is maintained for the fields even through radical changes in the table, but would be quite a task in any case. The option in pivottable options - display - show items with no data on rows/columns is greyed out.

Thanks for possible replies,

JackXcel