Formatting Issues

How to Apply Conditional Formatting Across Matching Budget and Spending Ranges in Excel

Khadija Khan Oct 1, 2026 869 views

Question details

The user needs to compare a range of spending values against a identically structured budget range, color-coding the text based on whether the spending is under, equal to, or over budget.

How to Apply Conditional Formatting Across Matching Budget and Spending Ranges in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking budget versus actual spending across multiple categories and periods, requiring a visual indicator for variances without manually checking each cell.
Observed behavior: Spending values must display in a green font when they are less than or equal to the budget, and in a red font when they exceed the budget amount.

Before you start

Ensure your budget range and spending range have the exact same dimensions and layout so that the relative cell references map correctly cell-by-cell.

Solution 1Recommended

Use Relative Formula Rules in Conditional Formatting

Apply a conditional formatting rule using relative cell references to evaluate an entire spending range against a budget range simultaneously.

By omitting absolute references (the dollar signs) in your formula, Excel dynamically adjusts the cell references as it applies the formatting rule across your selected range. This allows two identically shaped data blocks to be compared cell-by-cell with just one set of rules.

Select the spending range

Highlight the cells containing your spending data (for example, B46:E77). It is crucial to remember the address of the top-left cell (active cell) in this selection, such as B46.

Open Conditional Formatting

Navigate to the Home tab on the Excel ribbon, click on 'Conditional Formatting', and select 'New Rule' from the drop-down menu.

Apply the green font rule

Choose 'Use a formula to determine which cells to format'. Enter the formula =B46<=B3 (assuming B3 is the top-left cell of your budget range). Click the Format button, change the font color to green, and click OK.

Apply the red font rule

Click 'Conditional Formatting' and 'New Rule' again. Use the formula =B46>B3, click Format, set the font color to red, and click OK to apply.

Use Relative Formula Rules in Conditional Formatting

Alternative Method: You can also set the default font color of the spending range to red, and simply apply a single conditional formatting rule (=B46<=B3) to turn the font green when the condition is met.

Efficient Spreadsheet Management

Apply Conditional Formatting Easily in WPS Spreadsheet

WPS Spreadsheet offers powerful, intuitive conditional formatting tools that are fully compatible with Excel formulas, allowing you to highlight budget variances quickly and accurately.

1. Open your file in WPS: Launch WPS Office and open your budget tracking spreadsheet.
2. Select the target range: Highlight the entire spending data block where the font colors need to change.
3. Access conditional formatting: Go to the Home tab on the top menu, click Conditional Formatting, and select New Rule.
4. Input the relative formula: Choose 'Use a formula', input your relative references (like =B46<=B3), set the desired format, and click OK.

Fully compatible with Microsoft Excel conditional formatting rulesFamiliar user interface with no learning curve requiredLightweight performance even when handling large datasetsFree to download and use for your daily financial tracking

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting applied to the wrong cells?

This usually happens if you used absolute references (with $ signs, e.g., =$B$46<=$B$3) instead of relative references. It can also occur if the active cell when creating the rule did not match the first cell referenced in your formula.

Can I highlight the background instead of changing the font color?

Yes. When you are in the 'Format Cells' dialog box while setting up the conditional formatting rule, switch to the 'Fill' tab and select a background color instead of changing settings on the 'Font' tab.

Does conditional formatting update automatically when the budget changes?

Yes, conditional formatting is entirely dynamic. Any changes to the numbers in either your budget range or spending range will instantly trigger the rules and update the font colors accordingly.

Document & File Issues