9 Data Analysis Excel Functions (With Detailed Examples)

By Indeed Editorial Team

Published June 19, 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Data analytics is a pervasive skill that applies to every sector and every industry, ranging from marketing to healthcare. Excel, a common spreadsheet-based software for performing analysis, comes with a variety of helpful tools and functions. Understanding how to identify the ideal analytics function and execute it quickly can help you show value in a data-driven career. In this article, we list nine data analysis Excel functions, along with their uses, and an example, so you can easily reference these useful formulas.

9 data analysis Excel functions

The following is a list of nine essential data analysis Excel functions, each with an example and detailed purpose:

1. Finding characters in a cell

=LEN is a function that returns the number of characters in any cell, without spaces. It enables the user to manage unique identification numbers, essential for tracking. Any set of numbers that isn't in a logical sequence can benefit from this function. Consider a business that knows that the red carpet (A4) has a nine-number code, and the blue rug (A3) has a 12-number code. It serves as an error flag in certain situations and an organizational tool in others.

Formula: =LEN(SELECT CELL)

Example:

LEN lets you track the blue rug and red carpet industry:

=LEN(A4) =9
=LEN(A3) = 12

2. Combining information from multiple cells

=CONCATENATE is a potent Excel tool for its versatile applications. It draws dates, numbers, text, and other input from multiple cells and combines it into a single cell unit. The function enables you to provide a reference point by which to integrate plug-ins or other software. These apply to product stock-keeping units (SKU) and application interface endpoints.

Formula: = CONCATENATE (SELECT CELLS TO COMBINE)

Example:

Consider a spreadsheet where column A represents a landing page and column B is the connecting URL. The two connecting via the concatenate function results in a usable API for monitoring. Each following number connects the cells to a different page on the site.

= CONCATENATE (A15,B15)

Read more: Top 10 Data Analyst Interview Questions (With Example Answers)

3. Assessing dates

The date function =DAYS tells you the number of calendar days between two dates. These counts apply to report periods and serve an important role in analytics. Correct date application ensures that you count every day only once. There are two formulas for this, as follows:

Formula = DAYS(FIRST CELL, SECOND CELL)

Formula = NETWORKDAYS(FIRST CELL,SECOND CELL, [numberofholidays])

Example:

If you want to track the number of days that the production line was operational, you can run this function. Consider column A representing the dates in ascending order. The two cells are the start and end dates of the period. It shows that the company had 26 production days during that time.

=NETWORKDAYS(A71,A104, [7])

=26

4. Tracking empty cells

=COUNTA determines whether a cell is full or empty. Data analysts incur incomplete sets regularly, and this helps evaluate their location and nature. It returns zero if the cell is empty and a one if the cell is full.

Formula: =COUNTA(SELECT CELL)

Example:

Consider a marketing firm conducting cold calls basing the contacts on a central list. In this situation, the A column is a yes or no option as to whether the user reached out to the consumer. When Mary starts her shift, she checks what changed in the data set.

=COUNTA(A5:A541)

This shows the string of cells, each receiving either a zero or a one. By sorting this data, Mary can easily track the team's progress.

Read more: What Does a Data Analyst Do? (With Requirements and Salary)

5. Sum of data

=SUMIFS is an important formula that can arise in an entry-level data analysis interview. The base of this function is =SUM, though most analysts expand this with extra parameters. In a product analysis context, this can help determine the amount by which any component contributes.

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

Example:

Consider a company that seeks the total revenue from each different product it offers. For example, consider column A representing an SKU, and column B representing revenue for the period. The analyst imports the cell values from a separate worksheet or page.

=SUMIF($B$4:$B$55,$A$4:$A$55)

=800,000

In this situation, cells A4 through A55 represent the entire outdoor section. The 800,000 shows the sum of its revenue.

6. Average of data

=AVERAGEIF has similarities to =SUMIF, and the two usually work in conjunction. It enables you to determine averages of multiple variables. It applies when companies look for the average hours an employee works, the mean revenue during a period, and other metrics. This function is best for providing data that represents a spread rather than a fixed point.

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

Example:

Consider a business that wants to track its average inventory stocks of a certain item, over a period of time. In this situation, column C is the blue jeans stock quantity on any given date in column B. To determine how many blue jeans the store carries, on average, the manager executes the formula.

=AVERAGEIF($C1:$C364,$B1:$B364)

=721

In this situation, the average quantity of blue jeans in the warehouse, between the two dates was 721.

7. Search for text

The =FIND and the =SEARCH functions perform similar applications by identifying a specific string of text within a set of data. With =FIND, the string is case sensitive, while with =SEARCH, it isn't case sensitive. To take a look at the macro picture of a dataset, =SEARCH applies, but for something specific, =FIND is more precise.

Formula:

  • =FIND(TEXT,WITHIN_TEXT,[START_NUMBER])

  • =SEARCH(TEXT,WITHIN_TEXT,[START_NUMBER])

Example:

For example, consider a data management company that wants to identify corporations from a large list. To do this, it can search the text for phrases including 'corp' or 'inc'. The analyst can use both functions, with each yielding a unique result.

=((FIND("Corp", C3:C211)"")

In this situation, the result shows any cell with the string 'Corp' in it, showing incorporated businesses in this category.

=((SEARCH("corp", C3:C211)"")

With this application, the results can return positive for phrases such as 'corpses' or 'corporate', even though they have lower case c.

Read more: How to Write a Data Analyst Resume (With Template and Example)

8. Connecting data sets

The =VLOOKUP function is one of the most significant data analysis Excel functions. It enables users to marry data from two different sources within the spreadsheet to get a numerical result. If an analyst requires information about the total revenue from a new product line, yet the supplier can only offer it in units, the =VLOOKUP lets the analyst connect the two and get the total.

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

Example:

In this situation, column A represents the company's sales values for a certain product, and the analyst wants to know how many items sold five units or fewer. The formula tells the program where to look for information by first referencing the price column and then searching for the correct value. It then places this into a separate portion of the spreadsheet to indicate the results. In this example, the results show in columns E and F. The parameters at the end of the formula indicate that the lookup function seeks, where the analyst wants to find values between zero and five.

=VLOOKUP($A2,$E$1:$F$5,0)
=17

The results mean that 17 items sold fewer than five units during that period. Using this information, the analyst can determine whether it is worthwhile to continue selling those items.

Read more: How to Write a Data Analyst Entry-Level Resume (With Tips)

9. Checking for errors

The =IFERROR formula is something that analysts use as a way to verify the accuracy of their data sets. When the analyst runs other functions, if the dataset lacks a match, it ordinarily returns an error code saying #VALUE. Instead of returning the error, analysts use =IFERROR to replace the errors with any value or text.

Formula: =IFERROR(FIND“VALUE”,SELECT CELL,VALUE_IF_ERROR)

Example:

In this situation, the analyst wants to return any cells that show an error with a value of one to fill the cell. If the analyst searches for items that say small, such as in a retail clothing store, a small typographical error can cause issues in the dataset. To prevent the error and return a text string that says 'yes' the analyst runs the following formula:

=IFERROR(FIND"SMALL",$A5:$A99,'Yes'),"")

Consider that the analyst uses this formula in column E, where the row number corresponding to an error in the word small, such as misspelling it, shows a Yes. If A17 said Small, then E17 says Yes. It allows analysts to isolate and address any issues in the dataset.

Please note that the company, institution, or organization mentioned in this article is not affiliated with Indeed.

Explore more articles