15 Excel Data Analysis Functions You Need to Know – Excel with Business

15 Excel data analysis features you need to know

If you’ve used Excel before, you may have the hassle of choosing the wrong formula to analyze your dataset. Maybe you’ve been trying for hours because the data output is wrong or the functionality is so complex that it seems easier to calculate the data manually manually. If that sounds like you, data analysis in Excel Top 15 is for you.

Excel has hundreds of functions and trying to match the right formula with the right kind of data analysis can be overwhelming. The most useful features do not have to be complicated. In fact, there are 15 simple features that improve your data analysis skills, so you wonder how you lived without data.

Whether you use Excel or your work a lot, there are features for everyone on this list.

1. CONCATENATE

= CONCATENATE is one of the easiest to learn but one of the most powerful formulas when doing data analysis. Combine text, numbers, dates, etc. from multiple cells into one. Useful for creating API endpoints, product SKUs, and Java queries.

Formula: = CONCATENATE (Please select the cell you want to select)


In this example: = CONCATENATE (A2, B2)

2. LEN

= LEN quickly gives you the number of characters in a given cell. As in the example above, you can use the formula = LEN to identify two different kinds of product retention units (SKUs) to determine the number of characters contained in a cell. LEN is especially useful when trying to figure out the difference between different Unique Identifiers (UIDs), which are long and not in the right order.

Formula: = LEN (SELECT CELL)

In this example: = LEN (A2)

. COUNTA

= COUNTA identifies whether the cell is empty. Incomplete data sets occur daily in the life of a data analyst. COUNTA allows you to evaluate the differences that may exist in your data set without reorganizing your data.

Formula: = COUNTA (SELECT CELL)


Example: = COUNTA (A10)

4. DAYS / NETWORKDAYS

= DAYS means exactly. This feature determines the number of calendar days between two dates. This tool is useful for determining the lifecycle of products, contracts, and execution revenues based on data analysis essential service life.

= NETWORKDAYS is slightly more powerful and useful. This formula determines the number of “working days” between two dates and the option to describe the holiday. Sometimes even addicts need a break! Comparing time periods using these two formulas is especially helpful for project management.

Formula: = DAYS (SELECT CELL, SELECT CELL) OR = NETWORKDAYS (SELECT CELL, SELECT CELL, [numberofholidays]) Note: [numberofholidays] is optional

Example: = DAYS (C8, B8) OR = NETWORKDAYS (B7, C7,3) [19659003]

5. SUMIFS

= SUMIFS is one of the “to know” formulas for data analysts. The commonly used formula is = SUM, but what if you need to sum values ​​based on multiple criteria? SUMIFS is that. In the example below, SUMIFS is used to determine how much each product contributes to sales.

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

Example: = SUMIF ($ B $ 2: $ B $ 28, $ A $ 2: $ A $ 28, $ F2)

6. AVERAGEIFS

Like SUMIFS, you can use AVERAGEIFS to get an average based on one or more criteria.

Formula: = AVERAGEIF (SELECT CELL, CRITERIA, [AVERAGE_RANGE]) Note: [average_range] is optional

Example: = AVERAGEIF ($ C: $ C, $ A: $ A, $ F2)

7. VLOOKUP

VLOOKUP is one of the most useful and recognizable data analysis features. Excel users will probably need to “marry” the data at some point. For example, a receivable account can know how much each product costs, but the shipping department can only provide shipping units. This is the perfect use case for VLOOKUP.

In the image below, we use reference data (A2) combined with a price tag to find the matching criteria in the first column well and return adjacent values.

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

Example: = VLOOKUP ($ A2, $ G $ 1: $ H $ 5,2,0)

8. FIND / SEARCH

= FIND / = SEARCH is a powerful feature for separating certain text within a data set. Since = FIND is case sensitive, querying “Big” using FIND only returns Big = true results, so both are listed here. But = SEARCH for “Big” matches Big or big, so the query is a bit wider. This is especially useful for finding exceptions or unique identifiers.

Expression: = FIND (TEXT, WITHIN_TEXT, [START_NUMBER]) or = SEARCH (TEXT, WITHIN_TEXT, [START_NUMBER]) Note: [start_number] is optional and used to indicate the starting cell . Text to search for

Example: = (FIND (“Big”, A2,1) ””)

9. IFERROR

= IFERROR should be used by analysts who actively present data. Using the previous example, if a particular text / value is found in the dataset, no match is returned. This causes a #VALUE error and is harmless but confusing and unobtrusive.

Use = IFERROR to replace #IF error with text / value. In the example above, the cell is empty, so the data consumer can easily choose which row returned the matching value. Expression: = IFERROR (FIND “VALUE”, SELECT CELL, VALUE_IF_ERROR)

Example: = IFERROR (FIND “BIG”, A6,1) , ””

10. COUNTIFS

= COUNTIFS is the easiest way to count the number of instances for which a dataset meets a set of criteria. In the example above, the product name is used to determine which product is the best seller. COUNTIFS is powerful because of the infinite criteria you can enter.

Formula: = COUNTIFS (RANGE, CRITERIA)

In the example: = COUNTIFS ($ A: $ A, $ F9) [19659005] 11. LEFT / RIGHT

= LEFT, = RIGHT is a simple and efficient way to extract static data from cells. = LEFT returns the number of “x” characters at the beginning of the cell, and = right returns the number of “x” characters at the end of the cell. In the example below, = LEFT is used to extract the consumer area code from the phone number, and = RIGHT is used to extract the last 4 digits.

Formula: = LEFT (SELECT CELL, NUMBER) OR = RIGHT (SELECT CELL, NUMBER)

In this example: = left (A6, 3) AND = right (A6,4)

12. RANK

= RANK is an ancient Excel function, but it does not degrade the effect on data analysis. With = RANK, you can quickly rank the values ​​in a dataset in ascending or descending order. In this example, “RANK” is used to determine which customers order the most products.

Formula: = RANK (SELECT CELL, RANGE_TO_RANK_AGAINST, [ORDER]) Note: [order] is optional

Example: = RANK ($ B7, $ B $ 2: $ B $ 7,0) Note: 0 returns the largest value above # 1

13 MINIFS

= MINIFS is very similar to the min function, except that it can take a minimum set of values ​​and match the criteria. In this example = MINIFS is used to find the lowest price at which each product was sold.

Formula: = MINIFS (RANGE1, CRITERIA1, RANGE2)

In this example: = MINIFS ($ B $ B, $ A: $ A, $ E5)

14. MAXIFS

= MAXIFS can be matched by criteria just like other minis, but this time it finds the maximum number.
Formula: = MAXIFS (RANGE1, CRITERIA1, RANGE2)


In this example: = MAXIFS ($ B $ B, $ A: $ A, $ E5)

15. SUMPRODUCT

= SUMPRODUCT is a useful feature for calculating average revenue, price, and profit. SUMPRODUCT multiplies the values ​​in one range by the corresponding row corresponding values. Data Analysis Gold. The example below uses sumproduct to multiply Price by quantity and then divide by total sales to calculate the average selling price of all products.

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


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

I hope this helps. If you are interested in analyzing data in Excel, take a look at the Excel process to help hundreds of millions of people master Excel.

If you’re interested in more free tips, we’ve recently collected the top 100 most useful Excel tips. CLICKING HERE