How to use mathematical formulas in Google Sheets
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 anice Chromebookwouldn’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.
That’s neat but not exactly useful. If we wanted to display4in 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, whereSalaryandTaxesare 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 - D2into 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:
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 could 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 atthe full list of functionsavailable 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.
The note-taking app I should have used all along
Broader branding hints at wider paid-tier ambitions
$135 is its lowest price in months
Generative AI, now poolside
What’s new? A lot
It’s been an interesting journey
