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.
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.
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.
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.
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.
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
And here’s the answer she received from it.
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.
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.
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.
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!
SUM is a “function,” and the real implementation of a function (like
=SUM(B2:B10) is a “formula.”