Fix Pivot Sorting Problems
Feb 23, 2016
See how to fix pivot table sorting problems, Slicer trick, and more, in this week's Excel news.
- Debra - ddalgleish @ contextures.com
Visit my Excel website for many more tutorials and videos.
Do new items appear out of order, when you add them to a pivot table? Let's see why that happens, and how you can fix it.
When you first create a pivot table, all the items are shown in alphabetical order, and it's easy to find what you're looking for. However, if you add new items to the source data later, those items will appear at the end of the list, instead of being in alphabetical order. You might not even notice that they're in the list!
This happens because there are three pivot Sort settings (A-Z, Z-A, Manual), and the default sort setting is Manual.
Fortunately, there is an easy fix:
You can read more about pivot table sorting on my website.
Do you have worksheets in which you show and hide specific columns, over and over again? Maybe you hide calculation columns, every time you print, but you need to see those columns while working.
If you don't have any named Excel tables in the workbook, you can set up Custom Views (they're not allowed with tables), and use them to store hidden column settings. Add the Custom Views command to your Excel Ribbon, then just pick the view that you need.
If you can't use Custom Views, you could put an "X" in the first row, for any columns that should be hidden (leave the rest of that row empty). Then, use the Find & Select command to select the Constants, and hide the selected columns. If you do this frequently, you can record a macro, or add the Hide Columns command to your Quick Access Toolbar.
Here are a couple of Excel articles I read recently, that you might find useful.
Pivot Charts -- Jon Peltier explains everything you ever wanted to know about pivot charts, including how to copy a pivot chart, and connect it to a different pivot table. (Level - All)
Slicer Trick -- Jon Acampora shows a handy trick for creating a search box to use with a Slicer. This will make it much easier to find things, if the Slicer has a long list of items.. (Level - Intermediate)
To avoid the cold weather, I've been staying inside as much as possible. However, that didn't protect me from catching a cold, courtesy of my lovely little granddaughter. There is a big price to pay for those sweet hugs! She did wear gloves for our craft project though, so that marbled paper is germ-free.
Anyway, I'm following the age-old advice of drinking plenty of fluids (in my VLOOKUP mug), getting lots of rest (working in Excel is very restful, right?), and coating my poor, red nose with mentholated ointment. That should get rid of the cold in 7 days, instead of the full week that it would last without treatment. ;-) I hope you're staying healthy!
NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser:
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Last updated: May 9, 2018 8:58 AM