One of the most important features of Google Sheets spreadsheet software is the ability to use formulas of all complexities. It allows you to calculate, analyze, and manipulate data for scholarly work, business, accounting, research, and countless other fields where managing information is of paramount importance.
In this guide, we’ll dive into the most useful Google Sheets formulas. Feel free to use this as your cheat sheet on your next work session!
We also have a new tool, the Google Sheet formula generator, that can help you with any Google Sheets formula.
Use this guide to save the data generated by the formulas in Google sheets.
Easily Insert Google Sheets Formulas Using Automatic Suggestions
To insert formulas and functions into a cell, follow the steps below. Google Sheets will provide you with suggestions on-screen that help you easily select a formula relevant to your data using AI.
If you’re already familiar with how to insert formulas, skip this section and go straight to our Cheat Sheet below:
Step 1. In your Google Sheets spreadsheet, type in the equal sign “ = ” in a cell you want to use.
Step 2. You should see suggestions automatically based on your data. If the suggestions aren’t showing up, turn on automatic suggestions by clicking the three-dot icon as shown below. Make sure Enable formula suggestions and named functions suggestions are checked.
Step 3. If the suggestion is different from what you need, type in a letter and Google Sheets will provide you a list of formulas and functions to choose from.
In our example, the suggestion was to use the Average formula to calculate the average of the selected cells. Let’s say we’d like to discover the highest number in our table. This is going to require Max to calculate the maximum value in our dataset.
Instead of going with the Average formula, we type in Max and the suggestion will automatically pop up.
Step 4. After selecting Max, you will be prompted to select your Range. Simply put, the cell range is the selection of cells or rows or columns to be used to perform the action.
In this example, Google Sheets automatically suggested the list of numbers under Grades to use in the formula. If you need to change it, you may manually select a different range instead.
Note: Alternatively, you can select a range of cells prior to entering a formula. Just highlight the cells you’d like to use then press the Functions icon on your menu bar. If you’re using a lower-resolution display, it might be tucked away under the dropdown icon.
Step 5. With your range already selected, press Enter. Congratulations, your cell is now performing calculations on your data!
Top Google Sheets Formulas Cheat Sheet
In this list, we’re compiling the best Google Sheets formulas you should be using right now to be able to manipulate your data in a more powerful and meaningful way.
14 Useful Mathematical and Statistical Functions
These are some of the most useful math and statistics functions you have to be using on Google Sheets. This list is great for educators, students, and data analysts.
| Name | Description | Syntax |
|---|---|---|
| SUM | Returns the sum of a series of numbers or cells. | SUM(value1, [value2, …]) |
| SUMIF | Returns the sum of a series of cells according to a specified condition. | SUMIF(range, criterion, [sum_range]) |
| AVERAGE | Returns the numerical average value in a dataset, ignoring text. | AVERAGE(value1, [value2, …]) |
| MEDIAN | Returns the median value in a numeric dataset. | MEDIAN(value1, [value2, …]) |
| MODE | Returns the most commonly occurring value in a dataset (i.e. most popular/most prevalent) | MODE(value1, [value2, …]) |
| MIN | Returns the minimum value in a numeric dataset. | MIN(value1, [value2, …]) |
| MAX | Returns the maximum value in a numeric dataset. | MAX(value1, [value2, …]) |
| COUNT | Returns a count of the number of numeric values in a dataset | COUNT(value1, [value2, …]) |
| RANK | Returns the rank of a specified value in a dataset. | RANK(value, data, [is_ascending]) |
| PI | Returns the value of Pi to 14 decimal places. | PI() |
| ODD | Rounds a number up to the nearest odd integer. | ODD(value) |
| EVEN | Rounds a number up to the nearest even integer. | EVEN(value) |
| EXP | Returns Euler’s number, e (~2.718) raised to a power. | EXP(exponent) |
| ROUND | Rounds a number to a certain number of decimal places, always rounding up to the next valid increment. | ROUNDUP(value, [places]) |
9 Logical Functions to Master
Logical functions in Google Sheets are fundamental for creating complex decision-making instructions for your spreadsheets on the fly. Lido best explains the usage of Logical functions in this article.
| Name | Description | Syntax |
|---|---|---|
| IF | Returns one value if a logical expression is `TRUE` and another if it is `FALSE` | IF(logical_expression, value_if_true, value_if_false) |
| IFS | Evaluates multiple conditions and returns a value that corresponds to the first true condition. | IFS(condition1, value1, condition2, value2, …) |
| IFERROR | Returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent. | IFERROR(value, [value_if_error]) |
| SWITCH | Tests an expression against a list of cases and returns the corresponding value of the first matching case, with an optional default value if nothing else is met. | SWITCH(expression, case1, value1, [case2, value2, …], [default]) |
| AND | Returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false. | AND(logical_expression1, [logical_expression2, …]) |
| OR | Returns true if any of the provided arguments are logically true, and false if all of the provided arguments are logically false. | OR(logical_expression1, [logical_expression2, …]) |
| NOT | Returns the opposite of a logical value – `NOT(TRUE)` returns `FALSE`; `NOT(FALSE)` returns `TRUE` | NOT(logical_expression) |
| TRUE | Returns the logical value `TRUE`. | TRUE() |
| FALSE | Returns the logical value `FALSE`. | FALSE() |
21 Essential Date Functions
Dates are the core information of many spreadsheet setups. Master these to ante up your date management and analysis up a notch!
| Name | Description | Syntax |
|---|---|---|
| DATE | Converts a provided year, month, and day into a date | DATE(year, month, day) |
| DATEDIF | Calculates the number of days, months, or years between two dates. | DATEDIF(start_date, end_date, unit) |
| DATEVALUE | Converts a provided date string in a known format to a date value. | DATEVALUE(date_string) |
| DAY | Returns the day of the month that a specific date falls on, in numeric format. | DAY(date) |
| DAYS360 | Returns the difference between two days based on the 360 day year used in some financial interest calculations. | DAYS360(start_date, end_date, [method]) |
| EDATE | Returns a date a specified number of months before or after another date. | EDATE(start_date, months) |
| EOMONTH | Returns a date representing the last day of a month which falls a specified number of months before or after another date. | EOMONTH(start_date, months) |
| HOUR | Returns the hour component of a specific time, in numeric format. | HOUR(time) |
| ISOWEEKNUM | Returns the number of the ISO week of the year where the provided date falls. | ISOWEEKNUM(date) |
| MINUTE | Returns the minute component of a specific time, in numeric format. | MINUTE(time) |
| MONTH | Returns the month of the year a specific date falls in, in numeric format. | MONTH(date) |
| NETWORKDAYS | Returns the number of net working days between two provided days. | NETWORKDAYS(start_date, end_date, [holidays]) |
| NOW | Returns the current date and time as a date value. | NOW() |
| SECOND | Returns the second component of a specific time, in numeric format. | SECOND(time) |
| TIME | Converts a provided hour, minute, and second into a time. | TIME(hour, minute, second) |
| TIMEVALUE | Returns the fraction of a 24-hour day the time represents. | TIMEVALUE(time_string) |
| TODAY | Returns the current date as a date value. | TODAY() |
| WEEKDAY | Returns a number representing the day of the week of the date provided. | WEEKDAY(date, [type]) |
| WEEKNUM | Returns a number representing the week of the year where the provided date falls. | WEEKNUM(date, [type]) |
| WORKDAY | Calculates the end date after a specified number of working days. | WORKDAY(start_date, num_days, [holidays]) |
| YEAR | Returns the year specified by a given date | YEAR(date) |
11 Powerful Text Functions
Text functions in Google Sheets allow you to manipulate your data in several ways just like magic. Use the cheat sheet below for the crucial text functions you need to know such as CONCATENATE and JOIN.
| Name | Description | Syntax |
|---|---|---|
| CONCATENATE | Appends strings to one another. | CONCATENATE(string1, [string2, …]) |
| CLEAN | Returns the text with the non-printable ASCII characters removed. | CLEAN(text) |
| EXACT | Tests whether two strings are identical. | EXACT(string1, string2) |
| FIND | Returns the position at which a string is first found within text. | FIND(search_for, text_to_search, [starting_at]) |
| FIXED | Formats a number with a fixed number of decimal places. | FIXED(number, [number_of_places], [suppress_separator]) |
| JOIN | Concatenates the elements of one or more one-dimensional arrays using a specified delimiter. | JOIN(delimiter, value_or_array1, [value_or_array2, …]) |
| LOWER | Converts a specified string to lowercase. | LOWER(text) |
| UPPER | Converts a specified string to uppercase. | UPPER(text) |
| HYPERLINK | Creates a hyperlink inside a cell. | HYPERLINK(url, [link_label] |
| VALUE | Converts a string in any of the date, time or number formats that Google Sheets understands into a number. | VALUE(text) |
| TEXT | Converts a number into text according to a specified format. | TEXT(number, format) |
Downloadable PDF Formulas Cheat Sheet
Feel free to download the free PDF version of our Top 55 Most Useful Google Sheets Formula Cheat Sheet! Just download and have it handy for your next spreadsheet projects.
PDF Download link – Top 55 Most Useful Google Sheets Formula Cheat Sheet
Conclusion on Top 55 Google Sheets Formulas Cheat Sheet
Having a cheat sheet handy for learning the essential and most useful functions and formulas is a must-have if you are to succeed in data sets with higher complexity in Google Sheets. By no means is this list an exhaustive list of all the Google Sheets functions but treat this as a summary of the essentials you should be using more frequently if you aren’t already.
Today we’ve uncovered math and statistical functions; logical operators; text functions; and date functions. Want to learn more? Be sure to subscribe to our blog at the jump below designed to help you become more productive in your Google Sheets and Docs journey!
Get More Things Done Using FileDrop for Google Sheets
Looking to be even more productive? We at FileDrop offer a powerful and add-on for Google Sheets, Docs and Slides that ightly integrates those apps with your Google Drive. It allows you to drag and drop files into your documents and spreadsheets, provides a way to organize those files in a library, and has even more features like optical character recognition for converting PDFs and images to text!
There are a lot of things FileDrop can do including the following:
- Drag and Drop files from your computer.
- A File Library space to manage the files you’ve added and use them again.
- Add files already inside your Google Drive in Sheets cells
- PDF to text directly from Docs, Slides and Sheets using OCR technology
- Image to text without leaving the app.
- + lots of more time-saving features with regular updates!
Using it is simple! Learn more about our add-on by visiting FileDrop and find out how you can save a lot of time by streamlining your processes using our free tool.
The Bottom Line:
One keeps you awake. The other gets work done.
A month of coffee: $150
A month of FileDrop: $9.99
Why not have both?
