Sorting (and beyond!)

By Bob Umlas, Excel MVP

In this blog, I’ll discuss sorting, likely beyond features you knew were available! We’ll start with this screenshot of a file:

It needs sorting. There are many ways to sort data.

There are 3 ways to access sorting from the Data tab:

1–

2–You can right-mouse click on a cell and select Sort:

and this leads to another flyout with more options:

3–Then there are the sort options from the Home tab, way over on the right side (and includes options for filtering, at the bottom half):

Let’s look at these different options.

The A-Z from the Data tab will sort the contiguous range of cells based on the active cell. So, if I wanted to sort the original screenshot by the first column, Country, all I need do is click in any one cell in that column and click the A-Z tool (or select the entire contiguous range of cells), and the beginning of the data would look like this:

Had I clicked the Z-A button, it’d look like this (I’ve scrolled down a bit so you could see then change in column A):

You can see that row 143 has a different country, UK, and that’s being sorted in reverse order alphabetically, after the US. Canada shows up in row 238:

(By the way, sorting is case insensitive, but there’s an option to be case sensitive which I’ll show later).

OK, here’s the first trick I’m going to assume you didn’t know: If you wanted to sort the region within the country, you could simply click one cell somewhere in column B, click the A-Z tool (on the Data tab), click in column A, click the A-Z tool, and you get this:

The trick is to click in the reverse sequence of the order you want the columns sorted. Here, we wanted column A to be the major sort, then within column A, we want column B sorted. So, we click a cell in column B, A-Z tool, a cell in column A, A-Z tool. I’ll do it again, clicking a cell in column D then C then B then A, each time clicking the A-Z tool:

Cool?

OK, the next icon in the Data tab is also the main sorting tool access:

.

This brings up the following dialog:

Here are the various values:

 

The first option is to sort by Country, Region, Company, etc. Excel reads the headers in the first row. How did it know the first row was headers? Because of that checkbox  you see at the top right, “My data has headers.” If this is unchecked, you would see this dropdown instead:

.

OK, so if you wanted to sort Region within Company, you would select Country as the major sort, then you click on the Add level button at the top, then select Region from the dropdown:

You could continue this process for all the columns, and if you make a mistake you could click the Delete Level button. The Copy Level does just that – copies the level which is the active one.  To see which level is “active” you can see the slight grey background behind it. In the above illustration, Region is the active level. You can use it as a starting point to then only change one item. For example, in the following dialog, the first level was copied to produce the second one, and now all that needs changing is the color in the Order dropdown. This is slightly easier than re-entering all the fields.

You can sort one level A to Z, and another Z to A, etc.

The third dropdown had an option for Custom List. Why choose a custom list to sort by? Well, if you have month names as text (as opposed to real dates formatted as text), then if I sort Jan thru Dec, I’d see this:

Alphabetical, but not very useful! So, you would do this:

Then the result is what you’d hope for:

Let’s take a closer look at the Sort On the dropdown, usually “Values”. As you may have noticed, you can also sort on Cell Color, Font Color, or Cell Icon.

Here’s the dialog for sorting cell color in column B and then font color in column D:

The Order column in the dialog shows all the various color possibilities that exist in the column:

Region:

Department:

Let’s sort the yellow region (cell color) then a red department (font color):

Or how about Yellow region followed by Brown region:

(Notice Region is there twice to accomplish this sort. Also, notice I could choose “On top” or “On Bottom”):

One more, then we’ll move on!

Same as above, but alphabetical for each as well, and then numerically, descending:

Which yields:

The sort by cell Icon is for when you used any symbols from the icon sets of conditional formatting. A quick example where I used conditional formatting of 3 icon sets in column E:

Now I can sort by the icon set:

Here’s the result after scrolling down a bit:

Okay. One more button we haven’t looked at, the Options button at the top. This brings up:

See? I told you we’d show you how to do a case-sensitive sort! There’s the checkbox at the top to do that.

Usually, you sort top to bottom, but you can sort left to right. Suppose I want to reorder these columns so that I have Company, then Region then Department. First, insert a row at the top, and enter the sort sequence you want to see!

I also need to select all the cells (pressing Ctrl/A) will do it) then bring up the sort left to right feature shown above from the Options button, and I’m presented with this dialog:

The sort by dropdown shows Row 1, Row 2, etc. I want to sort row 1 left to right (blanks sort to the bottom in a top-to-bottom sort or to the right in a left-to-right sort. Here’s the result:

Now I can delete row 1 and the columns are sorted as I wanted.

Last tip. Double spacing data. Or triple-spacing, or more!

Enter the value 1 in a parallel range in an empty column, and select the blank cell to the right as well:

Now double-click the fill handle (dark square in the bottom right corner of the selection):

Press Ctrl/c (copy), then ctrl/down arrow (takes you to last value), then down arrow (takes you to the empty cell below the last value), then ctrl/V (paste), and here’s the bottom of the data I was using:

Now you have 2 rows with a 1, two with a 2, etc. Click on one of these cells in column F and click the A-Z tool and presto!:

It’s double spaced! The blank rows all got sorted into place. Clear column F and you’re all set!

You can do this left-to-right as well. Here is a sequence of screenshots as a sample to insert 2 columns between each column (then we’re done!):

Copy thru 1 thru 5 and paste twice:

Bring up the left-to-right sort options then choose to sort on row 1:

Then delete row 1 and you’re done!!

Happy sorting!

About the Author
Bob Umlas has been using Excel since its inception in 1986. He has had more than 300 articles published on subjects ranging from beginner to advanced, VBA, tips, shortcuts, and general techniques using virtually all aspects of Excel. He is the longest-running Microsoft Excel MVP in the world (25 years). He has been the technical editor of (about 20) of Bill Jelen’s (MrExcel) Excel books. Bob speaks at Excel conferences around the world on his favorite topic, Tips & Tricks, which wows even the experts. He is the author of 3 Excel books and is the current leader of NYC’s Excel Special Interest Group (which meets on the 2nd Tuesday of each month).

Books:
This isn’t Excel, it’s Magic!
Excel Outside the Box
More Excel Outside the Box


From Googlewhack to Gazillion!

By Judith W. Umlas | IIL Senior Vice President, Author, and Facilitator

When McGraw-Hill first decided to publish my second book (the first was The Power of Acknowledgment, published by IIL Publishing), I thought it should be called Leadership and The Power of Acknowledgment. It felt right due to all of the positive experiences and wonderful stories that emerged training leaders in this initiative. “What would you think of a different title — Grateful Leadership?” asked my Editor.

Well, I didn’t have to THINK at all — I got a telltale case of half inch-high goosebumps at the suggestion, therefore I KNEW it was right. But then another thought hit me — that’s such a great title! There must be tons of articles and even books, websites, blog posts about that subject, I thought. I did a quick Google search and came up with what is known as a “Googlewhack”! What is that you may ask? It’s “exactly two words without quotation marks that returns exactly one hit”(according to Wikipedia). I couldn’t believe it!

This single “hit” was the 2010 article from the NASA CIO Blog by Linda Cureton called “Grateful Leadership.” It starts out this way: “As Thanksgiving approaches, this is the time of year when we reflect on the things that we want to be thankful for.”

I loved the thought behind this very well-written and inspiring article about how important it is for leaders to express their gratitude on this day, but my purpose was (and still is) to make every day a day of thanksgiving and gratitude.

That’s why I HAD to write this book. That’s why we have created the Center for Grateful Leadership. That’s why we have an unbelievably active and contributory community that is committed to the Grateful Leadership initiative, such as:

All of this is why IIL gives its total support to making Grateful Leadership training and resources available around the globe. That’s why I’m living my true purpose, my passion, and my mission as I write and speak and deliver keynotes to companies and groups of all sizes, industries and leadership levels – and in turn, helping to create more “Grateful Leaders.”

Take the exciting 7 hour Grateful Leadership On-demand course from IIL

I believe that Grateful Leaders can make huge changes in the very way people do their work and how they feel about what they’re doing. By my definition, Grateful Leaders are those who see, recognize, and express appreciation and gratitude for their employees’ and other stakeholders’ contributions and for their passionate engagement, on an ongoing basis… By creating a culture of appreciation throughout their organization, in which people truly feel valued, these leaders motivate their followers to strive for continuous improvement and always greater results. *

There is no doubt in my mind that what everyone wants underneath it all is “Grateful Leadership,” and it’s just my job to make it available to all of those I possibly can. I’m an honored “messenger” for helping spread this work in order to help create a world that works. And you who are reading this are part of that world. You, too, are a messenger carrying this valued message far and wide with me.

And oh, by the way, if you Google “Grateful Leadership” now, you will get this startling response: “About 97,400,000 results”! So I guess we have come quite a distance… and we still have far to go. So join me, and join each other. And I hope all of you have a precious, grateful time full of acknowledgment and appreciation with family and friends during the holidays…and every day!

*Excerpted from Grateful Leadership: Using the Power of Acknowledgment to Engage All Your People and Achieve Superior Results by Judith W. Umlas (McGraw-Hill, 2013).

Judith W. Umlas is Sr. Vice President and trainer at International Institute for Learning, Inc. She is the author of the ground-breaking book, The Power of Acknowledgment and two other books which have been credited with changing workplaces and lives.

Judith delivers inspiring, motivational and transformational keynote addresses, course and webinars on Grateful Leadership and The Power of Acknowledgment all over the world. Grateful Leadership and The Power of Acknowledgment are Judith’s passion, mission, and her purpose!