Pivot table sorting issue

markster

Well-known Member
Joined
May 23, 2002
Messages
537
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi

I have a pivotable sorting issue whereby columns mostly sort in date order and then randomly don't. Here's what it does

1623449688197.png


So you can see that the latter part of May is in the right order but then it goes up to 31-May is fine then it randomly goes to 7-May, 9-May, then 1-Jun, 2-Jun, 4 Jun.

I was thinking it must be something to do with the date format in the pivot table data so I tried to use the Datevalue function to convert the date (i.e Datevalue(A7) but this function just returns VALUE! error.

The actual data date is in the following format so seems like a date

1623450049726.png


Any ideas anyone?

Thanks
Mark
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,282
Office Version
  1. 2010
The actual data date is in the following format so seems like a date
Are the dates right or left aligned when you don't apply manual alignment? If left aligned : text. Apply one of the well known techniques to make them real dates
 

markster

Well-known Member
Joined
May 23, 2002
Messages
537
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi there - the dates align to the right so that's not the problem but this is happening with numbers too. Please see below. I just want everything to be ordered by date /numerical but I can't figure out why this is happening. Any other ideas? Cheers.

1623676881835.png


Thanks Mark
 

markster

Well-known Member
Joined
May 23, 2002
Messages
537
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Just to clarify the above is two different headings from 2 different pivot tables
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,138
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I have to ask. Have you actually applied a sort ?
A pivot table appears to sort when you first create it but unless you actually apply a sort it will not sort when you refresh the data.

What is not visible from your screenshots is the heading showing the little sort icon which in the both the below is pointing up for ascending.

Tabular layout and Compact layout respectively

1623679592637.png
 
Solution

markster

Well-known Member
Joined
May 23, 2002
Messages
537
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
oops sorry just getting to grips with pivot tables - i'm glad you mentioned that because I didn't and I have now and it's fine. I just assumed that they would automatically sort in order - I've learned something ! Thanks again.
 

Forum statistics

Threads
1,136,644
Messages
5,676,967
Members
419,665
Latest member
allsmilees2

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
Top