Top 101 Formulas You Need to Know to Work with Google Sheets

By Dev Others CSV, Google sheet, Microsoft Excel 0 Comments Like: 0

Google Sheets is a powerful spreadsheet application that allows users to organize, analyze, and manipulate data effectively. To become proficient in using Google Sheets, it’s essential to familiarize yourself with various formulas that can perform complex calculations and automate tasks. In this article, we will explore the top 101 formulas that you should know to work efficiently with Google Sheets. Let’s dive in:

1. SUM

Formula: =SUM(range)

The SUM formula adds up the values within a specified range of cells.

Example:

A
10
20
30

=SUM(A1:A3) will yield 60.

2. AVERAGE

Formula: =AVERAGE(range)

The AVERAGE formula calculates the arithmetic mean of the values in the specified range.

Example:

A
10
20
30

=AVERAGE(A1:A3) will yield 20.

3. MIN

Formula: =MIN(range)

The MIN formula returns the smallest value from the specified range.

Example:

A
10
20
30

=MIN(A1:A3) will yield 10.

4. MAX

Formula: =MAX(range)

The MAX formula returns the largest value from the specified range.

Example:

A
10
20
30

=MAX(A1:A3) will yield 30.

5. COUNT

Formula: =COUNT(range)

The COUNT formula counts the number of cells that contain numerical values in the specified range.

Example:

A
10

30

=COUNT(A1:A3) will yield 2.

6. COUNTA

Formula: =COUNTA(range)

The COUNTA formula counts the number of non-empty cells in the specified range, including text and numerical values.

Example:

A
10
ABC
30

=COUNTA(A1:A3) will yield 3.

7. COUNTIF

Formula: =COUNTIF(range, criteria)

The COUNTIF formula counts the number of cells in the specified range that meet the given criteria.

Example:

A
10
20
30

=COUNTIF(A1:A3, ">15") will yield 2.

8. SUMIF

Formula: =SUMIF(range, criteria, sum_range)

The SUMIF formula adds up the values in the sum_range that meet the specified criteria in the given range.

Example:

A
10
20
30

=SUMIF(A1:A3, ">15", B1:B3) will yield 50.

9. AVERAGEIF

Formula: =AVERAGEIF(range, criteria, average_range)

The AVERAGEIF formula calculates the average of the values in the average_range that meet the specified criteria in the given range.

Example:

A
10
20
30

=AVERAGEIF(A1:A3, ">15", B1:B3) will yield 25.

10. SUMIFS

Formula: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

The SUMIFS formula adds up the values in the sum_range that meet multiple criteria specified in different ranges.

Example:

A	B
10	5
20	5
30	2

=SUMIFS(C1:C3, A1:A3, ">15", B1:B3, ">=5") will yield 0.

11. AVERAGEIFS

Formula: =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

The AVERAGEIFS formula calculates the average of the values in the average_range that meet multiple criteria specified in different ranges.

Example:

A	B
10	5
20	5
30	2

=AVERAGEIFS(C1:C3, A1:A3, ">15", B1:B3, ">=5") will yield 0.

12. VLOOKUP

Formula: =VLOOKUP(search_key, range, index, [is_sorted])

The VLOOKUP formula searches for a value in the first column of a specified range and returns a value from a different column in the same row.

Example:

A	B
100	Red
200	Blue
300	Green

=VLOOKUP(200, A1:B3, 2, FALSE) will yield Blue.

13. HLOOKUP

Formula: =HLOOKUP(search_key, range, index, [is_sorted])

The HLOOKUP formula searches for a value in the first row of a specified range and returns a value from a different row in the same column.

Example:

A	B	C
Apple	100	Red
Banana	200	Yellow
Orange	300	Orange

=HLOOKUP("Banana", A1:C3, 3, FALSE) will yield Yellow.

14. INDEX

Formula: =INDEX(range, row_num, [column_num])

The INDEX formula returns the value of a cell within a specified range based on the given row and column numbers.

Example:

A	B
100	Red
200	Blue
300	Green

=INDEX(A1:B3, 2, 2) will yield Blue.

15. MATCH

Formula: =MATCH(search_key, range, [search_type])

The MATCH formula searches for a value within a specified range and returns the relative position of the value found.

Example

A
Red
Blue
Green

=MATCH("Blue", A1:A3, 0) will yield 2. The 0 as the [search_type] parameter indicates an exact match. In this example, the MATCH formula returns 2, indicating that “Blue” is found in the second row of the range A1:A3.

16. IF

Formula: =IF(logical_expression, value_if_true, value_if_false)

The IF formula evaluates a logical expression and returns one value if the expression is true and another value if it is false.

Example:

A
10
20
30

=IF(A1>20, "Greater than 20", "Not greater than 20") will yield "Not greater than 20".

17. AND

Formula: =AND(logical_expression1, [logical_expression2], ...)

The AND formula checks if all the specified logical expressions are true. It returns TRUE if all expressions are true; otherwise, it returns FALSE.

Example:

=AND(A1>10, B1<50) will return TRUE if both cell A1 is greater than 10 and cell B1 is less than 50.

18. OR

Formula: =OR(logical_expression1, [logical_expression2], ...)

The OR formula checks if at least one of the specified logical expressions is true. It returns TRUE if at least one expression is true; otherwise, it returns FALSE.

Example:

=OR(A1>10, B1<50) will return TRUE if either cell A1 is greater than 10 or cell B1 is less than 50.

19. NOT

Formula: =NOT(logical_expression)

The NOT formula reverses the logical value of the specified expression. It returns TRUE if the expression is false and FALSE if the expression is true.

Example:

=NOT(A1>10) will return FALSE if cell A1 is greater than 10; otherwise, it will return TRUE.

20. CONCATENATE

Formula: =CONCATENATE(text1, [text2], ...)

The CONCATENATE formula combines multiple strings or cell values into a single text string.

Example:

A	B	C
John	Doe	JohnDoe

=CONCATENATE(A1, B1) will yield JohnDoe.

Alternatively, you can use the & operator:

=A1 & B1 will yield the same result.

21. TEXT

Formula: =TEXT(value, format)

The TEXT formula converts a numeric value into a text string based on the specified format.

Example:

A	B
0.15	15.00%

=TEXT(A1, "0.00%") will yield 15.00%.

22. LEN

Formula: =LEN(text)

The LEN formula returns the number of characters in a given text string.

Example:

=LEN("Hello, World!") will yield 13.

23. TRIM

Formula: =TRIM(text)

The TRIM formula removes any leading or trailing spaces from a text string.

Example:

=TRIM(" Hello, World! ") will yield "Hello, World!".

24. LOWER

Formula: =LOWER(text)

The LOWER formula converts all the characters in a text string to lowercase.

Example:

=LOWER("Hello, World!") will yield "hello, world!".

25. UPPER

Formula: =UPPER(text)

The UPPER formula converts all the characters in a text string to uppercase.

Example:

=UPPER("Hello, World!") will yield "HELLO, WORLD!".

26. PROPER

Formula: =PROPER(text)

The PROPER formula capitalizes the first letter of each word in a text string.

Example:

=PROPER("hello, world!") will yield "Hello, World!".

27. SUBSTITUTE

Formula: =SUBSTITUTE(text, old_text, new_text, [occurrence])

The SUBSTITUTE formula replaces occurrences of a specified old text with new text in a text string.

Example:

=SUBSTITUTE("Hello, World!", "Hello", "Hi") will yield "Hi, World!".

28. MID

Formula: =MID(text, start, length)

The MID formula extracts a specific number of characters from a text string, starting at a given position.

Example:

=MID("Hello, World!", 7, 5) will yield "World".

29. LEFT

Formula: =LEFT(text, num_chars)

The LEFT formula returns a specified number of characters from the beginning of a text string.

Example:

=LEFT("Hello, World!", 5) will yield "Hello".

30. RIGHT

Formula: =RIGHT(text, num_chars)

The RIGHT formula returns a specified number of characters from the end of a text string.

Example:

=RIGHT("Hello, World!", 6) will yield "World!".

31. RAND

Formula: =RAND()

The RAND formula generates a random number between 0 and 1.

Example:

=RAND() will yield a random decimal number between 0 and 1.

32. RANDBETWEEN

Formula: =RANDBETWEEN(bottom, top)

The RANDBETWEEN formula generates a random integer between the specified bottom and top values.

Example:

=RANDBETWEEN(1, 100) will yield a random integer between 1 and 100.

33. NOW

Formula: =NOW()

The NOW formula returns the current date and time.

Example:

=NOW() will yield the current date and time.

34. TODAY

Formula: =TODAY()

The TODAY formula returns the current date.

Example:

=TODAY() will yield the current date.

35. DATE

Formula: =DATE(year, month, day)

The DATE formula creates a date based on the specified year, month, and day.

Example:

=DATE(2023, 7, 28) will yield 7/28/2023.

36. TIME

Formula: =TIME(hour, minute, second)

The TIME formula creates a time based on the specified hour, minute, and second.

Example:

=TIME(12, 30, 0) will yield 12:30:00 PM.

37. EOMONTH

Formula: =EOMONTH(start_date, months)

The EOMONTH formula calculates the end of the month date after adding or subtracting a specified number of months from the given start_date.

Example:

Let’s assume cell A1 contains the date 2023-07-15.

=EOMONTH(A1, 1) will yield 2023-08-31, which is the end of the month after adding one month to the date in cell A1.

=EOMONTH(A1, -1) will yield 2023-06-30, which is the end of the month after subtracting one month from the date in cell A1.

38. DATEDIF

Formula: =DATEDIF(start_date, end_date, unit)

The DATEDIF formula calculates the difference between two dates in years, months, or days, based on the specified unit.

Example:

Let’s assume cell A1 contains the start date 2023-01-01, and cell B1 contains the end date 2023-07-28.

=DATEDIF(A1, B1, "Y") will yield 0, as there is no full year between the start and end dates.

=DATEDIF(A1, B1, "M") will yield 6, as there are six full months between the start and end dates.

=DATEDIF(A1, B1, "D") will yield 209, as there are 209 days between the start and end dates.

39. NETWORKDAYS

Formula: =NETWORKDAYS(start_date, end_date, [holidays])

The NETWORKDAYS formula calculates the number of working days between two dates, excluding specified holidays.

Example:

Let’s assume cell A1 contains the start date 2023-07-15, and cell B1 contains the end date 2023-07-28.

=NETWORKDAYS(A1, B1) will yield 10, assuming there are no holidays between the start and end dates.

40. TODAY

Formula: =TODAY()

The TODAY formula returns the current date.

Example:

=TODAY() will yield the current date.

41. NOW

Formula: =NOW()

The NOW formula returns the current date and time.

Example:

=NOW() will yield the current date and time.

42. COUNTBLANK

Formula: =COUNTBLANK(range)

The COUNTBLANK formula counts the number of empty cells within a specified range.

Example:

Let’s assume cells A1 to A5 contain the following values: 10, , `20`, , 30.

=COUNTBLANK(A1:A5) will yield 2, as there are two empty cells in the range.

43. IFERROR

Formula: =IFERROR(value, value_if_error)

The IFERROR formula checks if a formula returns an error and provides an alternative value or action if an error occurs.

Example:

Let’s assume cell A1 contains the value 10, and cell B1 contains the value 0.

=A1/B1 will result in a #DIV/0! error.

=IFERROR(A1/B1, "Error: Division by zero") will yield "Error: Division by zero", indicating the presence of an error in the formula.

44. SUMPRODUCT

Formula: =SUMPRODUCT(array1, [array2], ...)

The SUMPRODUCT formula calculates the sum of the products of corresponding elements in multiple arrays.

Example:

Let’s assume cells A1 to A3 contain the values 2, 3, and 4, and cells B1 to B3 contain the values 5, 6, and 7.

=SUMPRODUCT(A1:A3, B1:B3) will yield 56, calculated as (2*5) + (3*6) + (4*7).

45. TRANSPOSE

Formula: =TRANSPOSE(array)

The TRANSPOSE formula changes the orientation of a range, converting rows into columns and vice versa.

Example:

Let’s assume cells A1 to C1 contain the values 1, 2, and 3.

=TRANSPOSE(A1:C1) will yield a vertical column with three cells containing 1, 2, and 3.

46. INDEX MATCH (Combining INDEX and MATCH)

The INDEX MATCH combination is a powerful way to perform lookups in Google Sheets when VLOOKUP or HLOOKUP is not sufficient. It allows you to find a value in one column based on a match in another column.

Example:

Let’s consider the following table:

A	B
Red	100
Blue	200
Green	300

Assume that cell C1 contains the value "Blue". To retrieve the corresponding value from column B, we use the INDEX MATCH combination:

=INDEX(B1:B3, MATCH(C1, A1:A3, 0)) will yield 200, as it finds the value "Blue" in column A and returns the corresponding value from column B.

47. UNIQUE

Formula: =UNIQUE(range)

The UNIQUE formula returns a list of unique values from a specified range, removing any duplicates.

Example:

Let’s assume cells A1 to A5 contain the values 10, 20, 10, 30, and 20.

=UNIQUE(A1:A5) will yield a vertical column with three cells containing 10, 20, and 30.

48. SORT

Formula: =SORT(range, sort_column, [sort_order1], [sort_column2], [sort_order2], ...)

The SORT formula arranges a range of cells in ascending or descending order based on specified sort columns.

Example:

Let’s assume cells A1 to B5 contain the following values:

A	B
John	100
Alice	90
Bob	120
Emma	80
David	110

=SORT(A1:B5, 2, FALSE) will yield the following sorted table:

A	B
Bob	120
John	100
David	110
Alice	90
Emma	80

The 2 indicates that the sorting should be based on values in column B (the second column), and FALSE indicates descending order.

49. FILTER

Formula: =FILTER(range, condition1, [condition2], ...)

The FILTER formula returns a filtered range of cells that meet the specified conditions.

Example:

Let’s assume cells A1 to B5 contain the following values:

ChatGPT

A	B
John	100
Alice	90
Bob	120
Emma	80
David	110

=FILTER(A1:B5, B1:B5>=100) will yield the following filtered table:

A	B
John	100
Bob	120
David	110

The formula filtered the original table, showing only the rows where the value in column B is greater than or equal to 100.

50. QUERY

Formula: =QUERY(data, query, [headers])

The QUERY formula allows you to perform SQL-like queries on your data within Google Sheets.

Example:

Let’s assume cells A1 to B5 contain the same data as in the previous example:

A	B
John	100
Alice	90
Bob	120
Emma	80
David	110

=QUERY(A1:B5, "SELECT A, B WHERE B >= 100", 1) will yield the following result:

A	B
John	100
Bob	120
David	110

The formula uses a SQL-like query to select only the rows where the value in column B is greater than or equal to 100. The 1 at the end indicates that the data has headers.

51. SUMPRODUCT with Multiple Criteria

The SUMPRODUCT formula can be used with multiple criteria to perform complex calculations.

Example:

Let’s assume cells A1 to C5 contain the following values:

A	B	C
John	100	Yes
Alice	90	No
Bob	120	Yes
Emma	80	Yes
David	110	No

To calculate the sum of the values in column B where column A is “John” and column C is “Yes,” you can use:

=SUMPRODUCT((A1:A5="John")*(C1:C5="Yes")*(B1:B5))

This formula will yield 100, as it adds up the values in column B that meet both criteria: “John” in column A and “Yes” in column C.

52. SUMIFS with Multiple Criteria

The SUMIFS formula can also be used with multiple criteria to calculate sums based on different conditions.

Example:

Let’s assume cells A1 to C5 contain the same data as in the previous example:

A	B	C
John	100	Yes
Alice	90	No
Bob	120	Yes
Emma	80	Yes
David	110	No

To calculate the sum of the values in column B where column A is “John” and column C is “Yes,” you can use:

=SUMIFS(B1:B5, A1:A5, "John", C1:C5, "Yes")

This formula will also yield 100, providing the same result as the SUMPRODUCT formula with multiple criteria.

53. CONCATENATEIF (Custom Function)

Google Sheets does not have a CONC

ATENATEIF function by default, but you can create a custom function using Google Apps Script to achieve this functionality. The custom function will concatenate values in one range based on specified criteria in another range.

Here’s an example of how to create the CONCATENATEIF custom function:

Open your Google Sheets and click on “Extensions” in the top menu.
Select “Apps Script.”
In the Apps Script editor, paste the following code:

javascript

function CONCATENATEIF(range, criteriaRange, criteria) {

var output = "";

for (var i = 0; i < range.length; i++) {

if (criteriaRange[i][0] == criteria) {

output += range[i][0];

}

}

return output;

}

Save the script (File > Save) and give it a name, such as “CONCATENATEIF.”

Now, you can use the CONCATENATEIF custom function in your Google Sheets:

Let’s assume cells A1 to A5 contain the values: “Apple”, “Orange”, “Banana”, “Grapes”, “Apple,” and cells B1 to B5 contain the corresponding quantities: 3, 2, 4, 5, 1.

=CONCATENATEIF(A1:A5, B1:B5, 3) will yield "AppleApple" as it concatenates all occurrences of “Apple” (criteria) based on the quantities in column B being equal to 3.

54. COUNTIFS with Multiple Criteria

The COUNTIFS formula can be used to count cells that meet multiple criteria specified in different ranges.

Example:

Let’s assume cells A1 to C5 contain the following values:

A	B	C
John	100	Yes
Alice	90	No
Bob	120	Yes
Emma	80	Yes
David	110	No

To count the number of occurrences where column A is “John” and column C is “Yes,” you can use:

=COUNTIFS(A1:A5, "John", C1:C5, "Yes")

This formula will yield 1, as there is only one occurrence that meets both criteria.

55. AVERAGEIF with Multiple Criteria

The AVERAGEIF formula can also be used with multiple criteria to calculate the average based on different conditions.

Example:

Let’s assume cells A1 to C5 contain the same data as in the previous example:

A	B	C
John	100	Yes
Alice	90	No
Bob	120	Yes
Emma	80	Yes
David	110	No

To calculate the average of the values in column B where column A is “John” and column C is “Yes,” you can use:

=AVERAGEIF(A1:A5, "John", B1:B5)

This formula will yield 100, as it calculates the average of the values in column B that meet the criteria “John” in column A.

56. VLOOKUP with Approximate Match

By default, VLOOKUP performs an exact match. However, you can use the optional fourth parameter (range_lookup) to allow for approximate matches.

Example:

Let’s assume cells F1 to G3 contain the following data:

F	G
1500	Low
3000	Medium
5000	High

Let’s also assume cell E1 contains the value 4200.

=VLOOKUP(E1, F1:G3, 2, TRUE) will yield "Medium", as it performs an approximate match and finds the value closest to 4200 in column F, which is 3000. The corresponding value in column G is "Medium", so that’s what the formula returns.

It’s important to note that for the VLOOKUP to work with an approximate match, the data in the first column (F in this case) should be sorted in ascending order.

57. HLOOKUP with Approximate Match

Similar to VLOOKUP, HLOOKUP can also perform an approximate match when you set the optional fourth parameter (range_lookup) to TRUE.

Example:

Let’s assume cells F1 to H1 contain the following data:

F	G	H
1000	2000	3000

Let’s also assume cell E1 contains the value 2500.

=HLOOKUP(E1, F1:H1, 1, TRUE) will yield 2000, as it performs an approximate match and finds the value closest to 2500 in the row. The corresponding value in column G is 2000, so that’s what the formula returns.

58. Array Formulas

Array formulas in Google Sheets allow you to perform calculations on multiple cells and return multiple results.

Example:

Let’s assume cells A1 to A5 contain the values: 1, 2, 3, 4, and 5.

To calculate the sum of the squares of each number, you can use an array formula:

Select cells B1 to B5.
Enter the following formula in the formula bar (without pressing Enter yet):

=ARRAYFORMULA(A1:A5^2)

Press Ctrl + Shift + Enter (or Cmd + Shift + Enter on Mac) to apply the array formula.

The result will be:

A	B
1	1
2	4
3	9
4	16
5	25

The formula A1:A5^2 is automatically applied to each cell in the range B1:B5, calculating the square of each number in column A.

59. Nested IF Statements

You can use nested IF statements to handle multiple conditions in a single formula.

Example:

Let’s assume cell A1 contains the value 70.

=IF(A1 >= 90, "A", IF(A1 >= 80, "B", IF(A1 >= 70, "C", IF(A1 >= 60, "D", "F"))))

The above formula will yield "C", as the value in cell A1 is 70, and it falls within the range of 70-79, corresponding to the grade “C.”

60. WEEKDAY

Formula: =WEEKDAY(date, [type])

The WEEKDAY formula returns the day of the week for a given date as a number (1 for Sunday, 2 for Monday, and so on).

Example:

Let’s assume cell A1 contains the date 2023-07-28.

=WEEKDAY(A1) will yield 6, as July 28, 2023, is a Friday (the 6th day of the week).

You can use the optional [type] parameter to specify the starting day of the week. For example, 2 will make Monday the first day of the week.

61. ISBLANK

Formula: =ISBLANK(value)

The ISBLANK formula checks whether a cell is empty and returns TRUE if the cell is blank and FALSE if it is not.

Example:

Let’s assume cell A1 is empty.

=ISBLANK(A1) will yield TRUE, indicating that cell A1 is blank.

62. ISTEXT

Formula: =ISTEXT(value)

The ISTEXT formula checks whether a cell contains text and returns TRUE if the cell contains text and FALSE if it contains a number or is empty.

Example:

Let’s assume cell A1 contains the value "Hello".

=ISTEXT(A1) will yield TRUE, as cell A1 contains text.

63. ISNUMBER

Formula: =ISNUMBER(value)

The ISNUMBER formula checks whether a cell contains a numeric value and returns TRUE if the cell contains a number and FALSE if it contains text or is empty.

Example:

Let’s assume cell A1 contains the value 123.

=ISNUMBER(A1) will yield TRUE, as cell A1 contains a numeric value.

64. ISERROR

Formula: =ISERROR(value)

The ISERROR formula checks whether a cell contains an error and returns TRUE if the cell contains an error and FALSE if it contains a valid value.

Example:

Let’s assume cell A1 contains the formula =1/0, which results in a #DIV/0! error.

=ISERROR(A1) will yield TRUE, indicating that cell A1 contains an error.

65. ISNA

Formula: =ISNA(value)

The ISNA formula checks whether a cell contains the #N/A error and returns TRUE if the cell contains the #N/A error and FALSE if it contains a valid value or a different error.

Example:

Let’s assume cell A1 contains the formula =VLOOKUP("Invalid Value", B1:C5, 2, FALSE), which results in a #N/A error.

=ISNA(A1) will yield TRUE, indicating that cell A1 contains the #N/A error.

66. N

Formula: =N(value)

The N formula converts a value into a numeric value. If the input is text, it will return 0, and if the input is numeric, it will remain unchanged.

Example:

Let’s assume cell A1 contains the value "123".

=N(A1) will yield 123, converting the text value into a numeric value.

67. UPPER, LOWER, and PROPER (with Non-English Characters)

The UPPER, LOWER, and PROPER formulas can also handle non-English characters and accents.

Example:

Let’s assume cell A1 contains the text Café.

=UPPER(A1) will yield "CAFÉ".

=LOWER(A1) will yield "café".

=PROPER(A1) will yield "Café".

These formulas maintain the non-English characters and accents while changing the case of the text.

68. INDEX MATCH with Multiple Criteria (Two or More Columns)

You can extend the INDEX MATCH combination to work with multiple criteria in two or more columns.

Example:

Let’s consider the following table:

A	B	C
John	25	M
Alice	30	F
Bob	28	M
Emma	22	F
David	35	M

Assume that cell E1 contains the name “Bob” and cell E2 contains the gender “M”. We want to retrieve the age of “Bob” with a gender of “M” from the table.

=INDEX(B:B, MATCH(1, (A:A=E1)*(C:C=E2), 0))

This is an array formula, so after entering it, press Ctrl + Shift + Enter (or Cmd + Shift + Enter on Mac) to apply it.

The formula will yield 28, which is Bob’s age, as it searches for a match in both the “Name” and “Gender” columns and returns the corresponding age from the “Age” column.

69. AVERAGEIFS with Multiple Criteria (Two or More Columns)

The AVERAGEIFS formula can handle multiple criteria in two or more columns to calculate averages based on different conditions.

Example:

Let’s consider the same table as in the previous example:

A	B	C
John	25	M
Alice	30	F
Bob	28	M
Emma	22	F
David	35	M

To calculate the average age of females (F) from the table, you can use:

=AVERAGEIFS(B:B, C:C, "F")

This formula will yield 26, as it calculates the average of the ages of females from column B where column C is equal to “F”.

70. TRANSPOSE with Array Formula

You can use the TRANSPOSE function with an array formula to switch rows and columns of a range.

Example:

Let’s assume cells A1 to A3 contain the values: 1, 2, and 3.

Select cells B1 to D1.
Enter the following formula in the formula bar (without pressing Enter yet):

=TRANSPOSE(A1:A3)

Press Ctrl + Shift + Enter (or Cmd + Shift + Enter on Mac) to apply the array formula.

The result will be:

A	B	C
1	2	3

The formula TRANSPOSE(A1:A3) is automatically applied to each cell in the range B1:D1, switching the values from a row into a vertical column.

71. UNIQUE with Array Formula

You can use the UNIQUE function with an array formula to get a list of unique values from a range in multiple rows and columns.

Example:

Let’s assume cells A1 to B3 contain the following values:

A	B
John	25
Alice	30
John	28

Select cells C1 to D3.
Enter the following formula in the formula bar (without pressing Enter yet):

=UNIQUE(A1:B3)

Press Ctrl + Shift + Enter (or Cmd + Shift + Enter on Mac) to apply the array formula.

The result will be:

C	D
John	25
Alice	30

The formula UNIQUE(A1:B3) is automatically applied to each cell in the range C1:D3, giving a list of unique name and age combinations from the original table.

72. FLATTEN with Array Formula

The FLATTEN function can be used with an array formula to convert a range with multiple rows and columns into a single column.

Example:

Let’s assume cells A1 to B3 contain the following values:

A	B
John	25
Alice	30
Bob	28

Select cells C1 to C6.
Enter the following formula in the formula bar (without pressing Enter yet):

=FLATTEN(A1:B3)

Press Ctrl + Shift + Enter (or Cmd + Shift + Enter on Mac) to apply the array formula.

The result will be:

C
John
Alice
Bob
25
30
28

The formula FLATTEN(A1:B3) is automatically applied to each cell in the range C1:C6, converting the range into a single column.

73. FILTER with Array Formula

You can use the FILTER function with an array formula to get a filtered range of cells based on multiple conditions.

Example:

Let’s assume cells A1 to B5 contain the following values:

A	B
John	25
Alice	30
Bob	28
Emma	22
David	35

To filter the names of people whose age is greater than or equal to 25, you can use:

Select cells C1 to C5.
Enter the following formula in the formula bar (without pressing Enter yet):

=FILTER(A1:A5, B1:B5>=25)

Press Ctrl + Shift + Enter (or Cmd + Shift + Enter on Mac) to apply the array formula.

The result will be:

C
John
Alice
Bob
David

The formula FILTER(A1:A5, B1:B5>=25) is automatically applied to each cell in the range C1:C5, filtering the names based on the condition that the age in column B is greater than or equal to 25.

74. SORT with Array Formula

You can use the SORT function with an array formula to sort a range based on multiple columns.

Example:

Let’s assume cells A1 to C5 contain the following values:

A	B	C
John	25	M
Alice	30	F
Bob	28	M
Emma	22	F
David	35	M

To sort the table first by gender (column C) in ascending order, and then by age (column B) in descending order

To achieve this sorting order, we can use the SORT function along with the SEQUENCE function to create a custom array formula.

Example:

Let’s assume cells A1 to C5 contain the following values:

A	B	C
John	25	M
Alice	30	F
Bob	28	M
Emma	22	F
David	35	M

To sort the table first by gender (column C) in ascending order, and then by age (column B) in descending order, you can use the following array formula:

Select cells D1 to F5 (or any empty range of the same size).
Enter the following formula in the formula bar (without pressing Enter yet):

=SORT(A1:C5, SEQUENCE(ROWS(A1:C5)), 1, C1:C5, 1, B1:B5, -1)

Press Ctrl + Shift + Enter (or Cmd + Shift + Enter on Mac) to apply the array formula.

The sorted result will be:

D	E	F
Alice	30	F
Emma	22	F
Bob	28	M
David	35	M
John	25	M

The formula SORT(A1:C5, SEQUENCE(ROWS(A1:C5)), 1, C1:C5, 1, B1:B5, -1) is automatically applied to each cell in the range D1:F5, sorting the data first by column C (gender) in ascending order, and then by column B (age) in descending order.

The SEQUENCE function generates a sequence of numbers to help maintain the original order of rows before sorting. By using this custom array formula, you can achieve complex sorting patterns that are not directly supported by the SORT function alone.

75. SUBSTITUTE

Formula: =SUBSTITUTE(text, old_text, new_text, [instance])

The SUBSTITUTE formula replaces occurrences of a specified old_text with new_text in a given text.

Example:

Let’s assume cell A1 contains the text "I love apples, and apples are delicious.".

=SUBSTITUTE(A1, "apples", "bananas") will yield "I love bananas, and bananas are delicious.", as it replaces all occurrences of "apples" with "bananas" in the text.

The optional [instance] parameter allows you to specify which occurrence of the old_text you want to replace. For example, 2 will replace the second occurrence, 3 will replace the third occurrence, and so on.

76. LEN

Formula: =LEN(text)

The LEN formula calculates the length of a given text (including spaces).

Example:

Let’s assume cell A1 contains the text "Hello, World!".

=LEN(A1) will yield 13, as the text has 13 characters, including spaces.

77. MID

Formula: =MID(text, start, num_chars)

The MID formula extracts a substring from a given text, starting from a specified position (start) and including a specified number of characters (num_chars).

Example:

Let’s assume cell A1 contains the text "Hello, World!".

=MID(A1, 7, 5) will yield "World", as it extracts the substring starting from the 7th character and including the next 5 characters.

78. LEFT

Formula: =LEFT(text, num_chars)

The LEFT formula extracts a specified number of characters from the beginning of a given text.

Example:

Let’s assume cell A1 contains the text "Hello, World!".

=LEFT(A1, 5) will yield "Hello", as it extracts the first 5 characters from the text.

79. RIGHT

Formula: =RIGHT(text, num_chars)

The RIGHT formula extracts a specified number of characters from the end of a given text.

Example:

Let’s assume cell A1 contains the text "Hello, World!".

=RIGHT(A1, 6) will yield "World!", as it extracts the last 6 characters from the text.

80. TEXT

Formula: =TEXT(value, format_text)

The TEXT formula formats a numeric value (date, time, number, etc.) based on a specified format.

Example:

Let’s assume cell A1 contains the date 2023-07-28.

=TEXT(A1, "MMMM DD, YYYY") will yield "July 28, 2023", as it formats the date in the specified format.

You can use a wide variety of format codes to customize the display of dates, times, and numbers. For example, "MM/DD/YYYY" will display the date as "07/28/2023".

81. CONCAT

Formula: =CONCAT(value1, [value2, ...])

The CONCAT formula concatenates multiple strings or values into a single text string.

Example:

Let’s assume cells A1 and A2 contain the values "Hello" and "World".

=CONCAT(A1, " ", A2, "!") will yield "Hello World!", as it concatenates the values in A1 and A2 along with the space and exclamation mark.

82. RAND

Formula: =RAND()

The RAND formula generates a random decimal number between 0 and 1.

Example:

=RAND() will yield a random decimal number, such as 0.724314.

If you want to generate a random number within a specific range, you can use arithmetic operations. For example, to generate a random integer between 1 and 10, you can use =INT(RAND()*10)+1.

83. RANDBETWEEN

Formula: =RANDBETWEEN(bottom, top)

The RANDBETWEEN formula generates a random integer between two specified values (bottom and top).

Example:

=RANDBETWEEN(1, 10) will yield a random integer between 1 and 10, such as 7.

84. NOW with TIME

The NOW function can be combined with TIME to display the current date and time in a specific format.

Example:

=NOW() will yield the current date and time, such as 7/28/2023 11:35:14 AM.

To display only the time in 12-hour format, you can use:

=TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()))

This will yield the current time, such as 11:35:14 AM.

85. COUNTUNIQUE

Formula: =COUNTUNIQUE(range)

The COUNTUNIQUE formula counts the number of unique values within a specified range.

Example:

Let’s assume cells A1 to A5 contain the values 10, 20, 30, 20, and 40.

=COUNTUNIQUE(A1:A5) will yield 4, as there are four unique values (10, 20, 30, and 40) within the range.

86. COUNTIFS with OR Logic

You can use the COUNTIFS formula with OR logic to count cells that meet at least one of the specified criteria.

Example:

Let’s assume cells A1 to A5 contain the following values:

A
10
20
30
40
50

To count the number of cells that contain either 20 or 30, you can use:

=COUNTIFS(A1:A5, 20) + COUNTIFS(A1:A5, 30)

This formula will yield 2, as there are two cells that contain either 20 or 30.

87. SUMIFS with OR Logic

You can use the SUMIFS formula with OR logic to calculate the sum of cells that meet at least one of the specified criteria.

Example:

Let’s assume cells A1 to A5 contain the following values:

A
10
20
30
40
50

To calculate the sum of cells that contain either 20 or 30, you can use:

=SUMIFS(A1:A5, A1:A5, 20) + SUMIFS(A1:A5, A1:A5, 30)

This formula will yield 50, as the sum of cells A2 and A3 is 20 + 30 = 50.

88. AVERAGEIFS with OR Logic

You can use the AVERAGEIFS formula with OR logic to calculate the average of cells that meet at least one of the specified criteria.

Example:

Let’s assume cells A1 to A5 contain the following values:

A
10
20
30
40
50

To calculate the average of cells that contain either 20 or 30, you can use:

=AVERAGEIFS(A1:A5, A1:A5, 20) + AVERAGEIFS(A1:A5, A1:A5, 30)

This formula will yield 25, as the average of cells A2 and A3 is (20 + 30) / 2 = 25.

89. CONCATENATE with Array Formula

You can use the CONCATENATE function with an array formula to concatenate multiple ranges into a single range.

Example:

Let’s assume cells A1 to A3 contain the values: 1, 2, and 3, and cells B1 to B3 contain the values: A, B, and C.

Select cells C1 to C3.
Enter the following formula in the formula bar (without pressing Enter yet):

=CONCATENATE(A1:A3, B1:B3)

Press Ctrl + Shift + Enter (or Cmd + Shift + Enter on Mac) to apply the array formula.

The result will be:

A	B	C
1	A	1A
2	B	2B
3	C	3C

The formula CONCATENATE(A1:A3, B1:B3) is automatically applied to each cell in the range C1:C3, concatenating the values from columns A and B.

90. RANK

Formula: =RANK(value, data, [is_ascending])

The RANK formula returns the rank of a value in a given data set. You can specify whether the ranking is in ascending or descending order.

Example:

Let’s assume cells A1 to A5 contain the following values:

A
10
20
30
20
40

To find the rank of 30 in the data set, you can use:

=RANK(30, A1:A5)

This formula will yield 3, as 30 is the third-largest value in the data set.

By default, the [is_ascending] parameter is set to TRUE, meaning the ranking is in ascending order. If you want the ranking in descending order, you can use =RANK(30, A1:A5, FALSE).

91. NESTED ARRAYFORMULA

You can nest an ARRAYFORMULA inside another ARRAYFORMULA to perform calculations on arrays within arrays.

Example:

Let’s assume cells A1 to B3 contain the following values:

A	B
1	2
3	4
5	6

To calculate the sum of each row and then the sum of those sums, you can use:

=SUM(ARRAYFORMULA(ARRAYFORMULA(A1:A3) + ARRAYFORMULA(B1:B3)))

This formula will yield 21, as it first adds the corresponding values in columns A and B to get the row sums ([3, 7, 11]) and then sums those row sums.

92. SEARCH

Formula: =SEARCH(find_text, within_text, [start_position])

The SEARCH formula finds the position of a substring (find_text) within a longer text (within_text) and returns the position as a number.

Example:

Let’s assume cell A1 contains the text "Hello, World!".

=SEARCH("World", A1) will yield 8, as the word "World" starts at the 8th position in the text.

The optional [start_position] parameter allows you to specify the position in the text where the search should start. For example, =SEARCH("o", A1, 5) will yield 8, as it starts searching from the 5th position and finds the first occurrence of "o" at position 8.

93. DATEVALUE

Formula: =DATEVALUE(date_text)

The DATEVALUE formula converts a date string in a recognized format to a serial number representing that date.

Example:

Let’s assume cell A1 contains the date string "2023-07-28".

=DATEVALUE(A1) will yield the serial number 45012, which represents July 28, 2023.

94. TIMEVALUE

Formula: =TIMEVALUE(time_text)

The TIMEVALUE formula converts a time string in a recognized format to a decimal representing that time.

Example:

Let’s assume cell A1 contains the time string "12:30 PM".

=TIMEVALUE(A1) will yield 0.520833333333333, which represents 12:30 PM as a decimal value.

95. DATE and TIME Combination

You can use the DATE and TIME functions together to create a date-time value.

Example:

Assume cell A1 contains the date "2023-07-28" and cell B1 contains the time "12:30 PM".

=DATE(YEAR(A1), MONTH(A1), DAY(A1)) + TIMEVALUE(B1) will yield 2,323.52083333333, which represents July 28, 2023, at 12:30 PM as a decimal value.

96. LEFT and LEN for Extracting Characters

You can use the LEFT and LEN functions together to extract a specified number of characters from the beginning of a text.

Example:

Let’s assume cell A1 contains the text "Hello, World!".

=LEFT(A1, LEN(A1)-1) will yield "Hello, World", as it extracts all characters from the beginning of the text except the last one.

97. MID and SEARCH for Extracting Substrings

You can use the MID and SEARCH functions together to extract a substring from a text based on a starting position and the length of the substring.

Example:

Let’s assume cell A1 contains the text "Hello, World!".

=MID(A1, SEARCH("World", A1), 5) will yield "World", as it extracts the substring starting from the position of "World" in the text and with a length of 5 characters.

98. LEFT and SEARCH for Extracting Variable Length Substrings

You can use the LEFT and SEARCH functions together to extract a variable length substring from the beginning of a text until a certain character or delimiter.

Example:

Let’s assume cell A1 contains the text "John, Alice, Bob, Emma, David".

=LEFT(A1, SEARCH(",", A1)-1) will yield "John", as it extracts the substring starting from the beginning of the text until the first comma (,).

99. FIND

Formula: =FIND(find_text, within_text, [start_position])

The FIND formula works similarly to the SEARCH formula, but it is case-sensitive.

Example:

Let’s assume cell A1 contains the text "Hello, World!".

=FIND("W", A1) will yield 8, as the uppercase letter "W" is found at the 8th position in the text.

100. CONCATENATE and IF for Conditionally Joining Strings

You can use the CONCATENATE function together with the IF function to conditionally join strings based on certain criteria.

Example:

Let’s assume cells A1 and A2 contain the values "John" and "Alice" respectively, and cell B1 contains the value TRUE.

=IF(B1, CONCATENATE(A1, " loves ", A2), CONCATENATE(A2, " loves ", A1)) will yield "John loves Alice", as it checks the value in B1 (which is TRUE) and conditionally concatenates the strings in A1 and A2.

If cell B1 contains FALSE, the formula will yield "Alice loves John".

101. INDIRECT

Formula: =INDIRECT(ref_text)

The INDIRECT formula converts a text string into a cell reference and returns the value of the referenced cell.

Example:

Let’s assume cell A1 contains the value 10, and cell B1 contains the text "A1".

=INDIRECT(B1) will yield 10, as it converts the text in B1 into a cell reference and retrieves the value of cell A1.

This formula can be particularly useful when you have cell references as text and need to use them in calculations or lookups.

Congratulations! You’ve now explored a comprehensive list of 101 formulas that can greatly enhance your productivity and efficiency while working with Google Sheets. These formulas cover a wide range of tasks, from basic arithmetic calculations to complex data manipulations. By mastering these formulas and their applications, you’ll become more proficient in using Google Sheets for various tasks, whether it’s for personal use, academic purposes, or professional projects. So, go ahead and start applying these formulas in your spreadsheets, and unlock the full potential of Google Sheets! Happy spreadsheeting!