Group by Month when no data
 

I have a series of pivot tables set up as one report to show data activity
per month, however, some months there is no data and the month is excluded
from the table. This means the month are out of line on the different tables
from each other. How can I set it up so that when there is no data that
month it will show Zero or blank?

Many thanks

--
GLS

OK GLS, are all the months present in the source data, even if they have no
data? If not, make sure that they are (you can remove them later). Now
refresh the pivotable and you can click the arrow on the Months tab, which
will allow you to check that all the months have ticks next to them by
selecting "Show All". Then goto Table Options and unclick AutoFormat Table.
Now your pivottable should show ALL months regardless of whether they have
contain data, and should also stay the same size. Remember, if it isn't in
the source, it can't be in the pivot so you mite have to add some 'dummy'
data in the first instance. You can always remove it later, as once in the
cache, the table 'remembers' its exsistence. Clear as mud? I tried to be
concise!

Hth,
Oli

--
Message posted via http://www.officekb.com

Right-click the Date field button
Choose Field Settings
Add a check mark to 'Show items with no data'
Click OK

GLS wrote:
I have a series of pivot tables set up as one report to show data activity
per month, however, some months there is no data and the month is excluded
from the table. This means the month are out of line on the different tables
from each other. How can I set it up so that when there is no data that
month it will show Zero or blank?

Many thanks



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html