Google Sheets Formulas: From Basic to Expert

Google Sheets and Microsoft Excel are both powerful spreadsheet tools, but Google Sheets excels in several areas, especially when it comes to collaboration, accessibility, and integration with other Google services. Some areas where Google Sheets fares better compared to Microsoft Excel include:

• Real-time Collaboration
• Cloud-Based Access & Storage
• Version History
• Integration with Google Ecosystem
• Ease of Sharing and Compatible with multiple OS and Platforms
• Free Access

While there is little difference between the way Google Sheets and MS Excel formulas work, we thought some widely used formulas would come in handy for Google Sheets beginners. Below is a list of basic and advanced Google Sheets formulas.

Basic Google Sheets Formulas

1. SUM
   • Purpose: Adds up a range of numbers.
   • Example: Calculate the total of values in cells A1 to A5.
   • Formula: =SUM(A1:A5)
2. AVERAGE
   • Purpose: Calculates the average of a range of numbers.
   • Example: Find the average value of cells B1 to B5.
   • Formula: =AVERAGE(B1:B5)
3. COUNT
   • Purpose: Counts the number of cells that contain numbers.
   • Example: Count the number of numeric entries in cells C1 to C5.
   • Formula: =COUNT(C1:C5)
4. IF
   • Purpose: Performs a logical test and returns one value if true, and another if false.
   • Example: Check if the value in D1 is greater than 10, and return “Yes” if true, “No” if false.
   • Formula: =IF(D1>10, “Yes”, “No”)
5. VLOOKUP
   • Purpose: Looks for a value in the first column of a range and returns a value in the same row from another column.
   • Example: Find the price of the item “Apple” in a list where the first column contains item names and the second column contains prices.
   • Formula: =VLOOKUP(“Apple”, A2:B10, 2, FALSE)
6. CONCATENATE
   • Purpose: Combines multiple text strings into one.
   • Example: Combine the first name in cell E1 with the last name in F1.
   • Formula: =CONCATENATE(E1, ” “, F1)
7. LEN
   • Purpose: Returns the number of characters in a text string.
   • Example: Count the number of characters in cell G1.
   • Formula: =LEN(G1)
8. LEFT
   • Purpose: Extracts a specified number of characters from the start of a text string.
   • Example: Get the first 3 characters from cell H1.
   • Formula: =LEFT(H1, 3)
9. RIGHT
   • Purpose: Extracts a specified number of characters from the end of a text string.
   • Example: Get the last 4 characters from cell I1.
   • Formula: =RIGHT(I1, 4)
10. TODAY
    • Purpose: Returns the current date.
    • Example: Display today’s date in cell J1.
    • Formula: =TODAY()

Advanced Google Sheets Formulas

1. INDEX-MATCH
   • Purpose: More flexible alternative to VLOOKUP. Finds a value in a row or column and returns a corresponding value.
   • Example: Find the price of an item called “Banana” using INDEX-MATCH.
   • Formula: =INDEX(B2:B10, MATCH(“Banana”, A2:A10, 0))
2. ARRAYFORMULA
   • Purpose: Applies a formula to a range of cells instead of a single cell.
   • Example: Multiply each value in column K by 2.
   • Formula: =ARRAYFORMULA(K1:K10 * 2)
3. IMPORTRANGE
   • Purpose: Imports a range of cells from another Google Sheets spreadsheet.
   • Example: Import data from the range A1 in another Google Sheets file.
   • Formula: =IMPORTRANGE(“spreadsheet_url”, “Sheet1!A1:B10”)
4. QUERY
   • Purpose: Allows querying of data in a range (similar to SQL).
   • Example: Select all rows from the range L1 where the value in L is greater than 50.
   • Formula: =QUERY(L1:M10, “SELECT * WHERE L > 50”)
5. SPARKLINE
   • Purpose: Creates a mini chart within a single cell.
   • Example: Display a line chart of values in cells N1 to N5.
   • Formula: =SPARKLINE(N1:N5, {“charttype”,”line”})
6. OFFSET
   • Purpose: Returns a reference to a range that is offset from a starting cell by a specified number of rows and columns.
   • Example: Get the value 2 rows down and 1 column to the right of cell O1.
   • Formula: =OFFSET(O1, 2, 1)
7. FILTER
   • Purpose: Returns a filtered version of the source range, returning only the rows that meet the specified conditions.
   • Example: Filter values from range P1 where the value is greater than 100.
   • Formula: =FILTER(P1:P10, P1:P10 > 100)
8. TRANSPOSE
   • Purpose: Converts a vertical range of cells to a horizontal range, or vice versa.
   • Example: Convert vertical range Q1 to a horizontal range.
   • Formula: =TRANSPOSE(Q1:Q5)
9. REGEXMATCH
   • Purpose: Checks whether a text string matches a regular expression.
   • Example: Check if the text in cell R1 contains the word “Google”.
   • Formula: =REGEXMATCH(R1, “Google”)
10. GOOGLEFINANCE
    • Purpose: Fetches current or historical securities information from Google Finance.
    • Example: Get the current price of Google stock.
    • Formula: =GOOGLEFINANCE(“GOOG”, “price”)

These formulas cover a wide range of use cases, from basic arithmetic to more complex data manipulation and analysis. For further learning, explore the following courses:

• Master Google Sheets Basics in 60 Minutes or Less
• Google Sheets Level 2: Intro to Formulas and Data Manipulation
• Google Sheets Level 3: Intermediate Data Analysis