Excel is one of the most powerful tools used by data analysts worldwide. Excel formulas perform automatic calculations that streamline workflows. Dynamic array formulas can handle a large range of cells at once. Excel formulas help to calculate and analyze large datasets. This post will show you 10 essential Excel formulas every data analyst should know to boost their skills and productivity.

Let’s consider a sample sales data to show the example of 10 essential Excel formulas every data analyst should know.

1. SUM

The SUM function is one of the most used functions for data analysis. The SUM formula adds up the number from the selected cell range.  Quickly calculate the total sales, expenses, or any other measurable quantity, making it essential for any basic data analysis. 

Syntax:

=SUM(number1, [number2], …)

Formula:

=SUM(G2:G71)

It calculates the total sales of all products from the “Sales Amount” which is $9,868.32.

2. AVERAGE

The AVERAGE function calculates the average (arithmetic mean) of a range of numbers. The AVERAGE formula determines the average performance, sales, spending, or any metric that needs summarizing.

Syntax:

=AVERAGE(number1, [number2], …)

Formula:

=AVERAGE(E2:E71)

This formula calculates the average “Retail Price” which is $84.42.

3. MIN & MAX

MIN & MAX function returns the smallest (MIN) and largest (MAX) values in a dataset. These functions help identify data range, trends, outliers, and potential errors. These functions are helpful for risk analysis and performance tracking.

Syntax:

=MIN(number1, [number2], …)

=MAX(number1, [number2], …)

Formula:

=MIN(G2:G71)

=MAX(G2:G71)

Both of this formula returns the minimum ($29.99) and maximum ($299.97) “Sales Amount”.

4. COUNT & COUNTA 

The COUNT function returns the number of cells that contain numbers. The COUNTA function counts the number of cells that are not empty. It includes both numbers and text.

Syntax:

=COUNT(value1, [value2], …)

=COUNTA(value1, [value2], …)

Formula:

=COUNT(A2:A71)

It counts the total number of “Orders” which is 70.

=COUNTA(C2:C71)

COUNTA counts the total number of Customers (non-empty values in the “Customer Name” column) which is 70.

5. IF

The IF function checks conditions and returns one value if a condition is true and another if it is false. This is important for logical tests in data analysis.

Syntax:

=IF(logical_test, value_if_true, value_if_false)

Formula:

=IF(G2>150, “Higher Sales”, “Lower Sales”)

The IF formula checks the orders with a Sales Amount greater than $150. If the condition is true this will return “Higher Sales” otherwise “Lower Sales”.

6. VLOOKUP & HLOOKUP

The VLOOKUP function looks for a value in the first column of a table and returns a value in the same row from a specified column. It’s a must-have to extract data from another sheet or table. The HLOOKUP works similarly to VLOOKUP but looks at data horizontally rather than vertically. It looks for a value in the top row and returns a value from a row below.

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Formula:

=VLOOKUP(1007, A2:C21, 3, FALSE)

It looks up a “Customer Name” based on a specific Order No (e.g., 1007). This will return the Customer Name for Order # 1007, which is “Daniel Martinez”.

Microsoft Excel 365 offers the XLOOKUP function which is more advanced and dynamic.

If your data is structured horizontally then you can use the HLOOKUP function.

=HLOOKUP(1007, A1:F2, 2, FALSE)

It returns the value from the second row (row index = 2) in the same column where 1007 was found.

7. INDEX-MATCH

INDEX and MATCH together create a more flexible alternative to lookup values. It allows for searches both horizontally and vertically, and it works with columns in any order. INDEX returns a value from a specified position, and MATCH locates the position of a value in a range.

Syntax:

=INDEX(array, row_num, [column_num])

=MATCH(lookup_value, lookup_array, [match_type])

Formula:

=INDEX(D2:D71, MATCH(“David Wilson”, C2:C71, 0))

• MATCH(“David Wilson”, C2, 0): Returns the row where “David Wilson” is located in column C.
• INDEX(D2:D71, …): Returns the value from the Ship Date column D that corresponds to the row found by MATCH.

This formula will return the “Ship Date” for” David Wilson”, which is 1/8/2024 based on the dataset.

8. CONCATENATE (or TEXTJOIN)

The CONCATENATE function joins two or more text strings into one. In updated versions of Excel, you can also use TEXTJOIN to add delimiters between strings.

Syntax:

=CONCATENATE(text1, [text2], …)

=TEXTJOIN(delimiter,ignore_empty,text1,…)

Formula:

=CONCATENATE(A2,” “, C2)

=TEXTJOIN(” “, TRUE, A2,C2)

These formulas combine the “Customer Name” with the “Order Number” with a space delimiter which is “1001 John Smith”.
 

9. SUMIF / COUNTIF

SUMIF adds the values in a range based on specific conditions. The COUNTIF counts the number of cells based on specific criteria.

Syntax:

=SUMIF(range, criteria, [sum_range])

=COUNTIF(range, criteria)

Formula:

=SUMIF(E2:E21, “>50”, G2:G21)

This formula sums up the values in the “Sales Amount” column where the corresponding “Retail Price” is greater than 50. The result is $1,819.83.

=COUNTIF(E2:E71, “>50”)

It counts the number of orders where the “Retail Price” is greater than 50. total count is 39.

10. LEFT, RIGHT, LEN

The LEFT, RIGHT, and LEN functions in Excel are essential for working with text data. These functions extract specific portions of a text string or count its characters to perform tasks like splitting, formatting, or validating text entries. These functions are beneficial when cleaning and preparing data for analysis.

Here are the syntax and examples for LEFT, RIGHT, and LEN functions, which are useful for manipulating and analyzing text data in Excel.

Syntax:

=LEFT(text, [num_chars])

Formula:

=LEFT(C2, 4)

Extract a specified number of characters from the starting of a text string. It returns the first four characters of the “Customer Name” that is”John” for “John Smith”.

Syntax:

=RIGHT(text, [num_chars])

Formula:

=RIGHT(C2, 5)

Extract a specified number of characters from the end of a text string. This formula returns the last five characters of the “Customer Name” that is”Smith” from “John Smith.

Syntax:

=LEN(text)

Formula:

=LEN(C2)

This formula returns the number of characters in the “Customer Name” that is 10 for John Smith (including the space).

Practical use of these essential formulas:

Conclusion

Mastering these 10 essential Excel formulas will help you to be a skilled data analyst. These formulas will help to streamline your workflows, automate calculations, and quickly analyze large datasets. Keep practicing these formulas to handle data with precision and speed. The pivot table is not a direct function but it’s one of the most useful tools for data analysis. You can practice some other functions like TEXT, ROUND, FILTER, SORT, TRIM, and DATEDIF.