The most useful Excel functions for data analysis

If you’re familiar with Excel (and you probably are), you’ve most likely experienced the pain of writing a complex formula only for it to return the dreaded #REF!

You’ve also probably broken a link in your spreadsheet, resulting in multiple workbooks returning errors (hello undo button, my old friend).

It can be rough trying to match the right formula with the right type of data analysis. The good news is that the most useful Excel functions aren’t even the hardest. If you add the right functions to your spreadsheet skillset, analysing data becomes a breeze.

Take a look at these super useful functions (sorted by category) and level up from data zero to data hero.

Aggregating functions

Starting with the basics. The workhorses of the Excel world. 

These functions allow you to perform calculations on a set of values and return a single value. It’s basically maths.

• AVERAGE()

The AVERAGE function calculates the average of numbers entered into its formula. To calculate the average, Excel sums all numeric values and divides by the count of numeric values. Like we said, it’s basically maths.

The usual way to use this function is to use a range, like we’ve shown below. You can see that the formula used is: =AVERAGE(B2:D2)

• SUM()

The SUM function is used to add values together. This applies to numbers and cell ranges in any combination.

This function can save you so much time when working with large sets of data containing numbers that need adding up. Instead of dusting off the calculator or straining your brain with mental maths. Just use SUM.

The example below shows the function in action. The formula used is: =SUM(B2:D2)

• MEDIAN()

This function is used to calculate the middle number in a group of numbers. The most basic example would be, =MEDIAN(1,2,3,4,5) which would return 3.

The example below shows how the function would work in a dataset of random numbers. The formula used is: =MEDIAN(B2:D2)

Without this function, finding the median of a large group of numbers would require you to manually sort the data into ascending or descending order to find the middle number. In Excel, the MEDIAN function makes this process quick and easy.

• COUNT()

The COUNT function allows you to count the cells that contain numbers. The example below uses the formula: =COUNT(B2:D2) and shows that this function accounts for percentages, decimals, and even fractions.

There are variations of this function that allow you to count numbers and text, and empty cells. The possibilities are endless with this function. When analysing your data, you’ll be able to generate counts of various metrics without the need for any manual calculations.

Conditional functions

This blog is pretty simple so far. True or false? (Spoiler: It’s true)

Time to step it up a notch.

This next function allows you to make logical comparisons between a value and what you expect.

• IF()

This is one of the most important functions in Excel. An IF statement can have two results. One if your comparison is True, and another if False. The formula follows this simple syntax: =IF (logical_test, [value_if_true], [value_if_false]).

For example, in the table below, the formula says that if the score in column B is greater than (>) 80, it means that the result is a pass. If not, it’s a fail. The formula used is: =IF(B2>80,"pass","fail")

You can also use this function in conjunction with other functions such as AND and OR. 

Text manipulation functions

Have you ever imported/downloaded some data and found that it’s all over the place? 

First names and last names joined together as one long name (johnsmith), and addresses are in ALL CAPS MAKING YOU FEEL LIKE YOUR DATA IS SHOUTING AT YOU.

Well, these text manipulation functions will allow you to clean up your data and make sure the formatting looks as good as you need it to be for presenting to stakeholders.

If you had ‘john smith’ in cell A1 and you didn’t like the look of it, you could use these 3 easy functions to change it.

• UPPER()

To change the name to uppercase (JOHN SMITH), use the formula: =UPPER(A1)

• LOWER()

To change it back to lowercase (john smith), use the formula: =LOWER(A1)

• PROPER()

To change it to title case (John Smith), use the formula: =PROPER(A1)

• CONCATENATE()

Use this function to join two or more text strings into one string.

Let’s say you have ‘John’ in cell A1 and ‘Smith’ in cell A2, you can join them together (with a space in the middle) using concatenation. The formula would look like this: =CONCATENATE(A1," ",A2)

If you want to make the process even easier, you can use the concatenation character (‘&’) instead. 

1. Select the cell where you want to put the combined data.

2. Type = and select the first cell you want to combine.

3. Type & and use quotation marks with a space enclosed.

4. Select the next cell you want to combine and press enter.

The same formula would now look like this:  =A1&" "&A2

For a more visual explanation, take a look at this short video demonstrating how concatenation is used.

Date and time manipulation functions

Did it hurt? When you had to manually add the dates and times to your Excel spreadsheet? Well those painful days are over.

• NOW()

The NOW function returns the current date and time of when you entered the formula. Simply select your desired cell and enter the formula: =NOW() 

The value returned is usually a serial number that represents an Excel date and time. But you can change its appearance in the number format settings.

• DATEVALUE()

This function converts a date that is stored as text to a serial number that Excel recognizes as a date.

The formula for this is: =DATEVALUE(date_text) 

For example, =DATEVALUE("3/10/1975")  will return 27463

It’s especially useful when you’re analysing a workbook that has dates in a text format that you want to filter or sort as dates.

• WEEKDAY()

The WEEKDAY function turns any date (in serial number form) into a number, ranging from 1 (Sunday) to 7 (Saturday) by default. The integer representation can be changed by using the Return_type (table below) in your formula.

The formula for the WEEKDAY function is: =WEEKDAY (serial_number, [return_type])

This function is useful when planning and scheduling work for business projects. If you wanted to show how long it would take to complete a project, you could use the WEEKDAY function to remove any weekends in a given time frame.

Check out this video to see the WEEKDAY function in action.

VLOOKUP function

The piece de resistance of Excel functions. 

As an Excel user, you’ll probably need to “marry” data together at some point. For example, marketing might know how much each product costs, but the sales department can only provide the number of calls booked. This is the perfect use case for VLOOKUP.

VLOOKUP is like a directory for your workbook. It allows you to look up data in a table that’s organised vertically (that’s what the ‘v’ stands for).

This means you can find data automatically instead of having to spend a lot of time manually looking through rows and rows of data for one thing.

In the example above, the VLOOKUP function looks for the ID (104) in column A of the range $E$4:$G$7 and returns the value in the same row from the third column (third argument is set to 3). The fourth argument is set to FALSE to return an exact match or a #N/A error if not found.

If you’re more of a visual learner, this video shows and tells you how VLOOKUP works in practice. 

If you’ve made it this far, you’ve probably learned a few new tips and tricks 

Our Data trainers are all experts in Excel and more, and can help you become data-savvy and comfortable with complex data sets.

Check out our Data Analytics Immersive Course and level up your data skills!