We all know that Google Sheets is a spreadsheet program. Spreadsheets = numbers and maths. At the end of the day we want it to be easy. To help with that Google Sheets has functions to speed things up. Let’s look at the popular functions in Google Sheets that you’ll want to know about.
Formulas and Functions
Let’s start with what’s the difference between these two options.
Formulas
In a spreadsheet, you need to tell it that what you’re entering is not text or numbers to be displayed. They are actually something to be calculated.
The general term is a Formula.
To identify that what you’re entering is a formula to be worked out, all formulas start with equals (=).
Without the equals sign, your formula is displayed on the screen, rather than the result.
One of the key things about formulas is that they can be anything that you want them to be. You write them to achieve the outcome or calculation that you need.
Functions
When you think about it, there are a number of things that we all want to do within a spreadsheet.
As they say “you don’t have to reinvent the wheel”, and that’s true within Google Sheets.
For a lot of the most often used calculations, Sheets has pre-written them, ready for you to use.
It’s these specific calculations that are called Functions.
While all functions are formulas (they all start with the equal sign (=), not all formulas are functions. i.e. the ones that you write are formulas.
Due to the sheer number of available functions, each one has a unique name to identify it.
When you want to use a function, your enter it’s name after the =, and then inside rounded brackets (), include the information that it needs to work out the answer.
Note: Most of these Google Sheets functions, are in fact available in most spreadsheet programs – the only exception being Google Translate. 😉
Function Help
Do you now that there’s help (for all the functions) built into Google Sheets?
Start typing in the Function
=<Function_Name>
Click on the blue question mark on the left.
It expands to show you information about the chosen function, including
- the order of expected information
- an example of a completed function
- what the function does
- an explanation for each of the function parts
Popular Functions in Google Sheets
Let’s start this list, with the most used or most popular functions available. You’ll see these ones in pretty much every spreadsheet program, no matter who wrote it.
Using functions in Google Sheets is pretty straightforward. For each function I’ve included not only what it does, but the information that it requires to work out the answer.
Standard/Simple Functions
What we can call simple or basic functions are the ones that we most often use. I only call them basic functions, because they are the easiest ones to use.
Note: Range is the same for all of these functions, so I’ve only explained it for Sum.
SUM
=SUM(Range)
Probably ones of the most well know and used of all of the built-in functions. Put simply – it adds things up.
Range is the group of cells where the information can be found. Normally the first cell and the last.
For horizonal and vertical ranges first and last are obvious. When you’re talking about a range in both directions, we use the first (top left) and last (bottom right) cell addresses.
You could type each number directly into the function (separated by commas) but it’s a long way to do it.
Average
=AVERAGE(Range)
Shows the average of all of the given numbers. Adds them together, and divides them by the number of items.
Min / Max
=MIN(Range)
What is the smallest number in the given range.
=MAX(Range)
What is the largest number in the given range.
Note: Neither of these functions shows you which cell holds the number, only the answer.
Count
=COUNT(Range)
Need to know how many items are in the range – use Count.
Note: It counts numbers only.
Basic Functions – Alternatives
The basic functions are useful, however, some of them have alternatives that expand on the original use.
In some cases (and other functions), you’ll see square brackets [], inside the round ones when you look at a function. This means that’s it’s optional – the function will work without it being entered. But before you ignore them, take a read – as they might be useful.
SumIf
=SUMIF(Range, Criterion, [SumRange])
While Sum adds up all the numbers in the given range, SumIF allows you to add up specific information (that you specify).
Range – of course, is the cells to look at.
Criterion is what Google Sheets should be looking for, to add up. This can (like so many others), be either the information or a cell reference where the information can be found.
CountIf
=COUNTIF(Range, Criterion)
While the basic version has, I think, limited scope, these alternative can be really useful.
Range is the section of cells (remember first and last) that you want the function to look through.
Criterion is either the information that you want the function to count, or the cell that contains that information.
CountA
=COUNTA(Range)
Have you noticed that Count will only count the cells that have numbers in them? While that has a use, being able to count all the cells in a range can be useful as well.
If you haven’t guessed, the A in CountA stands for All.
Other Popular Functions in Google Sheets
Once you’ve got the basic functions all sorted you can move onto the rest. And there’s a lot to choose from. Depending on what you actually want to do (or need to do), pick and choose. You don’t need to know everything. 😊
The main difference for most of the other functions is that they need more information inside the brackets.
Today
=TODAY()
A really easy function, but one of those that can be really useful.
In a cell type in the formula, no additional info needed. When you press Return, today’s date – in the format based on your location – appears.
Tip:
As this is a function, the cell will always show today’s date, not the date that you entered the function.
GoogleTranslate
=GOOGLETRANSLATE(Text, [Source_Language], [Target_Language])
You’ll be unsurprised to learn that this function allows you to take advantage of the translation services offered by Google.
Note: This is an example of a function that has square brackets, the language information is optional.
Text is either the text that you want translated, or the cell address of the text.
Source_Language – if the language used for ‘Text’ isn’t obvious, or you want to be specific enter the language in.
Note: You don’t need to type in the square brackets, but you do need to put double quotes (“”) around the language as it’s text.
Tip:
For the language, Google Translate is expecting a two character code to be entered. E.g. “En” not “English”.
A full list of codes can be found here: https://cloud.google.com/translate/docs/languages
Target_Language – is (if you haven’t already guessed) the language to be translated into. Again use the two character code to specify which one.
Next Steps
Interested in learning more and working with me? Take a look at my Everything Page for all of your options.
Related Posts
- How to choose your favourite online spreadsheet
Unsure of which online spreadsheet program to choose? Find out here the features and benefits of both Microsoft’s Excel and Google Sheets, as well as some tips on how to pick the right one for your needs. - How to deal with duplicates in Google Sheets
Learn how to deal with duplicates: find, highlight and remove duplicates from your spreadsheet in a few easy steps. - Popular Formulas and Functions in Google Sheets
Learn about the top 10 popular formulas/functions to use with Google Sheets. How to find them and description of each purpose and examples. - How to Increase Cell Size in Google Sheets
Do you have a lot of data that doesn’t fit in your current cell size? Here’s how to increase the cell size in Google Sheets. - Merge Cells in Google Sheets – when is it useful?
Learn when to merge cells in Google Sheets, and how to do it quickly and easily. This guide will show you the benefits of merging cells and some common use cases.
Popular Functions in Google Sheets, using functions in google sheets, google sheets functions