Unbeknownst to many people, Google Sheets has some powerful functions under the hood, you just need to know where to look.
The online spreadsheet has many functions that people can use to perform some complex equations. The following tutorial will show you how to use Google functions.
Parser functions
| Name | Example | Description |
| TO_PERCENT | TO_PERCENT(value) | Converts a provided number to a percentage. |
| TO_DOLLARS | TO_DOLLARS(value) | Converts a provided number to a dollar value. |
| TO_DATE | TO_DATE(value) | Converts a provided number to a date. |
| CONVERT | CONVERT(value, start_unit, end_unit) | Converts a numeric value to a different unit of measure. |
Statistical functions
| Name | Example | Description |
| PERCENTILE | PERCENTILE(data, percentile) | Returns the value at a given percentile of a dataset. |
| VAR | VAR(value1, value2) | Calculates the variance based on a sample. |
| FORECAST | FORECAST(x, data_y, data_x) | Calculates the expected y-value for a specified x based on a linear regression of a dataset. |
| AVERAGE | AVERAGE(value1, value2) | Returns the numerical average value in a dataset, ignoring text. |
Google functions
| Name | Example | Description |
| IMAGE | IMAGE(url, mode) | Inserts an image into a cell. |
| IMPORTDATA | IMPORTDATA(url) | Imports data at a given url in .csv (comma-separated value) or .tsv (tab-separated value) format. |
| GOOGLEFINANCE | GOOGLEFINANCE(ticker, attribute, start_date, end_date|num_days, interval) | Fetches current or historical securities information from Google Finance. |
| QUERY | QUERY(data, query, headers) | Runs a Google Visualization API Query Language query across data. |
Database functions
| Name | Example | Description |
| DSUM | DSUM(database, field, criteria) | Returns the sum of values selected from a database table-like array or range using a SQL-like query. |
| DVAR | DVAR(database, field, criteria) | Returns the variance of a population sample selected from a database table-like array or range using a SQL-like query. |
| DAVERAGE | DAVERAGE(database, field, criteria) | Returns the average of a set of values selected from a database table-like array or range using a SQL-like query. |
| DCOUNT | DCOUNT(database, field, criteria) | Counts numeric values selected from a database table-like array or range using a SQL-like query. |
Creating a function
To create a function, choose the cell where you want the answer to appear and then type in the equal sign (=). Follow this with the preferred function name.
This cell will be the target cell for the formula or function. As well as typing the name of the function into the cell directly, you can also choose the formula from the Insert, Function menu available.
From this menu, there are four functions available; SUM, AVERAGE, COUNT, MAX, and MIN.
SUM
The SUM function lets you quickly add, subtract, multiply or divide numbers. To use this, click on the cell you want to use, select SUM from the Insert Function menu item, and then either type in the cells you want to operate on or select them by clicking on the other cells.
In the image below we want to multiply the item price by item sales to find the total price. E.G. for Apples, the item price is 1.2 and the item sales are 10, so we type in =SUM(C2*D2) to give us our total.
When typing directly into the formula bar, we can see a number of other options such as SUMIF, SUMSQ, SUMXMY2, etc. Sheets provides a brief description of how each function works and what parameters are needed for these functions.
With our example spreadsheet, we can then show the sum of out total sales by clicking on a cell underneath the last Total Price cell and then clicking on Insert, Function, SUM, and then clicking on the top cell with a number and dragging the cursor down to the last cell with a value and the hitting the return or enter key. In this example the cell formula is =SUM(E2:E8), which means the sum of all cells from E2 down to E8.
AVERAGE
The AVERAGE function can be used to calculate an average value of a range of cells. To calculate the average in our example spreadsheet, click on a free cell and type in =AVERAGE(E2:E8).
MAX
The MAX function enables users to find the largest value in a range of cells. In our example spreadsheet, type into the formula bar above the spreadsheet =MAX(E2:E8).
MIN
This function in Google Sheets allows you to find the smallest value in a range of cells. In our example spreadsheet, type into the formula bar above the spreadsheet =MIN(E2:E8).
More functions
While these functions listed above will be great for most work you do with Google Sheets, there are a host of other function available for users. By clicking on Insert, Functions, you can scroll down to an option called “More Functions”. This then lists all available functions that Google Sheets can perform. A link can be found here.
Putting the fun in functions
If you are a power user of Microsoft Excel, you will find many functions that work there will also work just as well in Google Sheets. For example, typing in =now() will show the current date and time.
You can also use functions to add a bit of colour to spreadsheets with the use of conditional formatting. In our example spreadsheet, select a range of values, right click on these and choose “Conditional Formatting”.
You can then do such things as colour a cell red if the value is below a certain number or green if above a certain value. There are plenty of other conditions to choose from.
