The first step to using Excel is to understand the formulas. Excel functions and formulas are inserted into the cells.
If you are an aspiring business or data analyst, then you should have good knowledge of Excel and the formulas therein. There are several formulas that are specially designed for business analysis, and it is quite pertinent for you to have a good grasp of the formulas. Do you wish to have a taste of these formulas? Let's go! We will walk you through the top 10 Excel formulas for Business Analysts.
1. SUMIFS Formula
This formula will reduce the stress of massive filtering and manual selections. It is very efficient in collecting and summing up a well-defined set of data/values that meet certain criteria.
=SUMIFS(the values you want to sum up, criteria for column 1, criteria, criteria for column 2, criteria…)
=SUMIFS(sales, regions, "A", products, "B", customer types, "C", month, "M")
2. VLOOKUP Formula
This formula simplifies your efforts when searching for a particular value in a set of data. It unravels the number and also displays the corresponding value.
VLOOKUP formula lets you search for a value in a table and return a corresponding value. For example, you can ask the name of the customer with ID=C00023 or How much the product price for product code =p0089, and VLOOKUP would give you the answers.
Below is the syntax for VLOOKUP
=VLOOKUP( this value, your data table, column number, optional is your table sorted?)
=VLOOKUP("C003", customers' table, 3, false)
This syntax says that we Lookup customer ID C003 in the customers' table and return the value from the 3rd column.
We use "False" when we assume that the customers' table is unsorted.
3. INDEX & MATCH Formula
These two formulas are twins in nature. They assist in searching for designated values in any column, and they return the corresponding values without any limitations.
See below for their syntaxes:
=INDEX(the values, MATCH(the item you want to lookup, the column, sorting status of the column))
=INDEX(Student IDs, MATCH("Paul Peterson", Student names, 0) )
We use "0" because the column is not sorted.
4. Nesting Formulas
Business analysis involves complex formulas, and you can simplify it by nesting. By nesting, we mean inserting one formula in the other. Nesting is a must-learn formula for all business analysts because it helps us to simplify complex logic formulas. You can nest as many formulas as possible.
Give out a 10% hike to customers who visit our website at least 4 days per week; Give an 8% hike to customers who visit our website at least twice a week; for others, give a 3% hike.
The Nested formula for this is:
=IF(number of customers who visit our website in a week >=4, "10% hike", IF( number of times employee reads chandoo.org in a week >1, "8% hike", "3% hike"))
In the formula above, two IF formulas were nested together.
5. Text formulas
It is very pertinent for all aspiring analysts to know how to use text formulas in Excel. Text formulas are very efficient in cleaning and arranging tons of data. There are a number of text formulas; some are advanced while some are basic. For a start, you should be well vested with the following formulas:
LEFT, RIGHT & MID
Illustrations of how these formulas are used are stated in a series of our posts hereon.
6. Basic Arithmetic Expressions
This is clearly basic mathematics operations, and I'm quite sure that you know about them. You actually need them too in excel. As a business analyst, you need to know the formulas for addition, subtraction, multiplication, division, power functions, etc.
Below is a typical example of excel Arithmetic functions:
=(((215+102)*(253+174)) > ((103-274)/(537-782)))^2
7. IF Formula
Businesses make decisions every day, and the IF function is handy for business analysts.
This is the syntax for IF function:
=IF(Condition for testing, True, False value)
For instance, your company may decide to give 15% bonuses to customers who buy from websites & 10% hike to others. Now, the Excel formula for this example is written below:
=IF(Customers who buy from our websites, "15% bonus", "10% bonus")
8. IS ERROR
Business analysis should be done devoid of errors and IS ERROR function assists in detecting errors in formulas. When you enter the formula, the result will be "true" if the cell is faulty, but will show "false" if the cell has no error.
The formula for IS ERROR function:
9. OFFSET formula
This formula solves problems involving ranges by taking references from a particular starting point. In this case, the height and width of the cells are given.
This is the OFFSET formula:
=OFFSET(starting point, rows to move, columns to move, height, width)
10. SUBTOTAL Formula
As the name implies, business analysts use the SUBTOTAL formula to deduce the subtotal of a given range of data in cells. This formula is versatile because it can carry out addition, find the average, and add a number of cells.
SUBTOTAL's formula is stated below:
=SUBTOTAL(Type of Total, Range of Cells)
The ten formulas listed here are essential tools in business analysis. As a Business analyst, you will come across these formulas on a daily basis.
Here are more excel formulas you can consider as an analyst.
Rank allows you to quickly denote your data either in ascending or descending order, it is an ancient function that doesn’t downplay the effectiveness of data analysis.
Note: [order] is optional
MINIFS is used to find the lowest price each product is sold for.
The UNIQUE Function is a new function available to those using Microsoft 365 version only. The function wants to know three things:
- The range to return the unique list from
- Whether you would like to check for unique values by column or row
- Whether you want a unique list or a distinct list.
=UNIQUE(array, by col, exactly once)
We have a list of product sales and we want to extract a unique list of product name. For this we only need to provide the range
This is a dynamic array function and therefore spills the results. The blue border indicates the spilled range.
This function will filter the range. It is an extremely powerful function and a dream for analyzing data and product reports. The Filter function makes three arguments
- The range to filter
- The criteria that specifies which result to return
- What action to take if no results are returned
=FILTER(array, included, if empty)
The COUNT function, counts the total number of cells in a range that contains a number. Only includes cells that hold data in any other format apart from numeric.
To count from C1 TO C4
When required to count all the cells with numeric values, text, and any other data format you use the below formula.
To count the blank cells present in a range of cells you use the formula below.
=REPLACE(old_text, start_num, num_chars, new_text)
Start_num refers to the index position you want to start replacing the characters with. Num_chars indicate the number of characters you want to replace.
Here, we are replacing A101 with B101 by typing “=REPLACE(A5,1,1,”B”)”
Next, we are replacing A102 with A2102 by typing “=REPLACE(A6,1,1,”A2”)”
Finally we are replacing Adam with Sam by typing “=REPLACE(A7,1,2,”Sa”)”
17. LEFT, RIGHT, MID
The LEFT() function gives the number of characters from the start of a txt string.
The MID() function returns the characters from the middle of a string, given a starting position and length.
the RIGHT() function returns the number of characters from the end of a text string.