IT Pro is supported by its audience. When you purchase through links on our site, we may earn an affiliate commission. Learn more

Google Sheets functions list: Everything you need to know

Google Sheets is more than a simple spreadsheet package

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

NameExampleDescription
TO_PERCENTTO_PERCENT(value)Converts a provided number to a percentage. 
TO_DOLLARSTO_DOLLARS(value)Converts a provided number to a dollar value. 
TO_DATETO_DATE(value)Converts a provided number to a date. 
CONVERTCONVERT(value, start_unit, end_unit)Converts a numeric value to a different unit of measure.

Statistical functions

NameExampleDescription
PERCENTILEPERCENTILE(data, percentile)Returns the value at a given percentile of a dataset.
VARVAR(value1, value2)Calculates the variance based on a sample.
FORECASTFORECAST(x, data_y, data_x)Calculates the expected y-value for a specified x based on a linear regression of a dataset.
AVERAGEAVERAGE(value1, value2)Returns the numerical average value in a dataset, ignoring text.

Google functions

NameExampleDescription
IMAGEIMAGE(url, mode)Inserts an image into a cell.
IMPORTDATAIMPORTDATA(url)Imports data at a given url in .csv (comma-separated value) or .tsv (tab-separated value) format. 
GOOGLEFINANCEGOOGLEFINANCE(ticker, attribute, start_date, end_date|num_days, interval)Fetches current or historical securities information from Google Finance.
QUERYQUERY(data, query, headers)Runs a Google Visualization API Query Language query across data.

Database functions

NameExampleDescription
DSUMDSUM(database, field, criteria)Returns the sum of values selected from a database table-like array or range using a SQL-like query.
DVARDVAR(database, field, criteria)Returns the variance of a population sample selected from a database table-like array or range using a SQL-like query.
DAVERAGEDAVERAGE(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.
DCOUNTDCOUNT(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.

Featured Resources

Four strategies for building a hybrid workplace that works

All indications are that the future of work is hybrid, if it's not here already

Free webinar

The digital marketer’s guide to contextual insights and trends

How to use contextual intelligence to uncover new insights and inform strategies

Free Download

Ransomware and Microsoft 365 for business

What you need to know about reducing ransomware risk

Free Download

Building a modern strategy for analytics and machine learning success

Turning into business value

Free Download

Recommended

What is Amazon S3?
Amazon S3

What is Amazon S3?

16 May 2022
EDB unveils world-first openly governed Kubernetes Postgres operator
Cloud

EDB unveils world-first openly governed Kubernetes Postgres operator

13 May 2022
How the cloud primed Markerstudy for an M&A spree
Cloud

How the cloud primed Markerstudy for an M&A spree

9 May 2022
Gaia-X: The last chance saloon for Europe’s visionary cloud project
Cloud

Gaia-X: The last chance saloon for Europe’s visionary cloud project

4 May 2022

Most Popular

Russian hackers declare war on 10 countries after failed Eurovision DDoS attack
hacking

Russian hackers declare war on 10 countries after failed Eurovision DDoS attack

16 May 2022
16 ways to speed up your laptop
Laptops

16 ways to speed up your laptop

13 May 2022
Windows Server admins say latest Patch Tuesday broke authentication policies
Server & storage

Windows Server admins say latest Patch Tuesday broke authentication policies

12 May 2022