Automated Data Column
Sorting in Excel
Single-Column Sorting Automated With Formulas in Excel
A single column of numeric data can be sorted by using formulas or with the Data Sorting Tool. Using formulas is a much better solution because data sorted by formulas will be automatically resorted if the data is changed or new data is added. The Data Sorting Tool, just like all of the other Data and Data Analysis ToolPak Tools, must be re-run manually when a new sort is required. Unlike Data Tools, formulas automatically recalculate their output when their inputs are changed.
Descending Sort Using Formulas in Excel
The following image shows the implementation of a descending sort using formulas in Excel.
The formula can be typed into the first cell and then copied all the way down, in this case, to cell D200.
The LARGE() formula is explained as follows:
LARGE(data range, k) returns the kth largest value in the data range. The position count, starting at 1 at the top, is implemented by the following:
ROW() is the number of the current row. If this formula is in cell D4, ROW() = 4 since cell D4 is in the 4th row.
ROW(D3) = 3 since cell D3 is in the 3rd row.
In this case, ROW()-ROW($D$3) = 4 – 3 = 1
If the formula is in cell D5, ROW()-ROW($D$3) = 5 – 3 = 2
Note that the data range in the formula extends from cell B4 all the way down to cell B200. Empty cells in this range are ignored by the sort because of the If-Then-Else statement.
Ascending Sort Using Formulas in Excel
A descending sort can be converted into an ascending sort by substituting the word SMALL for LARGE in the formula as follows:
New or Changing Data
The advantage of sorting with formulas instead of the Data Sorting Tool is the formulas will automatically resort the data if any data is changed or additional data is added. The following changes were made to the data:
-6 was changed to -7 (colored orange)
New data points 11, 13, and 14 were added (colored light blue)
The data is automatically resorted after these changes and additions. The Data Analysis Sorting Tool would have to be re-run manually to resort the data.
Multi-Column Sorting With the Data Sorting Tool in Excel
Multiple columns of data that require secondary or tertiary sorts require the use of the Data Sorting Tool. Alphabetic data can also be sorted using this tool. Below are 3 columns of data that will be sorted using a primary sort of Column 3, then a secondary sort of Column 2, and finally a tertiary sort of Column 1.
The first step is to highlight the data including the column headers if they are there as follows:
The Data Sorting Tool is found under the Data tab as follows:
Clicking on Sort brings up the following dialogue box. The column headers show up because they were highlighted with the original data in Step 1.
The following completed dialogue is set up to perform a primary sort of Column 3, then a secondary sort of Column 2, and finally a tertiary sort of Column 1.
Clicking OK performs that sort as follows:
Excel Master Series Blog Directory
You Will Become an Excel Statistical Master!