Excel conditional formatting, validation and Tables: rules as formulas

The idea

Three features in this chapter look unrelated: highlighting cells, filtering rows and validating what people type. They share one mechanism. Each one is a formula that Excel evaluates for a single row or cell and that answers TRUE or FALSE. TRUE means "apply the colour", "keep the row" or "accept the entry".

Once you see that, the dialogs lose their mystery. Greater than, below, above average, "Use a formula to determine which cells to format": they are all =E2>500 with a friendlier label.

Figure 1

One rule per column, one TRUE or FALSE per row

lines
row	line_total	over_500	under_200	above_average
2	378.00	FALSE	FALSE	FALSE
3	107.98	FALSE	TRUE	FALSE
4	1137.00	TRUE	FALSE	TRUE

Step 1 of 4 The three lines of order 1049 in E2:E4. A conditional formatting rule needs a question it can ask of each cell. The question is a formula.

The three lines of order 1049 in E2:E4. A conditional formatting rule needs a question it can ask of each cell. The question is a formula.
=E2>500 asks "is this line total above 500?". Only 1137.00 answers TRUE, so only that cell would turn green.
=E2<200 is the red rule. Only 107.98 answers TRUE.
=E2>AVERAGE($E$2:$E$4) compares each line with the average of the whole column, 540.99. The average range is locked; the tested cell is not.

Why it exists

A sheet that other people type into and read needs rules, because nobody scans four thousand rows by eye. Colour does the scanning. A filter shrinks the list to what you are asking about. Validation stops the bad value at the door, which is cheaper than finding it in a report.

The reason to write them as formulas, and not click through presets, is that you can test a formula. Put the rule in a spare column first and fill it down: a column of TRUE and FALSE shows exactly which rows will light up, and you can check three of them by hand before you commit to the rule.

How it works under the hood

Write the rule for the top-left cell

When you set a conditional formatting rule or a validation rule over a block, Excel evaluates your formula once per cell, shifting relative references as it goes, exactly like fill-down. So the formula is always written as if for the first cell of the range. Select E2:E38, write =E2>500, and Excel tests E3>500 for row 3 and so on.

The chapter-one rules apply: relative parts follow the cell, $ freezes what must stay. =E2>AVERAGE($E$2:$E$38) compares each cell with the same fixed range. A "use a formula" rule across a whole grid often needs mixed references, such as highlighting the whole row when one column meets a condition: =$E2>500 freezes the column and lets the row move, so every cell of the row gets the same answer.

Filters and sorts on a criteria column

A filter keeps rows where a condition is TRUE, and hides the others, without changing them. A reliable way to build a filter you can check is a helper column holding the condition as a formula, then filtering on TRUE. For a sort, the dialog sorts in the order of the levels you list, so "by category, then by revenue" means revenue only breaks ties within each category. Sorting a single column by itself scrambles the rows, so select the whole block, or work in a Table.

Data types decide which rule applies

Before you write a rule, ask what kind of column it is. Numbers can be discrete (counts) or continuous (prices); categories can be nominal (channel) or ordinal (small, medium, large, which have an order); and some columns, like order_id, look like numbers but are labels. Averaging an id is meaningless, and sorting a nominal category alphabetically implies an order that is not there.

Validation is a gate with holes

Validation runs when someone types into the cell.

Figure 2

Data validation runs the rule before the cell accepts a value

C5 = 13.99 someone types a price
AND(ISNUMBER(C5), C5>=K2, C5<=K3) the rule, TRUE or FALSE
TRUE the value is stored
FALSE an alert appears, the cell keeps its old value

Step 1 of 4 A product import sheet. Someone types 13.99 in the price column. Before the cell stores it, Excel checks the rule you set under Data → Data Validation → Custom.

A product import sheet. Someone types 13.99 in the price column. Before the cell stores it, Excel checks the rule you set under Data → Data Validation → Custom.
The rule is a formula that must be TRUE for the value to stay: it is a number, at least the minimum in K2 and at most the maximum in K3.
13.99 passes all three tests, so the rule returns TRUE and the value is accepted.
Type free or -5 and a test fails. The rule returns FALSE and Excel shows the alert you wrote. Validation guards new typing; it does not fix values that were already in the cell.

It does not check what is pasted over the cell, and it does not retroactively fix old data. So treat it as the first line of defence and add a column that applies the same rules to every row as a TRUE/FALSE audit. Rows that fail are the rows a validated form would have refused.

Tables make ranges grow

A plain range is a fixed rectangle. A Table is a range Excel keeps track of: it has a name, header row and automatic formatting, and it grows when you type under it. Formulas can refer to columns by name (Lines[line_total]), so they cover every row, including those added later. Conditional formatting, validation and formulas that you set on a Table column extend to new rows automatically.

Figure 3

A new line arrives under the data

totals
kind	formula	covers	shows
range	=SUM(D2:D4)	rows 2 to 4	1622.98
table	=SUM(Lines[line_total])	every row of the table	1641.87

Step 1 of 3 Order 1049 has three lines in rows 2 to 4. A plain-range total, =SUM(D2:D4), is correct: 1622.98.

Order 1049 has three lines in rows 2 to 4. A plain-range total, =SUM(D2:D4), is correct: 1622.98.
A fourth line of 18.89 is typed in row 5. The range formula is fixed to its corners, so it still covers rows 2 to 4 and still says 1622.98.
Format the data as a Table (Insert → Table) and name it Lines. The formula points at the column by name, Lines[line_total], and the Table grows to row 5 when you type there. The total is 1641.87.

Slicers are buttons that filter a Table. They are a nicer way to run the filter you already understand.

Checking a rule before you trust it

Run the rule over a few rows you can judge by eye: one that clearly passes, one that clearly fails and one sitting exactly on the boundary. The boundary row is where rules go wrong. Does "between 5 and 500" allow 500? >= and > give different answers there, and the person who set the rule needs to know which one the business meant. Write the decision down next to the threshold cell, so the next person does not have to guess. Keep thresholds in their own cells too, locked with $, so changing the rule means changing one number and not editing the formula.

Common misconceptions

"Conditional formatting changes the data"

It changes only how a cell looks. A red cell holds the same value. To count or sum the highlighted cells you need the same condition written as a formula, with COUNTIF or a helper column, because functions cannot see colours.

"Validation guarantees clean data"

It stops mistakes typed into the cell. Pasted values, imported files and anything entered before the rule existed bypass it. Audit with a formula column and treat validation as a courtesy to the person typing.

"A Table is just a styled range"

The styling is incidental. What matters is that the Table knows its own boundaries. Formulas that reference it by name stay correct as rows arrive and leave. That one property removes the "my total missed the last row" bug.

Check yourself

One question. Answering it ticks this lesson off on the map; it never locks a level.

A data validation rule on C5:C100 is =AND(ISNUMBER(C5),C5>0). A user pastes the text abc into C7, and another types -5 into C8. What happens? Typing -5 is refused. Pasting abc may slip through, because pasting can bypass validation Both are refused, because validation checks every way a value can reach the cell Both are accepted, because the rule only highlights cells and never blocks them Typing -5 is accepted, because the rule only runs on cells that already contain a number