Google Sheets supports powerful cell formulas typically found in most desktop spreadsheet applications. Functions can be used to create formulas that manipulate data and calculate strings and numbers.

In this article, we will look at the top 15 Google Sheet Formulas to help you get more work done in less time with spreadsheets.

Google Sheet Formulas 101

Although it functions and looks just like other spreadsheet tools, Google Sheets is an online application that offers more features than it’s alternatives:

• It is a web-based spreadsheet you can use wherever you are.
• It can be used on any device with the mobile apps for iOS or Android and its core web app.
• Google Sheets is available for free and comes bundled with Google Drive Docs and Slides. This allows you to share files, documents and presentations online.
• You can use almost all the same functions in Google Sheets, provided you are familiar with Excel.
• Download Google Doc add-ons for even more superpowers

Common Spreadsheet Terms You Need to Know

Let’s start by covering some terminology for spreadsheets to help you understand what terms we’ll use:

• Cell: One data point in a spreadsheet.
• Column: A vertical group of cells.
• Row: A horizontal group of cells.
• Range: A range of cells that extend across a row, column or both.
• Function: This is a built-in operation in the spreadsheet app that can be used for cell, row, column or range values calculations, manipulation of data and many other things.
• Formula: A combination of functions, cells and rows, columns to produce a particular result.
• Worksheet: These are the named rows and columns that make up your spreadsheet. You can have multiple sheets in one spreadsheet.
• Spreadsheet: This is the entire document that contains your worksheets

The 15 Best Google Sheet Formulas

1. F4 key

The F4 key is a very useful shortcut to learn in Google Sheets. It allows you to toggle between relative and absolute references in ranges, which can save you time when typing out formulas.

2. F2 key to Enter into the Formula

If you need to copy part of a Google Sheets formula to use elsewhere, press the F2 key to enter into the formula. Then, select the cell containing the formula you want to copy and press Ctrl+C (Windows) or Command+C (Mac) to copy it.

3. Shift + Enter to Edit Cell

To edit a cell, you can use the shortcut Shift + Enter.

4. Escape to Exit a Formula

If you’ve 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, just hit the usual Return key).

5. Move to the Front or End of your Formula

If you want to quickly move to the beginning or end of a long Google Sheets formula, use the arrow keys. Pressing Up takes you to the front of the formula, while Down moves you to the last character.

6. Function Helper Pane

If the pane is getting in the way, you can press the “X” button to get rid of it. You can also minimize/maximize the pane using the arrow at the top right corner.

The formula pane’s best feature is the yellow highlight that it uses to indicate which section of your function is active.

The function also provides information on the data it expects and a link to all the Google documentation.

If the function pane is hidden or not visible, click the blue question mark near the equals sign. You can click that to restore the function helper panel.

7. Colored Ranges in Google Sheets Formulas

Google Sheets uses different colors to help you identify the ranges in your formulas and on your actual Sheet. This can be helpful when troubleshooting problems with your formulas.

8. F2 Key

If you position your cursor over a range of data in your formula and then press the F2 key, it will help you to see and understand that range of data more clearly.

9. Function Name Drop-Down

It is easy to find new functions by simply typing a single letter after the equals sign and then browsing what appears.

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

10. Tab to Auto-Complete Function Name

To auto-complete the function name, press the tab key.

11. Adjust the Formula Bar Width

To adjust the width of the formula bar, simply click and drag on the base of the bar until you see a double-ended arrow cursor. Then, drag it down to make it as wide as you want.

12. Quick Aggregation Toolbar

The quick aggregation toolbar in the bottom right corner of your Sheet provides a convenient way to find the aggregate measures COUNT, COUNT NUMBERS, SUM, AVERAGE, MIN, and MAX for a range of data.

13. Quick Fill Down

To quickly fill in a column, highlight the range you want to fill, then press Ctrl + D (PC and Chromebook) or Cmd + D (Mac). This will copy the contents and format down the whole range.

14. Know How to Create an Array Formula

Google Sheets’ powerful extension to regular formulas is the Array Formulas. They allow you to work with a range of data instead of individual pieces.

According to the official definition array formulas allow the display of values in multiple rows or columns. They also permit the use of non-array function with arrays.

A normal formula outputs one value; array formulas output multiple cells!

Google Sheets must be notified that we would like a formula to become an Array Formula. This can be done in two ways:

1. Hit Ctrl+ Shift + Enter (PC/Chromebook), or Cmd+ Shift+ Enter (on a Mac), and Google Sheets will add ArrayFormula wrapper
2. You can also type the word ArrayFormula into your browser and add brackets for wrapping your formula

15. Array Literals with Curly Brackets

Are you familiar with the use of curly brackets or ARRAY LITERALS for correct nomenclature in formulas?

An array is a table containing data. You can use them in the same manner as a range of columns and rows in your formulas.

These are constructed with curly brackets: { }.

The data is separated by commas into columns on the same row.

Semi-colons can create a new row within your array.

(Please note that if you are based in Europe, your syntax may be slightly different. ).

Enter this formula into cell A1 to create a 2×2 array that contains data from the range A1 through B2.

= {1 , 2 ; 3 , 4}

You can use the array component (in this case {1, 2; 3 and 4} ) as input to other formulas.

Bonus Tips: Downloading your Data

If you need to send your files to external collaborators, or just like having backups for posterity, then turn toward one of Google Sheets’ many data export options. The most common exports will be either an Excel document (.xls) or a comma-separated values file (.csv). If you’re not sure which format to use, a .csv is usually the best bet.

Bonus Tips: Use your Google Sheet in Offline Mode

If you’re looking for a way to keep working on your spreadsheet even when you’re not connected to the internet, Google Sheets has an Offline Mode that will automatically sync your changes to the document when you reconnect.