Pivot Table - Sort by Year Without Grouping

[Jamsandwich]

Hey guys,

I've generated a pivot table with dates as Row Labels. The source data has several entries for each day so I've had to group the Row Labels by day.

This has had the effect of taking out the year from the dates (now dd-mmm). So when the data goes back beyond the beginning of this year, last years dates are automatically sorted to the bottom of the dataset.

I know that I can group by year too, but this adds rows that I don't want (I have another table being generated from the pivot table rows, important that row data remains as is).

Is there a way to group the rows by day while retaining dd-mmm-yy format? Or can I get the year to appear in a seperate column rather than a row?

Excel 2010.

 

[RoryA]

If you have the pivot table in tabular layout you will get a new column. Is your other table linked using formulas?

 

[Jamsandwich]

Oh wow, that was easy.

Yeah, my next table's first column is "=PivotSheet!B5" with the row number changing for each row of course. This is a table that allows me to produce Statistical Process Control charts.

Having the year in the same column screwed up all the formulas. Didn't realise I could switch to a different layout though. Never actually used a pivot table before, great tool.

That fixed it. Thanks once again Rory.

 

[RoryA]

I'd suggest you use GETPIVOTDATA formulas instead - then it won't matter what the layout of the table is. The syntax is a little trickier than a simple cell link, but it's more robust.

 

[Jamsandwich]

OK, thanks for the advice.

I'll have a look into that formula and see if I can improve my sheets.

 

[RoryA]

Glad to help.

You might find this helpful for GETPIVOTDATA: Customising GETPIVOTDATA formulas | Excel Matters