Pivot table help needed

JMH022

Active Member
Joined
Mar 7, 2002
Messages
320
I have a pivot table that organizes year-over-year sales data by month and year for current month-plus 2. I have the month and year data in columns; Month and Year are both sorted Ascending, with month listed first so the pivot will display
Nov..................Dec
2005 2006........2005 2006, etc.

The problem I am having is this...

Starting this month, my pivot table contains data for Nov, Dec, and Jan. The pivot table is placing Jan before Nov and Dec, because the table is set to sort the months in Ascending order. I need to know how to make January data to follow December while keeping the pivot table in the same display format it is currently in.

Anyone out there an expert on pivot tables that can help me out?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi,

I think I know of 2 options you can try.

1 - In your source data replace each month with a month number. So replace all Jan's with 1, replace all Nov's with 11 and so on.
So now all months will be in numerical order and not alphabetical. Not the smartest way.

2 - Replace each month with a date. Such as replace Jan's with 01/01/05, replace Nov's with 11/01/05
In the pivot table, you can now use the group funtion and group the dates by month (Jan, Feb etc) that will display the months by alpha whilst sorting them by numerically.

I hope that helps you a little.
Regards
Mark
 
Upvote 0
Mark: Thank you for your suggestions. Your item #1 may give me the same result, as the table is sorting the Month and Year separately. I will look into your #2 suggestion...it may be feasible, but I was hoping to use the fields already built instead of having to create another field.

Thanks again.

Any other suggestions anyone?
 
Upvote 0
Jim

How is the date stored in the original data?

How are you getting the months in the pivot table?
 
Upvote 0
the Pivot data is extracted from an Access db I inherited. Queries within the db extract daily sales data; The db then reformats the sale date field using the 'DatePart' function and counts up the number of sales in a given month.

I think I see where you are headed with this one...use the original sale date field or concatenate the month and year fields to use as a sort field.

Am I correct?
 
Upvote 0
Jim

Where I was heading was to just group by year and month on the date.
 
Upvote 0
I love this board. It really helps get you thinking. I will work on these suggestions and see what I can come up with.

If anyone has other ideas, please let me know.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top