Excel Xpert · Lesson 124
Highlighting in Excel means three different things, and knowing which one you need saves a lot of frustration. There is manual fill color, there is conditional formatting that colors cells based on a rule, and there is an active cell highlight that follows your cursor so the row and column you are working in light up as you move. That last one is what the video covers, and it is the reason people can actually follow along when you share your screen.
Lesson 124 · 1 minute 22 seconds. The video walks through highlighting the active cell in Excel so its row and column are shaded as you move around the sheet, which makes it obvious where everyone should be looking during a screen share or a presentation. The written steps below cover the same setup in more detail, along with the other two kinds of highlighting and when each one is the right choice.
The same free Excel Xpert companion workbook used in the video, one tab per lesson. No login, no email required.
Fill color is the paint bucket on the Home tab. You select cells, you pick a color, and the color stays exactly where you put it. It is manual, permanent and completely indifferent to what the data does afterwards, which is fine for labeling a header row and a genuine problem for marking overdue items that stop being overdue.
Conditional formatting is a rule rather than a color. You describe a condition, Excel checks every cell against it, and the formatting appears wherever the condition holds. When the numbers change the highlighting updates itself, which is the entire reason it exists.
Active cell highlighting is conditional formatting pointed at your cursor instead of your data. The rule asks which row and column are currently selected and shades them, so a crosshair follows you around the sheet. On a wide table where the labels are twelve columns to the left, it is the difference between an audience following along and an audience quietly lost.
Most confusion about highlighting comes from mixing the first two up. Someone applies conditional formatting, later tries to clear it with No Fill, and nothing happens, because No Fill removes a color and does nothing at all to a rule. That one distinction explains most of the highlighting questions people ask.
Six steps, building one formula. The table after step six shows exactly what you type at each stage and what it does to the sheet.
Click the top left cell of your data and select down and right across everything the crosshair should reach. Conditional formatting only exists inside the range it was created on, so a rule built on A1:H50 does nothing at all in row 51 when you add it next month.
Select more than you currently need. Going out to A1:Z200 on a table that fills A1:H50 costs nothing visually, because the rule only paints where the condition is true, and it saves editing the range in Manage Rules every time the data grows. What you should not do is select entire columns by clicking the headers, since a rule evaluating a million rows per column is exactly how a workbook becomes slow.
Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format, the last option in the list.
The preset options above it — Format all cells based on their values, Format only cells that contain — all test the contents of a cell. None of them can ask where your cursor is, which is why the formula option is the only route to an active cell highlight rather than a preference.
Whatever formula you write here gets evaluated once for every cell in the range, with the references shifting cell by cell, exactly as if you had typed it in the top left cell and filled it across and down. Holding that picture in your head makes step four obvious rather than mysterious.
CELL("row") returns the row number of the current selection, and CELL("col") returns its column number. Those are the only two pieces of information the whole technique needs.
Compare each of those to the cell being evaluated. ROW() and COLUMN() return the position of the cell the rule is currently testing, so =ROW()=CELL("row") is true along the active row and nowhere else, and =COLUMN()=CELL("col") is true down the active column.
Joining them with OR gives you the crosshair: =OR(ROW()=CELL("row"),COLUMN()=CELL("col")). Swapping OR for AND highlights only the single active cell, which is occasionally what you want on a very wide sheet where a full band is too much.
This is where nearly every attempt fails, and it is worth understanding rather than copying. Because the formula is re-evaluated for every cell with references shifting as it goes, a $ is what stops a piece of it from shifting.
ROW() and COLUMN() with empty brackets take no reference at all, so the version above needs no dollar signs and works as written. The moment you point at an actual cell, they matter enormously. A rule that shades a whole row when column D says Overdue is written =$D2="Overdue": the $ before D pins the test to column D while row 2 shifts down the sheet, so the rule is evaluated once per row and paints every cell across it.
Drop that dollar sign and each cell tests the column it happens to sit in, giving you scattered fragments instead of bands. Add one to the row as well, making it $D$2, and every cell in the range tests the same single cell, so the entire table colors or none of it does. One character, three completely different outcomes.
Click Format, go to the Fill tab and pick a pale tint. Light grey and light blue both work; anything saturated makes the numbers harder to read and looks considerably worse projected than it does on your monitor.
A crosshair covers a full row and column at once, which is a lot of surface area. That is the argument for going lighter than instinct suggests — the highlight is a pointer, not a status flag, and it competes with genuine conditional formatting already on the sheet if the two are similar strengths.
If you already use color for meaning, such as red for overdue, keep the active cell highlight in a neutral grey so nobody reads it as data.
Finish the rule and the highlight will look broken, because it sits still until you type something. This is not a mistake in the formula. Conditional formatting re-evaluates only when the sheet recalculates, and moving the cursor is not a recalculation, so CELL keeps reporting the position it saw at the last calc.
Pressing F9 refreshes it manually, which is enough to confirm the rule works. To make it automatic, right click the sheet tab, choose View Code, and add a Worksheet_SelectionChange event that calls Target.Calculate. Two lines, and it forces a recalculation every time the selection moves.
That turns the file into a macro enabled workbook, so it has to be saved as .xlsm. Save it as .xlsx and Excel drops the macro without much of a warning, and the highlight silently stops following the cursor the next time somebody opens it.
| Step | What You Type | What Happens on the Sheet |
|---|---|---|
| 1. Select range | A1:Z200 | Sets where the rule can ever paint |
| 2. New Rule | Use a formula to determine which cells to format | Opens a box that runs once per cell |
| 3a. Active row only | =ROW()=CELL("row") | A horizontal band across the cursor |
| 3b. Active column only | =COLUMN()=CELL("col") | A vertical band down the cursor |
| 3c. Both, the crosshair | =OR(ROW()=CELL("row"),COLUMN()=CELL("col")) | Row and column light up together |
| 3d. Active cell only | =AND(ROW()=CELL("row"),COLUMN()=CELL("col")) | One cell, no bands |
| 4. Lock for a row rule | =$D2="Overdue" | Whole row shades when D says Overdue |
| 4. Same rule unlocked | =D2="Overdue" | Scattered cells, the usual bug |
| 5. Format | Fill tab, pale grey or blue | Visible but still readable |
| 6. Refresh | Worksheet_SelectionChange calling Target.Calculate | Highlight follows the cursor, save as .xlsm |
Rows 3a to 3d are the same idea with one operator changed, and rows 4 are the same rule with one dollar sign moved. Almost every conditional formatting problem people hit is one of those two decisions made differently than intended, which is why it pays to build the rule in this order rather than pasting a finished formula and hoping.
The rules people reach for most, with the reference locking that makes them behave.
Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Note that it colors the first occurrence too, so a value appearing twice shades both cells. If you only want the repeats flagged, a COUNTIF rule counting from the top of the range down to the current row does that instead.
Select the whole table, then write the rule against one column with its letter locked, as in =$D2="Overdue". Start the row number at whatever the first row of your selection is, since the rule is written from the perspective of the top left cell and shifts from there.
Lock both parts of the comparison cell, as in =B2>$H$1, so every cell in the range is tested against that one threshold rather than a reference that drifts as the rule spreads out. Putting the threshold in a cell also means changing it re-colors the sheet instantly.
=ISBLANK(A1) exposes gaps during cleanup, =ISERROR(A1) surfaces broken formulas, and =$C2<TODAY() flags anything past due. Combining the last one with an AND that also checks status is what stops completed items from showing as overdue forever.
Where most highlighting questions actually come from.
Select the cells and choose No Fill from the Fill Color dropdown. This touches manual color only. If the color survives, it was never fill color to begin with.
Conditional Formatting > Clear Rules, then either the selected cells or the entire sheet. This is the only thing that removes a rule based highlight, and it is what people are looking for when they say the highlighting will not come off.
Conditional Formatting > Manage Rules, then switch the dropdown to This Worksheet rather than the default current selection, which is why people open it and see nothing. It lists every rule, its range, and the order it fires in.
Rules apply top down and later rules can override earlier ones. Ticking Stop If True on a rule prevents everything below it from evaluating for those cells, which is how you keep an overdue rule from being repainted by a general banding rule underneath it.
Copying and pasting rows brings their rules along, so ranges fragment into dozens of near identical rules like A2:A15, A16:A16, A17:A40. This is a leading cause of a sluggish workbook. Open Manage Rules occasionally, delete the fragments, and rebuild one rule over the full range.
No Fill removes a color, not a rule. If the highlight comes straight back or never leaves, it is conditional formatting and only Clear Rules will remove it.
A $ in front of the row number when you wanted a whole row shaded produces a vertical stripe instead. This single character is the difference between the rule working and looking broken.
Without a SelectionChange macro it only updates when the sheet recalculates, so it looks stuck. The rule is correct, the refresh is missing.
Excel drops the macro silently and the highlight stops following the cursor next time the file opens. It has to be saved as a macro enabled workbook.
A vivid fill makes the text hard to read and looks considerably worse projected than it does on your screen. Pale tints do the same job without fighting the data.
Copied rows bring their rules with them, and a sheet with dozens of fragmented overlapping rules gets noticeably slow. Check Manage Rules occasionally and consolidate.
Coloring cells is how a spreadsheet says something needs attention.
Free 14-day trial. No credit card required.
Select the cells and use the Fill Color bucket on the Home tab, or open Format Cells with Ctrl 1 and pick a fill on the Fill tab. That colors the cells permanently until you clear it.
Use conditional formatting with a formula based on the CELL function, so the rule evaluates true for the row and column you are sitting in. The highlight then follows the cursor around the sheet.
Conditional formatting only refreshes when the sheet recalculates, and moving the cursor is not a recalculation. Pressing F9 updates it, or a short worksheet SelectionChange macro refreshes it automatically.
Apply conditional formatting to the whole range with a formula that locks the column reference, such as testing $B2, so the rule evaluates once per row and colors every cell across it.
Select the range, then Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values. It flags every value appearing more than once, including the first occurrence.
Fill color is manual and stays where you put it even after the data changes. Conditional formatting is a rule that reapplies itself, so the highlighting stays correct as the numbers update.
For fill color, choose No Fill from the Fill Color dropdown. For conditional formatting, use Conditional Formatting, Clear Rules, since No Fill will not remove a rule based highlight.
It is almost certainly conditional formatting rather than fill color. A rule based highlight ignores No Fill entirely and only disappears when the rule itself is deleted or its condition stops being true.
There is no direct fill color shortcut, but Alt H H opens the fill color palette, and F4 repeats the last fill you applied on any newly selected cells.
Yes by default, and both fill color and conditional formatting print. Ticking Black and white under Page Setup, Sheet prints without the fills if you want a clean copy.
Excel allows a large number, but performance degrades well before the limit. Dozens of overlapping rules on large ranges are a common cause of a slow, laggy workbook.
Use conditional formatting with the Use a formula option and reference the other cell, locking the parts of the reference that should not shift as the rule is applied across the range.
Every business starts with a spreadsheet. Updoot is where you scale past it, with status and alerts built in instead of painted on.
Start Your Free Trial →