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!

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.

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])
See also  How To Add or Attach Files in Bulk to Google Sheets Cells

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)
See also  How to Use Google Sheets and Docs Add-ons on An iPad or Android Tablet

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 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:

  • 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.
  • PDF to text directly from Docs and Sheets using OCR technology
  • Image to text without leaving the app.
  • Premium upgrade to do even more with these features.
  • + 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.

Get 3 Months Free with the Yearly Plan
This is default text for notification bar