How to change the color of Excel conditional format based on the previous column value

Key points of this article

Use formula rules to compare with another column. Write a relative reference in the formula, such as =B2>A2, and Excel will automatically adjust the row number with each row.
The application range must select data rows and do not include table headers; formulas are written based on the cell in the upper left corner of the selection.
You can create one rule each for greater than and less than, and set different colors respectively. The two rules do not affect each other.
Use $ to lock the cell with a fixed value, such as =B2>$D$1; when there is a blank space in the previous column, add the LEN(A2)>0 condition to avoid accidental color change.

When many people use Excel to make monthly reports, inventory comparisons or performance tracking, they want cells with higher values for this month than the previous month to automatically turn green, and cells with lower values than the previous month to turn red. This type of requirement usually does not compare the number with a fixed value, but with the same row of data in the left column. The setting method is different from "change color when it is greater than 100". The key is to use formula rules and use relative references correctly.

This article is written for readers who already know how to use basic formulas and want to make the color automatically change with the data. After reading, you can do three things: set the color according to the value of the previous column, set the two colors of "greater than" and "less than" respectively, and deal with common problems such as the previous column being blank and the header being mistakenly selected. The operations in this article only use the common functions of Excel. The menu locations of different versions may be slightly different. Just find them by the function name.

How to make a cell change color after comparing it with the previous column? One sentence conclusion

Direct answer: Select the column you want to change color (excluding the header), create a new formatting rule based on a formula, write the formula in the form of comparing the column on the left, for example =B2>A2, and then set the fill color. The table below lists several common situations and corresponding actions.

your situation	Recommended practices	reason
Compare this month's value with last month's (left column). If it is higher, it will change color.	Use the formula =B2>A2, the selection starts from B2	The relative reference will automatically change with the line number, and each line will be compared with the data on its left
To distinguish between two colors above and below	Create two rules: =B2>A2, set it to green, =A2>B2, set it to red	The two rules are judged separately, and the colors do not interfere with each other.
To be compared with a fixed target value	The formula is written as =B2>$D$1, or use the "cell value" rule	$ Lock the target cell to avoid the target position changing when dragging
Allow a certain floating range, such as no discoloration within 10% of the upper and lower range	Use =AND(LEN(A2)>0,ABS(B2-A2)>A2*0.1) as the discoloration condition	Write the gap into the formula, the range is set by you, do not rely on fixed numbers
There is a blank row in the previous column	Add the condition LEN(A2)>0 to the formula	When comparing blanks with numbers, the result is unreliable. It is more reliable to exclude blanks first.

Before you start: Confirm these points first

Before you start, take a look at the data structure. Many "wrong color" problems arise here.

Confirm which column the comparison object is. "Previous column" refers to the column adjacent to the left. If column B is to be changed, the comparison object is column A, and what is written in the formula is A2, not the row above B2.
Confirm that the data is numbers, not text. Numbers copied from web pages or other files are sometimes formatted as text and appear to be numbers but do not participate in comparison. You can enter a simple addition calculation in the cell. Only if you can calculate the result, it means it is a number.
Confirm the header position. Line 1 is usually the header, and rules should be set starting from line 2. If the headers are selected together, the text will be compared with the numbers, and the result will be messed up.
Confirm data range. If rows will be added after the data, try to set the application scope to a lower enough position, or use a table instead (Excel tables will automatically expand the rules) to avoid the newly added data being colorless.
Confirm the meaning of the colors. Before changing the color, think clearly about "what does green mean and what does red mean" and leave a line of explanation in the form so that others will not understand it wrong when they see the color.

Steps to change color according to the previous column value in Excel

Select the data area to be changed color. For example, if you want to set rows 2 to 100 of column B, select B2:B100. The upper left cell of the selection is B2. This is very important, and the subsequent formulas must be based on it.
Open conditional formatting and create a new rule. Find the new rule in the conditional formatting menu, and then select the rule type that formats based on formulas, that is, use formulas to determine which cells need to change color. Different versions have slightly different text, just look for similar names.
Enter the comparison formula. Enter =B2>A2 in the formula box. B2 and A2 here are relative references. Excel will understand it as "compare column B of each row with column A of the same row", and row 3 will automatically become B3>A3, and so on.
Set the fill color. Open the format settings and select a light color in the Fill tab, such as light green. Too dark colors will affect the reading of numbers, while light colors are easier to see.
Check the application scope. After the rule is created, check if the area it applies to is B2:B100. If the range is wrong, modify it directly in the rules management instead of relying on redistricting.
Add "less than" rule. Create a new rule. The formula is written as =A2>B2, which means column B is smaller than column A. Select light red as the fill color. In this way, the upper and lower levels will be distinguished by two colors.
Handle blank lines. If some rows in column A are empty, change the formula to =AND(LEN(A2)>0,B2>A2), and the blank rows will not participate in the comparison and will not change color by mistake.
OK and scroll through the check. After clicking OK, scroll a few screens from top to bottom to see if the color is consistent with the data. Confirm that everything is correct before saving the file.

If you want to compare with a fixed value, change the formula to =B2>$D$1, which means comparing the numbers in column B and cell D1. The function of $ is to lock D1. When dragging or copying the rule, D1 is always compared.

Check each item after completion

After the color setting is completed, don't just glance at it, check it one by one according to the table below.

Check items	How to see	What to do if there is a problem
Is the first row the correct color?	Compare the two numbers in line 2 to manually determine what color it should be	If the direction is opposite, check whether the formula is B2>A2 or A2>B2. Do not write the reverse of the two directions.
Check if the color changes with the line number	Scroll to line 50 and row 100, and randomly check the colors of several lines	If all rows are the same color, it means the references in the formula are locked and the unnecessary $ symbol is removed
Check if the blank or zero values have miscolored	Find an empty row in column A and see if column B changes color	Add the LEN(A2)>0 condition, excluding blank rows
Whether the meter header is included	Check if the first row has color	Modify the scope of application, starting from line 2
Whether the text numbers participate in the comparison	Check if the left-aligned numbers don't discolor	Convert text to numbers or use column breaks to standardize formatting
Do the two rules conflict?	Only the color with higher priority will be displayed within the same cell	Adjust the order in rule management and ensure the two formulas are mutually exclusive (one greater than sign, one less than sign).

If the formula results look incorrect, you can first enter =B2>A2 directly in a blank cell to see if TRUE or FALSE matches expectations. This way, the problem can be pinpointed to the formula itself, or the color settings.

📊 Core Comparison

When setting colors, there are three common approaches, each suitable for different scenarios.

Comparison methods	Suitable scenarios	Formula writing
Comparison with the previous column (relative citation)	Compare month by month and line by line, such as this month and last month, today and yesterday	=B2>A2, the formula changes with the line and only needs to be set once
Compare with fixed values ($ lock cells)	There are scenarios with clear target lines, such as whether sales have reached targets or whether inventory is below safe levels	=B2>$D$1, the target value is placed in a cell, and only this part is changed when modified
Comparison with the floating range (formula calculates the difference)	Allow for some fluctuations, such as price within 10% without marking	=AND(LEN(A2)>0, ABS(B2-A2)>A2*0.1), with the range written in the formula for more flexible adjustment

These three methods can be combined, for example, first checking if the area is within the floating range, then marking the parts outside the range in red.

❓ Frequently Asked Questions (FAQ)

1. Why write B2 in conditional formatting formulas instead of B$2 or $B 2?

B2 is relative reference; both row and column numbers change with position, making it suitable for row-by-line comparison. B$2 locks row numbers, $B 2 locks column numbers, and is only used when fixing a row or column. For a regular comparison in the previous column, just write B2 and A2.

2. How do you set Excel conditional formatting to be greater or smaller than another format?

A direct approach is to create two rules: one formula is =B2>A2, set the green pad; The other is =A2>B2, set the red fill. The two rules do not conflict because the same set of numbers cannot satisfy both directions simultaneously. If you want to distinguish between "equal," add a rule =B2=A2.

3. After setting the settings, the color doesn't appear. What is the reason?

There are three common reasons: the number is in text format and does not include the cell in the application scope, or the reference starting point in the formula is incorrectly selected. First, check whether the cells are numeric format, then confirm the range and formula in rule management, and finally test the formula results separately in the blank cells.

4. Is it possible to change the color of the entire row according to the value in the previous column?

Yes, you can. When selecting the entire row of data ranges, the references in the formula should be based on the upper left corner of the selection. For example, after selecting A2:E100, write the formula =$B2>$A2, meaning the entire row will be changed color based on the comparison results of columns B and A. Adding $ before a column keeps the comparison column fixed and row numbers relative to each other.

5. What if there is a lot of data and too many rules to set?

Rule settings can be applied to the entire area once without needing to set cells individually. For large data volumes, it is recommended to test the formula on a few lines first, confirm everything is correct, then expand the application scope, and write the color meanings next to the table for others to easily consult.

📝 Summary

To change color according to the previous column's values, the key is to use relative reference formulas, such as =B2>A2, and set the application range based on the upper left corner of the selection. Rules for greater or less than or less should be set separately, excluding blank lines and headers. By following the steps and checklist checks item by item, the table will stably change color automatically according to the data.

Open OmniDoc and process your table file