As part of our solving real-life problems in Excel series, figure out which expenses are over budget in this interactive.
Could you tell me which of our expenses are over budget? You can probably use our budget spreadsheet to figure it out. Thanks!"
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.
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.
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:
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.
…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:
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:
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.
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!"