YouTube2Text

Learn all the important Excel Formulas in One Hour [Telugu] — Transcript

by Chandoo - Tech in Telugu · 11,778 words · 1,676 segments · language en · Watch on YouTube

Full transcript

  1. 0:00When it comes to Excel, the biggest
  2. 0:02blunder many people make is trying to
  3. 0:05learn everything one after another by
  4. 0:07rote memorization, like SUMIFS,
  5. 0:09COUNTIFS, VLOOKUP, XLOOKUP, and so on.
  6. 0:13Instead of that, how should we approach
  7. 0:15it when we actually have a business
  8. 0:16problem? Which function should we use
  9. 0:19for that problem and in what way? If we
  10. 0:21learn which function to combine with
  11. 0:24another, it stays in our mind for the
  12. 0:27long term. This allows us to work much
  13. 0:29better. So, in this video, we will take
  14. 0:3210 business problems and look at which
  15. 0:35formulas to use for them. Why should we
  16. 0:37use them? How to use them? How to nest
  17. 0:40one inside another? I will explain all
  18. 0:42of this to you thoroughly, clearly, and
  19. 0:45in detail. Alright, let's head into the
  20. 0:48video and talk about the rest of the
  21. 0:49details. Let's go. So, this is the file
  22. 0:52we are going to use. I have a sample
  23. 0:54employee dataset in this. Likewise, we
  24. 0:58are learning these 10 concepts; I will
  25. 1:00clearly explain how to answer these 10
  26. 1:03business questions and all these
  27. 1:05functions along the way. I have also
  28. 1:09prepared some challenges and homework;
  29. 1:10you will understand what those are as
  30. 1:12we go along. I have placed both the
  31. 1:15completed version and the blank version
  32. 1:17of this file in the video description.
  33. 1:19My suggestion is that you download the
  34. 1:21blank version and practice along with
  35. 1:24me as I explain, so the concepts will
  36. 1:26be clearly embedded in your mind. But
  37. 1:29if you have any doubts anywhere, you
  38. 1:31can compare it with the completed file
  39. 1:33and verify the formulas. Alright, let's
  40. 1:37get into the details. This is a
  41. 1:39somewhat time-consuming process, so try
  42. 1:42to clear some time and complete it all
  43. 1:45in one sitting; that way, everything
  44. 1:48will connect well for you. So, normally
  45. 1:51, why do we use Excel functions and
  46. 1:53formulas? Everyone knows Excel is just
  47. 1:56a place to maintain data. However, the
  48. 1:59real power of Excel lies in its ability
  49. 2:04to calculate things. If you just put
  50. 2:07data in it, there's nothing much there.
  51. 2:09There isn't much difference between
  52. 2:10this and a book if we just store
  53. 2:12information. But the real power of
  54. 2:15Excel is that it can take all the
  55. 2:17numbers here, do something with them,
  56. 2:19and generate new numbers or new values.
  57. 2:22For example, I have this employee data.
  58. 2:25If my boss comes and asks, "Okay, this
  59. 2:27is all fine, but we have to pay
  60. 2:29salaries this month, so what is the
  61. 2:31total amount we need to pay?" what
  62. 2:33happens? Currently, I have the annual
  63. 2:35salary in this salary column. So, if I
  64. 2:38select this entire salary column, it's
  65. 2:40in a table format. So, if we just click
  66. 2:43like this, the whole column gets
  67. 2:45selected. After doing that, I can see
  68. 2:48the sum here; if I look at the status
  69. 2:51bar, I can see 19 million 208 thousand.
  70. 2:54That is the total annual salary. Since
  71. 2:57we only need the next month's salary,
  72. 3:00dividing this by 12 is enough. Instead
  73. 3:04of doing all this manually, we can use
  74. 3:06Excel formulas. For example, if I want
  75. 3:08the total in this column, let me show
  76. 3:11you what the name of this table is. One
  77. 3:13, it is in a table, and the name of
  78. 3:15this table is "Staff." So, when we use
  79. 3:18data in a table, we use the table name
  80. 3:21and the column name. For example, if I
  81. 3:24want the total salary here, I use the
  82. 3:28SUM () function, with the Staff table,
  83. 3:31opening bracket, Salary column, and
  84. 3:35closing bracket. Now, if I press enter,
  85. 3:38I get the same value that was shown in
  86. 3:40the status bar. However, since we want
  87. 3:43the next month's figure, if I divide
  88. 3:46this by 12, I'll know that we need 1.6
  89. 3:49million dollars per month. So, by using
  90. 3:53these functions and formulas, we can
  91. 3:56understand the data in various ways and
  92. 3:59discover new insights from it. This is
  93. 4:02what these functions and formulas are
  94. 4:04used for. Both are synonyms; function
  95. 4:07and formula are the same. Generally, a
  96. 4:11function is like SUM, and a formula is
  97. 4:15created when those functions are
  98. 4:18combined into an expression. But, in
  99. 4:22general, we can use them
  100. 4:23interchangeably. Alright, that was a
  101. 4:26simple formula. Let's move on to actual
  102. 4:29formulas; most people who use Excel,
  103. 4:31even a little, already know SUM, COUNT,
  104. 4:33and IF functions. So, I don't want to
  105. 4:36waste your time explaining those; let's
  106. 4:38move to the next level. The next level
  107. 4:40is about real business problems and
  108. 4:42which formulas to use for them. Another
  109. 4:46mistake some people make is that while
  110. 4:48learning, they try to memorize these
  111. 4:50functions one after another, like SUM,
  112. 4:52COUNT, COUNTIF, and VLOOKUP, trying to
  113. 4:54keep them in their heads. That is not
  114. 4:57very useful. In general, to learn and
  115. 5:00use this well, one must know which
  116. 5:03function to use for a specific problem.
  117. 5:06So, I will follow that approach. I have
  118. 5:0910 problems, and as we solve these 10
  119. 5:12problems, we will discover all these
  120. 5:14functions. Let me tell you about my
  121. 5:17version; I am using Excel 365. If you
  122. 5:21use Excel 365 or Excel Online, you can
  123. 5:24apply everything exactly as is. If you
  124. 5:28are using an older version of Excel,
  125. 5:30you won't be able to use some of these
  126. 5:31functions. I will tell you what those
  127. 5:34are while I am working on them. So that
  128. 5:37you are aware. But wherever you join a
  129. 5:41job or work for a client at any other
  130. 5:44company, 99%of the time they have 365
  131. 5:47nowadays. So, it is better to learn on
  132. 5:50the latest version, which is what I
  133. 5:52will focus on. However, I will let you
  134. 5:54know in advance if something won't work
  135. 5:57in your older version, so you can make
  136. 5:59a mental note. Alright, let's go into
  137. 6:01each one of them. The first thing we
  138. 6:03will do is solve this question. This is
  139. 6:06about my staff; I want to know their
  140. 6:10total salary and headcount
  141. 6:13department-wise, and I want to produce
  142. 6:17a small report. We will learn what
  143. 6:20functions we need to write and how to
  144. 6:22write them for that. Looking at the
  145. 6:24data I have, there are many departments
  146. 6:27. How many? If I apply a filter, I can
  147. 6:29show you; see, there are about 10
  148. 6:32departments here. I need to know the
  149. 6:34headcount and total salary for every
  150. 6:37department—Legal, Marketing, Product
  151. 6:41Management, Research and Development.
  152. 6:45So, I have set up a space here where I
  153. 6:47will list the department names, and I
  154. 6:50need all this information. I will show
  155. 6:54you a quick, simple solution to list
  156. 6:56the department names, and later, I will
  157. 6:58also show you a formula-based approach.
  158. 7:01I will select this Department column.
  159. 7:04You can use the arrow keys and shift
  160. 7:07keys to select everything, then copy it
  161. 7:10with Ctrl + C, come here, right-click,
  162. 7:12and select Paste Values—meaning I
  163. 7:15just pasted the departments as they are
  164. 7:17, without any formatting; now, keeping
  165. 7:20it selected, I go to the Data ribbon,
  166. 7:23where I have a "Remove Duplicates"
  167. 7:25button. Let’s use this button to
  168. 7:28remove them. So, these features are
  169. 7:30available in any version of Excel. They
  170. 7:32have been available since 2007. I will
  171. 7:36leave it as is, select "Continue with
  172. 7:38the current selection," and click "
  173. 7:40Remove Duplicates" in the Department
  174. 7:42column, so each department will appear
  175. 7:44exactly once per row. Any duplicates
  176. 7:47will be gone. This is one method. There
  177. 7:50is another method too, which I will
  178. 7:52explain later in the video. Now that I
  179. 7:55have my departments with one row for
  180. 7:58each, I need to know the headcount and
  181. 8:00total salary for every department.
  182. 8:03Meaning, I want to count how many times
  183. 8:06the "Training" department appears. So,
  184. 8:09the function we use for this is
  185. 8:12COUNTIFS. Actually, in Excel, these
  186. 8:15functions—COUNTIF, SUMIF—all have
  187. 8:17two versions. One is COUNTIF, and the
  188. 8:20other is COUNTIFS, as you can see when
  189. 8:23you type it here. So, my recommendation
  190. 8:26when learning is to just straight away
  191. 8:29learn the COUNTIFS function and forget
  192. 8:32about COUNTIF. The difference between
  193. 8:36them is that one is singular and the
  194. 8:38other is plural. Wait, COUNTIFS allows
  195. 8:41us to count for a single condition.
  196. 8:44With COUNTIFS, you can count using
  197. 8:46multiple conditions, like the
  198. 8:47department must be this, the gender
  199. 8:49must be female, and the salary must be
  200. 8:52over 50,000. So, that is the only
  201. 8:55difference between them; generally,
  202. 8:57whether for one or multiple, I use
  203. 8:59COUNTIFS, and you should learn that too
  204. 9:01; forget the original one, it is not
  205. 9:04even necessary. So, I type COUNTIFS and
  206. 9:07specify what the range and criteria are
  207. 9:10. For the range, I will come here to
  208. 9:13the data and select this Department
  209. 9:15column; when data is in a table, you
  210. 9:17can manually drag to select everything,
  211. 9:20or you can just click on the header to
  212. 9:22select the entire column. Now, as you
  213. 9:26can see with COUNTIFS Staff [Department
  214. 9:28], when the data is in a table, instead
  215. 9:31of going to the worksheet to select it,
  216. 9:33I just type it out myself because it is
  217. 9:36faster. But initially, let’s do it
  218. 9:38this way. Comma, so I want to know how
  219. 9:42many people are in the Training
  220. 9:44department. So, I will select Training
  221. 9:47here. This means '1! B3', asking what
  222. 9:51the value in cell B3 of Worksheet 1 is.
  223. 9:56If you close this and hit enter, it
  224. 9:58tells me there are 24 people in the
  225. 10:01Training department. Let me explain how
  226. 10:04this works; if you come here and filter
  227. 10:07by Training, it tells you exactly how
  228. 10:11many rows are there. Let me show you
  229. 10:14how many rows are here. If you see here
  230. 10:17, it says 24 of 260 records, right?
  231. 10:21That is the same 24 it shows there. So,
  232. 10:25this is the COUNTIFS function; I can
  233. 10:27use it to count one condition or
  234. 10:29multiple conditions, and now that we
  235. 10:31have this 24, we need to know the
  236. 10:33counts for everyone else too. For this,
  237. 10:37we can drag the formula down like this.
  238. 10:39There is another trick: instead of
  239. 10:41dragging manually, since you need as
  240. 10:43many as there are here, just go to the
  241. 10:45corner and double-click when the mouse
  242. 10:47pointer turns into a plus sign, and it
  243. 10:49will automatically fill to the bottom.
  244. 10:51I will tell you a third technique,
  245. 10:53which is really powerful; actually,
  246. 10:55this is what I use these days. That is,
  247. 10:59if you have Excel 365, instead of
  248. 11:01writing a formula for each cell, since
  249. 11:05I have all these departments here and
  250. 11:08need counts for all of them, I will
  251. 11:11remove this and select all of them
  252. 11:14instead of just one; when we hit enter,
  253. 11:17it calculates the counts for each and
  254. 11:21neatly shows all the values. This
  255. 11:25process is called spilling; it means we
  256. 11:28wrote the formula here and it
  257. 11:30automatically spilled, or poured, all
  258. 11:33the values down to here. You can
  259. 11:36imagine pouring milk from a jug and
  260. 11:39having it flow down; that’s what
  261. 11:41spilling is. This whole thing This
  262. 11:47entire thing is called a dynamic array
  263. 11:49range. These names can be a bit
  264. 11:52confusing, but the meaning is simply
  265. 11:54that this is an array. An array is like
  266. 11:57a list of values that are dynamically
  267. 11:59generated. Again, whether we use that
  268. 12:01name or not, the meaning remains the
  269. 12:03same. So, if you understand the meaning
  270. 12:05, you can forget about the name. So,
  271. 12:08this is the best technique. The only
  272. 12:10problem is that it only works in Excel
  273. 12:13365. If you use older versions, you
  274. 12:15cannot use this technique; with older
  275. 12:16versions, you have to write it once and
  276. 12:18drag it manually. You might wonder, "
  277. 12:21Brother, why should we use this? What
  278. 12:22is the advantage of this?" Exactly. The
  279. 12:25advantage is that if the number of
  280. 12:28departments changes in the future (say
  281. 12:31there are 12 now and they become 14
  282. 12:33later), we can set this formula to
  283. 12:36automatically adjust and get the counts
  284. 12:39for everything. In the current setup,
  285. 12:43it won't work that way because the
  286. 12:46range is limited to B3 to B14, so if
  287. 12:48another department is added, it won't
  288. 12:51update. But with the techniques we are
  289. 12:54going to learn further, we can automate
  290. 12:56all of that. So, this reduces our
  291. 12:59workload. Once we create it, nothing
  292. 13:01needs to be changed. Whether new
  293. 13:04departments come or new employees join,
  294. 13:06everything will update automatically.
  295. 13:08So, that is the superpower you get when
  296. 13:12you use formulas. Alright, let's look
  297. 13:15at the total salary as well. The
  298. 13:16function used for this is called SUMIFS
  299. 13:18(). This means we want to see the total
  300. 13:22salary in each department. What did we
  301. 13:24do earlier? We counted how many people
  302. 13:26were in each department. Now we are
  303. 13:28summing, so the function changes from
  304. 13:30COUNTIFS to SUMIFS. That is the
  305. 13:32difference. In this SUMIFS function, we
  306. 13:34first have to state what we are going
  307. 13:36to sum. For this, we use the Staff
  308. 13:38table, open bracket, and the Salary
  309. 13:40column; again, you can type it or
  310. 13:42select it. I generally prefer typing it
  311. 13:44, as it is faster. We need the Salary
  312. 13:47column, and for the Staff table
  313. 13:50department, it must match that
  314. 13:53department. Instead of just one, if we
  315. 13:56select all of them like this, I will
  316. 13:59get the total for everyone here; I
  317. 14:01think I closed an extra bracket. Their
  318. 14:04data will appear, and we can clearly
  319. 14:07understand everything. So, what is the
  320. 14:10headcount, what is the total, and what
  321. 14:12would the average be for each
  322. 14:14department? Try this as a small
  323. 14:16homework task; I will tell you my
  324. 14:18solution in a couple of seconds, so
  325. 14:20pause here and try it yourself. For the
  326. 14:24average, there is a similar function
  327. 14:27called AVERAGEIFS; it asks what needs
  328. 14:29to be averaged and what the condition
  329. 14:32is. We can add multiple conditions;
  330. 14:36again, I want the average salary from
  331. 14:39the Staff table salary column where the
  332. 14:42Staff department matches this list of
  333. 14:44departments. So, after closing the
  334. 14:48bracket and hitting enter, I get the
  335. 14:50average for each department. If you try
  336. 14:53this and get the values but not the
  337. 14:55formatting, you should select and apply
  338. 14:57the currency formatting beforehand.
  339. 15:00This is because these dynamic formulas
  340. 15:02only calculate the value and do not
  341. 15:04handle currency formatting. I already
  342. 15:08set the formatting in advance, so it
  343. 15:09appeared directly with the formatting.
  344. 15:12So, this is the first business problem;
  345. 15:15I hope this problem helped you
  346. 15:17understand how to use COUNTIFS, SUMIFS,
  347. 15:20and AVERAGEIFS. Using these functions,
  348. 15:22we can answer any business questions
  349. 15:25like these. For example, how many
  350. 15:28people joined last month? Or, if you
  351. 15:31are running a school, how many students
  352. 15:33have not paid the fee for this term yet
  353. 15:35? Or, what is the total fee we
  354. 15:38collected in the last three days?
  355. 15:40Alternatively, if you are organizing an
  356. 15:43event, how many people bought a ticket
  357. 15:45but did not show up? We can use these
  358. 15:48formulas to answer all such questions.
  359. 15:51So, these three are very powerful. But
  360. 15:55the good thing is that all three follow
  361. 15:57the same pattern; if you understand one
  362. 15:58, you understand all three. So easy. In
  363. 16:01fact, it is not just these three; there
  364. 16:02are a few more. I will introduce those
  365. 16:04to you later. Alright, let's move on to
  366. 16:07the next one. Before we move to the
  367. 16:09next part, you have a small homework
  368. 16:11challenge. That is, calculate the
  369. 16:14permanent headcount for each department
  370. 16:17. Now, let's look at the data; let me
  371. 16:21clear the data here. There is a column
  372. 16:24called Employee Type. In this, I have
  373. 16:26three types of employees: Fixed Term,
  374. 16:28Permanent, and Temporary. I need to
  375. 16:31know how many there are in this batch,
  376. 16:33the permanent ones. So, calculate how
  377. 16:36many permanent employees are in each
  378. 16:39department, and keep it in an adjacent
  379. 16:42column here. This is your homework.
  380. 16:45Alright, let’s move on to the next
  381. 16:47concept. The next one is, all employees
  382. 16:51with more than 100,000 salary. My
  383. 16:55intention here is to see who earns a
  384. 16:58higher salary in this data. So, looking
  385. 17:02at this filter, there are some people
  386. 17:04here who earn more than 100,000. I want
  387. 17:08to know who they are, where they are,
  388. 17:11their names, their IDs, and their
  389. 17:13departments; that is what I need. This
  390. 17:17can be solved in two ways. One is the
  391. 17:20manual method, where we apply filters
  392. 17:23here, select Number Filters, choose
  393. 17:25Greater Than, and type 100,000. Among
  394. 17:29those with more than 100,000...There
  395. 17:35are 47 people, their list will appear
  396. 17:38here, and I can view those 47 people. I
  397. 17:41called this a manual method because to
  398. 17:43do this, we have to apply the filter
  399. 17:46manually. So, once I set it to 100, for
  400. 17:49example, if I want 110, I have to
  401. 17:51change it again. Or if I want 95, I
  402. 17:54have to change it. So, there is a
  403. 17:58limitation with this technique: we have
  404. 18:00to change it manually every time. The
  405. 18:04second problem is, if I want to do
  406. 18:06something with the data I filtered—
  407. 18:08like, what is the total salary of those
  408. 18:11earning more than 100,000? What is
  409. 18:14their average? Or, based on their
  410. 18:17joining date, who are the five oldest
  411. 18:19employees? If I want to do any further
  412. 18:22analysis like this, I cannot do it. It
  413. 18:25would become even more manual work. So,
  414. 18:27that is why we use certain formulas.
  415. 18:29The concept we are introducing here is
  416. 18:31the FILTER function. Unfortunately,
  417. 18:34this FILTER function is available only
  418. 18:37in Excel 365 and Excel on the Web. In
  419. 18:40older versions, there isn't even a
  420. 18:42comparable formula for this. There are
  421. 18:45some long-winded techniques, but I
  422. 18:47wouldn't even recommend them. So, learn
  423. 18:51this FILTER function. If you don't have
  424. 18:55Excel 365 in your current work
  425. 18:57environment or at home, you can simply
  426. 19:00go to your browser and type "excel.new"
  427. 19:03; you will be able to open an Excel
  428. 19:06file. It will ask for your Microsoft
  429. 19:11user ID and password, or you can sign
  430. 19:13up for a new account if you don't have
  431. 19:16one. Since I have one, I will log in to
  432. 19:20it. This will create a brand new Excel
  433. 19:24file for you in the browser. You can
  434. 19:28copy the data I provided and then
  435. 19:30freely use the FILTER function here.
  436. 19:33The FILTER function works perfectly
  437. 19:35here, there are no issues. It is free.
  438. 19:39As for me, I have a paid account, so I
  439. 19:41logged into it; if you have a free
  440. 19:42account, you can log into that. Alright
  441. 19:45, I will show you how to use this
  442. 19:47FILTER function here. So, for this, I
  443. 19:49have set up a grid-like structure here.
  444. 19:52All employees with more than 100k
  445. 19:54salary, I will remove their headers too
  446. 19:56. So, I want their details here and
  447. 20:00their headers here. So, for the details
  448. 20:02, we use the FILTER function like this.
  449. 20:04FILTER staff table, comma, staff table
  450. 20:11salary greater than 100k. That’s it;
  451. 20:18there are two options for the filter
  452. 20:21here. One is what to filter, and the
  453. 20:23other is what the condition is. Here,
  454. 20:26we are providing one condition of
  455. 20:28greater than 100,000. I will explain to
  456. 20:31you later what to do if there are
  457. 20:34multiple conditions. So now, if we hit
  458. 20:36enter on this, those 47 people will
  459. 20:39appear here. If I select them like this
  460. 20:43, you can see it shows me the count is
  461. 20:4747. So, the FILTER function filters the
  462. 20:50data and automatically creates a spill
  463. 20:53range. Earlier, the spill went
  464. 20:57downwards. Since there are multiple
  465. 21:00columns here, it spilled this way and
  466. 21:02that way. It creates a two-dimensional
  467. 21:07range for us. The problem with this
  468. 21:11spill function is that the filter, or
  469. 21:13any spill function, only brings the
  470. 21:15data, it doesn't do formatting. If you
  471. 21:18look now, the formatting, the currency
  472. 21:19formatting, has not been applied. These
  473. 21:21numbers also came with extra details,
  474. 21:23everything appeared, right? So that is
  475. 21:25a limitation. With the FILTER function,
  476. 21:28or any dynamic function, you have to do
  477. 21:30the formatting manually. So, generally,
  478. 21:33what I do is select an extra range
  479. 21:35beforehand and set my formats so that
  480. 21:38it looks good. Another problem is that
  481. 21:41it only gives data and not the headings
  482. 21:44; we have to fetch the headings from
  483. 21:47the table again. So, a trick to get the
  484. 21:50headings is to open a bracket in our
  485. 21:53staff table, and if you look there, you
  486. 21:55can select every column, and if you go
  487. 21:58all the way down, there is an option
  488. 22:00called headers. This directly gives us
  489. 22:04the headers of that table. So, if I say
  490. 22:07hash headers here and then hit enter,
  491. 22:10all those headers, since they are
  492. 22:13horizontal, will spill horizontally and
  493. 22:16all the headers will appear neatly. So,
  494. 22:19this is for all employees with more
  495. 22:22than 100k. Now let's go to the next
  496. 22:25problem. The next problem is all
  497. 22:28employees with 100k, but I only want a
  498. 22:31few columns. So, we are in the same
  499. 22:33place for this too. I want those with
  500. 22:36more than 100k, but I don't need every
  501. 22:38column of theirs. All that is useless
  502. 22:41to me. For the analysis I am about to
  503. 22:43do, what I need is this employee ID
  504. 22:46column, first name, last name, and I
  505. 22:49don't need gender, department, salary.
  506. 22:51So, the columns I'm looking at are
  507. 22:55these: 1, 2, 3, 5, 6. If you look here,
  508. 22:58suppose I've numbered these columns:
  509. 23:02this is 1, this is 2, 3, 4, 5, and 6. I
  510. 23:05don't need column 4; I need these three
  511. 23:09, and these two, and nothing else. So,
  512. 23:12how do we do that? This is also very
  513. 23:14simple. If we take the filter and send
  514. 23:17that output to another function, that
  515. 23:19function will give us only these
  516. 23:21columns. Let me show you what that
  517. 23:23function is. First, we write the filter
  518. 23:25just as it is. Staff, Staff salary
  519. 23:29greater than 100k. Instead of typing
  520. 23:33100k manually here like this, what we
  521. 23:37can do is put a pointer in this place.
  522. 23:40We can say "greater than this cell"
  523. 23:43here. Now, since this cell is F67, I
  524. 23:45can say "greater than F67". The result
  525. 23:48is that if I change my mind and want to
  526. 23:51go for 110 instead of 100, I just need
  527. 23:53to change this number; I don't need to
  528. 23:55edit the formula. So, instead of
  529. 23:57hard-coding it manually, I will put "
  530. 23:59greater than F67" here. As of now, if I
  531. 24:02just hit enter, I will get all the
  532. 24:04columns. What is our problem? We don't
  533. 24:06want all the columns, we only want 1, 2
  534. 24:08, 3, 5, 6. So, I will send this filter
  535. 24:11function to another function. Another
  536. 24:13function that is only available in
  537. 24:16Excel 365 is called CHOOSECOLS. What
  538. 24:19this CHOOSECOLS does is take an output
  539. 24:22and present only specific columns from
  540. 24:24it. Those columns are 1, 2, 3, 5, 6. We
  541. 24:30can type the list of columns we want.
  542. 24:33If I hit enter, I get only these
  543. 24:36specific columns from that data. So,
  544. 24:40this is one concept. In this concept,
  545. 24:43the thing you need to understand is
  546. 24:45nesting. What nesting means is taking
  547. 24:48one function and sending it to another
  548. 24:50function, this CHOOSECOLS function. So,
  549. 24:54we are putting a function inside
  550. 24:55another function, like a samosa. This
  551. 24:57is what is called nesting. This nesting
  552. 24:59can be done in one layer or in multiple
  553. 25:03layers. Meaning, you can put one inside
  554. 25:06another, then another inside that, and
  555. 25:08keep going on like that. I don't have
  556. 25:11any food examples for this, but I'll
  557. 25:13leave it to your imagination. So that's
  558. 25:16the nesting concept. And how do we
  559. 25:18bring the headers? The same logic
  560. 25:21applies to headers; using CHOOSECOLS on
  561. 25:23the staff table headers 1, 2, 3, 5, 6
  562. 25:25means taking all those headers and
  563. 25:27telling it to give me only these
  564. 25:28headers, and it will bring the headers
  565. 25:30and provide them like this. So that
  566. 25:34covers all employees with a salary
  567. 25:36greater than 100k. Now let's move to
  568. 25:38the third one. The second and third are
  569. 25:40somewhat connected, so I am putting
  570. 25:42both two and three in the same
  571. 25:44worksheet. That is: all female
  572. 25:46employees with a salary more than 100k.
  573. 25:49So here we are setting two conditions:
  574. 25:51one, the salary must be more than 100k,
  575. 25:53and second, their gender must be female
  576. 25:56. So, here after I do this filter, I
  577. 26:00need to go into the gender and select
  578. 26:05female as well; I am looking for these
  579. 26:0918 people. How do we do this? Try it as
  580. 26:14homework, and I will explain. So here,
  581. 26:19filter, filter, staff table, staff
  582. 26:22table salary. Greater than, and I will
  583. 26:28point to this. So that is one condition
  584. 26:31, but we have two conditions, right? It
  585. 26:34must be female as well. In that case,
  586. 26:37what we do is put an open bracket
  587. 26:39around the first condition, add
  588. 26:41brackets, and before writing the second
  589. 26:43condition, we multiply using a star.
  590. 26:48Now, the second condition is staff
  591. 26:51table gender equals female. Like this,
  592. 26:57you can write as many conditions as you
  593. 26:59want by adding a star. So, that is how
  594. 27:02you do multiple conditions for filter.
  595. 27:06One difference here is, if you look at
  596. 27:08COUNTIFS or SUMIFS, when you have to
  597. 27:10write multiple conditions, we just
  598. 27:13write them separated by commas. But in
  599. 27:15filter, we multiply them, using a star.
  600. 27:19What actually happens internally is, if
  601. 27:21you look at this bit by bit, when I
  602. 27:24point here, you see the result showing
  603. 27:26up above—false, false, false, true.
  604. 27:30That is, it checks every row; if their
  605. 27:32salary is more than 100k, it becomes
  606. 27:33true, otherwise false. The second part
  607. 27:36does the same thing. But for gender,
  608. 27:39when you multiply both, these trues and
  609. 27:42falses cancel out, and where both are
  610. 27:45true, it results in one. Everything
  611. 27:47else, regardless of the combination—
  612. 27:49whether one is true and one false, or
  613. 27:51both false—becomes zero. And finally,
  614. 27:53what the filter does is ignore all
  615. 27:55zeros and brings only those with one.
  616. 27:57So that is how it works internally. And
  617. 28:01when we hit enter now, we get the list
  618. 28:04of female employees earning more than
  619. 28:06100k. So, as you can see, I got a
  620. 28:10shorter list, those same 18 people. Now
  621. 28:13, I don't actually need all the data
  622. 28:15for these 18 people; I only want the
  623. 28:17first six columns from them. This time,
  624. 28:21we also need gender, but we need only
  625. 28:23the first six columns. How to do this?
  626. 28:26We can use CHOOSECOLS here, but I will
  627. 28:28show you another technique. That is,
  628. 28:31since the ones we need are continuous
  629. 28:34from one to six, instead of writing
  630. 28:37inside the filter as "staff," we write
  631. 28:41"staff," open square bracket, open
  632. 28:44another bracket [select the first
  633. 28:47column we need, Employee ID, close
  634. 28:50bracket, then put a colon [open bracket
  635. 28:53, last column, Salary] column], and
  636. 28:56then close all the brackets. I mean,
  637. 29:00what we are saying is: I don't want the
  638. 29:03entire staff table, just filter and
  639. 29:06show the columns from Employee ID to
  640. 29:09Salary from the staff table. These are
  641. 29:12the conditions, these are the filtering
  642. 29:15conditions, and this is the output data
  643. 29:17. If you write it like that and hit
  644. 29:20enter, we get those first six columns.
  645. 29:23So, how do we handle headers? For
  646. 29:26headers, you just type equals, go into
  647. 29:29the data, and select the first six
  648. 29:32headers, and they will appear. The
  649. 29:36formula actually looks like this: after
  650. 29:38writing hash (#) headers and adding a
  651. 29:40comma, selecting from Employee ID to
  652. 29:43Salary asks it to give only those
  653. 29:44column headers. So those headers will
  654. 29:47appear, and you can apply formatting to
  655. 29:49them. So, that is number three. Let's
  656. 29:52review the concepts we've looked at; we
  657. 29:54learned about filters and how to use
  658. 29:56the asterisk when applying multiple
  659. 29:58conditions. There is a small challenge
  660. 30:02for you: it shouldn't stop at just "
  661. 30:05Female"—it must be females who earn
  662. 30:07more than 100, and their joining date
  663. 30:10must be 2020 or later; if you look at
  664. 30:13the data, I also have a start date here
  665. 30:16. In this start date, for example, if
  666. 30:20you look at this person, they joined in
  667. 30:222018, this one in 2019, and these two
  668. 30:25also in 2018. I don't need these people
  669. 30:27. Those who joined on or after January
  670. 30:311, 2020, aren't needed either. I only
  671. 30:34want the details of those specific
  672. 30:36people. So, how would you do this?
  673. 30:38Think about it, modify the filter
  674. 30:40function, and try it out here. This
  675. 30:44will be a small challenge as homework;
  676. 30:46it will help you think a bit about how
  677. 30:48to write conditions. Alright, let's
  678. 30:51move on to the next concept. Our next
  679. 30:53example is about lowest, highest, and
  680. 30:55top five salary values, and in this
  681. 30:57process, we are going to learn those
  682. 30:59functions again. So first, suppose you
  683. 31:02want to find out whose lowest salary it
  684. 31:05is—no, not whose, but what it is—
  685. 31:07for this, we can use the MIN function.
  686. 31:10MIN of staff table, salary column, and
  687. 31:16I get 28,160. We can also do the same
  688. 31:21for minimum female or male salary; I
  689. 31:24will tell you that in a minute. First,
  690. 31:28let's look at the highest; for the
  691. 31:31highest, we use MAX of staff table,
  692. 31:34salary column. It comes to 120,000. So
  693. 31:40that is minimum and maximum. If you
  694. 31:44want to see what the top five salaries
  695. 31:47are, there is a function called LARGE
  696. 31:50for this. LARGE, staff table, salary
  697. 31:54column, and the next option is K. This
  698. 31:57K means if we put 1, the highest person
  699. 32:01comes; if we put 2, the second person;
  700. 32:04if 3, the third person, and so on. I
  701. 32:08will type it here by putting 1. For the
  702. 32:12next person, we copy and paste the same
  703. 32:15formula and put 2 in place of 1. The
  704. 32:20reason 120,000 appeared for both 1 and
  705. 32:222 is that we have two people earning
  706. 32:23120,000, so that's why it happened; it
  707. 32:25will change for 3. Generally, instead
  708. 32:29of manually typing 1, 2, 3, 4, 5 like
  709. 32:32this, another technique is that I have
  710. 32:34a hidden column here. I keep 1, 2, 3, 4
  711. 32:38, 5 typed in a range beforehand, and
  712. 32:42now I point to that instead of using 1.
  713. 32:46Then, if I drag this, it will fetch
  714. 32:49that number for every row and show me
  715. 32:52what that salary value is. So, that is
  716. 32:56how we can use the LARGE function. The
  717. 32:58purpose of this 'four five' is just to
  718. 33:01explain things here, so once I set it
  719. 33:03up, I will hide this column. So that my
  720. 33:06output worksheet looks a bit cleaner.
  721. 33:09So that's for the top five salaries.
  722. 33:12Now, let's see how to do this whole
  723. 33:14thing for male and female. For male,
  724. 33:17instead of MIN, there is a function
  725. 33:20called MINIFS. This is a newer function
  726. 33:23, so if your version of Excel is very
  727. 33:26old, like 2007, 10, or 13, it won't be
  728. 33:29there. This function seems to have been
  729. 33:31available since 2016. So, it works just
  730. 33:33like SUMIF and COUNTIF. First, we
  731. 33:37select the range from which we need the
  732. 33:40minimum, which is the Staff table
  733. 33:42salary column, and then the Staff table
  734. 33:45gender column, and the gender listed
  735. 33:47here. So the minimum is 28,000 for male
  736. 33:51, but if I change this to female, the
  737. 33:54female minimum is 28,305. Whoever has
  738. 33:57the lowest salary is male, so that's
  739. 33:59why it showed that for male, and a
  740. 34:01different one for female. For the
  741. 34:05highest, there is a function called
  742. 34:09MAXIFS: MAXIFS, Staff table salary
  743. 34:12column, Staff table gender column. So
  744. 34:18male and female are both 120,000. Since
  745. 34:21there are two people at 120,000, it
  746. 34:23seems there's one male and one female.
  747. 34:26So, try to do this top five challenge
  748. 34:29as your homework; I will explain the
  749. 34:33next technique to you. There are
  750. 34:36actually two ways to do this top five.
  751. 34:38One is by using the LARGE function with
  752. 34:42a hidden column. But in newer versions
  753. 34:45of Excel, they have added new functions
  754. 34:48. So, in Excel 365, there is a function
  755. 34:51called SORT and a function called TAKE.
  756. 34:54I will show you how to solve this
  757. 34:56problem by combining these two. I will
  758. 34:58also explain how to handle the male and
  759. 35:00female part there, but figure out how
  760. 35:02to do it yourself as well. So, first,
  761. 35:06if I want to see the top five, in Excel
  762. 35:09365, I have a function called SORT.
  763. 35:12What this SORT function does is, if we
  764. 35:14provide data, for example, if I give
  765. 35:17SORT Staff Salary, it sorts the salary
  766. 35:20values. By default, it sorts in
  767. 35:22ascending order, meaning the lowest
  768. 35:24salary is here, and the highest salary
  769. 35:27goes to the bottom. So you see, it goes
  770. 35:302860, 305, 329, and all the way down at
  771. 35:35the bottom, I'll have my 120,000. So
  772. 35:39this SORT function sorts the data and
  773. 35:42returns it. This SORT function has an
  774. 35:45option, which is the sort order, the
  775. 35:46third parameter. When there's only one
  776. 35:49column, we don't need to specify the
  777. 35:51sort index; if there are multiple
  778. 35:52columns, we must specify the index. So,
  779. 35:55the sort order is ascending by default;
  780. 35:57if I put minus one, it will sort in
  781. 35:58descending order. If I put minus one,
  782. 36:01my highest salary comes to the top, and
  783. 36:05all the other salaries go down. Like
  784. 36:08this, now after sorting, I don't need
  785. 36:10everyone's values. I need to tell it to
  786. 36:12sort and give me the top five people.
  787. 36:15For this, there is a function called
  788. 36:17TAKE. So TAKE, this TAKE function takes
  789. 36:20a list or a range and gives you the
  790. 36:23number of rows you need from the top.
  791. 36:27So, for the TAKE function, whatever the
  792. 36:29sort is, if we say five, it will give
  793. 36:31the first five rows after sorting. So,
  794. 36:33TAKE 5 will give me the exact same
  795. 36:36output as the LARGE function. So, this
  796. 36:39is one technique. In this, we are using
  797. 36:42SORT and TAKE directly instead of using
  798. 36:46LARGE (). Using this, I can also
  799. 36:48extract data for male and female
  800. 36:49categories separately. To do this,
  801. 36:50first, we perform a filter. If we
  802. 36:54filter the staff table, salary column,
  803. 36:58where staff gender is equal to male,
  804. 37:03stopping there would give me only male
  805. 37:07salaries. If I sort these male salaries
  806. 37:12in their original order—meaning we
  807. 37:15are doing it ascending—they will go
  808. 37:17from lowest to highest. If I put a
  809. 37:22minus sign, I will get the male
  810. 37:24salaries in descending order. It has
  811. 37:27worked so far, right? Is it clear? We
  812. 37:29filtered it, sorted the filter, and now
  813. 37:32I have sent this whole thing into the
  814. 37:34TAKE function. TAKE 5 means I will get
  815. 37:39those top five male salaries. Let me
  816. 37:44show you again; this involves triple
  817. 37:48nesting. First, we filtered; with that,
  818. 37:52I filtered the data down to males, so
  819. 37:55only male salaries come up. Then, I
  820. 37:58sorted those male salaries. So, this is
  821. 38:01the next level. After sorting in
  822. 38:04descending order, the final level is
  823. 38:06taking five people from that list, the
  824. 38:09top five. So, by nesting TAKE like this
  825. 38:12—one inside another—we get the
  826. 38:15final desired result. This concept can
  827. 38:18be a bit confusing when you are
  828. 38:20initially learning and practicing Excel
  829. 38:23. Once you get used to thinking this
  830. 38:26way, your work becomes much simpler. So
  831. 38:29, practice this kind of nesting with
  832. 38:31different problems; I did it for males,
  833. 38:34you try it for females. Also, try to
  834. 38:36solve this without using SORT and TAKE,
  835. 38:39so that you can understand some
  836. 38:41additional concepts. I will show you a
  837. 38:44final bonus trick. That is, what if you
  838. 38:47change your mind? You don't want five,
  839. 38:50you want to see 10, 12, or eight. What
  840. 38:53then? In this whole formula, any
  841. 38:56numbers used can be parameterized. So,
  842. 39:00for example, instead of the number five
  843. 39:04here, I can point to this cell, I12.
  844. 39:09Then, it will read that five from there
  845. 39:12. So, I go here, remove the five, and
  846. 39:16select cell I12; my output won't change
  847. 39:19. But now, if I put eight in place of
  848. 39:22five, you can see that it automatically
  849. 39:24shows the salaries of eight people.
  850. 39:26Similarly, if I put three, the top
  851. 39:28three will appear. If I put 12, the top
  852. 39:2912 will appear. So, we are making the
  853. 39:33TAKE function dynamic. Likewise, we can
  854. 39:37use a parameter to control these
  855. 39:39outputs using a single cell. Alright,
  856. 39:43let's move to the next concept. The
  857. 39:46fifth concept is to list all
  858. 39:49departments and also count how many
  859. 39:52there are. So, if we remember the first
  860. 39:55one, in that, we calculated the
  861. 39:57headcount and average salary for each
  862. 39:59department. What do we do for this? For
  863. 40:02the actual department list, we went
  864. 40:05into the data, copied this department
  865. 40:07column, pasted it here, and then
  866. 40:10removed the duplicates from it. Suppose
  867. 40:14I want a list of all existing
  868. 40:17departments directly without this
  869. 40:19nonsense; for example, if I want to
  870. 40:22bring everything, I can directly type "
  871. 40:25Staff Table Department" here, and it
  872. 40:28will list all 120, 260 departments
  873. 40:31exactly as they are. I don't want all
  874. 40:34of them; I need to remove these
  875. 40:36duplicates from this list. For example,
  876. 40:38if this Business Development is
  877. 40:39repeated twice, I need to remove them.
  878. 40:41Here is another one that should be gone
  879. 40:43. Similarly, this Legal department is
  880. 40:44repeating, it should be removed. So, if
  881. 40:46I want to remove the repetitions and
  882. 40:48get each one individually, there is a
  883. 40:50function called UNIQUE for this "Staff
  884. 40:52Department." This is a new function
  885. 40:55available only in Excel 365. If I pass
  886. 40:59it to this, it removes the duplicates
  887. 41:01from my departments, and the remaining
  888. 41:04data comes exactly as it is. So, this
  889. 41:07is the UNIQUE function. You can use the
  890. 41:09UNIQUE function in various ways. It is
  891. 41:11very useful, and it will give you the
  892. 41:14list. Suppose you want this list in
  893. 41:17alphabetical order, I can send the
  894. 41:19UNIQUE function into SORT. SORT UNIQUE
  895. 41:22means Accounting and Business
  896. 41:23Development will come on top, and
  897. 41:25Support and Training will come below.
  898. 41:28So, this is UNIQUE. After doing this,
  899. 41:31you might think, "Okay, this is fine,
  900. 41:33but I also want to count how many
  901. 41:35departments are actually there," for
  902. 41:36which we can use the COUNTA () function
  903. 41:38. The COUNT function only counts
  904. 41:41numbers. Since we have text values here
  905. 41:44, we use COUNTA. COUNTA means it counts
  906. 41:46everything, all of it. So, I want to
  907. 41:49count how many values are here using
  908. 41:53COUNTA. The problem here is that we
  909. 41:57know where it begins. Our values begin
  910. 42:00with cell B4, but we don't know how
  911. 42:03long it will be. We will have as many
  912. 42:06values as there are departments because
  913. 42:08this is all coming from the UNIQUE and
  914. 42:10SORT functions. So, we know the
  915. 42:12beginning but not the end. There is a
  916. 42:15special operator for this. Whenever you
  917. 42:18have a formula that spills and creates
  918. 42:21values, if you put B4 followed by a
  919. 42:24hash (#), it will grab the entire spill
  920. 42:27range. Whatever amount is here, the
  921. 42:30count will be the same. So, COUNTA of
  922. 42:32B4 hash will give me 12. Because
  923. 42:35currently, we have 12 departments.
  924. 42:38Generally, in these organizations,
  925. 42:40every four to six months, they do some
  926. 42:42internal reorg and keep changing
  927. 42:44everything. So, for example, let's
  928. 42:47assume a reorg happened in our
  929. 42:49fictional setup as well. Let's assume
  930. 42:51some of these Business Development
  931. 42:52people were moved to Business Growth.
  932. 42:54So, I'm renaming this department to "
  933. 42:57Growth." If you remember, this "Growth"
  934. 42:59category wasn't there before; it's a
  935. 43:01new addition. Meaning, we have created
  936. 43:04a new department. Now let's see, if I
  937. 43:07go here, "Business Growth" has
  938. 43:10automatically appeared, see? Not only
  939. 43:13that, the count has also updated since
  940. 43:17our list is now 13. So, we don't need
  941. 43:20to touch our formula ever again. Once
  942. 43:22set, it updates automatically, whether
  943. 43:25it's sorting or counts. That's why
  944. 43:28using these new features saves us a lot
  945. 43:31of time. So, if you have Excel 365,
  946. 43:35definitely use these techniques. Like
  947. 43:38using the hash symbol, as well as SORT
  948. 43:40and UNIQUE functions. I have a small
  949. 43:42homework assignment for you here. What
  950. 43:44if I don't want all these departments
  951. 43:47in individual cells, but rather in a
  952. 43:50single cell separated by commas? How
  953. 43:54would you do that? Think about it and
  954. 43:57try to solve it. You will discover how
  955. 44:00to do that as we move further through
  956. 44:03our setup. But pause here for a moment
  957. 44:06and try it yourself. Alright, let's
  958. 44:08move on to the next concept. That is
  959. 44:10lookup functions. These are some of the
  960. 44:13most important functions in Excel. In
  961. 44:16fact, my first video on my Telugu
  962. 44:19channel was also about lookups. Since I
  963. 44:22have already introduced lookups there,
  964. 44:24I won't bore you with all those details
  965. 44:26again. I will explain what they are and
  966. 44:30give simple examples; if you want, you
  967. 44:31can watch that video to get some
  968. 44:33context. I think it uses slightly
  969. 44:35different data. But for example, I want
  970. 44:37to perform a lookup. What is it? I have
  971. 44:39this much data. I don't need all of it.
  972. 44:42For instance, I want to find the salary
  973. 44:46of our favorite Subbarao. How? There
  974. 44:50are multiple methods for this. One is
  975. 44:53to go here, apply a filter, and search
  976. 44:55for "Subbarao." There is his row:
  977. 44:59salary 10,675, working in Product
  978. 45:02Management, joined on this date. I get
  979. 45:05all this information clearly, and he is
  980. 45:07working in Hyderabad. But this is a
  981. 45:09manual method. This isn't efficient
  982. 45:11because I need Subbarao now, but
  983. 45:13tomorrow I might need Rangarao. So,
  984. 45:15instead of manual work, how do we use
  985. 45:17formulas to lookup? There are various
  986. 45:20lookup functions. The main functions
  987. 45:23are VLOOKUP and XLOOKUP, so I will show
  988. 45:26you how to use both. For this whole
  989. 45:29thing, as a simple example, I want to
  990. 45:31search using an employee ID. I put
  991. 45:34PR03886 here, but let's actually use
  992. 45:37our Subbarao's ID. So, that's
  993. 45:42Subbarao's ID; I will paste it here,
  994. 45:45and I want that employee's first name,
  995. 45:48last name, department, and salary. I
  996. 45:53will explain how to do it with VLOOKUP,
  997. 45:55how to do it with XLOOKUP, and what the
  998. 45:57advantage of each function is. After
  999. 46:00that, I will also explain INDEX MATCH.
  1000. 46:03So, how does the VLOOKUP function work?
  1001. 46:08VLOOKUP asks what you are looking for
  1002. 46:11—what is the lookup value? Where are
  1003. 46:16you looking? Since it is in a table, I
  1004. 46:20will come here and directly type "staff
  1005. 46:24." Once you have looked it up, what is
  1006. 46:28the output column number you need? If
  1007. 46:31we look here, Employee ID is the first
  1008. 46:34column, so VLOOKUP always works based
  1009. 46:36on the first column. So, since the
  1010. 46:39staff data is here, it will look up
  1011. 46:41based on the Employee ID. After looking
  1012. 46:43it up, which column do you need? This
  1013. 46:46is one, we need the first name, so we
  1014. 46:48need two. So, we type "2" here. Next,
  1015. 46:55the last part is also important: you
  1016. 46:57need an exact match, so for this last
  1017. 46:59argument, we set it to "false" almost
  1018. 47:0199%of the time. Once I press enter here
  1019. 47:05, I get the first name of the person
  1020. 47:07with that ID. So, that is VLOOKUP. Let
  1021. 47:11me explain it to you visually once.
  1022. 47:13VLOOKUP acts like a finger. This finger
  1023. 47:18keeps scanning along like this. It goes
  1024. 47:21until it finally finds where Subbarao
  1025. 47:24is; once it reaches Subbarao, our index
  1026. 47:27finger stops here, moves to the second
  1027. 47:30column, and returns that value. So, a
  1028. 47:34simple analogy is that VLOOKUP moves
  1029. 47:37along like a finger. It goes to the
  1030. 47:41value, takes it, and returns that value
  1031. 47:45to us. So, that is VLOOKUP. If I want
  1032. 47:48the last name, I use the same formula,
  1033. 47:52copy and paste it as is, and replace "2
  1034. 47:56" with "3"; the last name will appear.
  1035. 48:00Generally, instead of changing it every
  1036. 48:04time to get 2, 3, or 4, we can maintain
  1037. 48:08numbers on the side and point directly
  1038. 48:11to that cell. Because of this, if I
  1039. 48:15drag it directly, I will get the others
  1040. 48:17as well. Why didn't it work? It didn't
  1041. 48:21work because before dragging this
  1042. 48:24formula, we left cell C4 as relative.
  1043. 48:28That's why when I dragged it, C4 became
  1044. 48:30C5. So, I will lock C4. To lock it as $
  1045. 48:35C $ 4, select C4 and press the F4
  1046. 48:38function key, which will add the dollar
  1047. 48:41signs. Now, if I drag this down, I get
  1048. 48:45Subbarao's department, his salary, and
  1049. 48:48everything else. This VLOOKUP is a very
  1050. 48:52powerful function. Everyone learning
  1051. 48:56Excel learns VLOOKUP first without fail
  1052. 48:58, and it's a very useful, practical
  1053. 49:00function because most of the time while
  1054. 49:03working with data, questions like "
  1055. 49:05where is this,""what is the
  1056. 49:06corresponding value," and "what is this
  1057. 49:09or that" keep coming up, though there
  1058. 49:11are some problems with VLOOKUP. So, one
  1059. 49:14problem is that you must set it to "
  1060. 49:17false." If you accidentally forget to
  1061. 49:20do that, you will end up with some
  1062. 49:22garbage value. For example, that ID
  1063. 49:24doesn't belong to Vasu, so why is it
  1064. 49:27saying Vasu? Because we forgot to enter
  1065. 49:29"FALSE". If you forget to enter "FALSE"
  1066. 49:31, it does an approximate match and
  1067. 49:33returns some nonsense. That is why you
  1068. 49:35get the wrong result. So you must
  1069. 49:37remember this; if you forget it, you
  1070. 49:38will get the wrong result. That is one
  1071. 49:40problem. The second problem is, if
  1072. 49:44VLOOKUP cannot find the value, it shows
  1073. 49:47an error. So, for example, instead of
  1074. 49:514058, let's say 4057; that person
  1075. 49:53doesn't exist in our data, so I
  1076. 49:55automatically get these errors. That is
  1077. 49:58another problem, and it doesn't end
  1078. 50:00there; there are a few more problems.
  1079. 50:03Another problem is that we can search
  1080. 50:06by ID to get the name, but for example,
  1081. 50:08suppose I have the last name. I know
  1082. 50:12his last name, which is Tuxwell; if I
  1083. 50:14want to know his ID, department, or
  1084. 50:16salary, I cannot use VLOOKUP. Because
  1085. 50:20the syntax of VLOOKUP is, as you can
  1086. 50:22see here, we only specify what value we
  1087. 50:28are looking for, which table, and which
  1088. 50:31column we want. So, whatever table I
  1089. 50:34specify here, it will only look in the
  1090. 50:37first column of that table. Because the
  1091. 50:40first column in our staff table is
  1092. 50:42Employee ID, it can only search based
  1093. 50:44on Employee ID. If I ask it to search
  1094. 50:47in this middle Last Name column in the
  1095. 50:49staff table, find Tuxwell, and then go
  1096. 50:51to the other side to bring the Employee
  1097. 50:53ID, it cannot do that; it will get
  1098. 50:55confused. So that is one more
  1099. 50:58limitation of VLOOKUP. There are some
  1100. 51:01more limitations, but basically, these
  1101. 51:03are the main ones. To fix all these and
  1102. 51:06add some additional power, Microsoft
  1103. 51:08created XLOOKUP. This XLOOKUP is a
  1104. 51:11newer function; it is available in
  1105. 51:14Excel 365 and also in the Excel 2021
  1106. 51:16version. It will not be available in
  1107. 51:18any older version than that. Also, in
  1108. 51:20Excel Online, as I showed you earlier
  1109. 51:22with Excel.new, where you can practice,
  1110. 51:24it is available there too. So, if you
  1111. 51:27are learning Excel newly, my suggestion
  1112. 51:29is to learn XLOOKUP directly; don't
  1113. 51:32waste your time with VLOOKUP and use
  1114. 51:34this instead. But sometimes, even if we
  1115. 51:38have the new version, the client we
  1116. 51:40deal with or some colleagues in the
  1117. 51:42office might have an older version.
  1118. 51:45That is why it is important to know
  1119. 51:48VLOOKUP. But for those learning today,
  1120. 51:50it is better to start their journey
  1121. 51:52directly with XLOOKUP. So, I will show
  1122. 51:55you how to solve this exact problem
  1123. 51:57with XLOOKUP, and then I will also tell
  1124. 51:59you how to solve these other problems.
  1125. 52:01The syntax of XLOOKUP is somewhat like
  1126. 52:06VLOOKUP, but there is a slight change.
  1127. 52:10Lookup value is the same, lookup array
  1128. 52:13means where to search; so where to
  1129. 52:16search? Search in the staff table's
  1130. 52:20Employee ID column. After searching,
  1131. 52:23what should it return? Provide the
  1132. 52:25staff table's First Name column. This
  1133. 52:29doesn't have that "FALSE" nonsense; by
  1134. 52:31default, XLOOKUP always does an exact
  1135. 52:33match. And it will give it perfectly.
  1136. 52:39For other things, like the last name, I
  1137. 52:43will just copy this formula, paste it,
  1138. 52:47and in place of the First Name, I will
  1139. 52:51put Last Name. An extra slash was added
  1140. 52:59. For the packet, okay, so the last
  1141. 53:07name will come, for the product
  1142. 53:10department, the department will come in
  1143. 53:13the department place, and the salary
  1144. 53:16will come in the salary place;
  1145. 53:19everything will appear. So, that is
  1146. 53:23XLOOKUP. Lookup is very simple. You
  1147. 53:25just state what you are looking for,
  1148. 53:27which column you are looking in, and
  1149. 53:29which column you need. Once we tell it,
  1150. 53:31those values come automatically.
  1151. 53:33XLOOKUP has some more superpowers. For
  1152. 53:36example, instead of writing these four
  1153. 53:39columns one after another, what we can
  1154. 53:43do is, since there are four here like 2
  1155. 53:46, 3, and so on, I can use XLOOKUP for
  1156. 53:49the staff table's employee ID...so,
  1157. 53:51just perform the lookup on the employee
  1158. 53:55ID. When returning the result, if I
  1159. 53:58want first name, last name, let's add
  1160. 54:01gender too, gender, department, and
  1161. 54:03salary, all five columns in order, I
  1162. 54:06can just say staff table, open bracket,
  1163. 54:09from first name...Up to salary, if I
  1164. 54:13say it like this, I am telling it to
  1165. 54:16bring all these columns when returning
  1166. 54:19the result, from here to here. So, if I
  1167. 54:23enter this, I get his full record
  1168. 54:26sideways like this. If you don't want
  1169. 54:30it sideways and want it vertically, we
  1170. 54:32can send this to another function,
  1171. 54:34which is the TRANSPOSE () function.
  1172. 54:37This TRANSPOSE function flips the data.
  1173. 54:41By turning rows into columns and
  1174. 54:43columns into rows, I get it like this.
  1175. 54:46This is one technique. But, what if I
  1176. 54:48don't need gender and only want these
  1177. 54:50two...how can I do that? I'll leave
  1178. 54:53that to your imagination. I have
  1179. 54:55already told you this answer in a
  1180. 54:57different context. So, try to imagine
  1181. 55:00what that is, connect the dots, and
  1182. 55:02solve it. Alright, now let's move to
  1183. 55:04the next problem. Another advantage of
  1184. 55:06XLOOKUP () is, what if the value is not
  1185. 55:09there? For example, if there is a 57, I
  1186. 55:12get an error. An error will come in
  1187. 55:14VLOOKUP as well. But how can we solve
  1188. 55:17the error? It means XLOOKUP has another
  1189. 55:19optional parameter, which is 'if not
  1190. 55:21found'. Actually, it has many
  1191. 55:24parameters, but 'if not found' is the
  1192. 55:27next one. So, we can return a value
  1193. 55:30like "Not Found" if it is not found.
  1194. 55:35Then, if it doesn't find it, it will
  1195. 55:37say "Not Found". I will write "Not
  1196. 55:40Found" here as well. How can we do this
  1197. 55:45in VLOOKUP? In VLOOKUP, when such an
  1198. 55:47error occurs, we use the "IFERROR"
  1199. 55:49function. If I put IFERROR around
  1200. 55:52VLOOKUP and add "Not Found", I will get
  1201. 55:56that result too. Here, IFERROR, VLOOKUP
  1202. 56:02, "Not Found". So, if there is an error
  1203. 56:08, I get the "Not Found" message. If
  1204. 56:10there is no error, meaning if that
  1205. 56:12person exists, his details will appear.
  1206. 56:15So, with XLOOKUP, we can mention "Not
  1207. 56:17Found" directly, whereas with VLOOKUP,
  1208. 56:19you have to use another function like
  1209. 56:22IFERROR to handle that error. So that
  1210. 56:24is another advantage. XLOOKUP has a few
  1211. 56:28more advantages, and you will
  1212. 56:29understand what they are in the next
  1213. 56:31example. The next example is about how
  1214. 56:34to go by using the last name. As I said
  1215. 56:37, this won't work with VLOOKUP because
  1216. 56:39the limitation of VLOOKUP is that it
  1217. 56:40can only look to the right. Or, if it
  1218. 56:43looks up the last name, it can only go
  1219. 56:46in that direction, sorry, it can only
  1220. 56:48go forward, it cannot go backward, so
  1221. 56:51it has that limitation. But if I want
  1222. 56:55to search by last name and get the
  1223. 56:57employee ID, department, and salary for
  1224. 56:59that last name, we use another
  1225. 57:01technique called INDEX MATCH. To use
  1226. 57:05INDEX MATCH, first we look for where
  1227. 57:07the last name is located. If you look
  1228. 57:11at the data here, if we search for "
  1229. 57:13Tuxwell," it is in the third row.
  1230. 57:15Actually, there are duplicates, you see
  1231. 57:18there are two Tuxwells, but basically
  1232. 57:20it's in the third row, so I will take
  1233. 57:23the first one. For this, we use the
  1234. 57:25MATCH function. MATCH "Tuxwell" in the
  1235. 57:29staff table's last name column, and
  1236. 57:32we'll set the last option to zero for
  1237. 57:35an exact match. By default, it does
  1238. 57:39something like "less than," so we must
  1239. 57:40put zero. If we apply this, it will say
  1240. 57:44that Tuxwell is in the third position.
  1241. 57:48Basically, it counts as it goes down.
  1242. 57:51Since Tuxwell is the third one, it says
  1243. 57:54three. I will replace Tuxwell with
  1244. 57:57Veeravasarapu, so we can look a bit
  1245. 57:59further down. So, this person
  1246. 58:05Veeravasarapu is in the 12th position.
  1247. 58:08As we go down, it's 12. So now that I
  1248. 58:11know it's 12, the next step is to find
  1249. 58:14out what the employee ID is in the 12th
  1250. 58:17position; since the last name is 12,
  1251. 58:20the ID must also be in 12, so I need
  1252. 58:23the ID. For this, we use the INDEX
  1253. 58:28function; if we point the INDEX of the
  1254. 58:31staff table's employee ID to that 12, I
  1255. 58:35will get the employee ID of the person
  1256. 58:38with that last name; this is a
  1257. 58:41round-about way, we use MATCH here and
  1258. 58:44INDEX there, we combine this into one
  1259. 58:47formula called the INDEX MATCH formula.
  1260. 58:52In that, instead of pointing separately
  1261. 58:55, we can write the MATCH directly here:
  1262. 58:59MATCH of this last name, staff table
  1263. 59:02last name, zero. I will set this to an
  1264. 59:05absolute reference as well. So, I get
  1265. 59:07their details directly. I will fill the
  1266. 59:11same formula in these two cells, let's
  1267. 59:16do the department here and the salary
  1268. 59:20here. So, we clearly get these three
  1269. 59:24values for the person with that last
  1270. 59:26name. This is called INDEX MATCH. Since
  1271. 59:29VLOOKUP once had this limitation, INDEX
  1272. 59:32MATCH used to be very popular. Everyone
  1273. 59:35used to learn VLOOKUP first, then
  1274. 59:37realize it had this problem and learn
  1275. 59:40this as well. But after XLOOKUP arrived
  1276. 59:42, all these problems became irrelevant.
  1277. 59:44Because if you look at the syntax of
  1278. 59:46XLOOKUP, you can provide three things:
  1279. 59:48what we are looking for, where we are
  1280. 59:50looking, and what we need. So, even if
  1281. 59:53I have the last name, I use XLOOKUP;
  1282. 59:57XLOOKUP, sorry, XLOOKUP, last name,
  1283. 1:00:00where to search? Search in the staff
  1284. 1:00:05table last name column. What do I want?
  1285. 1:00:08After searching, I want the staff table
  1286. 1:00:10employee ID. That's it, it will come
  1287. 1:00:14directly, so the same formula can tell
  1288. 1:00:17me the ID if I have the name, or the
  1289. 1:00:19name if I have the ID. So I can use one
  1290. 1:00:23formula for both, which is a great time
  1291. 1:00:25saver, as I don't have to keep using
  1292. 1:00:27different formulas repeatedly. That is
  1293. 1:00:29why XLOOKUP is a better option to learn
  1294. 1:00:32and understand. Once we are done with
  1295. 1:00:36this, we can just put what we need here
  1296. 1:00:39at the end. So, that is why not many
  1297. 1:00:43people are using Index-Match that much
  1298. 1:00:45anymore. But, while learning, you might
  1299. 1:00:49have to deal with different situations,
  1300. 1:00:53so it is important to learn both. My
  1301. 1:00:56recommendation is to master XLOOKUP
  1302. 1:00:59first. Here, I am giving you another
  1303. 1:01:02small challenge, which is...print the
  1304. 1:01:05full record of this person,
  1305. 1:01:06Veeravasarapu, here. Also, print that
  1306. 1:01:10record vertically as well. Again, use
  1307. 1:01:12the techniques here and practice.
  1308. 1:01:15That's it for VLOOKUP, Index-Match,
  1309. 1:01:19XLOOKUP, and IF-ERROR. Alright, there
  1310. 1:01:22is one more small bonus homework here.
  1311. 1:01:25That is: Find all employees with a
  1312. 1:01:29100,000 salary. So, one limitation is
  1313. 1:01:33that whether it is VLOOKUP, Index-Match
  1314. 1:01:35, or XLOOKUP, any of these three
  1315. 1:01:37techniques has a limitation. That is,
  1316. 1:01:41if you have multiple people, you will
  1317. 1:01:43only get the first person's data. Now,
  1318. 1:01:45if you look at this person
  1319. 1:01:48Veeravasarapu, if I filter his data,
  1320. 1:01:52there is only one person. But this
  1321. 1:01:55Tuxwell is not like that. There are
  1322. 1:01:59three Tuxwells here. Different
  1323. 1:02:01departments, different salaries. Now,
  1324. 1:02:04if I want the second person among these
  1325. 1:02:07three, or if I want the third person,
  1326. 1:02:09can I get them? I cannot. Because if I
  1327. 1:02:12look for Tuxwell here, the first person
  1328. 1:02:18will automatically come up. Because
  1329. 1:02:19what do these lookup functions do? They
  1330. 1:02:22keep searching. They search and as soon
  1331. 1:02:25as they find it, they stop. Once found,
  1332. 1:02:28they don't start searching again; they
  1333. 1:02:29stop right there. So, that is why the
  1334. 1:02:32first person PR 013 this number came up
  1335. 1:02:36. That salary comes up, 189,696; if we
  1336. 1:02:38look at the second one, they have a
  1337. 1:02:40salary of 85,000. In fact, the ID is
  1338. 1:02:42also the same; it's a bit of a data
  1339. 1:02:44quality problem. But I couldn't get to
  1340. 1:02:46the second one. So that is the
  1341. 1:02:50limitation with VLOOKUP, Index-Match,
  1342. 1:02:52and XLOOKUP, but if I want everyone
  1343. 1:02:55with 120,000 or all the Tuxwells, how
  1344. 1:02:58would I do that? Think about how to do
  1345. 1:03:02this as homework and tell me in the
  1346. 1:03:04comments. So, I have completed the
  1347. 1:03:08basic examples here. The next three are
  1348. 1:03:12complex problems. Let's move on to
  1349. 1:03:15those. Our eighth example is a complex
  1350. 1:03:18formula. In fact, these three, the
  1351. 1:03:21remaining three, are also complex
  1352. 1:03:23formulas. So, the eighth one is: What
  1353. 1:03:25is the name of the person with the
  1354. 1:03:27highest salary? We want to find that
  1355. 1:03:30out. So, we previously saw what the
  1356. 1:03:33highest salary was here in the fourth
  1357. 1:03:36question. This is MAX, the maximum of
  1358. 1:03:39the salary. But here, we only know the
  1359. 1:03:41amount, we don't know who the person is
  1360. 1:03:43. So now, we want to find out who that
  1361. 1:03:46person is. Actually, if you remember,
  1362. 1:03:49the highest salary of 120,000 is shared
  1363. 1:03:51by two people. So, for the eighth one,
  1364. 1:03:54there are two sections: one is who the
  1365. 1:03:57first person is, and the other is who
  1366. 1:03:59all the people are, separated by commas
  1367. 1:04:01. So let's move on to this. First, what
  1368. 1:04:05we need to do is use XLOOKUP because we
  1369. 1:04:07need the person's name, so let's write
  1370. 1:04:10XLOOKUP and look up the highest salary.
  1371. 1:04:13We will calculate the highest salary
  1372. 1:04:17here using MAX of the Staff table
  1373. 1:04:20salary column. So, the first part of
  1374. 1:04:24XLOOKUP generated 120,000 for me; if I
  1375. 1:04:28point to it, I can see it in the
  1376. 1:04:31tooltip. Once it finds this 120,000 in
  1377. 1:04:35the staff salary column, we want that
  1378. 1:04:39person's name. We don't just want the
  1379. 1:04:42first name. We want the full name,
  1380. 1:04:44combining the first and last name
  1381. 1:04:45together. So, I will show you another
  1382. 1:04:47trick for that. Here, we take the staff
  1383. 1:04:49table first name...since just the first
  1384. 1:04:51name isn't enough, we need the last
  1385. 1:04:54name too. So, we use an ampersand, then
  1386. 1:04:57a space inside double quotes, and
  1387. 1:05:00another ampersand—meaning after the
  1388. 1:05:04first name, add a space, then the staff
  1389. 1:05:07last name. This will find those with
  1390. 1:05:12the highest salary in the column and
  1391. 1:05:15print "Minerva Ricard" directly here.
  1392. 1:05:19Let's go into the data and verify if
  1393. 1:05:21this is correct. If I go down here and
  1394. 1:05:25select 120,000, I get the name Minerva.
  1395. 1:05:30In fact, if you see here, it's not just
  1396. 1:05:33Minerva; we also have Mick. Sometimes,
  1397. 1:05:37when we ask who sold the highest or
  1398. 1:05:40lowest products, there are multiple
  1399. 1:05:43people involved. So, if I want both
  1400. 1:05:46these names together as a
  1401. 1:05:48comma-separated list instead of just
  1402. 1:05:51one, let's see how to do that next. For
  1403. 1:05:54just one, this works, but since there
  1404. 1:05:56are obviously multiple, let's see how
  1405. 1:05:58to do it. For this, we will use the
  1406. 1:06:00FILTER function. We won't use XLOOKUP
  1407. 1:06:01anymore. What do we need to filter
  1408. 1:06:03using the FILTER function? We need to
  1409. 1:06:06combine the first name and last name,
  1410. 1:06:08so Staff table first name, ampersand,
  1411. 1:06:16and the Staff table last name. Up to
  1412. 1:06:21this point, it will create a column by
  1413. 1:06:23combining the first and last names.
  1414. 1:06:25What is that? We need to include the
  1415. 1:06:28condition where Staff salary equals the
  1416. 1:06:31MAX of the Staff salary. So, as you can
  1417. 1:06:36see here, we write the condition like
  1418. 1:06:38this. If you only run the filter, it
  1419. 1:06:41gives both names vertically in a spill
  1420. 1:06:43range, one in each cell. Actually, we
  1421. 1:06:46don't want it like that; we want both
  1422. 1:06:49names separated by a comma in a single
  1423. 1:06:51value. To do this, after the filter
  1424. 1:06:54work is done, there is a function
  1425. 1:06:56called TEXTJOIN. TEXTJOIN can join
  1426. 1:06:59multiple items together. Since we want
  1427. 1:07:02a comma between them for this TEXTJOIN,
  1428. 1:07:04I will provide a comma and a space as
  1429. 1:07:05the delimiter. There is a parameter
  1430. 1:07:08called 'ignore empty'; we will ignore
  1431. 1:07:10that for now. I will close the filter
  1432. 1:07:13at this point. This is a complex nested
  1433. 1:07:15formula. If you look closely, it
  1434. 1:07:17contains multiple items. There is
  1435. 1:07:19TextJoin, Filter, Max, and several
  1436. 1:07:22other items like that. In fact, there
  1437. 1:07:25is also the technical concept of 'First
  1438. 1:07:26', right? Combining First Name and Last
  1439. 1:07:29Name, then comparing directly here, it
  1440. 1:07:32gets a bit complicated. Try it out once
  1441. 1:07:34, and you will get used to it. So, if I
  1442. 1:07:38do this, I will get both their names
  1443. 1:07:41directly. Actually, if I want a
  1444. 1:07:46different condition, let me write
  1445. 1:07:49another one. Instead of salary, I will
  1446. 1:07:57say Department equals Sales. What will
  1447. 1:08:04this do? It will get all the names of
  1448. 1:08:06the people in the Sales department and
  1449. 1:08:08join them into one big list for me. As
  1450. 1:08:11you can see, when we have such a long
  1451. 1:08:13list, we might think, it would be
  1452. 1:08:15better if it were in alphabetical order
  1453. 1:08:18. In that case, we can send this list
  1454. 1:08:22to 'Sort' after filtering, and then
  1455. 1:08:25join the text, giving us an A-to-Z
  1456. 1:08:28order. Like Antoinette, Cary, Fidela,
  1457. 1:08:33Freida, Joaquin, John, and so on. So,
  1458. 1:08:36if you add a few extra techniques to
  1459. 1:08:40this nesting, you can provide exactly
  1460. 1:08:43what the business needs. That was the
  1461. 1:08:46eighth example; now let me show you the
  1462. 1:08:49ninth. The ninth one is: "All employees
  1463. 1:08:53that joined in the month of March". If
  1464. 1:08:57we look at the data here, I have the
  1465. 1:09:00start dates. Actually, people join
  1466. 1:09:04every single year. I am not concerned
  1467. 1:09:07with the year or the specific day. If
  1468. 1:09:09they joined in the month of March—for
  1469. 1:09:12example, here is someone who joined in
  1470. 1:09:15March 2021; I need them. There aren't
  1471. 1:09:18any others on this screen, they must be
  1472. 1:09:21further down. Oh, look here, there is
  1473. 1:09:25someone who joined in March 2018 and
  1474. 1:09:29someone who joined in 2020. I need all
  1475. 1:09:32of them, meaning any year, as long as
  1476. 1:09:36they joined in March. How do we get
  1477. 1:09:39them? We only need their ID, First Name
  1478. 1:09:42, and Last Name. How do we write this?
  1479. 1:09:44Try it yourself once, pause the video.
  1480. 1:09:46But I will show you my solution. We
  1481. 1:09:49start with 'Filter' as usual, filtering
  1482. 1:09:53the Staff table. Since we need ID,
  1483. 1:09:56First Name, and Last Name, I open
  1484. 1:09:58another square bracket, select ID,
  1485. 1:10:01colon, and Last Name, telling it to
  1486. 1:10:04give me those three columns. Now, what
  1487. 1:10:07is the problem in the 'Include' part?
  1488. 1:10:10We need March, but it could be any
  1489. 1:10:12March. So, we use the 'Month' function
  1490. 1:10:17on the Staff table's Start Date column.
  1491. 1:10:22What this MONTH function does is, it
  1492. 1:10:25looks at the start date and tells us
  1493. 1:10:27what month it is. So, for Jan it is one
  1494. 1:10:30, Feb it is two...likewise, it ignores
  1495. 1:10:32the date and year and extracts only the
  1496. 1:10:34month for us. If we set this equal to 3
  1497. 1:10:37, then everyone who joined in March
  1498. 1:10:39will automatically appear. So, the
  1499. 1:10:42technique you observed here is that you
  1500. 1:10:44don't necessarily have to write a
  1501. 1:10:46condition with just a column. We can
  1502. 1:10:49pass that column into a function like
  1503. 1:10:51MONTH and write a condition based on
  1504. 1:10:54that, like "equal to 3"; this is a very
  1505. 1:10:57powerful and amazing technique. Once we
  1506. 1:11:01understand this, we can easily pull out
  1507. 1:11:03even very complicated data. So, when I
  1508. 1:11:07hit enter, everyone from the batch that
  1509. 1:11:09joined in March appears. You can verify
  1510. 1:11:12if this is correct or not. Using this
  1511. 1:11:15same approach, for example, if I want
  1512. 1:11:18everyone whose first name starts with '
  1513. 1:11:21H', I can fetch that as well. So, let's
  1514. 1:11:24see how we can do that in the next step
  1515. 1:11:27. Let's filter and get all the data.
  1516. 1:11:31Staff, Staff First Name. Instead of
  1517. 1:11:36just "first name" here, if we say "LEFT
  1518. 1:11:39of first name", it will give us the
  1519. 1:11:42leftmost letter of the first name. If
  1520. 1:11:46this is equal to 'H', then basically it
  1521. 1:11:48searches the left character, and if
  1522. 1:11:50it's 'H', then everyone—meaning the
  1523. 1:11:52batch starting with 'H'—will show up.
  1524. 1:11:54See? Here we have Hussain, Hogan,
  1525. 1:11:57Hephsibah, Hemavathi, Hinda, all of
  1526. 1:12:00them! It's that easy. Like this, you
  1527. 1:12:04can write anything; you want those
  1528. 1:12:06starting with 'H', those with a salary
  1529. 1:12:09greater than 1000, or maybe the name
  1530. 1:12:12ends with the letter 'N'...you can
  1531. 1:12:14write whatever you want and easily
  1532. 1:12:17fetch it. So that's the ninth example.
  1533. 1:12:20Our final example is to create a
  1534. 1:12:24department report using complex
  1535. 1:12:28formulas. In this, I am combining
  1536. 1:12:31multiple formulas—everything we've
  1537. 1:12:33used so far—mixing them all up, and
  1538. 1:12:35showing you; plus I'll show you some
  1539. 1:12:38extra techniques and how to do
  1540. 1:12:40conditional formatting. There is also a
  1541. 1:12:43surprise for you. Come, let's do this.
  1542. 1:12:46This is basically like a grand finale,
  1543. 1:12:48a chance to combine everything we've
  1544. 1:12:51learned so far. Here, I want every
  1545. 1:12:54department to appear automatically, I
  1546. 1:12:56want to know how many people are in
  1547. 1:12:58them, I want to know their average
  1548. 1:13:00salary, and I also want to know the
  1549. 1:13:03difference between that average and the
  1550. 1:13:05overall average. And I also need to
  1551. 1:13:08know what the highest salary is in each
  1552. 1:13:11department. There's a small surprise,
  1553. 1:13:13I'll show you that at the end. So first
  1554. 1:13:14, let's get the departments. For this,
  1555. 1:13:17we use UNIQUE (Staff Table Department),
  1556. 1:13:20which gives me every department here. I
  1557. 1:13:24will sort all these departments
  1558. 1:13:26alphabetically; SORT of UNIQUE will
  1559. 1:13:29sort them, and they will come in A to Z
  1560. 1:13:32order. Now, to see the head count for
  1561. 1:13:34all these departments, we can use
  1562. 1:13:36COUNTIFS. COUNTIFS (Staff Table
  1563. 1:13:40Department, is, this spill range B C#).
  1564. 1:13:46This will automatically calculate and
  1565. 1:13:48give me the values for each department.
  1566. 1:13:50Everything is correct. You might be
  1567. 1:13:52wondering why there are only two, or
  1568. 1:13:54why there are fewer people here. That
  1569. 1:13:56is because, in the middle of the video,
  1570. 1:13:58we changed a few names. We changed
  1571. 1:14:00their department name to "Business
  1572. 1:14:02Growth" to test if the UNIQUE function
  1573. 1:14:04works or not. That is why it came out
  1574. 1:14:05like this. So, now that we have it,
  1575. 1:14:07let's calculate the average. The
  1576. 1:14:09average is very simple: AVERAGEIFS.
  1577. 1:14:14Staff table salary column, staff table
  1578. 1:14:19department is B6 hash; the average for
  1579. 1:14:23every department will come out neatly.
  1580. 1:14:28Now, let's say I want to know the total
  1581. 1:14:30number of people overall. I will write
  1582. 1:14:33it above, this is COUNTA of the staff
  1583. 1:14:35employee ID, meaning I...Calculate how
  1584. 1:14:42many IDs there are, and it tells me 260
  1585. 1:14:44. Our total population is 260. Let's
  1586. 1:14:48also calculate the overall average,
  1587. 1:14:50AVERAGE of the staff table salary is
  1588. 1:14:5273,000. So, what I need now is the
  1589. 1:14:57distance of each department from that
  1590. 1:15:00overall average. Here, the difference
  1591. 1:15:03from the overall average is basically
  1592. 1:15:06taking this spill range D6 hash minus
  1593. 1:15:09this number, which gives me the
  1594. 1:15:11distance for each. So, for example, if
  1595. 1:15:14you look here, it is 69,000. This is at
  1596. 1:15:17a negative 4,000 compared to this
  1597. 1:15:1973,000. Whereas, if it is 79,000, they
  1598. 1:15:23are 5,800 above the 73,000 mark. So, by
  1599. 1:15:26calculating the difference for everyone
  1600. 1:15:28like that, the list is automatically
  1601. 1:15:30printed out neatly. Instead of just
  1602. 1:15:33looking at these lists, we can normally
  1603. 1:15:35add a small graphical representation
  1604. 1:15:37for them. So, for that purpose, I will
  1605. 1:15:40use "equals" to bring in this same
  1606. 1:15:42range again. So that I only get these
  1607. 1:15:45numbers. I have preset the formatting
  1608. 1:15:48for these numbers so that negative red
  1609. 1:15:50colors don't appear; now, I select this
  1610. 1:15:53range and add a data bar using
  1611. 1:15:55conditional formatting. What this does
  1612. 1:15:58is print a bar in the cell itself based
  1613. 1:16:00on that number. Negative is red,
  1614. 1:16:04positive is blue; once I manage this
  1615. 1:16:07rule, select "Edit/Manage" and edit it,
  1616. 1:16:10I can change the colors there.
  1617. 1:16:13Basically, first, what I want to do is,
  1618. 1:16:16if you look here, the number appears
  1619. 1:16:18twice. Since we don't need the number
  1620. 1:16:20twice, I will use "Show Bar Only" to
  1621. 1:16:22remove the number; only the bar will
  1622. 1:16:24appear. So, let's do "Show Bar Only".
  1623. 1:16:27Next, let's adjust these colors a bit.
  1624. 1:16:30For positive, I will set this dark
  1625. 1:16:33color, and for negative, I will set
  1626. 1:16:36this one. So, the departments with
  1627. 1:16:41salaries lower than the overall average
  1628. 1:16:43will be clearly visible in red, and
  1629. 1:16:45those that are positive will appear in
  1630. 1:16:48a dark color like this. And that amount
  1631. 1:16:50also appeared here for my reference.
  1632. 1:16:53Now, the highest salary—this is very
  1633. 1:16:58simple: MAXIFS, staff salary, staff
  1634. 1:17:03department is B6 hash. If you look at
  1635. 1:17:08the highest in each department, it
  1636. 1:17:10comes to 120, which is our overall
  1637. 1:17:12highest, and it is in the Engineering
  1638. 1:17:13department. Another person is in
  1639. 1:17:15Services. If you look at the other
  1640. 1:17:18departments, some have 100, some have
  1641. 1:17:20119, and some departments have 99 as
  1642. 1:17:23well. So, that is the overall report.
  1643. 1:17:27Let's also calculate the overall max
  1644. 1:17:30here, the max staff salary. So, that is
  1645. 1:17:34the department report. Now I will also
  1646. 1:17:36show you a surprise. Basically, there
  1647. 1:17:38is no surprise, it's homework for you.
  1648. 1:17:41Calculate what the median salary is for
  1649. 1:17:43each department. Median means what is
  1650. 1:17:46the mid-point of the salary? And what
  1651. 1:17:48is the female ratio? There is a
  1652. 1:17:51headcount of 26, how many of them are
  1653. 1:17:52females, as a percentage of the overall
  1654. 1:17:54, what is that ratio? Calculate these
  1655. 1:17:56two and print them here. This is your
  1656. 1:17:59homework, also print the overall median
  1657. 1:18:01and overall female ratio at the top.
  1658. 1:18:03The reason for this is that we don't
  1659. 1:18:06just use formulas, we mainly use them
  1660. 1:18:09to generate reports like these and
  1661. 1:18:12produce analysis. So, you will get an
  1662. 1:18:15idea of how to think and how to solve
  1663. 1:18:17in this process. So that is it for
  1664. 1:18:21essential Excel functions and formulas,
  1665. 1:18:24from beginner to advanced, I have shown
  1666. 1:18:27you all the formulas I normally use at
  1667. 1:18:30work along with a sample data set. If
  1668. 1:18:34you want to learn anything further, let
  1669. 1:18:37me know in the comments saying these
  1670. 1:18:39are the topics we want to learn, and
  1671. 1:18:41based on that, I will prepare my next
  1672. 1:18:43videos. Okay, I hope you liked this
  1673. 1:18:46video, if you did, make sure to like it
  1674. 1:18:48and share it with at least four friends
  1675. 1:18:51. I will meet you again in another
  1676. 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.