Contextures

Contextures News

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.

Pivot Table Sorting

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.

  • Advantage: You can drag items to a different position on the worksheet, so it's easy to put important items at the top of the list.
  • Disadvantage: Alphabetical order isn't maintained for new items.

Fortunately, there is an easy fix:

  • Right-click one of the items in the pivot field. In the popup menu, click Sort A to Z.
  • Later, if you want to go back to Manual sort, click the More Sort Options command

You can read more about pivot table sorting on my website.

Hide Columns Before Printing

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.

Excel Articles

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)

More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog. And for a bit of humour, read my weekly collection of Excel tweets.

Excel Training

  • Data Analysis: Chandoo's new course, 50 Ways to Analyze Your Data, will start tomorrow, Feb. 24th. It's not for everyone though, you need at least intermediate or higher level of Excel knowledge. To get all the details, click here.
  • Free Webinars: Ends Thursday - Mynda Treacy is offering two free one-hour webinars -- 1) How to Build Excel Dashboards, and 2) Dashboards with Power Query and Power Pivot. Get the details, and sign up for a date and time that is convenient for you.
  • Excel Macros: Registration closes this Thursday (Feb 25th) for Jon Acampora's VBA Pro Course.

Cold War

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:

http://www.contextures.com/newsletter/excelnews2016/20160223ctx.html

Recommended Excel Products

________________________

Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.

 

 

 

Search Contextures Sites

 

30 Excel Functions in 30 Days

 

Excel Data Entry Popup List

 

 

 

 

Last updated: June 14, 2017 11:45 PM