Connect with us

Technology

7 Excel Functions That Will Make You a Better Analyst

Published

on

7,028

Excel is the most used spreadsheet tool on the planet.

In 1996, there were 30 million users, and I wouldn’t be surprised if that number has gone up multi-folds. No other spreadsheet tool comes anywhere close to the level of functionality that Excel provides.

If you work with data, there is a really good chance you use Excel. And if you’re not using it already, you should definitely think of switching to it.

Excel has more than 450 functions that can do a lot of things. While it would be really hard to learn about all the formulas, it can be a great skill to know the important ones.

In this article, I will list the 7 Excel functions (or group of functions) that will help you become a better analyst.

So let’s get started.

VLOOKUP

VLOOKUP is one of the most popular Excel functions. And it’s understandable given its utility.

It does the same thing that you do when you go to a restaurant and scan a list of items. As soon as you get to that juicy chicken burger you want to order, you look to the right and check the prices.

VLOOKUP function allows you to go through a vertical list of items and find a matching item. Once it has found the item, you can fetch the value from a corresponding column.

I have been part of many job interview panels for analysts. Whenever the interviewer wanted to check the Excel knowledge of the candidate, they would ask about the VLOOKUP function.  So in case you’re preparing for your next interview, make sure you know this function well.

COUNTIF/SUMIF

While counting items and adding values is something you would be required to do often, it’s counting/adding data with a condition that can be a bit tricky.

For example, if you have the sales record of all the sales reps and you want to know how many sales did Laura managed in the quarter or how much was the value of her total sales, then you need to use the COUNTIF or SUMIF function.

These functions allow you to specify a condition, and only the records where the condition is met are counted/added.

And if there are multiple conditions, you can also use the COUNTIFS or SUMIFS functions.

Text Functions

There are a number of text functions in Excel – such as LEFT, RIGHT, MID, TRIM, LEN, FIND, etc.

With text functions, you can do a lot of amazing things such as:

  • Calculate the number of characters in a string
  • Extract the first name, middle name, or the last name from full name
  • Extract the username from an email id
  • Find if a string contains a specific word or not.

These are just some of the scenarios. If you work with text-based data in Excel, knowing how to use these text functions can come in very handy and help you get a lot of work done in minutes.

IF Function

Excel IF function allows you to assess two different conditions and return a value based on which condition is true.

A simple example of this is when you’re assigning commissions to sales reps. If the sale is less than $1,000, the commission is 5%, and if it’s more than $1,000, then the commission increases to 7.5%.

Using the IF formula, you can easily calculate the commissions of thousands of sales rep at one go.

You can also use nested IF functions, which means using an IF function within an IF function.

LARGE / SMALL

When working with data in Excel, there can be situations when you need to know the top three or bottom three values.

For example, if you’re a teacher and you want to quickly get the top three scores in an exam, you can use the LARGE function (and use SMALL when you want to get the bottom values).

While there is a Max/Min function is Excel as well, what Large/Small function also do is give you the second largest or the third largest value (while Max/Min will only give the maximum/minimum values).

CONCATENATE Function

With CONCATENATE function, you can easily combine data from different cells into one single cell.

Some cases when this can be useful is when you want to combine the first and the last name, or when you have the address components (house number, street name, city, country) in different cells and you want to combine it to make it a single string.

In Excel 2016, a new and improved function – TEXTJOIN – was introduces. It does everything Concatenate function does, plus a few more.

SUMPRODUCT Function

SUMPRODUCT is an advanced function and can be extremely powerful when analyzing a huge amount of data. This is one of the best function to use when you’re trying to create interactive dashboards.

SUMPRODUCT function can be used when you want to first multiply two or more sets to arrays and then get its sum.

To give you a practical example, if you go to the market to buy fruits and you buy varying quantities of different fruits with different prices, you can use the SUMPRODUCT function to get the total cost of all fruits. It will first multiply the quantity of each fruit with its price and then give you the sum of all.

Now, this is only one example of using it. SUMPRODUCT formula is a lot more versatile. You can use it to get the final value based on conditions.

For example, if you have the yearly sales data for different items, and you want to know what were the sales of ItemA in the first quarter, you can do that using the SUMPRODUCT function.

I have listed 7 formulas (or category of formulas) that are most commonly used and can make you look like an Excel Pro. There are many other useful formulas in Excel that you can explore based on your work and requirement.

Sumit Bansal is an online entrepreneur and a spreadsheet enthusiast. He has been recognized by Microsoft as the Most Valuable Professional (MVP) in MS Excel. He writes at TrumpExcel.com. He also helps people build and grow their blogs at Craft of Blogging.

Continue Reading
Advertisement

Facebook

Trending