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


Reflection and Takeaways on Agility from the SMC-IT 2018 Space Mission Design conference

By Tom Friend - Agile Consultant / LtCol USAF (Ret)
CSP, ACP, AHF, FLEX, CSM, PSM, ATP

The 6th International Conference on Space Mission Challenges for Information Technology, held in Alcala de Henares, Spain, brought together scientists, engineers, and researchers from NASA, the European Space Agency, universities and industry.

Case studies on how agile methodologies have been applied to mission planning and how scrum has been used in spacecraft construction were discussed, as well as topics such as developing and delivering software, reliability and reuse of software, onboard processing, and communication.

Representing Scrum, Inc. as a keynote speaker, I opened the conference with “Scrum to the Stars” which looked back into aviation history and to the future of innovation in aerospace, and how Scrum methodologies have been, and will continue to be effective tools.

Iterative discovery has been at the core of aviation exploration since the dawn of flight. Whether it was the first aeronauts in balloons, or the Wright brothers at Kitty Hawk, explorers of flight used processes that built on incremental failures and successes. Aerospace design processes were modified as improvements to flight technology were discovered, and the knowledge base expanded. Empiricism and Incremental improvement evolved as a standard path to improvement.  This standard path emerges as patterns.  For example Interfaces in small satellites are deliberately over-designed to reduce need for disruptive renegotiation.  The pattern of S\simple pre-negotiated physical bus structure for data and power increase design versatility, and loose production coupling.  One of the most significant scructural patterns is that of standard adapters allows objects with incompatible interfaces to work together by wrapping its own interface around that of an already existing interface.  These are just some of the patterns that when combined defines the evolving path to improvement.

In essence, Scrum was there at the start of aerospace exploration. Over the years, as systems have increased in size and complexity, common sense has been lost, and projects hit overruns in both time and money spent. By utilizing an Agile framework, you can break down these complex systems into smaller pieces that can then be integrated into the whole design. The step-by-step, incremental approach can be an effective time and cost management tool.

Today the trend in space exploration is making small satellites. Frequently, these small satellites are part of a larger mission.  In doing this, risk is reduced by breaking a complex mission into parts and delivering it in smaller submission components. Think of it as component architecture with your software systems, same pattern. The end deliverable: small satellites that are tailored to a particular mission.

This approach complements Agile planning where focus is on delivering small increments of value and dedicated Scrum teams to build and deliver the satellites.   The success and low cost of small satellites with focused space missions is now mainstream with a standard type of microsatellite called, “CubeSat” that follows set size and weight requirements. This standard is a simple 6-page document keeping with the Agile tradition of minimum viable documentation.

CubeSats by necessity have evolved to leverage many Scrum in Hardware Patterns to speed development and reduce costs. This conformance to patterns has created a whole cottage industry of commercial off the shelf (COTS) suppliers.  They provide hardware and software systems and components that can be used together like LEGOs because they have standard power, size, bussing, and know stable interfaces that allow them to be configured quickly and with low expense.

One of my favorite ways to demonstrate how effective Scrum can be in a hardware setting is a class I give using the CubeSat format. This class is generally offered in a 6-hour format, and is very hands on. In this course, we build a 3D paper CubeSat with a specific mission. All the steps from mission design, roadmap, and components are broken down into a backlog and worked by a scrum team to deliver a fully functional model.  We then walk through the launch and operation of the CubeSat, discussing what each component is doing as it circles the table in the middle of the room that represents Earth.

This simple class exercise using scrum to build components and the visualization of talking through a mission shows how prototyping lets you see problems with design early and builds shared understanding on the team.  These are lessons that you can take back to your own teams to make them even better.


About the Author

Tom Friend is an accomplished Agile consultant, trainer, and coach with 23 years’ experience leading software development teams in various industries to include federal, banking, cable, telecommunications, and energy. He has 12 years of hands on Agile / XP / Scrum software development experience.  He is a distinguished graduate from Air War College and has a BS in Aeronautics.