Even if you don’t work with or around spreadsheets on a daily basis, you likely understand the basics: data (names, numbers, dates) are placed into cells and organized into rows and columns. Although this may be the limit of most people’s understanding of spreadsheets, you need to push beyond this if you want to cross the line from the mundane world of data entry to the realm of data manipulation. Having a nice Chromebook wouldn’t hurt, either.
Foundations of spreadsheet wizardry
The first thing to understand is that there are two fundamental ways to put data into a cell: values and formulas. Values are the raw data (the names, numbers, and dates). Without data, there is nothing for the magic of formulas to act upon.
Formulas are equations and functions that manipulate the data so that useful information can be extracted from them. Equations are just math. You likely won’t use something as simple as 2 + 2, but that’s the basic idea.
How to use Google Sheets formulas
To input a formula into Google Sheets, preface what you type with the equal sign (=). Let’s look at two examples to see the difference this makes.
- If we input 2 + 2 into cell A1 and press Enter, that’s precisely what Sheets displays.
- If we change our input slightly to =2 + 2, instead of displaying what we input, Sheets evaluates it as if it were a math problem.
That’s neat but not exactly useful. If we wanted to display 4 in cell A1, it would have been easier to input that rather than a formula. What we can do instead of using numbers is use the numbers that are in other cells by referring to their location. To calculate our after-tax income, we subtract how much we paid in taxes from how much we made. To pull this off in Google Sheets, we can use the formula =Salary – Taxes, where Salary and Taxes are the cells with those values.
In this case, our salary data is in cell C2, and our taxes are in cell D2. To calculate the after-tax income, we need to input our new formula =C2 – D2 into cell E2. As you type the formula, Sheets highlights the cells you are referring to.
At first glance, this might seem like nothing more than a time-saving trick, but we’re not calculating $80,000 – $10,170. We’re calculating the difference between the values currently in C2 and D2. Any changes we make to C2 or D2 automatically cause E2 to change.
Let’s look at a useful trick before we move on to functions. We’ve done a good job of calculating the take-home pay for one employee, but what if there’s more than one employee?
Do we have to enter a variation of the same formula over and over for each employee? No. There is an easier way. Notice the blue square in the lower-right corner of the highlighted cell. Click and drag the square to highlight the empty cells in which we want to input our take-home pay formula, and Sheets fills in the missing formulas.
How to use Google Sheets functions
Formulas are the basic syntax of data manipulation in Google Sheets. Still, functions are the vocabulary, and understanding how to use them unlocks the powerful functionality Sheets hides under its hood.
A function is a short way of writing a complex mathematical equation. Let’s look at an example to better understand what this means.
To know how much our annual payroll budget will be, we could use a simple, albeit tedious, formula =(C2 + C3 + … + C10 + C11) to find the answer.
This method works, but Sheets has a built-in SUM function that does the typing for us behind the scenes. Here’s how it works:
- As with a regular formula, preface it with the equal sign (=).
- Type the name of the function you want to use, in this case, SUM.
- After the name of the function, type the argument inside parentheses. In mathematical terms, an argument is the information needed by a function to complete its calculation. In this case, our argument is the range of numbers from C2 to C11. In Google Sheets, put a colon (:) between two values to indicate you mean every value between those two cells.
- When everything is entered correctly, it should look like this: =SUM(C2:C11).
Sheets guesses what you’re trying to do and helps you along the way. This is useful if you can only remember the name of the function you want to use but can’t remember how to format your arguments.
Some parting advice
Now that you know the basics of using formulas and functions in Google Sheets, it’s time to put them into practice so that you can master them. Don’t be afraid to mix and match formula terms, either. For example, if you’re a freelancer, you may need to sum your hours, then multiply that by your hourly rate to know how much you’re owed: =SUM(first_project:last_project) * hourly_rate.
You can also look at the full list of functions available in Google Sheets. You might find something useful there. And if you need a few more tricks to put up your sleeve, check out our top eight.
