FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
April 14, 2016
 
Greetings from The Excel Addict
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.


H
ave a great week and keep on Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com


Francis Hayes (TheExcelAddict.com)


1604 Excel Pivot Table Webinar


If you missed my last newsletter, you can click here to view it online.


 
TheExcelAddict.com Quote of the Day

"No one can make you feel inferior without your consent."

-- Elanor Roosevelt --

If you have a favourite quote, send it to me and I may post it in my newsletter.

Today's Microsoft Excel Tip

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.

Conditional Format Entire Row in Microsoft Excel 2007 2010 2013 2016 365
DOWNLOAD PRACTICE FILE HERE

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;

Add New Conditional Format Rule in Microsoft Excel 2007 2010 2013 2016 365

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, c
onditional 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.

Conditional Formatting Formula in Microsoft Excel 2007 2010 2013 2016 365

Help with Building your Conditional Formatting Formula

If you find it difficult to create your formula in the Conditional Formatting Rule dialog, you may find easier to create the formula in your worksheet first. In an empty column next to your data,
on the same row as the active cell in your selection, create a formula for the condition you want to test that results in either TRUE or FALSE. Then copy it down the column.

Build Conditional Formatting Formula In Worksheet in Microsoft Excel 2007 2010 2013 2016 365
To check if you have your absolute and relative references correct, copy the formulas to another column and you should get the same TRUE and FALSE results.

Conditional Formula Check Cell References in Microsoft Excel 2007 2010 2013 2016 365
Once you have your formulas working correctly in the worksheet, copy the formula from the 'active row' (e.g. D2 in our example) and paste into the Conditional Formatting rule.


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;

Choose Red Fill Format in Microsoft Excel 2007 2010 2013 2016 365

7) Click OK to close the Format Cells dialog and you will see a preview of the cell formatting you've selected;
 

Conditional Format Preview in Microsoft Excel 2007 2010 2013 2016 365

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.

Entire Rows Formatted With Conditional Formatting in Microsoft Excel 2007 2010 2013 2016 365
DOWNLOAD PRACTICE FILE HERE

1604 Excel Pivot Table Webinar

Thanks for supporting this newsletter and website

FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"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