21 Google Sheet Formula & Tips that’ll make your smarter in 10 min

Google Sheet is one of the most used ( almost every one I know) tool now a days to excel in the business or at work. Here are 21 Google Sheet Formula and tips that will take loads of you work OFF your shoulder.

These Google Sheet Formula magically improves your data processing work that you do in excel or google sheet.

Google Sheet Formula List

1. F4 Key

Undoubtedly one of the most useful Google Sheets formula shortcuts to learn.

Press the F4 key to toggle between relative and absolute references in ranges in your Google Sheets formulas.

21 Google Sheet Formula & Tips that'll make your smarter in 10 min 1

It’s WAY quicker than clicking and typing in the dollar ($) signs to change a reference into an absolute reference.

Back to top

2. F2 To Edit Cell

Have you ever found yourself needing to copy part of a Google Sheets formula to use elsewhere? This is a shortcut to bring up the formula in a cell.

Start by selecting a cell containing a formula.

Press the F2 key to enter into the formula:

21 Google Sheet Formula & Tips that'll make your smarter in 10 min 2

Back to top

3. Shift + Enter To Edit Cell

Shift + Enter is another shortcut to enter into the Google Sheets formula edit view.

Back to top

4. Escape To Exit A Formula

Have you ever found yourself trying to click out of your formula, but Sheets thinks you want to highlight a new cell and it messes up your formula?

Press the Escape key to exit the formula view and return to the result view.

Any changes are discarded when you press the Escape key (to save changes you just hit the usual Return key).

Back to top

5. Move To The Front Or End Of Your Google Sheets Formula

Here’s another quick trick that’s helpful for longer Google Sheets formulas:

When you’re inside the formula view, press the Up arrow to go to the front of your formula (in front of the equals sign).

Similarly, pressing the Down arrow takes you to the last character in your formula.

21 Google Sheet Formula & Tips that'll make your smarter in 10 min 3

Back to top

6. Function Helper Pane

Learn to read the function helper pane!

21 Google Sheet Formula & Tips that'll make your smarter in 10 min 4

You can press the “X” to remove the whole pane if it’s getting it the way. Or you can minimize/maximize with the arrow in the top right corner.

The best feature of the formula pane is the yellow highlighting it adds to show you which section of your function you are in. E.g. in the image above I’m looking at the “[headers]” argument.

There is information about what data the function is expecting and even a link to the full Google documentation for that function.

If you’ve hidden the function pane, or you can’t see it, look for the blue question mark next to the equals sign of your formula. Click that and it will restore the function helper pane.

Back to top

7. Colored Ranges

Helpfully Google Sheets highlights ranges in your formulas and in your actual Sheet with matching colors. It applies different colors to each unique range in your formula.

21 Google Sheet Formula & Tips that'll make your smarter in 10 min 5

Back to top

8. F2 To Highlight Specific Ranges

As mentioned in Step 2, you press the F2 key to enter the formula view of a cell with a formula in.

However, it has another useful property. If you position your cursor over a range of data in your formula and then press the F2 key, it will

F2 to highlight range in Google Sheets formula

Back to top

9. Function Name Drop-Down

A great way to discover new functions is to simply type a single letter after an equals sign, and then browse what comes up:

21 Google Sheet Formula & Tips that'll make your smarter in 10 min 6

Scroll up and down the list with the Up and Down arrows, and then click on the function you want.

Back to top

10. Tab To Auto-Complete Function Name

When you’re using the function drop-down list in the tip above, press the tab key to auto-complete the function name (based on whatever function is highlighted).

Back to top

11. Adjust The Formula Bar Width

An easy one this! Grab the base of the formula bar until you see the cursor change into a little double-ended arrow. Then click and drag down to make the formula bar as wide as you want.

21 Google Sheet Formula & Tips that'll make your smarter in 10 min 7

Back to top

12. Quick Aggregation Toolbar

Highlight a range of data in your Sheet and check out the quick aggregation tool in the bottom toolbar of your Sheet (bottom right corner).

21 Google Sheet Formula & Tips that'll make your smarter in 10 min 8

Quickly find out the aggregate measures COUNT, COUNT NUMBERS, SUM, AVERAGE, MIN and MAX, without needing to create functions.

Back to top

13. Quick Fill Down

21 Google Sheet Formula & Tips that'll make your smarter in 10 min 9

To copy the formula quickly down the column, double-click the blue mark in the corner of the highlighted cell, shown by the red arrow. This will copy the cell contents and format down as far as the contiguous range in preceding column (column A in this case).

An alternative way to quickly fill in a column is to highlight the range you want to fill, e.g.:

21 Google Sheet Formula & Tips that'll make your smarter in 10 min 10

Then press Ctrl + D (PC and Chromebook) or Cmd + D (Mac) to copy the contents and format down the whole range, like so:

21 Google Sheet Formula & Tips that'll make your smarter in 10 min 11

You can also do this with Ctrl + Enter (PC and Chromebook) or Cmd + Enter (Mac), which will fill down the column.

Back to top

14. Know How To Create An ArrayFormula

Array Formulas are powerful extensions to regular formulas, allowing you to work with ranges of data rather than individual pieces of data.

Per the official definition, array formulas enable the display of values returned into multiple rows and/or columns and the use of non-array functions with arrays.

In a nutshell: whereas a normal formula outputs a single value, array formulas output a range of cells!

We need to tell Google Sheets we want a formula to be an Array Formula. We do this in two ways:

  1. Hit Ctrl + Shift + Enter (PC/Chromebook) or Cmd + Shift + Enter (on a Mac) and Google Sheets will add the ArrayFormula wrapper
  2. Alternatively, type in the word ArrayFormula and add brackets to wrap your formula/li>

Back to top

15. Create Arrays With Curly Brackets

Have you ever used the curly brackets, or ARRAY LITERALS to use the correct nomenclature, in your formulas?

An array is a table of data. They can be used in the same way that a range of rows and columns can be used in your formulas. You construct them with curly brackets: { }

Commas separate the data into columns on the same row.

Semi-colons creates a new row in your array.

(Please note, if you’re based in Europe, the syntax is a little different. Find out more here.)

This formula, entered into cell A1, will create a 2 by 2 array that puts data in the range A1 to B2:

= { 1 , 2 ; 3 , 4 }

The array component (in this example { 1 , 2 ; 3 , 4 } ) can be used as an input to other formulas.

16. Multi-line Formulas

Press Ctrl + Enter inside the formula editor bar to add new lines to your formulas, to make them more readable. Note, you’ll probably want to widen the formula bar first, per tip 11.

21 Google Sheet Formula & Tips that'll make your smarter in 10 min 12

17. Comments In Formulas

Add comments to your formulas, using the N function.

N returns the argument provided as a number. If the argument is text, inside quotation marks, the N function returns 0.

So we can use it to add a comment like this:=SUM(A1:A100) + N("Sums the first 100 rows of column A")

which is effectively the same as:

=SUM(A1:A100) + 0

which is just:


This tip is pretty esoteric, but it’s helpful for any really long formulas!

Back to top

18. Use The Onion Approach For Complex Formulas 🙂

Complex formulas are like onions on two counts: i) they have layers that you can peel back, and ii) they often make you cry

Use The Onion Method To Approach Complex Formulas

If you’re building complex formulas, then I advocate a one-action-per-step approach. What I mean by this is build your formula in a series of steps, and only make one change with each step. So if you start with function A(range) in a cell, then copy it to a new cell before you nest it with B(A(range)), etc.

This lets you progress in a step-by-step manner and see exactly where your formula breaks down.

Similarly, if you’re trying to understand complex formulas, peel the layers back until you reach the core (which is hopefully a position you understand). Then, build it back up in steps to get back to the full formula.

Your Can Master Complete Excel in Less Than 2 Coffees $. Most Recommended Course

Leave a Comment