Share via

Pivot Table Filter Not Working

Anonymous
2016-05-03T02:51:23+00:00

Why when I select to Filter a column of Numbers into Ascending Order the numbers don't change? I've noticed that if I amend the data source then on the pivot table the modified number "climbs" to the top of the list and still can't filter into ascending order? 

In pivot table options - Layout (tab) down the bottom there are "Sort" options which I have also played around with and still doesn't change what I am trying to do.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jim G 134K Reputation points MVP Volunteer Moderator
    2016-05-12T16:26:37+00:00

    That's not going to work.

    Use another column as a "helper" column on the Job Card worksheet. For example, use column J, which presumably has General format (the default).

    1. Turn Data > Filter Off
    2. Enter this formula =TEXT(A4,"0000") Then copy it down.
    3. Format column K as text.
    4. Select column J
    5. Copy column J
    6. In cell K1 use Paste Special > Values. This pastes the entire list of numbers as into the column, which is formatted as text so it keeps the leading zeros.
    7. Select column K
    8. Edit copy
    9. Right-click on Cell A1 and choose Insert Cut Cells. Your column A1 should now show the Cost center code and everything should have shifted over 1 column to the right
    10. Delete column B (formerly column A)
    11. Re-create your pivottable

    Your pivottable will now have the order you expect.

    Now that column A is formatted as text, additional entries will work fine.

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2016-05-03T20:31:41+00:00

    Could you provide us the screen shot and the step by step information to reproduce the issue.

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2016-05-11T00:44:01+00:00

    Jim,

    I've selected the A column and changed format to "Text". I have click the on filter on column A to "Ascending" with NO response ???

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  4. Jim G 134K Reputation points MVP Volunteer Moderator
    2016-05-05T15:52:50+00:00

    Is the Row Labels column formatted as number or as text?

    Was this answer helpful?

    0 comments
  5. Anonymous
    2016-05-04T00:56:19+00:00

    You can see that the Cost Centers beginning with "0" will not filter to top of the list ???

    Above is the Settings for this pivot table, thinking that the "Sort" option here would override Field filter settings but it doesn't do anything or change the filter order....

    Above is the Field Settings for "Cost Centre Code"...

    Hopefully this gives enough information for you to help ???

    Was this answer helpful?

    0 comments