OmniDocHow to use conditional formatting to color data in Excel
When comparing two columns of data, how to use conditional formatting to automatically color the decreasing values. Explain the writing method of formula rules, the points to note about relative references, the method of marking by percentage, and the difference from color marking of repeated values.
Key points of this article
- When comparing two columns of data, use the formula rule
=AND(ISNUMBER(B2),ISNUMBER(C2),C2-B2<0)to determine "this period is less than the previous period", which is more stable than simply coloring based on numerical values. - The formula rule requires writing a relative reference based on the cell in the upper left corner of the selection. If you write a wrong dollar sign, the entire column color will be misaligned.
- If you want to color mark according to the decrease ratio, you must first exclude null values and zeros, and then use "(original value − new value) ÷ original value" to calculate the ratio.
- Marking and reducing data uses formula rules in conditional formatting. Coloring of repeated values is another function. The judgment logic of the two is different.
- The color marking is just a visual reminder, and the results need to be checked manually by taking a few lines, especially text-type numbers and empty cells.
When doing monthly reports, inventory comparisons, or price tracking, it's often important to see at a glance which numbers have dropped from the previous period. If you manually compare row by row, it will be slow and easy to miss. Excel's conditional formatting can automatically change the color of cells that meet the conditions, and the color will also change after the data is updated.
This article is written for people who already know how to enter data, but are not very good at setting conditional formatting. You will learn: how to use formula rules to determine "this period is lower than the previous period", how to extend the color range to the entire row, how to mark the data with a large decrease by percentage, and how to check whether the results are correct after completion.
The following method takes two columns of data as an example: column B is the previous period, and column C is the current period. When your table structure is different, you only need to replace the column numbers and judgment conditions.
How to color the reduced comparative data in the table? One sentence conclusion
Use the formula rules in conditional formatting to determine whether the current period's value is less than the previous period's value, and then set the red fill or red font. This method can automatically update with the data and does not require manual coloring every time.
| your situation | Recommended practices | reason |
|---|---|---|
| I only want to mark the cells where the current period is lower than the previous period. | Use formula rule =AND(ISNUMBER(B2),ISNUMBER(C2),C2-B2<0) for current period columns | Only change the color of the numerical column, the judgment logic is clear |
| Want the entire row to change color with decreasing data | Select the entire table range and use a formula with a dollar sign like =$C2-$B2<0 | Lock the compared column, and the row number changes with dragging |
| Only care about data whose decline exceeds a certain percentage | Calculate (B2-C2)/B2 in the formula and compare it with the threshold | It is also a ratio, which is more suitable for data of different magnitudes than a simple difference. |
| I want to directly see the extent of the reduction. | Add a column of "difference", use =C2-B2, and set the red number format for negative numbers | Colors and numbers are visible at the same time for easy checking |
| Want to find recurring content | Use highlight duplicate values in cell rules instead of formula rules | Duplicate values determine whether the content is the same, regardless of the size of the value. |
Before you start: Confirm these points first
1. Confirm which column is the previous issue and which column is the current issue. The direction of size determination in formula rules depends on this. If the positions of the two columns are reversed, the growth data will be marked. It is recommended to clearly write "previous issue" and "current issue" in the header to avoid confusion later.
2. Confirm whether the data is a real number. Numbers copied from web pages or other systems sometimes exist in text form with a small green triangle in the upper left corner. They appear to be numbers but do not participate in comparison. The results of comparing text numbers with numbers are unreliable and need to be converted into numerical values first.
3. Confirm how empty cells are handled. Empty cells are treated as 0 in formulas. If the previous period is empty and the current period is 5, the formula may be misjudged as "rising"; if the current period is empty, it may also be misjudged as "down". Therefore, the ISNUMBER check needs to be added to the formula, and only rows with numbers on both sides are judged.
4. Make sure the range of contrast is fixed. If new rows are added later under the table, the rules should be applied to a sufficiently large area, or directly to entire columns. Otherwise, the new data won't be colored, making it easy to think "there hasn't been a decrease."
5. Confirm whether the meanings of the colors are consistent. In the same report, red usually indicates a decline, while green indicates an increase. If you use multiple rules at the same time, try to write the color instructions above the table to avoid misunderstandings for readers.
Mark the steps to lower the data in Excel by previous and current periods
- Open the worksheet, enter the previous period's data in column B, and the current period's data in column C. Write the header in the first row.
- Select the area you want to color, such as C2 to C20. Note: The cell in the upper left corner of the selection area is the baseline cell in the formula rules, here is C2.
- First, find the conditional formatting function in the toolbar, create a new rule, and select the type of format determined by formulas. This step allows Excel to judge frame by frame according to formulas, rather than just comparing fixed values.
- Enter
=AND(ISNUMBER(B2),ISNUMBER(C2),C2-B2<0)in the formula box. The formula states: both sides are numbers, and if the current period minus the previous period is less than zero, the condition holds. - Go to the Format option, select Red in Font or Fill, then confirm. You can preview the effect first.
- After confirming the rule, check whether the scope of application in the rule manager is C2:C20 to avoid mistakenly applying it to other columns.
- If you want the entire line to change color, select A2 to C20, create a new formula rule, enter
=$C2-$B2<0, and set the format. The dollar sign locks the comparison column, so no matter how you drag the color marker area, the comparison object will not shift. - Modify any value and observe if the color changes accordingly. If the color hasn't changed, it's most likely a problem with the formula reference or the number formatting.
If you want to annotate by scale, you can change the formula for step 4 to =AND(ISNUMBER(B2),ISNUMBER(C2),B2<>0,(B2-C2)/B2>=0.1). Here, B2<>0 is used to avoid dividing by zero, while 0.1 means the reduction has reached 10%. Thresholds can be adjusted according to your own data.
After completion, check each item one by one
Once the color settings are complete, don't just glance and hand them over to others. The following suggestions are to check one by one:
| Inspection items | How to see it | What should you do if there's a problem? |
|---|---|---|
| Check whether the color marking positions are correct | Randomly select three to five elements and manually compare the values from the previous period with the current period | Check the column and row numbers in the formula, especially whether B2 and C2 are written in reverse |
| Relative citation is followed or not | After dragging the selection or copying the rule down, check if the color matches the correct row | Use a dollar sign for columns and keep relative references to rows |
| Check whether the null value and text have been misjudged | Find a blank cell or text number line and see if it is colored | Retain the ISNUMBER judgment and convert the text numbers into values |
| Whether the rules conflict with each other | Open the Rule Manager and check if there are multiple rules in the same area | Adjust the order of rules or remove duplicate rules |
| Whether the new data is colored | A temporary row of test data is added at the end of the table | Expand the scope of application, or change to a whole citation |
When verifying, note that conditional formatting only changes the display effect and does not alter the values in the cells. If you need to delegate the results for statistics, you should separately list the data for the reduction, rather than relying solely on colors.
📊 Core Comparison
Common ways to reduce data with color labeling include the following. Which one to choose depends on the amount of information the reader needs.
| Method | Suitable for the scene | Advantages | Limitations |
|---|---|---|---|
| Formula rules and color markings | Comparing the two columns, the only concern is whether it has dropped | Flexible, with customizable conditions | You need to understand cell references |
| The value is less than a fixed value | Compared to fixed goals | Easy to set up | It's impossible to compare the previous data line by line |
| Icon Set (Arrows) | You need to look at the trend direction at the same time | More intuitive than color, suitable for printing | Not sensitive enough to subtle changes |
| Difference column plus negative number format | You need to see exactly how much it has decreased | Both numbers and colors are visible | A column of data will be added |
| Repeat value color marking | Look for duplicate names or numbers | The operation is straightforward | The judgment is that the content remains the same, not the value drops |
🎯 Which method suits you?
- Those who only perform one-time checks: Use formula rules to mark the current period's data as lower than the previous period, check a few rows, and then use it.
- Monthly Report Updaters: Apply rules to entire columns and write color notes near the header, then replace data each time.
- People who need to report to others: Besides color, try to add a column of difference or decrease so readers can see the specific changes.
- Data from external systems: First check for text-type numbers and extra spaces, then set rules, saving a lot of troubleshooting time.
❓ Frequently Asked Questions (FAQ)
1. What is the difference between conditional formatting and repeated value coloring?
Conditional formats can be judged using any formula, such as comparing the values in two columns or calculating the reduction rate. Duplicate value coloring only checks whether the content is the same. To find the lower data, formula rules should be used.
2. Why are the color marking results different from what I calculated by hand?
Common reasons include numbers in text format, or invisible spaces within cells. Additionally, row or column numbers in the formula may be written incorrectly, causing the comparison to be done in different rows. Checking the first few lines of data line by line usually reveals problems.
3. Can we only highlight data where the decrease exceeds a certain amount?
Yes, you can. Change the condition in the formula to =AND(ISNUMBER(B2),ISNUMBER(C2),B2-C2>=100), where 100 is replaced with the amount you set. This method is suitable for tables where the amount differences are quite clear.
4. What should I do if the printed color is not obvious after marking?
You can replace the red font with bold, darker text, or add a column of text next to it. If the print is black and white, the color prompt will be invalid. In this case, try to supplement the information with icons or difference columns.
5. After data is deleted, does the color remain?
Colors follow the formatting rules of the cell; as long as the rules still apply to that area, the colors will be retained. If you delete an entire line, the rules will adjust accordingly. When encountering exceptions, you can open the Rule Manager to check whether the scope of application is still correct.
📝 Summary
Marking the reduced data in conditional format is key: write the correct formula, lock the reference, and use several real data points to verify the results. First, clearly identify the previous and current periods, then decide whether to mark only the values or whole rows. Finally, check the null values and text numbers. The table will update with the data, continuously providing reliable prompts.
