Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't group pivot table items by month in Excel | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions | |||
Pivot Table - Group by Month - Show Items with no data | Excel Discussion (Misc queries) |