Learn all the important Excel Formulas in One Hour [Telugu] — Transcript
Full transcript
- 0:00When it comes to Excel, the biggest
- 0:02blunder many people make is trying to
- 0:05learn everything one after another by
- 0:07rote memorization, like SUMIFS,
- 0:09COUNTIFS, VLOOKUP, XLOOKUP, and so on.
- 0:13Instead of that, how should we approach
- 0:15it when we actually have a business
- 0:16problem? Which function should we use
- 0:19for that problem and in what way? If we
- 0:21learn which function to combine with
- 0:24another, it stays in our mind for the
- 0:27long term. This allows us to work much
- 0:29better. So, in this video, we will take
- 0:3210 business problems and look at which
- 0:35formulas to use for them. Why should we
- 0:37use them? How to use them? How to nest
- 0:40one inside another? I will explain all
- 0:42of this to you thoroughly, clearly, and
- 0:45in detail. Alright, let's head into the
- 0:48video and talk about the rest of the
- 0:49details. Let's go. So, this is the file
- 0:52we are going to use. I have a sample
- 0:54employee dataset in this. Likewise, we
- 0:58are learning these 10 concepts; I will
- 1:00clearly explain how to answer these 10
- 1:03business questions and all these
- 1:05functions along the way. I have also
- 1:09prepared some challenges and homework;
- 1:10you will understand what those are as
- 1:12we go along. I have placed both the
- 1:15completed version and the blank version
- 1:17of this file in the video description.
- 1:19My suggestion is that you download the
- 1:21blank version and practice along with
- 1:24me as I explain, so the concepts will
- 1:26be clearly embedded in your mind. But
- 1:29if you have any doubts anywhere, you
- 1:31can compare it with the completed file
- 1:33and verify the formulas. Alright, let's
- 1:37get into the details. This is a
- 1:39somewhat time-consuming process, so try
- 1:42to clear some time and complete it all
- 1:45in one sitting; that way, everything
- 1:48will connect well for you. So, normally
- 1:51, why do we use Excel functions and
- 1:53formulas? Everyone knows Excel is just
- 1:56a place to maintain data. However, the
- 1:59real power of Excel lies in its ability
- 2:04to calculate things. If you just put
- 2:07data in it, there's nothing much there.
- 2:09There isn't much difference between
- 2:10this and a book if we just store
- 2:12information. But the real power of
- 2:15Excel is that it can take all the
- 2:17numbers here, do something with them,
- 2:19and generate new numbers or new values.
- 2:22For example, I have this employee data.
- 2:25If my boss comes and asks, "Okay, this
- 2:27is all fine, but we have to pay
- 2:29salaries this month, so what is the
- 2:31total amount we need to pay?" what
- 2:33happens? Currently, I have the annual
- 2:35salary in this salary column. So, if I
- 2:38select this entire salary column, it's
- 2:40in a table format. So, if we just click
- 2:43like this, the whole column gets
- 2:45selected. After doing that, I can see
- 2:48the sum here; if I look at the status
- 2:51bar, I can see 19 million 208 thousand.
- 2:54That is the total annual salary. Since
- 2:57we only need the next month's salary,
- 3:00dividing this by 12 is enough. Instead
- 3:04of doing all this manually, we can use
- 3:06Excel formulas. For example, if I want
- 3:08the total in this column, let me show
- 3:11you what the name of this table is. One
- 3:13, it is in a table, and the name of
- 3:15this table is "Staff." So, when we use
- 3:18data in a table, we use the table name
- 3:21and the column name. For example, if I
- 3:24want the total salary here, I use the
- 3:28SUM () function, with the Staff table,
- 3:31opening bracket, Salary column, and
- 3:35closing bracket. Now, if I press enter,
- 3:38I get the same value that was shown in
- 3:40the status bar. However, since we want
- 3:43the next month's figure, if I divide
- 3:46this by 12, I'll know that we need 1.6
- 3:49million dollars per month. So, by using
- 3:53these functions and formulas, we can
- 3:56understand the data in various ways and
- 3:59discover new insights from it. This is
- 4:02what these functions and formulas are
- 4:04used for. Both are synonyms; function
- 4:07and formula are the same. Generally, a
- 4:11function is like SUM, and a formula is
- 4:15created when those functions are
- 4:18combined into an expression. But, in
- 4:22general, we can use them
- 4:23interchangeably. Alright, that was a
- 4:26simple formula. Let's move on to actual
- 4:29formulas; most people who use Excel,
- 4:31even a little, already know SUM, COUNT,
- 4:33and IF functions. So, I don't want to
- 4:36waste your time explaining those; let's
- 4:38move to the next level. The next level
- 4:40is about real business problems and
- 4:42which formulas to use for them. Another
- 4:46mistake some people make is that while
- 4:48learning, they try to memorize these
- 4:50functions one after another, like SUM,
- 4:52COUNT, COUNTIF, and VLOOKUP, trying to
- 4:54keep them in their heads. That is not
- 4:57very useful. In general, to learn and
- 5:00use this well, one must know which
- 5:03function to use for a specific problem.
- 5:06So, I will follow that approach. I have
- 5:0910 problems, and as we solve these 10
- 5:12problems, we will discover all these
- 5:14functions. Let me tell you about my
- 5:17version; I am using Excel 365. If you
- 5:21use Excel 365 or Excel Online, you can
- 5:24apply everything exactly as is. If you
- 5:28are using an older version of Excel,
- 5:30you won't be able to use some of these
- 5:31functions. I will tell you what those
- 5:34are while I am working on them. So that
- 5:37you are aware. But wherever you join a
- 5:41job or work for a client at any other
- 5:44company, 99%of the time they have 365
- 5:47nowadays. So, it is better to learn on
- 5:50the latest version, which is what I
- 5:52will focus on. However, I will let you
- 5:54know in advance if something won't work
- 5:57in your older version, so you can make
- 5:59a mental note. Alright, let's go into
- 6:01each one of them. The first thing we
- 6:03will do is solve this question. This is
- 6:06about my staff; I want to know their
- 6:10total salary and headcount
- 6:13department-wise, and I want to produce
- 6:17a small report. We will learn what
- 6:20functions we need to write and how to
- 6:22write them for that. Looking at the
- 6:24data I have, there are many departments
- 6:27. How many? If I apply a filter, I can
- 6:29show you; see, there are about 10
- 6:32departments here. I need to know the
- 6:34headcount and total salary for every
- 6:37department—Legal, Marketing, Product
- 6:41Management, Research and Development.
- 6:45So, I have set up a space here where I
- 6:47will list the department names, and I
- 6:50need all this information. I will show
- 6:54you a quick, simple solution to list
- 6:56the department names, and later, I will
- 6:58also show you a formula-based approach.
- 7:01I will select this Department column.
- 7:04You can use the arrow keys and shift
- 7:07keys to select everything, then copy it
- 7:10with Ctrl + C, come here, right-click,
- 7:12and select Paste Values—meaning I
- 7:15just pasted the departments as they are
- 7:17, without any formatting; now, keeping
- 7:20it selected, I go to the Data ribbon,
- 7:23where I have a "Remove Duplicates"
- 7:25button. Let’s use this button to
- 7:28remove them. So, these features are
- 7:30available in any version of Excel. They
- 7:32have been available since 2007. I will
- 7:36leave it as is, select "Continue with
- 7:38the current selection," and click "
- 7:40Remove Duplicates" in the Department
- 7:42column, so each department will appear
- 7:44exactly once per row. Any duplicates
- 7:47will be gone. This is one method. There
- 7:50is another method too, which I will
- 7:52explain later in the video. Now that I
- 7:55have my departments with one row for
- 7:58each, I need to know the headcount and
- 8:00total salary for every department.
- 8:03Meaning, I want to count how many times
- 8:06the "Training" department appears. So,
- 8:09the function we use for this is
- 8:12COUNTIFS. Actually, in Excel, these
- 8:15functions—COUNTIF, SUMIF—all have
- 8:17two versions. One is COUNTIF, and the
- 8:20other is COUNTIFS, as you can see when
- 8:23you type it here. So, my recommendation
- 8:26when learning is to just straight away
- 8:29learn the COUNTIFS function and forget
- 8:32about COUNTIF. The difference between
- 8:36them is that one is singular and the
- 8:38other is plural. Wait, COUNTIFS allows
- 8:41us to count for a single condition.
- 8:44With COUNTIFS, you can count using
- 8:46multiple conditions, like the
- 8:47department must be this, the gender
- 8:49must be female, and the salary must be
- 8:52over 50,000. So, that is the only
- 8:55difference between them; generally,
- 8:57whether for one or multiple, I use
- 8:59COUNTIFS, and you should learn that too
- 9:01; forget the original one, it is not
- 9:04even necessary. So, I type COUNTIFS and
- 9:07specify what the range and criteria are
- 9:10. For the range, I will come here to
- 9:13the data and select this Department
- 9:15column; when data is in a table, you
- 9:17can manually drag to select everything,
- 9:20or you can just click on the header to
- 9:22select the entire column. Now, as you
- 9:26can see with COUNTIFS Staff [Department
- 9:28], when the data is in a table, instead
- 9:31of going to the worksheet to select it,
- 9:33I just type it out myself because it is
- 9:36faster. But initially, let’s do it
- 9:38this way. Comma, so I want to know how
- 9:42many people are in the Training
- 9:44department. So, I will select Training
- 9:47here. This means '1! B3', asking what
- 9:51the value in cell B3 of Worksheet 1 is.
- 9:56If you close this and hit enter, it
- 9:58tells me there are 24 people in the
- 10:01Training department. Let me explain how
- 10:04this works; if you come here and filter
- 10:07by Training, it tells you exactly how
- 10:11many rows are there. Let me show you
- 10:14how many rows are here. If you see here
- 10:17, it says 24 of 260 records, right?
- 10:21That is the same 24 it shows there. So,
- 10:25this is the COUNTIFS function; I can
- 10:27use it to count one condition or
- 10:29multiple conditions, and now that we
- 10:31have this 24, we need to know the
- 10:33counts for everyone else too. For this,
- 10:37we can drag the formula down like this.
- 10:39There is another trick: instead of
- 10:41dragging manually, since you need as
- 10:43many as there are here, just go to the
- 10:45corner and double-click when the mouse
- 10:47pointer turns into a plus sign, and it
- 10:49will automatically fill to the bottom.
- 10:51I will tell you a third technique,
- 10:53which is really powerful; actually,
- 10:55this is what I use these days. That is,
- 10:59if you have Excel 365, instead of
- 11:01writing a formula for each cell, since
- 11:05I have all these departments here and
- 11:08need counts for all of them, I will
- 11:11remove this and select all of them
- 11:14instead of just one; when we hit enter,
- 11:17it calculates the counts for each and
- 11:21neatly shows all the values. This
- 11:25process is called spilling; it means we
- 11:28wrote the formula here and it
- 11:30automatically spilled, or poured, all
- 11:33the values down to here. You can
- 11:36imagine pouring milk from a jug and
- 11:39having it flow down; that’s what
- 11:41spilling is. This whole thing This
- 11:47entire thing is called a dynamic array
- 11:49range. These names can be a bit
- 11:52confusing, but the meaning is simply
- 11:54that this is an array. An array is like
- 11:57a list of values that are dynamically
- 11:59generated. Again, whether we use that
- 12:01name or not, the meaning remains the
- 12:03same. So, if you understand the meaning
- 12:05, you can forget about the name. So,
- 12:08this is the best technique. The only
- 12:10problem is that it only works in Excel
- 12:13365. If you use older versions, you
- 12:15cannot use this technique; with older
- 12:16versions, you have to write it once and
- 12:18drag it manually. You might wonder, "
- 12:21Brother, why should we use this? What
- 12:22is the advantage of this?" Exactly. The
- 12:25advantage is that if the number of
- 12:28departments changes in the future (say
- 12:31there are 12 now and they become 14
- 12:33later), we can set this formula to
- 12:36automatically adjust and get the counts
- 12:39for everything. In the current setup,
- 12:43it won't work that way because the
- 12:46range is limited to B3 to B14, so if
- 12:48another department is added, it won't
- 12:51update. But with the techniques we are
- 12:54going to learn further, we can automate
- 12:56all of that. So, this reduces our
- 12:59workload. Once we create it, nothing
- 13:01needs to be changed. Whether new
- 13:04departments come or new employees join,
- 13:06everything will update automatically.
- 13:08So, that is the superpower you get when
- 13:12you use formulas. Alright, let's look
- 13:15at the total salary as well. The
- 13:16function used for this is called SUMIFS
- 13:18(). This means we want to see the total
- 13:22salary in each department. What did we
- 13:24do earlier? We counted how many people
- 13:26were in each department. Now we are
- 13:28summing, so the function changes from
- 13:30COUNTIFS to SUMIFS. That is the
- 13:32difference. In this SUMIFS function, we
- 13:34first have to state what we are going
- 13:36to sum. For this, we use the Staff
- 13:38table, open bracket, and the Salary
- 13:40column; again, you can type it or
- 13:42select it. I generally prefer typing it
- 13:44, as it is faster. We need the Salary
- 13:47column, and for the Staff table
- 13:50department, it must match that
- 13:53department. Instead of just one, if we
- 13:56select all of them like this, I will
- 13:59get the total for everyone here; I
- 14:01think I closed an extra bracket. Their
- 14:04data will appear, and we can clearly
- 14:07understand everything. So, what is the
- 14:10headcount, what is the total, and what
- 14:12would the average be for each
- 14:14department? Try this as a small
- 14:16homework task; I will tell you my
- 14:18solution in a couple of seconds, so
- 14:20pause here and try it yourself. For the
- 14:24average, there is a similar function
- 14:27called AVERAGEIFS; it asks what needs
- 14:29to be averaged and what the condition
- 14:32is. We can add multiple conditions;
- 14:36again, I want the average salary from
- 14:39the Staff table salary column where the
- 14:42Staff department matches this list of
- 14:44departments. So, after closing the
- 14:48bracket and hitting enter, I get the
- 14:50average for each department. If you try
- 14:53this and get the values but not the
- 14:55formatting, you should select and apply
- 14:57the currency formatting beforehand.
- 15:00This is because these dynamic formulas
- 15:02only calculate the value and do not
- 15:04handle currency formatting. I already
- 15:08set the formatting in advance, so it
- 15:09appeared directly with the formatting.
- 15:12So, this is the first business problem;
- 15:15I hope this problem helped you
- 15:17understand how to use COUNTIFS, SUMIFS,
- 15:20and AVERAGEIFS. Using these functions,
- 15:22we can answer any business questions
- 15:25like these. For example, how many
- 15:28people joined last month? Or, if you
- 15:31are running a school, how many students
- 15:33have not paid the fee for this term yet
- 15:35? Or, what is the total fee we
- 15:38collected in the last three days?
- 15:40Alternatively, if you are organizing an
- 15:43event, how many people bought a ticket
- 15:45but did not show up? We can use these
- 15:48formulas to answer all such questions.
- 15:51So, these three are very powerful. But
- 15:55the good thing is that all three follow
- 15:57the same pattern; if you understand one
- 15:58, you understand all three. So easy. In
- 16:01fact, it is not just these three; there
- 16:02are a few more. I will introduce those
- 16:04to you later. Alright, let's move on to
- 16:07the next one. Before we move to the
- 16:09next part, you have a small homework
- 16:11challenge. That is, calculate the
- 16:14permanent headcount for each department
- 16:17. Now, let's look at the data; let me
- 16:21clear the data here. There is a column
- 16:24called Employee Type. In this, I have
- 16:26three types of employees: Fixed Term,
- 16:28Permanent, and Temporary. I need to
- 16:31know how many there are in this batch,
- 16:33the permanent ones. So, calculate how
- 16:36many permanent employees are in each
- 16:39department, and keep it in an adjacent
- 16:42column here. This is your homework.
- 16:45Alright, let’s move on to the next
- 16:47concept. The next one is, all employees
- 16:51with more than 100,000 salary. My
- 16:55intention here is to see who earns a
- 16:58higher salary in this data. So, looking
- 17:02at this filter, there are some people
- 17:04here who earn more than 100,000. I want
- 17:08to know who they are, where they are,
- 17:11their names, their IDs, and their
- 17:13departments; that is what I need. This
- 17:17can be solved in two ways. One is the
- 17:20manual method, where we apply filters
- 17:23here, select Number Filters, choose
- 17:25Greater Than, and type 100,000. Among
- 17:29those with more than 100,000...There
- 17:35are 47 people, their list will appear
- 17:38here, and I can view those 47 people. I
- 17:41called this a manual method because to
- 17:43do this, we have to apply the filter
- 17:46manually. So, once I set it to 100, for
- 17:49example, if I want 110, I have to
- 17:51change it again. Or if I want 95, I
- 17:54have to change it. So, there is a
- 17:58limitation with this technique: we have
- 18:00to change it manually every time. The
- 18:04second problem is, if I want to do
- 18:06something with the data I filtered—
- 18:08like, what is the total salary of those
- 18:11earning more than 100,000? What is
- 18:14their average? Or, based on their
- 18:17joining date, who are the five oldest
- 18:19employees? If I want to do any further
- 18:22analysis like this, I cannot do it. It
- 18:25would become even more manual work. So,
- 18:27that is why we use certain formulas.
- 18:29The concept we are introducing here is
- 18:31the FILTER function. Unfortunately,
- 18:34this FILTER function is available only
- 18:37in Excel 365 and Excel on the Web. In
- 18:40older versions, there isn't even a
- 18:42comparable formula for this. There are
- 18:45some long-winded techniques, but I
- 18:47wouldn't even recommend them. So, learn
- 18:51this FILTER function. If you don't have
- 18:55Excel 365 in your current work
- 18:57environment or at home, you can simply
- 19:00go to your browser and type "excel.new"
- 19:03; you will be able to open an Excel
- 19:06file. It will ask for your Microsoft
- 19:11user ID and password, or you can sign
- 19:13up for a new account if you don't have
- 19:16one. Since I have one, I will log in to
- 19:20it. This will create a brand new Excel
- 19:24file for you in the browser. You can
- 19:28copy the data I provided and then
- 19:30freely use the FILTER function here.
- 19:33The FILTER function works perfectly
- 19:35here, there are no issues. It is free.
- 19:39As for me, I have a paid account, so I
- 19:41logged into it; if you have a free
- 19:42account, you can log into that. Alright
- 19:45, I will show you how to use this
- 19:47FILTER function here. So, for this, I
- 19:49have set up a grid-like structure here.
- 19:52All employees with more than 100k
- 19:54salary, I will remove their headers too
- 19:56. So, I want their details here and
- 20:00their headers here. So, for the details
- 20:02, we use the FILTER function like this.
- 20:04FILTER staff table, comma, staff table
- 20:11salary greater than 100k. That’s it;
- 20:18there are two options for the filter
- 20:21here. One is what to filter, and the
- 20:23other is what the condition is. Here,
- 20:26we are providing one condition of
- 20:28greater than 100,000. I will explain to
- 20:31you later what to do if there are
- 20:34multiple conditions. So now, if we hit
- 20:36enter on this, those 47 people will
- 20:39appear here. If I select them like this
- 20:43, you can see it shows me the count is
- 20:4747. So, the FILTER function filters the
- 20:50data and automatically creates a spill
- 20:53range. Earlier, the spill went
- 20:57downwards. Since there are multiple
- 21:00columns here, it spilled this way and
- 21:02that way. It creates a two-dimensional
- 21:07range for us. The problem with this
- 21:11spill function is that the filter, or
- 21:13any spill function, only brings the
- 21:15data, it doesn't do formatting. If you
- 21:18look now, the formatting, the currency
- 21:19formatting, has not been applied. These
- 21:21numbers also came with extra details,
- 21:23everything appeared, right? So that is
- 21:25a limitation. With the FILTER function,
- 21:28or any dynamic function, you have to do
- 21:30the formatting manually. So, generally,
- 21:33what I do is select an extra range
- 21:35beforehand and set my formats so that
- 21:38it looks good. Another problem is that
- 21:41it only gives data and not the headings
- 21:44; we have to fetch the headings from
- 21:47the table again. So, a trick to get the
- 21:50headings is to open a bracket in our
- 21:53staff table, and if you look there, you
- 21:55can select every column, and if you go
- 21:58all the way down, there is an option
- 22:00called headers. This directly gives us
- 22:04the headers of that table. So, if I say
- 22:07hash headers here and then hit enter,
- 22:10all those headers, since they are
- 22:13horizontal, will spill horizontally and
- 22:16all the headers will appear neatly. So,
- 22:19this is for all employees with more
- 22:22than 100k. Now let's go to the next
- 22:25problem. The next problem is all
- 22:28employees with 100k, but I only want a
- 22:31few columns. So, we are in the same
- 22:33place for this too. I want those with
- 22:36more than 100k, but I don't need every
- 22:38column of theirs. All that is useless
- 22:41to me. For the analysis I am about to
- 22:43do, what I need is this employee ID
- 22:46column, first name, last name, and I
- 22:49don't need gender, department, salary.
- 22:51So, the columns I'm looking at are
- 22:55these: 1, 2, 3, 5, 6. If you look here,
- 22:58suppose I've numbered these columns:
- 23:02this is 1, this is 2, 3, 4, 5, and 6. I
- 23:05don't need column 4; I need these three
- 23:09, and these two, and nothing else. So,
- 23:12how do we do that? This is also very
- 23:14simple. If we take the filter and send
- 23:17that output to another function, that
- 23:19function will give us only these
- 23:21columns. Let me show you what that
- 23:23function is. First, we write the filter
- 23:25just as it is. Staff, Staff salary
- 23:29greater than 100k. Instead of typing
- 23:33100k manually here like this, what we
- 23:37can do is put a pointer in this place.
- 23:40We can say "greater than this cell"
- 23:43here. Now, since this cell is F67, I
- 23:45can say "greater than F67". The result
- 23:48is that if I change my mind and want to
- 23:51go for 110 instead of 100, I just need
- 23:53to change this number; I don't need to
- 23:55edit the formula. So, instead of
- 23:57hard-coding it manually, I will put "
- 23:59greater than F67" here. As of now, if I
- 24:02just hit enter, I will get all the
- 24:04columns. What is our problem? We don't
- 24:06want all the columns, we only want 1, 2
- 24:08, 3, 5, 6. So, I will send this filter
- 24:11function to another function. Another
- 24:13function that is only available in
- 24:16Excel 365 is called CHOOSECOLS. What
- 24:19this CHOOSECOLS does is take an output
- 24:22and present only specific columns from
- 24:24it. Those columns are 1, 2, 3, 5, 6. We
- 24:30can type the list of columns we want.
- 24:33If I hit enter, I get only these
- 24:36specific columns from that data. So,
- 24:40this is one concept. In this concept,
- 24:43the thing you need to understand is
- 24:45nesting. What nesting means is taking
- 24:48one function and sending it to another
- 24:50function, this CHOOSECOLS function. So,
- 24:54we are putting a function inside
- 24:55another function, like a samosa. This
- 24:57is what is called nesting. This nesting
- 24:59can be done in one layer or in multiple
- 25:03layers. Meaning, you can put one inside
- 25:06another, then another inside that, and
- 25:08keep going on like that. I don't have
- 25:11any food examples for this, but I'll
- 25:13leave it to your imagination. So that's
- 25:16the nesting concept. And how do we
- 25:18bring the headers? The same logic
- 25:21applies to headers; using CHOOSECOLS on
- 25:23the staff table headers 1, 2, 3, 5, 6
- 25:25means taking all those headers and
- 25:27telling it to give me only these
- 25:28headers, and it will bring the headers
- 25:30and provide them like this. So that
- 25:34covers all employees with a salary
- 25:36greater than 100k. Now let's move to
- 25:38the third one. The second and third are
- 25:40somewhat connected, so I am putting
- 25:42both two and three in the same
- 25:44worksheet. That is: all female
- 25:46employees with a salary more than 100k.
- 25:49So here we are setting two conditions:
- 25:51one, the salary must be more than 100k,
- 25:53and second, their gender must be female
- 25:56. So, here after I do this filter, I
- 26:00need to go into the gender and select
- 26:05female as well; I am looking for these
- 26:0918 people. How do we do this? Try it as
- 26:14homework, and I will explain. So here,
- 26:19filter, filter, staff table, staff
- 26:22table salary. Greater than, and I will
- 26:28point to this. So that is one condition
- 26:31, but we have two conditions, right? It
- 26:34must be female as well. In that case,
- 26:37what we do is put an open bracket
- 26:39around the first condition, add
- 26:41brackets, and before writing the second
- 26:43condition, we multiply using a star.
- 26:48Now, the second condition is staff
- 26:51table gender equals female. Like this,
- 26:57you can write as many conditions as you
- 26:59want by adding a star. So, that is how
- 27:02you do multiple conditions for filter.
- 27:06One difference here is, if you look at
- 27:08COUNTIFS or SUMIFS, when you have to
- 27:10write multiple conditions, we just
- 27:13write them separated by commas. But in
- 27:15filter, we multiply them, using a star.
- 27:19What actually happens internally is, if
- 27:21you look at this bit by bit, when I
- 27:24point here, you see the result showing
- 27:26up above—false, false, false, true.
- 27:30That is, it checks every row; if their
- 27:32salary is more than 100k, it becomes
- 27:33true, otherwise false. The second part
- 27:36does the same thing. But for gender,
- 27:39when you multiply both, these trues and
- 27:42falses cancel out, and where both are
- 27:45true, it results in one. Everything
- 27:47else, regardless of the combination—
- 27:49whether one is true and one false, or
- 27:51both false—becomes zero. And finally,
- 27:53what the filter does is ignore all
- 27:55zeros and brings only those with one.
- 27:57So that is how it works internally. And
- 28:01when we hit enter now, we get the list
- 28:04of female employees earning more than
- 28:06100k. So, as you can see, I got a
- 28:10shorter list, those same 18 people. Now
- 28:13, I don't actually need all the data
- 28:15for these 18 people; I only want the
- 28:17first six columns from them. This time,
- 28:21we also need gender, but we need only
- 28:23the first six columns. How to do this?
- 28:26We can use CHOOSECOLS here, but I will
- 28:28show you another technique. That is,
- 28:31since the ones we need are continuous
- 28:34from one to six, instead of writing
- 28:37inside the filter as "staff," we write
- 28:41"staff," open square bracket, open
- 28:44another bracket [select the first
- 28:47column we need, Employee ID, close
- 28:50bracket, then put a colon [open bracket
- 28:53, last column, Salary] column], and
- 28:56then close all the brackets. I mean,
- 29:00what we are saying is: I don't want the
- 29:03entire staff table, just filter and
- 29:06show the columns from Employee ID to
- 29:09Salary from the staff table. These are
- 29:12the conditions, these are the filtering
- 29:15conditions, and this is the output data
- 29:17. If you write it like that and hit
- 29:20enter, we get those first six columns.
- 29:23So, how do we handle headers? For
- 29:26headers, you just type equals, go into
- 29:29the data, and select the first six
- 29:32headers, and they will appear. The
- 29:36formula actually looks like this: after
- 29:38writing hash (#) headers and adding a
- 29:40comma, selecting from Employee ID to
- 29:43Salary asks it to give only those
- 29:44column headers. So those headers will
- 29:47appear, and you can apply formatting to
- 29:49them. So, that is number three. Let's
- 29:52review the concepts we've looked at; we
- 29:54learned about filters and how to use
- 29:56the asterisk when applying multiple
- 29:58conditions. There is a small challenge
- 30:02for you: it shouldn't stop at just "
- 30:05Female"—it must be females who earn
- 30:07more than 100, and their joining date
- 30:10must be 2020 or later; if you look at
- 30:13the data, I also have a start date here
- 30:16. In this start date, for example, if
- 30:20you look at this person, they joined in
- 30:222018, this one in 2019, and these two
- 30:25also in 2018. I don't need these people
- 30:27. Those who joined on or after January
- 30:311, 2020, aren't needed either. I only
- 30:34want the details of those specific
- 30:36people. So, how would you do this?
- 30:38Think about it, modify the filter
- 30:40function, and try it out here. This
- 30:44will be a small challenge as homework;
- 30:46it will help you think a bit about how
- 30:48to write conditions. Alright, let's
- 30:51move on to the next concept. Our next
- 30:53example is about lowest, highest, and
- 30:55top five salary values, and in this
- 30:57process, we are going to learn those
- 30:59functions again. So first, suppose you
- 31:02want to find out whose lowest salary it
- 31:05is—no, not whose, but what it is—
- 31:07for this, we can use the MIN function.
- 31:10MIN of staff table, salary column, and
- 31:16I get 28,160. We can also do the same
- 31:21for minimum female or male salary; I
- 31:24will tell you that in a minute. First,
- 31:28let's look at the highest; for the
- 31:31highest, we use MAX of staff table,
- 31:34salary column. It comes to 120,000. So
- 31:40that is minimum and maximum. If you
- 31:44want to see what the top five salaries
- 31:47are, there is a function called LARGE
- 31:50for this. LARGE, staff table, salary
- 31:54column, and the next option is K. This
- 31:57K means if we put 1, the highest person
- 32:01comes; if we put 2, the second person;
- 32:04if 3, the third person, and so on. I
- 32:08will type it here by putting 1. For the
- 32:12next person, we copy and paste the same
- 32:15formula and put 2 in place of 1. The
- 32:20reason 120,000 appeared for both 1 and
- 32:222 is that we have two people earning
- 32:23120,000, so that's why it happened; it
- 32:25will change for 3. Generally, instead
- 32:29of manually typing 1, 2, 3, 4, 5 like
- 32:32this, another technique is that I have
- 32:34a hidden column here. I keep 1, 2, 3, 4
- 32:38, 5 typed in a range beforehand, and
- 32:42now I point to that instead of using 1.
- 32:46Then, if I drag this, it will fetch
- 32:49that number for every row and show me
- 32:52what that salary value is. So, that is
- 32:56how we can use the LARGE function. The
- 32:58purpose of this 'four five' is just to
- 33:01explain things here, so once I set it
- 33:03up, I will hide this column. So that my
- 33:06output worksheet looks a bit cleaner.
- 33:09So that's for the top five salaries.
- 33:12Now, let's see how to do this whole
- 33:14thing for male and female. For male,
- 33:17instead of MIN, there is a function
- 33:20called MINIFS. This is a newer function
- 33:23, so if your version of Excel is very
- 33:26old, like 2007, 10, or 13, it won't be
- 33:29there. This function seems to have been
- 33:31available since 2016. So, it works just
- 33:33like SUMIF and COUNTIF. First, we
- 33:37select the range from which we need the
- 33:40minimum, which is the Staff table
- 33:42salary column, and then the Staff table
- 33:45gender column, and the gender listed
- 33:47here. So the minimum is 28,000 for male
- 33:51, but if I change this to female, the
- 33:54female minimum is 28,305. Whoever has
- 33:57the lowest salary is male, so that's
- 33:59why it showed that for male, and a
- 34:01different one for female. For the
- 34:05highest, there is a function called
- 34:09MAXIFS: MAXIFS, Staff table salary
- 34:12column, Staff table gender column. So
- 34:18male and female are both 120,000. Since
- 34:21there are two people at 120,000, it
- 34:23seems there's one male and one female.
- 34:26So, try to do this top five challenge
- 34:29as your homework; I will explain the
- 34:33next technique to you. There are
- 34:36actually two ways to do this top five.
- 34:38One is by using the LARGE function with
- 34:42a hidden column. But in newer versions
- 34:45of Excel, they have added new functions
- 34:48. So, in Excel 365, there is a function
- 34:51called SORT and a function called TAKE.
- 34:54I will show you how to solve this
- 34:56problem by combining these two. I will
- 34:58also explain how to handle the male and
- 35:00female part there, but figure out how
- 35:02to do it yourself as well. So, first,
- 35:06if I want to see the top five, in Excel
- 35:09365, I have a function called SORT.
- 35:12What this SORT function does is, if we
- 35:14provide data, for example, if I give
- 35:17SORT Staff Salary, it sorts the salary
- 35:20values. By default, it sorts in
- 35:22ascending order, meaning the lowest
- 35:24salary is here, and the highest salary
- 35:27goes to the bottom. So you see, it goes
- 35:302860, 305, 329, and all the way down at
- 35:35the bottom, I'll have my 120,000. So
- 35:39this SORT function sorts the data and
- 35:42returns it. This SORT function has an
- 35:45option, which is the sort order, the
- 35:46third parameter. When there's only one
- 35:49column, we don't need to specify the
- 35:51sort index; if there are multiple
- 35:52columns, we must specify the index. So,
- 35:55the sort order is ascending by default;
- 35:57if I put minus one, it will sort in
- 35:58descending order. If I put minus one,
- 36:01my highest salary comes to the top, and
- 36:05all the other salaries go down. Like
- 36:08this, now after sorting, I don't need
- 36:10everyone's values. I need to tell it to
- 36:12sort and give me the top five people.
- 36:15For this, there is a function called
- 36:17TAKE. So TAKE, this TAKE function takes
- 36:20a list or a range and gives you the
- 36:23number of rows you need from the top.
- 36:27So, for the TAKE function, whatever the
- 36:29sort is, if we say five, it will give
- 36:31the first five rows after sorting. So,
- 36:33TAKE 5 will give me the exact same
- 36:36output as the LARGE function. So, this
- 36:39is one technique. In this, we are using
- 36:42SORT and TAKE directly instead of using
- 36:46LARGE (). Using this, I can also
- 36:48extract data for male and female
- 36:49categories separately. To do this,
- 36:50first, we perform a filter. If we
- 36:54filter the staff table, salary column,
- 36:58where staff gender is equal to male,
- 37:03stopping there would give me only male
- 37:07salaries. If I sort these male salaries
- 37:12in their original order—meaning we
- 37:15are doing it ascending—they will go
- 37:17from lowest to highest. If I put a
- 37:22minus sign, I will get the male
- 37:24salaries in descending order. It has
- 37:27worked so far, right? Is it clear? We
- 37:29filtered it, sorted the filter, and now
- 37:32I have sent this whole thing into the
- 37:34TAKE function. TAKE 5 means I will get
- 37:39those top five male salaries. Let me
- 37:44show you again; this involves triple
- 37:48nesting. First, we filtered; with that,
- 37:52I filtered the data down to males, so
- 37:55only male salaries come up. Then, I
- 37:58sorted those male salaries. So, this is
- 38:01the next level. After sorting in
- 38:04descending order, the final level is
- 38:06taking five people from that list, the
- 38:09top five. So, by nesting TAKE like this
- 38:12—one inside another—we get the
- 38:15final desired result. This concept can
- 38:18be a bit confusing when you are
- 38:20initially learning and practicing Excel
- 38:23. Once you get used to thinking this
- 38:26way, your work becomes much simpler. So
- 38:29, practice this kind of nesting with
- 38:31different problems; I did it for males,
- 38:34you try it for females. Also, try to
- 38:36solve this without using SORT and TAKE,
- 38:39so that you can understand some
- 38:41additional concepts. I will show you a
- 38:44final bonus trick. That is, what if you
- 38:47change your mind? You don't want five,
- 38:50you want to see 10, 12, or eight. What
- 38:53then? In this whole formula, any
- 38:56numbers used can be parameterized. So,
- 39:00for example, instead of the number five
- 39:04here, I can point to this cell, I12.
- 39:09Then, it will read that five from there
- 39:12. So, I go here, remove the five, and
- 39:16select cell I12; my output won't change
- 39:19. But now, if I put eight in place of
- 39:22five, you can see that it automatically
- 39:24shows the salaries of eight people.
- 39:26Similarly, if I put three, the top
- 39:28three will appear. If I put 12, the top
- 39:2912 will appear. So, we are making the
- 39:33TAKE function dynamic. Likewise, we can
- 39:37use a parameter to control these
- 39:39outputs using a single cell. Alright,
- 39:43let's move to the next concept. The
- 39:46fifth concept is to list all
- 39:49departments and also count how many
- 39:52there are. So, if we remember the first
- 39:55one, in that, we calculated the
- 39:57headcount and average salary for each
- 39:59department. What do we do for this? For
- 40:02the actual department list, we went
- 40:05into the data, copied this department
- 40:07column, pasted it here, and then
- 40:10removed the duplicates from it. Suppose
- 40:14I want a list of all existing
- 40:17departments directly without this
- 40:19nonsense; for example, if I want to
- 40:22bring everything, I can directly type "
- 40:25Staff Table Department" here, and it
- 40:28will list all 120, 260 departments
- 40:31exactly as they are. I don't want all
- 40:34of them; I need to remove these
- 40:36duplicates from this list. For example,
- 40:38if this Business Development is
- 40:39repeated twice, I need to remove them.
- 40:41Here is another one that should be gone
- 40:43. Similarly, this Legal department is
- 40:44repeating, it should be removed. So, if
- 40:46I want to remove the repetitions and
- 40:48get each one individually, there is a
- 40:50function called UNIQUE for this "Staff
- 40:52Department." This is a new function
- 40:55available only in Excel 365. If I pass
- 40:59it to this, it removes the duplicates
- 41:01from my departments, and the remaining
- 41:04data comes exactly as it is. So, this
- 41:07is the UNIQUE function. You can use the
- 41:09UNIQUE function in various ways. It is
- 41:11very useful, and it will give you the
- 41:14list. Suppose you want this list in
- 41:17alphabetical order, I can send the
- 41:19UNIQUE function into SORT. SORT UNIQUE
- 41:22means Accounting and Business
- 41:23Development will come on top, and
- 41:25Support and Training will come below.
- 41:28So, this is UNIQUE. After doing this,
- 41:31you might think, "Okay, this is fine,
- 41:33but I also want to count how many
- 41:35departments are actually there," for
- 41:36which we can use the COUNTA () function
- 41:38. The COUNT function only counts
- 41:41numbers. Since we have text values here
- 41:44, we use COUNTA. COUNTA means it counts
- 41:46everything, all of it. So, I want to
- 41:49count how many values are here using
- 41:53COUNTA. The problem here is that we
- 41:57know where it begins. Our values begin
- 42:00with cell B4, but we don't know how
- 42:03long it will be. We will have as many
- 42:06values as there are departments because
- 42:08this is all coming from the UNIQUE and
- 42:10SORT functions. So, we know the
- 42:12beginning but not the end. There is a
- 42:15special operator for this. Whenever you
- 42:18have a formula that spills and creates
- 42:21values, if you put B4 followed by a
- 42:24hash (#), it will grab the entire spill
- 42:27range. Whatever amount is here, the
- 42:30count will be the same. So, COUNTA of
- 42:32B4 hash will give me 12. Because
- 42:35currently, we have 12 departments.
- 42:38Generally, in these organizations,
- 42:40every four to six months, they do some
- 42:42internal reorg and keep changing
- 42:44everything. So, for example, let's
- 42:47assume a reorg happened in our
- 42:49fictional setup as well. Let's assume
- 42:51some of these Business Development
- 42:52people were moved to Business Growth.
- 42:54So, I'm renaming this department to "
- 42:57Growth." If you remember, this "Growth"
- 42:59category wasn't there before; it's a
- 43:01new addition. Meaning, we have created
- 43:04a new department. Now let's see, if I
- 43:07go here, "Business Growth" has
- 43:10automatically appeared, see? Not only
- 43:13that, the count has also updated since
- 43:17our list is now 13. So, we don't need
- 43:20to touch our formula ever again. Once
- 43:22set, it updates automatically, whether
- 43:25it's sorting or counts. That's why
- 43:28using these new features saves us a lot
- 43:31of time. So, if you have Excel 365,
- 43:35definitely use these techniques. Like
- 43:38using the hash symbol, as well as SORT
- 43:40and UNIQUE functions. I have a small
- 43:42homework assignment for you here. What
- 43:44if I don't want all these departments
- 43:47in individual cells, but rather in a
- 43:50single cell separated by commas? How
- 43:54would you do that? Think about it and
- 43:57try to solve it. You will discover how
- 44:00to do that as we move further through
- 44:03our setup. But pause here for a moment
- 44:06and try it yourself. Alright, let's
- 44:08move on to the next concept. That is
- 44:10lookup functions. These are some of the
- 44:13most important functions in Excel. In
- 44:16fact, my first video on my Telugu
- 44:19channel was also about lookups. Since I
- 44:22have already introduced lookups there,
- 44:24I won't bore you with all those details
- 44:26again. I will explain what they are and
- 44:30give simple examples; if you want, you
- 44:31can watch that video to get some
- 44:33context. I think it uses slightly
- 44:35different data. But for example, I want
- 44:37to perform a lookup. What is it? I have
- 44:39this much data. I don't need all of it.
- 44:42For instance, I want to find the salary
- 44:46of our favorite Subbarao. How? There
- 44:50are multiple methods for this. One is
- 44:53to go here, apply a filter, and search
- 44:55for "Subbarao." There is his row:
- 44:59salary 10,675, working in Product
- 45:02Management, joined on this date. I get
- 45:05all this information clearly, and he is
- 45:07working in Hyderabad. But this is a
- 45:09manual method. This isn't efficient
- 45:11because I need Subbarao now, but
- 45:13tomorrow I might need Rangarao. So,
- 45:15instead of manual work, how do we use
- 45:17formulas to lookup? There are various
- 45:20lookup functions. The main functions
- 45:23are VLOOKUP and XLOOKUP, so I will show
- 45:26you how to use both. For this whole
- 45:29thing, as a simple example, I want to
- 45:31search using an employee ID. I put
- 45:34PR03886 here, but let's actually use
- 45:37our Subbarao's ID. So, that's
- 45:42Subbarao's ID; I will paste it here,
- 45:45and I want that employee's first name,
- 45:48last name, department, and salary. I
- 45:53will explain how to do it with VLOOKUP,
- 45:55how to do it with XLOOKUP, and what the
- 45:57advantage of each function is. After
- 46:00that, I will also explain INDEX MATCH.
- 46:03So, how does the VLOOKUP function work?
- 46:08VLOOKUP asks what you are looking for
- 46:11—what is the lookup value? Where are
- 46:16you looking? Since it is in a table, I
- 46:20will come here and directly type "staff
- 46:24." Once you have looked it up, what is
- 46:28the output column number you need? If
- 46:31we look here, Employee ID is the first
- 46:34column, so VLOOKUP always works based
- 46:36on the first column. So, since the
- 46:39staff data is here, it will look up
- 46:41based on the Employee ID. After looking
- 46:43it up, which column do you need? This
- 46:46is one, we need the first name, so we
- 46:48need two. So, we type "2" here. Next,
- 46:55the last part is also important: you
- 46:57need an exact match, so for this last
- 46:59argument, we set it to "false" almost
- 47:0199%of the time. Once I press enter here
- 47:05, I get the first name of the person
- 47:07with that ID. So, that is VLOOKUP. Let
- 47:11me explain it to you visually once.
- 47:13VLOOKUP acts like a finger. This finger
- 47:18keeps scanning along like this. It goes
- 47:21until it finally finds where Subbarao
- 47:24is; once it reaches Subbarao, our index
- 47:27finger stops here, moves to the second
- 47:30column, and returns that value. So, a
- 47:34simple analogy is that VLOOKUP moves
- 47:37along like a finger. It goes to the
- 47:41value, takes it, and returns that value
- 47:45to us. So, that is VLOOKUP. If I want
- 47:48the last name, I use the same formula,
- 47:52copy and paste it as is, and replace "2
- 47:56" with "3"; the last name will appear.
- 48:00Generally, instead of changing it every
- 48:04time to get 2, 3, or 4, we can maintain
- 48:08numbers on the side and point directly
- 48:11to that cell. Because of this, if I
- 48:15drag it directly, I will get the others
- 48:17as well. Why didn't it work? It didn't
- 48:21work because before dragging this
- 48:24formula, we left cell C4 as relative.
- 48:28That's why when I dragged it, C4 became
- 48:30C5. So, I will lock C4. To lock it as $
- 48:35C $ 4, select C4 and press the F4
- 48:38function key, which will add the dollar
- 48:41signs. Now, if I drag this down, I get
- 48:45Subbarao's department, his salary, and
- 48:48everything else. This VLOOKUP is a very
- 48:52powerful function. Everyone learning
- 48:56Excel learns VLOOKUP first without fail
- 48:58, and it's a very useful, practical
- 49:00function because most of the time while
- 49:03working with data, questions like "
- 49:05where is this,""what is the
- 49:06corresponding value," and "what is this
- 49:09or that" keep coming up, though there
- 49:11are some problems with VLOOKUP. So, one
- 49:14problem is that you must set it to "
- 49:17false." If you accidentally forget to
- 49:20do that, you will end up with some
- 49:22garbage value. For example, that ID
- 49:24doesn't belong to Vasu, so why is it
- 49:27saying Vasu? Because we forgot to enter
- 49:29"FALSE". If you forget to enter "FALSE"
- 49:31, it does an approximate match and
- 49:33returns some nonsense. That is why you
- 49:35get the wrong result. So you must
- 49:37remember this; if you forget it, you
- 49:38will get the wrong result. That is one
- 49:40problem. The second problem is, if
- 49:44VLOOKUP cannot find the value, it shows
- 49:47an error. So, for example, instead of
- 49:514058, let's say 4057; that person
- 49:53doesn't exist in our data, so I
- 49:55automatically get these errors. That is
- 49:58another problem, and it doesn't end
- 50:00there; there are a few more problems.
- 50:03Another problem is that we can search
- 50:06by ID to get the name, but for example,
- 50:08suppose I have the last name. I know
- 50:12his last name, which is Tuxwell; if I
- 50:14want to know his ID, department, or
- 50:16salary, I cannot use VLOOKUP. Because
- 50:20the syntax of VLOOKUP is, as you can
- 50:22see here, we only specify what value we
- 50:28are looking for, which table, and which
- 50:31column we want. So, whatever table I
- 50:34specify here, it will only look in the
- 50:37first column of that table. Because the
- 50:40first column in our staff table is
- 50:42Employee ID, it can only search based
- 50:44on Employee ID. If I ask it to search
- 50:47in this middle Last Name column in the
- 50:49staff table, find Tuxwell, and then go
- 50:51to the other side to bring the Employee
- 50:53ID, it cannot do that; it will get
- 50:55confused. So that is one more
- 50:58limitation of VLOOKUP. There are some
- 51:01more limitations, but basically, these
- 51:03are the main ones. To fix all these and
- 51:06add some additional power, Microsoft
- 51:08created XLOOKUP. This XLOOKUP is a
- 51:11newer function; it is available in
- 51:14Excel 365 and also in the Excel 2021
- 51:16version. It will not be available in
- 51:18any older version than that. Also, in
- 51:20Excel Online, as I showed you earlier
- 51:22with Excel.new, where you can practice,
- 51:24it is available there too. So, if you
- 51:27are learning Excel newly, my suggestion
- 51:29is to learn XLOOKUP directly; don't
- 51:32waste your time with VLOOKUP and use
- 51:34this instead. But sometimes, even if we
- 51:38have the new version, the client we
- 51:40deal with or some colleagues in the
- 51:42office might have an older version.
- 51:45That is why it is important to know
- 51:48VLOOKUP. But for those learning today,
- 51:50it is better to start their journey
- 51:52directly with XLOOKUP. So, I will show
- 51:55you how to solve this exact problem
- 51:57with XLOOKUP, and then I will also tell
- 51:59you how to solve these other problems.
- 52:01The syntax of XLOOKUP is somewhat like
- 52:06VLOOKUP, but there is a slight change.
- 52:10Lookup value is the same, lookup array
- 52:13means where to search; so where to
- 52:16search? Search in the staff table's
- 52:20Employee ID column. After searching,
- 52:23what should it return? Provide the
- 52:25staff table's First Name column. This
- 52:29doesn't have that "FALSE" nonsense; by
- 52:31default, XLOOKUP always does an exact
- 52:33match. And it will give it perfectly.
- 52:39For other things, like the last name, I
- 52:43will just copy this formula, paste it,
- 52:47and in place of the First Name, I will
- 52:51put Last Name. An extra slash was added
- 52:59. For the packet, okay, so the last
- 53:07name will come, for the product
- 53:10department, the department will come in
- 53:13the department place, and the salary
- 53:16will come in the salary place;
- 53:19everything will appear. So, that is
- 53:23XLOOKUP. Lookup is very simple. You
- 53:25just state what you are looking for,
- 53:27which column you are looking in, and
- 53:29which column you need. Once we tell it,
- 53:31those values come automatically.
- 53:33XLOOKUP has some more superpowers. For
- 53:36example, instead of writing these four
- 53:39columns one after another, what we can
- 53:43do is, since there are four here like 2
- 53:46, 3, and so on, I can use XLOOKUP for
- 53:49the staff table's employee ID...so,
- 53:51just perform the lookup on the employee
- 53:55ID. When returning the result, if I
- 53:58want first name, last name, let's add
- 54:01gender too, gender, department, and
- 54:03salary, all five columns in order, I
- 54:06can just say staff table, open bracket,
- 54:09from first name...Up to salary, if I
- 54:13say it like this, I am telling it to
- 54:16bring all these columns when returning
- 54:19the result, from here to here. So, if I
- 54:23enter this, I get his full record
- 54:26sideways like this. If you don't want
- 54:30it sideways and want it vertically, we
- 54:32can send this to another function,
- 54:34which is the TRANSPOSE () function.
- 54:37This TRANSPOSE function flips the data.
- 54:41By turning rows into columns and
- 54:43columns into rows, I get it like this.
- 54:46This is one technique. But, what if I
- 54:48don't need gender and only want these
- 54:50two...how can I do that? I'll leave
- 54:53that to your imagination. I have
- 54:55already told you this answer in a
- 54:57different context. So, try to imagine
- 55:00what that is, connect the dots, and
- 55:02solve it. Alright, now let's move to
- 55:04the next problem. Another advantage of
- 55:06XLOOKUP () is, what if the value is not
- 55:09there? For example, if there is a 57, I
- 55:12get an error. An error will come in
- 55:14VLOOKUP as well. But how can we solve
- 55:17the error? It means XLOOKUP has another
- 55:19optional parameter, which is 'if not
- 55:21found'. Actually, it has many
- 55:24parameters, but 'if not found' is the
- 55:27next one. So, we can return a value
- 55:30like "Not Found" if it is not found.
- 55:35Then, if it doesn't find it, it will
- 55:37say "Not Found". I will write "Not
- 55:40Found" here as well. How can we do this
- 55:45in VLOOKUP? In VLOOKUP, when such an
- 55:47error occurs, we use the "IFERROR"
- 55:49function. If I put IFERROR around
- 55:52VLOOKUP and add "Not Found", I will get
- 55:56that result too. Here, IFERROR, VLOOKUP
- 56:02, "Not Found". So, if there is an error
- 56:08, I get the "Not Found" message. If
- 56:10there is no error, meaning if that
- 56:12person exists, his details will appear.
- 56:15So, with XLOOKUP, we can mention "Not
- 56:17Found" directly, whereas with VLOOKUP,
- 56:19you have to use another function like
- 56:22IFERROR to handle that error. So that
- 56:24is another advantage. XLOOKUP has a few
- 56:28more advantages, and you will
- 56:29understand what they are in the next
- 56:31example. The next example is about how
- 56:34to go by using the last name. As I said
- 56:37, this won't work with VLOOKUP because
- 56:39the limitation of VLOOKUP is that it
- 56:40can only look to the right. Or, if it
- 56:43looks up the last name, it can only go
- 56:46in that direction, sorry, it can only
- 56:48go forward, it cannot go backward, so
- 56:51it has that limitation. But if I want
- 56:55to search by last name and get the
- 56:57employee ID, department, and salary for
- 56:59that last name, we use another
- 57:01technique called INDEX MATCH. To use
- 57:05INDEX MATCH, first we look for where
- 57:07the last name is located. If you look
- 57:11at the data here, if we search for "
- 57:13Tuxwell," it is in the third row.
- 57:15Actually, there are duplicates, you see
- 57:18there are two Tuxwells, but basically
- 57:20it's in the third row, so I will take
- 57:23the first one. For this, we use the
- 57:25MATCH function. MATCH "Tuxwell" in the
- 57:29staff table's last name column, and
- 57:32we'll set the last option to zero for
- 57:35an exact match. By default, it does
- 57:39something like "less than," so we must
- 57:40put zero. If we apply this, it will say
- 57:44that Tuxwell is in the third position.
- 57:48Basically, it counts as it goes down.
- 57:51Since Tuxwell is the third one, it says
- 57:54three. I will replace Tuxwell with
- 57:57Veeravasarapu, so we can look a bit
- 57:59further down. So, this person
- 58:05Veeravasarapu is in the 12th position.
- 58:08As we go down, it's 12. So now that I
- 58:11know it's 12, the next step is to find
- 58:14out what the employee ID is in the 12th
- 58:17position; since the last name is 12,
- 58:20the ID must also be in 12, so I need
- 58:23the ID. For this, we use the INDEX
- 58:28function; if we point the INDEX of the
- 58:31staff table's employee ID to that 12, I
- 58:35will get the employee ID of the person
- 58:38with that last name; this is a
- 58:41round-about way, we use MATCH here and
- 58:44INDEX there, we combine this into one
- 58:47formula called the INDEX MATCH formula.
- 58:52In that, instead of pointing separately
- 58:55, we can write the MATCH directly here:
- 58:59MATCH of this last name, staff table
- 59:02last name, zero. I will set this to an
- 59:05absolute reference as well. So, I get
- 59:07their details directly. I will fill the
- 59:11same formula in these two cells, let's
- 59:16do the department here and the salary
- 59:20here. So, we clearly get these three
- 59:24values for the person with that last
- 59:26name. This is called INDEX MATCH. Since
- 59:29VLOOKUP once had this limitation, INDEX
- 59:32MATCH used to be very popular. Everyone
- 59:35used to learn VLOOKUP first, then
- 59:37realize it had this problem and learn
- 59:40this as well. But after XLOOKUP arrived
- 59:42, all these problems became irrelevant.
- 59:44Because if you look at the syntax of
- 59:46XLOOKUP, you can provide three things:
- 59:48what we are looking for, where we are
- 59:50looking, and what we need. So, even if
- 59:53I have the last name, I use XLOOKUP;
- 59:57XLOOKUP, sorry, XLOOKUP, last name,
- 1:00:00where to search? Search in the staff
- 1:00:05table last name column. What do I want?
- 1:00:08After searching, I want the staff table
- 1:00:10employee ID. That's it, it will come
- 1:00:14directly, so the same formula can tell
- 1:00:17me the ID if I have the name, or the
- 1:00:19name if I have the ID. So I can use one
- 1:00:23formula for both, which is a great time
- 1:00:25saver, as I don't have to keep using
- 1:00:27different formulas repeatedly. That is
- 1:00:29why XLOOKUP is a better option to learn
- 1:00:32and understand. Once we are done with
- 1:00:36this, we can just put what we need here
- 1:00:39at the end. So, that is why not many
- 1:00:43people are using Index-Match that much
- 1:00:45anymore. But, while learning, you might
- 1:00:49have to deal with different situations,
- 1:00:53so it is important to learn both. My
- 1:00:56recommendation is to master XLOOKUP
- 1:00:59first. Here, I am giving you another
- 1:01:02small challenge, which is...print the
- 1:01:05full record of this person,
- 1:01:06Veeravasarapu, here. Also, print that
- 1:01:10record vertically as well. Again, use
- 1:01:12the techniques here and practice.
- 1:01:15That's it for VLOOKUP, Index-Match,
- 1:01:19XLOOKUP, and IF-ERROR. Alright, there
- 1:01:22is one more small bonus homework here.
- 1:01:25That is: Find all employees with a
- 1:01:29100,000 salary. So, one limitation is
- 1:01:33that whether it is VLOOKUP, Index-Match
- 1:01:35, or XLOOKUP, any of these three
- 1:01:37techniques has a limitation. That is,
- 1:01:41if you have multiple people, you will
- 1:01:43only get the first person's data. Now,
- 1:01:45if you look at this person
- 1:01:48Veeravasarapu, if I filter his data,
- 1:01:52there is only one person. But this
- 1:01:55Tuxwell is not like that. There are
- 1:01:59three Tuxwells here. Different
- 1:02:01departments, different salaries. Now,
- 1:02:04if I want the second person among these
- 1:02:07three, or if I want the third person,
- 1:02:09can I get them? I cannot. Because if I
- 1:02:12look for Tuxwell here, the first person
- 1:02:18will automatically come up. Because
- 1:02:19what do these lookup functions do? They
- 1:02:22keep searching. They search and as soon
- 1:02:25as they find it, they stop. Once found,
- 1:02:28they don't start searching again; they
- 1:02:29stop right there. So, that is why the
- 1:02:32first person PR 013 this number came up
- 1:02:36. That salary comes up, 189,696; if we
- 1:02:38look at the second one, they have a
- 1:02:40salary of 85,000. In fact, the ID is
- 1:02:42also the same; it's a bit of a data
- 1:02:44quality problem. But I couldn't get to
- 1:02:46the second one. So that is the
- 1:02:50limitation with VLOOKUP, Index-Match,
- 1:02:52and XLOOKUP, but if I want everyone
- 1:02:55with 120,000 or all the Tuxwells, how
- 1:02:58would I do that? Think about how to do
- 1:03:02this as homework and tell me in the
- 1:03:04comments. So, I have completed the
- 1:03:08basic examples here. The next three are
- 1:03:12complex problems. Let's move on to
- 1:03:15those. Our eighth example is a complex
- 1:03:18formula. In fact, these three, the
- 1:03:21remaining three, are also complex
- 1:03:23formulas. So, the eighth one is: What
- 1:03:25is the name of the person with the
- 1:03:27highest salary? We want to find that
- 1:03:30out. So, we previously saw what the
- 1:03:33highest salary was here in the fourth
- 1:03:36question. This is MAX, the maximum of
- 1:03:39the salary. But here, we only know the
- 1:03:41amount, we don't know who the person is
- 1:03:43. So now, we want to find out who that
- 1:03:46person is. Actually, if you remember,
- 1:03:49the highest salary of 120,000 is shared
- 1:03:51by two people. So, for the eighth one,
- 1:03:54there are two sections: one is who the
- 1:03:57first person is, and the other is who
- 1:03:59all the people are, separated by commas
- 1:04:01. So let's move on to this. First, what
- 1:04:05we need to do is use XLOOKUP because we
- 1:04:07need the person's name, so let's write
- 1:04:10XLOOKUP and look up the highest salary.
- 1:04:13We will calculate the highest salary
- 1:04:17here using MAX of the Staff table
- 1:04:20salary column. So, the first part of
- 1:04:24XLOOKUP generated 120,000 for me; if I
- 1:04:28point to it, I can see it in the
- 1:04:31tooltip. Once it finds this 120,000 in
- 1:04:35the staff salary column, we want that
- 1:04:39person's name. We don't just want the
- 1:04:42first name. We want the full name,
- 1:04:44combining the first and last name
- 1:04:45together. So, I will show you another
- 1:04:47trick for that. Here, we take the staff
- 1:04:49table first name...since just the first
- 1:04:51name isn't enough, we need the last
- 1:04:54name too. So, we use an ampersand, then
- 1:04:57a space inside double quotes, and
- 1:05:00another ampersand—meaning after the
- 1:05:04first name, add a space, then the staff
- 1:05:07last name. This will find those with
- 1:05:12the highest salary in the column and
- 1:05:15print "Minerva Ricard" directly here.
- 1:05:19Let's go into the data and verify if
- 1:05:21this is correct. If I go down here and
- 1:05:25select 120,000, I get the name Minerva.
- 1:05:30In fact, if you see here, it's not just
- 1:05:33Minerva; we also have Mick. Sometimes,
- 1:05:37when we ask who sold the highest or
- 1:05:40lowest products, there are multiple
- 1:05:43people involved. So, if I want both
- 1:05:46these names together as a
- 1:05:48comma-separated list instead of just
- 1:05:51one, let's see how to do that next. For
- 1:05:54just one, this works, but since there
- 1:05:56are obviously multiple, let's see how
- 1:05:58to do it. For this, we will use the
- 1:06:00FILTER function. We won't use XLOOKUP
- 1:06:01anymore. What do we need to filter
- 1:06:03using the FILTER function? We need to
- 1:06:06combine the first name and last name,
- 1:06:08so Staff table first name, ampersand,
- 1:06:16and the Staff table last name. Up to
- 1:06:21this point, it will create a column by
- 1:06:23combining the first and last names.
- 1:06:25What is that? We need to include the
- 1:06:28condition where Staff salary equals the
- 1:06:31MAX of the Staff salary. So, as you can
- 1:06:36see here, we write the condition like
- 1:06:38this. If you only run the filter, it
- 1:06:41gives both names vertically in a spill
- 1:06:43range, one in each cell. Actually, we
- 1:06:46don't want it like that; we want both
- 1:06:49names separated by a comma in a single
- 1:06:51value. To do this, after the filter
- 1:06:54work is done, there is a function
- 1:06:56called TEXTJOIN. TEXTJOIN can join
- 1:06:59multiple items together. Since we want
- 1:07:02a comma between them for this TEXTJOIN,
- 1:07:04I will provide a comma and a space as
- 1:07:05the delimiter. There is a parameter
- 1:07:08called 'ignore empty'; we will ignore
- 1:07:10that for now. I will close the filter
- 1:07:13at this point. This is a complex nested
- 1:07:15formula. If you look closely, it
- 1:07:17contains multiple items. There is
- 1:07:19TextJoin, Filter, Max, and several
- 1:07:22other items like that. In fact, there
- 1:07:25is also the technical concept of 'First
- 1:07:26', right? Combining First Name and Last
- 1:07:29Name, then comparing directly here, it
- 1:07:32gets a bit complicated. Try it out once
- 1:07:34, and you will get used to it. So, if I
- 1:07:38do this, I will get both their names
- 1:07:41directly. Actually, if I want a
- 1:07:46different condition, let me write
- 1:07:49another one. Instead of salary, I will
- 1:07:57say Department equals Sales. What will
- 1:08:04this do? It will get all the names of
- 1:08:06the people in the Sales department and
- 1:08:08join them into one big list for me. As
- 1:08:11you can see, when we have such a long
- 1:08:13list, we might think, it would be
- 1:08:15better if it were in alphabetical order
- 1:08:18. In that case, we can send this list
- 1:08:22to 'Sort' after filtering, and then
- 1:08:25join the text, giving us an A-to-Z
- 1:08:28order. Like Antoinette, Cary, Fidela,
- 1:08:33Freida, Joaquin, John, and so on. So,
- 1:08:36if you add a few extra techniques to
- 1:08:40this nesting, you can provide exactly
- 1:08:43what the business needs. That was the
- 1:08:46eighth example; now let me show you the
- 1:08:49ninth. The ninth one is: "All employees
- 1:08:53that joined in the month of March". If
- 1:08:57we look at the data here, I have the
- 1:09:00start dates. Actually, people join
- 1:09:04every single year. I am not concerned
- 1:09:07with the year or the specific day. If
- 1:09:09they joined in the month of March—for
- 1:09:12example, here is someone who joined in
- 1:09:15March 2021; I need them. There aren't
- 1:09:18any others on this screen, they must be
- 1:09:21further down. Oh, look here, there is
- 1:09:25someone who joined in March 2018 and
- 1:09:29someone who joined in 2020. I need all
- 1:09:32of them, meaning any year, as long as
- 1:09:36they joined in March. How do we get
- 1:09:39them? We only need their ID, First Name
- 1:09:42, and Last Name. How do we write this?
- 1:09:44Try it yourself once, pause the video.
- 1:09:46But I will show you my solution. We
- 1:09:49start with 'Filter' as usual, filtering
- 1:09:53the Staff table. Since we need ID,
- 1:09:56First Name, and Last Name, I open
- 1:09:58another square bracket, select ID,
- 1:10:01colon, and Last Name, telling it to
- 1:10:04give me those three columns. Now, what
- 1:10:07is the problem in the 'Include' part?
- 1:10:10We need March, but it could be any
- 1:10:12March. So, we use the 'Month' function
- 1:10:17on the Staff table's Start Date column.
- 1:10:22What this MONTH function does is, it
- 1:10:25looks at the start date and tells us
- 1:10:27what month it is. So, for Jan it is one
- 1:10:30, Feb it is two...likewise, it ignores
- 1:10:32the date and year and extracts only the
- 1:10:34month for us. If we set this equal to 3
- 1:10:37, then everyone who joined in March
- 1:10:39will automatically appear. So, the
- 1:10:42technique you observed here is that you
- 1:10:44don't necessarily have to write a
- 1:10:46condition with just a column. We can
- 1:10:49pass that column into a function like
- 1:10:51MONTH and write a condition based on
- 1:10:54that, like "equal to 3"; this is a very
- 1:10:57powerful and amazing technique. Once we
- 1:11:01understand this, we can easily pull out
- 1:11:03even very complicated data. So, when I
- 1:11:07hit enter, everyone from the batch that
- 1:11:09joined in March appears. You can verify
- 1:11:12if this is correct or not. Using this
- 1:11:15same approach, for example, if I want
- 1:11:18everyone whose first name starts with '
- 1:11:21H', I can fetch that as well. So, let's
- 1:11:24see how we can do that in the next step
- 1:11:27. Let's filter and get all the data.
- 1:11:31Staff, Staff First Name. Instead of
- 1:11:36just "first name" here, if we say "LEFT
- 1:11:39of first name", it will give us the
- 1:11:42leftmost letter of the first name. If
- 1:11:46this is equal to 'H', then basically it
- 1:11:48searches the left character, and if
- 1:11:50it's 'H', then everyone—meaning the
- 1:11:52batch starting with 'H'—will show up.
- 1:11:54See? Here we have Hussain, Hogan,
- 1:11:57Hephsibah, Hemavathi, Hinda, all of
- 1:12:00them! It's that easy. Like this, you
- 1:12:04can write anything; you want those
- 1:12:06starting with 'H', those with a salary
- 1:12:09greater than 1000, or maybe the name
- 1:12:12ends with the letter 'N'...you can
- 1:12:14write whatever you want and easily
- 1:12:17fetch it. So that's the ninth example.
- 1:12:20Our final example is to create a
- 1:12:24department report using complex
- 1:12:28formulas. In this, I am combining
- 1:12:31multiple formulas—everything we've
- 1:12:33used so far—mixing them all up, and
- 1:12:35showing you; plus I'll show you some
- 1:12:38extra techniques and how to do
- 1:12:40conditional formatting. There is also a
- 1:12:43surprise for you. Come, let's do this.
- 1:12:46This is basically like a grand finale,
- 1:12:48a chance to combine everything we've
- 1:12:51learned so far. Here, I want every
- 1:12:54department to appear automatically, I
- 1:12:56want to know how many people are in
- 1:12:58them, I want to know their average
- 1:13:00salary, and I also want to know the
- 1:13:03difference between that average and the
- 1:13:05overall average. And I also need to
- 1:13:08know what the highest salary is in each
- 1:13:11department. There's a small surprise,
- 1:13:13I'll show you that at the end. So first
- 1:13:14, let's get the departments. For this,
- 1:13:17we use UNIQUE (Staff Table Department),
- 1:13:20which gives me every department here. I
- 1:13:24will sort all these departments
- 1:13:26alphabetically; SORT of UNIQUE will
- 1:13:29sort them, and they will come in A to Z
- 1:13:32order. Now, to see the head count for
- 1:13:34all these departments, we can use
- 1:13:36COUNTIFS. COUNTIFS (Staff Table
- 1:13:40Department, is, this spill range B C#).
- 1:13:46This will automatically calculate and
- 1:13:48give me the values for each department.
- 1:13:50Everything is correct. You might be
- 1:13:52wondering why there are only two, or
- 1:13:54why there are fewer people here. That
- 1:13:56is because, in the middle of the video,
- 1:13:58we changed a few names. We changed
- 1:14:00their department name to "Business
- 1:14:02Growth" to test if the UNIQUE function
- 1:14:04works or not. That is why it came out
- 1:14:05like this. So, now that we have it,
- 1:14:07let's calculate the average. The
- 1:14:09average is very simple: AVERAGEIFS.
- 1:14:14Staff table salary column, staff table
- 1:14:19department is B6 hash; the average for
- 1:14:23every department will come out neatly.
- 1:14:28Now, let's say I want to know the total
- 1:14:30number of people overall. I will write
- 1:14:33it above, this is COUNTA of the staff
- 1:14:35employee ID, meaning I...Calculate how
- 1:14:42many IDs there are, and it tells me 260
- 1:14:44. Our total population is 260. Let's
- 1:14:48also calculate the overall average,
- 1:14:50AVERAGE of the staff table salary is
- 1:14:5273,000. So, what I need now is the
- 1:14:57distance of each department from that
- 1:15:00overall average. Here, the difference
- 1:15:03from the overall average is basically
- 1:15:06taking this spill range D6 hash minus
- 1:15:09this number, which gives me the
- 1:15:11distance for each. So, for example, if
- 1:15:14you look here, it is 69,000. This is at
- 1:15:17a negative 4,000 compared to this
- 1:15:1973,000. Whereas, if it is 79,000, they
- 1:15:23are 5,800 above the 73,000 mark. So, by
- 1:15:26calculating the difference for everyone
- 1:15:28like that, the list is automatically
- 1:15:30printed out neatly. Instead of just
- 1:15:33looking at these lists, we can normally
- 1:15:35add a small graphical representation
- 1:15:37for them. So, for that purpose, I will
- 1:15:40use "equals" to bring in this same
- 1:15:42range again. So that I only get these
- 1:15:45numbers. I have preset the formatting
- 1:15:48for these numbers so that negative red
- 1:15:50colors don't appear; now, I select this
- 1:15:53range and add a data bar using
- 1:15:55conditional formatting. What this does
- 1:15:58is print a bar in the cell itself based
- 1:16:00on that number. Negative is red,
- 1:16:04positive is blue; once I manage this
- 1:16:07rule, select "Edit/Manage" and edit it,
- 1:16:10I can change the colors there.
- 1:16:13Basically, first, what I want to do is,
- 1:16:16if you look here, the number appears
- 1:16:18twice. Since we don't need the number
- 1:16:20twice, I will use "Show Bar Only" to
- 1:16:22remove the number; only the bar will
- 1:16:24appear. So, let's do "Show Bar Only".
- 1:16:27Next, let's adjust these colors a bit.
- 1:16:30For positive, I will set this dark
- 1:16:33color, and for negative, I will set
- 1:16:36this one. So, the departments with
- 1:16:41salaries lower than the overall average
- 1:16:43will be clearly visible in red, and
- 1:16:45those that are positive will appear in
- 1:16:48a dark color like this. And that amount
- 1:16:50also appeared here for my reference.
- 1:16:53Now, the highest salary—this is very
- 1:16:58simple: MAXIFS, staff salary, staff
- 1:17:03department is B6 hash. If you look at
- 1:17:08the highest in each department, it
- 1:17:10comes to 120, which is our overall
- 1:17:12highest, and it is in the Engineering
- 1:17:13department. Another person is in
- 1:17:15Services. If you look at the other
- 1:17:18departments, some have 100, some have
- 1:17:20119, and some departments have 99 as
- 1:17:23well. So, that is the overall report.
- 1:17:27Let's also calculate the overall max
- 1:17:30here, the max staff salary. So, that is
- 1:17:34the department report. Now I will also
- 1:17:36show you a surprise. Basically, there
- 1:17:38is no surprise, it's homework for you.
- 1:17:41Calculate what the median salary is for
- 1:17:43each department. Median means what is
- 1:17:46the mid-point of the salary? And what
- 1:17:48is the female ratio? There is a
- 1:17:51headcount of 26, how many of them are
- 1:17:52females, as a percentage of the overall
- 1:17:54, what is that ratio? Calculate these
- 1:17:56two and print them here. This is your
- 1:17:59homework, also print the overall median
- 1:18:01and overall female ratio at the top.
- 1:18:03The reason for this is that we don't
- 1:18:06just use formulas, we mainly use them
- 1:18:09to generate reports like these and
- 1:18:12produce analysis. So, you will get an
- 1:18:15idea of how to think and how to solve
- 1:18:17in this process. So that is it for
- 1:18:21essential Excel functions and formulas,
- 1:18:24from beginner to advanced, I have shown
- 1:18:27you all the formulas I normally use at
- 1:18:30work along with a sample data set. If
- 1:18:34you want to learn anything further, let
- 1:18:37me know in the comments saying these
- 1:18:39are the topics we want to learn, and
- 1:18:41based on that, I will prepare my next
- 1:18:43videos. Okay, I hope you liked this
- 1:18:46video, if you did, make sure to like it
- 1:18:48and share it with at least four friends
- 1:18:51. I will meet you again in another
- 1:18:54video. Until then, bye.
About this transcript
This page contains the full transcript of Learn all the important Excel Formulas in One Hour [Telugu] by Chandoo - Tech in Telugu, generated from the public captions YouTube serves with the video. The transcript has 11,778 words across 1,676 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.
What you can do with it
Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to choose a caption track available on the video.
Free YouTube transcript tool
YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, caption language selection, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.