Highlight cells equal to
Excel lets you easily highlight values based on a condition you specify, with a built-in formatting or a custom formatting.
How to apply Conditional Formatting to values equal a condition
- Select cell range containing values you want to highlight.
- Go to tab "Home" on the ribbon if you are not already there.
- Press with left mouse button on "Conditional formatting" button.
- Press with mouse on "Highlight Cells Rules".
- Press with left mouse button on "Equal to..."
- A dialog box appears that lets you specify the date condition and the formatting.
- Pick a prebuilt formatting or use a custom format to create a new one.
- Light red Fill with dark red text
- Yellow fill with dark yellow text
- Green Fill with dark green text
- Light red fill
- Red text
- Red border
- Custom format...
- Press with left mouse button on OK button.
Highlight rows equal to a condition
You need to use a formula instead of the prebuilt ones in order to highlight the entire row if a cell in column B meets the condition in cell F4.
- Go to tab "Home" on the ribbon.
- Press with left mouse button on the "Conditional Formatting" button.
- Press with left mouse button on "New Rule.." to open a dialog box.
- Press with left mouse button on "Use a formula to determine which cells to format".
- Type the formula. (See below).
- Press with left mouse button on "Format..." button and choose a formatting.
- Press with left mouse button on OK button twice.
Conditional formatting formula
Explaining conditional formatting formula
The CF formula changes from cell to cell depending on how you set it up, a cell reference may be absolute or relative.
The dollar sign makes a cell reference locked (absolute), however, a cell reference may also have two dollar signs locking both the column and row. This is the case with $F$4, the condition cell reference never changes.
Cell reference $B3 is only locked to column B, when the CF moves to cell C3 cell ref $B3 is still $B3. This highlights cell C3, see example image above. The next cell is D3 and $B3 is still $B3 highlighting cell D3.
Cell B4 is on the next row and $B3 changes to $B4, cell $B4 is not equal to the condition in cell F4 so this cell is not highlighted and so on.
Highlight cells rules category
Excel allows you to quickly highlight cells containing a given text string. How to apply Conditional Formatting Select cell range. […]
Excel has a built-in feature that allows you to highlight dates if a given condition is met. Section 1 below […]
Excel has a few built-in conditional formatting features, one of them highlights values that have at least one duplicate. The […]
Conditional Formatting categories
Excel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.