[go: up one dir, main page]
Scribd
Upload
6 views4 pages

Excel Formulas - Over-Budget Items

Uploaded by

wba59179
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views4 pages

Excel Formulas - Over-Budget Items

Uploaded by

wba59179
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

TEXTBOOK REFERENCE: Form 4, Unit 1. Pages 2-10.

Senior Secondary Computer Studies, Andrew Nasalangwa.

Your videos are excellent:


short, sweet, and to the
”
point.

Online Learner

All Topics Excel Formulas Over-Budget Items

Excel Formulas
Over-Budget Items

Page 1

"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 overbudget_practice.xlsx


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-2013: 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!"

Terms Of Use
©1998-2014 Goodwill Community Foundation, Inc. All rights reserved.

You might also like