Grasp the potential of functions and formulas
Functions and formulas are very useful because:
They can quickly give you more information than you previously had, like a sum or an average.
They are more efficient, in that you don’t have to make the calculation yourself.
If the value of one cell changes later on, using a function or formula means your result will automatically get updated. The spreadsheet fetches the right value, and the resulting value will be updated automatically.
Write formulas
To enter a formula, you need to start by checking the references of the cells you want to include ( A6, A7, A8, for example), then choose your result cell (A9, for example), and finally type an equal sign in there, followed by your cell references.
It could look like this =A6*A7+A8 .
Write functions
Functions follow the same rules (using the equals sign and no spaces), but always start with a word. You can either:
Type this word in yourself, and the program will recognize which calculation you aim to use, or
Select a function by navigating to Insert > Functions. From there, you’ll see the most common functions.
Once you've written out the name of the simple function, you need to:
Open a parenthesis.
Either select individual cells or a range of cells (OR write out their reference).
Close the parenthesis.
Release or click enter for the result to appear.
The spreadsheet helps you every step of the way with a cheat sheet of what you have to enter:
If you are in a result cell and you want a reminder of the formula or function behind it, select the cell and check out the formula bar.
This gives you a visual display of the function or formula, and cells that are used.
Use common functions
Common functions include =SUM(), =AVERAGE(), =COUNT(), =MIN(), and =MAX(), which we’ll cover now.
SUM
The sum is the total of selected cells. The most efficient way to identify cells, if they are right next to each other, is to drag the cursor over the range of cells you want to total.
Here is a quick video of the SUM function in action:
AVERAGE
Averaging takes the total of the values divided by the number of cells. You don't need to specify the number of cells because the spreadsheet does this leg work on its own!
COUNT
COUNT() provides the count of the number of cells with numeric values in them in the selected range. It's close sister function is COUNTA() which counts the number of non-empty cells, whether they are filled with numeric values or text.
A few extra functions for the road
MAX | = MAX (start cell:end cell ) | Finds largest number in specific range |
MIN | = MIN (start cell:end cell ) | Find smallest number in specific range |
TODAY | = TODAY() | Returns the current date |
UPPER | = UPPER (cell with text) | Converts text to uppercase |
LOWER | = LOWER (cell with text) | Converts text into lowercase |
Let’s recap!
To enter a formula or a function, start by typing an equal sign into the cell where you want the calculated value to be.
Make sure not to include any spaces inside your formula or function as this will create error messages!
A formula is an equation (like math) which can be designed by a user.
A function is a predefined calculation in the spreadsheet and relies on words to instruct the spreadsheet on which calculations to make.
Once you've written out the name of the simple function, you need to:
Open a parenthesis.
Either select individual cells or a range of cells (OR write out their reference).
Close the parenthesis.
Release or click enter for the result to appear.
Now that we’ve covered the basics of formula and functions, we’re going to go deeper with IF: a pretty powerful spreadsheet function.
