Google Sheets Formulas Cheat Sheet
Spreadsheets are incredibly useful tools that can help you organize data of all sorts. They can be used for work or at home, and with the right formulas, they can make life so much easier. For a long time, the most popular spreadsheet available was Microsoft Excel, but with the introduction of Google Sheets, it’s possible to create spreadsheets for free.
To ensure you’re getting the most out of your Google Sheets, you need to add formulas to the cells. The right formula can perform operations and tasks automatically, which further enhances your data and makes your life that much better.
You might be surprised to learn how many Google Sheets functions there are. There are, of course, the basics, such as adding and subtracting numbers in cells, but it goes beyond that. Trying to memorize the formulas you might need seems unnecessary, especially when you can use a Google Sheets formulas cheat sheet.
In an effort to make your life simple and straightforward, we’ve put together a Google Sheets cheat sheet that you can use.
Google Sheets Formulas Cheat Sheet
As mentioned, there are many, many different formulas you can add to your Google Sheets. However, the chances of you using them all are fairly slim. It’s not possible within the scope of this article to list all of the formulas you might use, but we’re going to focus on some of the most useful Google Sheets formulas.
To easily access this information, you might consider making a Google Sheets formulas cheat sheet PDF. You can either save the file on your computer or print a hard copy to have on your desk.
If you happen to have one formula that needs to be applied to multiple rows or columns, instead of typing it into one cell and then copying and pasting, it’s in your best interest to use ARRAYFORMULA. Add this to your Google Sheets formulas PDF so you always remember it.
Should something happen to go wrong in your spreadsheet, instead of having to go through each cell to figure out what happened, you only have to check and fix one formula.
The SPLIT function allows you to break up strings of text from one cell into separate cells. How you use this will depend on what type of spreadsheet you’re putting together, but it might be useful to have on your Google Sheets formulas cheat sheet. Maybe you’ve collected a bunch of names and both the first and last names appear in one cell, but you want to split them into separate cells.
Perhaps you have email addresses that you want to break into sections with the person’s username split from the email domain.
Only you will know why you want to use this in your spreadsheet, but once you have the formula inserted into your cell, it will reduce the amount of time and energy you expend splitting this information by hand.
Perhaps you find that you don’t want to split up your information but need to join it together. Looking at your Google Sheets formulas cheat sheet, you’ll find that you can do that with JOIN.
This function works with strings of text and takes them from two or more separate cells and joins them together in one. This could be useful if names are listed in separate cells, or maybe you want to combine names with companies where the person works. Your spreadsheet, your decision!
When looking for data within your Google Sheet that meets specific criteria, instead of wasting time going through each cell, you can add a function from your Google Sheets formulas cheat sheet to help you with this task.
This function can help you narrow down huge chunks of data to get the specific information you’re looking for, and it can do it quickly and easily.
The INDEX function is similar to FILTER, but it pulls a specific value from a range of cells rather than looking for certain data. You can think of this as being like the index that is found at the end of a book. Adding the INDEX formula can help you quickly locate specific data within your spreadsheet.
If you work with many different spreadsheets and need to pull information from one to another, then this is the formula you want to use and add to your Google Sheets formulas PDF.
Using this formula, as opposed to copying and pasting, will ensure that if any changes are made on the original sheet, that data will change on the new sheet as well. Your information will stay up to date without you expending energy and wasting time making updates.
Google Sheets Tricks
In addition to using a Google Sheets formulas cheat sheet, there are some other tricks that can be applied to Google Sheets that can make using this software unexpected and life-changing. Here are a few we think you might need:
1. Send an Email While Making a Comment
One of the reasons people like to use Google Sheets is because it can easily be shared with others. That means if you’re working on a project with a team and need their input, you can send them the spreadsheet and get feedback in real-time.
Another amazing feature that you may not have known about is that you can send a specific person an email to let them know you’re adding a comment to Google Sheets. This can also let them know that they need to address the question you’re asking.
For them to be notified about your intentions, while you’re making your comment, you’ll add a + and then type in the recipient’s email address. When you add the comment to the spreadsheet, they’ll automatically get an email with your message.
2. Clean and Trim
As you go through your spreadsheet, if you happen to find any unwanted characters within the cells, you can use CLEAN and TRIM functions to get rid of them. If you want, you can even add these to your Google Sheets formulas cheat sheet so that you have easy access to them.
The CLEAN function can be used to get rid of any non-printable ASCII characters in your spreadsheet, while TRIM will remove white space (i.e., extra spaces) from the beginning or end of your cells.
3. Integrate with Google Forms
Collecting data from Google Forms can be incredibly useful. What makes it even better is being able to visualize and analyze the data once it’s been collected. By integrating Google Sheets with Google Forms, you can do just that.
Integrating the information requires that you select the responses inside the Google Form, then you click the Google Sheets icon to send those answers to the spreadsheet. Easy integration means you have more time to scrutinize the data and figure out what needs to be done to make life better for everyone.
4. Insert a Chart from Google Sheet to Google Docs
Charts are a great way to make complicated data easier to read. If you find that you need to move a chart from Google Sheets into Google Docs, it’s super easy to achieve that goal.
While in Google Docs, here’s how you’ll insert the chart from Google Sheets:
- Open Google Docs
- Click on Insert in the toolbar at the top of the page
- From the drop-down menu, choose “Chart”
- When the next menu pops up, click on “From Sheets”
The chart is automatically inserted.
What’s nice about this process is that if you make changes to the data in the spreadsheet and it changes the chart, those edits will update within Google Docs.
5. Create QR Codes
QR codes can be used for a variety of different reasons, including directing people to a specific website or checking them into an event. No matter why you need a QR code, you can quickly and easily create them in Google Sheets.
When it comes to creating a QR code, you’ll need to assign the person a specific number, then pull a formula from your Google Sheets formulas cheat sheet. Specifically, if you have data in cell A2, in cell B2, you’d put the following:
This will automatically create a unique code for every person on your list.
6. Import Data from Websites
When it comes to organizing information from a website or RSS feed, instead of copying and pasting the data you need, Google Sheets has functions that can help you achieve your goals. Here are some more formulas you can add to your Google Sheets functions cheat sheet:
- ImportHTML – this option allows you to import HTML lists and tables
- ImportFeed – with this, you can import RSS entries to your sheet
- ImportData – if you have a web-based CSV file, you’ll need this formula to import it to your sheet
- ImportXML – should you find that you need to import a specific section of a webpage and it can be identified with an Xpath, you’ll use this function
7. Other Add-Ons
In addition to the items we’ve listed above, there are also a variety of extra add-ons that you can add to Google Sheets. Some of these extras that you might consider include the following:
With this add-on, you can analyze the data from your site and other sources to make decisions on the best way to run your business. You can even add visualizations to a dashboard to make it simple and easy to read the information in your spreadsheet.
AdStage is essential if you run paid campaigns, as it will help you analyze pay-per-click performance data from multiple platforms, including Google Adwords, Facebook, LinkedIn, and Twitter.
With the data added directly to Google Sheets, you can easily review it and make decisions to improve future ad campaigns.
Yet Another Mail Merge
If you’re looking for a way to optimize your email strategy, then you need to add Yet Another Mail Merge to your arsenal. This add-on organizes your contacts and then allows you to draft an email that can be sent with one click.
Once the email is out in the world, you’ll be able to track in real-time who has opened the message, who has unsubscribed, who has responded, and if any messages bounced.
Excel Functions Cheat Sheet
Just like the functions that are available in Google Sheets, there are numerous options you can use in Excel as well. Again, it seems silly to attempt to memorize them all, so having a reference page with this information is useful. You can either access it from a website or print it out and have a hard copy sitting on your desk.
Excel Functions Tips
To ensure that you’re getting the most out of your Excel functions when inputting them into a spreadsheet, consider doing the following:
- Use brackets
- Use line breaks to clarify complex formulas
- Use the Fill Handle to copy formulas
Make Your Data Work for You
Spreadsheets are useful when it comes to organizing complex data. They often come equipped with a variety of formulas that make using them easier. When it comes to a Google Sheets formulas cheat sheet, you’ll need to find the formulas that will work best for you, but some that you might consider using are the following:
The above article may contain affiliate links, which help support Clever Sequence