Inconsistent pivot table format at sorting behavior.

[dmanww]

I ran into a very annoying issue with a PowerPivot and it's driving me nuts.

- I set it up using "flattened pivot" from the datamodel menu.
- The layout is about 15 field rows and 1 value.
- The first field is set up to insert a blank row between each group.
-The others are normal.
- There are not subtotals or grand totals.

- There is a slicer on a different tab that controls several pivots along with this one.

Here's the issue
- When I set up the pivot yesterday I was able to format the cells in each field (selecting title so group is highlighted. Text alignment, borders, etc.
- Also set up sort order of several groups by dragging sets.
- When I filtered using the slicer the formats were retained.

- When I tried to add a column today it would retain formating.
- Did the same process, but when the filters were applied with the slicer the format got applied to random rows for the colum.
- When I returned the filter to the original setting the formating was still messed up.

- Tried recreating the pivot from scratch using flattened pivot again in the same file
- Now the formats seem to be ok, but the manual sort order isn't retained.
- Basically trying to sort RAG statuses manually, but if I apply the filter and R disappears. When the filter is changed R now shows up at the bottom as if it's a new value that didn't exist before.

Both these pivots exist in the same file and seem to have different behavior with formats and ordering.

Couple ideas I had.
- I protected the sheets after I finished editing yesterday, but I unprotected all sheets before I started making changes today. I know having even on sheet protected in the file can cause issues with making changes to pivot styles.
- Checked that retain formating is on
- Checked that sorting settings are the same between the two pivots

Beyond that I have no idea what else to check.

 

[denzo36]

A manual sort will be lost often times, when refreshing or changing pivot tables.

Hard to see exactly what you're doing with seeing your data but one thing I use is letters in the field I'm trying to sort that you can then use to sort A to Z to which will always preserve the sort order.

E.g. say you have categories of values in a field you want to sort e.g 0 to 1,000, 1,001 to 2,000, 2,001 to 3000 etc etc. When you drop this field into a pivot table, it won't necessarily sort them in the correct order lowest to highest so to force it to, I go back to the source data and relabel the categories as such:

a. 0 to 1,000
b. 1,001 to 2,000
c. 2,001 to 3000
d. 3,001 to 4,000
etc etc

Adding the letter at the start means you can then sort the field A to Z and it will sort in whatever way you want it.

You could also add a custom list in excel for the field you want to sort correct and within the pivot table there is an option to sort by custom list which would also work