The Ultimate Google Sheets Formulas Cheat Sheet

Google spreadsheets are useful for organizing leads, sharing data, and calculating numbers. Plus, you can use them with PhantomBuster. Here’s how to get the most out of them.

What are Google Sheets formulas?

A Google Sheets formula is used to do mathematical equations in seconds. Instead of manually processing data, you can do it all in a few clicks.

Plus, there are lots of handy shortcuts, templates, and hacks to help you organize your Google spreadsheet and make it look ultra-professional.

If you’re new to Google Sheets, take a look at this diagram for an overview of the menu bar, add-ons, heading, new sheet button, share button, the range of cells, and more.

Annotated Google sheets overview

Now let’s dive into the Google Sheets tricks and tutorials (36 of them, to be precise)...

Shortcut to create a new Google Sheet

Open your search bar and type in: sheets.new.

A Google Sheet will magically appear in your browser! Easy peasy.

Typing sheets.new in your browser

Remove duplicate results from your Google Sheet

To have the best possible dataset, you want to make sure there aren’t any confusing duplicates hiding in the depths of your spreadsheet.

Fortunately, instead of manually going through your cell references to find any duplicates, you can do it on autopilot in exactly 3 clicks.

1. Open the “Data” tab in the menu bar.

2. Select the “Data cleanup” header in the dropdown list.

3. Click on “Remove duplicates”.

And you’re all done!

Eliminating duplicate results

View images in your Google spreadsheet setup

Some Phantoms will deliver images (such as logos) in their results, and it can be useful to visualize these directly in your spreadsheet.

If your Google Sheets file is linking to an image, go to the formula bar and type in: =IMAGE (“URL”).

Your image will then magically appear in the Google Drive spreadsheet, just like that.

Viewing images in a Google Sheet

Match your results with other data in the spreadsheet

What if you want to search for similar results that are already in your Google spreadsheet? You can use the MATCH function.

The MATCH function searches for a specific item in a certain number of cells and then shares its position in the selected cell range.

Here’s how to use it:

1. Type: =MATCH or alternatively, go to “Insert”, “Function”, “Lookup”, and “MATCH”.

2. Share a search_key manually or with a cell reference.

3. Choose your range of cells, and define how the search should be run if needed.

Setting up the MATCH formula

Convert Sales Navigator profile URLs to LinkedIn ones

If you have a list of Sales Navigator profile URLs, and you need to display them as LinkedIn ones, then we’ve got you.

An easy option is to put your list of profiles straight into the Sales Navigator URL Converter Phantom.

Or, if you want to do this from within your Google Sheet, then try this formula...

=LEFT(SUBSTITUTE(A2,"https://www.linkedin.com/sales/lead/","https://www.linkedin.com/in/"),SEARCH(",",SUBSTITUTE(A2,"https://www.linkedin.com/sales/lead/","https://www.linkedin.com/in/"))-1)

The “COUNTIF” formula

What if you want to count how many times something appears in your spreadsheet? The COUNTIF formula is here to help.

1. Open your Google Sheet and choose an empty cell to write your formula.

2. Type: =COUNTIF(“

3. In the parenthesis, list the range of cells you want to analyze, and then close the quotation marks. For example, you might write =COUNTIF(“A1:A10”

4. Type a comma to separate this range from the criteria you want to count. After the comma, write this criterion inside quotation marks, such as “$25” or “New York”, then close the parenthesis.

5. Your finished formula should look like this: =COUNTIF(“A1:A10”, “Apples”)

For example, in this Google Sheet, we used the COUNTIF formula to identify how many of our Twitter posts had 1 retweet.

COUNTIF formula setup

The “ARRAYFORMULA” setup

This formula lets you perform calculations or apply formulas to lots of cells at once. This is especially useful when you want to do the same thing to entire rows and columns of data.

1. In an empty cell, write: =ARRAYFORMULA(“

2. Type the formula you want to apply to a range of cells in quotation marks. For example, if we wanted to double the numbers in a column, we could type: =ARRAYFORMULA(“2*A1:A5”)

3. Close the parenthesis and hit Enter on your keyboard. The cells in that range will instantly follow that formula.

For example, here’s what happened when we used the ARRAYFORMULA to double the retweet count in three of the cells.

ARRAYFORMULA setup

The “SUMIF” formula

The SUMIF formula lets you add up numbers, based on a condition.

1. In an empty cell, type: =SUMIF(“

2. Select the range of cells with the numbers you want to check. For example, you might write: =SUMIF(“A1:A10”

3. Type a comma to separate the range from the condition.

4. Write the condition you need the numbers to meet. For example, if they need to be greater than 80, you’ll write: “>80”.

5. Type a comma again to separate the condition from the range of cells that you want to add up. It can be the same range as before, so you'll write "A1:A10" again.

6. Close the parenthesis and hit Enter on your keyboard.

7. Magic! You'll see the total sum of all the numbers higher than 80 in the range.

The SUMIF formula helps you add up only the numbers that meet a specific condition. It's like a special filter that collects only the things you want and adds them together.

sumif formula

Conditional formatting

Conditional formatting is a powerful feature that allows you to change the appearance of cells based on certain conditions or rules. It's like giving your spreadsheet special instructions on how to highlight or format cells automatically.

1. Select the range of cells you want to format.

2. Then, you'll go to the "Format" menu at the top of the spreadsheet and choose "Conditional formatting."

3. A panel will appear on the right side of the screen with various formatting options.

4. Choose the type of rule you want to apply, such as "Greater than," "Less than," or "Equal to."

5. Specify the value or condition that triggers the formatting.

6. Choose the formatting style, such as the text color, background color, or font style.

7. Once you've set the rule and formatting, you can see the changes in real-time as the cells automatically update based on the conditions you defined.

Conditional formatting

For more advanced formulas, take a look at VLOOKUP (to search for information in your database), IFERROR (to handle mistakes), and DELIMITER SPLIT formulas (to show your spreadsheet how to split sentences).

27 keyboard shortcuts for Google Sheets

No more misplaced right-clicks or slow searches through the menu bar! With these keyboard shortcuts, you can build the ultimate Google Sheet in record time.

Open a blank Google sheet and take these handy shortcuts for a test drive.

• Open: Ctrl + O

• Print: Ctrl + P

• Select column: Ctrl + Space

• Select row: Shift + Space

• Select all: Ctrl + A

• Insert rows or columns: Ctrl + Alt + =

• Delete rows or columns: Ctrl + Alt + -

• Insert new sheet: Shift + F11

• Cut: Ctrl + X

• Copy: Ctrl + C

• Paste: Ctrl + V

• Undo: Ctrl + Z

• Redo: Ctrl + Y

• Find and replace: Ctrl + H

• Insert or edit link: Ctrl + K

• Bold: Ctrl + B

• Italics: Ctrl + I

• Underline: Ctrl + U

• Align left: Ctrl + Shift + L

• Align center: Ctrl + Shift + E

• Align right: Ctrl + Shift + R

• Apply cell border: Alt + Shift + 7

• Format as decimal value: Ctrl + Shift + 1

• Format as time value: Ctrl + Shift + 2

• Format as date value: Ctrl + Shift + 3

• Format as currency value: Ctrl + Shift + 4

• Clear formatting: Ctrl + /

...And breathe! With these keyboard shortcuts under your sleeve, you’ll be a Google Sheets magician in no time.

Conclusion

Once you've mastered the art of Google Sheets, this is just the beginning.

These handy spreadsheets can be plugged into the different Phantoms, or you can export your Phantom results into them with a few clicks.

Visit the Phantom Store to see the full range of things you can do!