The article was written by: Michelle Kaye
Do you remember a game show called Fear Factor? No - I’ll be honest and say that I don’t really remember the series, but I do remember the title.
While it was a stunt/dare game show, for many of us business owners there’s one program that’s almost guaranteed to have us shaking in our shoes:
Spreadsheets.
Let me share a ‘secret’ with you - all computers are just glorified calculators!
If you have a fear of calculators, then I’m not sure that I’ll be able to help, but if not, then read on (please).
Take a minute - what’s your current spreadsheet fear factor number? Let’s keep it simple between 1 - 5, with 5 being petrified.
Take a deep breath (or whatever works for you) and…
Giant Calculator
So if a computer is a large calculator, then a spreadsheet is the simplest thing to use - right?
You don’t believe me… 😯
OK - let me ask you this…
If I gave you a pen or pencil (your choice), a pad and a calculator - could you work out things?
(Fingers crossed you’ve just said yes)
Great - then you can use a spreadsheet.
No, really you can!
In the spreadsheet, write out the titles that you need, just like you do on that pad.
(leave some space you’ll need to write the details in)
Underneath each title write the details or specifics.
Next to it (in the next column to the right) type in your starting numbers.
So far, so good?
Right, now onto the maths.
Remember it’s a giant calculator - the biggest difference, we want the spreadsheet to do the hard work of working the answers out.
We’re going to write a formula.
Whoa - we’re keeping it simple here! It’s basically just what you’d enter into the calculator - but start it with the equals symbol (=) instead of pressing it at the end.
You might be wondering where in your spreadsheet you’re supposed to enter this. It’s where you want the answer to appear. Click on that cell, now you’re ready to start.Whether you’re adding up to get a total or subtracting the cost from the sales price, just follow the same practices that you’d use with that calculator.
=1000+94+47
=2000-543
Pro Tip - press Return or Enter when you’ve finished to see the answer.
Voila!
What makes a spreadsheet better?
I’m sure that this is a question you’re asking yourself (or perhaps me).
Well, how many times with a calculator have you typed the numbers in again and gotten a different answer?
Once or twice I’m guessing. 😁
Because you’ve typed all your figures in, and they stay on screen, you can go back and review them. Checking, correcting, or updating as needed.
But this doesn’t have a direct impact on your calculations - does it?
That’s because we need to make that formula you typed in flexible.
Let me ask you a question
What does 3 equal?
Bear with me here.
You’re scratching your head and thinking “What on earth is Michelle talking about?”
But what you’re really thinking is that 3 is just, well, 3!
And you’re right.
A number doesn’t change, but in business, our numbers are always changing - we hope to go up, but sometimes not.
To make our formula flexible we need to get rid of that fixed information i.e. the number.
OK, OK - a formula that says = + + + won’t help (or even work).
We need to replace that fixed number with something else.
In our spreadsheet, that’s the location where the number can be found, i.e. the cell address.
Whoa - wait a min Michelle what are cells or addresses?
Cell Address
Let’s deal with that now, shall we…
Your spreadsheet is made up of rows and columns.
Each row is identified with a number, while the columns have letters.
Where the two meet is a cell - the box that you type into.
Each cell is identified by its address - made up of the column letter and the row number.
(Yes, I switched it around - column first, as that’s the order that will work.)
Each cell has its own unique address - starting in the top left corner with A1 and moving down or right.
Flexible Formulas
Back to the previous point.
We need to remove that fixed number and replace it with a cell address that stores the number.
Your formula now looks something like this:
=B1+B2+B3+B+B6
Or
=B10-B5
Are you getting the same answer as you did before?
Yes - great
No - you *may* have mistyped something into the formula (check the cell addresses and was that supposed to be added or subtracted?)
Now you can check all the numbers you typed in (again?) and make any changes you want to.
The formula updates as you change things, so it’s always correct.
Remember a spreadsheet is a giant calculator - I promise you, you’ve got the right answer to what you typed in.
Ahh - you may (again) have typed something different to what you planned. But here’s the “joy” of a spreadsheet: We can ALWAYS change things.
Just go back, have a look at what you typed in, and if it’s not right, correct it.
Ready for the Next Level?
Well done, you’ve created a spreadsheet.
What’s your fear factor like now?
(Hoping it’s less)
Was that easier than a pen and a calculator?
(Again, hoping that you’re saying Yes or nodding)
But it’s only slightly easier than a calculator…
You’re still doing a lot of manual work.
So, the next level is to make sure of the built-in options to allow you to work faster (and perhaps smarter).
Ready?
These are Functions (a type of formula) that have been written to do the ‘heavy lifting’.
I’ll be honest - there are a lot of functions, but you don’t need all of them - just the ones that will help.
(Trust me, you really don’t want to know all the functions 😉)
Let’s start with the one that everyone likes to use.
Sum Function
I’m sure that you’ve already worked out that this function adds things together.
First question: What do you want to add up? And have you typed it in?
(You choose if you want to work going down the sheet, or across)
Next question: Where do you want the answer to appear?
Remember, that’s where we’re going to type in the function.
Now we’re ready to calculate
All formulas and functions start with equals (=).
Next we need to tell the spreadsheet which function we want to use - and it’s simply sum.
Pro Tip: when you type it in, use all lowercase. if the spreadsheet is happy it will Capitalise it, otherwise it’s left in lowercase.
So far, we’ve got =sum
(Still with me?)
Now we need to tell it what to actually add up. We give it that information in brackets ().
What goes in the brackets?
It can be the numbers but remember we want it to be flexible, so cell addresses, where the numbers can be found, is the best option.
I know, you’re wondering how to get all of that information into the function.
Want the easiest option?
You’re going to select it.
On the screen you’ve typed in =sum(
Now use the mouse, click on the first number to be added. Hold down the left button and drag it to the last number.
Your spreadsheet will automatically write the cell address(es) in for you.
Not having much luck with the click and drag option? (Sometimes the mouse just doesn’t want to move in the right direction)
Click on that first cell (again), now hold down SHIFT on the keyboard, move the mouse (don’t need to hold any button down), and click on the last cell with your number.
(You are still holding down the SHIFT aren’t you…)
And voila - the cell addresses are added in.Best practice - type in the closing bracket and press return
Quick way - press return
And guess what… you’ve written your first function!
How did it feel?
Ahh - here’s the clever bit…
Go and change the numbers.
What’s happening to the result?
Updating right… and that’s the beauty of using functions (easy to use and does the hard work for you) and cell addresses (it allows them to always be reading what you’ve typed in).
Final question - how’s your fear factor now?
Wow - a minus figure, I’m impressed!
Next step - the world. Oh OK, just perhaps a bigger and better spreadsheet 😉
About The AuthorMichelle Kaye has spent the last 25 years in front of computers - mostly in her role as a computer trainer.
After being an in-house IT trainer (helping staff), Michelle realized that she wanted to help more people understand that tech and computers aren't hard - they aren’t a mountain to climb!
She can help you save hours of yelling at your computer (cough spreadsheets cough) with actionable and personalized answers to your specific questions. For more computer learning tips from Michelle, visit her Learning Blog.
0 Comments