Google Sheets is a powerful tool, not just for creating spreadsheets, but also for analyzing, automating, and visualizing data. While Excel and Google Sheets share many common functions, Google Sheets offers unique features and functions that make it stand out, especially for collaborative and online data tasks. In this article, we will cover 25+ useful Google Sheets functions that are either exclusive to Google Sheets or work differently from Excel.
1. GOOGLEFINANCE
Purpose: Fetch live stock, currency, and financial data directly into Google Sheets.
Syntax:
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
Example:
=GOOGLEFINANCE("NASDAQ:GOOGL", "price")
This will fetch the current stock price of Google.
Use case: Track stock prices, compare market performance, or build a live portfolio tracker.
2. IMPORTDATA
Purpose: Import data from a CSV or TSV file hosted on a URL.
Syntax:
IMPORTDATA(url)
Example:
=IMPORTDATA("https://example.com/data.csv")
Use case: Automatically fetch datasets from online sources for analysis, such as weather, sales, or survey data.
3. IMPORTXML
Purpose: Extract structured data from websites using XPath queries.
Syntax:
IMPORTXML(url, xpath_query)
Example:
=IMPORTXML("https://www.example.com", "//h2")
This fetches all <h2> tags from the specified website.
Use case: Scrape tables, headlines, or any structured data from websites without coding.
Also Read: IMPORTRANGE in Google Sheets
4. IMPORTHTML
Purpose: Import a table or list from a webpage.
Syntax:
IMPORTHTML(url, query, index)
Example:
=IMPORTHTML("https://example.com/stats", "table", 1)
This imports the first table from the webpage.
Use case: Track live tables, sports scores, or online rankings.
5. IMAGE
Purpose: Insert an image directly into a cell using a URL.
Syntax:
IMAGE(url, [mode], [height], [width])
Example:
=IMAGE("https://example.com/logo.png", 4, 100, 200)
Use case: Include company logos, product images, or charts in your spreadsheet without embedding files.
6. SPARKLINE
Purpose: Create mini charts within a single cell.
Syntax:
SPARKLINE(data, [options])
Example:
=SPARKLINE(B2:B10, {"charttype","line";"color","blue"})
Use case: Quickly visualize trends like sales, revenue, or performance without creating a full chart.
7. TEXTJOIN
Purpose: Join text from multiple cells with a delimiter.
Syntax:
TEXTJOIN(delimiter, ignore_empty, text1, [text2,...])
Example:
=TEXTJOIN(", ", TRUE, A2:A5)
This will join all values in A2:A5 separated by a comma.
Use case: Combine names, emails, or IDs into a single cell for easier sharing or reporting.
8. JOIN
Purpose: Combine multiple cell values into a single string (simpler than TEXTJOIN for small ranges).
Syntax:
JOIN(delimiter, array_or_range)
Example:
=JOIN(" | ", B2:B5)
Use case: Merge a small list of items into one cell for reports or labels.
9. FLATTEN
Purpose: Convert a multi-row/multi-column range into a single column.
Syntax:
FLATTEN(range1, [range2,...])
Example:
=FLATTEN(A1:C3)
Use case: Transform a 2D table into a single column for easier analysis or filtering.
10. ARRAY_CONSTRAIN
Purpose: Limit the size of an array output.
Syntax:
ARRAY_CONSTRAIN(array, num_rows, num_columns)
Example:
=ARRAY_CONSTRAIN(A1:C10, 5, 2)
This will take the first 5 rows and 2 columns from A1:C10.
Use case: Useful for controlling outputs from functions like QUERY or SORTN.
11. SORTN
Purpose: Return the top n values from a range.
Syntax:
SORTN(range, n, display_ties_mode, [sort_column, is_ascending])
Example:
=SORTN(A2:B20, 5, 0, 2, FALSE)
Returns the top 5 rows based on the second column in descending order.
Use case: Identify top performers, sales, or products.
12. SEQUENCE
Purpose: Generate a sequence of numbers in a column or row.
Syntax:
SEQUENCE(rows, [columns], [start], [step])
Example:
=SEQUENCE(5,1,10,5)
Generates 10, 15, 20, 25, 30 in a single column.
Use case: Quickly create numbered lists or custom indexes.
13. RANDARRAY
Purpose: Generate random numbers in an array.
Syntax:
RANDARRAY([rows], [columns], [min], [max], [whole_number])
Example:
=RANDARRAY(5, 2, 10, 100, TRUE)
Generates 5×2 array of random integers between 10 and 100.
Use case: Create test datasets or simulate scenarios.
14. RANDBETWEEN
Purpose: Generate a single random number between two values.
Syntax:
RANDBETWEEN(min, max)
Example:
=RANDBETWEEN(1, 50)
Use case: Random sampling, lottery numbers, or test data.
15. REGEXMATCH
Purpose: Check if text matches a regex pattern.
Syntax:
REGEXMATCH(text, regular_expression)
Example:
=REGEXMATCH(A2, "^[A-Z]{3}\d{4}$")
Checks if the cell matches a pattern like ABC1234.
Use case: Validate product codes, emails, or IDs.
16. REGEXEXTRACT
Purpose: Extract text based on a regex pattern.
Syntax:
REGEXEXTRACT(text, regular_expression)
Example:
=REGEXEXTRACT("Invoice #12345", "#(\d+)")
Extracts 12345.
Use case: Pull order numbers, IDs, or codes from text strings automatically.
17. REGEXREPLACE
Purpose: Replace text using regex pattern matching.
Syntax:
REGEXREPLACE(text, regular_expression, replacement)
Example:
=REGEXREPLACE("Call me at 9876543210", "\d{10}", "##########")
Replaces phone number with masked format.
Use case: Data cleaning, masking sensitive info, or formatting text.
18. HYPERLINK
Purpose: Create clickable links in a cell.
Syntax:
HYPERLINK(url, [link_label])
Example:
=HYPERLINK("https://smarttejas.com", "Visit Smart Tejas")
Use case: Create dashboards or reports with clickable references.
19. TEXT
Purpose: Format numbers, dates, or times into custom strings.
Syntax:
TEXT(value, format_text)
Example:
=TEXT(TODAY(), "DD-MMM-YYYY")
Use case: Display dates, currency, or percentages in a readable format.
20. PROPER, UPPER, LOWER
Purpose: Convert text capitalization.
Syntax & Examples:
=PROPER("hello world") → "Hello World"
=UPPER("hello") → "HELLO"
=LOWER("HELLO") → "hello"
Use case: Standardize text for reports, names, or addresses.
21. TRIM
Purpose: Remove extra spaces from text.
Syntax:
TRIM(text)
Example:
=TRIM(" Hello World ")
Output: Hello World
Use case: Clean up imported data or user input.
22. LEN
Purpose: Count the number of characters in a string.
Syntax:
LEN(text)
Example:
=LEN(A2)
Use case: Validate text length like product codes, phone numbers, or IDs.
23. VALUE
Purpose: Convert text into a numeric value.
Syntax:
VALUE(text)
Example:
=VALUE("123.45")
Use case: Useful when importing numeric data as text.
24. ISBLANK
Purpose: Check if a cell is empty.
Syntax:
ISBLANK(cell)
Example:
=ISBLANK(B2)
Use case: Conditional checks, data validation, or cleaning datasets.
25. IFNA
Purpose: Return a custom value if a formula returns #N/A.
Syntax:
IFNA(value, value_if_na)
Example:
=IFNA(VLOOKUP(101, A2:B10, 2, FALSE), "Not Found")
Use case: Avoid #N/A errors in reports or dashboards.
26. IFERROR
Purpose: Return a custom value if a formula returns an error.
Syntax:
IFERROR(value, value_if_error)
Example:
=IFERROR(A2/B2, "Division Error")
Use case: Handle errors gracefully in reports or calculations.
27. NOW / TODAY
Purpose: Get current date and time or just the date.
Syntax:
=NOW() → returns date & time
=TODAY() → returns current date
Use case: Create dynamic timestamps, dashboards, or deadlines.
28. OFFSET
Purpose: Returns a range shifted from a starting cell.
Syntax:
OFFSET(reference, rows, cols, [height], [width])
Example:
=SUM(OFFSET(A1,1,0,3,1))
Sums 3 cells below A1.
Use case: Dynamic ranges for formulas like SUM, AVERAGE, etc.
29. INDIRECT
Purpose: Refer to a cell or range dynamically using a text string.
Syntax:
INDIRECT(cell_reference_as_text)
Example:
=INDIRECT("A"&B1)
If B1=5, it refers to A5.
Use case: Dynamic formulas based on user input or dashboard selections.
30. INFO
Purpose: Returns system information about Google Sheets environment.
Syntax:
INFO(type_text)
Example:
=INFO("timezone")
Use case: Helpful for global spreadsheets or troubleshooting.
Conclusion
Google Sheets is more than just a spreadsheet tool. With these 25+ functions, you can:
- Fetch live data from websites and stock markets
- Clean and standardize data efficiently
- Build dynamic dashboards and mini-charts
- Handle text, numbers, and arrays effortlessly
- Automate tasks without coding
Unlike Excel, Google Sheets’ web integration, regex capabilities, and collaboration features make it ideal for modern data analysis. Learning and mastering these functions will make your spreadsheets smarter, faster, and more insightful.