Since I’m unemployed, I had a lot of time to focus on lots of things which we’ll go through one by one.

20/08/2025

Focusing on the top formulas used in Excel.

If you’re wondering where am I getting this from don’t worry. I’m following along Chandoo’s “Free DATA Analyst Course” and the resources are mentioned in the video’s description (Not Sponsored)

I have added a new worksheet and added the necessary data as per the image below:

But there’s an issue with it - Duplicate Values. If you look at the image, Business Development has been repeated 3x and we need to remove it. To do that, select the entire column → go to “Data” tab → select “Remove Duplicates”

Select “Continue with the current selection” and click OK.

Always make sure to select from the Column Heading so that Excel will find it easier to identify the column heading.

Now, I need to figure out how many people are from each department (eg, I want to know how many people are in Sales department). To do that, we’re gonna use a function called COUNTIFS which is used to count the number of cells that meet one (or) multiple criteria (in this case counting the number of employees from each department)

Here’s how it goes : =COUNTIFS(staff[Department],C6)

“staff” refers to the table name

“Department” refers to the column name

C6 refers to the name of the department.

Next formula - SUMIFS which is used to add cells that meet one (or) more criteria (in this case, the total amount that a department is getting paid).

Here’s how it goes : =SUMIFS(staff[Salary],staff[Department],Sheet1,C6:C17)

Now, our data shows up like this:

I also want to calculate the average salary of each department based on the current data. To do that, let’s use AVERAGEIFS → which gives the average range of cells based on a criteria (or) multiple criteria.

Eg: =AVERAGEIFS(staff[Salary],staff[Department],C6:C17)

staff[Salary] refers to the range of cells from which we want to get our average values from, staff[Department] refers to the criteria upon which the average values are based.

FILTER function is used to filter data based on a select criteria.

For eg, =FILTER(staff,staff[Salary]>100000)

staff specifies the range or the table from which we’re getting the data. Note that staff is a table name & not the actual formula.

staff[Salary] is where we’re specifying the criteria. In this case, I want the list of employees whose salary is greater than 100k.

This is how the data appears.

It appears without the headers & we have to manually format columns as some of them have only numbers but when you look at the table, it shows the actual dates with the proper formatting.

In businesses, they don’t want to look at this entire table. They just want to look at the people who are earning more than 100k with certain columns. How do you do that?

Use CHOOSECOLS → extracts specific columns from a range or array and returns them as an array.

Best part is it can be combined with different formulas (or) the output of a formula.

For eg, =CHOOSECOLS(FILTER(staff,staff[Salary]>D4),1,2,3,5,6)

The above formula chooses the entire column based on the FILTER function & shows column number 1, 2, 3, 5 & 6 but unfortunately it won’t work on mine cause it works only on Excel 365 which I don’t own. I use a simple Excel 2021 version.

We can also add multiple conditions in the FILTER function. For eg, let’s say I want to know all the female employees who earn > 100k.

Now, I can add two conditions: =FILTER(staff,(staff[Salary]>D4)*(staff[Gender]="Female"))

But I don’t want each and every single column, only the 1st 6 columns. We can do that using the same filter function. Let me show you how:

=FILTER(**staff[[Emp ID]:[Salary]]**,(staff[Salary]>D4)*(staff[Gender]="Female"))

Essentially, I’m telling Excel, “Hey, filter the staff table & show me columns from Emp ID to Salary”.

Let’s take a look at MIN, MAX, LARGE & SORT.

MIN - returns the lowest value of a range/array of cells. Eg, =MIN(staff[Salary])

MAX - returns the largest value of a range/array of cells Eg, =MAX(staff[Salary])

LARGE - returns the top highest values of a range/array of cells. For eg, =LARGE(staff[Salary],1). Here Excel will show the 1st largest value in the column Salary from the table “staff” cause “1” has been mentioned in the end which means the 1st largest value of the column. Similarly you can enter 2 (or) 3 instead of 1 and it will show you the 2nd or 3rd largest value in the column.

SORT - sorts/arranges rows (or) columns in a specified criteria.

=SORT(staff[Salary]) → this shows the column Salary from the table “staff” in Ascending Order by default. You can sort by Descending Order using this → =SORT(staff[Salary],,-1) adding -1 at the end.

Let’s take a step further.

What if I want to know the lowest salary of a male (or) a female employee?

In that case, use MINIFS which returns the minimum value of a range of cells based on a criteria/condition.

Eg: =MINIFS(staff[Salary],staff[Gender],D$4)

staff[Salary] - the range of cells from which you want your value to be

staff[Gender] - the criteria from the range of cells which you want your value to be

D$4 - contains the criteria exact value (in this case, Male)

The same can be done for Females as well.

MAXIFS - returns the maximum value of a range of cells based on a criteria/condition.

Eg. =MAXIFS(staff[Salary],staff[Gender],D$4)

UNIQUE - returns a list of unique values from a select range of cells. Helpful if you don’t want any duplicate values.

Eg. =UNIQUE(staff[Department]) - this shows only the unique values and doesn’t show up any duplicate values.

COUNTA - counts the number of cells in a range that contains any type of data. It does not count empty cells.

Let’s look at an example:

I want to count the number of Departments in the above image. Let’s look at the formula:

=COUNTA(B4:B15)(or) =COUNTA(B4#)

What’s with the #?

“#” is called a spill operator which is used to fill the values automatically. In this case (=COUNTA(B4#)), by adding a “#” at the end, Excel automatically fills the values from B4 to B15 (or) from B4 till the cells with data.

Another cool thing about the spill (#) operator is whenever values get updated in the table will automatically reflect here as well.

21/08/2025

VLOOKUP - stands for ‘Vertical Lookup’. This formula looks up the data vertically & returns the required value. It is one of the most popular formulas in Excel.

Eg. =VLOOKUP(C4,staff,2,FALSE) - C4 is the value I want to lookup for, staff is the table where the data is present, 2 is the column from which the value will be returned & FALSE means I get an exact match.

Sometimes, the desired Employee ID may not be available in the table. In such cases, an error will occur. To avoid that, let’s use IFERROR which basically says “Hey, if there’s an error with the formula, enter any message you want (in this case, I’ll type “Not found”)”.

Eg. =IFERROR(VLOOKUP($C$4,staff,2,FALSE),"Not Found")

One issue with VLOOKUP is that it only searches to the right of the lookup column. Let’s say I want the Last name from the Employee ID. Last name is in 3rd column and Employee ID is in 1st column.

So when you use VLOOKUP , Excel automatically searches from the column Last name & proceeds to the right of the column which ultimately ends up in an error.

So, how do you solve this?

There’s 2 ways - by using INDEX + MATCH or by using XLOOKUP. Let’s explore these concepts one by one.

MATCH - this function is used to find where the item is present in a list. Let’s understand this using an example.

Now I want to find out where is Scad present in the table (or) in the sea of data. To do that, let’s use the formula → =MATCH(C16,staff[Last Name],0)

C16 - the data which we want to find out

staff[Last Name] - the range of data from which the data is present

0 - specifying the match type (0 stands for an exact match)

Here’s the output:

It has shown that the value “Scad” is present in the 43rd row of the table (or) the range of data which also means that “Scad” is the 43rd employee.

With this data, I would like to find the Employee ID, Department & the Salary.

Here, we can use the INDEX formula which gives us the required value from the rage of data.

Eg, =INDEX(staff[Emp ID],C17) this returns the employee ID from the row no.43 of the table. C17 refers to the data from which we previously got using the MATCH function.

Here, we can combine both the INDEX and the MATCH functions to get the same result.

Eg. =INDEX(staff[Emp ID],MATCH(C16,staff[Last Name],0))

We can achieve the same by using XLOOKUP which is a much more better & smarter version of VLOOKUP.

Let’s explore XLOOKUPwith examples.

=XLOOKUP(C4,staff[Emp ID],staff[First Name])

C4 - refers to the value which we want to look up

staff[Emp ID] - refers to the array of values from which Excel should look up

staff[First Name] - the array of values from which Excel should return

This looks good but what’s the difference? Let’s go into it.

Why it’s showing up as #N/A? Cause in the previous range, we have used IFERROR.

One of the advantages of using XLOOKUP is you don’t have to use IFERROR as you can specify in the end what to do if the value doesn’t match.

Here’s an eg: =XLOOKUP(C4,staff[Emp ID],staff[First Name],"Not Found")

"Not Found" - this specifies the criteria of what to do if the data is not found. So instead of using IFERROR, we can simply add the condition within the XLOOKUP formula.

Another advantage is instead of returning a single value, you can return multiple values using XLOOKUP. Here’s an example

=XLOOKUP(C16,staff[Last Name],staff,"Not Found")

This formula specifies that I need the entire row of the value that is present in C16.

This is helpful when you want to find the details of a specific employee. This shows the full row corresponding to Tuxwell.

Why it’s showing up in a row? Well, that’s how the data is present in the table hence it shows up as a row. But what if I want it to be in a column. Well, that’s where TRANSPOSE comes in.

TRANSPOSE - flips the data from columns to rows and from rows to columns.

Eg, =TRANSPOSE(XLOOKUP(C16,staff[Last Name],staff,"Not Found"))

Alright, moving on…

I want to find the name of the person who earns the highest salary. Here’s how:

=XLOOKUP(MAX(staff[Salary]),staff[Salary],staff[First Name]&" "&staff[Last Name],"Not Found")

Here, we have combined the MAX function within the XLOOKUP .

Also there’s another interesting part in the formula - the 1st name and the last name has shown up as the result but how?

&" "& - this is helpful to combine values and the " " is helpful in giving the necessary space between the values.

But if we look at the table, there’s another person who’s also earning $120,000. So, how can we show it up? By using the FILTER function.

=FILTER(staff[First Name],staff[Salary]=MAX(staff[Salary]))

This shows up both the person’s names but we don’t want that. We want their full names. So, we can use the &" "& trick which was used previously.

=FILTER(staff[First Name]&" "&staff[Last Name],staff[Salary]=MAX(staff[Salary]))

Let’s explore another function - LEFT which is used to extract certain numbers or text from the left side of the text (or) the number.

For eg, =FILTER(staff,LEFT(staff[First Name],1)="H")

This returns the names of all the people who have a First Name that starts with “H”

But I want to know all the employees who have joined in March. To do that, let’s use the MONTH function - =FILTER(staff,MONTH(staff[Start Date])=3)

Here “3” represents the 3rd month of the year which is March.

22/08/2025

Let’s explore Pivot Tables in-depth by answering business-related questions.

To create a Pivot Table, go to Table Design → select “Summarize with PivotTable”

Alternatively, you can also go to the Insert tab & find the PivotTable option.

Click OK. For now, let’s place the PivotTable into a new worksheet. Later, we can explore the option “Add this data to the Data Model”.

1 → its’ called the field’s list. Each column is a field

2 → field’s area where you can drag the field into the relevant aspect of the field’s area.

With this info, let’s answer our 1st question - How many calls we are getting by customer?

For this, we have dragged the Customer ID to Rows & the Call number to Values.

Rows arrange the data in rows & Values help us calculate our values (can be sum, average etc…)

2nd question - How satisfied are our customers?

Let’s drag the Satisfaction Rating field to Values cause it contains the Individual customers satisfaction ratings.

But there’s a problem. By default, it has added the values. But we only require the average rating of each customer. To do that, select any value in Satisfaction rating → right click → Summarize Values by → select “Average”

But the numbers are too accurate. I want it in a format like 3.9 for example. To do that, select any value in Satisfaction Rating → click on Number Format → follow the steps in the below image and click OK.

3rd question - Who are our top 10 customers?

Let’s create a new PivotTable for this.

After creating a new PivotTable, drag the Customer ID field to Rows & the Purchase Amount field to Values.

Now that we’ve got the necessary data at place, let’s use the Filter option to find our top 10 customers. To do that, select the tiny arrow near the Row Labels column

Then go to Value Filters → select Top 10

Click OK and proceed.

4th question - Top 10 customers for a specific representative? Let’s say that customer C0003 might prefer R05 more. Similarly customers might prefer a certain representative more than anybody else. How to identify such representatives?

Drag the Representative field to the Filters below → click on the tiny arrow in the Representative cell → make sure to tick “Select Multiple Items” → select the desired representative of your choice

I also created an imaginary PRD (which is one of the requirements as a BA) by copying & pasting a company’s JD into a tool like DeepSeek & asking what kind of project should I build in order to impress the recruiter & that the project should be beginner friendly.

Here’s the PRD → Click Here

See ya until next week!