In Part 1 of this series, we talked about using Microsoft Excel efficiently. You learned some fun finesse tools for moving around the program and laying out data.
Today, we’re going to get to the nitty-gritty part of Excel. We’re basically going to “just skip to the good stuff.”
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 here in California – 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, in 15 years of formula writing I’ve never used it…so we’re skipping it.
Focus on the following rule of Excel when you do formulas and you’ll be fine:
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 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 do the math 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 parentheses 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 Part 1, 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 in Part 3 of this series. We’ll get to those time savers I’ve been hinting at, but I wanted you to feel 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? Part 1’s readers asked tons of 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. 🙂
About Jenny Hansen
Jenny fills her nights with humor: writing memoir, women’s fiction, chick lit, short stories (and chasing after her toddler Baby Girl). By day, she provides training and social media marketing for an accounting firm. After 15 years as a corporate software trainer, she’s digging this sit down and write thing.
When she’s not at her blog, More Cowbell, Jenny can be found on Twitter at JennyHansenCA and here at Writers In The Storm. Jenny also writes the Risky Baby Business posts at More Cowbell, a series that focuses on babies, new parents and high-risk pregnancy.
Now there’s a wild-eyed risk if ever I’ve seen one: math and computers for authors!
Here’s my trick for dealing with computational priority (the order stuff gets calculated in) — use parentheses. If you just group stuff inside parentheses, you can get away with murder in a calculation.
And then you can get back to getting away with it in your writing.
I love to use Excel’s conditional sum feature. Put together a spreadsheet with all the calcs for, say, our move to Ireland, and then play with the numbers. Excel will figure out, on the fly, how many web jobs it’ll take, how many publishing clients, how much in book sales, to reach our financial goal. It’s quite the groovy tool.
LOL…wild-eyed risk indeed. You had me at “computational priority.” 🙂
I love Conditional Sums too and Excel 2010 added Conditional Formatting options that make my heart sing.
Jenny, I went from math land to algebra land to la-la land. I probably have a half dozen reasons to use Excel. But the five dozen reasons it gives me a headache keep pounding in my brain. Love the organized and wonderful way you teach. Yet, with ten fingers and ten toes, I think I’ll plod along … inept, disorganized and happy to avoid learning yet another software 🙂
The last thing I’d want is to give you a headache. I promise Part 3 will be a little less technical. I’ll get you over to the dark side yet, my pretty Florence!
And my little dog too ??
Yep, that one too. 🙂
I feel like I’m back in HS math and chemistry classes. I’m the kid whose Chemistry teacher sat her by herself in an isolated corner during tests because she kept helping the folks around her during tests. Anything math or logic or foreign language related popped my kettle corn.
That is one of the reasons I had severe self-doubt when it came to defining myself as a writer. English and history classes were boring in school. How could I have a logic lovin’ mind and write creatively? *shrugs shoulders*
Pay attention to Jenny, folks! Excel rocks! For me it’s as much fun to invent a new, complex formula as it is to solve a complex Sudoku puzzle. That is to say; I love it.
I realize that’s an anomaly for a writer, but if The Jenny Hansen can show her geek in public, I may as well admit mine.
THE Jenny Hansen, huh? You’re making my day, Gloria!
And I’m beyond impressed with that strong math muscle of yours. My hubby’s good at math and I’m hoping Babykins takes after him. I’m good at software and languages so Excel taps into that (THANK GOD).
It’s that logic-brain strutting its stuff, Jenny. You’ve got that in spades when it comes to techie talk. And, yes, THE Jenny Hansen…
Get out’a town … do you mean you are a closet geek, Gloria?
I toted my NYT Electronic Sudoku in my purse for writing “breaks” until I did a time/activity analysis. Yup! Closet geek on all things math and logic related, Florence.
Can we still be friends?
That’s so freaking funny to me…
Functions, they’re a wonderful thing. I personally have a crush on the vlookup formula. 🙂
VLookups are pretty sexy. My accountants use it all the time for billing and tax rates. It’s great for HR types of things too. 🙂
It’s a good thing Excel will do the math for me. I can calculate a percentages but anything else and I run into trouble. Thanks for this blog Jen. It’s going to my how-to folder. 🙂
Oh yes, Excel can do all that pesky numbers stuff. The things you do with your bills and with trying to figure out refi numbers, etc? Excel will help with all that! I can see you and Pivot Tables having a love/love relationship too as your indie career gets off the ground. 🙂
Pingback: Techie Tuesday and Some Thoughts On NaNoWriMo | Jenny Hansen's Blog
Pingback: Thirsty Thursday Blog Round-up | Writing, Reading, and Life
Pingback: Writing Blog Treasures 12~8 | Gene Lempp ~ Writer
I loved math and always did well with it. excel is wonderful – you can do so many good things with it. thanks for the reminders
Pingback: Writerly Uses for Excel – Part 3 | Writers In The Storm Blog