Select the rows first: drag across the row numbers for adjacent rows, or hold Ctrl and click the row numbers for separate ones. To highlight rows automatically by a condition, use Home > Conditional Formatting > New Rule with a formula that returns TRUE or FALSE. Your choice depends on whether you pick rows by hand or by rule.
"Highlight" can mean two different jobs in Excel. You might want several rows selected at once so you can work on them together, or you might want certain rows to light up on their own when they meet a condition. Both are covered below, using Microsoft's pages on how to select cell contents and how to use conditional formatting.
Selecting several rows by hand
- For one whole row, click its row number.
- Alternatively, click a cell in the row and press Shift + Space.
- For adjacent rows, drag across the row headings.
- For separate rows, hold Ctrl and click each row number.
One more shortcut: select a first cell and press Ctrl+Shift+Arrow, and the selection stretches along the row to the last used cell.
If nothing will select, check whether the sheet is protected, because a protected worksheet may stop you selecting cells or their contents.
Highlighting rows automatically with a rule
Let the data decide which rows stand out. A conditional formatting rule does that job for you.
- Select the cells the rule should cover.
- On the Home tab, choose Conditional Formatting, then New Rule.
- Pick the formula-based rule type.
- Write a formula beginning with =, giving TRUE or FALSE.
- Select Format, then choose number, font, border or fill.
The catch with formulas
A rule formula has to start with an equal sign and has to give back TRUE or FALSE. If the formula is invalid, no formatting is applied at all, and Excel quietly colours nothing. When a rule seems to do nothing, check the formula before anything else.
Two quick checks catch most problems. First, look at the very start of the formula and make sure the equal sign is there. Second, ask yourself whether the formula really gives TRUE or FALSE for the rows you expect to light up. A formula that never comes out TRUE will never colour a row, however neat the rest of the rule looks.
It also pays to test a new rule on a small block of rows before you apply it to a whole list. If the right rows light up there, you can be confident the formula is behaving.
Which selection method suits which job
- Click the row number: quickest for a single row.
- Drag across the row headings: best for a block of neighbouring rows.
- Hold Ctrl and click: the only way to pick rows that aren't touching.
- Shift + Space: handy when your hands are already on the keyboard.
None of these changes how the rows look. They only select them, which is the first half of the job. Once they're selected, you can apply whatever change you like to all of them together, and that's what makes selecting several rows at once so useful.
Picking the right route
| You want to | Use |
|---|---|
| Work on several rows together | Selection by row number, dragging or holding Ctrl |
| Have rows light up when a value qualifies | A conditional formatting rule with a formula |
A worked example: flagging rows that reach a target
Suppose you've got a list where column C holds a figure and you want every qualifying row to stand out. Select the cells in the list, start a New Rule and choose the formula option. Write a formula that returns TRUE for the rows you care about, then use Format to choose a fill.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
From then on, any row whose figure meets the condition lights up by itself, and rows that stop qualifying go back to normal. That's the advantage over highlighting by hand, where you'd have to redo the job every time the data changed, and it keeps the sheet honest when figures are updated later.
For finding repeats, there's a dedicated guide on how to highlight duplicates in Excel, and if you're tidying rows afterwards you may need to unhide rows or group rows.
Frequently asked questions
How do I pick rows that aren't touching?
Hold Ctrl and click each row number in turn.
Is there a keyboard shortcut for a row?
Click any cell in it, then press Shift + Space.
Why does my rule colour nothing?
An invalid formula applies no formatting at all.
Why can't I select anything?
A protected worksheet may stop you selecting cells or their contents.
Where do I choose the colour for a rule?
Under Format in the rule dialog, by picking a fill.
What does Ctrl+Shift+Arrow do?
It stretches the selection from a chosen cell along the row to the last used cell.
