Microsoft Excel 365 Bootcamp: Master working with data in Microsoft Excel 365

1. Introduction: Have you ever ignore something that you use on a daily basis and took it for granted? Have you ever felt that commonplace and ever present pink don't require further introspection. I did so a while back while in college. I didn't make an effort to learn the nuances of Microsoft Excel. One of the most versatile and useful softwares in the world by focus was on learning more advanced softwares for working with data. Sql are Python's data, etc. As a result, by knowledge of Excel was woefully limited. As soon as I entered in this play, I realized my limitations in Excel. Excel is widely used in industry and being adept at it is a must. Realizing my mistake, I said about learning Excel in depth. I don't want you to make the same mistakes as he does. I created this course, which is meant to provide you with a thorough knowledge of Excel applications. Hi, my name is Ricky light heavy, and I am currently a PhD student in Business Administration at the University of London, Eye Research, entrepreneurial finance, and use Excel on a daily basis for some applications. Despite knowing more advanced software such as R and Python, Excel, I feel is comparable to many softwares and there lies its unique value in this course, I'll teach you applications and users of Excel from writing conditional statements to conducting statistical analysis to conducting water for dialysis, VLookups, data visualization, pivot tables and other applications of Excel, which a lot of people don't know about due to the general notion that Excel is just meant for B6 spreadsheet applications. So I request you to give me your full attention and take this course seriously very seamlessly. Enjoy learning. 2. Excel Walkthrough: Tutorial entity, we're gonna do a quick walk-through of excellent, I'm gonna explain the ribbon and what different tabs and the ribbon mean and what they can do. And I'm going to explain some other basic things about Excel. So the first thing we're gonna do is we're going to highlight the workspace. The workspace is rather huge. It's comprises of different cells. And the workspace, as you can see, it's highlighted right now. I'm going to color this in yellow for your benefit so that you get an idea about what the workspaces. So the workspace is contained in the sheet. Please follow my cursor. It has a round green circle around it. And the circle is right now pointing at the sheet. So a workspace is contained in the worksheet. And here in the ribbon on top, please follow my cursor. I'm running my cursor over the different ribbon options you have the home option, it'll hopefully appears to have Cartier copy. You have the font type, you have the font size, bold, italics, underline. You have fill color, you have font color. You have different formatting options, alignment options. Then you have merging options. You can increase or decrease indent, you can do conditional formatting. You can format as a table, you can sort data, you can analyze data, you can filter data. That is what the home option is all about. The home tab is all about. Then the Insert tab has some of the options. You're going to insert a pivot table. You can insert a table, you can insert pictures, shapes, icons, you can insert blot. So you have recommended charts over here, for instance, you can insert different types of jobs right now there are no jobs recommended because I don't have to data in the Excel sheet. You have bar plots, etc. You have different options for different plots. And if you take a look at the shapes you can enter, I'm going to enter a box over here, and you can use that, do that using the Insert option. So that is the box. And you can obviously you can change the fill color. You can do a lot of variety of different things with that. You can also enter us straight line or an arrow. I'm going to enter an arrow. So I'm entering an arrow over here down below the box, so you can do that tooth, then you have the draw option. In the ribbon in drop shouldn't. You can draw something, you can use a pen to draw something. So I'm drawing a squiggle over here. So that is what you can draw using the draw option. It's useful if you're trying to annotate something. You're the page layout option. In the page layout option, you can specify margins, orientation of the page, size of the page, etc. Any of the formulas option in this Formulas tab, you have all different types of functions. All different types of mathematical functions have AutoSum function, sum, average, count numbers, max, min, et cetera. You will have recently used functions that you've used before. Then. You have logical functions are logical tests. If, if error, et cetera, and et cetera. Then you have the text options to work with text. Then you have date and time, then you have lookup reference, malfunctions, math and trigonometric functions, which include functions such as, let's say a cos sine Theta, cos x, etcetera, etcetera. Then you're the data option. And in the deed option you can get data to query. Then you have, again, do stocks, currency data is embodied off and you can work with stocks and currency data. You can sort data in ascending and descending order. You can filter data, you can then do whatever analysis, et cetera. And finally, the other important part of the ribbon and other important tab in the ribbon is a tell me dad, and that's for help. So that is what the entire ribbon is all about. Now, we're going to take a look at the formula bar. If you notice the top bar over which I'm hovering my cursor with the green ring around it. This is known as the formula bar in this value type in formula, right? This is where you type in formula. Now, I'm gonna go back to the draw option and uncheck the pen because the pen was checked. And then I'm gonna go back to the home oxygen or rather Home tab. And I'm going to select the cell. Now let's say we want to enter a formula into this cell. Now, we'll enter a basic formula for demonstration purposes. We'll enter a formula which calculates the summer four plus five. So what you have to do in this formula bar, and as you can see, the formula bar over yet, it's at the top of the workspace and my cursor is hovering over it. You can see the space in the formula bar within the confines of my green cursor or the green ring around my cursor. So you want to find out the sum of four plus five. You write it in the formula bar, you click enter, and as you can see, the result is displayed here in the formula bar is quite important because most of the things we do in Excel, it brutalizes the formula bar. The formula bar, as you can see, has the equals to four plus five formula highlighted in gray inside the green ring around my cursor. That's all for the walkthrough. Thank you so much. 3. Cell Referencing, Work Sheets, and Adding or Deleting worksheets: Etc, some technical stuff or rather let me put it this way, some other Excel jargon. So the spreadsheet that you see on your screen is known as the workspace, and it's contained in sheet number one. Now, I'm highlighting the areas part of the area in light yellow so that you know what a workspace looks like. This workspace has different cells. I'm highlighting one of the cells in orange. The orange cell over here, the reference number, the reference numbers E1. So it's contained in column a and row one. So if you want to find the seller or if you want to reference the cell from another cell, then you have to use the reference A1. E stands for column and one stands for the row number or column number grownup. But that is how you reference a cell. And now you're the sheet over here. If you look at the bottom-left corner of your screen, there's a tab called sheet, control-click or right-click it. I'm going to do that. And you have this pop-up menu where you have different options. You can insert sheets, you can delete, you can rename sheet. Let's rename the sheet. Let's call it test. She does now renamed as test. Now you're going to add a sheet. You can add achieved by control-click or right-click. Right. You can control click on the sheet that is already present. And then you can select the Insert Sheet option. And this will insert a new sheet. Let's rename this sheet. And I'm going to rename it as just being now you can delete the sheet. Let's delete the initial sheet. Yeah, then you should cheat and deleted by control-click or right-click. The pop-up menu appears and you can delete this sheet and click on delete. And you can also copy or move the sheet. And you can copy the sheet, do the same workbook, the workbook reference to the Excel file that you're working on. As you can see, a copy of Desk has been created. So that's all about sheets. Thank you so much. 4. Absolute and Relative Referencing: Hey everyone, welcome to a new lecture on Excel. And today we're going to take a look at absolute and relative referencing. What is that? Let's say we have a value in the orange cell, which is 35. Let's say we have a value in the blue cell, which is 20. What is the sum of 35.20? Do the sum, we have to click on another cell where we're going to store the sum. And then we're going to type equal to on in that cell. Then we're going to select the orange cell, A1. Then we're going to use the plus symbol. Then we're going to select the blue cell B2. We're going to click Enter. The sum is stored in the yellow cell right? Now suppose we have more data than 35.20. Suppose we have theta, which is like this. Ready 31230. So we have data in the cells. I'm coloring in red. The data we, and we want to find out the sums be A2, B2, B3, A3, right? So we want to find out what is the sum between A2 and B2. What do we do then? Well, we can write the form ligand or we can copy the formula and paste it into the cell. I'm coloring in light blue over here, right? So C2. How do we do that? We click on the cell C1, or the cell that is colored in yellow. And then we go to the bottom-right corner, click on the square root symbol, and we drag the selected downwards. So as you can see over here, the formula has been copied into C2 and C3. What is the formula for c2? I'll change the color of the cell and make it light purple. The formula and C2 is A2 plus B2, as you can see Excel that this automatically, right? So in the first cell C1, or this first cell containing the sums of the columns E and B, you had a formula that was A1 plus B1. And highlighting it in gray in the formula bar. Just look at the formula bar over here. I'm highlighting it in gray, A1 plus B1. So you have the formula for the cell C1, C1 equals T1 plus V1. When you copy that formula and pasted it to the cells underneath it, to C2 and C3. The cell references automatically change to A2 plus B2 and B3 plus b3. Excel does this automatically. Suppose you want to copy and paste it somewhere else. Let's paste it over here. The sum is zero, the sum in the sea blue cell is zero, y is zero because the formula is not referenced. Absolute manner is referenced in a relative manner because once you copied the cell and paste the formula in F3, what it did is it took the sum of D3 and E3. I'm highlighting the cells in green. So once you've copied the formula in cell C1, which is A1 plus B1, just follow the formula in the formula bar it and pasted it in cell F3, the cell referencing change. The how can you fix that? Well, you can fix relative referencing problems by using absolute referencing. So how do you do that? Instead of having A1 plus B1? Let's use $1 symbol before a with states that the sum will be calculated for cells in column a. And let's use the dollar symbol before B and column B. So Psalms will be calculated for the cells in column a and column B. So we are using absolute referencing. The sums in cells C2 and C3 remain the same. C2 and C3 cell C2 and C3 are being highlighted purple. So this sums and C2 and C3 remain the same. And what will be the sum in F3 into the cell that is colored in yellow at the moment. Let me change the color here for your convenience. Let's make it light green. Let's make it some other color. Let's make deep blue, right? And I've changed the font color to red. So the sum in F3 is now 40 again, because we have referenced it and be in an absolute fashion. So this tells Excel that wherever you move, you whichever cell you input the formula into it. And B are absolutely referenced and the sum will be calculated for a and B and not some other column. Now, if you want to make certain that the rows are absolutely reference, do you add dollar symbols before the rows? Now what does that do? Now let's copy cell C1 and let's paste it down in cell B9. I'll change the color to light blue. As you can see here, even though I pasted the formula into cell E9, which is far away from where the formula was, since I've absolutely rapid. And to look at the referencing, just look at the formula bar. I'm highlighting the formula bar content in light gray as I have absolutely referenced. The cells drop shipped around. This tells Excel that summer has to be calculated for cells A1 and B1 and not any other son. And if we copy the formula into C2 and C3, then as you can see, the formula does not change. And cell C1 and C2 have the values in orange over here. As you can see, this formula remains. Dollar sign $1 sign $1 sign B, dollar sign one. Write. The formula, does not change. That is absolute. Referencing by using $1 sign years, telling Excel that wherever the formula shifted, the initial formula or the initial cells being used to calculate a certain number or some other operation will remain static. And if you do not add the dollar signs or you do not add dollar signs before a certain row or a certain column, then that tells Excel that the formula is dynamics. So when you copy the formula and paste it into another cell, the formula will change automatically if you are using relative referencing. If you're using absolute referencing, then because of the dollar signs, Excel interprets the formula as static. And even if you copy the formula into another cell, the formula remains the same. That's all. Thank you so much. 5. Insert rows, columns, and cells: Hey everyone, welcome to a new Excel tutorial. And today we're going to take a look at how to insert columns, rows, and cells. So that will add this dataset accompany idea of sales for the company in milling setup the status of the company, whether it is profitable or unprofitable. Let's say I want to insert a new column which stores values about the fixed asset of the company, fixed assets of the company. That can be factories, that can be warehouses for all space, et cetera. I want to insert this column beside sales, column. Beside the sales column, what do I do? I click on the column besides the sales column, sales in millions column, which is column number C. And then I control-click or right-click. So I'm going to control-click or right-click, and I'm going to insert, a new column has been inserted. Similarly, a row can be inserted. Let's say I want to insert a row between company number three and number four. So what do I do? I'm going to click on row four, control-click or right-click, and I'm going to insert a new row. So row has been inserted between company 3M, Company for, let's say I want to insert a cell, I'm going to click on any random cell over yet less than going to click on cell number B3. So this cell going to highlight it in orange. And I'm going to right-click or control-click. Then I'm going to insert. And I can shift the cell to the right. I can ship the cell down. I can add an entire row, I can add an entire column less, ship the cell down. And I have a new cell over here. And likewise, you can ship the cell downright inserted and dy rho less than certain entire row. Insert, insert an entire row, and now you have an entire row that has been inserted. So that's all for this lecture. Thank you so much. 6. Enter Values into Cells: Hey everyone, welcome to a new tutorial on Excel. And today we're going to take a look at how to input values into cells. So the best way to do this is to type on the cell, right? So I clicked on the cell and typed on it. Right. Now I'll just color the cell. I've colored in yellow. And just take a look at how I do this. I type on the cell, I click on the cell and type on the cell. Or you can use the bar over here to type in values. And by bar, I'm referring to the area of the screen which I'm highlighting in green. Just follow my cursor. I'm highlighting this area in gray. So you can use this bar to input cells, input values into cells. So you can use the equal to command and input values. So let's say we want to input a value in the cell. A4 is the column name, and four is the row name or row number is the column number. Four is the row number. So what I'm doing over here is I'm going to the input bar, typing in equal to and then entering a value. That is how you input values into cells. 7. Pasting Cells and Values: Hey everyone, welcome to a new lecture on Excel and Geneva going to take a look at how to best cells. So what you can do is you can right-click or control-click if you're using a map and you can copy yourself or you can cut ourselves. And yet you can paste it using the right-click or control-click in Mac. And as you can see, I've pasted cell A1 into cell A9. Let's paste it somewhere else. Let's paste it. Over here. I'm coloring the cell in yellow, or let me use another color, orange. I'm going to paste it over here using the Beast special command. So let's first copy the value. And then we're gonna go over here and Paste Special. And we can paste the value. In the next lecture, I'm going to talk about absolute and relative referencing. And that's very important when you're pasting cells. But let's say if you want to paste the same value, 35 in all, all the rules underneath G1 or the row m, highlighting an orange. If you want to paste the same value in the rules underneath this particular cell, what you can do is you can click on the click on the bottom right corner of your cell and drag it downwards is fine. The square little oxygen over here, as you can see, there's a square option over here. There's a square. You click on the square and pull it down. And you can copy all the values into the cells underneath this particular cell. And you can do it horizontally too. So you just click on the cell, go to the bottom right corner, click on the square button, and then drag the selector to the point up to which you want to copy the value. Thank you so much, That's all for this lecture. 8. Importing files into Excel: Hey everyone, welcome to a new tutorial on excellent. Today we're going to take a look at how we can import data. So to do that, we have to go to file. If you follow me on the screen. And if you can follow my cursor, go to File, go to Open. Click on Open. Then using open, go to your search window. And from there you can choose a file. And as you can see, I have inputted a file, or rather I've opened a file in Excel. And this is a biolab opened. It has ID, sales and price. I'm highlighting the entire area in yellow. So that is how you insert a file in the excellent, How do you see if you go to Excel or rather you go to the File option in Excel and click on Save. And that is how you save files and Excel. 9. Power Query and cleaning up data: Hey everyone, welcome to your new Excel tutorial. And today we're going to take a look at importing data and cleaning up data. So how do you import data? Well, you can go to the File option over here and import. But another way of importing data is using Power Query. So we're going to use power Query to embody that I'm going to import from file by data sets are stored in a folder in my computer. So I'm going to import from there. And I'll go to Import Excel workbooks. So I have data on sales figures of some automobile companies and the automobile companies, our Tesla, Ford, and Chrysler. The data is stored in Sheet1 of that particular word book. And I have the revenue and billion for desolate border will have the revenue in billions for Ford in quarter two, quarter three for Chrysler in waterfall, and other cost and bullied for each company and the date the company was established and the dated became profitable. So let's load this data. Now, what can you do with this data, as you can see in the first column, whether labels that are being displayed, you have the filter option over here. So let's use the filter option first. Let's read your sort in descending order. As you can see, the data has been sorted in descending order. Now let's filter some data. Let's see how the filter works. We are going to filter data based on some logical rule. You can filter by color. If the cell color is red, you can filter that data. If it's yellow, it can filter the data. Let's use the logical rules to filter some data if the data is greater than 12, in this case, sorry, let me go back. Let's go to the revenue in billions. So if the revenue is greater than 12, then you're going to filter the data. Now, as you can see over here, Tesla had a revenue of billions and it has been printed. So Tesla had a revenue of 12 billion. Highlighting this solid orange, add your field, delete all data that is greater than 12, right? So Tesla is dropped from the dataset. Let's use another filter. Let's go back to the original dataset. Let's remove the highlight. Let's use another filter. If cost is less than 12, then we eat data. So if the cost and billions is less than adult, then we keep that particular company in the dataset. Now what we can do is we can change this if the cost of billions is greater than 12. Dead, we keep the companies in the dataset, as you can see, grasslands, the cost of 16000000003rd has a cost of 23 billion. So they are in the dataset. You can add AND, or, OR operators do this. You can have two rules. Let's add two rules. So if this case, as you can see, cost is greater than dove and less than 13 dead. You'll keep the or less than 20 lesson. As you can see in this case, delta has a cost and billions of 23. So if we apply the previous rule, greater than 12 or lesser than 30, then the only data point that we'll rebate, or greater than 12, less of a grade D. Let's use 20. The only observation that we'll rebate is priceless because Chrysler has a cost that is greater than dove and lesser than 20. Let's use our. Now over here, you have two rules. If cost is greater than two or if cost is less than 20. As you can see, the cost of Chrysler, the cost and billions of Chrysler is greater than total cost and billions of dirt as greater than total cost. And billion of Ford is 12, and Tesla is one. But they are also less than 2012 and water is less than 20. So all the four observations were made like this. You can filter data. What else can you do? As you can see over here, we have not defined what sort of data these observations are for is just what is quarter? Quarter is type data. So let's divide. It is time. Quanta is time data. Let's define it as day. Follow my cursor, go to the bar where the options for defining datasets are. They defined it as tight. Right? Now. You have revenue in billions. You can define it as currency, or you can divide a desk. I counted. The dollar sign is appearing before it costing billions. You can define it as a counting. The data established, you can define as short date. Deed profitable. We can define us sharp D. Now as you can see over here, we have something in this dataset that looks B. That is, someone has made the mistake of associating the company name with the company id. So the company name and the company id are present in one column. Because over here as you can see, the company id is followed by the company name with a dash in between. So you have the company id, which is 546 for Chrysler, followed by a dash, followed by the name of the company, chrysler. We want this in separate columns. How do we do this? Let's first select this. Now, we are going to go to data. And what we're gonna do is we're gonna go to the option called text to columns. As you can see over here, the dataset, the column has been important and we're going to click on Delimited. So how are these observations delimited? What sort of characters or dead between different information? So the first piece of information is company idea. The second piece of information is company Dave, and there's a dash between it, so it's delimited with a dash. Now what we're gonna do is we're going to go click Next. And as you can see, the dash has been used to break the data into two columns. So Excel is reading the dash and assigning values before the dashed one column and after the dash to another column. So let's click on Next. The destination is column G. Column G B1. Be mindful of this, you'd learn what to replace data that is already there, your dataset. This has to be done in a new column. Destination is G1 or the type of data. What is the data format? The data format is text. And let's click on the Advanced option. You can specify if you want to separate based on decimal operators or 1,000 operators, et cetera. But we don't have anything of that sort over here. Click on finish. As you can see over here, the company ID has been as big a one column. The dashes have become another column. The name of the companies have become another column. Let's delete the column with the dashes, control-click or right-click and delete this. And color. As you can see over here, which contains the name of the company, cars, car companies, most to the left. Now we're going to change the name of this column. We have ugly car companies. And we're going to deem the column right to the right of it, which can taste the company IDs company. So your company id of car companies, name of card companies, and you have everything else that you require. 10. Conditional Formating: Hey everyone, welcome to a new lecture today we're going to take a look at conditional formatting. How do you format cell colors, et cetera, based on some condition. What we can do is we can select the cells over here and go to Conditional Formatting under the Home ribbon. And we can see that we want to highlight cells based on certain rules, right? Let's say we want to highlight cells that have values greater than hundred. As you can see over here, all the string variables have been highlighted as in pink. The value 105 has been highlighted in pink. Let's go back. Let's just look at the prices of product columns. Let's say we want to highlight all values less than 75. In green. As you can see, the values less than 75 have been highlighted. You can play around with this and you can play around with the formatting styles so that it becomes easier for you to represent data properly. Thank you so much. 11. Basic Operations in Excel: Hey everyone, welcome to a new session on Excel. And in this tutorial, we'll take a look at some basic operations that can be done in Excel. Now you have this data set where you have a product ID, which is the idea of different products. You have the manufacturing cost of the advertising costs. As you can see, manufacturing cost and advertising costs, the labels are too big for the cell and they are not fitting into the cell. So what you can do over here is you can go to the EBC option under the Home ribbon. Then you can wrap text, just follow my cursor. And what I'm doing on the screen. You can wrap text and this is wrapped the manufacturing cost, labor. And I'll do the same thing for advertising costs. Let's say we want to find out the total cost, which is manufacturing cost-plus advertising costs. We want to find it alpha product once we have to add, basically we have to add manufacturing cost for product, 1.2, advertising cost for product while I'm highlighting the cells into, so you type in equal to, in the formula bar or into the cell. You click on manufacturing cost for product one. And then you add advertising cost per product one to it. You click on both cells basically. So you click on B2, first syllable, B2, which is the manufacturing cost for product one. Then you insert the addition symbol. Then you click on cell C2, which contains the advertising cost for product one. And you're the sum of the cost. But if you want to find out the sum of all, some of costs for all the products. Just click on the cell. Then go to the bottom-right corner, click on the plus symbol and drag down the selector. Since the referencing over here is relative, the formula will update itself. And as you can see, by clicking on the formula, the formula, it's automatically updating itself from cell B2 in the next row or in the cell underneath the cell, which does the total cost for product one. The formula is V3 plus C3 in the cellar need that the formula as before, placebo, the seller need that is B phi plus d phi, B and C are the costs. So it's column number B contains the costs. Column number C contains the advertising costs, the manufacturing costs and advertising costs. That is how you do summation. Suppose you want to find the difference between the two products. Then you do the same process. You click on manufacturing cost, you insert the minus symbol, and then you click on advertising toss and you have the difference. You want to find the product, you click on manufacturing cost per product when you insert the product, or rather the multiplication symbol or the star, click on rising cost per product to the product, you will find out the quotient or soap. You divide advertising costs by manufacturing costs, or let's say manufacturing cost by advertising costs. Because we have been doing that before, right? When manufacturing cost goes first, the manufacturing cost for product one is in cell B2 is ten. And you insert the division symbol, which is the backslash. And you click on the advertising cost for product one, which is in cell c two, and you get the quotient, right? So that is how you do basic operations in Excel. 12. Formulas in Excel: Hey everyone, welcome to your new tutorial on Excel entity. We're going to take a look at how we can insert popular chooses cell, choose any cell where you would insert the formula. Click on this cell and click on equal two. Or you can go to the Formula bar over here and click on equal two. Now let's say you want to find the sum of all the prices of different products. So in this dataset we have product id, sales volume, and the price of different products, $10, $12, $15 credit, $3, they give dollar. You want to find the sun. So type in some. Excel has a huge library of different functions. So you need to know which function you want to use. But the way to type in formulas is does. So basically you type in equal to, then you type in the formula ni, and then you pass the arguments to the function, right? So you want to find the sum of all the prices of products. So select all the prices and close the bracket and enter. Click Enter, hit Enter. Then you have the sum of different products. Now let's say we want to find the sum of the sales of different products. What we can do is instead of finding the sum again, we can just copy and paste the formula into the cell. I'm highlighting in orange, red. In the cell. I'm highlighting right now in yellow, you have the sum of the prices of different products in the cell, which I'm highlighting in orange, you have the sum of the sales volume or different products. Now why was this possible? How did the formula updated? It updated because I've used absolute references, I've used relative referencing. So the first formula was some of C2 colon C6. So the colon symbolizes from which cell to which sell. Your selection ranges from radius, right? If your formula says that your cells ranged from C2 to C6, the colon signifies that it ranges from C2 to C6. And when I copied and pasted this formula, and how did I do that? I, when I clicked on the cell, I went to the top, or rather the bottom right corner. Click on the plus symbol and drag the selected to the cell. To the left of the cell I copied, right? That is how the formula was copied. The formula automatically updated itself, as you can see in the cell that I'm highlighting right now in blue. The formula is now sum of B2 colon B6. So B2 colon B6 represents the range I am highlighting in yellow, right? So the first formula represented the range I'm highlighting in orange. And once I copied the formula and I can do this by manually copying it to, I can just click, right-click or control-click copy those, and paste the formula in the cell or to the left of the cell, right? So basically the formula or the objects, if you've got the formula and if you're not using absolute referencing, the formula updates the cell numbers. By itself. Now, if I was using absolute referencing, so I like dollar symbols but before C, dollar symbol, before to like dollar symbol before C. And I'll add $1 symbol between two, between six. So now I'm using absolute referencing, right? Let's copy the formula. Right-click or control-click. Click on Copy. Click on the cell where you want to paste it, which is the cell I'm going to highlight in green right now. And I'll keep changing the highlighting for your convenience. Click on the cell, Control, click or right-click based on whichever computer use it, and then paste. Copy, paste. As you can see, now, the formula does not apply to update the cell numbers, right? Because I'm using absolute referencing. Now, the formula is being copied as it was in the copy itself. So the formula is not auto updating, so that is what you do with formulas. Thank you so much. 13. Sorting and Filtering: Hey everyone, welcome to a new tutorial on Excel. And today we're going to take a look at sorting. Let's say we want to sort advertising costs in ascending order. For that, we'll go to the Data tab and then we'll click on Sort smallest to largest. So you can use the sort function sort option to use a sort option and you can expand selection or you can continue with current selection if you expense election, the sorting will be extended to other columns too. So if you're sorting, Let's say advertising costs, based on the sorting of advertising costs, the other columns will be sorted to advertising cost is sorted in ascending order. Then accordingly, manufacturing cost and product ID will be sorted along with advertising costs, right? So this is how it looks, right? So click on Sort and you can sort by some value, Product ID, manufacturing cost or advertising costs. Let's say we're going to sort column advertising costs and we're going to sort smallest to largest. Then click on, Okay. Then as you can see, the advertising cost column has been cited. So you start with $10 million, then you have $12 million, $15 million for the $3,000,000.90, $8 million. And the product ID simultaneously has changed. The sequence of the product ID has changed accordingly. So based on the sorting or advertising costs, the sequence of the product ID and manufacturing costs have changed accordingly. If you did not do expense election, how would it look like, right? Let's say we continue with current selection and we thought we sort the values largest to smallest. Since we did not expand the selection of sorting, what happened was, as you can see, the other two colors, manufacturing cost and product ID cost, when sorted, along with advertising costs to the products sequences did not change. The manufacturing cost sequence does not change, and the entire dataset became jumbled, essentially because product three does not have an advertising cost of 19 million. What was the advertising cost of product three? Let's undo what we did. The advertising cost for product three was $10 billion because we did not expand the selection while sorting. And I'm going to highlight the average cost in yellow because we did not expand the selection while sorting the entire data set begin jumbled and became a mess. Now, this is how you sought, if you want to sort, let's say in descending order, select the column. Then click on sought, click on expense election, click on Largest to Smallest over here in the order function in the other option rather. And you can solve this to now let's say you want to sort based on color of the cell. One cell is colored yellow. What do we do then? We'll flip all the salt option under the Data tab. We'll go to the Data tab and select the cells. But click on the Data tab. We'll do Expand selection. And instead of values under the salt on option, we're going to select cell color. And we're going to sort by the colored gold. One of the cells is colored in gold, right? So as you can see, the first value is the value of this, the cell that is colored in gold. And the rest of the values belong to cells that are, that have no fill or no color. So that is how the sorting was done. And since I expanded selection, the other two columns are sorted simultaneously. Now, another thing we can do under the data ribbon is filtered data. We can filter data using Excel. Now, what do we mean by filtering data? Well, when you click on filter data, you have this downward facing button symbol that appears in the column. And then we have to click this. Now we can sort the data in ascending or descending in the filter option, or we can filter data. You can filter by color, we can filter by cell color. So you can filter by goal. What do we do with God? When we click on filter with cell color, what it does is it removes all the other cells that do not have this particular cell color. So only the cell that has a golden cell color remains in the spreadsheet. Now, let's say you want to do some other sort of filtering, right? And we clicked on novel. So only the cells that have no fill or no cell color remained in the selection. Now let's go and filter on the basis of if a value is equal to a certain value, right? So if the value is equal to 15, Let's say it remains in the selection. Else it is out of the selection. As you can see over here, only the advertising cost that has a value of $15 million remains in the selection and the associated manufacturing cost and product ID remains in the selection. Or you can change the option over here. Under the filter option, you can filter values that do not equal to 15, right? And as you can see over here, values that do not equal to 15, that is, advertising cost for product 3425, etcetera, remain in the selection because these values are not equal to 15. So you can play around with this. Let's say we want to sort values that are between ten and drove. These values will remain in the selection. The rest will be deleted from the selection. Let's take a look at the data. As you can see, the values 10.12 rebate in the selection. The rest are deleted from the selection. So this is what you can do with the Sort and Filter options. Thank you so much. I'll see you in the next year. 14. Statistical Functions: Hey everyone, welcome to a new tutorial. Excellent. Today we're going to take a look at some basic functions, statistical functions, right? So let's first find out how you can find out the sum of different values, right? Let's say you want to find out the sum of the prices of products. Insert equal to into the cell. Then insert some which is the name of the function, and then select the range which the sum function will use to find out the sum of the function. The sum is 166. Let's say we want to find, want to find out the average of the prices, the products or the mean. Use the average function. Reduce average function, and let's see what value it returns. It returns a value of 33 point to highlighting it in blue. Let's say we want to find out the median. Click on median, select the number range. The median is 15. Let's say we want to find out the mode. Select the nominator inch, close the bracket. There is no mode, as you can see, no number is repeating itself twice, and that is what a mode is. If a number repeats itself more than once, that becomes a mode. Let's now find out the standard deviation, or rather the variance first. Variance of this dataset, or the radius of the price of products is 1634, 0.2. Let's now find out the standard deviation. Select the number range. Click Enter, and you have a standard deviation of 40.4. Like this, you can find out the maximum number in the number range, which is 105 over here. That is the maximum number. I'm highlighting it in blue. And as you can see, the maximum number is five. I'm highlighting it in orange. And you can find the minimum number two. Select the range, click on enter. The minimum number is ten. So those are some basic statistical functions in Excel. Thank you so much. 15. Rounding Values: Hey everyone, Today we're going to take a look at the round function. Now let's say the prices of products are not whole numbers. They say they have decimals, 313, like this, all the prices have decimals. We can round this up. We have to use the round function. Click on equal two in the blank cell, type and round or typing part of it. And Excel will automatically true up the function round. We have to select the number that we want to round. Let's select the number in cell C2. Unless it, we're going to round it to one digit after the decimal point. So insert one. And as you can see, it has been rounded. Now we can use the round down function. And what the round down function does is it rounds down the value of the number to the whole number below it, right? So the number is C2 and the number digit is, let's say one. As you can see, it has round down the value to the number below it, right? Or let's say we want to have a whole number. So the number, we change the number of digits after decimal 0.20. And as you can see, the route down function as round the value down to ten. The price of the products. For product ID3 was 10.313 and the round down function as rounded it down to ten because we specify that we do not want any digits after decimal point. And then we have the roundup function. And the numbers selected is 10.313. I'm highlighting it in orange. We're going to round it up to the next whole number. So the number of decimal points, because rather than numbers after the digits after decimal point is zero. And it has rounded the number 211, highlighting it in orange. That is all about the round function. 16. Autosum: Hey everyone, welcome to a new Excel tutorial. And today I'm going to demonstrate the AutoSum function. So we have a dataset over here. We have ID of companies. We have sales volume in units for the companies. We have price of product for the companies and we have the status of the company as profitable or unprofitable, etc. What we want to do is we want to find out the sum off sales volumes. And we can use the AutoSum to do this. Just click on the cell below the range which contains the sales volume. So in this case the cell is B7. Click on AutoSum and you have the sum. Let's say you don't want the sum, you want the average of sales volume. Go to the AutoSum tab in the formula ribbon. So you have to go to, usually excel is in the home ribbon or Home tab. You have to go to the Formula tab, go to AutoSum, click on the downward bracket, and then select whichever function you need to select. Let's say we select average and automatically autosome will take the range above the cell as the range which will be used to calculate the average. So we have the average Israeli politics less. Confirm this. So average of this ranges pretty politics, yeah. So everything is being calculated correctly. So that's what the AutoSum function does. Thank you so much. 17. Sumif, Averageif: Hey everyone, welcome to a new Excel tutorial. And today we're going to take a look at the summary of average if counter functions. So what do these functions do? These functions basically we'll find the sum or find an average of fine the count only if a certain criteria is met. So how do we write these functions? Let's stay. We're going to type in the function in cell B8 and we want to find out the sum of sales volumes for only profitable companies. So total sales volumes for profitable company. So let's type in equal to, let's type in some. If somebody function appears in the drop-down menu. Let's choose the range where we're going to check if the company is profitable or loss. So this range doors whether the company is profitable or not. So the labels are stored in the status variable and the ranges D2 to D6. And the criteria is prof tables. And we're going to find the sum of sales volumes for profitable company. Unless click Enter. And this sales volume of profitable companies is 46. Let's check this product. Company number one is profitable. I'm highlighting it in orange. Company number three is profitable. I'm highlighting it in orange. Company number five is profitable, I'm highlighting it in orange. The total sales for these three companies are ten plus 12 plus 24. So you have ten, you have 12. And we have 2410 plus 12 plus 24 is 46. So the formula is working right? Let's see. Another version of this which is average. If the average if function will only find the average of sales volume if a certain criteria is met. Let's say in this case, we're going to find out the average sales volumes in units for unprofitable companies. So the range which stores the status of the company has the company is profitable or unprofitable is D12, D6. And we're searching for on Ross bit able. The sales volumes are stored in the variable are in the range B2 to B6. Let's click Enter and the average is 2,828.5. So there are two companies which are unprofitable. The first one has a sales volume of 23. The second one has a sales volume of 34. So the total sales volume is of unprofitable companies is 37 plus 20, which is 5757/2 is the mean and the mean is 28.5. 28.5 into two is 57. So that is how you work. The Sabbath average IF functions, these are conditional statements, the dig into account a certain variable and will only show the Psalms, the total subs that average all the counts of variables which are meeting a certain condition. If the condition of being profitable is met, then calculate the sums. If the condition of being unprofitable is met by the company, then Excel will automatically calculate the average of these companies like this. You have some that average, COUNTIF, etc. This is how these functions work. Thank you so much. 18. COUNTIF: Hey everyone, welcome to a new tutorial. Today we're going to take a look at the COUNTIF function. Suppose you have data where you have some categories. Now let's look at this data set that we have. You have company IDs, you have sales in millions for the company, and you have the scatter of the company, whether it is profitable or unprofitable. Now let's say you're trying to find out how many of these companies are profitable and how many are unprofitable? Well, here, you can tell with the naked eye that do companies are profitable and three companies are unprofitable. But in reality, you will have huge data sets and becomes difficult to find out which category a particular observation belongs to, a particular company or a particular product belongs to. This product may be, let's say, iPhone, iPad, etc. And you're trying to find out if the product is selling, are not selling. It may be a company, which is the case over here. In which case you're trying to find out whether the company is profitable or unprofitable. So how do you do that? How do you calculate the number of unprofitable of profitable companies that are there? Or how do you calculate the category, the count of different observations belonging to a certain category. How do you do that? You use the COUNTIF function. You can do this using if statements but becomes complicated count if it's an easier method. So choose a cell to that empty cell where you got to store the results of the COUNTIF function. I'm choosing celibacy aid that have been equal to, then type in count. If the function appears in the drop-down menu, then you have to specify the range. The range contains all the categories, but different observations for different companies, for different products, et cetera. The range over here is the scatters of the company. And the range is between C2 to C6. We are ignoring the label of this particular variable. The label is status. So for this column, label a status, but we're ignoring that. The range is between C2 to C6. And this range contains information on whether the company is profitable or unprofitable. So we have the range null. Then we have to specify the criteria. So we are, we have to specify which particular categories count. We want. Harmony, profitable companies are there, let's say less specify the criteria. You have to specify the criteria within quotes. Profitability. I want to find out how many profitable companies out there. As you can see, the result is two. And I'm highlighting this in orange with the naked eye. You can tell that there are two profitable companies. I'm highlighting that in orange. Let's say you want to find out the number of unprofitable companies that are there. I'll copy and paste this formula into the next cell. The problem over here is not referenced this using absolute reference. So the references changed, the cells moved. So I'll change the reference and I'll type in on profitable over here. Let's see how many unprofitable companies are there? There are three unprofitable companies, I'm highlighting in blue, and unprofitable companies with the naked eye you can see, and you can see that there are three. So that's what coded functions are all about. 19. Dealing with missing values: Hey everyone, welcome to a new Excel tutorial. In this tutorial, I'm going to show you what to do with blank data. Let's say we have this dataset over here, which has the quarterly performance of different companies. You have four quarters and the performance of all companies across different quarters. So we're not tracking the performance of companies for all the quarters, right? You have revenue in billions of those companies in that particular quarter, you have costed billions. And you have the deed, the company was established and the date it became profitable. Let's introduce some blank cells over here are other less, remove some data. So we can call this missing data. So some of the data is missing. For instance, for bottle world and card company Tesla, the cost in billions is basic for quarter three and car company toyota, the revenue in billions is basically, let's highlight the cells in yellow. So these cells that are highlighted in yellow have missing values. What do you do? We go to? Filter. First, select the cells. Select the entire range you can. And the after clicking on the Filter button in the Data ribbon. So what I did was quite simple. I went to the data ribbon and clicked on the Filter button. And the button with the button was introduced to all the different columns. And now we can filter revenue in billions, right? So I'm gonna filter revenue in billions first. So which column? The column I'm highlighting right now, I put a filter out the sig value. I want to remove the missing value from our analysis because they can be really annoying. So I'm going to uncheck the blank. The blank won't be shown in the data. The blank will be removed from the data. And when you remove the blank from the data or the missing value from the data, the entire observation, the entire row will be removed. That is how you remove data. So we have removed one blank data. Now let's go to costed billions, the columns highlighted greater. Let's again go click, click on the Filter button and less object blacks. And as you can see now, we don't have black data. The blank data was removed. The entire observations comprising of Chicago Police was removed, and the blacks are no longer there. Thank you so much. 20. Dealing with strings in Excel: Hey everyone, welcome to a new tutorial on excellent. Today we're going to take a look at some string operations. So for that, I have introduced a new column to my dataset. And this column contains product names or names or iPhone, iPad, iPod, MacBook, Air pods. Let's first take a look at the Len function. Click on a, click on an empty cell, insert equal to type length. Length. Length is basically the length function. It gives you the length of string in terms of character. So how many characters are there in the spring? So select cell D2 over here, which contains the string iPhone. Click on Enter. As you can see, iPhone six characters or letters expand the selection. Double-click on the right bottom corner where you can see the square symbol, this small square button and double-click on an iPad has four characters. I bought his book, characters that book seven, etc. Now we're going to take a look at the right, left and big function. While the write function does is it takes a string and it'll return characters at a certain number of positions from the right. So let's say we want characters which is, which are two positions from the right. We all want all the characters which are two positions from the end of the string, right? So we want two characters which are from the end of the string, two positions from the end of the string. So iPhone has six characters, and the last two characters are in. The right function is returning any two characters from the end of the string because we have specified that we want to characters from the end of the string. Let's use the left function. Left function, what it does is it's similar to write over here in the formula there to specify how many characters you want from the beginning of the string or from the left of the string. Let's say we want three characters from the beginning of the string. And iPad. As for characters and the first three characters or IP. So that is what the left function is returning. Let's use the MID function. The next function, we can specify if we want characters for the middle of the string or how many characters we want for the middle of the string and from which position to which position. Let's say we want to characters from the middle of the street from position to position three. Okay, let's select the text first. Position two to position three. So we're inserting 2.3 in the formula. And the result we are getting is pod. So position two to position three, the result we're getting is spotless. Change the position numbers, position two to position two, we are getting PO, right. That is what the right, left, and MID function does. That is how you deal with string on Excel. Thank you so much. 21. Changing data from string to numeric: Hey everyone, welcome to a new Excel tutorial. And today we're going to take a look at what we should do when we encountered the text values amongst numeric values. Now, over here, I have a column called cost and beliefs. Now, this dataset comprises of different quarters and the quarterly income in terms of revenues of different car companies and the costs in billions of those different cargo revenues or currency values, they are in billions. And the cost and billions. Again, our currency values they are in billions, as you can see over here, I have the cost of billions of phone companies. Now I'm going to click on cell above d2, which is highlighted in light yellow. I'm going to change the highlight. Let's highlight this in light blue. Now, instead of having a numeric currency value over here, a numeric value for cost of billions. I'm going to insert a string value. So I'm going to insert 1.1 will be within quotes when you put something within codes in Excel, that means you're telling Excel that this is a streak. So I've defined the costed billions for Tesla as a string. Now, how do we deal with this? Because you cannot conduct a dialysis if you have a string amongst numeric values, how do we deal with this? Well, there's thankfully a simple way to do this. Go to data, go to Text, to Columns, click on Text to Columns there to select the data range, select the cost of billions column. So you've selected the data rate, click on delimited, delimited, basically what it does, it checks for different characters that might be present between data or data. Click on Delimited, and over here, click on other. And over here, click on quotes as the Text qualifier. So the Text qualifier, over here, it has to be coats in order for Excel to remove the course of the data and define the data as a numeric value. So the Text qualifier is course. Click on next. Destination will be G1. Perfect. Less Lake finished. As you can see over here, the courts have been removed and the data is now numeric. That's all. Thank you so much. 22. Concatenate, Upper or Lower Functions for string manipulation: Hey everyone, welcome to your new Excel tutorial. And today we're going to take a look at couple of more string functions. The first function we're going to take a look at is upper. We're going to type in equal to an empty cell. Then we're going to type up what this function does is it converts all characters to uppercase. So let's choose the cell B1, which contains the word Potter. And as you can see, border has been converted to uppercase. Now let's look at the function lower. Again, you have to pass an argument to this function or rather to this function. The argument we're going to pass in this case is the cell B2, which contains the capitalized word bottom. So every character in potters capital, and we're going to convert it into law. And as you can see over here, border has been converted to lowercase. That is what the upper and lower function do. Now, let's say we are trying to concatenate two strings. What do we do by concatenate or combine two strings? So we have Harry over here, which I'm highlighting in our lives. Another color, blue. And your part over here, which I'm highlighting in orange, are light orange. I want to combine Harry Potter. Let's go to cell C1. I'm highlighting the cell in red. Let's say, let's type in the formula, Let's type in the formula, in the formula bar. Let's type in equal to and gone get. And you have that concatenate function. And the texts we're going to concatenate are in cell A1, which is Harry. Then enter a comma, enter to coach. While one open code and one closed code, and enter the next word Potter. As you can see, you have the word Harry Potter. Now I've entered To course over here In this to cause the purpose of having these two costs. And the formula is that in these two cos, you can have some other words, harry James. Let's type in James and less. Click Enter and you have Harry James Potter. So if you'd not word or if it not want another word. In this particular function, you do not want to add James to the Harry Potter thing. Harry Potter would just click on concatenate. B and A1 contains the word Harry, B1 contains the word portal. Click on Enter. And again, as you can see, you have Harry Potter in cell number. See one. Now, if we remove the course over here and type in hurry and type in border. As you can see in cell number C1, you have the word Harry Potter without the quotes. And you can insert a space so that there's a space between Harry Potter. And as you can see right over here in cell number C1, which is highlighted in red, you have peri bottles. So that's all about the concatenate function. Thank you so much. 23. If statements and Nested Ifs: Hey everyone, welcome to a new tutorial on Excel. And today we're going to take a look at that it's taken. So let's define our if statement. Let's click on any random cell. I'm going to highlight the cell in yellow. And in the cell we're going to insert the if statement like on equal two and then type in if. So, Excel has already an inbuilt IF function. So you click on the function. As you can see, Excel requires the logical test, which is whether a value is greater than, lesser than, equal, or unequal to a certain value, it has. Then, after that, the dense statement after that. So if a logical test is true, then what should the value b and in which cell? And if it's false, then what should the value b and in which cell, right? So let's say we're trying to see if a particular value is lesser than 100, right? So let's say we are trying to see if the prices are less than 100. Let's click on the first price for product ID3 in the cell C2. If C2 lesser than hundred, That is our logical test. If it is lesser than 100, then the value in the cell remains the same. So we are clicking on C2. Again, if it's less than 100, than the value in the cell remains the same. So whatever the value in the cell was, it remains the same. If it's greater than 100, then the value and the cell will be 45. Now you can reference the cell in this else part of the if statement. Or you can reference a value. You can insert a certain value or you can insert the value stored in a certain cells but less, insert a value of 45 over here. As you can see in this cell, the value of the price of products or the price of the product in this case was ten. And the if statement was if it's less than hundred, right? I'm highlighting the c2 less than 100 part of the if statement in gray over here. So the value of the cell remains the same, right? Less, copy and paste these values so that we have if statement results for all the different prices, for all the different products. So I'm going to go to the bottom right corner of the cell, click on the square root symbol. Drag the selection downwards. And as you can see, all the values remain the same because the prices of products are all less than 100. Let's say we change the price of a product to 105. So I've changed the price of the product for 200, highlighting the cell in light green, right? As you can see, in the cell that are clicked, and I'm highlighting the cell in dark green, the value over here is changed to 45. So if statement checks, if C6 less than 100, well, C6, as you can see from the cell number over here, has a value of 105 is greater than 100. So we go to the else statement which states that if C6 is greater than 100, then the value will be 45. So the value over here has been replaced with 45. Now let's say we want to input or rather check if a certain cell has a value over 100. And if it's over 100 ft, we're going to replace that value with the value stored in cell number G2. I'm highlighting the cell in blue over here. And the value stored in cell G2 is 32. Let's change the if statement. If the value in the cell lesser than hundred, then the value in the cell remains the same, or C2, else it becomes g2. Let's copy this new statement to all other cells by doing what? By go into the bottom-right corner and dragging the selection downloads. As you can see, the value in the cell whether for which the price of products was great, greater than hundred is showing zero. Why is it showing zero? Because I did not use absolute referencing in the formula. So the value and the cell G2 has to be absolute referenced. And how do I do that? I add dollar symbols over here. Because we do not want this value to change. Because if G2 becomes G6 and the formula, as was happening before, as you can see in the formula over here, G2 became G6 because I did not. Use absolute referencing. If that happens, then the value will obviously be zero because there's no value stored in G6 cell highlighted in green, there's no value store. So what I'm gonna do is I'm gonna go back to the original form, land use, absolute referencing, dollar and dollar. To now I'm going to copy and paste the formula to all the cells below this particular cell. And as you can see in the cell, I'm coloring in yellow. The value has been replaced with the value 105 has been replaced with 32. So that is how our if statement works. What if you want to include two if statements, right? Let's say you want to test for some other things in the data. And for that you need an if statement, right? If I'm defining a new column called a state meant. So let's write the if statement. If the logical test is if price of product greater than, less than 100, then we're going to include another if statement. We're going to check if sales volume is greater than ten. Sales volume is stored in column B. Then insert another IF command. If sales volume is lesser than 15, let's say comma, then if both conditions are met, that is, brighter products is less than 100 and sales volume is lesser than 15. Then the cell containing the price of products for product three, in this case of a product 4512, etc. When you copy and paste the formulas to the other cells and the same formulas transmitted to the other different cells. Then what happens? Then? We're going to see that the price of products will remain the same. So if the price of product is less than hundred and sales volume is lesser than 15, then price a product remains the same. Else price of product will be 50. Now you close this if statement and coma. If none of these conditions are true, the conditions that are basically that price of product is less than 100 and volume is lesser than 15 units. If none of these conditions are true, then the function will return a value of zero. Double-click on the bottom right corner, which has a square root symbol over here. As you can see, you're going to double-click on it. Because by double-clicking the formula, can we paste it to the cells underneath the cell where the formula is defined? So as you can see in the cell highlighted in light blue over here, the value returned by the f statement is zero. Y is zero, because C6 or 105 is, the price of product is greater than 100 in this case. And it does not matter what the sales volume is because the price of the product is greater than 100. In the cell that I'm highlighting in light orange over here, the value returned by the function is 50. Why is that? So? Because the price of products or C5 is 23 to 23 is less than 100. Since it's less than 100, we go to the nested if statement. The second if statement, right, is B5 or the sales volume lesser than 15? Well, the sales volume is not less than 15. So basically, we go to the part of the if statement which states that if sales volume is not less than 15, then the function will return a value of 50. And that is what it did. It returned a value of 50. So that is how you construct an if statement in Excel. Tacky so much, I'll see you in the next lecture. 24. Logical Operators in Excel: Hey everyone, welcome to a new Excel tutorial. And today we're going to dive deeper into if statements. We're going to take a look at the use of logical operators in if steepens by logical operators AND, and, OR operators, right? So you're not just dealing with greater than or equal to, equal to etcetera. You're also dealing with an and operator. So what do AND, and OR operators do that? Suppose you have a situation or scenario over here. You have five students in a class, and they have taken a computer science class. They have taken an exam at the end of the course, and they've taken the theoretical exam and a practical example, the theoretical exam scores are highlighted in yellow. The practical exam scores are highlighted in light blue. The stipulation is, let's say, that students have to pass both exams to pass the course. So you're trying to find out if students will pass both exams and if they pass both exams, that is, if they get greater than 50 in the theoretical exam and greater than 50 in the practical exam, then they pass the exam, ends, they fail the exam. Scores range 0-150 is the passing score. So if they get 50 in the practical exam, and if they get 50 in the theoretical exam, then they pass. That goes as the fee. So how do you modern set the scenario? So if they pass the exam in the column that I'm highlighting in, I'm highlighting in green or light orange. Then in this column yellow the store, their final pass or fail grade. So if they get, so let's say student one gets 50/50 in both the peoples, in both the theoretical example and in the practical exam, then he passes the course. So in the column, pass or fail when both exams required. The one that I'm highlighting in light orange, cream color in that particular harlem, the final grade will be displayed, which will be pass or fail. So how do we model this? We need an if statement. So let's click on a cell, the cell number and taking all these D2. And let's write a formula here, V equals two. If click on if. The logical test is that they have to pass both exams. So score in theoretical exert greater than 50 and scoring practical exam greater than 50. So after typing in if, type, and click on that, and now enter the logical operators. So the logical operators are scoring theoretical exam greater than 50 or greater than equal to 50. Rather scoring practical exam greater than equal to 50. If both schools at greater than equal to 50, then display, pass. Else display, feel and notice. Since pass and fail or springs, I'm enclosing them in quotation marks. Click Enter. So the first student has passed. Now we're going to copy the same formula to the other cells below the first cell that I worked on right now, as you do that, we're going to double-click on the right bottom corner of the cell, which contains the formula. Or we can click on the right bottom corner, the squares, small square symbol on the right bottom corner, and drag it downwards to copy the formula, I'm going to double-click. As you can see, two students or pass the course and three students have failed the course. Student to phase that goes to the three fields that goes to the Piazza post. Now let's say you have a teacher who's more lenient. And he says You have to either pass the theoretical exam or pass the practical exam to pass this course. How do you model this situation? You'll need if statements again. So let's select cell E2 and E2. We're going to write the formula. In this case we need it at all. So either pass the theoretical exam or pass the practical exam. And after, if I click on Add, and then you have to type in the arguments. The first argument is that the score in theoretical exam has to be greater than equal to 50. Oh, what am I doing? Sorry, it's not an n is odd. In this case is odd because we're trying to find out if the person has passed the theoretical exam or the practical exam. If he passes the practical or the theoretical, he gets a password. The previous gates, you have to pass both. So we're using our practical exam scores for student one is stored in V2, or rather theoretical exam score for students, while it's stored, stored in v2. V2 greater than or equal to 50, or C2 greater than equal to 50. So either the practical exam score or the theoretical exam score has to be greater than 15, greater than equal to 50, while the student to pass. If this happens, then you give the grade pass to the student. Else you'd give the grid feel to the student. Close the bracket and the first due date has passed. As you can see, is score in the theoretical example, 65's for the practical exam is 56. Fine. Let's copy this formula by double-clicking on the bottom right corner of the square, in the bottom right corner of the cell. And all students to pass this pose right now. So I'm going to highlight the entire column in, let's say like me, all students who pass, why is that the case student who has passed his practical exam, you got 78 in the practical exam. Look at the cell and clicking on yes, 78 in the practical exam and 45 in the theoretical exam. You feel the theoretical exam, but it passed the practical exam. Student three similarly pass the theoretical exam, but feel the practical exam, but he pass this course student for failed the theoretical exam, but the practical exam. So we pass the course, student five as the theoretical exam and pass the practical exam, do so he passes. So everyone passes. That's how you use AND and OR operators in an if statement. Thank you so much. 25. Isnumber, Isblank, and Iserror: Hey everyone, welcome to a new tutorial on excellent. Today we're going to take a look at that is blank and SRS deepened and that is planned and it's error statement are very, very useful when you're trying to find out if there's a blank in one of the cells or if there's an error or number in one of the cells, right? So let's start with a blank statement. What you have to do is you have to select the cell. Where are you going to store the results of the blank statement? And just, let's name this column is blank. And then click on the cell. Insert equal to and type is blank. And insert a value. Suppose you are trying to see if there are blanks in the price of products. If we don't have data in the price of products column, right? So click on the value. So it's blank. C2, C2 is the value of z to a blank click. Then the answer is false because there is no blank over here. So let's expand the selection to the other products. We have done this. All the values are false because there is no black. Let's say there is a blank. As you can see over here. That is blank value becomes true. Again, we're going to insert a value is blank, value becomes false. As you can see in the blue salvia. Let's take a look at is number. Are all the values under the price of products column or lumbar. It's quite simple, right? You click on equal two. Click on Islam bar over here from the selection of functions, and you click on the value. Now it's always true. Yes, all the values are numbers. Now let's insert a word we hear. So as you can see in the cell highlighted in orange, since as soon as we entered, so the number function returns a value of false. So, so it's not a number and we're going to return back to revert back to a number. And the value highlighted in orange becomes true again. And then there's the error statement. Let's select the prices value for this one. There is no error in either one of the price of product values. So as you can see, this is how the is blank, is number, is error. Functions work. Thank you so much. I'll see you in another. 26. Data Visualization in Excel: Hey everyone, welcome to a new Excel tutorial. And today we're going to take a look at data visualization for that at this particular dataset over here at the company names for automobile companies have their revenue in billions of their cost and billions. And if you're confused about the symbol before the revenue numbers is indeed repeats. I have the date established and I have the deed profitable nerve. Remember, this is a made-up data set. This is not real data, but anyhow this will suffice for the demonstration. So let's go to the Insert tab. And over here, under the insert ribbon, you have these different options. You have column chart, you have hierarchical jagged, which we don't need. In this case we have statistical charts. You have live chat, you have pie chart, and you're scattered jars. Let's do a boxplot first. Just click on the bar plot option. As you can see, it's blank right now go to Select Data under the Chart Design Option. And you have to select the range. We want to plot revenue against company names. I've selected the range revenue and company names. The name of the chart is revenue in billions. So I've selected that the y values are the revenues. The revenue values of the y values, the x values, the company names. Click on Okay, and you have a nice bar chart over here with different companies and their revenues to it as the most revenue, Tesla's the least, which is understandable given the fact that electric cars haven't caught on yet in most parts of the world. Now, let's say you want to change how the chart looks. Go to the chart design option of a unit. My cursor is hovering above this option and click on any different option, which will give you a different type of look for the chart. Now let's go to Add Chart Element and the chart design. We can add access title, we can add access. We can add a chart title. Job title is already there. We can add data labels. We can add data tables with Legend E. We get to add percentage, et cetera, grid lines, legend and trend line. Let's add a trend line. I'll add a linear trendline does not look like a good fit, the linear trend line. But what can we do with the trend line? Double-click on the trend line and you get these options, you can add a logarithmic trend line. We can add a polynomial trend line, which looks like a good fit actually are. What you can do is you can go to this paint bucket option, which is called fill in line, or which looks like a paint bucket, basically this option, click on it. You can change the width of the light. You can change the dash type, et cetera. You can change the color of the line to this orange, as you can see. Now I'll do away with the slide. I'll show you another light. Another trend line. This red line is called the linear forecasts. If you want to forecast what another company, what sort of revenue another company will have, you can use this forecast lead. As you can see, the Excel is trying to forecast into the future. And Australia's tell you what sort of guard cells another company might have. The forecast lead is probably aren't any of the trend lines are probably good fits for this sort of chart. So I'm going to remove this. Now you can switch row and column. I'm not going to do this for this chart because by in batches switching rows and columns makes no sense because then you'll have revenue in the x-axis. We won't do that. We're going to change the chart type. We're gonna go to pie chart, pecan pie. And as you can see, you have a pie chart over here. The legend is missing. So go to the different chart style options. Click on one of them. And the legend is here right now, then as you can see, Tesla, which is represented in blue, has 12% stroke person sales or rather CLF dwell. So I will, what I'll do is I'll go to another chart style which displays percentages. Yeah. So Tesla has a total revenue of 12 billion and it constitutes 11% of the total sales. Then Ford has a total revenue of 23 billion. It constitutes 21% of the total sales. Then Toyota hazard revenue of 42 billiard constitutes 39 per cent of the total sales. I just expand this chart. And Chrysler has a revenue of 32 billion. It constitutes 29 per cent of the total sales. So that's what a pie chart tells you. It tells you the portion of the entire sales each company represents. Let's delete this chart for the time being. Let's go to Insert tab again, unless insert a scatter plot. Right now we have a blind scatter plot. I'm going to change, I'm going to select data for the scatter plot. So the artery and just cost in millions in revenue. So I'm going to block revenue against cost and billions. Name of the charters. Give it a new name, type in equal to the name bar, open God's name of Jack. Revenue versus costs. X values are costing billions. Y values are revenue in billions. And that's all you need. And you have a scatterplot. Revenue was his cause. X-axis is cost and billions, and y axis's revenue in billions. Let's add the axis titles. You can add horizontal title over here, which will be gloss. You can add. A vertical axis over here, which will be revenue. What we need now is a trend line. So we're going to add a trend line. Let's add a linear trendline. The linear trend line is a good fit. Let's double-click on the trend line and less bright, different other trend lines. They are not good fist polynomial seems like a good fit. So let's keep the polynomial instead of the linear. Let's go to the fill and line option, which is represented by the paint bucket. Let's click on it. Let's increase the breadth of the line, and let's change the dash type. Let's change the color of the line so that it pops out. Now, what we can do is we can also play around with the scatterplot data points. The data points are represented in blue right now, they are marked in blue. I'm going to increase the width of the data point. For that, we'll go to the paint bucket option again, instead of the light will go to the marker and we are going to increase the width of the lines. And I'm going to change the color to green so that it pops out. As you can see over here, the color has been changed, agree that the data points are right over here. So let's click on this chart. Let's go to Add Chart Elements. Let's go to Data Labels, and let's give the data labels. The data labels, as you can see, the topmost is 42 billion, which is for Toyota. Toyota has been represented the chart represented in the chart. Then you have 32 billions. Then you have 12 billion basically. So you can play around with this. And as you can see, you can clearly see what does scatter plot is telling you. You can play around with this. Now what we can do, what we can do basically, we can go to the chart design option and we can change the chart type. Let's do a line chart. And we have a line chart over here. We have the different data points. As you can see, 12 billion, 33 billion, 42 billion, 2 billion. And we have a trend line. Let's remove the trend line. And we have a line chart right over here. We have a line chart. And we can change the way the line chart looks. We can make it look like this. Revenue versus cost, any of the different costs you can make it look like this by clicking on the different options and other Chart Design tab. So that is what you can do. The rest of the jars are not important for this sort of data. You can try a different scatter plot. The scatter plot. And you can change the design of the scatter plot. As you can see the scatterplot husband represented over here. We can go back to more traditional scatter plot like this. And then click on the data points. Increase the width. And as you can see, the data points are now in green boxes so that they pop out from within the entire chart space. So that is what you can do. The different things that you can do with charts. I'll, I'll show another type of chart. Before I end this. I'm going to insert a histogram. So what does a histogram do? Basically, a histogram is essentially a frequency distribution. So essentially it tells you the frequency of values in a particular rate. So let's say we have a rich which is like this, zero to 2020 to 40 and read you cannot be disputed. It has to be continuous. And for T2, 60. So these are the ranges in which revenues can fall in any of the values for revenue, how many of the companies fall in the range zero to 20. You have one value over yet, Tesla. Tesla is the revenue of $12 billion. So it falls in the range zero to 20. How many of the values fall in the range 20 to 40? Well, Ford has a revenue of credit 3 billion, Christ as a revenue of 32. So two of the values fall in this range. How many of the values fall under age 40 to 6,042 billion. So one of the values fall and the spring there, but it keeps the type of data we're because Excel is interpreting this as currency. Making in general. The table over here basically is what will be represented in the histogram. But I'm going to keep this table. Now let's go to insert and insert the histogram. But before that we have to select the data. So I'm going to insert a histogram for the revenue. So the histogram will be reinserted for the revenue. And let's click on this. Histogram has been inserted. Let me change the chart title. Click on the Chart Title IV it revenue. Now, what can we do with this? As you can see the graph on the y-axis, you have the frequency. On the x-axis. You have the radius, the radius r. I'm going to expand the chart over here. The radius r 12 to 14. Billions for the first color, when 40 to 60 billion for the second column, click on the histogram. Now, click on the histogram, it Double-click. What you can do with this is you can change the color of the histogram. So I'm going to meet the first range that is close to $40 billion. Or I'll go to bake the second rage, greed. So we can change the colors. Now we can add borders. Now let me add a border. Let me add a deep blue border for the first column. So you got to add a border. You can increase the width. Just follow my cursor will be the width over here. That is what you can do. Let's see, I do not like how the histogram looks because it has two columns or two ranges. Let's say we want to change those ranges. The first thing we're going to do is we can double-click or the particular column in the graph. Or you can write up, right-click or control-click. I'm right-clicking it, control clicking. And we can format the plot area and we can follow that. We can change the color, et cetera. Let me make the color green for this one too. Right? So you can read the chart area. Now I've got a format color of this one. I'm going to make it blue so that it stands out. And I'm going to make the other would do two. So this is how you can format the chart area and go to Control and click, right-click whichever option works based on your operating system. So let me click on the column. Are we here, are the frequency column over here. And let V control-click or right-click. And let me go to format data series. So what I can do with this format data series, I can change the bin width, the current bid with this $28 billion. So well to 40 is $20,000,000,000.40 to $68 billion that we bake it. Now I have a histogram that looks better. As you can see, most of the values fall $22-32 billion. I can change the number of bids to I can make it. Five does not look good. Let's revert back to three. So that is what I can do. Let me change the width to five. Again, it does not look good. It looks discreet. Let me keep it as debt. So that is what I can do with the format sees often though, let's say I want to display how many values fall in which category. So by that, I mean, what is the frequency for each category or each range? What is the frequency for predictability? Dollars? What is the frequency for 22 to $32,000,000,000? What is the frequency for 32 to 42 billion-dollar? Click on the columns. It the plot. Right-click or control-click. And then click on. Add data labels in the drop-down menu. As you can see, we have data labels over here. And we can make the data levels orange, or we can make the data labels red. And we can increase the borders of the data labels. Let's make them big. The chart area orange so that there's a good contrast. We can increase the width of the data labels so that they become bored provident in the chart. Let me be the color yellow. So as you can see it, the rage, 12 to $22 billion, that will change the chart color to white. As you can see, the rage 12 to $22 billion. There's one value in the range 22 to $32 billion. There's there are two values in the range, 32 to $42 million. There is one value. Is it correct, As you can see, it is correct. You can play around with the bead width, number of bids, etc. You could do all of that. That is what a histogram is all about. 27. Data Analysis: Summary Statistics: Hey everyone, welcome to a new lecture on Excel. And today we're going to take a look at the Data Analysis ToolPak in Excel. To activate this ToolPak, the first thing we need to do is we need to go to Tools, and then we need to go to Excel Add-ins. Click on Excel Add-ins. Make sure that the Analysis ToolPak and the Solver add-in options are checked. Check the check boxes, and then click on. Okay, and this will activate the Data Analysis ToolPak. Now let's go to data. Let's go to Data Analysis. My cursor is on the data analysis option over here. Click on data analysis. Over here you can do single factor ANOVA, two-factor, ANOVA two-factor Without Replication correlation, covariance, descriptive statistics, exponential smoothing, F-test Fourier analysis, histogram, moving average random number generation rank and percentile, regression sampling, et cetera, z-test, t-test, etc. Most of these techniques are beyond the purview of this course, beyond the scope of this course. But I'm going to cover descriptive statistics, correlation, and regression as far as this particular toolkit is concerned. So let's first work with descriptive statistics. Let's click on okay. So the input range we want to find the descriptive statistics for sales and price our products now bear in mind. You cannot include non numeric values in this range, so you have to exclude the labels. If you want to include the labels, then what you need to do is you need to select the columns and then check Labels in First Row. As you can see over here, there's an option called Labels in First Row, my cursor is on this particular option. Click on that. Now click on Check summary statistics. That is what we are trying to find out. The output range should be somewhere which is far away from your data. Let's select the output range. Now that we've done that, we're ready to find out the summary statistics. Okay, I get it. I'm going to select the input range again. And the output range I'm going to select, again. Made a mistake with the range selection before this, and I'm going to click on, Okay. And now we have the summary statistics. Now for steels, the mean is 20.6. I'm going to highlight these in different colors, right? So highlighted in orange is the mean standard error is highlighted in blue is 4.3, median is highlighted. Yellow is 23, standard deviation is 9.70. So instead of typing in formulas and finding all these things individually, you can just use the Data Analysis ToolPak, sample variances 95.8. I'm going to highlight this in purple, skewness and kurtosis. They are, they have certain values. -1.1 to 0.20 light ranges for any full range is maximum minus minimum. I'm highlighting this in debri, minimum is ten. The minimum sales volume was ten. Allied highlighting this and see blue. Maximum is 34, highlighting this in orange. And sum of all the sales is highlighting it in a shade of blue and cow, despite there are five sales observations highlighting this an extreme light green and the price of products have similar results for the descriptive statistics, I'm highlighting the entire thing in yellow. So as you can see, the mean is 33.2 standard error, 18, standard deviation for our sample variance once it's three point, if you recall, we found the same values while using the commands in a previous lecture. Good also skew range, minimum, maximum, etc. You can find all this using the Data Analysis ToolPak. Thank you so much, That's all for this lecture. 28. Data Analysis: Correlation: Hey everyone, welcome to a new lecture on Excel. And today we're going to take a look at correlation. So let's find the correlation between sales and products. And what is correlation? Correlation is basically a metric that tells you if sales and products and price of products rather, or if two variables vary together or vary in opposite directions. So if they vary together, then we see that they are positively correlated. So if one variable increases with increase in another variable, then we say they are positively correlated. If one variable decreases with the increase in another variable, then we say they are negatively correlated. Correlations course vary between minus one to one, with minus one being highly perfect, negative correlation, zero being no correlation, one being highly positive, perfect correlation. In general, correlation values between 0.7 and -0.7 or other correlation values near 0.7 or -0.7 tell us that there is a strong degree of correlation. So there's some association between two variables over here. The two variables we're going to test our sales and prayers of products. So when price of products increases, the sales increase, or the sales reduced. When price a product decreases, the sales increase of sales reduce. We look at the correlation coefficient and find this out. Let's go to the Data tab. Let's go to Data Analysis. Let's go to correlation. Click on Correlation, click Okay, select the Input Range. Select the columns B and C. Check Labels in First Row because the first row contains illegal sales and price of products. And output range is in the same worksheet. Let's select output range. Click on Okay. And we have the correlation matrix over here. As you can see, the correlation between sales and price of products is 0.796. So there's high positive correlation. I'm highlighting the cell in orange. As you can see over here, you have price of products and you have sales. And the correlation between sales and price of products is 0.796. So that is how you can find out correlation between different variables in Excel. So that's all for this lecture. Thank you so much. 29. Data Analysis: Regression: Hey everyone, welcome to a new tutorial on excellent. Today we're going to take a quick look at regression. I'm not going to go deep into regression is beyond the scope of this course. I've taught progression to other courses. In this course, I'm just going to tell you how to do regression using Excel and why regression is important. Regression is important because regression tells you if there's a relationship between two variables. So if sales increases, or rather price of products in places, does sales increase? Seems intuitive? If price of products increases, then sales should increase. Or if the agent quizzes, then, does salary increase? Or if salary increases, then does spending increase? If salary increases, does food spending or spending on food increases? These are the sort of questions, regression answers. In this case, we are concerned about two variables, right? We're concerned about two things, sales and price a product. We're trying to find out if sales increases, when price of products increases. And for that, we need to go to the Data tab, go to the data analysis step. We need to go to the Regression tab, click on OK. And the input Y range is sales, and y is the variable that depends on an independent way, right? So the difference between dependent and independent variable is that when independent variable varies, it causes some variation. Why are the dependently, but not the other way around? So if, let's say price of products increases, then sales increases due to some reason. But if sales increases, that doesn't mean price of products will increase or decrease. Sales is the dependent variable because it depends on price, a product, which is the independent variable. So sales is the dependent variable, price of products as the independent variable. When price a product changes, it causes some change in sales. So sales is the dependent variable and we are selecting the range of sales. We are selecting the range of sales over a year. And that is the why ridge and the X variable or the independent variable, is the price of products. Remember, remember a graph on a graph, your y-axis and x-axis. With changes in x, there is some change in y, there's a variation in y. So x, basically the variable that changes or the data that changes, which causes some change in y, or the data that is dependent on x. So y is the dependent variable, x is the independent variable. Click on labels. We want the labels to be taken into account sales and price of product status. And the output rate will be in the same, very same worksheet. And that is all we need for our basic regression analysis. We are the regression results. Two things are important in the regression results. The R-square value, R-squared value. I'm highlighting it in yellow over here. Let me choose another color. I've used the yellow, plenty. Green. Yeah. So the R-square value highlighted in green over here, as you can see, tells us how good the model is if the model is good at predicting some change in sales. So we're trying to see if changes in price has some change on sales. So the R-squared value tells us if the model is good enough to predict that, to predict the changes in sales. So the R-square value is 0.6 h per value varies 0-1. If the value is near zero, that means the model is not good. If the value is near one, that means the model is very good. Values are always 0.63, which means the model is decent. And the next piece of information we are interested in is the p-value. And I'm highlighting this in orange. The p-value should be lesser than 0.05. There's a huge amount of statistics to learn before we completely understand what P value signifies. But for the time being, just for demonstrating Excel, I'll just say that p value needs to be lesser than 0.05, right? So I'm highlighting the condition less than 0.05 in purple. If you look at the purple cell over here, or I'll change the color to, let's say, light blue. So that it's easier for you to comprehend this and see this. So if the p-value is less than 0.5, then you see that the prices of products has some impact on sales. Then we look at the coefficient which I'm highlighting in debri. Let me use a lighter color. The coefficient, as you can see over here, highlighted in green is 0.19 is positive. Coefficient tells us if within the change in price, since changes in the positive direction, or if for the change in price, sales changes in the negative direction. So in this case, the coefficient is positive. So this shows us that really increase price by one unit or $1, then the seals changes by 0.19 million units sold. And if this value was negative, it would mean that if you increase price by $1, then the sales will change or reduce by, the sales would reduce by 1.9 million units. So that is what the prices do, or rather the coefficients do. So if price increases, sales increases, in this case, a price increases by one unit, sales increases by 0.19 million units. So that is what regression tells us and that is how you do regression in Excel. Thank you so much. 30. HLOOKUP: Hey everyone, welcome to a new lecture on Excel. And today we're going to take a look at H look-ups. We have done VLookups and today we'll take a look at H look-ups, which is similar to VLookups. When do you use HLookup swelled by the DBA looks kind of like this, right? So instead of having columns with names and labels in it, so you don't have columns with labels. In case of sort of a dataset. What you have is you have rows with liberals. So while in the VLookup scenario, you will, you will using columns with labels. Over here, you have rules with labels. So the entire dataset is a transpose, or rather is the mirror image of what it was while we used VLookup. And why is that? As you can see, the first row, first row has ID, the second row has steals, the third row has price data. Now, in the previous example, we had data that looked like this. Id to the first column are the IDs. The second column has the sales, and the third column adds up prices. So data looked like that. And then we used VLookup to find particular values inside the table using references. And you're using a lookup, dumb lookup number, whatever lookup value. In this case, since the dataset looks different, since instead of having columns with labels, you have rows with levels here to use HLookup. Now, we're going to click on a cell over here. The cell is H7 and we're going to write the HLookup formula. Is that click on HLookup and the lookup value looking at is two, id number two. So that is the lookup value in the table we're looking at is the a data table that, which I'm highlighting using the selector. And the row index is essentially, we're trying to find out the seals for sales for ID number to enter. False, we're not fine. Sales for ID number two. Now it has fallen to see if I made a mistake in the formula. So the formula looks like this. I'll do the formula again so that I can demonstrate properly. So whatever you do is you have to click on the cell, click equal to type in H, looked up. I'll do this quickly. Hlookup. You have to select the lookup value at searching for ID number two, you're searching for the sales volume of idea. But to, let's say that's your problems deeper. You're searching for the sales volume of ID number two. You enter the ID number. You enter the entire table array which stores all the data. And then you enter the row index number. The row index number for sales volume is two into two. In Tacoma, then you enter false, you want an exact match. And the sales value for ID number two is 23, the cn's volume that is. And I'm going to highlight the cell in orange. So as you can see, 23 has been displayed in cell number h seven, and I'm highlighting the answer in red. In the cell number h seven, 3d3 has been displayed, which is the sales value, sales volume rather for ID number two, for product ID number to the product ID number to the sales volume of 23. So that's all about HLookup. Thank you so much. 31. VLOOKUP: Hey everyone, welcome to a new lecture on Excel. And today we're going to take a look at VLookup. What does VLookup do is basically a search tool. If you have a huge database with a lot of different values, numbers, product IDs, than price of product sales, volumes, et cetera, D, down a wide variety of different things. It's sometimes very inconvenient to search for a particular data point, or search for a particular observation or search for a particular product in this huge database, it becomes like searching for a needle in a haystack. So instead of that be a VLookup. And using VLookup, we can specify which product ID, which identify we're searching for and against that identifier of product ID, we can find out different metrics regarding that particular product. For instance, over here in this dataset that I'm going to highlight in yellow over here, we have product id, sales price of products. Wouldn't it be nice if we could just use product ID to find out the sales and price of product for that particular product. And if we do not have to go through the entire database for that, then it will save us a lot of time in VLookup does just that. So how do we implement VLookup over yet? A column which I'm marking in orange is called VLookup ID. I have to ID the bus for products or product to end product for. And for that, I'm going to try to find the seals and the price of products. And for that, we're gonna implement the VLookup command. Select an empty cell, click or equal to type and v lookup or feel a part of it, anything. And once you have inputted a part of it, the entire function will pop up, click on the function, then the lookup value we're interested in are the value against which we're going to try to find out different information. So if you have a product ID of two, what is the sales and price of products for that particular product ID? So the information associated with that particular product ID, that is what we are trying to find out, right? So the lookup value would be two. Let's select two over here, which is Select, which is stored in the cell H two. We have two over here at the table area is essentially their dy, dw is. So the entire table containing information, so the entire table in yellow, the column index number is basically over here. Let's say we're trying to find out the sales, right? For product ID to the column index number is exact match. So false. We want an exact match, we want a data point or observation, or we want information on product id2 and we want an exact match. So let's run this. As you can see over here. We found out the product ID, but since we referenced it wrongly, we've referenced the product ID column. Let's reference the sales column. And as soon as we referenced the sales column and what did we do? We change the column reference Nobody two. Let's start this process again. I messed up the product reference ID in the previous iteration. So let's look at VLookup. The reference value is for, let's say the style. We want to find out information about product ID for it to be specific, we want to find out the sales volume of product ID for. As you can see over here, the sales volume is 34 and the price of products is 35, right? We're going to select the entire table array over here. Then we're gonna specify the column index number. And the column index number. We're going to use this to search for an exact match. And as you can see, for the column index number to the product ID, has a sales by product ID four has a sales value of 34 million. The sales volume is 34 billion. For product ID, for AdWords, we use VLookup. We can just send the product ID number to Excel. And Excel will search for the associated sales volume. Returns, the value of the sales volume. So by changing the column reference number, we can essentially find out the price of products true. And the price of products is stored in column reference, the column index number three. So the columns are, columns start from one, so a is one, B is two, C is three, So product id is stored in column one. Sales in stores is stored in column two, and price of products is told in column three. So let's change the column index number 23. And now we have the price of the product. Let's highlight this in deep blue. And as you can see, the price of the product over here in the blue is also 105. So that is what VLookup does. You have to feed it be Lookup or rather, you have to feed it the lookup value. You have to feed it the data table, and you have to specify the column index number. Remember column start from one. And finally, you have to specify whether you want an exact match. If we want an exact match, then it will find that exact. But I didn't give you the requested value. That's all for this particular lecture. Thank you so much. 32. Match: Hey everyone, welcome to a new Excel tutorial. And today we're going to take a look at the match function. So what does the match function to? Suppose you have a value, you have a sales value, such as over here, right? You have sales unit, sales volume in units, your price, and your ID of the product. Now suppose you have a sales volume value and you want to see if it is present in the dataset that I'm highlighting in yellow. So if you want to see if this value is presented the data set, you'll use a match function. What does it match function do? A match function takes a value, searches for it in an array. If there's a match, it will return the position of the value in that area. For instance, the areas over here are the id, the sales volume, and the price. There are three areas. So we'll search for some value in these three areas and it doesn't match, then the match function will return the position of the value. So let's see if there's a match for the value for. I'm highlighting the cell in light blue right after the match for the value four. And we are going to input the formula in cell H7 type in equal to typing match. The value is in H3, which is for insert a comma, and then search for it in the product ID. Now the product idea, it has a label which is ID. So you can include the label or you can exclude the level. In either case, the positions will be determined by how many values are there in the array. Let's say we include the label in the beginning for this particular iteration of the match function. So we have included the area which is A1 to A6, and now we want an exact match. So if there's an exact match, the match function will return the position of four. Let's run this and the position of four has been returned as five. I'm going to highlight this in light orange. Is it five? In positions one, you have the id in position two. You have one in position three, you have two in position four. You have three in position five, you have four, as you can see in the area in the idea array in position five, you have four. Now let's try to search for some, some other value. And the other value we are going to search for is let's say sales volume of 34. So the sales value of 34 will be entered into the match function, the area we are going to search for a sales volume, and I'm going to include the label in the search and we're looking for an exact match. So type in 0.34 appears at position five. Sales volume. Sales volume in units is position 110 is positioned to 23 is position 312 is position 4.35 is position pipe. I'm highlighting it in red. So 34 occurs at position five. Now let's say we exclude the labels, right? So I'm going to change the formula here. The formula, as you can see in the formula bar, is matched 34, B12, B6. Let's exclude the label. Let's, let's make B1 B2. So now the match function will search for 34 in the Arab B2 to B6. As you can see, it has been enclosed in a selector box, right? So let's run this function. And the position now is for position has changed because right now, position one is it, it contains the value ten. Position two contains the value three. I'm highlighting it in green. Position three contains the value 12, and position four contains the value default. I'm highlighting it in green. So position four has the value 34. So that is what a match function does. Thank you so much. 33. What If Analysis in Excel: Hey everyone, welcome to a new lecture on excellent. Today we're going to take a look at what if analysis for this type of analysis, I have a dataset over here. You have iPhone or number of iPhones souls sold by a shop. And you have the number of iPad sold by a sharp. So I'm highlighting the value of the number of iPhones sold by a sharp in orange and the number of bad sold by the shop in yellow. So let's say you have a shop and you sell iPhones and iPads and some other things, but we're just concerned about Apple products. You sell iPhones and iPads. So you sold 2000 iPhone's less than a month and he sold 400. I bought a bed in a month. And the price of the iPhone is $1,000. I'm highlighting that in green, in the price of the iPad is $700. I'm highlighting it in light blue. So the number of iPhone sold is highlighted in orange is $2,000. The number of iPads sold by the shop is highlighted in light yellow, and it's 400. The price of the iPhone is 1,000, the price of the iPad is 7,000, and the total revenue is equal to number of iPhone sold into price of an iPhone and number of, plus number of iPads sold into price of the iPad. So E2 into A5, A2 is number of iPhones sold. And if five is price of the iPhone, so A2 and A3, A5 plus you have number of iPads sold by the sharp, which is 400 into the price of the iPad. So the total revenue for this door is 2,280,000 in a month. That's a pretty well-to-do store. Now what if, let's say there's a scenario when you sell more number of iPhones. You have a scenario where you sell more number of iPhones, or you have a scenario where you sell more number of iPads. What happens to the total revenue? For this sort of question? We have to use what if analysis and to use whatever analysis we go to the Data tab and we go to What-If Analysis. Just follow my cursor. We go to whatever analysis. Let's use Scenario Manager. Now. We'll define a scenario for that. Click on the plus symbol Ella, I have, I already have a scenario to find, but I'll define a new scenario. Click on the plus symbol. Scenario name is 4,000. I bad soul. And we'll be changing the number of iPads sold. So I'm going to click on the cell over here, which is B2, which is number of iPad sold. So we're going to change the number of high-pass or notice we are using absolute referencing over here because we do not want the cell to move around. So we'll be changing the cell, the number of iPads sold, which are rather we'll be changing the value of the salvage stores the data on the number of iPads sold. So what happens if 4,000 iPads are sold? So let's click on, Okay over here. So let's say 4,000 iPads or sold. What happens to the total revenue? Let's find out the summary. And let's click on, Okay. And as you can see over here, the summary has been printed in a new worksheet. So we're changing the value of the cell B2. I'm going to highlight this in orange. So if 14000 iPads are sold, profit made by the shop is 4,080,000 or the total revenue generated by the sharp is 4,800,000. I'm highlighting this in green. So the total profit made by the sharpest 4,800,000, 4,000 iPads are sold. Now, I had another scenario over here, right? So let's take a look at that scenario. And that scenario, what I'm doing is I'm changing the number of iPhones sold. So I'm changing the number of iPhone sold to 3,000 and I'm changing the value in cell E2. If you notice the screen cell E2 is highlighted in orange over eager. Currently without an iPad. Iphones was sold or were sold in the previous month. But I'm trying to find out if 3,000 iPhones are sold, then what the total revenue will be. So let's find out the summary and the result. Sellers sell lumber in I1, as you can see in the highlighted cell in the spreadsheet. Click on okay. And when you change A2, and then I'm going to highlight the data in yellow and change cell A2, 2000-3 thousand. In a scenario, 3,000 iPhones are sold, then the total revenue changes do 3,280,000. The total revenue changes to 3,280,000. I'm highlighting the syllabus stores the total revenue in late week, the 3,280,000. Now we can do something else. We can do another what if analysis and this sort of what if analysis. It's called Goal Seek. So we're going to set the value of total revenue by 1 million. So we want to find out what will be the number of iPhones that should be sold. If the shop has to make a sharp has to have a total revenue of 5 billion. So what number of iPhones sold will lead to a total revenue of 5 million. And this cell we're going to change. We're going to change the number of iPhones sold that is highlighted in orange. In the spreadsheet. We're going to change the number of iPhones sold until we arrive at a total revenue of 5 million. As you can see over here, 4,720. After a ran the what-if analysis, the world of analysis, automatically change the value of the number of iPhones sold. So now it shows that do have a total profit of 5 million. And I'm going to highlight this in yellow now to make it and bright yellow, that is to make it more legible. So to have a sale of 5 million, the number of iPhone sold has to be 4,720. And I'm highlighting that cell which contains the value 4,720. In Skype, you 4,720 iPhones would need to be sel sold if you want to generate a total revenue of $5 million. So that's it about whatever analysis. What are the analysis can be done using solver to. In that case, Solver uses optimization algorithms to find out different values based on constraints. And based on if you're trying to maximize something, minimize something, or set the value of an operation to a certain value. So that is all about what if analysis. Thank you so much. 34. Using Solver to Solve Equations: Hey everyone, welcome to a new lecture on excellent. Today we're going to take a look at the Solver Add-in. So let's say we have to solve the following problem. You have wages per hour per worker. For each worker is paid a certain amount of money per hour. Let's wrap this text. Each worker is paid a certain amount of money per hour worked, uh, one is p $2 per hour could do is pay $3 per hour, but Guthrie is paid $5 per hour. Block of four is paid $6 per hour. Welcome. Five is bid $7 per hour. These are the we just per hour per worker. And you're trying to find out how many hours they should each work so that the company wage bill is minimum. You don t know how many hours they should each work. You're trying to find out how many hours each should work. So you don't know this, right? You don't know this. You're trying to find this out using some mechanism, some patient. And then you're trying to find out all you can minimize the total wages per worker, given wages per hour of each worker. So you're trying to find out number of hours each worker should work so that the total wage bill at the company's minimum. So how do you calculate the wage bill of the company? You need a formula here. The formula is, click on equal to E2, which is the clock tower, into wages per hour. Hours worked into wages per hour plus hours worked into wages per hour plus hours worked into we just borrow a look at the formula bar. I'm writing the formula right now. Into ages per hour. There are five workers. So each worker has a wage per hour. Each worker is going to work for a certain number of hours. And based on that, we are trying to find out the total wages right now it's showing zero because we don't have data on how many hours people should work. Or less. Renamed this particular piece of information. Workout was right? So right now the total wages is zero, but we are trying to find out how many hours each worker should work so that the total wage can be all the wage bill can be minimized. Let's go to Data tab. Let's go to Solver. I'll do this again in case you did not see this the first time. So click on Data and then go to solver. So the objective function, what are you trying to find out? You're trying to find out the total wages and you're trying to make sure that the total wages is minimized. And you're going to change. Which variables are you going to change? Which variable cells? You're going to change the hours worked by workers. That is what you're going to change. Let's say there are some constraints. The constraints are the work hour of work. One has to be lesser than equal to 10 h he wanted worth more than 10 h. The work hours of work Good. Two, which is cell number 83, has to be lesser than 15 h. He won't work more than 15 h. Let's add them because strain. And in this case we're going to add a constraint for worker number three or cell A4. The work hour of work at three has to be less than 5 h. He won't work more than 5 h. So we have three constraints. Let's add another one, a final constraint. The work hour of work before, which is contained in cell A5 has to be equal to 4 h, so he will only work for hours, not less, not more. Click on Okay. Now, click on solving method. Let's use simplex. You can use simplex, you can use evolutionary, you can use GRG Nonlinear. These are different optimization algorithms. A lot go into the math behind this underlying these optimization algorithms. Because these algorithms are quite complex, they are taught in operations research courses. Just use simplex. Click on Solve. Perfect. And we have the solutions to the problem over here. Solver says that if you make worker for work for hours, then the total wage bill will be minimum. That is what solver says. You don't need to make the other workers work at all. If you make workup for work for 4 h, total wage bill will be minimum. The total wage bill in this case is highlighted in orange over here, $24. So if you make worker for work for hours, that is how your total wage bill will be minimized. So you can play around with this solver. You can add different constraints depending on the problem. You can add different problems to the solver. Solver will solve it for you. So you can use it for a variety of different things. So that's all for this lecture. Thank you so much for joining me in this course. Thank you. 35. Pivot tables and Pivot charts: Hey everyone, welcome to a new Excel tutorial. And today we're going to take a look at pivot tables. But what do pivot tables do? Pivot tables basically make life easier for you. It makes life easier for you. Pivot tables are used to summarize information. Suppose you want to find out the average of selling prices over here in the dataset that we have. Suppose you want to find the inventory cost average, or suppose you want to find out how many products are different colors are there? Suppose you want to find out the count of different colored iPhones, iPad or Mac box, you can use a pivot table and this will summarize information in a nice tabulated for, and you can easily access information and data and analysis of information using pivot tables. So we have a data set W, we have the product types which are iPhone or iPad mac book. You have the color which is, which can be white, black, gray, or cream. The inventory cost of keeping the iPhone or iPad back books and the inventory or the selling price. So this is a refurbish retailer. They sell refurbished products. So we're going to use this table as an example. The entire table that is highlighted yellow. But let's go to Pivot Tables. And over here, as you can see, there are two options. You have PivotTables, have Recommended PivotTables. Let's click on Recommended PivotTables. So in this case, Excel will recommend what you should represent in the pivot table. But we're not interested in that. It has a lot of recommendations is telling you to include inventory cost and selling price in the pivot table, but we're not going to go by that. And what we're gonna do is we're going to delete the sheet, contains the Recommended Pivot table. What we'll do is we will create our own pivot table. So the range of data is the entire table basically. And we're going to get the output in the data underneath the dataset in the same worksheet. As you can see, PivotTables has appeared underneath and neither table. So over here on the right-hand side of the screen you can see that there is a box called Pivot Table fields. So your field limbs, your filter, you have columns, you have throws and your values. What do rows do? I start with rows. Rows basically showed is a box where you can specify which variables you want in the rows. Do you want product type in the rows be one color and the roads be what? Inventory in the rows, the one selling price and the rows, columns over here. I'm clicking on the column box and the box has a green border to it. Right now since I'm clicking it in the column box over here, you have to specify what you want on your columns, which variable you want on your columns. And here the values box, I need the column box in the values box, basically, you have to specify which values you want to be displayed in the pivot table. Do you want count of different variables? Do you want some of different variables or doing an average of different variables, what we want to display in the pivot devo, any of the filter box which can be used to print the data is not very useful. What we're gonna do right now, but it is there. So let's select a variable for the rules. Let's say we want product type and the rules different product types. We're going to click on it and drag it into the rows box. As you can see over here, the product types are here. So I'm going to highlight it in yellow. The product types are here. Oops. The pivot D, we'll basically the entire window closed. Yeah. I'm going to delete this. So we're going to have the product types in the rows. Now this is clearer and in the columns we are going to have color. So you have prototype in the rows and the columns you have colored. The pivot table is displayed over here. I'm highlighting the entire area in light green. Light green doesn't look good on this. Lets the light blue. Yeah. So what sort of values do you want the pivot table to display? Let's look at different iPads. The count of different colors in terms of iPads, iPhone and MacBook. So how many white colored iPhones are there? How many white colored MacBooks are there? How many white colored iPads are there? How many cream colors are there? How many white color or gray colored? Iphone, iPad or Mac book is there? How many gold color iPhone or iPad mac book is there? So we're going to click on color, drag it to the values column, as you can see over here. Now you have the count of different colors. So there is one black iPad, one creamer iPad, one goal iPad. There is one black iPhone, too wide iPhones. There is two. There are two gray MacBooks and there is one white MacBook. And over here in the grand total column, you have the total of the counts. So you have three iPads. Iphones, you have three MacBooks and two grand total counters, nine. Now let's say we want to find out the inventory cost. And we want to find out this inventory costs for different products. And we ought to find out the average inventory costs. So let's include inventory in the rows. As you can see over here. Now you have the inventory costs to, the inventory costs are displayed right over here. So for iPad, the inventory costs $100, $110, and $126 for the three different iPads. So there are three iPads and the inventory costs are, thus, you have three iPhones and three MacBooks. Now, let's remove inventory costs from the rules and less include inventory costs in the value box. And I have inserted inventory cost in to the value box. So I'm going to remove colors from the column. So as you can see over here, the total inventory cost of iPad is 336. Total inventory cost of iPhone is 477. Total inventory cost of MacBook is 401. Let's say we want to find out inventory costs for each product according to color. I'm going to include color in the values box. The count of color over here, as you can see, is 333. I'm going to include color in the row box tool. And now, as you can see, the black iPod has an inventory cost of 100. Cream iPod has an inventory cost of 126, I'm highlighting it and orange. Go light pad, iPad rather, not iPod, has the inventory cost of hundred ten. Highlighting it an orange again, black iPhone has an inventory cost of 234. Wide iPhone either inventory cost of 243, et cetera, et cetera. Total count is nine. And total inventory cost. I'm highlighting it in green over here as 1214. That is the total inventory cost. Now, let's say we want to find out the selling price two and the total selling price. As you can see here, the sum of selling price columns over here. And what we did was we clicked on the selling price and brought it to the values box. And we pulled it into the Values box. And I'm going to collect the column in green. So the selling price of iPad is 16, 40, selling price of iPhone is two-to-one. A selling price of MacBook is 2430, and black eye pad sold for 600, cream iPad, sold for 560. Goal iPad sold for four. Black. Iphone sold for 678. White iPhone sold for 1,540. And the grand total, total selling price is six to 18. So this is how you can find out selling price. Let's say you want to, instead of the total selling price, you want to find out the average selling price. So what you can do over here is you can click on the inflammation or ice symbol beside the selling some of selling price variable inside the value box. So as you can see over here, the value box here, inside the value box you have this option called sum of selling price. Double-click it. And you can see it's highlighted in green over here. And besides that, there's an i, small i symbol. It's a button of sorts. You click on that button. Instead of the summer selling price, you find out the average selling price. And the average of total average selling price is 698. I'm going to highlight it in blue. 698. The average selling price of what the pivot table has done is it has shown selling price based on each category. So for each category there is a separate average selling price. So iPad has an average selling price of Pi fourths this coloring it in green. Iphone has an average selling price of 739, highlighting it in yellow. And Mac book has an average selling price of 810, highlighting it in blue. So as you can see, each category has an average selling price and the total average selling price highlighted in blue down here, 698. So you can play around with what you want as your metric or what sort of metric you want out of the pivot table. If you want the sum of selling price, you can specify that and you'll get the sum of selling price if you want the average of selling price or inventory costs, you'll get that average. But you have to specify it to Excel. And the way to do that is to click on the particular variable in the values box and click on the eye symbol. The eye symbol, as you can see, is beside the value in the values box, beside the name of the variables average selling price or selling price variable that's here. And beside average selling price, you have the eye option, which you click. And then you can change everything. Over here. You can find out the total sum we can paint. The county can find out the maximum minimum product called numbers, et cetera. Now let's say we want to find out what percentage. Let's now remove all of this, the products and everything. Before that, let me do something else. I'm going to remove values from the columns. And then what we have leftover here is the rules of which comprise off different types of Apple products and their colors. Let's say we're trying to find out which color accounts for what percentage of the total number of Apple products sold. So what percentage is black, what percentage is Queen? What purchase percentages goal? So what I would do is I am going to drag in color into the values bar, value box. Over here. I have the entire column highlighted in green. So we have three iPads, iPhones, MacBooks. I'm going to click on one. I'm going to then right-click or control-click and summarize values by you have this option over here. You can summarize values by average, show values as percentage of grand total, right? Or column total or rototiller, parent row total or parent column total. So you can show any particular data as a percentage of the total. So let's look at it at the percentage of grand total, because the grand total over here is nine. So as you can see, black iPhones, iPad constitute 11.1, 1% of the total sales made Creamery pascals to constitute 11.1 word per cent of the total sales made go-live birds, god, does constitute 11.1, 1% of the total CSP. So basically, it's distributed evenly because you have three iPads, MacBooks, three I foods, and total sales. So what you have to do is you have to click on a cell. You have to control click if you're using a Mac or right-click. And then you have to go to the summarize value as option over here, summarize value S. And you can summarize value as grand total column, total, row, total pair in total paid column total, etc. You can summarize values by this is another way to do this. Instead of using the I button inside the value box, you can summarize values by sub. In this case, it will not show anything useful because we don't have the sum of colors. So we'll go back to count. So less includes selling price in the values column, as you can see, the selling price or back in the pivot table. So less. Somewhere I click, right-click or control-click on one of the cells underneath the selling price column, index selling price column. And we'll summarize values by average. As you can see, the value has been summarized by average. So this is another way of changing what your pivot table is displayed. If you want the pivot table to display averages instead of sums is the sum of total selling price or sum of total inventory cost. You have to, you can click on one of the cells in the people. And you can then right-click or control-click, drag down menu or drop-down menu will appear. And in that, you have to be ethically girl. Summarize value by option. You're to go to the summarize value by option, you can select sum count average, max. Let's select MAX. And as you can see, the max is thousands, the most price here. The most pricey items that are sold. Cost $1,000 to the customer. So $1,000 was the maximum selling price. So that is what you can do. That is pretty much pretty much what this is all about, what conditional formatting is all about. And that is pretty much what you need to know about pivot tables. Pivot tables are a great tool if you want to basically summarize everything. Now let's look at pivot charts. Click on pivot charts over here, it's under the insert ribbon. Click on pivot charts. As you can see over here in the PivotCharts, do you have Excel classifying your iPads as black creamer goals. So there are, there, is there or the selling prices of the iPads represented as bars. So your bread, cream or gold iPad, your black and white, I fully agree. And white MacBook and the selling prices are represented as bars. So right-click on this or control click on this. And you can change chart, tried to line chart by chart. Let's click by chat. So Pi over here, as you can see, you have the count of different variables. So how many of the phones that you have the count of different folds, different Apple products. How many of the Apple products or iPads are many black iPads? How many cream iPads or media goal? Ipad, how many black iPhones? How many are white iPhone somebody a green bag, books. How many are white, black box? That is something you can do. I'll go back to the bar representations to bar column. Let's see what else we can do. So what we can do is we can include inventory cost in this chart. Just click on inventory costs in the pivot chart Field window and drag it into the chat. Yes, now you have inventory cost and is being represented as gray columns. Sum of inventory costs has a gray color over here. So basically the gray columns that you see in this chart, our inventory costs, Let's remove selling price and you have the inventory cost us only the inventory cost left. So gray Mac book has an inventory cost of around 56. Let's check this. Gray Mac book has inventory cost of 124 plus 132 to 56. So that is what you can do with a pivot chart. So you can, then you can go back and represent selling price. You can remove colors from the chart. And as you can see right now, without, if you want to remove colors from the chart, the pivot chart is just showing iPad, iPhone and MacBook is not showing different colors for iPad, different colors for iPhone, different colors for MacBook. Because we have uncheck the colors option is just showing total number of iPads and the selling price, total number of iPhones and the selling price, total number of MacBooks and the selling price and total inventory cost pipette, total inventory cost for iPhone, total inventory cost for Mac book. That is what is showing. Let's remove product type. And it's just showing the total selling price and the sum of inventory costs. Now, let's say we want to find out which colored product has the most selling price. So as we can see over here, white is the product. White colored products sell more than any other product. White-collar product, colored product sell more than black, green, gold, or gray white colored products, the most selling price. And finally, after we do one last thing, there's an option over here and just follow my cursor over here. It's called fields, items and sets. You can specify a new field using this. Suppose you want to find out the total profit, right? So you calculate a pill. So basically what you do is you go to the field option, you click on Calculated Field, and you name the field, total profit. And it will give the formula for this formula is selling price minus inventory costs. Click on Okay. And you have a new field called total profit. And now in the chart we want to see total profit by color. As you can see, the white colored phones, white colored phones over here, is most profitable. People like to buy white colored phones, iPads, MacBooks essentially. Lets see which product is most profitable. As you can see over here. I'm going to drag and drop the product type in do the row category. Yeah. And now we can see which product is most profitable. So after dragging and dropping the product type in do the row category or the access category. As you can see, MacBook is the most profitable, right? Mac book has the most total profit. So that's all about pivot tables. Thank you so much, I'll catch you in the next lecture. Thank you so much. 36. Statistical Analysis Process: Hey everyone, welcome to a new Excel tutorial. And today we're going to look at how to do data analysis and what to look for in data analysis and how to interpret data analysis results. Now for that, I have a dataset over here. I'm highlighting the dataset over here in, Let's highlight it in yellow. So this is the dataset. Now we have a number of parameters in this dataset. What is the objective of this exercise? First of all, we are trying to find out which article on medium.com will become popular. And how do you measure popularity? Well, the number of likes, each article gets. The articles get claps or likes. And based on that, we determined which article is popular and which article is not popular. So greater the number of likes an article gets. It is more popular just like YouTube or any other online content platform. So we're trying to predict or find out what leads to popularity. We're trying to find out what leads to popularity. What are the causes of popularity for medium.com articles? And side-by-side, we are trying to predict which article will become popular based on the number of likes the article gets. So if the article gets more lights, it's popular, right? So we're trying to predict what are those factors that result in an article getting more likes? And we're trying to predict based on those factors, which article will get, what number of likes. So the claps or like variable over here, and I'm highlighting the entire column in light blue, is known as the y variable, the dependent variable, or the predicted value. What is dependent variable mean? Well, dependent variable means that the number of clubs and article gets in this case, which is also represented as the y variable. The number of clubs is dependent on some other variables. So when some other variables Very, then they impact the number of likes an article gets. So that is why the number of likes or claps the article gets is a dependent variable and it's dependent on some independent variables. And what are the independent variables? The independent variables are titled score, title sentiment score, length of title, content sentiment score, duration in minutes. And the interaction term between title sentiment score and content sentiment score. Now I'm going to go through all of them. So now we know that lapses the dependent variable or the variable we're trying to predict. We're trying to predict the number of clubs and article will get. And we're trying to find out what influences the number of claps or likes and article gets. The first thing that may influence the number of likes and article gets is the title sentiment score. Now the title sentiment score is babies between minus two to two. How did I get this? I scrape the data, then I ran sentiment analysis on this data. This is the part of one of my projects I'm doing for my PhD. So it's a sentiment scores vary between -2.2 with minus two being extremely negative sentiment, plus two being extremely positive sentiment. And anything around zero means neutral sentiment. So what is extremely negative sentiments? I'm doing extremely poorly. I'm extremely sad. That is negative sentiment, that is high negative sentiment expressed through text. What is positive sentiment? I am extremely happy I'm doing really well. That is positive sentiment or extremely positive sentiment expressed through text. So minus two, extremely negative plus two, it's really positive. And what is neutral sentiment? I am okay. That is neutral sentiment. You're not expressing extreme positive or negative emotions or sentiments. So anything around zero is neutral sentiment. Anything or minus two is negative sentiment. And the thing around plus two, It's positive sentiment. So there's a scale. And title sentiment score varies between minus two to two. What is the length of the title in number of words? How many words does the title have? That is another independent variable. Contents sentiment score, again, varying between -2.2 is another independent variable duration in minutes. There's some other independent variables. So as you can see over here, named the independent variables as excess. That is common convention. So independent variables are called X, X1, X2, X3. So title sentiment score is X1, length of title and number of words is x2. Contents, sentiment scores extreme. Let's name it X3. Duration more minutes is x. And what we have is an interaction term which is titled sentiment score into contents sentiment score. What does an interaction term to an interaction dumped? Deaths for complementarity. So for different values of titles, sentiment score, how does content sentiment score impact the number of likes an article gets? Let's say title sentiments covariance between minus two to two. So for the value of minus two for title sentiment score, how does content sentiment score impact the number of likes that article gets for the value of plus two. For title sentiment score, how does content sentiment score impact the number of likes the article gets? So basically you're testing if two variables impact the dependent variable in a complimentary fashion. So do two variables increase together or decrease together while impacting the dependent variable or the predicted variable. So again, to recap, the number of likes is the predicted variable we're trying to predict the number of likes an article gets. And title sentiment score, length of title contents, sentiment score, duration, and the interaction term title sentiments go into contents sentiment score. The independent variables in that the interaction term is there because we want to see the impact of title sentiment score and content sentiment score together on the dependent variable. Y is dependent variable or predicted variable, x is our independent variables. So now that we understand the data, Let's first look at if the data is normally distributed. Now what do I mean by normal distribution? For that? We will do a histogram for the number of clubs or lifestyle article receives. So I've selected the number of clubs column. I'm going to insert, I'm going to go to histogram and you go to lot of histogram. Let's change the title. Histogram or likes. As you can see over here, this data seems unbalanced. Most of the data is around 1,200.1. And as we go further away from that, the data becomes sparse. So most of the data is at the lower end up the lower end of the range, or lower end of the spectrum. Most articles do not get a lot of likes as we go away from that. Some articles, a few articles get a lot of legs. Is this normally distributed? Now what is a normal distribution? Normal distribution is essentially a distribution where most of the values are around the mean, are around the mean of that vague. So what is the mean of claps? Let's just find out the mean of claps. Using the average function. The mean is 113. So are most of the variables centred around hundred and 13? It looks like they're centered around 113 rent a previously I said the dataset looks unbalanced. But now that I have actually gone into the different metrics and the dynamics, we find out that dataset may not be imbalanced. Most of the data might be distributed around the mean. As you can see most of the details between hundred one to 201. So what we need is a better representation of the data. This does not look good. What we have over here does not look good, the range does not look good. What this range tells us, there are outliers, what outlet outliers are values that fall beyond the general dispersion of the data. So most of the data is distributed around the mean, and most of the data is around 100.1313 is a mean, highlighting it in purple. Most of the data is around 113, but then you have likes, which are around 900, 800. So some articles get 800 likes, some articles get 900 legs. These are far away from the average or the mean and the general dispersion of the data, the general distribution of the data. So these data points are outliers. We'll talk a little bit more about outliers when I do the scatter plot next, but does not look like a good representation of the data. So what we're gonna do is we're going to format the data series. So I'm going to click on one of these columns are towers over here. I'm going to right-click or control-click and I'm going to click on Format Data Series. And what I'm gonna do is I'm going to change the bin width to 30 over here. I'm going to change the number of bins to 60 over here. I'm going to change the -100 over here. I'm going to change the underflow bin. As you can see over here, most of the data falls around the mean. So this might be normally distributed, and if it's not normally distributed, you gone to run a regression. You can't do certain techniques. In this case, it seems like it's normally distributed. Now let's do a scatter plot. I'm going to delete this. I'm going to go to Insert. I'm going to select Scatter Plot. And I'm going to select the data for this scatter plot. Chart. Data ranges. Basically, I've selected the chart data range. Name is basically scatter off likes, likes, by over content than demand. School. X values are, Let's select the x values. The x values are content sentiment score and the y values are. Claps. Click on. Okay. Now we have a scatter plot. Let's change the title of the plot. That's always good practice. Like versus sentiment grows. As you can see in the scatter plot, most of the sentiments scores are, most of the legs are 0-100. Most of the legs are 0-100. And some of the lakes like over here, the range of around 10,000, 800,000 are far away from the general dispersion of the data. The general dispersion of the data is 0-100 or 150. Some of the data is around thousands, some of the data is around 800. These are outliers. When you have outliers, it skews your data. What does it do? Well, it skews your data to the left or the right. So when you have most of the data to the left of the mean or to the right of the mean. That means the data is skewed. If the data is skewed, you cannot run regression. So you have to make certain that the data is not skewed. If you run regression, when the data is skewed your regression, we'll go, alright. So from the scatter plot we can see that we have outliers because of which the skew and kurtosis, kurtosis basically measures the amount of outliers that you have. It measures how the outlier affects your data. So since you have outliers, you still and you have ketosis. So what can we do to remedy this? The most used procedure to remedies outliers. Problems created by outliers or skew or kurtosis is to do a log transform. So I'm going to define a new table log off like and this will be the new y mu predictor variables. This will be the new wide formalized type in equal to type in log. Select the value for number of claps. And you have the log of likes. Double-click on the lower right corner of the cell and the formula is copied to all the observations. And now you have the number of likes or log of the number of likes for all the observations. Now that we have this, let's proceed. Do the next step. What do we do next? The next thing we do is we find out the descriptive statistics. And to do that, we're going to go to data. We have the data analysis. Optionally here. Click on Data Analysis will do descriptive statistics. Click on Okay. And here to select the input range. And the input range we want is basically H one to H, which is the last row over here. Let's check 40303 Labels in First Row summary statistics. Let's click on Okay. As you can see, the mean of the log of likes is 1.26. Highlighting it in green, standard error is 0.0 for very low standard error measures, if your sample is good enough to predict about the general population, about the general population from which the sample was drawn. What is the population? Population is the population of all students in the United States? A sample would be 100 students from the population of all students in the United States. The standard error tells you if your sample is good enough to predict the population parameters or predict about the population, or make some prediction about the population. Standard error of 0.04 is very small. So your sample is good. Median is 1.14 is near mean. And if medial is near mean, that means the data is normally distributed. There is hardly any skew because of the locks transform. We removed skew, removed kurtosis. Because after the log transform, what you have is your median and mean which are almost equal. They are close by. So our data is normally distributed. Most of the data is dispersed around the mean. Standard deviation is 0.88. It's quite a bit so. Basically, there is a lot of variants. There is a lot of variance in the data which the log transform could not deal with. Let's see how these veins impacts the final results. Kurtosis, which is a measure of outliers, is -1.0 line. It's okay. It's not too high if it's around four minus four, etcetera, It's too high. Skewness is 0.3. For very low, it's around zero. So there's hardly any skew left in the DDA because of the log transform. Ranges to 0.99 range is high. Given this context because you have outliers and log transform removes or reduces the effect of the outliers. But still, there will be some attack. Minimum is zero, maximum is 2.99, sum is 510, pounds is 400 true because we have were only two observations. Now we'll go back to the sheet containing the data. Now that we understand that there's hardly any skew and the data is likely to be normally distributed. And we can do a histogram again. We can do a scatter plot again to test this, but I will refrain from doing that and I'll go directly to correlation and find out if data or rather the new variable log apply X is correlated with the other independent variables. Other is the dependent variable correlated with the independent variables. Let's do some correlation analysis to test that. Click on OK. And the input range is basically the entire Data. Labels in First Row, check on that. Group by columns, check on that. Click on Okay. And here you have the correlation matrix. Now, reading the correlation matrix as easy as I showed in my introductory correlation analysis lecture. The correlation between title sentiment score and titled sentiment score on the column is one because title sentiment score and titles sentiment score at the same variable. So it's one, as you can see over here. And remember, correlation coefficients vary from minus one to one, with zero being no correlation and minus one being extreme negative correlation, plus one being extreme positive correlation. So correlation between two variables of the same nature is around one or minus one. In this case, correlation between title sentiment score and content sentiment score is one because they are the same value. Now what we are concerned with is the correlation between our dependent variable, which I'm going to highlight in blue over here, and content sentiment score. What is the correlation between contents sentiment score and our dependent variable, log of the number of likes and the correlation is 0.1. Going to highlight it in yellow. This is not the kind of Utah I wanted. So I'm going to change the color and the correlation is 0.103. Now what is the correlation between log of likes and title sentiments called the correlation, which I'm going to highlight in a light orange over here, is again 0.006. There's hardly any correlation. So what does this tell us? Well, probably title sentiments calls do not impact the number of likes an article might get. Maybe title sentiment scores have no impact on the amount of likes and article guests. Does content sentiment score have an impact on the amount of likes an article gets the contents sentiment score. Correlation. Pearson's correlation coefficient is 0.103 when you're finding correlation between log of y or log of likes and content sentiment score. The correlation is 0.103 with the correlation coefficient that's around 0.103 is difficult to say that content sentiment score will have any impact on the number of likes and article gets because the correlation coefficient is too small. Ideally, you will have high correlation coefficients, either positive or negative, if your correlation coefficient is around 0.7 or let's say your correlation coefficient is around -0.7. That means there is a strong relationship. So any correlation coefficient above 0.6 is desired. In this case, the correlation between log of lights and content sentiment score is 0.100. So does content sentiment score impact likes? Well, it's hard to say so given the correlation coefficient, on the other hand, the correlation coefficient between log of likes and length of title is -0.14. Again, it is very, very low. Since it is very low, it's difficult to see that the length of the title has any impact on the number of likes that article gets. So should we proceed? Well, we should proceed with the regression to find out if there is An impact. So we go back to the original data, and next we go to Data Data Analysis again. And then we conduct regression. I have the range for the independent variable, which is h12h 403. And the range for the independent variables is the range I'm selecting over here. 12403. Input range is H one to h403, which is the log of likes. And that is the input range for the dependent variable or the predicted variable. And x ranges 1-403, which is the range for the independent variables, are the X variable or the predictor variables or predictor variables are used to predict our predicted variable, to predict the outcomes of a predictive variable. Click on Labels because we want the labels to be there. I want residuals, I want residual plots and I want normal probability plots. What are residuals? Residuals essentially predicted value minus original y value. So we're trying to predict the number of likes that article gets. And we'll have, upon running regression, we'll have some value for the number of likes that is found by the regression. The regression analysis basically predicts values for the number of likes. And then you have the original observation, which is the number of likes. The article actually has. Regular residuals are basically predicted values or values found out by the regression minus observed values. So if, let's say the actual observed value for number of likes is 100 and the value finally, by the regression using the equation that we are telling Excel to execute is, let's say 150, then the residual is 50. So predicted value minus actual value, predicted value of Y minus actual observed value of y. So that is the residual is also known as error because it's obvious that if your predicted values far away from the original observed value, then that means there's a lot of error in your regression. Your regression model is not good enough. So that is a residual and we're doing normal probability plots because we want to see if the d, dy is probable. The data is normally distributed. So click on okay. The regression results yet and it's a new sheet. As you may remember, the R-square value and the adjusted R-square values are important. The r-squared value, which I'm going to highlight in green over here, tells us how good the model is in predicting values for likes, for the number of likes an article gets. The R-squared value is 0.04. R-squared varies 0-1. If it's near zero, that means the model is crappy. If it's near one, that means the model is very good at predicting the number of likes. The value is near zero is 0.04. So the model is not good. Adjusted R-square takes into account the number of regressors or number of predictors or number of independent variables you have. The more independent variables to include in your regression analysis, the adjusted R-square value reduces. Why does it reduce? Because more independent variables you include your basically artificially bumping up the R-square value and adjusted R-squared basically takes that into account and reduces the R-square value. Adjusted R-square is basically derived from the R-squared value and takes into account the number of predictors you have. Adjusted R-square value is 0.03, very less, 0.3 per cent, or rather three per cent. R-squared is 4% are adjusted. R-square highlighted here in orange is 3.4 per cent, which are very, very low. But F value is 3.83. And F value is important because it tells you if the model is actually valid. So if the F value found by the regression is greater than the significance F value that you have over here, I'm going to highlight this entire Significance F value in blue. So if F value, which is 3.83 is higher than the significance F, which is 0.002. That means your model. Value. So in this case, F value from the model. After calculating parameters of the model is 3.83. Going to highlight it in some other shade. I'm going to highlight this in yellow. So F value is greater than significance of F. Significance F value, the significance F value is 0.00 to F value is 3.8. So since the F value is greater than Significance F, That means the model is valid. What else do we need? We need to take a look at the p-values. I am going to highlight the p-values in orange. As you can see over here, the p-values are highlighted in orange. Let's choose another color. Let's choose green, grass, green vegetation green. The p-values are important. Remember what I told you about p-values in my previous regression introductory lecture. P values need to be lesser than 0.05. So if p-value is less than 0.05, then you say that the independent variable impacts the dependent variables. So let's take a look at the p-values. The p-value of title sentiment score is 0.86, is greater than 0.05. So title sentiments code does not impact the number of likes. The article gets. The p-value of length of title says 0.001. So it is lesser than 0.05. So length of title impacts the number of likes and article gets. Content sentiment score has a p-value of 0.00, 400467. Continents sentiment score impacts the number of likes and article guess that's intuitive. If contents sentiment score is positive, then people are more likely to like an article. If Canton sentiment score is negative, then people are probably less likely to like an article. The interaction term has a p-value of 0.89, which is greater than 0.05. So interaction term does not have any impact on the number of likes and article receives. And the duration has a p-value of 0.46. So again, duration does not have an impact. People don't get the article as long or short. So let's look at the coefficients now. Let's look at the coefficient of content sentiment score and the coefficients I'm highlighting in yellow. The entire column that I'm highlighting in yellow is the coefficients. The coefficient for contents sentiment score is 1.6. Now what does the coefficient tells you? A coefficient tells you how much your predicted or dependent variable increases by, if you increase your independent variable by one. So if you increase contents sentiment score by one or by one unit, how much does it impact the dependent variable or the number of likes? So when you increase contents sentiment score by one unit, the number of likes increases by 1.6 human. And this coefficient tells you that content sentiment score has a huge impact on the number of lights. So when you increase contents sentiment score by one unit, it increases the number of likes received by the article by 1.6 units. So Alton sentiment score is significant and it has a high coefficient, which means contents sentiment score truly impacts the number of likes and content sentiment score can be used to find out the number of likes and article gets. Now let's take a look at the residuals over here. The residuals, as you can see, are between around 01.80, 0.1. So residuals from here, what seems like is that the error is not that much. The residuals are low kind of load. But let's take a more nuanced look at it. First thing we're gonna do is we're going to check the residual chart for content sentiment score. As you can see over here, the residuals are distributed around zero. So if the residuals are distributed around zero, what does this tell us? This tells you that your regression model is valid. It's a good regression model because ideally errors, so the difference between predicted value and actual observed values for your y-variable or for your predicted variable, or for your dependent variable, should be around zero, the arrows should be around zero, the residuals should be around zero. The mirror they are to zero the better. That means that your model is good because most of your errors around zero or zeros. As you can see in this plot, the errors are randomly distributed around zero and there is no pattern in the errors. If there's a pattern, there is a problem when it isn't randomly distributed around zero and there's no pattern. That means that this particular variable contents sentiment score can be used to predict the number of likes. So that is how you read a residual plot. You have to see if the residuals are distributed around zero and they are randomly distributed around zero and they don't have any sort of pattern in depth. That is how you read a residual plot. And then we have the normal probability plot. In the normal probability plot, we tried to see if there's a straight line at around 45 degrees starting at the origin and moving upward. That is what we trying what we tried to see. The slope. Obviously, if the line is at 45 degree slope is around one. So as you can see on the y-axis, you have the dependent variable or log of likes. And on the x-axis we have sample percentile. And the line is a straight line, which basically is around 45 degrees from the x-axis and the y-axis. And it basically shows that there are values in all different percentiles of the distribution. So that indicates that the data is normally distributed. Because if there's a straight line which is around at a 45-degree angle from the x and y-axis. That is how you interpret the normal probability plot. And lastly, how do you find out a regression equation? The regression equation is quite simple. Is y equals to constant plus Beta naught. And Beta naught is the coefficient, coefficient or less a beta one. Into that is the coefficient into X1 plus Beta2 into x2, et cetera, et cetera. So y, which is the dependent variable value, or the predicted value is equals to constant plus the coefficient of x1 into x1. In this case, X1 is titled sentiment score plus the coefficient of x2 into x2 plus the coefficient of x2 and x3 plus era. The final term is error. So dot, dot, dot and you have error. That is what the regression equation looks like. And in this case, as you can see, some of the variables do not impact the dependent variable. Some of the independent variables have no impact on the dependent variable. So you can remove those, you can drop those from the regression equation. What you can do is, for the sake of having a regression equation that includes variables that truly impact the predicted or dependent variable. What you can do is you can drop those variables that do not have a significant impact on the dependent variable. And by significant impact, I mean p-values greater than 0.05. So if p-value is less than 0.05, that means there is significant impact of that variable on the dependent variable. If p-value is greater than 0.05, that means there is no impact of that variable on the dependent variable. So what you can do is you can drop variables which have p-values over 0.05 and only key variables which have p-values less than 0.05. What will be the regression equation in this case? So likes equals to constant plus coefficient of content, sentiments into content. Then determined scores. So you can have a regression equation.