How to sort Microsoft Excel Tables by double clicking on any cell
One of the most basic techniques in data analysis is sorting. And for sure it is one of the most helpful. If you are exploring a large data set for the very first time, I am pretty sure, sorting the data by certain fields is one of the first things you do.
Very often, you do one sort after the other. Let’s say you are looking at a large table of several hundreds of products and all their performance measures, like the unit price, the margin, the order quantities, etc. If you want to get an overview of the product portfolio, what do you need to know in the first step? The most expensive and the cheapest products, the products with the highest margins, the best selling products, etc.
So, what do you do? Sorting, again and again, right?
There is no data analysis or BI tool that doesn’t provide a sorting functionality. However, is it easy and fast to access? And even if it is? Couldn’t it be faster?
Today’s article provides a technique to sort Microsoft Excel tables by simply double clicking on a cell of the data table. As usual, including the workbook for free download.
Agreed, the time savings are humble, but give it a try. I guarantee, chances are high that you will love the feature.
The Idea
Sure, Microsoft Excel has a fast and powerful built-in sorting feature. Of course it has. However, you have to go to the home tab, click on Sort & Filter and select the sort order (or go to the data tab and click on one of the sort buttons). Mouse movements and up to three clicks necessary.
There is a nice option to build your own sorting procedure, even without VBA: If you want to provide a more user-friendly way of sorting (e.g. on a dashboard), I recommend having a look at part 2 of the KPI dashboard series on Chandoo’s blog. No VBA necessary, only some Excel formulas (SMALL, MATCH, INDEX) and radio buttons.
But what if you want to have a quick and intuitive way of sorting without
- a lot of formulas and time / effort needed to set this up
and without - spending some extra time for moving the mouse and clicking?
How about simply double clicking into the data table? Wouldn’t that be nice?
The Functionality
Well, not much to explain here. For the sake of completeness:
- Double clicking on any cell sorts the table by this column.
- If the column is not yet sorted, the default sort order is descending for numeric data and ascending for text.
- Double clicking again toggles the sort order.
- The sorted column is highlighted with a light grey fill color.
The Implementation
To trigger the sort procedure with every double click on the data table, we implement the code within the event driven standard sub Worksheet_BeforeDoubleClick of the sheet module containing the data table.
Here is the VBA procedure in pseudo code:
- Exit the sub if the user double clicked outside of the data table
- Define the data structure (ranges of the table and of the active column)
- Check if the active column contains numbers or alphanumeric data using the VBA function IsNumeric
- Check if the column is sorted and detect the existing sort order
- Use the worksheet function AND and a greater or equal than / lesser than or equal sign to create strings with array formulas for sort order checking (see Chandoo’s excellent article Using Array Formulas to check if a list is sorted for a detailed explanation of this array formula)
- Calculate the result of the array formula using the VBA function Evaluate
- Define the sort order (descending or ascending) depending on the existing sort order
- Sort the table by the active column in the detected sort order
- Set the fill color of the sorted column to a light grey (optional step)
It is a pretty lean VBA sub, only 50 lines of code (excluding empty lines and comments). If you are interested in the details, download the workbook and have a look.
A Variation
You may find it more intuitive to sort the columns only by double clicking on the headers of the columns, instead of sorting the table after a double click on any cell. If you prefer this option, simply replace the following line of code:
If Application.Intersect(ActiveCell, Range("myData").Cells) Is Nothing Then Exit Sub
by
If Application.Intersect( _
ActiveCell, Range("myData").Cells.Resize(1, Range("myData").Columns.Count)) _
Is Nothing Then Exit Sub
That’s all.
The Performance
I implemented and tested the VBA code using an extract of an example data set of the FIFA World Cup results, which I already used for a Tableau post here: FIFA World Cup Statistics with Tableau. The data has only 361 rows and I was not sure how the performance would be with a larger table.
Thus, I created a data set of 65,000 records and 30 columns using random numbers and texts and tested the workbook. The performance is still pretty good. On my machine, the sorting takes one or two seconds. Still acceptable, I think.
Download
Here is the Microsoft Excel workbook for free download:
Download Sort Excel Tables by Double Clicking (Microsoft Excel 2003, 97K)
How to use this in your own workbook
You need only 2 simple steps to transfer this feature to your own workbook:
- Assign the range name “myData” to your table (including the headers of the columns).
- Go to the VBE (ALT-F11) and copy and paste the code from the workbook provided above into the sheet module of the worksheet containing the data table.
Easy, isn’t it?
Enjoy…
…and stay tuned.
Leave a Reply to Neil Cancel reply