20 Useful Excel Function for Data Analysis

Ankit Anshu
5 min readMar 26, 2022

--

Microsoft Excel is an easy and powerful tool for data analysis. Excel is a widely used software application in industries today, built to generate reports and business insights. Excel contains over 450 functions, with more functions added every year. Here we cover those functions that are mostly used in data analysis.

MAX/MIN

The MAX and MIN functions are just what the names imply. MAX will find the largest number in a range, while MIN finds the lowest number in a range.

The formula for this Excel function is:

=MAX(number1, [number2], …)

MAX Function

The formula for this Excel function is:

=MIN(number1, [number2], …)

MIN Function

IF

The IF function tests a condition, and then reacts differently depending on whether the test was true or false.

The formula for this Excel function is:

=IF(test, value_if_true, value_if_false)

IF Function

IFS

The IFS function checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition.

The formula for this Excel function is:

=IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)

IFS function

SUM

The SUM function, as the name suggests, gives the total of the selected range of cell values. It performs the mathematical operation which is addition.

The formula for this Excel function is:

=SUM(number1, [number2], …)

SUM function

SUMIF

The SUMIF function returns the sum of cells that meet a single condition.

The formula for this Excel function is:

=SUMIF(range, criteria)

SUMIF function

SUMIFS

SUMIFS function adds all numbers in a range of cells, that meet multiple criteria.

The formula for this Excel function is:

=SUMIFS(sum_range, criteria_range1,criteria1,…)

SUMIFS function

COUNT

COUNT function as name suggest count the total number of cell in a range that contains a number.

The formula for this Excel function is:

=COUNT(Range)

COUNT function

COUNTIF

COUNTIF counts the numbers how many times a value appears based on one criteria.

The formula for this Excel function is:

=COUNT(Range,criteria)

COUNTIF function

COUNTIFS

COUNTIFS counts the numbers how many times a value appears based on multiple criteria.

The formula for this Excel function is:

=COUNT(Range,criteria)

COUNTIFS function

LEN

LEN is used to display the number of characters in a string.

The formula for this Excel function is:

=LEN(text)

LEN function

TRIM

TRIM function allows you to remove unwanted spaces or characters from text.

The formula for this Excel function is:

=TRIM(text)

TRIM function

LEFT

LEFT returns the desired number of characters from the beginning of the cell.

The formula for this Excel function is:

=LEFT(text,num_chars)

LEFT function

RIGHT

RIGHT returns the desired number of characters from the end of the cell.

The formula for this Excel function is:

=RIGHT(text,num_chars)

RIGHT function

SUBSTITUTE

The SUBSTITUTE function replaces the existing text with a new text in a text string.

The formula for this Excel function is:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

SUBSTITUTE function

CONCATENATE

Concatenate allows you to combine the values of several cells into one.

The formula for this Excel function is:

=CONCATENATE(text1, text2, text3, …)

CONCATENATE function

DAYS

DAYS function determines the number of calendar days between two dates.

The formula for this Excel function is:

=DAYS(end_date,start_date)

DAYS function

NETWORKDAYS

NETWORKDAYS formula determines the number of “workdays” between two dates as well as an option to account for holidays.

The formula for this Excel function is:

=NETWORKDAYS(start_date,end_date,[holidays])

NETWORKDAYS function

UNIQUE

The UNIQUE function returns a list of unique values in a list or range. Values can be text, numbers, dates, times, etc.

The formula for this Excel function is:

=UNIQUE(range)

UNIQUE function

LOOKUP

LOOKUP function performs an approximate match lookup in a one-column or one-row range, and returns the corresponding value from another one-column or one-row range.

The formula for this Excel function is:

=LOOKUP(lookup_value,lookup_range, [result_range])

LOOKUP function

Thanks for reading this article so far. If you like then please share them with your friends and colleagues. If you have any questions or feedback, then please drop a note.

--

--