Excel has the ability to sort data in a variety of ways:

**Alphabetically****Numerically****Chronologically**

In addition, the data can be sorted in either **ascending** (smallest to largest) or **descending** (largest to smallest) order.

Sorted lists are useful when printing or for scanning when looking for information. It is far easier to locate an item in a sorted list than if the list was seemingly random. Imagine trying to find a person’s name in a list that has been sorted by employee number. Assuming your company doesn’t hire people in alphabetical order, this will prove difficult.

Sorted lists will dramatically improve search times in functions like **VLOOKUP** (see previous post “Supercharge Your VLOOKUP Functions in Excel”.) The risk is that if a user were to sort a list being scanned by a VLOOKUP, and the VLOOKUP was NOT utilizing “*exact matching*” logic, incorrect information can easily be returned. If we trust our formulas to work properly, we may not realize we have been given erroneous data.

If you want to keep tabs on the sort order of a list, we can do this with an extremely simple formula and a few tricks to spice up the visuals. Our solution will utilize four key Excel features:

**Array Functions****Conditional Logic***(optional)***Conditional Formatting***(optional)***Custom Number Formatting***(optional)*

It sounds like it’s going to be a lot of work, but once you see it working, it’s actually quite easy and quick to implement. Some of the above mentioned features are not necessary to solve the immediate problem of being alerted to an unsorted list, but they are included so you can add a few more tricks to your Excel toolbox.

### Example of Things Going Wrong

Consider the below list:

If we were to use this list in a **VLOOKUP**, and we were not using “*exact match*” logic, we would have to ensure that the list were never to be sorted by any column other than the **Product Name** column.

Example: Cell **B13** contains a **VLOOKUP** function that takes the user input from cell **A13**, finds the item in the **Product Name** column, and then returns a price from the **Price** column of the list.

**=VLOOKUP(A13,A2:A10,2)**

This **VLOOKUP** is not using “*exact match*” logic, but since the list is sorted by **Product Name**, the returned **Price** is correct *(and super fast)*.

Suppose someone were to sort the list so the items were listed in **ascending** order by **Price**? Would the **VLOOKUP** function’s answer be correct? The answer is a “*No*”.

What we want is to be alerted for when this list changes from “*sorted ascending by product*” to any other sort order.

### Step 1 – Determine the Sort Status

We will check the sort order of the **Product Name** column by writing the following array formula.

**CAUTION**: If you have never created an array formula, you MUST enter the formula with a **CTRL-SHIFT-ENTER** key combination. A traditional **ENTER** key will not build the formula properly.

**{=AND((A2:A9)<=(A3:A10))}** *(do not type the beginning and ending braces; they will be created when you press the CTRL-SHIFT-ENTER key combination.)*

So what exactly is this formula doing? It’s actually quite simple, and that’s where the elegance lies.

The formula takes a list of products from **A2:A9** and compares it to a list of products from **A3:A10**.

The formula checks to see if each item in the first list is less than or equal to the corresponding item in the second list. Because this was constructed as an array function, the actual process looks something like this:

**A2<=A3**

**A3<=A4**

**A4<=A5**

**A5<=A6**

etc…

The list of results would appear as follows:

**True**

**True**

**True**

**True**

etc…

Because we are placing all of the results under the control of an **AND** function, if all of the individual tests result in **TRUE**, the entire test is true and is therefore a sorted list. If even one test were to result in a **FALSE** response, the entire test would fail resulting in a final **FALSE** answer.

You *COULD* stop right here and go home, satisfied with a job well done… but let’s jazz it up a bit.

### Step 2 – Conditional Logic to Display a Status Message

If you want to have message displaying the status of the list’s sort order, you could write an **IF** formula like the following: __(Remember – it’s an ARRAY formula)__

**{=IF(AND((A4:A11)<=(A5:A12)),”List is sorted properly”,”WARNING – List is NOT sorted”)}**

### Step 3 – Use Conditional Formatting to Apply Fill Color to Message

There are a variety of ways you could apply conditional formatting to the message.

*If A1 is equal to “List is sorted properly” then fill color equals green**If A1 is equal to “WARNING – List is not sorted” then fill color equals red*

Just to have a bit more fun, and learn a few more tricks, let’s do it a bit differently.

We have entered the array formula for the **IF** messages in cell **A1**. Let’s enter the below formula in cell **B1**.

**{=IF(AND((A4:A11)<=(A5:A12)),1,0)}**

This will return a “**1**” if the list is sorted and a “**0**” if the list is unsorted. We will treat the “**1**” as “**True**” and the “**0**” as “**False**”.

Select cells **A1:B1** and color the cells using a white font color with a green fill color. This will serve as the default “all is well” color scheme.

Using **Conditional Formatting**, select cells **A1:B1** and create a __new rule using a formula__ to format the cells. Enter the formula below and set your colors accordingly *(such as bold, white text on a dark red background.)*

**=$B$1=0**

Conditional Formatting colors always supersede manually applied colors. For more information on how colors are applied in Excel, see the post “Excel Color – Order of Application (Hierarchy)”.

### Step 4 – Making Data Invisible Using a Custom Number Format

The final trick is to hide the “**1**” or “**0**” displayed in cell **B1**. You could set the font color to match the fill color, but if the fill color changes, the number will reveal itself.

Select cell **B1** and open the **Format Cells** dialog box. On the **Number** tab, select the **Custom** category on the left and enter __ three semi-colons__ in the

**Type:**field.

If you are not familiar with custom number formatting, codes are broken into four presentation categories:

How we display

- positive numbers
- negative numbers
- zeroes
- text

Each set of codes is separated by a semi-colon. By typing the semi-colons **WITHOUT** any codes, we are saying “*don’t display ANYTHING*”. This will make anything typed into a cell invisible, regardless of font color or fill color settings.