Spreadsheets offer a great platform to manage data tables. But are there any easy Google Sheets functions for daily calculations? Find out below.
I believe the most required operation in tables is to find the total sum of different values. The first thing that comes to mind is to add every single cell of interest:
But this formula will become extremely time-consuming if there are too many cells to take into account.
The proper way to add cells is to use a special Google Sheets function – SUM – that lists all cells automatically using commas:
If the range consists of adjacent cells, simply indicate its first and last cells even if there are blank ones somewhere between. Thus, you'll avoid enumerating each and every cell in Google Sheets SUM formula.
Tip. Another way to add SUM is to select the column with numbers and pick SUM under the Formulas icon:
The result will be inserted to a cell right below the selected range.
Tip. Our Power Tools has an AutoSum feature. One click – and your active cell will return the sum of values from the entire column above.
Let me complicate the task. I want to add numbers from different data ranges on multiple sheets, for example, A4:A8 from Sheet1 and B4:B7 from Sheet2. And I want to sum them in a single cell:
As you can see, I just added one more sheet into Google Sheets SUM formula and separated two different ranges by a comma.
I often hear people ask about finding the percentage of different totals. This is usually calculated by the Google Sheets percentage formula like this:
The same also works whenever you need to check what part this or that number represents of the total:
Tip. Master percentage of a total, total & amount by percent, its increase & decrease in this tutorial.
In my table where I keep records of all sales for the past 10 days, I can calculate the percentage of each sale from the total sales.
First, I go to E12 and find total sales:
Then, I check what part the first day sales constitutes of the total in F2:
I recommend to make a few adjustments as well:
Tip. To copy the formula, use one of the ways I mentioned earlier.
Tip. To make sure your calculations are correct, enter the below one to F12:
If it returns 100% – everything's correct.
Why do I recommend using the percentage format?
Well, on one hand, to avoid multiplying each result by 100 if you'd like to get percents. On the other, to avoid dividing the results to 100 if you want to use them for any further non-percent math operations.
Here's what I mean:
I use the percentage number format in cells C4, B10, and B15. All Google Sheets formulas that reference these cells are much easier. I don't have to divide by 100 or add the percent symbol (%) to formulas in C10 and C15.
The same cannot be said about C8, C9, and C14. I must make these extra adjustments to get the correct result.
To work with loads of data in Google Sheets, nested functions and other more complicated calculations are used as a rule. Array formulas are there in Google Sheets for that purpose as well.
For instance, I have a table of sales per client. I'm curious to find the maximum sale of milk chocolate to Smith to check if I can give him an extra discount next time. I use the next array formula in E18:
Note. To finish any array formula in Google Sheets, press Ctrl+Shift+Enter rather than simply Enter.
I've got $259 as a result.
My first array formula in E16 returns the maximum purchase made by Smith – $366:
E17 show the maximum money spent for milk chocolate – $518:
Now, I'm going to replace all values used in Google Sheets formulas with their cell references:
Have you noticed what has changed?
Here's what I had before:
Just like that, juggling with values in cells you reference you can quickly get different results based on different conditions without changing the formula itself.
Let's have a look at a few more functions and formulas examples handy for every-day use.
Suppose your data is written partly as numbers and partly as text: 300 euros, total – 400 dollars. But you need to extract numbers only.
I know just a function for that:
It pulls the text by mask with a regular expression.
The text in my case is a cell with a data (A2). And I use this regular expression: [0-9]+
It means that I'm looking for any quantity (+) of numbers from 0 to 9 ([0-9]) written one after another:
If numbers has fractions, the regular expression will look like this:
"[0-9]*\.[0-9]+[0-9]+" for numbers with two decimal places
"[0-9]*\.[0-9]+" for numbers with one decimal place
Note. Google Sheets sees the extracted values as text. You need to convert them to numbers with the VALUE function or with our Convert tool.
Formulas within the text help getting a neatly looking row with some totals – numbers with their short descriptions.
I'm going to create such rows in lines 14 and 15. To start with, I merge cells in those rows via Format > Merge cells and then count the sum for column E:
Then I put the text I'd like to have as a description to double quotes and combine it with the formula using an ampersand:
="Total chocolate sales: "&SUM(E2:E13)&" dollars"
To make my numbers decimals, I use the TEXT function and set the format: "#,##0"
Another way is to use Google Sheets CONCATENATE function, like I used in A15:
=CONCATENATE("Total discount for customers: ",TEXT(SUM(F2:F13),"#.##")," dollars")
What if you upload the data from somewhere and all numbers appear with spaces, like 8 544 instead of 8544? Google Sheets will return these as text, you know.
Here's how to turn these values written as text to "normal numbers":
=VALUE(SUBSTITUTE("8 544"," ",""))
where A2 contains 8 544.
How does it work? The SUBSTITUTE function replaces all spaces in the text (check the second argument – there's space in double-quotes) with an "empty string" (the third argument). Then, VALUE converts text to numbers.
There are some Google Sheets functions that help manipulate text in your spreadsheets, for instance, change the case to sentence case. If you have something strange like soURcE dAtA, you can get Source data instead:
Let me explain that in detail. I take the first character in a cell:
and switch it to the upper case:
Then I take the remaining text:
and force it into lower case:
Lastly, I bring all pieces of the formula together with an ampersand:
Tip. You can switch between cases in a click with the corresponding utility from our Power Tools.
Of course, there's much more Google Sheets has to offer. Don't be afraid of different complex formulas – just try and experiment. After all, these toolsets let us solve many different tasks. Good luck! :)
Table of contents