In yesterday’s post, we talked about using Microsoft Excel efficiently. You learned some fun finesse tools for moving around the program and laying out data. If you read the comments, you learned even more because there were some great questions down there!

Today, in our *Bonus Wednesday *(I almost never post on Hump Day) we’re going to get to the nitty-gritty part of Excel. We’re basically going to “just skip to the good part.”

Note: If any of you have ever tried to post a blog with a toddler climbing all over you, you’ll know why I’m late putting this up today.

**What is the most common use of Excel?**

There are many people who use Excel just to keep lists. And that’s OK. A spreadsheet is a fine place to keep a list! You can sort this list, filter it, Subtotal it. All these list-y things are built into the program and they’re cooo-o-o-ool. But as cool as they are, a list can’t add up your numbers, unless you learn to really dial in Subtotals. You need formulas to add up numbers.

**This bonus Wednesday post is going to cover Formulas in all their glory.**

After 15 years of teaching, I’m convinced that Formulas and Functions are the most popular words in Excel. They’re like solar panels – everybody *wants* them but no one quite knows how to set them up.

**Some important things to know about Formulas:**

- If you know basic math, you can do a formula
- Formulas and Functions are two different things
- All Excel Formulas and Functions start with “=”
- Just like your stories, you will usually catch errors if you read them out loud.

Formulas are made up of the following symbols (called operators):

There is a default order in which calculations occur (called the Order of Operations), but you can *change this order* by using parentheses.

The default order of operations is that you Multiply and Divide before you Add and Subtract. That means that 3+4*10 will equal 43, rather than the 70 that some of you hoped for. How do you get the result of 70 for the above numbers?

(3+4)*10 will equal 70. THIS is what that blue paragraph above means – you can control the order with your parentheses. In Excel this would read:

**= ( B4 + C4 ) * D4**

My final thoughts on the whole “order of operations” thing…

The Negation, Percent and Exponentiation are actually the highest in this order (meaning they come before everything else). But since most of us don’t even really know what Exponentiation *is* (i.e. In 15 years of formula writing I’ve *never *used it), I’m going to tell you to chuck it out the window and focus on the following rule of Excel:

**Multiply (*)and Divide (/)
**BEFORE you

**Add (+) and Subtract (-)**

**Do you have to know math to be good at Excel? **

I’m going to confess something here…I have a really weak math muscle. I can put logical things together with the best of them but in high school, when we wandered from Algebra Land into Geometry Land (or worse, Trigonometry Land *shudder*), it gave me the Learning Trots. I don’t know how to describe it any better than that. Things just stopped working correctly on the learning front and I was either spewing wrong answers or I was completely blocked up.

When I started using Excel, I was terrified of it, because I thought I had to know math.

Um, no.

*Excel is there to take the math out of the equation and do it for you.* You just have to know how.

If you always start a formula with “equal” ( = ) and use the parentheses to group your order of operations properly, all will be a piece of cake on the Excel formula front. That brings us to Functions…

**Strap yourself in for the ride, people, we’re about to pass from Basic Arithmetic Land into Algebra Land…**

(Stop whining – you don’t have to *actually* know algebra, but Functions share the look and some of the principles of algebra.)

**A Function is a preset formula in Excel. **

That’s it…the big Function secret – it’s built into the program so you don’t have to make it up in your head like a formula.

Like formulas, functions begin with the equal sign ( = ) followed by the function’s name and then some parentheses around the range of cells you choose. (If you want to get technical, what’s inside the parantheses are called “arguments” – since most of us are writers, we’re calling this the “range of cells.”)

The function name tells Excel what calculation to perform. For example, the most frequently used function in Excel is **the SUM function**, which is used to add together the data in selected cells (in the example below, cells D1 through D6). The SUM function is written as –

= SUM ( D1 : D6 )

**Other popular functions are:**

- Averaging a group of numbers, called a range: = AVERAGE ( D1 : D6 )
- Getting the lowest number in a range: = MIN ( D1 : D6 )
- Getting the highest number in a range: = MAX ( D1 : D6 )

You remember the Name Box from yesterday, right? Well to the right of the Name Box, up on Excel’s formula bar is the Insert Function key, which looks like “fx” (see below):

If you click the Insert Function key button, and “=” sign shows up in the Formula bar, a check mark and an “X” appear to the left of the “fx” key *and* the Insert Function dialog box appears:

There’s enough functions available in the Insert Function box to keep you busy for weeks if you catch the Excel bug – just click the drop-down arrow next to Most Recently Used and you’ll see tons of ’em.

When I first found this place, I wanted to shoot my old Trigonometry teachers for wasting my time – all that sine and cosine business is *right here*. (My apologies to all you architects who actually *use* all that dreaded trigonometry the rest of us don’t need.)

**Can you type your own Functions in Excel?**

Sure you can…start typing right inside a cell as show below or up in that Formula Bar to the right of the Fx button. I find it easier to type directly into the cell.

If I were to break the function above into stages, it would read as follows:

- Go to the cell where you want the total number of books sold.
- Type “=”
- Type “SUM”
- Type an open parenthesis “ ( “
- Take your mouse and highlight the cells you want to add. A cell range will read as you see above “B4:D4”, which reads “B4 through D4”.
- If you’re finished, you can just hit the Enter key and Excel will add the closing parenthesis. If you prefer to type the “ ) ” feel free to do so.

Presto, you’ve done your first Function! There’s certainly more, but we’ll cover it next Techie Tuesday. My plan is to get to those time savers I’ve been hinting at. We just had to make sure you felt comfortable with the basics first.

What do you think? Do you think you can find a use for this program that some of you have been avoiding? Yesterday’s readers asked some questions and I hope you do too. Are there some Functions you’ve been dreaming about using? It’s OK, we won’t laugh at you for geeking out…you can tell us what they are down in the comments. 🙂

Have I mentioned lately that I love hearing from you?! I dig it so much, I created the Let’s Meet Up Contest where your comments enter you into a drawing for an hour-long webinar of partying learning.

*Jenny*

I love me some Excel. This post makes my geeky heart sing!

I just revamped one for a friend’s company. Nested IF statements, vlookups and all. Also, I do not love math. Just to reiterate your point that math doesn’t have to be your thing.

LikeLike

We’re kindred spirits on this one, Amber. I think I love Excel more than my advanced-math brethren because it saved me from something I was so bad at!

Nested IF statements…*swoon*

LikeLike

Another great break down of Excel Jenny!

LikeLike

Thanks, Raelyn! Since you love Excel, you’ll be a very happy camper next Tuesday. 🙂

LikeLike

Great job Jenny. I will post this link for my blog as well.

BTW, I love the story about Trig. My Dad made me take Trig and Elementary Analysis. I had to have a tutor. That’s okay, she turned out to be the Valedictorian of our class. While I stayed in the middle-middle. Good at English lousy at Math.

LikeLike

We’re in the same boat on this one, Lynn, and it’s a shame because I loved Algebra and think I would have really enjoyed Calculus. But trigonometry broke my math spirit first. I didn’t love Geometry but at least you *kind of* use it when you play pool. I like to play pool!

LikeLike

I actually liked Geometry but Trig…eh yuck. (BTW, I have a three year-old Godson that I live with so yes I understand about the toddler affect).

I tried pool once when I was still in basic training at Lackland AFB about…hmm over 30 years ago. I had a couple cute guys trying to show me how.

LikeLike

Re: your second comment, Lynn…

Baby Girl is almost 14 months old and thinks Mommy is the best person to climb on! Then there’s the computer, the phone, the coffee, etc. to explore. I schedule most of my blogs at night so the hour-long process doesn’t turn into three. 🙂

p.s. There’s no one better to learn pool from than some cute guys, in my humble opinion….

LikeLike

Thanks for this, Jenny! I’m self-taught in Excel, through the help of a couple of books, and I always found the calculation part the toughest. This explains it clearly for me.

LikeLike

You’re welcome, Julia! Formulas are the number one thing people want to learn when they come to training and there’s some basics to cover first. Did you go look at yesterday’s post to make sure you know all those Excel tricks too?

LikeLike

Calculations in excel always throw me for a loop so great post for me. I’ll mark as a favorite. Thanks for the training!

LikeLike

You are very welcome, Angela. Trust me, they throw most people for a loop. 🙂

LikeLike

Gonna put this in my “save for later” file when my brain actually works. This is a keeper–again!

LikeLike

Thanks, Amy! That’s good because I keep a lot of yours too. 🙂

LikeLike

Studied excel before but there are new functions that wasn’t available in the old excel. Thank you for this.

LikeLike

You are most welcome, Marilag!

LikeLike

Pingback: Should Writers Use Excel – Part 3 | Jenny Hansen's Blog