15 Google Sheets Formulas You Should Know As A Beginner

Friends when you are working with Google Sheets, efficiency is the main key.

The More Google Sheets Formulas And Tricks You Know, The More Easily And Fast You Can Do Your Work.

For every browser, Google sheets are free. So, you can not only play with data, but you can also share data with anyone, that too free of cost.

To help you and to increase your productivity we have compiled the best 15 Google Sheets formulas which will help you to save your time and you will feel like Google Sheet PRO!

In this PDF we are going to cover 15 Google Sheets formulas in detail.

So, lets get started.

Sum()

This function is used to add all the numbers in a given range.

Example 1: Input as single Row Range. In the below example we use the sum function in cell B6 as “=sum (B2:B4)”. The input to the function is the range B2:B4. The result would be the addition of all the numbers in Range B2:B4 (which is 600). We can even select a Higher Range as per our need.

Google Sheets Formulas

 

Example 2: Input as the multiple-row range

 

Google Sheets Formulas

 

Example 3: Input as a list of cells

 

Google Sheets Formulas

average()

This function is used to get the average of all the numbers in a given range.

Example 1: Input as single Row Range. In the below example we use the average function in cell B7 as “=average (B3:B5)”. The input to the function is the range B3:B5. The Result would be the average of all the numbers in Range B3:B5 (which is 200). We can even select a Higher Range as per our need.

 

Google Sheets Formulas

 

Example 2: Input as the multiple-row range

 

Google Sheets Formulas

 

Example 3: Input as a list of cells

 

Google Sheets Formulas

Text()

This function is used to convert a date into some other desired format. For example: “2020-01-01” to “Jan-2020”. Text Function takes in inputs as the Date and the format which is to be extracted. Let’s see some use cases to get a better understanding of this function.

Date to Month

In the below example, we have extracted the month in cell B9 using the Text Function with the first input as the Date in Cell A9 and the second input as the format to be extracted i.e., “mmm”

 

15 Google Sheets Formulas

Date to Month & Year

In the below example, we have extracted the month & year in cell B8 using the Text Function with the first input as the Date in Cell A8 and the second input as the format to be extracted i.e., “mmm-yyyy”

 

15 Google Sheets Formulas

Date to Weekday

In the below example, we have extracted the day of the week in cell B8 using the Text Function with the first input as the Date in Cell A8 and the second input as the format to be extracted i.e., “ddd”

 

15 Google Sheets Formulas

Datetime to Hour, Minutes & Seconds

In the below example, we have extracted the time in Hour, minutes & seconds in cell B7 using the Text Function with the first input as the Datetime in Cell A7 and the second input as the format to be extracted i.e., “hh:mm: ss”

15 Google Sheets Formulas

Some common formats used in Text Function are:

  1. mmm: month 3 characters (example: Jan, Feb, Mar)
  2. mmmmmm : month full name (example: January, February, March)
  3. mm: month 2 characters numeric (example: 01, 02, 03)
  4. yy : year 2 characters (example: 20, 21, 22)
  5. yyyy: year 4 characters (example: 2020, 2021, 2022)
  6. ddd: day of week 3 characters (Mon, Tue, Wed)
  7. dddddd: day of week full name (Monday, Tuesday, Wednesday)
  8. hh: Hour
  9. ss: seconds

Subtotal

The subtotal function is used to get the aggregate (sum, mean, etc) on the filtered range. This function takes in input as the range and the type of aggregation to be applied. Sum ()/Avg () function gives us the sum/avg of the entire Range and not the filtered range.

Let’s understand this better using the example below. In the below example Range A1:B16 contains the sales amount by different months which is aggregated in Cell B19 using sum function and in cell B20 using subtotal function.

The Function takes in input as the Type of function & the range (in this example Type is 9 which is used for Sum function within the subtotal and the range is B2:B16).

15 Google Sheets Formulas

Since we have not applied any filters yet, the output given by the sum and subtotal function is the same. Now let’s apply some filters in the data set as below.

15 Sheets Formulas

Once we apply some filters the value in cell B19 remains the same while that in B20 changes. The new value in cell B20 is the sum of the range that we have filtered. i.e., B5:16.

Subtotal is a very powerful function that enables us to do the operations on the filtered set and hence makes the discussion very interactive during any meetings.

In the above example, we have used the Type as 9 which is used to specify sum within the subtotal function. Some of the other commonly used codes are as follows:

  • 1 is for Average
  • 2 is for Count
  • 4 is for Maximum
  • 5 is for Minimum
  • 9 is for Sum

Countifs()

Countifs is a function that is used to count the number of occurrences of certain conditions in a dataset. This function takes in input as the data range and the condition which is to be counted.

Multiple conditions can be specified separated by a comma. Let’s check out a few examples: In the below example, in cell B19, we are using the countifs function to count the number of months with Sales Amount > 1000. Countifs takes input as the Range followed by the condition.

In the case where have more than one condition to apply then we add that condition in the countifs function separated by a comma. In Cell B20, we are using the countifs function with multiple conditions.

The first condition is Sales Amount is greater than or equal to 500 (B1:B16,” >=500”). The second condition is Sales Amount is less than or equal to 1000 (B1:B16,” <=1000”). Both conditions are given as an input to the countifs function separated by a comma.

15 Sheets Formulas

One thing to note is that in the case of multiple conditions, all ranges specified should be of equal length. In the above example, we have given the range as B1:B16 i.e., 16 cells.

Sumifs()

Sumifs is a function that is used to conditionally add some cells in a given range. This function takes in input as the Range to be added followed by the range and condition. Multiple conditions can be specified separated by a comma.

Let’s check out a few examples: In the below example, in cell B19, we are using the sumifs function to add the Sales Amount where the Sales amount is greater than 1000. In cell B19 we have specified One Condition i.e., B1:B16,” >1000”.

In the case where have more than one condition to apply then we add that condition separated by a comma. In Cell B20, we are using the Sumifs function with multiple conditions.

The first condition is Sales Amount is greater than or equal to 500 (B1:B16,” >=500”). The second condition is Sales Amount is less than or equal to 1000 (B1:B16,” <=1000”).

15 Sheets Formulas

One thing to note is that in the case of multiple conditions, all ranges specified should be of equal length. In the above example, we have given the range as B1:B16 i.e., 16 cells.

Averageifs()

Averageifs is a function that is used to conditionally take the average of some cells in a given range. This function takes in input as the Range to be added followed by the range and condition.

Multiple conditions can be specified separated by a comma. Let’s check out a few examples: In the below example, in cell B19, we are using the averageifs function to get the average of the Sales Amount where the Sales amount is greater than 1000. In cell B19 we have specified One Condition i.e., B1:B16,” >1000”.

In the case where have more than one condition to apply then we add that condition separated by a comma. In Cell B20, we are using the averageifs function with multiple conditions.

The first condition is Sales Amount is greater than or equal to 500 (B1:B16,” >=500”). The second condition is Sales Amount is less than or equal to 1000 (B1:B16,” <=1000”).

Google Sheets

One thing to note is that in the case of multiple conditions, all ranges specified should be of equal length. In the above example, we have given the range as B1:B16 i.e., 16 cells.

Concatenate()

Concatenate function is used to add two or more strings. This function takes in input as the strings are separated by a comma and the result is the combination of all the strings given to the function. Let’s see some examples for the same.

In the example below: We are trying to concatenate some strings in column D. Note that in Cell D2, we have given 5 strings as input to the function (“2021”, “- “, “03”, “- “, “24”). These 5 strings are combined and we get the output as “2021-03-24”.

Similarly in the example below, in cell D4, we have given space (““) as input string in the function along with A4, B4, C4. The strings are combined so that we get the complete address in one cell.

Google Sheets

VLOOKUP()

VLOOKUP is one of the most widely used functions. This function is used to map the data from one data set to another. In the below example, we have one data set which is the master data in columns E & F. In column A, we have some id’s for which we want to map the contact number from Master Data. This can easily be done using the VLOOKUP function.

VLOOKUP function takes in the input as follows:

  1. First input as the mapping id.
  2. Second input as the Range of dataset on which the data is required to be mapped.

Note: the first column of the data set should be the mapping id

  1. Third input as the lookup column. 2 in the below examples specifies that the desired lookup data is in the second column of the range given as the second input.
  2. Fourth input as the match type. 0 is for an exact match. 1 is for an approximate match.

Google Sheets

In case no matching id is found then the VLOOKUP function gives an error as the output.

HLOOKUP()

HLOOKUP is also used to map the data from one data set to another. The only difference between HLOOKUP and VLOOKUP is that HLOOKUP is applied horizontally while VLOOKUP is applied vertically. In the below example, we have one data set which is the master data in columns E to O with months and their corresponding sales. In column A, we have some months for which we want to map the sales from Master Data. This can easily be done using the HLOOKUP function.

HLOOKUP function takes in the input as follows:

  1. First input as the mapping key/id (which in this case is the month).
  2. Second input as the Range of dataset on which the data is required to be mapped. (Note: the first row of the data set should be the mapping key/id)
  3. Third input as the lookup column. 2 in the below examples specifies that the desired lookup data is in the second row of the range given as the second input.
  4. Fourth input as the match type. 0 is for an exact match. 1 is for an approximate match.

Google Sheets

In case no matching key is found then the HLOOKUP function gives an error as the output.

if()

If the function is also one of the most commonly used functions. This function is used to conditionally create output values. if () function takes in input as the following

  1. First input is condition (example: A2 > 10, A2 > B2, A2 = “Product 1”)
  2. Second input is value if the condition is TRUE
  3. Third input is value if the condition is FALSE

In the below example, we have demonstrated a used case of if () function to classify if sales met their target or not. Once Column D is created using the if function then identifying the months where Target is not Met becomes easy. We can even count Target Met/Not Met to any further analysis.

Google Sheets

if() function can also be nested within another if () function. Below is an example demonstrating the same. In Cell C2, we are trying to create some buckets of Sales using nested if which can be analyzed further (as we have done in A16:B20).

Google Sheets

Index()

Index function is used to get the data at a specific location in a data range. Index function takes in input as the Data range followed by the location of the data required. In the below example we try to get the data at position 3 in the data range F2:F23 using the index function. Index function is generally used along with match function.

Sheets

Match()

The match function is used to get the location of a certain value in a data range. The match can also be used to see if the value is present in the data range or not. Match function takes in the following input:

  1. The first input is the value to be matched
  2. The second input is the Range of data in which the first input is to be matched
  3. Type of search (0 refers to exact match)

In the below example, we have presented a very basic used case of Index and Match. We first match the value which is in cell A2 in the Range g2: g23. The output 10 refers to the location of Oct 2020 in range G2:G23 which is 10 i.e., Oct 2020 is at position 10. We use the output of the match function as an input to the index function in Cell D2 to the sales corresponding to Oct 2020.

Sheets

Note that if no value matches in the given range then the match function returns an error.

Offset

The offset function is similar to the Index function and is used to get the values at locations given as input in the function. The only difference in offset function is that we specify the location of row as well as column in an offset function. The offset function takes in input as the reference cell followed by offset row and offset column. Let’s understand this with an example

In the below example, we use the offset function in cell C2 to get value 1 row & 1 column away from the starting point i.e., $G$2

Sheets

Offset function is generally used along with match function as follows in which we use the match function for row location as well as the column location and put that as an input in offset function

Sheets

Importrange

This function is used to import data from one google sheet to another. This function takes in input as

  • The google sheet URL (not the complete URL but the token present in the URL)

Sheets

  • The range of data to be imported. Specified as “Sheet1! A1:C3”. Note that the data in this specified range will be imported

Sheets

Hope you like it.

If you want to know more about Google Sheets formulas, do let us know in comments and we will make complete guide for you.

Leave a Reply