**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.

**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`

.

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.

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!

^{[1] }`SUM`

is a “function,” and the real implementation of a function (like `=SUM(B2:B10`

) is a “formula.”

Start Free Trial

No credit card required