1

I have an Excel file which I can share with you, but here is how to recreate it:

  1. In a workbook tab, create a table called "DataTable" with the two "Company" and "Score" columns as shown below.

  2. Format the "Company" column explicitly as "Text". You can even precede the entries with single quotes to force Excel to treat them as Text: it won't change the behavior.

  3. Below this table, create a simple PivotTable on "DataTable" with "Company" as a Row Field and "Sum of Score" as a Value Field.

  4. In the PivotTable, Sort the "Row Labels" (Company) field "A-Z".

  5. You will see this, where the values "DEC" and "SEP" bubble to the top above the other A-Z values:

Screencap of PivotTable bug

What apparently is happening is that Excel insists on treating anything that looks like a month name or abbreviation as its numeric equivalent, which sorts above the other A-Z text values.

  • Question 1: Is this a confirmed bug?
  • Question 2: How can I keep the desired values (like "SEP") but stop this from happening?

I am happy to upload an actual simple spreadsheet which shows this behavior. Thanks!

Improve this question

2 Answers 2

Reset to default
2
  1. Click on Row Labels Filter
  2. Select More Sort Options
  3. Select More Options (Bottom Left)
  4. Uncheck Sort Automatically every time the report is updated
  5. Ensure First key sort order = No Calculation
  6. Refresh Pivot Table

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

That's good to know too, thanks! Unfortunately, that breaks the automatic sorting when the file is opened and the PivotTable is refreshed from dynamic data, so it would force my customer to have to remember to do a sort. It seems ridiculous that Excel doesn't honor the source data being formatted as Text... sigh. You'd think they would at least let me specify that at the field level without breaking the other features!
2

That's because Excel thinks SEP and DEC are months names and sorts them according to Custom Lists. To prevent this, you need to go to PivotTable -> Options and uncheck Use custom Lists when sorting in the Totals & Filters tab

enter image description here

Improve this answer

1 Comment

That definitely worked... thanks! I guess I'd better read up on Custom Lists. :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.