Excel treating people with the lastname of April or May as months

[richarjb]

Hey everyone,

I've got this really irritating problem. I work in payroll so obviously I'm dealing with lots of employee data all the time. Anyway, whenever I create a pivot table that has surname, forename etc. the pivot table always pushes people with a surname such as May or April to the top of the surname column. I've tried formatting as text before and after dropping into a pivot table but excel just ignores it. It always treats the surname as a month of the year. I just can't get it to sort properly in strict alpha order. Has anyone had the same issue? I mean it's not critical but for me it's a real pain in the - well you know.

 

[Norie]

How is Excel treating those names in the source data for the pivot table?

 

[richarjb]

Initially under the general format. Then I format as text.

 

[Krayons]

Hmm, that seems very unusual. I could understand if "April" appears at the top of the list since it starts with "A". Could you please provide us with an example?

What is the result you are expecting?
What are some of the other values that appear in these columns?
I was unaware that pivot tables even automatically sort things (then again, i don't really bother with pivot tables much, i just dont see the value in them).

Don't copy or paste any value, clear the contents of the cell, change the format to text, then manually type in the surname of "April" (or whatever) and see if it still happens.

Edit: Just a thought, but check to see if there are any spaces in front of these names that are causing you issue. Perhaps the space at the beginning is just giving you the illusion of a buggy sort order?

 

[Norie]

But in the source data are these names also being treated as dates?

You could test that using ISNUMBER, or perhaps just by looking in the formula bar when one of the names is selected.

PS Are you importing/pasting the data from another source?

If you are then you might want to try formatting as text before you import/paste.

 

[richarjb]

The result I expect is strict alpha order like you'd get in a phone directory. Other values could be hours or cash paid but they're in the values field not part of rows or columns. Copy and paste this into a sheet and put it into a pivot table and you might get the same issue I get.

Surname Forename Pay
ADAMS MIKE 100
APRIL STEVE 100
MARCH IAN 100
MACK CLARE 100
MAY JENNIFER 100

 

[richarjb]

Okay I'll give it the ISNUMBER test. Yes data is coming SAP BI4 reports.

 

[Krayons]

Very odd indeed. When I pasted some values and converted to a pivot table, it produced this resulting order (even though I've selected the filter as A-Z):

MARCH
APRIL
MAY
ADAMS
BOB
MACK
SALLY
THOMAS

This is precisely why I think pivot tables are bloody terrible.

Edit: Confirmed defining the fields as text prior to converting to pivot table yields the same result.

Edit 2: Here's your fix.

1. Select data in pivot table.
2. Right click, choose "PivotTable Options".
3. Select the "Totals and Filters" tab.
4. Uncheck the "Use Customer Lists when sorting" option.
5. Fixed.

My recommendation? Don't use Pivot tables.

 

[jamcall]

It is the opposite of the issue resolved here:
Excel Month Names Don’t Sort in PowerPivot

When you click the dropdown to sort by Surname, click more sort options, select Ascending A to Z, click More options, clear checkbox for Sort automatically, set First key sort order to Normal...OK, OK.

HTH,
~ Jim

 

[richarjb]

Nice recommendation Krayons. But 250K rows of payroll costing data per month I'll hang in there. Thanks for Edit 2 - not occurred to me.