You can modify your Google Sheets data and simplify processes by using this practical cheat sheet, which is available for free download and printing.
Google Sheets is an excellent Microsoft Excel substitute. It is loaded with functionalities, the most of which are found in Excel, and many other useful features. Procedures that would typically take a long time and a lot of effort can be sped up using functions in Sheets.
These are the crucial features you should remember if you’re new to Google Sheets or are getting ready to switch from Microsoft’s software.
Effective Google Sheets features
| Name | Syntax | Description |
|---|---|---|
| Array | ||
| GROWTH | 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 | TRANSPOSE(array_or_range) | Rearranges a selection of cells by swapping the columns and rows into rows and columns, and vice versa. |
| Date | ||
| DATE | DATE(year, month, day) | Changes the selected value and returns it in date format. |
| NOW | NOW() | Provides the current date and time. |
| TODAY | TODAY() | AutomaticaSyntax and Description of Name Array (known_data_y, [known_data_x], [new_data_x], [b])provides expected values in order to predict an exponential growth trend using partial data that is already available. TRANSPOSETRANSPOSE(array_or_range) flips rows and columns into columns and vice versa to rearrange a set of cells. Date DATE DATE(month, day, year)the specified value is modified, and the date-format value is returned. NOW() returns the time and date as of right now. AUTOMATIC TODAY TODAY()Automatically updates a cell and returns the current date. |
| Filter | ||
| FILTER | FILTER(range, condition1, [condition2]) | Displays a filtered version of the range matching the conditions. |
| SORT | 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 | UNIQUE(range) | Displays unique rows in the range without duplicates. |
| Logical | ||
| IF | 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 | 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.Known_data_y, [known_data_x], [new_data_x], [b], Array GROWTH GROWTH(known_data_y, [known_data_x], [new_data_x], [b])provides expected values in order to predict an exponential growth trend using partial data that is already available. TRANSPOSE Transposes a group of cells by switching the columns and rows for the rows and vice versa. TRANSPOSE(array_or_range) rearranges the cells. Date DATE DATE(month, day, year)the specified value is modified, and the date-format value is returned. RIGHT NOW()gives the time and date as of the moment. NOW TODAY()AutomaticaBy leveraging partial data that is already accessible, the name array’s syntax and description (known_data_y, [known_data_x], [new_data_x], [b]) gives expected values for predicting an exponential growth trend. To reorder a group of cells, use TRANSPOSETRANSPOSE(array_or_range), which converts rows and columns into columns and vice versa. Date The supplied value is updated, and the date-format value is DATE DATE(month, day, year).Displays the first value if there is no error, the second if there is an error, and is blank if no argument is present. |
| Lookup | ||
| FORMULATEXT | FORMULATEXT(cell) | Displays the formula as a text string. |
| VLOOKUP | VLOOKUP(search_key, range, index, [is_sorted]) | Searches a column for a value and returns it in the corresponding row. |
| XLOOKUP | 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 |
| Math | ||
| BASE | BASE(value, base, [min_length]) | Converts a number into another base. |
| COUNTBLANK | COUNTBLANK(range) | Calculates the number of blank cells in the range. |
| COUNTIF | COUNTIF(range, criterion) | Counts the number of cells that meet the criteria. |
| RAND | RAND() | Generates a random number between 0 and 1. |
| ROUNDUP | ROUNDUP(value, [places]) | Rounds a number up to the specified number of places. |
| SUM | SUM(value1, [value2, …]) | Calculates the sum of values in the selected group of cells or numbers. |
| SUMIF | 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.FORMULATEXT displays the formula as a text string when used with cell. VLOOKUPsearch_key, range, index, [is_sorted], VLOOKUPdelivers a value that was found during a column search in the corresponding row. XLOOKUPMissing value, [match_mode], [search_mode], [search_key], lookup range, result rangeprovides the value that was found after performing a vertical and horizontal range search. BASE BASE(value, base, [min_length]) in mathematicstransforms a number into a different base. Calculates the amount of empty cells in the range using COUNTBLANK COUNTBLANK(range). COUNTIFCOUNTIF(range, criterion) counts how many cells in the range satisfy the criterion. A random number between 0 and 1 is produced using RAND RAND(). COMPUTE COMPUTE(value, [places])increases a number’s precision by the supplied amount. The function SUM SUM(value1, [value2,…]] calculates the total of the values in the selected set of cells or numbers. (range, SUMIF SUMIF(Calculates the sum of values in a group of cells if they meet a specific criterion or fit a specific category. |
| Statistical | ||
| AVERAGE | AVERAGE(value1, [value2, …]) | Finds the average of the numerical values in the selected cells while ignoring text. |
| MAX | MAX(value1, [value2, …]) | Finds the maximum value in the set. |
| MIN | MIN(value1, [value2, …]) | Finds the minimum value in the set. |
| MODE | MODE(value1, [value2, …]) | Finds the most common value in the set. |
| Text | ||
| CONCATENATE | CONCATENATE(string1, [string2, …]) | Combines the values of two separate cells into one. |
| SEARCH | SEARCH(search_for, text_to_search, [starting_at]) | Searches for a value and returns where it’s located within text. |
| SPLIT | 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 | TEXT(number, format) | Changes a number into various text formats. |
| Web | ||
| IMPORTFEED | IMPORTFEED(URL, [query], [headers], [num_items]) | Imports and provides an RSS or ATOM feed as a value. |
| IMPORTXML | IMPORTXML(URL, xpath_query) | Imports different kinds of data from a selected URL, including HTML, XML, CSV, TSV, RSS, and ATOM XML feed |
For anyone using Google Sheets or equivalent spreadsheet applications, functions are practically a requirement. They assist in accelerating your job so that you may concentrate on what’s important by helping to simplify duties.
But if you want to get the most out of Google Sheets, you might want to check at all the Windows and Mac keyboard shortcuts that are offered. You’ll master Google Sheets using both keyboard shortcuts and features.