Google Sheets is a powerful, free spreadsheet application for individuals and part of the Google Workspace suite for businesses, which includes other popular apps like Google Docs, Google Forms, and Google Drive. This cloud-based application allows users to create, share, and analyse data in real-time, making it ideal for collaboration and data management. From tracking budgets to analysing survey results, Google Sheets supports a wide range of tasks with built-in functions that streamline workflows and make complex data tasks manageable.

Beyond just a basic spreadsheet, Google Sheets offers robust automation, security, and sharing features that help users maximise productivity. With advanced data filtering, importing, and visualisation functions, Sheets can handle everything from simple lists to complex data analysis. Below is a selection of useful Google Sheets functions you may need to know to improve your Sheets productivity.

1. Importing Data from a Specific Sheet with QUERY and IMPORTRANGE

  • Formula: =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ExampleURL", "Marketing!A1:C10"), "SELECT * WHERE Col1 > 100")
  • Explanation: Imports data from the "Marketing" sheet of a linked Google Sheet and filters to show only rows where the first column (Col1) has values above 100.
  • Use Case: Create a consolidated report showing all expenses over $100 from a shared budget sheet, which is automatically updated when the source data changes.

2. Handling Errors Gracefully with IFERROR

  • Formula: =IFERROR(A2/B2, "Error - Division by zero")
  • Explanation: IFERROR returns the result of A2/B2 if there’s no error; otherwise, it displays "Error - Division by zero" or any custom message you choose if an error occurs.
  • Use Case: When calculating financial ratios (like Profit Margin), use IFERROR to prevent error messages from displaying in cases of missing data or division by zero. This function helps keep reports clean, professional, and easy to understand for stakeholders.

‍3. Removing Specific Matched Words Using SUBSTITUTE

  • Formula: =SUBSTITUTE(A2, "RemoveThis", "")
  • Explanation: Replaces occurrences of “RemoveThis” in cell A2 with an empty string, effectively removing it.
  • Use Case: Simplify product names by removing extraneous words like “Special Edition” in a sales report, so it reads “Product X” instead of “Special Edition - Product X.”

4. Filtering Content with FILTER Where There is a Match in Another Column

  • Formula: =FILTER(A2:A10, B2:B10="Approved")
  • Explanation: Filters the range A2:A10 to show only rows where B2:B10 contains "Approved".
  • Use Case: In a project sheet, list tasks marked "Approved" in column B to provide a streamlined view of all approved tasks for team review.

5. Extracting Unique Values Using UNIQUE

  • Formula: =UNIQUE(A2:A20)
  • Explanation: Returns distinct values from the range A2:A20.
  • Use Case: In survey analysis, extract unique responses from a list to avoid redundant counts of the same response.

6. Applying Calculations Across an Array with ARRAYFORMULA

  • Formula: =ARRAYFORMULA(B2:B10 * C2:C10)
  • Explanation: Multiplies the values in B2:B10 by the corresponding values in C2:C10, returning an array of results in one step.
  • Use Case: Quickly calculate revenue by multiplying Quantity and Price columns for each product in a sales sheet.

7. Adding Mini-Charts with SPARKLINE

  • Formula: =SPARKLINE(B2:B10)
  • Explanation: Displays a simple, in-cell chart showing trends for the data range B2:B10.
  • Use Case: Visualize weekly sales or stock trends in a single cell, making it easy to spot trends without large charts.

8. Retrieving Stock Data with GOOGLEFINANCE

  • Formula: =GOOGLEFINANCE("AAPL", "price")
  • Explanation: Pulls the latest stock price for Apple Inc. (AAPL).
  • Use Case: In investment tracking, pull live stock prices into Sheets for real-time portfolio monitoring.

9. Splitting Text into Multiple Cells with SPLIT

  • Formula: =SPLIT(A2, ",")
  • Explanation: Splits text in A2 by commas into separate cells.
  • Use Case: Separate contact information like "John Doe, johndoe@example.com, Manager" into individual cells for cleaner CRM data.

10. Combining Data from Multiple Cells with JOIN

  • Formula: =JOIN(", ", A2:A5)
  • Explanation: Joins data in cells A2:A5 with a comma and space as a delimiter.
  • Use Case: Merge first and last names into a full name column for creating mailing lists.

11. Looking Up Data with VLOOKUP

  • Formula: =VLOOKUP("Product A", A2:C10, 3, FALSE)
  • Explanation: Searches for “Product A” in column A and returns the value from the third column in the range A2:C10.
  • Use Case: Find the price of a specific product from an inventory list without manual searching.

12. Horizontal Lookup with HLOOKUP

  • Formula: =HLOOKUP("Q1", A1:D2, 2, FALSE)
  • Explanation: Looks for “Q1” in the first row and returns the value from the second row in that column.
  • Use Case: Quickly access quarterly data in a financial summary table by column header.

13. Flexible Data Lookup with INDEX & MATCH

  • Formula: =INDEX(B2:B10, MATCH("Product B", A2:A10, 0))
  • Explanation: Uses MATCH to find the position of "Product B" in A2:A10 and INDEX to return the corresponding value from B2:B10.
  • Use Case: Retrieve product prices from complex data tables without the column limitations of VLOOKUP.

14. Transforming Rows to Columns with TRANSPOSE

  • Formula: =TRANSPOSE(A2:D2)
  • Explanation: Converts the row A2:D2 into a column.
  • Use Case: Rearrange data formats received in a row structure, helpful when handling survey or form data.

15. Conditional Summing with SUMIF & Counting with COUNTIF

  • Formula: =SUMIF(A2:A10, ">100")
  • Explanation: Sums values in A2:A10 if they are greater than 100.
  • Use Case: Calculate total revenue only for transactions over $100, ideal for financial summaries with conditional analysis.

16. Generating Random Numbers with RAND & RANDBETWEEN

  • Formula: =RANDBETWEEN(1, 100)
  • Explanation: Generates a random integer between 1 and 100.
  • Use Case: Create random IDs for test samples or model simulations.

17. Flexible String Combination with TEXTJOIN

  • Formula: =TEXTJOIN(", ", TRUE, A2:A5)
  • Explanation: Combines cells A2:A5 with commas, ignoring empty cells.
  • Use Case: Formulate a list of tags or notes, skipping blank cells for a clean display.

18. Importing Online Data with IMPORTDATA

  • Formula: =IMPORTDATA("https://example.com/data.csv")
  • Explanation: Imports data from a URL in CSV format.
  • Use Case: Automatically pull in data from external sources, like financial reports, that regularly update online.

19. Handling Dates with DATE and TIME Functions

  • Formula: =DATE(2024, 11, 12)
  • Explanation: Returns a date for November 12, 2024.
  • Use Case: Use DATE and TIME for calculating deadlines and schedules within project management timelines.

20. Limiting Data Size with ARRAY_CONSTRAIN

  • Formula: =ARRAY_CONSTRAIN(A2:B100, 10, 2)
  • Explanation: Limits an array to 10 rows and 2 columns.
  • Use Case: Handle large data imports by sampling only the first 10 rows, useful for previewing data without overwhelming the spreadsheet.

Use these Google Sheets functions to unlock new levels of productivity, transforming how you work with data, from organising and analysing to automating everyday tasks.

As part of Google Workspace, Google Sheets integrates seamlessly with other Google apps, enabling efficient data flows and collaborative features that make it an invaluable tool for individuals and businesses alike. Whether handling large datasets, automating reports, or collaborating on projects, these functions allow you to streamline your workflows and make smarter, data-driven decisions.

If you are an individual user looking to improve your productivity beyond the free Google Sheets, consider Google Workspace and the Slick Media Google Workspace Promotion, which offers multiple Google Apps productivity and cost savings of 10% off each month for 12 months, too!