Pivot table sorting numbers as text

crookesa

Board Regular
Joined
Apr 11, 2013
Messages
80
I have a pivot table created from a table on a different worksheet.
My first row is a field called Working Week followed by 2 other fields.

I am trying to sort the Working Week columns numerically from lowest to highest but the pivot table sees the data as text and is not sorting as I would expect.

The column in the table is a formula based value =IF([@Date]<>"",IF(WEEKNUM([@Date],15)=53,1,WEEKNUM([@Date],15)),"") and works as expected as I have set the category to number.

Is there anyway to get the pivot table to sort correctly, I have the same setup in another workbook and it works as expected but can't for the life of me figure out why it's not sorting correctly.

Pivot Table Sort
1607593355007.png


Source Data
1607593646222.png
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
707
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Sorting is not required it seems as shown below.
Did you sort on the correct pivot field?

No sort:
1607859086666.png


Sort on Weeknum
1607859274873.png
 
Solution

crookesa

Board Regular
Joined
Apr 11, 2013
Messages
80
Thanks for the help, I tried creating it again using my formula and it worked. Not sure why it saw it as text even though I specified it was a Number type.
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
707
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Good to read you got it sorted and that somehow my comment was useful for you.
You have double quotes in your formula evaluation, note that is a textstring for Excel. Empty it is, but also text.
 

crookesa

Board Regular
Joined
Apr 11, 2013
Messages
80
Good to read you got it sorted and that somehow my comment was useful for you.
You have double quotes in your formula evaluation, note that is a textstring for Excel. Empty it is, but also text.
Ok, my intention of the formula was that if that the @Date field in the table was left blank it wouldn't show the value 1 but present a blank. Would there be a better solution then for error catching and presentation of the calculated field?
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
707
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Since it is working now, you probably don't need to change anything.
Alternative, is wrapping your formula inside a TEXT() function, like TEXT(if-formula, "00"). Sorting on this text field will work.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,840
Messages
5,627,186
Members
416,228
Latest member
JOOTEISHERE

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