Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles CONDITIONAL FORMATTING How to Apply Conditional Formatting to an Entire Row

How to Apply Conditional Formatting to an Entire Row

Conditional Formatting is a feature in Excel that allows us to change the format of cells based on a set of rules or conditions. Conditional formatting can be applied to a set of selected cells, including an entire row. This step by step tutorial will assist all levels of Excel users in applying conditional formatting to an entire row.

Figure 1.  Applying conditional formatting to an entire row

Setting up the Data

Suppose we have the sample data below. We want to highlight the people that are below 18 years of age and apply the format to not just the “Age” column but to the entire row.  

Figure 2.  Sample data for conditional formatting to an entire row

Applying Conditional Formatting to an Entire Row

We can do this in 4 easy steps:

  • Step 1. Select the rows to be formatted.  In this case, select cells A4:E10.

Figure 3.  Selection of the data range for conditional formatting

  • Step 2. Click the Home tab, then the Conditional Formatting Menu and select “New Rule”. The New Formatting Rule dialog box will pop up.  

Figure 4.  Creation of a new rule in conditional formatting

  • Step 3. Select the Rule Type “Use a formula to determine which cells to format” and enter this formula in the dialog box :

=$E4<18

Important Note: Add the dollar sign “$” before the column E to fix the column that we use as reference for the conditional formatting.

The formula =$E4<18 serves as the condition or rule that will trigger the conditional formatting.  For every row of data, if the “Age” is less than 18, the format will be changed.  

Figure 5.  Entering the formula as a condition or formatting rule

How will the format change? Let us proceed to the next step.  

  • Step 4. Click “Format” and then decide on what will be the new format to apply to the entire row.  We can change the font, borders or fill the cells with different colors.

Example

Select “Fill” and choose Orange, Accent 6, Lighter 40% and click OK.  

Figure 6.  Selection of the format to use

Figure 7.  Completion of the new formatting rule with formula and selected format

This rule highlights the entire rows of data that satisfy the condition of Age < 18.  

Figure 8.  Output: New conditional formatting rule reflected in the entire row of data

As shown, we are able to change the format of the entire rows with ages less than 18.  

With Conditional Formatting, the options are endless.  We can customize the format of the cells to our preference, based on the rules we provide.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc