We all use Excel to keep lists.
And when it comes to lists, Excel does a great job. But the real Excel ninjas are people who have mastered the world of formulas. Using data well is all about executing the calculations that will tell you what you need to know, and formulas do those calculations, molding your data into something useful and illuminating. If you know your formulas, you can really make your numbers sing.
It’s your last night in New York City on a vacation you’ve taken with your friends Bob and Sasha. You’ve had a great time and really enjoyed the city.
But you’ve also spent plenty of money, and now the three of you want to see if you have enough left to go to a nice restaurant on your last night.
You’ve been handling expenses by having only one of you pay at a time. Instead of splitting up every check at every restaurant, you all figured you’d settle up later.
Now you need to settle up and decide whether you have any more money left in your budget for a big meal.
People often use Excel to keep permanent records of their data. The program is a great way to take a snapshot of your data and thinking at a certain point in time.
With your budgeting calculations set up in an Excel spreadsheet, you’ll be able to show your friends exactly how you came to your conclusions about how you should split up the expenses.
...but Excel is at its most powerful when you use it to crunch numbers.
To add up and divide what you spent, you use formulas.
Say you want to add together the totals from the two bills on the right. Here’s the formula you’d use.
Excel has a large variety of formulas you can use to make calculations, from basic addition to highly specialized engineering and statistical tools.
Exercise
Open Excel and write the formula that adds up the total you all spent together. Remember, Bob spent $61 and Sasha spent $296. You spent $332.
Exercise Solution
You just typed a formula to calculate how much you and your friends spent in total. What did you find?
Now you just need to split up the grand total among the three of you.
And that means dividing the $689 total you spent by three. What symbol will you use to make this calculation?
Who Does What?
Match each operation on the left with the formulas that implement the operation on the right.
Exercise
Write the formula to split up the total you spent ($689) among the three of you.
Have you spent less than your budget?
________________________________________
________________________________________
Nice, Bob. It looked like we had everything figured out, but now that he left out something from his total, we’re going to have to go back and fix our numbers.
Maybe he’s right: instead of getting totals from Bob and Sasha, we should just take a look at all the receipts. The total you find might be more accurate that way. On the other hand, that could be even more work....
Bob and Sasha sent you all their receipts. Combined with your own receipts, the final list looks like this.
You don’t have to write your formula like this:
This formula is hard to read, and even more importantly, it’s hard to change if a data point turns out to be wrong.
Instead of writing a long string of numbers like this, you can use references. References are a shorthand that Excel uses to look for values. For example, if you tell Excel to look at the reference B2, it will return the value 66, because that’s what it finds at B2.
Exercise Solution
Write the formula that adds your bills together using references.
Write a formula to determine whether you are still under budget and, if so, by how much.
=400-E5___________________________________
I’m still under my budget of $400.________________
__________________________________________
Sasha came up with her own formula, but when she ran it she got a different answer from yours. Here’s her formula:
=(B2+B3+B4+B5+B6+B7+B9+B10)/3
And here’s the answer she received from it.
$192
One really important skill for Excel users is the ability to go back and look carefully at formulas that have been already written. Formulas might look complex and long, but that doesn’t mean that they’re correct.
Be patient when you look at formulas and pay close attention to their references. One small mistake will usually create a false result.
You can point to a list of references in Excel using a range. A range is simply two references with a colon between them, and the colon tells Excel to look at every cell in between those two references.
For example, say you want your formula to do something to this list of references.
By placing a colon between B2 and B10, you tell Excel to look at those two cells and everything in between.
In order to make your ranges work, you need to pass them to formulas that know what to do with them. You can use the SUM function to add together all the cells in your range.[1]
Most functions consist of a word followed by parentheses that contain one or more arguments. They often need arguments in order to know where to look to get the data they need to evaluate.
How would you go about splitting up all the bills?
Since you’re going to the trouble to create a spreadsheet for your dining expenses, you might as well go ahead and break each check down individually. This approach will give the most equitable results.
Here are the receipts for the meals you bought yourself.
Exercise
Here is a spreadsheet to help you split up the expenses for the restaurant bills you paid for.
Fill in the blanks for Bob and Sasha using the values on the facing page.
Now, write a formula to calculate your share of meal #1. Instead of adding elements from the facing page, just subtract what Bob and Sasha spent from the total.
Select the formula you just wrote in cell
E4and copy/paste it for meals 2–4. What happens? Look at the references for each result.
Exercise Solution
You just calculated the breakdown for each bill you paid. What did you find?
Fill in the blanks for Bob and Sasha using the values on the facing page.
Now, write a formula to calculate your share of meal #1. Instead of adding elements from the facing page, just subtract what Bob and Sasha spent from the total.
Select the formula you just wrote in cell
E4and copy/paste it for meals 2–4. What happens? Look at the references for each result.
This feature of formulas is really useful because you can write just one formula to do a whole lot of different stuff.
It actually would not have taken you long to write three more similar formulas to calculate your share of the meals you bought. But what if, instead of four meals, you’d bought a hundred or a thousand meals? In that case, being able to copy formulas with automatic reference shifting would be a big help.
Long Exercise
Here are summaries of what each of you spent.
Below these three tables on your spreadsheet, write the formulas to answer these questions. Be sure to put text labels in the cells next to each formula to remind you what they mean.
Write the formula that shows what you spent on Bob.
Write the formula that shows what Bob spent on you.
Using the above two formulas, write a formula that shows what Bob owes you.
Write the formulas to show what Sasha owes you.
Write the formulas to show what Sasha owes Bob.
Now how are you doing relative to your budget of $400?
_________________________________________________
You can use spreadsheets as a hugely powerful tool with formulas. You might want to run a simple calculation, or you might need to build a really elaborate system of formulas to help you find the answers you need.
No matter which approach you choose to take, Excel is ready with the formulas you need to get the job done.
Your friends loved your spreadsheet and used it to figure out how they are doing relative to their own budgets. The verdict: everyone has plenty of money left.
With $197 burning a hole in your pocket, you are ready for a crazy night out with your friends in one of the most exciting cities in the world!
[1] SUM is a “function,” and the real implementation of a function (like =SUM(B2:B10) is a “formula.”
Get Head First Excel now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
