Google sheets formulas pdf

Top 55 Most Useful Google Sheets Formula Cheat Sheet

Google Sheets is the go-to spreadsheet software for working with data on the cloud. One of the most important features of 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.

Google sheets formulas pdf 1

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.

Google sheets formulas pdf 2

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.

Google sheets formulas pdf 3

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.

Google sheets formulas pdf 4

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.

SUMReturns the sum of a series of numbers or cells.SUM(value1, [value2, …])
SUMIFReturns the sum of a series of cells according to a specified condition.SUMIF(range, criterion, [sum_range])
AVERAGEReturns the numerical average value in a dataset, ignoring text.AVERAGE(value1, [value2, …])
MEDIANReturns the median value in a numeric dataset.MEDIAN(value1, [value2, …])
MODEReturns the most commonly occurring value in a dataset (i.e. most popular/most prevalent)MODE(value1, [value2, …])
MINReturns the minimum value in a numeric dataset.MIN(value1, [value2, …])
MAXReturns the maximum value in a numeric dataset.MAX(value1, [value2, …])
COUNTReturns a count of the number of numeric values in a datasetCOUNT(value1, [value2, …])
RANKReturns the rank of a specified value in a dataset.RANK(value, data, [is_ascending])
PIReturns the value of Pi to 14 decimal places.PI()
ODDRounds a number up to the nearest odd integer.ODD(value)
EVENRounds a number up to the nearest even integer.EVEN(value)
EXPReturns Euler’s number, e (~2.718) raised to a power.EXP(exponent)
ROUNDRounds a number to a certain number of decimal places, always rounding up to the next valid increment.ROUNDUP(value, [places])
See also  How to Share Files and Folders in Google Drive with Google Sheets

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.

IFReturns one value if a logical expression is `TRUE` and another if it is `FALSE`IF(logical_expression, value_if_true, value_if_false)
IFSEvaluates multiple conditions and returns a value that corresponds to the first true condition.IFS(condition1, value1, condition2, value2, …)
IFERRORReturns 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])
SWITCHTests 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])
ANDReturns 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, …])
ORReturns 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, …])
NOTReturns the opposite of a logical value – `NOT(TRUE)` returns `FALSE`; `NOT(FALSE)` returns `TRUE`NOT(logical_expression)
TRUEReturns the logical value `TRUE`.TRUE()
FALSEReturns 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!

DATEConverts a provided year, month, and day into a dateDATE(year, month, day)
DATEDIFCalculates the number of days, months, or years between two dates.DATEDIF(start_date, end_date, unit)
DATEVALUEConverts a provided date string in a known format to a date value.DATEVALUE(date_string)
DAYReturns the day of the month that a specific date falls on, in numeric format.DAY(date)
DAYS360Returns the difference between two days based on the 360 day year used in some financial interest calculations.DAYS360(start_date, end_date, [method])
EDATEReturns a date a specified number of months before or after another date.EDATE(start_date, months)
EOMONTHReturns 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)
HOURReturns the hour component of a specific time, in numeric format.HOUR(time)
ISOWEEKNUMReturns the number of the ISO week of the year where the provided date falls.ISOWEEKNUM(date)
MINUTEReturns the minute component of a specific time, in numeric format.MINUTE(time)
MONTHReturns the month of the year a specific date falls in, in numeric format.MONTH(date)
NETWORKDAYSReturns the number of net working days between two provided days.NETWORKDAYS(start_date, end_date, [holidays])
NOWReturns the current date and time as a date value.NOW()
SECONDReturns the second component of a specific time, in numeric format.SECOND(time)
TIMEConverts a provided hour, minute, and second into a time.TIME(hour, minute, second)
TIMEVALUEReturns the fraction of a 24-hour day the time represents.TIMEVALUE(time_string)
TODAYReturns the current date as a date value.TODAY()
WEEKDAYReturns a number representing the day of the week of the date provided.WEEKDAY(date, [type])
WEEKNUMReturns a number representing the week of the year where the provided date falls.WEEKNUM(date, [type])
WORKDAYCalculates the end date after a specified number of working days.WORKDAY(start_date, num_days, [holidays])
YEARReturns the year specified by a given dateYEAR(date)
See also  How to Forecast Trends and Patterns Using Date Functions in Google Sheets

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.

CONCATENATEAppends strings to one another.CONCATENATE(string1, [string2, …])
CLEANReturns the text with the non-printable ASCII characters removed.CLEAN(text)
EXACTTests whether two strings are identical.EXACT(string1, string2)
FINDReturns the position at which a string is first found within text.FIND(search_for, text_to_search, [starting_at])
FIXEDFormats a number with a fixed number of decimal places.FIXED(number, [number_of_places], [suppress_separator])
JOINConcatenates the elements of one or more one-dimensional arrays using a specified delimiter.JOIN(delimiter, value_or_array1, [value_or_array2, …])
LOWERConverts a specified string to lowercase.LOWER(text)
UPPERConverts a specified string to uppercase.UPPER(text)
HYPERLINKCreates a hyperlink inside a cell.HYPERLINK(url, [link_label]
VALUEConverts a string in any of the date, time or number formats that Google Sheets understands into a number.VALUE(text)
TEXTConverts a number into text according to a specified format.TEXT(number, format)

Downloadable PDF 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 free add-on for Google Docs and Google Sheets that even more tightly 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:

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.

🔥 Sizzling Summer Savings! 🔥
This is default text for notification bar