Hi fellow Excel Addict,
Knock! Knock! (on wood). I think spring is finally here! The snow is all gone. The sun has been shining. It's been reasonably warm. Oh, I almost forgot, this is Newfoundland where we sometimes get snow in May.
Last week I showed you how to conditionally format cells based on dates in a column. Subsequently, several readers have asked if they can highlight an entire row, not just a cell, based on the condition in one column.
The answer is yes and that will be the topic of today's 'Excel in Minutes' tutorial.
If you missed my 'Excel in Seconds' newsletter on Tuesday, I showed you how to 'Avoid Moving to the Next Cell'. You can read all about it here.
Have a great week and keep on Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
Format An Entire Row Using Conditional Formatting
Conditional Formatting is frequently used to format cells based on their contents. Sometimes however, rather than highlighting just the cell, we would like focus the reader's attention on the entire row that contains that cell.
To do this, we need to use a formula rather than selecting one of the built-in Conditional Formatting options.
In this example, we want to highlight the entire row where a student's grade in Semester 2 is lower than their grade in Semester 1. In a wider spreadsheet, having the entire row highlighted helps the viewer of the report to quickly see all of the data related to the condition being evaluated.
1) First select then entire range of data you want to format, excluding the column headings (in this example A2:C6);
2) From the Home tab, click Conditional Formatting, then click New Rule... The New Formatting Rule dialog appears;
3) In the 'Select a Rule Type' section, choose 'Use a formula to determine which cells to format';
4) In the 'Format values where this formula is true' field, we need create a formula that results in either TRUE or FALSE. For cells where this formula evaluates to TRUE, conditional formatting will be applied.
Active Cell: When you select a range of cells in Excel, all cells except for one are shaded. The one that is not shaded (remains white in most cases) is called the 'active cell'.
With Conditional Formatting, we need to use the 'active cell' in our selected range as the basis for building our formula. Since the active cell in this example is A2, we build our formula from the point of view of row 2 and we use absolute references for the columns in our formula.
The formula we use in this example is =$C2<$B2 since we are checking if the value in column C is less than the value in column B. We want to ensure that the conditional formatting for all cells in the selection refers to columns B and C, so we use 'absolute' ($) column references. The row reference will be 'relative' (no $) since formatting will be applied relative to each row.
5) The next step is to choose formatting to apply to rows where this formula is TRUE. To do this, click on the Format... button;
6) In the Format Cells dialog, click the Fill tab and choose a background color. If you use a dark color (e.g. red) for the background, you may want to select a lighter font color (e.g. white) from Font tab;
7) Click OK to close the Format Cells dialog and you will see a preview of the cell formatting you've selected;
8) Click OK and you will be returned to your worksheet. You should now entire rows formatted where the value in column C is less than column B.
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630