Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an Excel 2010 PivotTable pointed to some data in the workbook that includes a date. I can group the date field into year and month just fine. What I want to do is produce a chart of a filtered date range (e.g. the previous 12 months). However, whenever I try to apply a "Between" date filter to the grouped date field (either the Year or Month grouping), my table blanks the rows of the filtered-out dates, but continues to display the row labels.

To illustrate, the chart of the table ends up looking like the following (showing blank columns for the filtered-out dates): Excel 2010 Pivotchart

I control the underlying data; I can add additional columns to filter on, if necessary. So far, my only solution has been to delete all the data outside my desired date range and add separate columns for the month and year to act as row labels in the pivot table (i.e. I am no longer grouping on my date field). This seems like an unnecessary effort - surely someone else has this same requirement? What setting(s) am I missing?

share|improve this question
    
No reason why it shouldn't work (it does for me). What does the pivot table look like when you've filtered out the dates? Does it have lots of blanks too? –  Rhys Gibson Apr 11 '11 at 8:09
    
@Rhys Gibson - the pivot table likewise displays a blank row for each of those months in the chart above. –  ropable Apr 12 '11 at 0:07
    
Then the problem is the pivot table, not the chart. The chart is simply displaying what's in the table. So if you can work out why your pivot is showing 0's or filter out the blanks, the chart should automagically fix itself. –  Rhys Gibson Apr 12 '11 at 2:32
add comment

1 Answer

up vote 2 down vote accepted
  • Right-click on a cell in your month and/or year column, choose "Field Settings"
  • Layout and Print tab
  • Uncheck "Show items with no data"

P.S. This will hide months with no data within your desired period as well.

Also, since you control the source data, I find that adding permanent columns for years and months is great. For the months, I'd recommend having them as "2011-01-01, 2011-02-01, 2011-03-01" in your data and format the field as desired ("mm" or "mmm", etc.)

This can let you have huge amounts of data - just use Year and Month as row labels and/or filters to get the periods you want without the < and > totals that groups make.. Since they're raw data, the PT will always be simple and work predictably. It also lets you use features not-available on groups, such as regrouping them further in seasons or however you wish.

share|improve this answer
    
Thanks, that's the closest thing to a solution I've had (I would still like to display "empty" months, but I guess you can't have everything). Accepted for pointing out the "Show items with no data", which I didn't know about. –  ropable Apr 20 '11 at 3:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.