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 (/)
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.
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.