Google Sheets is a great alternative to Microsoft Excel. It’s packed to the brim with useful features, including a wide range of functions that include most of those available on Excel and more. You can use functions in Sheets to simplify processes that would otherwise take significant time and effort.
If you’re just getting starting with Google Sheets, or you’re ready to take the leap from Microsoft’s program, these are the essential functions you should keep in mind.
FREE DOWNLOAD:This cheat sheet is available as a downloadable PDF from our distribution partner, TradePub. You will have to complete a short form to access it for the first time. Download theGoogle Sheets Functions Cheat Sheet.
Useful Google Sheets Functions
Description
GROWTH(known_data_y, [known_data_x], [new_data_x], [b])
Uses existing partial data to predict an exponential growth trend by providing predicted values.
TRANSPOSE(array_or_range)
Rearranges a selection of cells by swapping the columns and rows into rows and columns, and vice versa.
DATE(year, month, day)
Changes the selected value and returns it in date format.
Provides the current date and time.
Automatically updates a cell and returns the current date.
FILTER(range, condition1, [condition2])
Displays a filtered version of the range matching the conditions.
SORT(range, sort_column, is_ascending, [sort_column2], [is_ascending2])*
Sorts the selected rows of cells in a specific order according to the values in one or more columns.
UNIQUE(range)
Displays unique rows in the range without duplicates.
IF(logical_expression, value_if_true, value_if_false)
Provides either a true or false value if the selected variable exists in the selected cells.
IFERROR(value, [value_if_error])
Displays the first value if there is no error, the second if there is an error, and is blank if no argument is present.
FORMULATEXT
FORMULATEXT(cell)
Displays the formula as a text string.
VLOOKUP(search_key, range, index, [is_sorted])
Searches a column for a value and returns it in the corresponding row.
XLOOKUP(search_key, lookup_range, result_range, missing_value, [match_mode], [search_mode])
Searches the range for a value (vertical and horizontal) and returns it or the closest match
BASE(value, base, [min_length])
Converts a number into another base.
COUNTBLANK
COUNTBLANK(range)
Calculates the number of blank cells in the range.
COUNTIF(range, criterion)
Counts the number of cells that meet the criteria.
Generates a random number between 0 and 1.
ROUNDUP(value, [places])
Rounds a number up to the specified number of places.
SUM(value1, [value2, …])
Calculates the sum of values in the selected group of cells or numbers.
SUMIF(range, criterion, [sum_range])
Calculates the sum of values in a group of cells if they meet a specific criterion or fit a specific category.
Statistical
AVERAGE(value1, [value2, …])
Finds the average of the numerical values in the selected cells while ignoring text.
MAX(value1, [value2, …])
Finds the maximum value in the set.
MIN(value1, [value2, …])
Finds the minimum value in the set.
MODE(value1, [value2, …])
Finds the most common value in the set.
CONCATENATE
CONCATENATE(string1, [string2, …])
Combines the values of two separate cells into one.
SEARCH(search_for, text_to_search, [starting_at])
Searches for a value and returns where it’s located within text.
SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
Splits text of a given value and divides it into multiple cells.
SUBSTITUTE
SUBSTITUTE(text_to_search, search_for, replace_with, [occurence_number])
Changes the existing text and provides new text in a string.
TEXT(number, format)
Changes a number into various text formats.
IMPORTFEED
IMPORTFEED(URL, [query], [headers], [num_items])
Imports and provides an RSS or ATOM feed as a value.
IMPORTXML(URL, xpath_query)
Imports different kinds of data from a selected URL, including HTML, XML, CSV, TSV, RSS, and ATOM XML feeds.
Improve Efficiency With Google Sheets Functions
Functions are almost a necessity for anyone using Google Sheets or similar spreadsheet software. Thanks to their ability to simplify tasks, they help speed up your work so that you can focus on what’s important.
To make the most of Google Sheets, though, you might consider looking into all the available keyboard shortcuts for Windows and Mac. With both keyboard shortcuts and functions, you’ll be a Google Sheets pro.
