These blog posts are part of research I’m doing for a potential new book, hopefully allowing me to further my love for spreadsheets. Theses posts are hoping to provide a practical cookbook of examples to the reader and allow me to learn what people are interested in and how to approach different subjects working with Google Sheets.
This has been a good experience in getting starting in my up skilling in Google Sheets. These are a good chunk of the main functions used to chain together formulas to process your data on Google Sheets. I will refer to this sheet in future posts, to help explain functions, and will add to it when I find some more functions that need to be added.
Current list includes the following 39 functions:
- ARRAYFORMULA
- AVERAGE
- CLEAN
- CONCAT
- CONCATENATE
- COUNT
- DATE
- DATEDIF
- DAY
- EDATE
- EOMONTH
- EVEN
- EXACT
- IF
- IMPORTDATA
- IMPORTRANGE
- INDIRECT
- LAMBDA
- LEN
- LOWER
- MATCH
- MAX
- MIN
- NOW
- ODD
- PI
- QUERY
- RANDARRAY
- ROUND
- SEQUENCE
- SORT
- SPARKLINE
- SUM
- SUMIF
- SWITCH
- TEXT
- TODAY
- UNIQUE
- VLOOKUP
- YEAR
- ARRAYFORMULA: Allows you to output a range of cells instead of just a single value and can be used with non-array functions. You could have price in one column, quantity in a second column, and then create an array formula for value in the third, then providing the value for the entire range:
- Syntax:
=ARRAYFORMULA(array_formula) - Eg:
=ARRAYFORMULA(M41:N45 + O41:P45)- In the above example, it will consolidate four columns into two, where values in column M, will be added to values in column O. Values in column N will be added to column P.
- Syntax:
- AVERAGE: This returns the numerical average in a range of data, ignoring any text
- Syntax:
=AVERAGE(value1, [value2, …]) - Eg;
=AVERAGE(B1:B10)- The above provides an average from all the values in B1 to B10.
- Syntax:
- CLEAN: This function returns the text without the non printable ASCII characters removed:
- Syntax:
=CLEAN(text) - Eg:
=CLEAN("AF"&CHAR(31))- In the example, we will only have the characters, AF returned.
- Syntax:
- CONCAT: Join two values together
- Syntax:
=CONCAT(value1, value2) - Eg; =CONCAT(B64, “ Text value”)
- In the above we have used a cell reference for the first value and a string for the second
- Syntax:
- CONCATENATE: Used to join multiple text strings into one singe text string. These can either be by a cell reference, or add the string directly in the formula:
- Syntax:
=CONCATENATE(text1, [text2, ...]) - Eg;
=CONCATENATE("Test data ", B35, " and ", D35)
- Syntax:
- COUNT: Returns the number of numeric values in a range of data. If there are any non numerical values, like a word or string, this will not be included in the count:
- Syntax:
=COUNT(value1, [value2, …]) - Eg:
=COUNT(A2:A100, B2:B100)
- Syntax:
- DATE: Converts values of a year, month and day and provides a date value. Values provided need to be in the form of numbers.
- Syntax:
=DATE(year, month, day) - Eg:
=DATE(1978, 5, 31)- Provides the value of 5/31/1978, but this is mostly useful when providing cell references as the values to the formula.
- Syntax:
- DATEDIF: Provides the difference between two dates. The formula will provide a number value, depending on the unit provided, which could be one of 'Y', 'M', 'D', 'MD', 'YM', 'YD'.
- Syntax:
=DATEDIF(start_date, end_date, unit) - Eg:
=DATEDIF(TODAY()-5, TODAY(), "D")- The above will result in 5
- Syntax:
- DAY: Returns the numerical day, that the date provided falls on.
- Syntax:
=DAY(date) - Eg:
=DAY("7/20/1969")- The above will return 20.
- Syntax:
- EDATE: Returns the date, for a specified number of months before or after the start_date provided. A negative number of months is provided to find the date before, and a positive number of months to find the date after.
- Syntax:
=EDATE(start_date, [months]) - Eg:
=EDATE("7/20/1999", -2)- The above example is looking for the date 2 months before the start_date, and will return the value or 5/20/1999.
- Syntax:
- EOMONTH: This function returns a date on the last day of a month that falls a specified number of months before or after another date. Zero months will provide the current month, with positive or negative numbers able to be used.
- Syntax:
=EOMONTH(start_date, months) - Eg:
=EOMONTH(TODAY(),-2)- The above example uses the TODAY() function to provide the current month, and we us -2 to find the date of the final day, two months ago.
- Syntax:
- EVEN: This rounds a number up to the nearest even integer
- Syntax:
=EVEN(value) - Eg:
=EVEN(19)- The above will round the number up to 20
- Syntax:
- EXACT: Tests if two string values are both exactly the same. This can be values directly in the formula or references to cells.
- Syntax:
=EXACT(string1, string2) - Eg:
=EXACT(H43,F39)- The example looks to compare two cell values and if they are both the same, will give a TRUE result, and if not, will return a FALSE value.
- Syntax:
- IF: Performs conditional logic that is based on a given condition
- Syntax:
=**IF(logical_expression, value_if_true, value_if_false)** - Eg:
=IF(M14 < 10, “Less than 10”, “10 or more”)- The above is simple where if the value in cell M14, if it is less than 10, you provide the first string, and if it is more than 10, you provide the second value.
- Syntax:
- IMPORTDATA: This function allows you to import data from a URL into your sheet in either csv or tsv format. You provide the URL, separator(eg; “,”) and an optional value of language for the data:
- Syntax:
=IMPORTDATA(URL, delimiter, locale) - Eg:
=IMPORTDATA("[https://weather.visualcrossing.com/…contentType=csv]")- Using Visual Crossing, you can sign up to get access to their API for current weather data. The above example imports data from this api.
- Syntax:
- IMPORTRANGE: This function allows you to import data from one spreadsheet to another. The sheet can be in a different location or folder
- Syntax:
=IMPORTRANGE(spreadsheet_url, range_string) - Eg:
=IMPORTRANCE(<sheet_url>, Sheet1!A1:B7)- Above the sheet_url, is the URL value provided when you want to share a sheet, and the range_string includes both the sheet name of Sheet1 and the range of data from A1 to B7
- Syntax:
- INDIRECT: Returns the reference to a cell from string values provided. The “is_A1_notation” is either TRUE or FALSE. FALSE implies that the cell reference is in R1C1 notation.
- Syntax:
=INDIRECT(cell_reference_as_string, [is_A1_notation]) - Eg:
=INDIRECT("A"&"1", TRUE)- The example will provide the value in the cell A1.
- Syntax:
- LAMBDA: This function allows you to create a custom function to use in your sheet. The name of the value will be used as a variable or parameter parsed to the function, with the formula then using these parameters as part of the expression:
- Syntax:
=LAMBDA([name, …], formula_expression) - Eg1:
=LAMBDA(miles, miles * 1.60934)(5)- The above example, with the function called miles, takes the value provided at the end of the LAMBDA, which is 5 and multiplies it by 1.6 to give you the number of kilometres
- Eg2:
=LAMBDA(test, UPPER(test))(J36)- The above might be a little clearer, where test is the name of the formula, but we also assign the value in cell J36 to that and return the text in uppercase text.
- Syntax:
- LEN: Calculate the number of characters in text or a cell.
- Syntax:
=LEN(text) - Eg:
=LEN(”string”)
- Syntax:
- LOWER: Converts all the specific text to lower case characters:
- Syntax:
=LOWER(text) - Eg:
=LOWER("Monday")- The example will convert all characters to lower case as “monday”.
- Syntax:
- MATCH: This function returns the relative position within a range of cells. The position is in the form of a number, so it can be used with other functions like INDEX of VLOOKUP:
- Syntax:
=MATCH(search_key, range, [search_type]) - Eg:
=MATCH("December, 10th", H39:H59, 1)- Above we are searching for a value “December, 10th” from a range H39:H59.
- The search_type value can be left blank and will default to 1 which is an approximate match of values in ascending order, 0 for a exact match, and -1 for again and approximate match in descending order.
- Syntax:
- MAX: Provide the maximum value in a range of data
- Syntax:
=**MAX(value1, [value2, …])** - Eg:
=MAX(A1:A10)- The example will provide the largest value from the range provided.
- Syntax:
- MIN: Opposite to the MAX function, this provides the minimum value in a range of data
- Syntax:
=**MIN(value1, [value2, …])** - Eg:
=MIN(A35:J35, 8)- In the example above, we have provided more than one value. First a range of cells, then the number 8. Where the formula will return the smallest value in all the ranges provided.
- Syntax:
- NOW: Similar to the TODAY function, but also returns the current time as well as the date.
- Syntax:
=NOW() - Eg: The returned value in the cell will be in the format of “2/3/2025 11:50:03” showing both date and time
- Syntax:
- ODD: This rounds a number up to the nearest odd integer
- Syntax:
=ODD(value) - Eg:
=ODD(10)- The above will round the number up to 11
- Syntax:
- PI: Returns the value of Pi to 14 decimal places
- Syntax:
=PI()
- Syntax:
- QUERY: This function allows you to extract and manipulate date from a specific range in your sheet and use SQL like queries on the data. When using the function, you provide a set of data, then a query, and then the number of header rows you need.
- Syntax:
=QUERY(data, query, [headers]) - Eg:
=QUERY(A37:D55, "select B, D limit 5", 1)- In the above we are using the query to select columns B and D, with a limit of 5 rows, from the data range, with one header value.
- Syntax:
- RANDARRAY: Provides a grid of random numbers from 0 to 1. The grid size will match the number of rows and columns provided as arguments. If no values are provided, it will be one random value in one cell.
- Syntax:
=RANDARRAY([rows], [columns]) - Eg:
=RANDARRAY(1 ,4)- The example will provide values for one row and four columns
- 0.644330833, 0.3389935013, 0.7119694899, 0.9485712661
- Syntax:
- ROUND: Rounds the value provided to 2 decimal places, or if a second value is provided, rounded to that number.
- Syntax:
=ROUND(value, [places]) - Eg:
=ROUNDUP(1.11111111, 3)- The above number is rounded to 1.111, as we have provided the second property of 3 in the formula.
- Syntax:
- SEQUENCE: This is a function that lets you generate a sequence of numbers in the specified range based on a starting value and a step value.
- Syntax:
=SEQUENCE(rows, [columns], [start], [step]) - Eg: =SEQUENCE(5, 3, 2, 8)
- The above creates a sequence of numbers with 3 rows across, and 5 columns down, starting at number 2 and then increasing by 8, for each number.
- Syntax:
- SORT: Sort the rows of a given range of data
- Syntax:
=SORT(range, sort_column, is_ascending, [sort_column2, …], [is_ascending2, …]) - Eg:
=SORT(A35:J50, 2, TRUE, 3, FALSE)- In the above example, in the range of data, the second column is sorted ascending, while the 3 column is not.
- Syntax:
- SPARKLINE: Create a mini chart within a single cell without needing to use the sheets chart builder function. By default, the chart will be a line graph, but the options available allow you to define a bar or column chart.
- Syntax:
=SPARKLINE(data, [options]) - Eg:
=SPARKLINE(B35:J35, {"charttype", "column"})- The above example will use the values in the cell range provided and provide a small column chart.
- Syntax:
- SWITCH: Like the IF function, it allows you to test against a list of cases and provide a corresponding result.
- Syntax:
=SWITCH(expression, case1, value1, [case2_or_default, …], [value2, …]) - Eg:
=switch(A1,1,"Sun",2,"Mon",3,"Tues",4,"Wed",5, "Thur",6,"Fri",7,"Sat")- As you can see it is a little nicer than nesting IF statements. It looks to see the value in A1, and whatever the corresponding number value, it provides a day of the week value.
- Syntax:
- SUM: The formula returns the sum of a series or race of numbers or cells
- Syntax:
=SUM(value1, [value2, …]) - Eg;
=SUM(B35:J35, 10)- Here this will sum the numbers in the range from B35 to J35 and then add 10 on top of this value.
- Syntax:
- SUMIF: This allows you to add up the values of a range based on a specific condition. You only need one condition, but can add as many as you like:
- Syntax:
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …]) - Eg:
=SUMIFS(A1:A10, B1:B10, ">20", C1:C10, "<30")- The above will sum the values in the range A1:A10 only if the values in B1:B10 are less than 20 and the values in C1:C10 are less than 30.
- Syntax:
- TODAY: Returns the current date as a date value.
- Syntax:
=TODAY() - Eg: The returned value in the cell will be in the format of “2/3/2025” showing just the date
- Syntax:
- TEXT: Converts a number into text, depending on the format provided in the formula:
- Syntax:
=TEXT(number, format) - Eg:
=TEXT(1.5, "$0.00")- In the example above, we are using 1.5, but you can easily refer to a value in a cell. The value will be converted to “$1.50” as per the format provided in the formula.
- Syntax:
- UNIQUE: This function returns a unique row from a range provides. The returned values have duplicate values removed and rows are returned in the order in which they first appear. The options by_column(how to filter the data), and exactly_once are optional.
- Syntax:
=UNIQUE(range, [by_column], [exactly_once]) - Eg:
=UNIQUE(J38:J40)- A simple example showing a column of data to provide only unique values from the range.
- Syntax:
- VLOOKUP: Search for a value in the first column of the range or table and return a corresponding value, from a specified column in the same row.
- Syntax:
=VLOOKUP(search_key, range, index, is_sorted) - Eg;
=VLOOKUP(F5, A1:D6, 2)- F5 is the value you are looking for
- A1:D6 is the range or table you are searching
- 2 is the second column in the table
- The is_sorted value is optional. If it is sorted(1 or 0), then the first match to search_key will be returned
- Syntax:
- YEAR: This function returns the year for a date provided
- Syntax:
=YEAR(date) - Eg:
=YEAR(TODAY())- The example, uses the TODAY function to provide todays date and will return a year value in the format of YYYY, like 2025.
- Syntax:
About The Author
The post is written by Vincent Sesto, a Aussie Software Engineer, living and working in Auckland, New Zealand. If you are interested in my authors page on Amazon, feel free to checked it out at the following link: Amazon.com Vincent Sesto: books, biography, latest update
People can always post questions, ideas and request clarification !LUV
(1/3) sent you LUV. | tools | discord | community | HiveWiki | <>< daily@strava2hive, @run.vince.run
I have a few sheets for tracking things, including some Hive stats. I don't use too many functions for that. It's cool to have these tools available though.
Yep, always trying to do more with what we have instead of investing in new tools. Thanks for the support @steevc