Formatting Issues

Highlight Column A Based on Column C Values in Excel

Adam DavisAdam Davis Nov 14, 2026 868 views

Question details

Highlight cells in Column A according to the corresponding values in Column C.

Product
Microsoft Excel
Device & OS
not provided
Scenario
The worksheet has many rows and columns, and the user wants to format Column A when the value in Column C is greater than or less than 2.
Observed behavior
Each selected cell in Column A should be highlighted when the numeric value in Column C on the same row is above or below 2.
Before you start

Identify the first data row and the last row you want to format. The example below assumes data starts in row 2 and extends through row 10000.

Solution 1Recommended

Create a formula-based conditional formatting rule in Excel

Use one rule to highlight Column A when the corresponding numeric value in Column C is not 2.

1
Select the target cells

Select A2:A10000 in the worksheet, adjusting the range to cover your data. Do not include a header row unless you want it formatted.

2
Create a formula rule

Go to Home > Conditional Formatting > New Rule, then select Use a formula to determine which cells to format.

3
Enter the condition

Enter =AND(ISNUMBER($C2),OR($C2<2,$C2>2)). Replace 2 in $C2 with the first row number of your selected range; leave the comparison values as 2.

4
Choose the highlight

Select Format, choose a fill color on the Fill tab, and select OK. Select OK again to create the rule.

5
Check the range

Open Home > Conditional Formatting > Manage Rules and confirm that Applies to covers the intended cells, such as =$A$2:$A$10000.

How the formula works: The dollar sign locks the reference to Column C, while the row number changes for each cell in Column A. ISNUMBER excludes blank and text cells, and a value of exactly 2 is not highlighted.
Format spreadsheets with WPS

Highlight Column A using Column C in WPS Spreadsheet

WPS Spreadsheet can apply a formula-based conditional formatting rule to Column A using values from Column C in the same row.

  1. 1. Open the spreadsheet: Open your workbook in WPS Spreadsheet and select the target cells in Column A, such as A2:A10000.
  2. 2. Add a rule: Go to Home > Conditional Formatting > New Rule and choose the option to use a formula to determine which cells to format.
  3. 3. Enter the formula: Enter =AND(ISNUMBER($C2),OR($C2<2,$C2>2)), changing the row in $C2 if your selection starts on a different row.
  4. 4. Set the formatting: Choose a fill color in Format, confirm the rule, and check its applied range in Conditional Formatting > Manage Rules.
Highlight rows based on values in another columnOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet ribbon and formatting toolsWork with a free, lightweight Office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why is a value of exactly 2 not highlighted?

The formula checks for values less than 2 or greater than 2, so 2 meets neither condition. To include 2, use a condition such as $C2<=2 or $C2>=2, depending on the result you want.

How do I highlight only values greater than 2?

In the formula-based rule, use =AND(ISNUMBER($C2),$C2>2). Match the row number in $C2 to the first row of the selected Column A range.

Why are the wrong rows being highlighted?

Check that the row number in the formula matches the first row in the Applies to range. In Excel, open Home > Conditional Formatting > Manage Rules to inspect both the formula and range.

Will blank cells in Column C trigger the rule?

No. The ISNUMBER($C2) part of the example formula excludes blank cells and text values.