Using Basic Functions and Formulas in Microsoft Excel

(A step-by-step guide on the basic Microsoft Excel functions and formulas, and their applications in data analysis)

Every data analyst must know how to use the functions and formulas in Microsoft Excel. This is because they are very important when working with data, performing calculations, and solving data-related problems. This guide sheds light on basic functions like MIN, MAX, and AVERAGE, the best ways to use them, and how to solve simple problems with them.

An introduction to Microsoft Excel formulas and built-in functions

If you work with data in any capacity, then Microsoft Excel is one tool that you must use. Microsoft Excel is a software application that helps individuals, businesses, and industries develop business insights and reports. The software also has internal applications that aid its use; one of them being the Microsoft Excel Sheet Formulas and Functions. Let us dive into the most popular formulas and functions, and how they can be used.

SUM, AVERAGE, MAX and MIN

SUM

This function is the equivalent of the mathematical operation,
addition. If you have basic mathematics knowledge, then you must know what addition (and subtraction) is. The SUM function helps users add the total of any selected range of cell values on Microsoft Excel.
As you can see in the example below, the quantity of three products can be summed up using the formula =SUM(number1,[number2],…). Since the ranges that need to be summed up are B2 to B4, the formula translates to =SUM(B2:B4).

Using that formula, the total as seen below is 360.

AVERAGE

This function calculates the average of any chosen range of cells. It simply totals the numbers in the cells and then divides by the number of cells. Continuing with our previous example, the average quantity sold can be gotten using the formula,=AVERAGE(number1,[number2],…). Since the ranges that need to be calculated are B2 to B4, the formula translates to =AVERAGE(B2:B4).

Using that formula, the average total as seen below in cell B7 is 120.

MAX:

MAX simply stands for maximum. This function helps you find the highest number in a range of cells.
The syntax for the MAX function is =MAX(number1,[number2],…)

Steps to use the MAX Function

1. Pick a cell.
2. Enter =MAX(
3. Select the range you want to find the highest number for.
4. Click enter.

See the example images below:

From our formula, the maximum number for the range of cells B2 to B4 is 140.

MIN:

MIN is the opposite of MAX and simply stands for minimum. This function helps you find the lowest number in a range of cells.
The syntax for the MIN function is =MIN (number1, [number2]…)

Steps to use the MIN Function

1. Pick a cell.
2. Enter =MIN(
3. Select the range you want to find the lowest number for.
4. Click enter.

See the example images below:

From our formula, the minimum number for the range of cells B2 to B4 is 100.

The LOOKUP functions

One of the most used Microsoft Excel functions is the LOOKUP function. They are used to find the value from an array, or a range (a column or row). All LOOKUP functions simply reference a cell to match values in another column or row against the cell, then, retrieve the corresponding results from the respective columns or rows.

Why use the LOOKUP functions?

• You can find data both vertically and horizontally.
• You can determine an appropriate or exact match with these functions.
• You do not need to select an entire table.

VLOOKUP FUNCTION

The function is one of (if not) the most popular Microsoft Excel functions. It is one function that applies to our everyday lives. Take the Contacts App on your phone for instance. You can find a friend’s phone number by looking up his/her name. This is how the VLOOKUP function works. It can be used to find data in a vertically organized table. The VLOOKUP function finds a value by searching down the left column of a table.
The syntax for the VLOOKUP function is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup] )

Steps to use the VLOOKUP function

In our example below, we try to find the number of bread sold.

1. Choose a cell you want the formula to be calculated(B10)
2. ENTER =VLOOKUP(
3. Choose the item you want to look up (A10)
4. Select the range you want to search from(A2:B7)
5. Select the col_index_num. In our example, this number is 2 because column B is the second column.
6. Since we are looking for the exact match, enter 0 for range_lookup.
7. Click enter.

From our formula, the number of bread sold is 100.

XLOOKUP FUNCTION

The XLOOKUP is a powerful Microsoft Excel function that combines the usefulness of other Excel functions like HLOOKUP, VLOOKUP, IFERROR, INDEX MATCH, and so on. Like the VLOOKUP, the XLOOKUP finds a value by searching down the left column of a table. However, it can also find a value by searching up a column. It can also be used to find partial or multiple matches and can return a defined value for cases where the value searched for is not found.

The syntax for the XLOOKUP function is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Steps to use the XLOOKUP function

In our example below, we try to find the number of every item sold.

1. Choose a cell you want the formula to be calculated(B12)
2. ENTER =XLOOKUP(
3. Choose the item you want to LOOKUP (A12)
4. Select the range you want to search from(A2:B7)
5. Select the range you want your value returned from (B2:B7)
6. Click enter.

From our formula, the number of milk sold is 120.

HLOOKUP FUNCTION

This function is very similar to the popular VLOOKUP function. The only difference is that it searches horizontally in columns (hence the H), while its VLOOKUP brother searches vertically. Like every LOOKUP function, the HLOOKUP is used to find a value in a range or table by matching it with data in a row.
The syntax for the HLOOKUP function is =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup] )

Steps to use the VLOOKUP function

In our example below, we try to find the number of bread sold.

1. Choose a cell you want the formula to be calculated(E4)
2. ENTER =HLOOKUP(
3. Choose the item you want to >lookup>(B1)
4. Select the range you want to search from(B1:G2)
5. Select the row_index_num. This number is 2 in our example because Quantity Sold is the second row.
6. Since we are looking for the exact match, enter 0 for range_lookup.
7. Click enter.

From our formula, the number of bread sold is 100.

THE IF FUNCTIONS

The IF functions are very popular and useful Microsoft Excel functions. The IF functions are used to determine whether certain conditions are met or not. An IF statement will test a condition, then return one value if the condition is met (TRUE), and another value if it is not (FALSE).
The syntax for the basic IF function is =IF(logical_test, [value_if_true], [value_if_false])

Steps to use the IF Function

In our example below, we try to find if the results of some students are good or bad. Any score over 80 is considered good, while scores less than 80 are bad.

1. Choose a cell you want the formula to be calculated(C2)
2. Enter =IF(
3. Enter the logical test you want to be calculated (B2>80)
4. Enter the value you want produced if true (Good)
5. Enter the value you want produced if false (Bad)
6. Click enter.

From our formula, the first result is a good one as the score is over 80.

You can repeat the formula for the other results by flash-filling.

SUMIF FUNCTION

This function is a simple one. It sums/adds up the values in a range that meets the criteria specified.
The syntax for the SUMIF function is =SUMIF(range, criteria, [sum_range])

Steps to use the SUMIF Function

In our example below, we calculate the number of goals scored by every player from the club, Barcelona.

1. Choose a cell you want the formula to be calculated(G5)
2. ENTER =SUMIF(
3. Select the range you want your criteria gotten from (B2:B11)
4. Specify the criteria (B2 which has the value “Barcelona”)
5. Select the range you want to be summed. (C2:C11)
6. Click enter.

From our formula, the total number of goals scored by players from Barcelona is 166.

We can repeat the same syntax to find the number of goals scored by Real Madrid players.

From our formula, the total number of goals scored by players from Real Madrid is 130.

SUMIFS FUNCTION

This is a more complex function. It sums/adds up the values in a range based on one or more true or false conditions.
The syntax for the SUMIFS function is =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2] …)

Steps to use the SUMIF Function

In our example below, we calculate the number of goals scored by every player from the club Barcelona, in the year 2009.

1. Choose a cell you want the formula to be calculated(I6)
2. ENTER =SUMIFS(
3. Select the range you want to be summed. (D2:D9)
4. Select the range you want your first criteria gotten from (A2:A9)
5. Specify the criteria (A6 which has the value “2009”)
6. Select the range you want your second criteria gotten from (B2:B9)
7. Specify the criteria (B2 which has the value “Barcelona”)
8. Click enter.

From our formula, the total number of goals scored by players from Barcelona in the year 2009 is 84.

We can repeat the same syntax to find the number of goals scored by Real Madrid players in the year 2011.

From our formula, the total number of goals scored by players from Real Madrid in the year 2011 is 103.

OTHER BASIC FUNCTIONS

We have looked at the LOOKUP and IF functions and how they are used. Despite being the most popular, these functions are not the only Microsoft Excel functions you will need for your day-to-day calculations and problem-solving. You also need basic functions like Trim, Len, Concatenate, and Count. Let’s start with the Trim function.

TRIM FUNCTION

When you have irregular text spacing, you might experience some difficulties when analyzing your data. The TRIM function helps you remove any irregular spacing.
The syntax for the TRIM function is =TRIM(text)

Steps to use the TRIM Function

1. Choose a cell (A3)
2. Enter =TRIM(
3. Select the text you want to trim(A1)
4. Click enter.

The TRIM function returns a new text without irregular spaces as seen below:

LEN FUNCTION

This Microsoft Excel function is simply used to find the length of a cell. It tells you the number of characters in any given cell.
The syntax for the LEN function is =LEN(text)

Steps to use the LEN Function

1. Choose a cell (A5)
2. Enter =LEN(
3. Select the text you want to find its length (A3)
4. Click enter.

N.B: The LEN function also calculates the spaces characters (if any) in a cell. Below you can see that our formula returns 31 for the text, ‘How do I use the TRIM function?’ instead of 25 if it didn’t count the spaces characters.

CONCATENATE FUNCTION

To concatenate simply means to link/join two or more things together. The CONCATENATE function helps you join or combine texts from multiple cells into one cell. Over time, newer Microsoft Excel functions such as Flash Fill, TEXTJOIN, and CONCAT have slowly replaced CONCATENATE.
The syntax for the CONCATENATE function is =CONCATENATE(text1, text2, text3….)

Steps to use the CONCATENATE Function

1. Choose a cell (A7)
2. Enter =CONCATENATE(
3. Select the texts you want to join (A1,A2,A3,A4, and A5)
4. Click enter.

The result can be seen below:

COUNT FUNCTION

This Microsoft Excel function is used to count the number of cells that contain numbers in a range. It doesn’t count cells with letters. To count cells with letters, the COUNTA function can be used.
The syntax for the COUNT function is =COUNT(value1, [value2],[value3],…)

Steps to use the COUNT Function

1. Choose a cell (B12)
2. Enter =COUNT(
3. Select the range of cells you want to count (A1:A10)
4. Click enter.

As you can see from the result below, only the cells that contained numbers were counted.

LEFT, RIGHT, MID FUNCTIONS

These functions are the most basic text functions in Microsoft Excel. They can be used to extract any specific parts of a text string. You can use the LEFT, RIGHT, and MID functions to extract the first four letters of a text, the last six letters, or seven letters from the middle of that text.
The syntax for the LEFT, RIGHT, and MID functions are:
=LEFT(text,num_chars)
=RIGHT(text,num_chars)
=MID(text,start_num,num_chars)

Steps to use the LEFT Function

1. Choose a cell (A3)
2. Enter =LEFT(
3. Select the text you want to extract from (A1)
4. Enter the number of characters you want to extract (2).
5. Click enter.

As you can see, the first two letters from the text are ‘My’.

Steps to use the RIGHT Function

1. Choose a cell (A3)
2. Enter =RIGHT(
3. Select the text you want to extract from (A1)
4. Enter the number of characters you want to extract (14).
5. Click enter.

As you can see, the last fourteen letters from the text are ‘Stephen Okorie’.

Steps to use the MID Function

1. Choose a cell (A3)
2. Enter =MID(
3. Select the text you want to extract from (A1)
4. Enter the position of the first character you want to start extracting (12).
5. Enter the number of characters you want to extract (7). Click enter.

As you can see, the middle letters extracted from the text are ‘Stephen’.

Conclusion

Microsoft Excel is a very easy but powerful tool for businesses, data analysts, and marketers. Used properly, it can help us perform complex calculations, boost performance, and improve efficiency. In this article, we have learned about the basic Microsoft Excel functions and formulas, and how they can be applied. With continuous practice, you are bound to learn more about these functions and formulas, and even other advanced ones.

Comments

[Okorie Stephen]

My very first article on here. Open to correction/constructive criticism. Cheers!