Highlight Rows Based on a Cell Value in Excel

Conditional formatting is a game-changer when it comes to making large data sets in Excel more readable and actionable. If you've ever wanted to draw attention to rows that meet specific criteria—whether it's identifying underperforming metrics, blank entries, or matching specific text values—learning how to highlight rows in Excel is an essential skill. This article breaks down the step-by-step process, covering everything from formatting rows based on text or numeric values to applying multiple conditions with different colors. With these techniques, you can elevate your Excel sheets into powerful tools for visual data analysis.

Why Highlight Rows Based on a Cell Value

Highlighting rows based on a cell value offers the following benefits:

• Improved Data Analysis: Quickly locate important rows in large datasets.
• Better Visualization: Make your spreadsheets more readable and visually engaging.
• Error Identification: Highlight errors or missing data automatically.
• Trend Tracking: Identify specific criteria like high performers or low inventory levels.

Why Use Conditional Formatting

Conditional formatting is invaluable when dealing with large datasets. By highlighting specific rows based on certain conditions, you can quickly identify key information without manually sifting through the data. For instance, in a college database, administrators can highlight rows of students who have been blacklisted due to misconduct. This makes it easier to spot these records in a sea of data.

Real-World Example: Highlighting Rows in Employee Data

Consider a company's employees' data is present. The following table consists of data about the project(s) which has been assigned to employees, their ages, and IDs. An employee can work on multiple projects. The blank cell in the project denotes that no project has been assigned to that employee.

How to Highlight Rows Based on a Cell Value

Follow the below steps to use Conditional Formatting to highlight entire rows based on the value of a specific cell:

1. Highlight Rows Based on Text Match

Aim: Highlight all the rows where Employee name is "Srishti".

Step 1: Select the Dataset

• Select the dataset you want to apply the formatting to.
• In this case, select the range A3:D14.

Step 2: Open Conditional Formatting

• Go to the Home tab on the ribbon.
• Click on Conditional Formatting in the Styles group.
• From the dropdown, select New Rule.

Keyboard Shortcuts for Accessing Conditional Formatting

• Windows Shortcut: Press Alt + H + L + N.
• Mac Shortcut: Press Command + Option + L.

Step 3: Choose a Rule Type

• In the New Formatting Rule dialog box, select Use a Formula to Determine Which Cells to Format.

Step 4: Enter the Formula

• In the formula box, enter the following formula

In the formula box, write the formula :

=$B2="Srishti"

Explanation: The $ symbol locks column B so that only values in column B are checked for "Srishti". The formula begins checking from cell B3 and moves row by row.

Step 5: Set the Formatting

• Click on the Format button in the dialog box.
• Go to the Fill tab and choose a color to highlight the rows (e.g., yellow).
• Click OK to confirm the format.

Step 6: Apply the Rule

• Click OK again in the New Formatting Rule dialog box.
• The rows where "Srishti" appears in column B will now be highlighted.

Step 7: Preview Results

• The rows containing "Srishti" in column B will be formatted with the color you selected, making them easy to identify.

2. Highlight Rows Based on Numeric Values

Aim : Highlight all the rows having age less than 25.

Step 1: Select the Dataset

• Select the dataset you want to apply the formatting to.
• For example, select the range A3:D14.

Step 2: Open Conditional Formatting

• Go to the Home tab.
• Click Conditional Formatting and choose New Rule.

Step 3: Choose a Rule Type

• In the New Formatting Rule dialog box, select Use a Formula to Determine Which Cells to Format.

Step 4: Enter the Formula

• In the formula box, enter:

=$C3<25

Explanation: The $ symbol locks column C so that only the age values in column C are checked for being less than 25.

Step 5: Apply the Rule and Preview Results

• Click OK in the dialog box to apply the rule.
• Rows where the age is less than 25 will now be highlighted.

3. Highlight Rows Using OR/AND Conditions

Conditional formatting in Excel allows you to apply custom rules using logical operators like OR and AND. These operators help define multiple conditions for highlighting rows.

Understanding OR and AND Operators

• OR: Highlights rows if at least one condition is true.
• AND: Highlights rows only if all specified conditions are true.

Scenario 1: Highlight Rows Based on OR Condition

Aim: Highlight all rows where employees are working on "Project 1" or "Project 4".
Column: Project details are in column D.

Step 1: Select the Dataset

Highlight the range (e.g., A3:D14).

Step 2: Open Conditional Formatting

Go to the Home tab and click Conditional Formatting > New Rule.

Step 3: Choose Rule Type

In the New Formatting Rule dialog box, select Use a Formula to Determine Which Cells to Format.

Step 4: Enter the Formula

The project details are in column D. So, the formula will be :

=OR($D3="P-1",$D3="P-4")

Explanation:

• The formula checks if the value in column D is either "P-1" or "P-4".
• The $ locks the column reference to D, ensuring only column D is evaluated for the condition.

Step 5: Set Formatting

Click Format, go to the Fill tab, and choose a color for highlighting rows.

Step 6: Apply the Rule

• Click OK to close the formatting dialog box.
• Click OK again to apply the rule.

Rows where employees are working on "Project 1" or "Project 4" will now be highlighted.

Scenario 2: Highlight Rows Based on AND Condition

Aim: Highlight rows where employee "Rishabh" has completed project "P-3".

Columns:

• Employee name is in column B.
• Project details are in column D.

Step 1: Select the Dataset

Highlight the range (e.g., A3:D14).

Step 2: Open Conditional Formatting

Go to the Home tab and click Conditional Formatting > New Rule.

Step 3: Choose Rule Type

In the New Formatting Rule dialog box, select Use a Formula to Determine Which Cells to Format.

Step 4: Enter the Formula

=AND($B3="Rishabh",$D3="P-3")

Explanation:

• The AND function ensures both conditions are true:
• Column B must contain "Rishabh".
• Column D must contain "P-3".
• Only rows meeting both criteria will be highlighted.

Step 5: Set Formatting

Click Format, go to the Fill tab, and choose a different color for highlighting rows.

Step 6: Apply the Rule

• Click OK to close the formatting dialog box.
• Click OK again to apply the rule.
• Rows where "Rishabh" has completed "P-3" will now be highlighted.

The name is in column B and the project details is in column D. The formula will be:

4. Highlight Rows with Blank Cells

Aim: Highlight rows containing at least one blank cell in the specified range.

• The project details are in column D.
• The dataset spans columns A to D.

We will use the COUNTIF Function to identify blank cells and apply conditional formatting to highlight rows with any blank cells.

Step 1: Select the Dataset

• Highlight the dataset where you want to apply the rule (e.g., A3:D14).

Step 2: Open Conditional Formatting

• Go to the Home tab.
• Click Conditional Formatting > New Rule.

Step 3: Choose Rule Type

• In the New Formatting Rule dialog box, select Use a Formula to Determine Which Cells to Format.

Step 4: Enter the Formula

• Enter the following formula in the formula field

=COUNTIF($A3:$D3,"")>0

"" : Denotes blank
The formula checks each column in the row (A3:D3) for blank cells.
If at least one blank cell exists, the condition evaluates to TRUE.

Step 5: Set the Formatting

• Click the Format button.
• In the Fill tab, choose a color (e.g., red) to highlight rows with blank cells.
• Click OK.

Step 6: Apply the Rule

• Click OK in the New Formatting Rule dialog box.
• Rows containing at least one blank cell in the range $A3:$D3 will now be highlighted.

5. Highlight Rows Based on Multiple Conditions with Different Colors

Aim:
Distinguish employees based on their age by highlighting:

• Yellow: Senior employees (age > 25).
• Green: Junior employees or trainees (20 < age ≤ 25).

Step 1: Highlight Senior Employees (Age > 25)

• Select the dataset (e.g., A3:D14).
• Go to Home > Conditional Formatting > New Rule.
• Select Use a Formula to Determine Which Cells to Format.
• Enter the formula

=$C3>25

• Click Format, choose Yellow Fill, and click OK.

Step 2: Highlight Junior Employees (20 < Age ≤ 25)

• Repeat the above steps.
• Enter the formula

=$C3>20

• Click Format, choose Green Fill, and click OK.

Step 3: Adjust Rule Priority

1. Go to Home > Conditional Formatting > Manage Rules.
2. Select the rule for Senior Employees (age > 25) and move it to the top using the Up Arrow.
3. Click Apply and OK.

This creates a problem as our goal is to make two separate groups. In order to resolve this we need to change the priority of highlighting the rows. The steps are :

• Undo the previous steps using CTRL+Z.
• Select the entire dataset.
• Go to Conditional Formatting followed by Manage Rules.

• In the Manage Rule dialog box:

The order of the condition needs to be changed. The condition at the top will have more priority than the lower one. So, we need to move the second condition to the top of the first one using the up icon after selecting the condition.

• Now click on Apply and then OK .

It can be observed that the rows are now divided into two categories:

• The yellow color is for the senior level employees
• The green color is for junior employees and trainees in the company on the basis of their ages.

Also Read:

• Conditional Formatting in Excel
• Top Excel Formulas and Functions You Should Know 2024
• COUNTIF Function in Excel
• Excel COUNTIF Function for Exact and Partial Match (With Examples)

Conclusion

Mastering the ability to format rows based on cell value not only makes your spreadsheets more intuitive but also enhances decision-making by presenting data in a visually organized manner. Whether you're analyzing employee records, tracking sales trends, or managing inventory, these tips for using Excel conditional formatting rows will transform how you interact with your data. Explore the advanced techniques shared here and discover how efficiently formatted spreadsheets can save you time and effort.

rishabhchakrabortygfg

Follow

Improve

Article Tags :

• Excel
• News
• Microsoft Office
• ExcelGuide