Excel 2010 pivot table/chart - how to filter a date range without empty rows

Question

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):

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?

Answer 1

• 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.

Answer 2

I had the same problem. Here is a workaround I have developed.

1. Make the first pivot table without chart otherwise the same way as before. Use the same setting , but without date filter. Be sure that "Show items with no data" is applied. This is not set by default and can be found using right click "field setting"/second tab. You will have lot of useless items as you had, but continue
2. Change your pivot table to look like an excel sheet. This is doable in Excel 2010: go to pivot design menu and eliminate subtotal, grand total and "Show in tabular form" and "repeat all item labels" form in report layout. Now your pivot table looks like an excel sheet.
3. Use the data as excel sheet, but for this you must copy rows you would like to show in your chart to a new sheet. Give some meaningful header to your data - good starting can be copying it from your pivot table. Now use the generated excel sheet as source of 2nd pivot table. Be sure you mark "Show items with no data" again, but this time you will not apply date filter (you have already filtered the period by selecting them for copy).

The chart based on 2nd pivot table will look like you wanted it from the beginning