Outlier Editor

10 spreadsheet formulas we use to superpower our data analysis

—

by

Gemma Gatticchi
in ,

Both Microsoft Excel and Google Sheets are powerful spreadsheet programmes for storing and sorting through mounds of data. Once you learn how to make even the most basic calculations using the tools, the possibilities for data stories are endless.

Here are 10 of our favourite/most-used spreadsheet formulas for making sense of data. Note: these functions can be used in both Excel and Google Sheets.

SUM

This is one of the most basic functions but it’s also one of the most popular. It can be used to add two or more numeric values.

AVERAGE

This function automatically calculates the average of a range of numbers. This is done by adding up all the numeric values and dividing that total by the number of values in your sum.

MODE

This function will return the most commonly used value in a dataset.

IF

The IF function allows users to set a condition that can end in one of two results:

  • If a certain cell meets the condition the user set, it will be true. For example: If the percentage is higher than 30% then it is a pass
  • If a certain cell does not meet the condition the user set, it is false. For example: If the percentage is lower than 30% then it is a fail

MAX/MIN

These two functions do exactly what it sounds like they do: they return the highest and lowest values in a range. The MAX function can be used to find the highest value and the MIN function can be used to find the lowest value.

COUNTIF

COUNTIF will return the number of cells that meet certain criteria.

In the example below, to find out how many of the employees come from South Africa:

  • Type in your function.
  • Select the range of countries (B2:B12)
  • Type in what you want Google Sheets to look for. In this instance, it’s ‘South Africa’.

The answer is 5 employees are South African.

SUMIF

This function adds values together based on a certain criterion. In the example, we want to add up the total salary amount of the South African employees only.

SPLIT

The SPLIT function allows a user to break up data by separating it wherever a specific ‘separator’ occurs. Separators can be periods, commas, semicolons or spaces. In the example below, commas are used to divide the data, so that is what is indicated in the formula ” , “

COUNT/COUNTA

COUNT and COUNTA do precisely what it sounds like they do. These functions count the number of cells with either text (COUNTA) or numbers and dates (COUNT). This means both functions exclude any blank cells.

These functions are helpful when you know how many rows of text or numbers you should have in a document filled with random blank cells. You can use these two functions to check yourself.

TRIM

Sometimes it’s difficult to tell if there are multiple spaces between the words in different cells. The TRIM function works by leaving only a single space between words, making your information appear much neater.

Notebook