Excel Budget Conditional Formatting: Setup and Rules

Yes. To show which items are under budget in Excel, select the actual-spending cells and create a conditional-formatting rule that compares each amount with the budget in the same row. The rule changes the cell’s appearance when spending is lower; it does not change the underlying amounts or calculate a new budget.

Set up a basic under-budget rule

Assume your worksheet has categories in column A, budget amounts in column B, and actual spending in column C, with the first data row in row 2. To color actual spending when it is below its budget, select C2:C100, then choose Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format, enter the formula below, choose a fill or font style with Format, and select OK.

For that layout, use this formula:

=AND($B2<>"",$C2<>"",$C2<$B2)

The dollar signs lock the budget and actual columns, while the row numbers stay relative. Excel checks row 2 for the first cell in the selected range, row 3 for the next, and so on. The blank checks prevent empty rows from being colored as though they were genuine results. If your data starts on a different row or uses different columns, adjust the references to match the first selected row and the corresponding budget and actual columns.

In Excel for Microsoft 365 on Windows, the formula-based rule is available in the New Formatting Rule dialog. Excel for Mac has conditional formatting under Home > Conditional Formatting; dialog wording or placement can vary slightly by release. Excel for the web also offers conditional formatting from the Home tab, though the available editing controls can differ from the desktop app.

What conditional formatting does

Conditional formatting applies a visual style when a cell meets a condition. The cell still contains its original number or formula, so you can continue to calculate totals, sort the table, and use the values elsewhere. A color is a signal for the reader, not a substitute for a correct calculation: a green cell does not prove that the budget data is complete or that the spending has been classified correctly.

For a simple comparison against a fixed threshold, Excel’s built-in rules can be quicker than a formula. Select the spending range and choose Home > Conditional Formatting > Highlight Cells Rules > Less Than, then enter the threshold. That approach compares every selected cell with the same number. It does not automatically compare each row with a different budget amount, so the formula-based rule is usually the better choice for a category-by-category budget.

Read the result with a budget example

Suppose a department has a monthly travel budget of $1,200 and has spent $950. Because $950 is less than $1,200, the rule marks the actual-spending cell as under budget. If it spends $1,250, the same rule does not apply. This is a direct comparison of the two amounts; it does not account for timing, purchase commitments that have not yet been paid, or whether the budget covers the same dates as the actual figure.

A separate variance column can make the size and direction of the difference clearer. With budget amounts in B2:B100 and actual spending in C2:C100, enter this in D2 and fill it down:

=B2-C2

For the example, D2 returns 250, meaning actual spending is $250 below budget. A negative result indicates spending above budget; for example, a $1,250 actual against a $1,200 budget gives -50. This convention—budget minus actual—makes positive values favorable for a spending budget, but label the column clearly because some reports use the opposite subtraction order.

You can also calculate the share of the budget used. With budget in B2 and actual in C2, enter this in E2 and format the result as a percentage:

=IF(B2=0,"",C2/B2)

For $950 spent against $1,200, the result is 79.2% when displayed to one decimal place. The formula returns a blank when the budget is zero, avoiding a division-by-zero error. A percentage can be useful for comparing categories of different sizes, but it should be read alongside the dollar variance: 90% of a large budget can represent much more money than 90% of a small one.

Choose the right condition and threshold

“Under budget” most often means actual spending is less than the approved budget. Some readers, however, want a warning when a category is nearly depleted, or want to distinguish a modest underspend from a large one. Decide what the colors should communicate before building several rules. For the same B-and-C layout, a three-color scheme might mean:

Green: actual is below 90% of budget.
Amber: actual is at least 90% but no more than 100% of budget.
Red: actual is above budget.

With data starting in row 2, a rule for the first category in the green range could use =AND($B2>0,$C2<$B2*0.9). The amber rule could use =AND($B2>0,$C2>=$B2*0.9,$C2<=$B2), and the over-budget rule could use =AND($B2>0,$C2>$B2). Apply each rule to C2:C100, choose distinct styles, and check that the formula row matches the first row of the range. These thresholds are examples, not universal accounting standards; choose limits that suit the reporting purpose.

Be deliberate about equality. A rule using less-than does not mark spending exactly equal to budget. If equality should count as on budget, create a separate rule or adjust the condition; do not assume a “less than” test includes the boundary. Also decide how to treat refunds, credits, or negative budgets. A negative actual can be valid in a returns or reimbursement process, but a simple “actual is lower than budget” comparison may not express the intended business meaning.

Make rules easy to read and maintain

Use a small, consistent set of visual cues. A pale fill with dark text is often easier to read than a saturated fill, and color should not be the only way to communicate status. Add a text label such as “Under,” “Near limit,” or “Over” in a separate status column if the workbook will be printed, viewed in grayscale, or used by people who may not distinguish the chosen colors.

To review or modify a rule, select a cell in the relevant range and choose Home > Conditional Formatting > Manage Rules. In the rules manager, inspect the rule formula and the Applies to range. A rule can be correct but appear ineffective if its range excludes new rows. Conversely, an overly broad range can color headings, totals, or unrelated cells. If rules overlap, review their order and whether Stop If True is selected; overlapping conditions can make the displayed result differ from what a reader expects.

For a growing list, converting the data range to an Excel table can make the worksheet easier to extend and filter. Check the rule’s applied range after adding rows, because the exact behavior depends on how the rule and table were created. Keep budget and actual values as numbers, not text strings containing currency symbols. Apply a currency number format to numeric cells instead; values stored as text may not compare as expected.

Common problems and how to fix them

Every row gets the same color: Check for a dollar sign before the row number. In a rule applied down a column, $B2 locks the budget column but lets the row change. $B$2 locks both and would compare every row with B2.
No cells are highlighted: Confirm the selected range, the Applies to address, the formula’s first-row reference, and whether amounts are numeric. Also check that actual is genuinely less than budget; equal values do not meet a strict less-than test.
Blank cells look under budget: Add blank checks such as those in the basic formula, or ensure unfinished rows are outside the rule’s applied range.
Totals or headings are colored: Narrow the applied range to detail rows, or create a separate rule designed for totals. A total may need a comparison of summed amounts rather than the row-by-row condition used for categories.
The colors look inconsistent after editing: Open Manage Rules and inspect rule order, overlapping conditions, and formatting that may have been copied from other cells.

When investigating a surprising result, temporarily add a variance formula or inspect the underlying cells. Conditional formatting can hide a data problem by making the display look authoritative, so check for missing entries, duplicated categories, inconsistent date periods, and numbers imported as text.

Compare the same time period

A meaningful comparison requires the budget and actual amounts to cover the same scope and dates. Comparing a full-year budget with one month of spending will make a category look under budget even when it is tracking above its expected pace. For a year-to-date review, compare year-to-date actuals with a year-to-date budget, or clearly label a full-period budget as a different measure.

Budgets may be distributed unevenly across months. A seasonal program, for example, may be expected to spend more in one quarter than another. In that case, compare actual spending with the budget allocated to that same period rather than dividing an annual budget evenly unless that is genuinely the plan. Conditional formatting will apply whatever comparison the worksheet defines; it cannot determine whether the selected budget is an appropriate benchmark.

For an illustrative monthly example, imagine a $12,000 annual allocation planned as $2,000 for the first quarter and $4,000 for the second. If first-quarter actual spending is $2,100, comparing it with the full-year $12,000 amount would misleadingly show a large underspend. Comparing with the $2,000 first-quarter allocation correctly shows a $100 overspend for that period. The figures are examples only. For real reporting, check the applicable budget definitions and reporting terms with the responsible organization or a qualified professional; this spreadsheet explanation is not financial, tax, investment, or legal advice.

Cost, performance, and alternatives

Conditional formatting is a built-in Excel feature, so this kind of rule does not require a separate add-in. Availability and some editing details depend on the Excel edition and platform, but the basic comparison is supported in current Microsoft 365 desktop Excel. If a workbook is shared, recipients may see the formatting in compatible Excel versions even if they do not edit the rule.

A few rules over a few hundred rows are generally straightforward to maintain. Very large workbooks with many complicated rules, broad applied ranges, or volatile calculations can become harder to manage and may affect responsiveness. Keep rules limited to the cells that need them, remove obsolete rules, and avoid creating many duplicate rules by repeatedly copying formatted cells.

For a one-time review, sorting or filtering a variance column may be clearer than relying on colors. A chart can show trends over time, while a PivotTable can summarize actuals by category or month. These options answer different questions: conditional formatting flags individual cells against a condition; a chart emphasizes patterns; and a summary helps compare groups. They can also be used together in a budget workbook.

Frequently Asked Questions

How do I highlight expenses that are under budget in Excel?
Put budget and actual amounts in corresponding rows, select the actual range, and create a formula-based rule that tests whether actual is less than budget. For budget in B and actual in C, the row-level comparison is =$C2<$B2; add blank checks if unfinished rows should remain unformatted.

Can Excel automatically highlight expenses that are over budget?
Yes. Create another conditional-formatting rule for the actual range using a greater-than comparison, such as =$C2>$B2 when budget is in B and actual is in C. Choose a distinct style and confirm the rule applies to the intended rows.

Can I highlight a cell based on a different column?
Yes. Use a formula-based rule and refer to the comparison column in the same row. Lock the column with a dollar sign when the rule should always compare against that column, but leave the row number relative when the condition should move down the list.

Why does conditional formatting not work when the amounts look like numbers?
The values may be stored as text, the rule may point to the wrong row or range, or the actual amount may equal rather than fall below the budget. Check the cell values, formula references, and Manage Rules settings before changing the format.

Does conditional formatting change the budget calculation?
No. It changes the display when a condition is true. Use worksheet formulas to calculate variances, percentages, or totals, and use conditional formatting only to make selected results easier to notice.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.