Google Sheets is a powerful tool for data analysis, and understanding its key formulas can significantly enhance your productivity.
Here are 25 essential formulas that cover a wide range of functionalities, from basic arithmetic to complex data manipulation.
Adds up a range of cells.
=SUM(A1:A10)
Calculates the average of a range of cells.
=AVERAGE(B1:B10)
Counts the number of cells in a range that contain numbers.
=COUNT(C1:C10)
Returns the smallest number in a set of values.
=MIN(D1:D10)
Returns the largest number in a set of values.
=MAX(E1:E10)
Performs a conditional test and returns one value if true and another if false.
=IF(F1>10, "Yes", "No")
Searches for a value in the first column of a range and returns a value in the same row from a specified column.
=VLOOKUP("Apple", A1:B10, 2, FALSE)
Searches for a value in the top row of a table or range and returns a value in the same column from a specified row.
=HLOOKUP("Jan", A1:D4, 2, FALSE)
Returns the value of a cell in a specific row and column of a range.
=INDEX(A1:C10, 2, 3)
Searches for a specified item in a range of cells, and then returns the relative position of that item.
=MATCH("Banana", A1:A10, 0)
Counts the number of cells within a range that meet a single condition.
=COUNTIF(A1:A10, ">20")
Adds all numbers in a range of cells based on one criterion.
=SUMIF(B1:B10, ">5", B1:B10)
Calculates the average of a range depending on a criterion.
=AVERAGEIF(C1:C10, "<>10", C1:C10)
Joins together text strings into one text string.
=CONCATENATE("Hello", " ", "World")
Returns the first character(s) in a text string based on the number of characters specified.
=LEFT("Google Sheets", 6)
Returns the last character(s) in a text string based on the number of characters specified.
=RIGHT("Google Sheets", 6)
Returns a specific number of characters from a text string starting at the position you specify.
=MID("Google Sheets", 8, 6)
Returns the length of a text string in terms of characters.
=LEN("Google")
Finds one text string within another text string and returns the position of the first occurrence.
=SEARCH("Sheets", "Google Sheets")
Removes extra spaces from text except for single spaces between words.
=TRIM(" Google Sheets ")
Converts text to all lowercase letters.
=LOWER("GOOGLE SHEETS")
Converts text to all uppercase letters.
=UPPER("google sheets")
Converts text to title case, the first letter in each word to uppercase, and the rest to lowercase.
=PROPER("google sheets")
Returns the current date and time.
=NOW()
Converts a year, month, and day into a date.
=DATE(2024, 2, 10)
We hope that this Google Sheets formulas list has helped you and given you a better understanding of the essential functions of Google Sheets. If you enjoyed this article, you might also like our articles on how to remove conditional formatting in Google Sheets and how to convert CSV to Google Sheets.
