Top 10 Excel Functions That Everyone Should Know

Mar 22, 2021
7 min read

An estimated 750 million people use Excel worldwide. According to Satya Nadella, Excel is Microsoft's most important consumer product. If you ask techies about it, they often dismiss Excel with a sniff. However, the vast majority of users in the world of business uses Excel. Most professionals prefer information in an Excel format due to the following reasons:

You can quickly get what you need with an Excel file without waiting for the IT department to deal with your question.
Most of the users have the experience to view their data in a specific way. They know they can ask their IT vendor or department for a particular report and often end up waiting for weeks or even months to get what they want. Alternatively, they can import data into Excel and get what they want to see in just five minutes with a pivot table and merrily mix data from many sources. You can pull data into the add-in of Excel called PowerPivot to combine data from numerous sources for more adventurous users. If you are using the latest Excel version, you can clean up and reorganize your data in minutes using Power Query.

Excel is incredibly flexible.
If you need to identify expiring contracts, use an IF function, condition formatting to have a constant up to date picture.
If you need to compare two data sets, you can do it in five minutes with Excel with a VLOOKUP, applying a conditional formation and a filter to find the missing items.
If you need to quickly summarize your data across the months or years or countries or cost centers, use a pivot table and do it in five minutes.

As most organizations use Excel, it is quite a marketable skill.
When you go to any organization, they'll be using Excel due to its flexibility and ease of use. You can usually find an Excel King or Queen in smaller organizations who can answer all your Excel queries. Most users turn to Excel for solving a problem as another alternative to waiting for weeks or even months for a proposal to be considered, assessed, and then executed. Instead, it is simple and easy to plan all of it out using Excel.

Usually, excel problems occur due to the misuse.
Excel does a superb job of analyzing the data and giving you complete flexibility over it. That's what also makes it weak. If you need an accounting system, you should invest in an accounting program. If you need to capture large amounts of clean normalized data, you should invest in a system designed to do that. Now, Excel wasn't intended for that. Often Excel is used to capture data, but as many users don't understand the importance of using normalized data, the set of data in an incorrect way will immediately limit what can be done with it using Excel tools.

Excel allows you to deliver clean data.
Clean data is a prerequisite to summarize and analyze your data, identify patterns, and ask questions. Usually, large systems deliver clean data. You can also use it for a quick analysis, answer the questions you have about your business issues.

Excel users can find a considerable amount of help.
If you have an Excel problem, chances are someone else had that problem, and there will be a solution somewhere for it. Excel users love helping other people with their problems for free. If you use a large-scale complicated system, you'll find scanty online help for it. Getting a working formula in Excel has a definite adrenaline rush to it. Also, there are many free Excel templates available within Excel to help you save time.

Excel has better sharing options.
If you look at Google sheets now, it has been superior to Excel as it allows collaborative use. In the previous versions before Office 365, it only allowed one user at a time. However, the more up to date versions, the more collaboration friendly.

Here are the top 10 excel functions that everyone should know:

1. LEN

This function quickly provides the number of characters in a given cell. You can identify two different kinds of product Stock Keeping Units (SKUs) using this function to see the number of characters in a given cell. LEN is especially useful when determining the variances between different Unique Identifiers (UIDs) that are often lengthy and not in the right order.
Formula: =LEN(SELECT CELL)
In this example: =LEN(A2)

1. LEN

2. SUMIF

This function is a must-know formula, especially for a data analyst. Though the most common formula used is =SUM, SUMIF is used to add the values based on multiple criteria. In this instance, SUMIF is used to determine how much each product contributes to the top-line revenue.

Formula: =SUMIF(RANGE, CRITERIA, [sum_range])

Note: here [sum_range] is optional.

In this instance: =SUMIF(A:C,F2,B:B)

2. SUMIF

3. CONCATENATE

It is one of the easiest Excel functions to learn. It is the most powerful formula used to conduct data analysis. Using this formula, you can select the cells you want to combine the numbers, text, dates, and more from multiple cells into one. This function is used to create product Stock Keeping Units (SKUs), Application Programming Interface (API) endpoints, and Java queries.

Formula: =CONCATENATE(SELECT THE NUMBER OF CELLS TO COMBINE)

In this example: =CONCATENATE(A2,B2)

3. CONCATENATE

4. COUNTA

It is used to identify whether a cell is empty or not. It is the most useful Excel function for a data analyst who is going to run into incomplete data sets daily. This function allows you to evaluate any gaps in the dataset without having to reorganize the data.

Formula: =COUNTA(SELECT CELL)

In this instance: COUNTA(A2)

4. COUNTA

5. VLOOKUP

It is one of the most useful and recognizable data analysis functions for an Excel user. For instance, if you need to marry data together accounts receivable might know the cost for each product while the shipping department can only provide with units shipped. This is the perfect use case for the VLOOKUP function.

In the following example, we use reference data in column A combined with the pricing table to have Excel look up the matching criteria in the first column and return an adjacent value.

Formula: =VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUM,[RANGE_LOOKUP])

In this example: =VLOOKUP(A:A,G:H,2,0)

5. VLOOKUP

6. RANK

It is an ancient Excel function that allows you to quickly denote how the values rank in a dataset in either an ascending or descending order. When it comes to data analysis, the effectiveness of the =RANK function can’t be downplayed. This function is used to determine which client orders the most products.

Formula: =RANK(SELECT CELL, RANGE_TO_RANK_AGAINST,[ORDER])

Note: [ORDER] is optional.

In this instance: =RANK(B7,B2:B7,0)

Note: 0 returns the highest value ranked #1

6. RANK

7. AVERAGEIF

This Excel function allows you to take an average based on one or more criteria.

Formula: =AVERAGEIF(SELECT CELL, CRITERIA, [AVERAGE_RANGE])

Note: [AVERAGE_RANGE] is optional

In this example: =AVERAGEIF(A:A,F2,C:C)

7. AVERAGEIF

8. COUNTIFS

It is the easiest way to count the number of times a dataset meets a specific set of criteria. This Excel function is powerful due to its limitless criteria you can input. In this instance, the product name is used to determine which product was the best seller.

Formula: =COUNTIFS(RANGE,CRITERIA)

In this instance: =COUNTIFS(A:A,F8)

8. COUNTIFS

9. DAYS/NETWORKDAYS

=DAYS function is used to determine the number of calendar days between two dates. It is a useful tool to assess the lifecycle of the contracts, products, and to run the rating revenue depending on the service length, which is essential for data analysis.

=NETWORKDAYS is a more robust and useful formula to determine the number of workdays between two dates, including an option to account for holidays. After all, an employee working from home too needs a break from work to prevent burnout.

Using both these formulas, you can compare time frames that are especially useful in project management.

Formula: =DAYS(SELECT CELL, SELECT CELL)

In this example: =DAYS(C8,B8)

Formula:=NETWORKDAYS(SELECT CELL, SELECT CELL,[numberofholidays])

Note: [numberofholidays] is optional.

9.1. DAYS

In this example: =NETWORKDAYS(B8,C8,3)

9.2. NETWORKDAYS

10. SUMPRODUCT

It is an excellent function to calculate average returns, margins, and price points. It is used to multiple a range of values by its matching row counterparts. This is known as gold in data analysis. In this instance, we calculate the average selling price of all our products using SUMPRODUCT to times Price by Quantity and then divide by the total volume sold.

Formula: =SUMPRODUCT(RANGE1,RANGE2)/SELECT CELL

In this instance: =SUMPRODUCT(B2:B9,C2:C9/C10)

10. SUMPRODUCT

Wrap Up

The problems with Excel arise when people use it for tasks that were never intended for it. It should not be your only computer tool, but it is indispensable when used properly with other systems.