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()-ROW($D$3)

ROW() is the number of the current row. If this formula is in cell D4, ROW() = 4 since cell D4 is in the 4^th row.

ROW(D3) = 3 since cell D3 is in the 3^rd 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.

(Click On Image To See a Larger Version)

 

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:

(Click On Image To See a Larger Version)

 

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.

(Click On Image To See a Larger Version)

 

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.

(Click On Image To See a Larger Version)

The first step is to highlight the data including the column headers if they are there as follows:

(Click On Image To See a Larger Version)

The Data Sorting Tool is found under the Data tab as follows:

(Click On Image To See a Larger Version)

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.

(Click On Image To See a Larger Version)

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.

(Click On Image To See a Larger Version)

Clicking OK performs that sort as follows:

(Click On Image To See a Larger Version)

 

Excel Master Series Blog Directory

 

Click Here To See a List Of All

Statistical Topics And Articles In

This Blog

 

You Will Become an Excel Statistical Master!