Hello:

I have a case that the pivot table's source data from the column that
was in month. I have try to use this data in pivot table it show the
format was not that I expected. May I know how can I correct it?

Date Month Units
02-Jan-2008 January-2008 234
08-Jun-2008 June-2008 5
21-Apr-2008 April-2008 235
16-Aug-2008 August-2008 34
26-Aug-2008 August-2008 6
26-Jul-2008 July-2008 6
29-Apr-2008 April-2008 34
13-Apr-2008 April-2008 6


I expect the pivot table result like this. How can I do?
Row Labels Sum of Units
April-2008 275
August-2008 40
January-2008 234
July-2008 6
June-2008 5
Grand Total 560




Thanks!

before creating pivot table

use helping column C

Col A - date
Col B - month
Col C ( helping column ) put this formula and drag it down =TEXT
(A2,"MMM")
Col D - units

Now, Go to tools | options | custom list | list entries :Apr
Aug
Dec
Feb
Jan
Jul
Jun
Mar
May
Nov
Oct
Sep Click Add | ok |

And now go to data | pivot table...... | range A1:D10 | next | click
layout | helper column in row data | units are in data field | ok

Select the cell | go to data | sort | click options | select the first
key sort order : Apr,Aug,Dec,Feb........ | ok | ok




On Dec 1, 8:47*am, Blue Fish wrote:
Hello:

I have a case that the pivot table's source data from the column that
was in month. I have try to use this data in pivot table it show the
format was not that I expected. May I know how can I correct it?

Date * *Month * Units
02-Jan-2008 * * January-2008 * *234
08-Jun-2008 * * June-2008 * * * 5
21-Apr-2008 * * April-2008 * * *235
16-Aug-2008 * * August-2008 * * 34
26-Aug-2008 * * August-2008 * * 6
26-Jul-2008 * * July-2008 * * * 6
29-Apr-2008 * * April-2008 * * *34
13-Apr-2008 * * April-2008 * * *6

I expect the pivot table result like this. How can I do?
Row Labels * * *Sum of Units
April-2008 * * *275
August-2008 * * 40
January-2008 * *234
July-2008 * * * 6
June-2008 * * * 5
Grand Total * * 560

Thanks!

If you just enter the "month" & "year" in month column (B), excel will
automatically insert Day 1st for that month, so you may not need to do the
text function to extract the month in dummy column.

Use Pivot table and put [Unit] field in the right-hand corner or Value
section in pivot table. Then put the [Month] field in Row Label column which
in the left hand side.

Hope it helps.

Thank you.


"Blue Fish" wrote:

Hello:

I have a case that the pivot table's source data from the column that
was in month. I have try to use this data in pivot table it show the
format was not that I expected. May I know how can I correct it?

Date Month Units
02-Jan-2008 January-2008 234
08-Jun-2008 June-2008 5
21-Apr-2008 April-2008 235
16-Aug-2008 August-2008 34
26-Aug-2008 August-2008 6
26-Jul-2008 July-2008 6
29-Apr-2008 April-2008 34
13-Apr-2008 April-2008 6


I expect the pivot table result like this. How can I do?
Row Labels Sum of Units
April-2008 275
August-2008 40
January-2008 234
July-2008 6
June-2008 5
Grand Total 560




Thanks!