Column order in pivot tables

R

Randy Harris

I have a workbook with several pivot tables that use "Month" for column or
row headings. Month is derived with:

=INDEX(Months,MONTH(F1),1)&" " &RIGHT(YEAR(F1),2)

The problem is that the columns don't come out in the correct order. (Apr
03, Jun 03, Dec 03, then May 03 - it's neither chronological nor ASCII
order). How should I go about fixing the pivot tables so that they display
the columns in order?

Excel Rookie -
 
D

Debra Dalgleish

Instead of calculating the month and year in the source table, you could
group the dates in the pivot table, by year and month.

Or, calculate the year, then the month number, and sort the headings in
the pivot table:

=YEAR(F2)&"-"&TEXT(MONTH(F2),"00")
 
R

Randy Harris

Debra Dalgleish said:
Instead of calculating the month and year in the source table, you could
group the dates in the pivot table, by year and month.
Click to expand...

This sounds like a good approach. I added the Date, to the pivot table (in
place of "Month"). When I select Group, however, I get a message: "Because
this field is grouped, you cannot add a calculated item to it." I've tried
ungrouping it, but still get that message. I don't know what to do to group
by year and month.
 
D

Debra Dalgleish

If you have a calculated item in the pivot table, you'll get that error
message. Either delete the calculated item, or use a formula in the
source table to create a Year-Month field, and add that to the pivot
table. For example: =YEAR(F2)&"-"&TEXT(MONTH(F2),"00")
 
Top