How to determine whether you are over or under budget using custom formats

Monitoring budgets can be quite stressful and may often involve the use of formulas. Instead of complicated formulas, you can use custom formats within Microsoft Excel to easily determine if you are over or under budget.

All it takes are a few steps, which we explain below, and you can easily monitor your budget.

You’re welcome to download the workbook to practice.

Applies to: Microsoft Excel  2010 and 2013

1. Select the data range E4:E15 and right click on the selected range.

2. Select Format Cells, then select Custom from the Category menu.

3. In the Type field select 0.

ExcelCustomFormatsBudget2

4. Type in the following: 0,000 “Under budget”;-0,000      “Over budget”

Then select OK

NB: If the formula populates an error, use the following instead:

0,000 “Under budget”;-0,000 “Over budget”

ExcelCustomFormatsBudget5

The values where the variance is positive have the words “Under budget” next to them. Where the variance is negative, “Over budget” is displayed. In that way, it is easy to determine whether a value is over or under budget.

ExcelCustomFormatsBudget4


Sage © Sage South Africa Pty Ltd 2019 . All Rights Reserved.