Excel Formulas: Over-Budget Items

Lesson 11: Over-Budget Items

/en/excelformulas/interactive-formula-flashcards/content/

"Hey, buddy. What's happening? So…as you know, we're preparing our monthly budget report.

Could you tell me which of our expenses are over budget? You can probably use our budget spreadsheet to figure it out. Thanks!"

Our spreadsheet

Once you've downloaded our spreadsheet, open the file in Excel or another spreadsheet application. As you can see, our coworker Carter has given us a pretty simple budget: Each category is on a separate row, and there are columns for the projected costs and actual costs.

What are we trying to find out?

Before we try to create our formulas, let's think about the question we're trying to answer: "Which expenses are over budget?"

How do we know if something is over budget? In this example, we can compare the projected and actual costs. If the actual cost is higher than the projected cost, then it is over budget.

Writing the formula

Now we just need to decide how to represent this idea in a formula. One way to compare two values is to subtract one from the other. In our example, we could simply subtract the actual cost from the projected cost.

If the result is negative, then we know that category is over budget. In our example, we expected to spend $14,000 on Facilities, and we actually spent $15,000. If we subtract $15,000 from $14,000, the result would be -$1,000 (negative $1,000). We could also say this category is $1,000 over budget.

Our projected expenses are in column B, and our actual expenses are in column C. So for the first category, the formula to compare these values would be:

=B2-C2

Let’s type this formula into cell D2:

Next, we can just drag the fill handle down to add the formula to the other cells in the column:

Now we can see that several of the categories have negative values, which means they are over budget.

"Hmm… this works, but it's hard to tell which items are over budget at a glance…

…Do you think you could make the over-budget items stand out more?"

Carter is right: Although this spreadsheet gives us an answer, it isn't very easy to read. There are a few different ways we could make the over-budget items more noticeable. For example:

We could filter the data to only show rows that have a negative result.
We could add conditional formatting to make the negative cells red.
We could select the data and create a chart, which will make the data more visual.

All of these solutions will work—there isn't a right or wrong answer. You may want to experiment with different methods to see which one you think is the clearest. In our example, we’ll add conditional formatting.

First, make sure the values in column D are selected. Next, we'll need to create a conditional formatting rule that looks for cells containing a value less than zero. The process will vary depending on which spreadsheet program you're using:

For Excel 2007-2019: From the Home tab, select Conditional Formatting > Highlight Cells Rules > Less Than.
For Excel 2003 and earlier: Go to Format > Conditional Formatting.
For Google Sheets: Go to Format > Conditional Formatting.

Make sure the rule is set to "Less than" and that the value is set to "0" (to look for cells that are less than zero), and choose a formatting option that will stand out. In this case, a red background with red text is a good choice.

If you're using Microsoft Excel, another option would be to apply a Currency number format that will make the negative numbers appear as red text.

That's it! Now we can easily see at a glance which items are over budget.

"Hey hey, this is great!

I'm going to show this to the Accounting department. The formatting really makes it easy to see where we need to cut back on spending. Which…looks to be just about everywhere this month. Anyway, thanks again!"