Excel Pivot Table Date Order

March 05, 2002 - by Bill Jelen

Thanks to Hernan in Argentina for the referral for this week's tip. Hernan's brother in Miami had a pivot table problem, and Hernan urged him to call MrExcel for help.

I have a pivot table with clients down the side and months going across the top. For some reason, May of 2000 is appearing after December of 2000. I keep hitting the recalc button, but May will not return to it's proper place.

My initial reaction was that somehow the records for May were not really dates. If you have a column of dates with some text entries that look like dates mixed in, this might happen. Upon closer examination, the entire column was correctly formatted as Excel dates.

From the pivot table, click on the gray Month field. This opens up a Pivot Table Field dialog box. On the right hand side, click the Advanced button. On the Pivot Table Field Advanced Options dialog, I found the AutoSort options to be set to Manual. When I changed this to Automatic, May quickly returned to the proper place.

This is a bizarre problem, because no one went in and purposely set the AutoSort options to manual. The problem innocently cropped up during the creation of the pivot table.

When the pivot table was created, it included only a subset of the customers. As luck would have it, this subset of clients made purchases in every month except May. Excel created the pivot table with 11 months going across the top. Later, while analyzing the data, additional customers were added to the pivot table who did have sales in May. Excel warned that the operation would change the layout of the pivot table, and then added May in as the 12th month. Excel was smart enough to realize the months were not in order and checked the "Manual" option in the AutoSort section.

If you are creating a pivot table which might offer this type of problem during creation, you can use the "Show Items with No Data" option to make sure that all of the months will appear. In the Layout section of the Pivot Table Wizard, double click the Month field, then check "Show Items with No Data" in the Field dialog box.