O'Reilly logo

Head First Excel by Michael Milton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 1. Introduction to Formulas: Excel’s real power

image with no caption

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.

Can you live it up on the last night of your vacation?

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.

image with no caption

Here’s what you budgeted and what you spent

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.

image with no caption

Now you need to settle up and decide whether you have any more money left in your budget for a big meal.

image with no caption
image with no caption

Excel is great for keeping records...

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.

image with no caption

...but Excel is at its most powerful when you use it to crunch numbers.

Formulas work with your data

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.

image with no caption

Excel has a large variety of formulas you can use to make calculations, from basic addition to highly specialized engineering and statistical tools.

Looks like Bob forgot a receipt...

image with no caption

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

Your friends sent you all the receipts

Bob and Sasha sent you all their receipts. Combined with your own receipts, the final list looks like this.

image with no caption

References keep your formulas working even if your data changes

You don’t have to write your formula like this:

image with no caption

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.

image with no caption
image with no caption

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

Check your formulas carefully

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.

image with no caption

Refer to a bunch of cells using a range

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.

image with no caption

By placing a colon between B2 and B10, you tell Excel to look at those two cells and everything in between.

Use SUM to add the elements in a range

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.

Bob and Sasha wonder whether we’ve been taking the right approach...

image with no caption

How would you go about splitting up all the bills?

Your friends agree: split the checks individually

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.

image with no caption

When you copy and paste a formula, the references shift

This feature of formulas is really useful because you can write just one formula to do a whole lot of different stuff.

image with no caption

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.

image with no caption

Excel formulas let you drill deep into your data

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.

image with no caption
image with no caption

No matter which approach you choose to take, Excel is ready with the formulas you need to get the job done.

image with no caption

Everyone has plenty of cash left for a food-filled night in New York City!

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!

image with no caption


[1] SUM is a “function,” and the real implementation of a function (like =SUM(B2:B10) is a “formula.”

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required