Spreadsheet And Excel : The Ultimate Guide

1. Introduction: Welcome everyone to a spreadsheet in Excel, the ultimate guide. In this course, we are going to learn the art of data analysis spreadsheet in Excel. No matter what level you're at, you're going to start from the very basics and we'll take it to the very advanced level once the course has been divided into four sections in the course, if we're going to talk about the basics of the spreadsheet in Excel, then how to format our data, how to use conditional formatting. And a lot more than in the second section, we're going to talk about how to analyze that data, one of the most important ticks and we're going to learn how to sort and filter our data. Validation, how to be a drop-down list, how to create checkboxes, how to concatenate two strings at the same time, extract the string from a whole data. We are going to learn how to use functions and formulas. Then move on to the third section where we are going to discuss about how to create visualizations, pivot tables, maps, and a lot of chart and exhibit dispersion. Then we'll move on to the fourth, and we're going to discuss about some additional features that have macros which I use to automator data, automate our task in excellent excretion. Don't forget to check out the introduction video and the whole structure of the course. Down below. What are you waiting for? I'll see you on the other side. 2. Basic and Spreadsheet and Its Interface: Welcome back everyone to the very first lecture of our course. So before getting started, I wanted to tell you that I've prepared an e-book for all of you guys who are, It's consists of all the things that we have learned across this course. So don't forget to check it out. You will find it in the last lecture of the course, and I hope you enjoyed the evil. Okay, let's get started. So before getting anywhere, I need to, I wanted to talk about why do we use Excel spreadsheet? So let's start by talking about past. As you almost remember, people used to store data in notebooks and use to be used to help libraries of data. And we can, It's not that easy to access those data. Your data can vanish and it can be, it can deteriorate, and you cannot find individual metrics. Different people can write down the data different way and collaboration is not that possible. At the same time, there are various other issues with that method. Dance where accident is spreadsheet comes. Firstly, it helps you to organize your data, keep it save. Even if your laptop is lost, you can save it on your Cloud and you can access them once again, you can collaborate better with a spreadsheet in Excel, with your group are people that you work with. The same time you can find out insights from your data, analyze it, and prepare a chart, make decisions out of it. Because now we are living in grenades taken to entry where it literally seconds. Maybe 24th century, where data is the way data is the future of everything. So that is why weekend all, all the businesses that we see nowadays is data-driven. Every ad, every specific ad that you see on various platform, everything is data-driven. Data plays a very big role in that and excellent spreadsheet make that possible. Before starting this course. Before going anywhere else when I needed, you need to know some basic vocabulary about Excel spreadsheet. Let us jump right into our Google Sheets and we'll discuss about this vocabularies in there as we are into our spreadsheet. So if you wanted to get right here, you need to play it on to Google, type in speciated getting there and login with your Google account and you'll be right here. You will see of a variety of things right here and the interface. So these other videos appreciate that we have Oakland are worked with and like pure data. And this is where we're going to create a spreadsheet. It's pretty she didn't even axial provides you various templates that you can work around and edit them on your business, such as an annual guide them there. If you need something related to Anaconda, you can work on this and edit it without not creating that whole calendar thing, right? You have a lot of templates that you can find in your spreadsheet. And I'll tell you, I'll recommend you to go through that. So let's get into our blank spreadsheet and start working with it. I'm getting in my blank spreadsheet. You can see various panels right here, the top-most bar, this is also known as your menu bar. This is where if you click on this button, you will get back to your sheet home where we came from right now. These are the various shortcut keys that you can find in a spreadsheet. Various buttons from the menu bar has been taken out and especially being put here so that it's basically chose you start forget to ensure. These is your undo redo and print button. And you can use all your keyboard shortcut and depreciate such as control C, control V. And we're going to talk specifically about some, a special keyboard shortcuts that you can use to optimize your spreadsheet. At the bottom bottom, you can see this bar. It is also known as your tab bar where you can have various spreadsheets in one group of yours period is known as your workbook. So overall we have a workbook and these are the parts of our workbooks as two different sheets. If I click on this, I can move this right. I had turned the corner of it, duplicate the sheet if I want to edit the same cheat and put in some new info. By making a new sheet, I can duplicate it, I can rename it. I can rename directly by double-clicking right here. I can rename my workbook by double-clicking right here are singles. Single clicking. You can collaborate with your team by clicking on the Share button and sharing them, giving them the editor power. You can also add your various things. Add your spreadsheet across various mobile workspace platform. This is our menu bars, shortcut bar, tab bar. So finally, this is our workspace. We're going to work around these ABCDEFG H, everything which is written the horizontal row, these are various columns. It goes vertical, so a goes from here. So everything which is vertical as your problem. So these are your columns. These 12345678 are your rows. If I click on some random column or row, you can see that it is showing the code which is basically each wealth, which basically shows the position of this cell. What do you sales L is basically intersection of your rows and column where we input our data. So it is basically the sale position where it is, add a column of E and at the position of row as 12, this sales location or address as H2O. This is our function bar where we're going to use various functions. We are going to talk about them very soon. I hope you understood the overall, overall UI or interface of a spreadsheet. It's quite easy, right? The next video we are going to discuss about how to input our data and work around without data. It will be really interesting. So I hope to see you in the next one. 3. Basics, Referencing and Autocompleting your Data: Welcome back everyone. We are going to start with how to put data and how to work across data. Firstly, a CD, you can directly click on any cell which you want to input your data and type right there. You can also select that cell and use your normal commands such as copy pH or you can select another cell to paste it in there. Oh my bad. Stand there. Okay, It's quite easy. You can use your command, Delete and other simple commands to work with it. At the same time, if you want to select a lot of data together, you can click one of the data sale, hold your mouse and drag it on. You can select all of them. Quite easy right? Now. Now, spreadsheet and exhale are so smart that they can autocomplete your data. You're going to learn about auto-completion but just a minute. So as you can see it as one, if I want to put the same thing over various other cell, what I can do is I can click on this plus sign right here when I get to the edge of this, the corner, and then drag it. And you'll get the same thing all over. But because we don't need it, I'm going to Control Z at the same time. Now, what I'm going to do is click on to get to. There's another thing in a spreadsheet in Excel, the numbers are usually shown right indented, and your leaders are shown as left-hand ended, as you can see right. Now, what I'm going to do, this spreadsheet is so smart that it can autocomplete your data. As to how can we autocomplete our data is needed to find a pattern to give them the buyer. We have typed in 12. Now what I'm going to do is select like before, select on, click on one and then drag it. I'm going to click on this one and drag it to two. And now both are selected together. What we are going to do is go into the corner and drag it down. And as you can see, it has predicted an auto completed our data pattern. At the same time. It is just not just go with number. You can also use it on months such as if I input January and February, a spreadsheet will get bytes on that we are trying to tell about the various ones. So if you drag it down, it will tell later on about the next ones. So the autocomplete is very, very powerful feature in the spreadsheet that you should work with. And it's really important, you can find it in Excel too at the same time and it's really handy. Now what we're going to do is there are two things that we have to learn additionally about from this is absolute reference referencing. Fortunately, absolute reference and normal referencing. So what do we do basically referencing is we get another sale. We're going to use formulas right here. Stay intact. How to use formula in town, in your exhale or spreadsheet. To use formula you just need to click equal to in the cell. And then you need to put your formula right there. The column number. If I want to put, Let's just delete this. Let's just delete all of this. Sorry. I'll talk about that later on. What happened is we're going to go for total. Okay? What are we going to write here is we're going to put an equal to symbol then by itself associated with try to predict something, but we're going to do it by our own. You'll be able to get positions of the cells. Remember, we're going to write a2, not guessing the day of so you can work with any. And then we're going to give a asterisk symbol for multiplication, then it's done. Now, the spreadsheet is so smart that it can, at the same time it has asked me to order for the other data all but I don't want to because I wanted to show you, but you can use those features, their trailer and the powerful. So what I'm going to do is I'm going to drag this formula down here. Especially it is so smart that it has by itself. And you can see here it went to V2. Whereas in this A3 into p3, the formula has gone by itself, reference, it has gone down by itself. So the column has remained constant, but it has seen the row by itself. That is one of the powerful features. So you can literally drag down all of the data by just putting one formula on top. And all of these roles formula will be kept onto there. There is another feature that I wanted to talk to you about. What if I don't want it this way? I wanted the only, only third row as the reference. That is also known as your absolute reference at that point, what I need to do is I need to put equal to, then I need to write my formula as this dollar symbol. You need to remember the dollar symbol. Dollar symbol basically fixes the position of something into B3. There's check out what this will do. Wait a second. Just need B2. You can edit your formulas right on top, as I already told you that that is apart millibar into what is happening right here. Same value, you'll get the same value. V2. You'll get the same value across both of these. The only change will be when I drag down for autocompletion here, the value chain. Whereas when I drag down this one for autocompletion, a diagonal multiplies all of these horizontal rows. But at this time what I've done, if I fix this too, by the dollar symbol. The dollar symbol is known as absolute reference. So our inference, we don't want to change. We want our A2 to be saved to the number of product will be ten, but the price will keep on painting for 2030. So this is for product, a prize for different number of products. Product a prize. Quite easy. I hope you're unnecessarily. And at the same time, if you want to, you can use this as also this equal to b1, b2. And this will copy and paste the data from there. So the, the other formulas, but we're also going to learn about functions which are predefined formula in a spreadsheet in Excel, we're going to go very depth into that when we start our Data Analytics portion. Hope you understood till here. And we can also, while using formulas, we can also select a lot of data. We don't need to select everything from here. We can directly select it right here and then do it. And it will show an error because I haven't done anything correct, but we are going to do it in future. And at the same time, what if I wanted to show this formula as A2 plus B2? But I wanted to show this formula when I click on it, what happens every time is the data will come and will add those of data. But I want to show this formula. To do that, what do we need to do is use this symbol. When you click on this symbol at top, what it does, it makes it as a comment, I click Enter. We can show the formula directly. Showcase the formula you need to use, the symbol. It comes really in handy when you need to show things onto your spreadsheets. So now we have done auto-completion of our data, are referencing our data are copy-pasting and inputting our data. The next section also I wanted to talk about one more thing that if you wanted to change the height or weight of any of these columns or you can directly right here. And during the height of height and weight, any other column and row. But if you want to change the height and weight of oral sheet, what do you need to do is select all the columns and you can directly click on Control a, Control a to select all the columns and rows and then drag one of these. All of them will be done in the same way. This will build, use this again while formatting our data. Also, you can do another thing is click on this corner cell, which will select all the data. I hope you understood all of this. In the next section, we're going to learn about how to format our data and then format our table, make them color colorful and more interactive. I, I'll see you there. 4. Formatting your Data: Welcome back everyone to your third lecture. In this lecture, we're going to learn how to format our data. Formatting basically consists of two things is firstly a formatting our tables and formatting our RTFO. This section in this lecture, and especially we're going to learn how to format our data. So hopefully you must be thinking why should we even format we format our data so that it can look very beautiful. At the same time, it saves you time. If you look up into that period after a week or month or year, you can get things where they are at the same time and it easier to collaborate and it also shifts a lot of time of your colleagues, share this heat sheet among them. At the same time, it also makes, it looks far more professional as your sheets now have a lot of things as well. Formatted data looks more beautiful, isn't it? This is our dataset which you are going to work on. I got the data actually, so I can show you things. As you can see. One of those data here is in dollars, but other data or not. So how can we change all of those formats? Formatting is really important when you go to data cleaning step. In any other softwares like Tableau, Power BI, our Google Analytics. When you have to do analogies and all of those hardwares, you must clean your data, which you can either perform there. But if you have a lot of errors, then you need to be prepared within your spreadsheet or your egg cell shape. Let's get started. This is our Format Format option. And also there are a few more things that we haven't learned about absolute referencing. And you're going to learn those when we start working with formulas, so don't worry, okay? And we're also going to learn how to reel have here first name and the last name of the forest and how to combine those names and then segregated number, anything out of all of these data. So we are going to learn all of those features installed with formatting. So you can go into this format and you can choose a theme which is a new feature here. What we are going to do is we're going to, formatting has various options such as alignment, grabbing rotation, size. Then these are the conditional formatting and all of these features we are going to learn soon. These are very, very, we'll be done with the formatting our table. You can also do what? You can click on this, top of this and then drag it all over here so to give the position. So I can keep my last name near to my first name. Quite easy. We can select two and drag both of them. It's quite easy. If I wanted to add another column or row, I can right-click in the middle and then insert one column to the left or the right. Quite easy, right? Formatting basically, firstly, by formatting, we start with formatting by form, by changing the font size, bold, doing some of our data bold, some of our data italic. And you can use all those keyboard shortcut Control, a control I control you for underlying and another thing. And as you can see here, we can change the font of our data. And actin swims very bad. Yeah, keep it in area and then you can change the size is don't go below aid because it's not visible. If we go below it though, for the purpose of the course, I'm going to increase it a bit more. Then we can bold some of our data such as we can select all of our headings and just pulled him. Some of these data. Starting with how to do data cleaning to at the same time some of these data I don't need, right? So I can directly delete. I don't need company named while formatting our data are working within any future. And what I'm going to do is completely delete this column. At the same time. I don't need the postal. I'm going to delete the column and the column will be deleted. Do any other things? Let's checkout. This is it. Alright. What we're going to do is firstly, here we're going to resize everything in size. Then change the width. As you can see the data as much as you can see right now. Data is very big. Adjust to work with it so that you can see it easily. What do we need to do? We can go ahead and every section and we can do the alignment as needed. You can change the alignment. And I told you by default text to the line on the left side, numbers are aligned on the right side, but you can change the alignment at the same time. If you have a lot of, you can change also the topmost length. Or you can get it on top or the bottom if your rows are big. That is what you can see. You can also do it. So these are the shortcut buttons. You can also do it through your Format button. You can rotate your tapes, as you can see here. You can align the x. You can do various things that we have learned. And the final is you can, you can also do this thing. Get it smaller. You can also do, as you can see, some of these data such as your email of the candidates, are hidden behind. So what do we can use? We can use text wrapping right there. What texture mapping does is either we can. And clip our data part, we can just wrap our data. So what I'm going to select the e-mail column and then we're going to do as prep our data and you'd have our data, whatever extra is written there began the extra welcome to the next one. At the same time, we can flip our data and it will keep on going behind the scenes. And you can also increase the width at the same time, so make it visible to us. These are the various data formatting that we have used. We have tried to wrap a line, then give the vertical alignment and then rotate our text at the same time. What if the money we can see in the money column, if the data are not there in dollars, whereas magnitude be in dollars when you worked with any of those offers and establish what happened, what will happen. We will consider this as to be numbers, not money. You have. What we have to do is we have to select policies data. And then we can go to the format and work on this. But we have the shortcut button right here so we can click on the color button and everyone, everything will be converted into your money. At the same time, if you wanted to, if you don't want decimal places to be this much, we can decrease decimal places by clicking on this and increase it by clicking on this button. I'll decrease the decimal places. You can also get percentage if you want to change it to percentage, quite easier, isn't it? The same time if I want dual team, the forearm active, some custom guarantee, I can change it to custom currency from here. I can change it to Bhutanese Ramadan dollar or something else. At the same time, if you have, as you can see, the data of every occasion is given right here. What I want is I wanted to do this date in just the number. I just want the month or the year of the date or whatever I can do is go to this button dropdown, but I'm called to a customer, they didn't die. And then we can change the various date format that are given here. We can use this format. We can also use we can also use it in this way. And you can see all the dates for the change in this format. I just wanted to be. You can also change it right here. You can, if you have let us consider that all of the data and only the cell is not in dollars. So what you can do is you can go and click on specific fail to change the datatype. We'll just say, okay, we obtain the working with the data types right ear. At the same time. Now, what I want is I want to change the format of it. Let's just do it this way and apply. And you can see are putting a hormone has been changed. At the same time you can work around. You can change the format of your number, of your tags. You can give scientific format numbers parliament to two decimal places and all of those other things. So I would recommend you to surely check it out. And we have worked with all sizing our data, working with data, wrapping our data, aligning our data, and then formatting our basic data and converting them where they deserve. I hope you understood all of this video. What we're going to do next is we're going to start with format in a table to make it look really, really beautiful by a given colors and everything out there in our table. I'll see you in the next one. 5. Formatting our Tables to make them More appealing: Hello and welcome back everyone. Now that we have learned how to format our data, we have also learned how to use all of these buttons to format our data. Now we are going to Parliament our table by using all these things. So what we're going to do is we're going to add alternating colors in our table. Please, rows or columns, edit grid lines, titles, ourselves farm at rows and column, chain size of cells, font and color, and a checkbox. We have already done changing fonts and color. I'll just show you and we evolved on Sudan Initiating size of the shell and formatting rows and column, which is basically deleting our row, adding a row and working around the rows. We're going to do all of the other things that we have discussed. So let's jump into our spreadsheet and get started. So now we are into our spreadsheet. So firstly, we'll start by adding our titles. So your titles is really, really important or your headers are really, really important because it tells HE user who are seeing, we're seeing the sheet, that what the sheet denotes. What are the various metrics about? We will add various title I've already added. So you should add, I'll provide this file and the description of this resource section of this videos or you should work on this file for sure. Go ahead and format all of the data. And all that. We have added all the data. What we're going to do is you're going to have also learned how to change the size by this antenna is right. We have also completed how to change our font, change our font, and particularly bold, italic and various other things. And at the same time we can fill in specific cells with colors using this font feature, this color feature. As you can see, it's quite easy, right? Then we can also provide we can also hide our grid lines or to integrate lines of a specific sales. If I wanted to change a line of Excel, it will give it all borders and as you can see, it will get dark patterns. And I can do this with the same way to give the outline to my overall table. I'll drag and drop here and then give the borders. You can choose the color of the water at the same time. You can choose to take care of the borders line. At the same time if you want to remove all the grid lines, these are all grid lines. So what you can do for doing that, by this, it will get all the lines. You can go to the View and then go to the Show Options and then click on grid lines. What this will do is take out all your grid lines from your sheet and you can see all the grid lines are gone except these which we have created just now. Alright? What we're going to do is we're going to put alternating color. How to do that is very well go to the Format section right here. And then we'll collect on artillery colors. When we click on that, it will get a better write this. And what do we need to do is firstly, we need to apply to the range where we need to select the range that we want to do it with. I'll select the overall table that we have to. I don't need to. And click Okay, now I'll select this blue color. This is the header color associated with it. Detect by itself as the first row as a header. The header is dark and the other colors are given as per our default file. And we can also chain those corners by clicking right here. That's quite easy. The alternating color looks so cool, right? Isn't it? Provided alternating color? We are born with her. She have any generic grid lines, we're given font color. Now what do we need to learn is, sometimes we want, What do you want to do is modular. So alpha's tell you, talk you through about how to get checkboxes. The what if I want checkboxes right here? If I wanted to create a to-do list. So what I can do is insert or some thought off last task list, and it will provide you a checkbox. I can select all of these at the same time, Insert and go to tick box. It'll get checkbox all there. And we can click on this checkbox. And do you think that we want easy, isn't it? Now, what we're going to learn is how to humphrey zeros. Generally free-throws as only applied for first few rows. So a unit to remember that, what do you need to go as format your data? Whereas again, it wasn't in format. Just give me a second. Yeah, you need to go to View Option. Click on the View option. You'll get a free adoption right here. So you need to give how many rows you can you want to phrase what freeze does. I'm going to show you shortly. I want to freeze my first row. I click on it. My first row is fleas. Now that when I drag this bar and go down on our data, our titles will be shown wherever we go. This is really, really important feature as the titles are sometimes really important. And when you go down, the titles are gone. By phrase from John, you can see your title even though you drag down your dragged down and T the data at the bottom part, you can know what data what does the data mean? Now what we're going to do is learn about our next feature, which is the mortgage option. Module option is one of the, one of the, another powerful feature that we have in a spreadsheet. So what we can do is we can click on these two tables, these two columns, basically, we can, this option is basically your emerge option, which basically emerges everything. But it cannot merge your frozen roads, as you can see. In a second. What we're going to do is we're going to select all of the data below. Click on Merge. Preserve the top leftmost value and merge anyway, what Moore's letter C, As you can see, what happens is right here. Wait a second. What Marx says is you can either go to Mars by using, by going to your Format and then go into your Marcell. Or you can, what you can do is click on the shortcut button right here. What basically it does is when you click, select any two rows or columns and you click on Mars option, you can select how you want to merge. When you click on March horizontally, that I've selected two rows and only preserve your left node value. And what happens is these two cells or margin between all of these. This can be handy in various scenarios. We might even talk about few more scenarios later on in the course. I hope you understood all the formatting and all, all every sort of formatting. How to use alternate colors, how to change the font, background color, and everything. Right? In the next, next, next lecture, we're going to learn about how to use conditional formatting, which is one of the most powerful feature if you're using it. Like creatures. I'll see you in the next one. 6. Using Conditional Formatting: Hello and welcome back everyone. So now that we have learned about the various formatting future, what we're going to do is on our final formatting feature, which is conditional formatting. Really important formatting feature. Show you how it helps us and how Conditional Formatting work. Before that, I wanted to talk about how to use marcel. We already talked about Marcell does. I wanted to explain you how it can be used. So suppose all of these data is Q1 quarter one report. Now this is the apple closing and opening Prize in 2021. I've downloaded it from Yahoo Finance and I'll provide the CMT it in your resource section. If this is a cube, one-quarter, 1 fourth, 4, month of the year reports. So we can merge all of these cells. We can merge all of these and write down Q1 report required ED and then align it or give them a position as metal. We can do it that way. So Marcell is really, really powerful if you want to give blocks of data as some herring or any other way, I hope you unnecessarily it quite easy, isn't it? Yeah. What, what is conditional formatting? Conditional formatting is basically formatting our data on the basis of some conditions that we already have. What are those conditions such as in our case, if I wanted to know, if I wanted to know out of this data, what are the datas which are below and 30 or above 130 or maybe between the dates of this. And it's hard to find them by its greening through my eye. As you can see, it's quite hard to look into these data. At that time. What I'm going to do is I'm going to conditionally format or data. For to do that, I'll go to the Format section. I click on conditional formatting. When I'll click on conditional formatting, it will ask me to rain. So I'll ask these are the high and low prices of Apple. So I'll select almost until here. You can delete the data above a 100 because it could take Tech working for now. And then I can do what I can click on, see I can see various things, whether I wanted to check which are the empty nonempty sales which can be indexed or not, which contain dates after this data, before there is. But what I'm going to use here is less than, I'll give the value as 113 in the data I didn't want. And I can change the color to conditionally format it. And we'll see the data. As you can see. Both of these rainforests where I'm at conditional formatting. Then we're going to go into go in lesson one thirty. One thirty, right. At what days? Data Atlassian on Thursday. I can also change the color of it. And you can see how conditional formatting word quite easy, isn't it? And it makes our data look really, really awesome. So we have learned all the features related to formatting. Next time, we're going to start with the data analytics, how to analyze your data. We are going to use various formulas, functions. If formula I felt formula at the same time VLookup formula and sawed filter our data and work with the data section where we're going to do data clean data validation and a lot more things. I hope you understood all of this, what we have learned in this section, next one, in the next video, what we're going to do is we're going to work with exhale. All we're all and see how these things can be used in exile at the same time. I'll see you in the next one. 7. Excel: Welcome back everyone. So as now we have learned how to use our basic features and then format on data in the spreadsheet. You can do the same in exhale and it's quite easy right here. How we can do that. We're going to learn that when you log in are getting into your anchor job, you'll find this interface right in front of you. I've had several templates and a lot of them plates and some templates to explain new areas things. If you want to check them out, you should get into our blank blank workbook. Then. It's the same thing. You have those panels such as home. These are your menu bar. You will have extended menu bar here so you can use all of these features right here. This is your sheet. You can add more sheets, rename your sheet and all of those features. This is your function bar, this is your cell location var tell selection bar. If I type something in that is high, I can align it up and down right here as we have done it. I can add the same time t and the rotation of it. Okay, I can merge it that we have used earlier. How to march? I can grab the text which was HIV, that x is going out of the limited can go back at the second line. It can go down and increase. Such as you can see, it's going down. It's not just showing it that way. What we're going to do is wrap Content tab and go into the next line. Same time you can use your percentage symbol and your decimal related that we learned earlier, and your currency we learned earlier, right here, if you want to do a custom currency, just as you didn't appreciate, you just need to click on this button, dear, and you can select whatever thing you want to customize. You can also customize your day time in the various format, whichever you like, okay? Same time. The other features such as we have used how to format our data. We have also used how to format our data. So what we have done, we have changed the psi is a height weight. What we can directly do it right here to drag and done. We don't need to use this one which is okay because it says normal features. Then you can delete, insert, and you can direct me right-click right here and do those features by just right-clicking, insert delete. So you can also use some features such as immature table, which we learn there as an alternating color that you can use right here the headings will get, let's just do it. So it'll get back and get to our sampled employee database. Maybe we'll get to our Apple database. What we're going to do is to format the table. And that way we're going to do everything. And it's quite easy, isn't it? You can format it in various ways at the same time. You can do various things around it quite easily. And I hope you understood everything and design section where you can do that. And finally, what we learned in the last election was how to do conditional formatting, which is literally the same right here. You can use the various conditional formatting features. It has some extra features such as top ten, top 10% and bottom ten errors that you can get data bars. I would recommend you to check all of them. We have also learned how to change your changer team font, background, color, uppercase, lowercase, or the grid lines as you can do it right here. And use this grid line feature. And if you want to go to more feature, you can go ahead. Right here. We have finally, did we learn anything extra in the formatting fiction? We learned, we'll learn how to input pivot tables and all of these things later on throughout the code. There is another feature right here, which is very good feature if you are stuck and you cannot find something, you can directly search it right here. If I wanted to find F3 top row, you can literally, you can search any feature which you have heard of but cannot find enough panel. You can sort your dried here and go to there. It is. Now I have freeze the top row so it is free. So you can use this feature which is quite effective if you ever forgot where the specific location or coordinates. If it, if it isn't view or your formulas or data or any other way. I hope you aren't necessarily all of what we have learned. We have also learned how to do it in exile at the same time, and it was quite easy. The formatting section was regularly. The next section when we discuss, things might get a bit large, but as of now, we learned very simple things such as conditional formatting, alternate colors, changing size, hide with, and a lot more things. I hope you understood all of this. And when I've provided you this TDD in the last resource, so you can open the sheet and what are these files and then start working on them and format our data. I'll see you in the next one where we'll start with an analysis our data, with analyzing our data and performing some key data analytics functions. See you there. 8. What's In this Section: Welcome back everyone, do this next section of our course. In this section we're going to learn how to analyze our data. What are the various things that we're going to do by analyzing our data are. Firstly, you're going to start with finding out insights and exploring our data with automatic and tight. Then we'll go by filtering and sorting your data and using slicer. And how to house slicer is a very useful function, will learn about all of that. Then we'll create our drop-down list. We will use formulas and functions, and I'll provide your formula and functions that you should remember some key formula then function that might come in handy. And then we will learn how to combine data, such as, as we talked earlier about when we have named self-reported sun in two different columns, how to combine those data. And we're going to use functions to do that. And at the same time, we'll learn how we can extract data from our setup data, such as if you have a Gmail address with someone, how can we extract the name or something like that? Then we are going to learn about two key major functions that we should use that are really, really powerful in Excel and expertise. Your first one is your If-else function. Really powerful form generally are going to learn everything about it. Then we're going to learn about her VLOOKUP function, which can interconnect two or more sheets then when it started with data validation so that you can avoid errors in your sheet. And finally, we'll end up by discussing what is data cleaning and how you can perform them within your spreadsheet. We're going to learn a lot about XML two and the final video. So let's get started. 9. Filter/Sorting Our Data: Welcome back everyone. So now we have formatted our data, but we are going to learn firstly is how to get insights. This is a special feature that we find in spreadsheet. If I select these two columns, control select both of these columns. You can see when you go to the Explorer, right down here below, you can see the various thing that I appreciate. I have provided at Son for us beforehand. Like it has given us the sum average minimum, maximum. And then have done some analysis on Minimum and Maximum, have given histograms and marries other things. So this is your incision site that you can get using your spreadsheet. Now that we have learned about this, Let's get back into our ROCE debt we were formatting. Get back into our sample imply worksheet and we're going to start with filtering, sorting, and creating slicer. Secondly, I wanted to tell you one more thing that I'm going to add the axial video just after the formula from tune because it might require to have more than one exhale video to explain things and more depth. To use filter or sauteing. What you can do is either you can directly go here. You can go here, click on this drop-down button and you can talk to your data from right here. Now I'm not going to use that. I'm going to go into our data section in our spreadsheet menu bar. And then we're going to go. As you can see, there are two features here for starting. Your first one is sought sheet by sheet, and the second one is short range. What do we use sort range for is sometimes when you require only a specific region of your sheet to be sorted, not the complete sheet, then you use sharp range at the same time UP, don't use that as often as sharp seed because we need to sort all of the data at once. What I'm going to do is I'm going to go to the data. Then it has selected column, the salt and a to Z is your ascending order to eight, you're descending order. So as you can see, now, everything is sorted in an ascending order. If I wanted to explain you one more feature. What if I select all of these range? And then I go to the data and sort range this artery. And we can directly do it in the column a, it, but we can apply more than one today that we are going to explore that will go to as bond rating starting here. First, we'll apply is by calling me by wizard in an ascending order. Then we can apply another coding on the basis of our column E, which is our money column, which will apply to it. So what it's gonna do is it is going to sort the people in via the basis of their name and at the same time with the same letters, it will sort them within the same letters as by the money they have. To understand that sorted out. And you can see now the sorting is done. You're a character and outcome for C. There are three people with a name with name is Todd, him from T, and it has been sorted in descending order from four thousand, one hundred and two thousand, seven hundred and two thousand nine hundred. It is really, really important feature. I hope you understood it. Now let's get to filter feature. You can access your filter features in two ways. First one is go to Data and create a filter. You can way is to click on this filter button right here. What Filter button does is basically it helps us and filtering our data. It requires certain sort of data. So I have added in another category right here. And you should add this in your sheet to while you are formed my after photographing your data Advil, learn how to work on it. We're going to click on create new filter view technology to the aedes. We'll go to data, will go to create new filter view. Just check out our filter. What do we have? Our one filter we're going to setting up the realtor, will delete it. I think I've had a new beliefs. As you can see, you can also sort your data by clicking on this plate is right here. It is showing that we have already spotted our data. Okay? You can see WAS think that it has and you can remove one of them if you want to. You can notice how when I clicked on the Filter button and I went to the Filter button, clicked on acclaimed new filter view. How our data has changed and it will be, I've got. This filter button, right ear everywhere. So what we can do is we can click on this firstly. And when get two options is filter by condition or filter by values. By filter by values, we can see the individual values which we don't want in our table and filter it out. Such as some countries that you don't want to learn about in our sales report. When we are working with biggest spreadsheet, we might not want to work on specific countries or specific places. So we can remove that by directly going on to the city and removing them by filtering by value, by filter by condition. It's somewhat similar what we have done in conditional formatting. It, what we do is we're going to filter which are AMD or maybe greater than a number, less than a number. So we can filter all of those things. Filters quite ET. I hope you understood it. I got stuck somewhere around it because I was a bit confusing. What we're going to do next is we're going to learn how to use Slicer. Slicer is a way to filter our data. It's really come in handy as we can visualize those things are filter. What do you need to do is fourthly we remove our filter. Okay? Then I'll go to Data again and click on add a slicer. And I'll click on his slicer, analyze the data range. So it has already two auditory and if I wanted to change the data range to something, I like to draw it here and the data will change at the same time. Then I need to select the column about which I wanted to filter. I will select the column as our select all of this firstly. And then I'll go to data, go to our slicer. Then I'll select the column name as our other. Why they're chewing the forest. One problem, I wanted to get this. The headers to that I'll call them names are available out there. Out of slicer. Now I call them names are still not visible because we haven't selected it while doing our filter slicer. I kind of messed it up sometimes. I say, okay, I didn't. Do. You're going to do is we're going to edit our slicer. Click on the orange, click on this button, then select data from right ear two, right here, okay? And there's another slider over there. First delete all these lasers, these black bars right in front of you or the slicers to lead all the previous slides that were created by mistake. Going to play it all the slicer, then go to data. Slicer, go to our data selection and then select all the data that we want to put fertilizer into. Then we're going to choose the column as category. Then we will apply, okay. What is slicer and charts in short, slicer is basically a tool which helps us use filter in a very efficient way. As you can see now, our slicer is visible. And what we can do is we can click on right here, and at the same time we can do the same filtering that we stated earlier. We can remove maybe head department or maybe finance sector and then get done with it. And then click Okay. And finance will be out of it. At the same time we can remove our coach. Coach will be out of our data. Slicer is really handy thing so you can see by category and you can, it was more than once risers. So you can basically filtering on more than one thing, such as giving you an example if you have category and subcategory. So we can get into one category and in that category we need only the sub categories. So we can apply to slicers, to socializers look a bit better in Excel that when we are going to work with the exalt, I'll show you how to use Slicer. I had really, really, really interesting. I hope you understood whatever we learned in this lecture and in the legislature, we are going to learn how to create your drop-down list and work with formulas and functions. I hope to see you there. 10. Data Validation(Creating Drop down List), Formulas and Functions: Welcome back everyone. So now we're going to learn how to create a drop-down list and also use data validation, which helps you are wide. And he thought off as well that the user cannot input anything. And your sheet if you ever wanted them to use only the data which we have provided. Basically what data validation does as we are going to explore data validation for us. Then we'll go to functions and formulas. They don't validation. If to access data validation, You need to go to Data and click on Data Validation. What it does is basically it validates your data and let it be what you want. What I want is I want our old where every one of my employees will select which department they are from. All of these. And I'll click Okay. Then I'll click on links from range, and then click on the data that I want her towards the range. You can understand that very soon, so yeah, don't worry. We're going to click on it and click Okay. Then sure, dropdown list and shortening. And then we're going to do is we're going to sleep. When we have saved this one. As you can see, a drop-down list has been created. The right side. And where if I click on one of the drop-down button, I can select which one of the fields those specific customers from. If I dive in something by myself, you'll see it will have a red corner, which means it is showing that our data, which we have inputted as valid, invalid because it's not in our data validation list. It shouldn't be part of the data validation in that way, it becomes really, really handy. Advil, healthy view and when you are working on data off so that anybody cannot just make your data not usable. At the same time. We can also use data validation in other ways. We can also use a few more things to data validation. So what I'm going to do is delete this. We're going to create another data validation. And we'll go to Data Validation again. Liked her column range idea by dragging click. Okay, and then you can add various things out there. You can select the text which contains this. You will approve it. You can all just link the date up to the validate. You can add to what dates they can add. You can also do custom formula that you want and you can also select checkbox. If, if I take, if I select a big box and it will issue a warning, you have to click on it because we wanted people to show warning when they input wrong data. Save it. You can see checkboxes will come all across our data and we can click on this checkbox is at five. What I do if I click something right here in this column and it still and label it, then it will show it as invalid. Data validation is really, really handy tool. I hope you understood how to create drop-down list using it, how to use checkbox, and how we can input a lot more things using data validation. To understand more about data validation, I will recommend you to check out each and every individual function that we could find in the data validation. So what we're going to learn now is about formed in the formulas. Basically what are functions and formal laws? We have already used them from students. And our function is basically a predefined formula that we get. And it started with continent formula about discharge with an equal to sign, which will have function will start after n equal two later. Whereas your formulas really slot with high specific call the market. Such as this is the formula A1 plus B1. This formula, basically what it does, it is adding A1 and B1. But at the same time, if I wanted to use this formula, this is your form. Would I started from your rows and columns, but at the same time, if I wanted to do it, added some formula, then you have to click on some. And then it will show you associated will show you the format, how you should input the data. We're going to write A1 ads for the format and coma. And then we're going to close the brand. You have to remember, you have to close the brackets. Easy to get. There are some really important formulas that you need to know. So I'll show you the sum formula for this. We can also select all of the range and then click, click equal to sum. Then what we're going to do is we're going to get our sum. And then we'll select all of this range. We can select it same way as we do, and close it and click Enter and it will be done. You can use your select forearm selecting way. You can add yourself by A1 to A2. I need it by using commas and various other things. You can also use count. There are certain formulas that you can access your formula like, like functions. Formulas are basically, we are inputting it by ourselves that equal to A1 plus A2. And you can give absolute reference it, as we have already talked about, are absolutely references, absolute references, what it does, it basically favors one of those things. We can fix either the row with a dollar symbol and we can also fix the column. We can also fix the column right here. If it is not visible to you guys. If I wanted to, as you can see right now, it is showing us as I've been put the wrong while loop, so it is showing as an error. But what I'm going to do is make it lm comment because I wanted to show you the formula, how to use it to make it as a comment. I've already discussed about it a lot of times with you guys that to make it the comment, we are going to use this symbol right here on top of it. And now it's just a common, So it won't, it won't show any error messages. And what we have done by putting dollar in front of a, we have phase the column. We have done by adding dollar in front of one as fixed 0. You can use that in the same way in your functions too. You can use references and prepared absolute and relative references out there. At the same time, there are important functions that I wanted to talk about. First one is your sine function we have already used. You can also use your average hometown. There is another function known as medical increase the size of it, more available to you guys. There's another foams are known as count from Janet can count all the data and then minimum, maximum form channel. You can count the minimum and maximum out of the trained. Then there is this function is w, sorry, spreadsheet is really this form journey is basically shows you the day off today. So I'll show you how to work. This is in brief ties for us and then we're going to write today, this function ensures coordinated. You. When I click on K, it will show us 44 target, which is wrong. Again. There are some errors. I don't understand why is it happening. But yeah, this function basically shows you that a goal we have learned about some average minimum, maximum today. And then you can also use lower and upper function to change your data into lower and opportunities. If you want to learn more about Home tab, you click on this drop-down right here. And you can learn more about the function, the various functions that exist in our spreadsheet. As you can see, the common functions are right here as sum, average count, maximum, minimum. Then you have your area from Jones, your database bumped January data functions such as month. It will show you our month day device like the data, like one of these functions. Then you have engineering function, we have filter functions, we have financial functions which are really, really important where we use statistical functions, where we use a variant, covariance and all of those things. Then we have some Google function given by Google, so you can dye it, negate your data or translate your data using this function right here as Google Translate, you can also use Google Finance function to get a financial data of today. And you can also use various financial firm genetics. We talked about the two functions that we are going to get into is one of our logical form generator as a function. And there are variants, so there's logical functions and they're true or not, all of those things. And we are going to also talk about VLookup function in the next few videos. I hope you understand how to use functions and functions. It's really, really handy tool. I will provide you a sheet. Alongside with this video, you can find it in the resource section, which will cover all everything, functions, some basic definitions rounded, and then talk about some key forms is that you want to know. I hope you understood all of this about data validation and functions. I'll see you in the next one where we'll discuss about extracting data and combining data. 11. Combining Data And Extracting Data from cells: Welcome back everyone. Now that we have learned how to use formulas information, in this lecture, we are going to learn about some, especially formulas which you can use to combine two different strings and at the same time separate them. To combine two different strings we use. They use concatenate function, concatenate what it does. It combines two strings. So as we have the first name and last name of the person's right here in our list. What we want now is to combine them. Though what we are going to do is we're going to, let me clear this. We are going to click on the silver. We want to combine it. U is equal to symbol to start our function. And then type in concatenate Khan Academy. Let me zoom in if you want to say it correctly. This is your concatenate function. Firstly, we are going to write down the string one, which is our a2 comma B12, which is our string to close the bracket and see what happens is these bolts of strings are combined. And if you click on the specific sale, you can see the formula there right above as concatenate a2 capability. Quite easy to now. But what happens with the error here? There is no space between the worst name and the last name of the person, which we want. So what you can do to do that, either you can click right here, double-click on the formula, or you can just click right here, give another space. Let me zoom in for that. We have another space and give two quotation mark with a space in-between. Okay? When we click Enter, you'll see there is a space in between the first and last name of the person. The quotation mark is basically the user input to your space. We have also learned how to use apostrophe. Apostrophe gifts, common comment. And at the same time what I do is I'm going to drag this all over. This is going to come up with a formula. And all the fields plus symbol here to the person will select these two. We can directly select this one and drop it. Are named are being combined. Now what happens is, now I want to, what I wanted to do is I want to separate these names, but I wanted to get the firstName and lastName out of. Or in case sometimes you need usernames from the e-mail or their website. They have created their mail from the test Gmail and OT me. And you can see right here, if you need such things, Our at the same time, if you want to learn about, if you want to get the addresses of people or extract, basically extract data from an already given its frame. How to do that? We're going to explore that now. There are several ways to do that. I'm going to discuss all of them. Firstly, we are going to do it using some functions such as left function, right from ten mega ohms. And then we're going to use do it using data, data Text to Column Feature, spreadsheet and even in exhale. And then we're going to use our final replace function. So let's get started. False form Jana is your left function. So what is your lead forms and left engine extract data from the left side of this frame, how to write it down. So let's get started. Firstly, we are going to write down, I'll provide you the resource section about where you can learn more about left from channel. So do check it out. Okay, I'll just zoom in a bit. What I've done here, let me click here. I've got our type in again. So you get it. Spreadsheet auto-completes most of the tasks. So it's ED L EFT, and you'll see a form tonight. You have to click Enter. Then you have to write down the string from which you want to extract the left bar. I have written down K2, which is R, this tavern DSL right in front of it. And then we're going to put a comma. And then we're going to use another function within left hometown, which is your fine function. This is fine form Jen will do is it will find something that we are looking for. So we're going to find, going to find I space as I have already use it. You remember, we are going to find a space from where to find function needs two things. It's for what? From where as you can see, whenever you click on any form, Janet will tell you how the continuity defines so you can rewrite here, but yeah, fine puncture need what you need to search for them on form where we will need to search it from here. Then I'm going to put minus one, which basically gives that exclude that one is page that we have to note that space. So I'm going to add minus one and then close our left function. When I click on Enter. As you can see. The first name of the person has come here at the same time, zoom out at the same time. If I drag it, I get across. You can see the first name has come. Oh, I have there is no table. We know grid lines, so let get started. Let's get our grid lines. Again, see if the first number of the person that is copied. So life-form. Really easy. Understood everything. Okay. Now, if we want to extract the last name of the person to how to do that, the identity, I'm going to use function, another function quite right forms and work same as left home. But you're going to use more function up into it. Let's get started. What I'm going to do is in this function, we're going to write down, right? We're going to start by writing down, right? And then it will ask from where you want to write it. When we get into right function and click on it, it will tell a string wherever you want to write in the number of characters. To get the number of characters we're going to use landforms and define function. Let's write downwards string, string is k2. We're going to put comma and then we're going to write down a length function, which basically tells a length of a string. So it will tell the overall length of this name, LH, yeah, Tom key, wish length of Cato. Then we are going to close it and then we're going to surprise. Fine, same as we have done already. I'll explain you in a good fine. And then comma k two. And we're going to close this bracket and blog another bracket. Enter. As you can see, the last name has come right in front of us. And we'll discuss. What happens is the last name came. So how does this formula word forms and basically give the right side of the values are from where k2. Then we have subtracted the length of k2, so the overall length, and we have subtracted the forest fuel ends this whole length till the space. You have subtracted by finding it into K2 and then we have got the last name. At the same time. There might be a case where we want to extract the middle name, or a person has a middle line such that I've created a random name of a person as Ls DOM wish, and I want to extract the middle labeled a person. How can we do that? We can use combination of left and right function to do such things. Let us do that. Fortunately, what I've done is I've used from Jen to extract the second part of the name as it will cut off. I have used the same formula. It will cut off the forest name as you have turned in the forest one, right? Explaining the formula. We got to do last two names and we're going to use, now, we're going to use the left function and this function and this function Control C, we're going to use the left function. The idea, we're going to use the left function, but this time we're going to use it in O2. You're going to use in our oral because we're going to do that. We're going to press Enter. Once again. Fine, It's based in auto. And then minus one and left function has been utilized and press Enter. Again. We're going to use so we have done this, we have found this, and we are done this. We've got our O2, or should I put x2 to erode? Oh, do I put 0 to m minus one m2 Tom, which this is our string. Then we have found search for this into that frame. This is Tom. And then minus one in the same way. We have done this. And finally our function. Nothing just made a second guys. Basically the other was with the color of the text. So that is why we weren't able to see any texts right here because otherwise they wouldn't be somewhere, right? So we have applied the right function and as you can see. But the same function. What happens is it detects factor, the force name, which is our middle name, objects, main names. But at the same time, what do you want to do? If I wanted to do another way? I, if I wanted to do another thing is maybe I want to get one function completely, which can do this home from work of the Board of information. Now what I have to do is I have to replace this poetry with the formation of the artery, which was right. This function, this whole function. If I replace audrey in the left, warm, gentle with this whole function, then you'll see the change. Let us show you. Let's go to the function of your left home to just leave this one. Just leave this one as it is. We're going to live function and we're going to replace all three with that function that we copied. This isn't a whole lot of like this big formula right in front of us. If you're type down this formula which you can't remember, it's hard to remember. The ways that if you use combination of the left and right function to create it. If you use this whole formula, you'll get the same thing. That is, that isn't about using left, right, and mid function. And there's another form general than MID function. I will provide you the documentation and you can learn about that from there. Quite straightforward. The next way, let's track data is from your text to columns feature in your data panel. So how to do that is you need to go to your data feature and then click on Text to Column. Before. Before doing that, what you need to do is you need to select the text that you want to split it from and then go to the AWS is split text to columns. When you click on it, click Text to Columns. There is this separator will come where it will ask, what do you need to separate from? If there's something that such as an e-mail. So you need an alternate symbol, but if there is no other symbol, then you can go to Custom and add it yourself right here. But we have a space. We're going to just click on a space. And as you can see it as separated, both names, the things. Get it back so you have to have a second feature is done from textual column. Our third feature is known as Find and Replace feature. So to finding the shortcut key for finding the place on your keyboard is Control H. When you click on Control H on your keyboard, you'll get a find and replace tab. What we're going to find and replace it with til we are going to work with our emails. This time. We're going to first just increase the width and we're going to write down the rate, will find the other end symbol. And then we're going to write multiplication symbol. Multiplication symbol basically shows all the things after I chose anything which is alter their diet. It will find which started from entering. And we're going to replace with, just leave it blank. We'll replace it with blank. And what will happen. But at the same time, what we need to do for this function, we need to copy all the things and then we need to paste it somewhere. So we are going to work on something. I've created another field and we have pasted there. What I'm gonna do now is replace with a space and then I'm going to type down replies. Find at a rate, Wait a second. Whereas I could, firstly, I have to select the column. Then we're going to click Control H, fine. Then starting small. And then if I, you know, I just found specific range. Let's get the range from here. We have found this and then highlight. Did I do something wrong? You can again go to control edge. We're going to write down the array symbol. Then we're going to put this infrared pulse, and then we're going to put the range to this range and internal click Okay. And then imagine entire cell contents. Then we're going to replace all, no match is found. Replace all choosing variable or expression. I will just remove this. No, I just found there somewhere. She just do it with the sheet. Then in our value. As you can see now when reflect on it, it has replaced all the rates with the same time. Remember you need to go with the range. I'll apply the range once again. Check it out. It has to be every moved all the symbols. But what if I do is like this. What you can do by using this way, you can replace and at the same time, let's just do Control Z and go again with everyday. We are going to go again and select all of our values and then Control. Going to type down at the raid, replace with Spacebar and we're going to replace all. And it has replaced, as you can see, quite easy, isn't it? There were some errors before. We're going to do another thing. We're going to Control Z again, select the control edge I don't need. And then we're going to point out now our star symbol. It is, it is not taking it as regular expression. As you can see, what this is called, this is known as your regular expression. What regular expression does is basically, I've also talked about these later on in the course, so you'll learn about them. So what basically did, we did? We have selected all the resistance in bulb means everything after iterate. And we replaced everything with space. The same time. If I want the username are the website they have created, the email from, then I have to do star Enter and we can replace all of them. Same way. Easier to understand and to head with this range. As you can see, everything will be replace. That will happen the same way I feel like my Internet admin though. So that's why spreadsheet, one of the major disadvantage of spreadsheet and states that when your internet is close is appreciate Bill it over. I hope you understood how to use Find and Replace feature, text to column feature left-right made out of those features. Very easy to understand. And I'll see you in the next one where we will discuss in the next part. 12. Using IF Function: Welcome back everyone. So in this lecture we're going to learn about one of the powers will function, that we have a spreadsheet and even in exhale the IF function. What the function does, it function basically help us categorizing our data. As you can see. It can help us do a lot of things such as we can categorize each and individual worker on the basis of their salary as whether they have high paying worker or low paid worker. At the same time, we can categorize things on the basis of a limit, or we can compare things using the function two, get what is correct functional work. So let's get started with that. Let me first just delete everything from this. We're going to write our forcing function right here. So what do you have to do is then we will start our bracket and we're going to type down. First. We have to write equal to symbol to initiate a function. Then if it will show F and then click Enter, which will enter our function. And then we have to write down here, as you can see, you have to input three things. Cause the logical operation. Logical operation is true, then mod. And if it is false, then what? We're going to type down the logical operation. You can even compare two different data in this logical operations such as if a2 greater than V2, then we'll say it is heavy and a two is not greater than b2, then it is less heavy. And then we can compare how many heavy them as everyday we have. If function is really very powerful and you can use in it, use it in various ways. What we're going to do is we're going to categorize here our employees on the basis of their salary, whether they are high-paid or low bit. What I'm going to do is I'm going to leave and then logical expression, I'm going to write E2 is greater than $2 thousand dollars. Then we have to write down comma. Then you need to remember any comment or anything you need to write such as this basis, then you need to use quotation marks. Quotation mark, I'm going to write high paid worker. Then there was another thing, value of true. And the last one is value if false. We have tried in coma. And then again quotation mark and write down low-paid workers. Low paid for occur. You're going to close the practice and press Enter. As you can see, the first Margaret low-paid, we can drag down or you can even use auto suspicion or you can drag it down. And we'll show the various worker on the basis of which workers I paid or low-paid. As you can see right here, that these are high and low paid workers. I hope you understood how to use a function and it can use in various ways that you can put a lot of logical ten times railway really powerful. Remember that? Now we are going to use another function which is a part of a form tearing away known as IF error function. This function is used when you have an error, such as I've used the formula right here, but then remaining value the formula take input from these column. But later values in this column has nothing. So the formula will give an error this way. But what if I don't want this error? In that case? I don't want this area, but I want a message right here written somewhere. And what I can do is I can click on equal two and then IF function, then this will show up. Fourthly, you need to write down value. So we're going to write down value is our B. We can write down B. And what was their column is five, right? There always five, P5. So it is going to take the value from there. And if the value is error, then we have to write down what do we need? So you can read a blank space if the value is there, but we are going to write down a message error. Complete previous test tasks. We're going to close the bracket and you can see all of these all will get complete review stars. What if I do is name? And you can see this has gone. At the same time. I can give, I can give, I can write down anything in those, those quotation marks. I can give a blank space to. I can give a hashtag or whatever symbol you want to denote your error. And it will replace everything with the same thing. Write down enter, and then we're going to copy this one. You can see it has changed. In today's lecture, we learned about information, really powerful function that we have used. And it can be used in variety of ways to categorize things. And then you can put charts and create graphs out of those. And which can give a lot of output, can tell us a lot of insights from our data. If function can help us getting a lot of insights. And then we learned how to handle our error. If we have an error, then how can we handle our error with not just this error message, but our message up? Well-written method by ourself that the values and error. You can even use it in data validation as we have learned earlier about data validation, how to use drop-down boxes and all those things. I hope you understood all of this. And in the next lecture we are going to learn about VLookup function and data cleaning. Function is another very powerful function. So I'll see you there. 13. Vlookup Function and Data Cleaning: Welcome back everyone. In this lecture, we're going to learn about VLookup function will look up. Function is really, really powerful formation as it connects. To differentiate. Give you an example in a way that VLookup function is used such as sales data for the month of July. And then we have sales data for the month of August with the people in air. We want to check which other people have bought the sales. Again, what most people do all, although the user exhibit but they do not about, did not know about VLookup function, the print both of these sales. She dumped both of the times, both July and August, and then they compare data by their hand and then again combine them and then retype it in diaxial, which is a very, very long time time taking work at the same time, but you can use a VLOOKUP function which can interconnect different chiefs and directly do that. So it's a really powerful function. And I'm going to use this and in this lecture and showcase to the power of it. But you can use it in variety of ways to connect various sheets. But remember whenever we are connecting two different functions, sorry to differentiate. It should be connected on the basis of your own unique identifier, such as if if Elisha has, but in a month of July and then in the month of August, there are two Elisa Ben VLookup function might do some error within those things. You can either go with email address or something which is a unique identifier. So VLookup function, It's really powerful. It helps you combine two data from to differentiate, compare, and analyze those data. You can analyze sales on two different man. And the Betas are better your customer and you can check out your customer retention whether the customer has come back on an audit, couldn't do a lot more things, one of which we are going to do now, as you can see, these are the forest name implies. This is our first sheet. The second sheet which tells these firstName of the implies with which in which division are there. So the division which every employee working for the second sheet basically works with that. What I want to do is I want to get the division into the forest sheet without typing them down, typing them down. Obviously, what do we need to do is we're going to use VLookup function. So for that first, select this thing. Then we are going to click on Insert one column to the left. We're going to name this column as deficient. It. Simple. Now then we're going to write down our function. So it started with equal to symbol. Let's zoom in a bit so you can understand it a bit more. Equal to symbol. After giving an equal to symbol, we're going to type down. Look up. As you can see, the VLookup function will appear and it will tell you for us it needed storage key range from wherever we want. So we can tell the other cheat for the range and the index. You're going to do the search key, the common parameter would be a VP or B2. This Elisha. And we're going to put a comma and we're going to do what is the amount? You're going to get into our sheet to the range. And we're going to select, just wait a second. Our overall range from here. Then we're going to get vector sheet. Then put comma, and then we have reviewed the index. The index was the common thing here. So is R to the index is two right here. And then we're going to press Enter, press Enter. And as you can see, the data has come bite, so on. And now I'm going to drag this form, gentle cross all the data. And you can see the division of each and every individual. So simple, just few clicks, writing down a function. And you have combined to differentiate within one. But at the same time, if you would not have used, just imagine if you would not have used this VLookup function, how hard it would be to get the division of the people what you would do if you would open it up to these two tabs right in front of each other. And then you're going to copy-paste or either right type down by seeing in under a day, which might even lead to errors in a lot more things you need to remember about VLookup function. It's also important to remember it. In the next week lecture, we are going to learn to utilize whatever we have learned in analyzing our data. Basically is the end of analyzing our data. And we're going to get into our next section. But before that, we're going to learn all of these features in Excel. I hope to see you there. 14. Excel : Using Checkbox, Slicer and All other features: Welcome back everyone. Now that we have learned how to do all the data cleaning data preparation steps in the spreadsheet, we are going to learn that now. Exhale. Exhale firstly starting with, we started by creating filters and taught in our data. Before that, I wanted to tell you something else, which we have already used was our conditional formatting. You can also create heat tables and data bars. I have talked about this earlier, but yeah, I didn't show you here an exhale. You have got a lot of conditional formatting features, judge, as you can get the top 10% of 20% by just clicking on it. And you can also create heat table by putting using this table right here in front of you. And it will give the values on the basis of their, on the basis of the data. It will give color to the data. As you can see. You can also create data bars, data bars, which will give you the data bars, okay? Which will give you the bar according to the highest data knowledge is deleted. So these are the various conditional formatting features that you can do. And we've talked about this earlier, but I didn't show you. Yeah, that's fine. Now let us begin with formatting and charting our data. We started with formatting, plotting our data. It is quite similar. It is as same as a spreadsheet. You can sort your data on the basis of any column, any part, any row or any way by clicking on this here. So I didn't filter. And then you can create your Sort and Filter from a to Z or to the ascending and descending. Or you can also create a custom sort and filter every time I've talked about an expression that can you add levels to it, right? You can add additional filters. We didn't want collection. You can add another section. That filter you can use. You can that sorting you can reuse right here, same weight, so easy. Okay? The next one is our filter. You can create a filter same as CML, this spreadsheet click on this filter and then you'll get a drop-down button. And you can do where you think you can remove sections such as division and the data will be gone. Okay? I hope you understood it. If you don't get this, how to do this, go to the spreadsheet video and you will get everything very well. The next thing that we learn, once you create worlds to Data Validation and create drop-down list. Actually we are going to do that soon, but firstly, I want to tell you how to create checkboxes. Checkboxes. It's something new. Started with creating checkboxes. So for that, I'll just, firstly, I'll go to our table and insert insert, want to call them. Then what do you need to do to protect? But specifically in Excel, what you need to do, you need to go into the developer tools. How do we get your developer tools? Is anywhere on this menu bar, click, right-click and you'll get this features. You need to click on Customize the Ribbon. And then here you will see the various features that we have home. And so if you have a drying bed, you can even add drop feature and I'm going to add developer feature right here. And I'll just end it. Can customize the ribbon developer. And then what I'm going to do is I'm going to click Okay, and we'll get our developer feature right here. Lover feature, you need to go to the Insert Panel. And then here you can insert your checkbox and even those dotted where you can collect right? And get a dot in-between the circle. You can get that. I'll get our checkbox, then it will use get a plus sign your mouse. So you need to just click variable. You need to take walks, okay? And then you need to adjust it right there. You can even delete the name, the key, the word written. At the same time, that is paste that we saw. I'll show you again. So this is phase. Do you see this is the space, is the clicking the space wherever you like in space. You need not to be in an axial. It need not, need not booklet. Right on the box you check box. You can click anywhere in this space which will create. And your checkbox will get a tick mark. Even if I click right here, it's either tick mark is there because the box was bigger for this. You can copy paste these boxes, check boxes across. More high. Hope you understood how to use it boxes in XML. It is very different and your security check it out. You need to go to Developer Tools. Now let's get to our Our dropdown box, our data validation. Firstly, we are going to go for data validation. We are going to go to our data battle, going to click on Data Validation. Let's find data validation right here. And then click on data validation. Then we need to go to the settings and we need to add list. So as I already told you in the data validation of the spreadsheet, you can have whole numbers, tick boxes, then you can validate what data validation does if a person is adding some other values, such as if I've given, there's, this column will only have whole numbers. But a person has added a text or string right there. Then adventitia an error such as this gives it a whole number. Now I'll give a whole number between 100. Here. I will also say input message to be an error. I'll show an error alert. Let's write down some. You'll see an error. At the same time. If I click one, then it will accept it. That is what data validation does. Now we're going to create another data validation where we are going to create list this time. This is violence. When you click on less, this will create your medically drop-down list. For that we need, we need to know. We need to give the data as we need to give the range from where the leash will begin. I'm going to give this range to be this. Again. As you can see. Wait a second. You can see there is a drop-down list in front of every person that we have. If I click everywhere, then you'll get a drop-down list to select. Okay? I hope you understand at the same time, if I click something else, it gives you an error message. So we have talked about data validation. Is it really, really important feature in Excel? I hope you understood it. We have already used in a spreadsheet. So if you want to learn more about it, go to the next one. What we learned, what we learned was about functions. Functions is almost the same. You can even, there is a function panel, formulas panel where you can take out the various formulas that we have and exhale. You can check out various formulas and it is where it is completely thing. We learned about if else function, we learned about left, right concatenate to segregate data and combine data, right? And then we learn about VLookup function. I won't be talking about those as we have already covered them in our spreadsheet. What we're going to talk about is your next option. When we have to divide any event, we have to use divide any names of people, such as we have this name right here. We have to divide this name using text to columns feature that we learned in as a second step to segregate data. For that you need to go to Data and then click on this text to columns. And it will show you a feature. And then you need to go next and you need to put what is your delimiter which will segregate your data and it will show a preview of how it will be after segregating. And it's really easy, isn't it? When you do next, then get done with it so it will segregate your data. We learned about that. And then we learned about, I want to talk about is efficiently one of the important feature that people use to create dashboards using XL. This feature was there in our spreadsheet do, but it wasn't that clear, but in Excel it is really, really clear and it's far more amazing. Let's get started with that feature. This will be our final feature, I think because I've talked about drop-down list checkbox formatting, encoding our data text to columns feature. Then we also learned about Replace feature. So if you go to home, Find and Replace, you can directly use your Control plus edge as we have used there. Or you can just click here and go to Find and Replace right here. And you can find out your data validation, all those things here too. You can use this are directly linked to the shortcut, but it's Control plus h, which we are going to use Slicer. Slicer is one of the most important features. So how are we going to, we're going to slide firstly, you sales division data for our slicer. So as we have already talked in the spreadsheet video there, sizer is a type of filter. Nothing far from filter. It is a type of filter. In our visual, very visual way, we are going to get a slicer. You need to click on Tables Design and then click on Insert Slicer. When you click on Insert Slicer to last year, which column about what you want to create a slicer? I'm going to use my column one. And click Okay, you'll see that our slicer welcome right in front of us. What I'll do, as, you know, we can format our table to very efficient manner. So what you should do this actually is format your table. Format your table. Format your table. Just paste it here. Wait a second. Control X. I'm gonna go to total. Everything is shifted and we're going to create it in a way. So it looks really amazing. Now. Wait a second, we have our slides as well as just delete her slicer. Here, Table Design, Insert Slicer about column 22, which is named depended who right now I don't know why. And as it comes in front of you, you can see if I click on what slicer is a type of filter, as I already told you, and it is very visual filter. While we create our, I'll show you another feature of this slicer is this. While we create our charts, it can make your charts a lot more visual, dynamic. If I click on one of the future, like academic, it will show my academy data. If I have a pie chart right here, pie chart is connected to our slicer. Then when I click on this, the pie chart will only show the academics section Theta, then it will show division section. So people can get dashboard. You must have seen dashboards. It is such a powerful feature when people have created, if you will, format slicer, it can make powerful dashboards when you create on finance and you can get a finance sector, jobs and bar graphs and everything, pie chart. And then if you want to create marketing sector or you can get data across the individual unit. Those are slides here comes really handy to the type of filter, but as you can see right here in spreadsheet, it wasn't that visible like we could not see much difference between a slicer and a filter. But here as you can see, we get buttons to work on and we click on them and our data hold data junior, old pie charts, graphs, and all our data. Like if I had that Apple data, if I create a slicer and a bar graph, then the bar graph bar, let's just create a, just create the basis of this table as create, insert recommended chart wherever it recommends. Lead inside this chart. And if I click on division then are charged will keep on telling, see, exchanging. Our chart has become dynamic on the basis of slicer. And we can add multiple slicers as we learned on the slicer is such a powerful feature. You must go through it. And when we learn, next, next section, when we learn about charts, I'll help you in creating a simple dashboard like this and using charts and we're going to use everything, all of these features. In the next. Now we're going to jump into our next section. We're going to start with creating pivot tables, then a lot of charts and everything. Edit our jobs and create all the types of chart. And then we're going to use, we have already learned how to create tables right now. How to create tables. So we're going to note other things and we're going to customize it with our slices. I hope to see you in the next section we are going to enjoy a lot in that section. Take care, Have fun. Bye-bye. 15. Quick recap: Welcome back everyone. This is just a quick recap lecture where we're going to talk about what you have learned in now. So we started with learning about basics on spreadsheet in Excel. We went on to learn about formatting, conditional formatting, and a lot more pragmatic features. Then we went on to the, the next section, we will learn how to how to analyze your data, then do data validation, job downloads, take boxes. Then we also learn how to filter data onto your slides are and how important slicer is. We learn how to use if-else function below on how we lose below consumption, which can create multiple sheets. We learned how to concatenate data and extract the string from the era of you also learn all of these features in Excel and how they can be different in Excel. And at the same time we learn how to take walks in and exhale which we need to get the developer do. And then we learn how to use Slicers and how important it can be and how it can meet your charge more in dynamic. And we're going to do that in future do. In the next section, we're going to talk about PivotTable and you're getting shot. And we're going to learn about how to create those things in exhale embryo that their coordinates then but creating diverted Rayleigh, it's obvious that this afternoon, so we're going to discuss in the next section, in the final section or the additional taken, we are going to talk about macros and how can we help? It can be used to automate tasks. I'm really happy that you are following me until here going. You put in your work. And yeah, I'll see you in the next lecture. 16. Pivot Tables: Welcome back everyone. In this lecture we are going to learn about pivot tables. It is one of the most powerful feature in a spreadsheet as well as in Excel. What pivot table actually are by the name of it pivots your data backup by some other values. So what happened? What did helps in it helps in organizing your data. And how did it actually do that? Such as, let us consider a case for an example that this isn't sales data that we have. You can find this in your resource section. So the sales data, what happens is I want to know the yearly sales of each and individual product. So this product sales data, as you can see. But what I want to do now is I want to know eerily sales, but at the same time, if I calculate the yearly sales of each product from this table has good at around 2014, there are so many products that we have to add them all up. So it will be very, very hard to answer. We have to write down everything or maybe mega some function and using each and every product and using my fellows from Jan, which will create a lot of time waste. And also it will be a lot of confusion. At that time we use pivot tables to change our data. And at the same time if we don't want other data, profit, so we can do that using pivot tables. How do we create our pivot tables? Let's jump into it. For creating your pivot tables, you need to click on your Insert tab. Now we are going to work with inserted, okay, because we are going to create charts and everything. To remember this, then you need to click on PivotTable. Lost your data range. What we're going to do is we're going to put all the data. We have. Bill here. Then we're going to press Okay. Then it will ask whether you want to create a pivot table in new sheet or the existing cheered? I would create a new sheet. We'll get into a new shoot. As you can see, a sample template will be in front of you. Whereas at the same time It's spreadsheet will suggest you view things if you want to go with it as it can, it will create wife. So on our pivot table of month average units sold in every month. And recreate on, click on that statistic table. But here's what we're going to create. A pivot table where we can get the profit data of each and individual product. I'll remove all of this. So this is your pivot table editor. You somehow you close it. If you're anywhere in the pivot table, it will get open a gland. As you can see here, we have our forces are data that we input earlier. And then this is what we want as our row and what we want either column, I want column to be r here. As you can see now we have to tell them to 1314 together. Okay. I'm going to remove this then. I'll add the row at our product. Our product has come. Now the third portion is, what are the values you want to add? I will add the value to be profit. And as you can see, the profit of individual products are right here. If you don't want to see this Grand Total both sides, then you can click on the checkbox right here and remove the grand total. Look at the same time, this is your one-dimensional pivot table. But there might be a challenge. You want to create a 2D pivot table. What basically our duty PivotTables. So what, in any case, if I want to know, if I want to know across which countries this character is performing well. So what we are going to do is we're going to create another row inside here. And we're going to give it as country. When I'll give that, you'll understand it by yourself. You can see firstly, you can drop, we can drag and drop right here in the room section. If I drag it right here, as the country's above, you can see the country is the first portion and then your product is the second portion. Here we can see the Canada. In Canada we're selling character Montana and parts you These rate across the years. But what we wanted to know is how we are selling this one product in this country. We need one product and data across various countries of the same product. So we can compare the individual product sales across various countries. Here we are comparing the sales of different products in one country. So you need to remember what metrics you want to create here. I want to create individual product sales across different countries. And what I'm going to do is I'm going to drag and drop it up on my country. And as you can see now, we have product as our first column and second column as our country, where we can see the individual product sales in various countries and we can compare them. As you can see, Germany has very, very high sales, whereas in Canada we have very long tails. So by using these, we can get a lot of information from our data. I hope you understood how to create pivot tables. It was quite easy and at the same time we learned how it can create 2D pivot tables and how we need to redesign it as per our metrics that we need, as per the data that we want to create. And at the same time, what if I don't want to get this? What if I wanted to get it at a percentage of the total, grand total? At the same time, what you can do is you can go to your value tab for offered here is you can do in this sum to average profit or whatever you want. And at the same time, you can also change it from default to percentage or percentage of grand total. If I click on percentage of grand total, you can see the Boston area of grand total in each country across different years. You can change your data on the basis of that. By default, all your column and rows are aligned in ascending order. But you can change that by clicking on this. You can add more columns if you want to. But yeah, this much is enough to understand what we wanted to create. Our main goal was to create, take out the sales of individual product across different countries from different years. And we got that at the same time. I'll remove this to percentage of rows. So we can do that too. And we can just remove it from presented to default. And we can make it an average function and various other things. Okay? So I'll recommend you to check this out, okay. Feel free to check it out. You can add your filters where you can add filters across. Whereas other things such as you can add filters via your unit tool. So the filter will be showing all the items, but you can, by the number of units sold, you can remove items as we have used filters earlier, so it is quite easy. You will be able to do that if you go through it once. You can also do filter by condition and everything, I hope I would recommend you to go through this and create few more PivotTables maybe, because we have more data such as the monthly data or the daily sales of the product. We have the daily sales so you can create filters on December month of July month. Okay. So check it out. Pivot table really easy. I hope you understood it. And I hope you understood how we created and how we can modify and edit our pivot tables. And if you want to get into our pivot editor, we just have to click anywhere on our pivot table and we'll get into our pivot table editor. At the same time we learned how to pay to the PivotTable. I hope you understood all of this. I'll see you in the next lecture. Take care. Bye-bye. 17. Creating Different Chart and Map Visualizations: Welcome back everyone. As we have learned how to create tables in this video, we're going to especially talk about how to create our charts. Before starting with creating our chart, I wanted to tell you some basic charts and visualizations that we have. Some of the basic visualization that we have is r bar or column chart and line chart pie charts scatter plots. At the same time, we have some other visualizations such as we are going to work with ethical locations. We are going to create everything on the map and put in a lot of things, so yeah, don't worry about all of that. But yeah, apart from that, uniquely remember one thing that is, whenever your table is on length, it's better to have your table by it to have a visualization. We'll talk about that when we get into our spreadsheet. So now, especially it will give you some recommended chart by itself. But at the same time you must know what type of chart you shouldn't need. To know what type of shout you should make. There is something known as charged user. I have provided the link and I'll put this into your resource section. So if such as you can see this chart to the right here. If you want to have a comparison among item, you can choose bar chart or the charts. And if you want to have it overtime, then you can have your line chart. Some other charts like that, like these. Same time if you want to show a relationship, you can chewed up scatterplot or a bubble chart. And if you wanted to assure distribution, you can do that Instagram and all of these shots. I would recommend you to go through the chart chooser. Now let's jump into our spreadsheet. I started really hard job. Now we're indoors. Proceed with creating our charge. We must know that I've already talked about insulin insights. Get a lot of charts out of your data. But we don't want to use that because we wanted to create our specific sales related, right? So for that we need to create our jobs, but at the same time we have to select the specific data. And you can see for I want to create a country based data chart. I have just like country and then it will be a lot of hectic to select other data. It's better to create a chart that we have created already at a pivot table. In the last lecture. In this lecture, we are going to utilize your pivot tables, how to create your chart if you want to create your chart. Fourthly, you can go to your Intuit option and then click on Chart and your child will come right in front of you, or you can click right, It's right in front of me. So let's get into our pivot table. Now that we are into our pivot table, let me delete this chart. Now that we are into our pivot table, what we are going to do, we're going to click on this chart button right in front of you. Clicked onto that and you're charged shows nothing entitled. What do you need to do is you need to select. You need to go to the data or data range and selective data. Such as, if I have no data right here, I click Okay, then there will be it up. But at the same time when I select data from year to year, you'll see that I did. I will come again. Selected from here to here. And then we'll press Okay, what just happened? Let's just average per year. Just like TO data from here to here. To delete data from top favorite table. As you have selected that. I remember while writing the formula sheets and you want to use other sheets from it. You can also choose this. You need to write this down, cheat one, an exclamation mark, and then add the value. If you want to add values from other sheet. At the same time, there wasn't any x and y axis, so I just didn't put it that and you can see we have our, we have our data right in front of us, and our x-axis we have put our products, and y-axis has has our product sales. Roberts. The same time. What if you don't and if you want to change the title idea, as you can see, the shadow is quite long, so we can write bar graph or something like that. At the same time. If I wanted to change this chart, just duplicate the sheet. Let's just let's just change the short. If I wanted to change the shore, I need to go. Firstly, what I need to do if I want to edit the chart, if every table it with Divi, if I click anywhere on pivot table, it will get us to the Edit Feature. But here's this won't happen. Or what do you need to do? Go and click on Edit chart. Same time you can download your chart as PNG PDF or Scalable Vector. You can copy and paste it in Google Docs or any other docs tool. It's really easy. And what I'm going to do is click on Edit chart. And then we have to go to Chart Type and we can select any charge right here. We can also select maps which we are going to select in the next lecture. Actually. We can also create pie chart and all of those things as this chart. And I've already talked to you about Zhao chooser and I've told you that when there is time. It's better to create line chart to show change of something over time. We'll create a line chart. And at the same time, what do we need to do is we need, we are going to put our put our timeline right ear. We're going to put our products right here, our timeline right here. And we can add another timeline from here. And we'll have two timelines on different ages, different dates, and you can edit it right here, the legend then add a lot more legend. So if you want to work with that, what do you need to do is go on to customize option. You can customize your charges style with background color in our font size. The font size is very small. You can also do such as if I want my background color to be yellow or something like that, you can do that. You can change your job title as I have given. You can either double-click right ear or you can give Joe title and you can give it. You can change the alignment to the middle and make it bold. And if you wanted to change the color of it, you can change the color. You can format your chart using this. At the same time you can form, you can format other things. You can also format your legends from here. We have auto legend, but you can give legends by yourself from bottom dollar variable, you want it to be that I want you to go through the chart editor and learn. You go through this chart editor is really easy. It has the same features such as I've talked about all of these ourselves. Explainable features. If you want to remove the grid lines, you can remove the grid lines. You can remove the grid lines. You can see his face. Okay. I hope you understood it, but at the same time I wanted to show you another if I want if I want a pie chart right here, I'll just copy this page right here. Control C to copy, control V to paste. And we have another chart. Forever. Though we've got what we're going to do. That's how you create a dashboard. So and final lecture of this section, I will be helping you create a simple dashboard. So that's how you're going to create a dashboard. So what we are going to do is we're going to edit this one is short and create it as a pie chart. Remember there is a rule of thumb for that you should not have more than five data as if you have more than five data, then it gets really weird to see and that's where pie chart is not usable. If they're less than five data, have remembering chart chooser, then you can go for my job. And at the same time you're gonna have your 3D by G2. If everything looks so nice. And this way, you can have these 3D features at the same time, I wanted to show you how to represent these data onto geographical maps. So let us get into our pivot table one where we created this pivot table which having every country having the product data. We are going to go again to our chart, the three buttons and click on chart. We'll get our chart as this short default chart. But what we are going to do, go and click on maps. These are two maps feature. Your first map feature basically shows you as a region. As you can see, every region will show the date of different years, Canada, United States of America, and then you have friends and Mexico. And always there's a state at the same time. If I wanted to show it as a dotted point, then we can show it this way too. And if I click on these dots, you can get that data from those places. I didn't see him time. You can customize these lot more. You can customize this in variety of ways. So it will show you the minimum age range. Okay. You can customize it in a variety of ways. And I would, I would like you to go for all of these features and learn about them. Customize them as much as you can, okay? And you can change your background and put her too. You can also change the font to maybe wide. Then you can see the font has been changed. And at the same time, I can change the color code from 2014 data on 201413, we can have one of those data and then we can aggregate and everything on it. It will show overall one dot along with the data. Or if I remove this address, okay, I click on this data, just go to our first chart. And you can flip this data right in front of here. What I want you to do, go ahead in your chart, chooser. Go ahead and try out all the charts. But remember with the help of chart chooser, what type of shot is best for location. I hope you understood all of these out-competed other graphical maps. For geographical maps, you need to remember one thing for sure, that you need to have a geographical data. So if you don't have, if, if by any case that spreadsheet don't detect these countries, then the geographical maps, one video and you have to change the datatype. As you have learned earlier, you have to change specifically indeed that I post those places to country. But most of the times it will be there. So I hope you understood it and that I clicked here. We're going to create a dashboard using all of these features. And at the same time, we're going to create, use all of these features. We have tables and charts in Excel, and then we're going to create a dashboard and exhale rather than here because I have already told you that slicer is more powerful than Excel. And we're going to use Slicer and exhale to create a dashboard because right now as you can see, this chart right in front of me, this job is a static job. So if I click on some, it won't change. But with the help of slicer, as I already told you in the exhale lecture, that with the help of sites or if I can make those chart dynamic so it can change its value on the basis of different places or things. I hope you understood all about jobs. Help you learn how to use chart editor or to use chart chooser. And the next lecture we are going to be a finally I board and then we'll move on to that regional chapters. 18. Creating Pivot table and Charts in Excel: Welcome back everyone. Now that we have learned how to create pivot tables and charts, it's pretty, it will do the same in our Excel, but it is a bit different in Excel. So let's get started with it. The community of women table. But you need to do is you need to go to the Insert tab. Then you need to click on pivot table right here. Then a pop-up will appear with the data range. So what I'm going to do is give all of this as my data range. Then I'll put it there and then we'll press OK. The unit tissue is whether you want it in a new sheet or existing sheet. I wanted in a new sheet. So prays, okay. This new change will appear right in front of us where we'll have our pivot table. So this function right here is the same thing. But what additionally happens in exhale is that you get a PivotTable Analyze tab right here. When you get into pivot table in Excel, if you create anything, you'll get a specific tab related to it. We have learned how to use developer tonight. We are going to use that very soon. And at the same time, we have got PivotTable Analyze. And when we click on chart, we will have a chart analyzer, chart editor or something. Let's get started. So what do you need to do here is you just need to drag and drop the things in the corresponding section as we did in the spreadsheet. So basically what we are going to do is we're going to put the product in our row section and it's their profit and our values section, and it is there. And then we're going to use ear in our column section. As you can see, this product profit across various years have been shown. And now we want country at the same time, we can add country here. And as I told you earlier that if I want every country with different products that I need to do to change the spacing. Which one is on the top and which one on the bottom. That's how you do it. Then we're going to close this pivot table, pivot table fields. And let's see how we can get into there. If I click somewhere outside of PivotTable, that pivot analyzer will go out. But if I click anywhere inside the pivot table, pivot analyzer. Welcome. So let us discuss about pivot analyzer. Analyzer when you get into your pivot analyzer. And it started with a pivot table names and options and all of these things. You can also add your slicers right here. Slicers, you remember, we can change our product details on the basis of some buttons and filters. These are basically the filter will just give some slicers. And as you can see, we have these advisors on sales price. And the things. You can click on these things and you can get. At the same time we learned how to get percentage values or maybe for getting that you can Gail here and show values as percentage. And you can find out from here. And at the same time, if you don't want the profit some, or you want an average maximum, minimum, you can go right here. Understood, right. Then you go to pivot analyze. Then this refresh button is used when you do any changes in your sheet. To reflect them back into your pivot table, you need to click on the Refresh button. Here it becomes you can create a pivot chart. This chart will be basically this is our recommended charts. I'm going to create that. And as you can see, this pivot chart are dynamic rather than static charge, static charge. So I'll just charts which are in front of us. We cannot change them, but at the same time here, we can change our pivot chart. We can use filters in this pivot chart. As you can see by clicking on every year a replace. And I can remove some countries if I want to end press. Okay, and it will show you as well it was okay, filtered values. Go ahead and play with it at the same time as you can see when we created a chart. There's Format section where you can format the various things off the chart, like lines and all these grid lines and everything. At the same time we get a chart formatting section way. I'll show you that very soon for us, Let's talk about pivot analyze. You have Recommended PivotTables and at the same time, if you want to get that field list again, you need to click on this button right here, that field list, and you'll get all the things right back. It's a bit different from what we have learned in Excel, isn't it? From what we have learned in a spreadsheet actually. So you need to remember all these things. And if we use some filters, see the thing about, I have already discussed two or three times about this. But yeah, I'm discussing more because it's a really important feature about the thing about our slicers are that with using that whole, our pivot table is changing. At the same time, our charts is changing. Everything is changing on the basis of our slicers. That is why slicers are so powerful features and you need to remember them. When I select one of them, you'll see all of the data and income. Okay, so slicers are really important and you need to know about it. Okay? Let me delete this slicer at the same time, like this, right? So I hope you understood how we created this pivot. This is also known as pivot chart because this chart is created with the use of pivot table, you can edit a chart. We'll talk about editing our jobs very soon. Get into our sheet because I don't want to pivot table, I want to static Static sheet had a chart. So what I need to do is I need to go to the intuitive tab again and click on Take a minute chart. This elegant dinner data. If you have numbers in the earlier, then you need to have selection. I already told you that is why we use pivot tables and especially country, because I want to make a geographical chart and then grow sales, sales price, manufacturing and all those things. Again. Then I'm going to click on Recommended Charts. I'm gonna click on our recommended chart, put it, I'll just clicked on our map and click on this chart. As you can see, paramount will appear right here. You can customize your chart design from here. This is where you customize your chart design, put in colors and everything. You can also change your chart type Move Chart or whatever you want to do. And you can see, when I click right here, you can see the sales data of this area. When I drag and you can see it, scan it, and values, and you can customize all of those things. It is really easy. I hope you understood the other factors. In Excel, you cannot get that other job that we had Map, other type of map, there's only one type of math. But you can also create a 3D map. An excellent We'll check around how to create a 3D map. You have to go to 3D map, click on Open 3D map. You'll get a panel like this. It takes a bit of time because it is creating a truly man. Let's wait for that detail towards the difference not valid. When we have gross. Going to be. Select all of these. Then we are going to take our sales, like sales. Then you can create your pivot chart here too. Pivot chart basically summarizing and it can help you with various filters that are across your tab. If I go far and recommended chart, as you can see right here, like this. This is called as a static chart. As I cannot do any changes in this. I cannot, I cannot make it a dynamic or if I want the data for 2012 to tell him before and I can apply it anywhere and the data will change right here. So that is why this is a static shot. And we'll learn how to be unlearned, how to create a dynamic chart. And when people create dashboards, then is the most useful thing that you could have. Let's go to our recommended chart once more. And we have all these jars. And I've already told you of the chart has timeline then you need to use timeline to. There are some error in creation of our 3D map. Let's just check it out once more. If it works. Again, it's not valid. There are some errors, but yeah, 3D map is not that useful. I wanted to show you is just for the fact I've created last time and I don't know there are some errors related to it. Okay. This time, let's just created and I'm pivot table. Table. We go to our pivot table that we go to 3D map. Open iterative math. And as you can see, this is your 3D map looked like and you can have all of these things. You can play towards ground said, something like those things. You can take out your data across various places and all. Although this is not that useful, but yeah, it seems fun, right? What we're going to do is we will get back to close. Then I have told you how to use that. We can create line yards where we learned how to do edit our chart. So if you click on my chart, then you'll get designed right here. And you can design switch roles in Harlem and tinned Chart Type and change color, layout and add Chart Elements such as access, data labels or any label you want to at the same time, if you want those labels, you can also work on those labels directly from here. I click here and get the exits titles and you can write down the axis titles. You can double-tap here and write down I did down like a spreadsheet. You can change the color and all those things. You can change the color of your chart, type and style. You can change the style of it. There is a lot of things to do. I would recommend you to check them out and we have disclosed about almost everything. Okay. Now what I wanted to do is discuss about how to create dashboards. Dashboards are another are either will go to the next lecture and talk about that. Yeah. I hope you understood how to create charts, pivot tables. In the next lecture, we are going to learn about how to be a dashboard. I'll see you there. 19. Creating your Dashboard in Excel: Hello and welcome back everyone to the final video of this section. This lecture, we are going to talk about how to create a dashboard in Excel or a spreadsheet, what our dashboards and a lot more. So firstly, dashboards are the visual representation of any company's data. And we take out a lot of information out of dashboards. So before creating and dashboard, that is why we must know what we need to get from this dashboard from the data that we have. At the same time. That is why we blueprint or dashboard, blue printing dashboard is basically the process of analysis and thinking about what data do we need, such as Eigen think, Oh, I have this much distribution across my sales. Then I want all my distribution of sales across various countries. At the same time, I might even think of getting a few more things such as sales record from past few years. What are the divisions that we are working well on? You need to Blueprint your dashboard, the respective company. So at the same time, that has moved after your final deliverable dashboard. There are several types of dashboard. After your final deliverable, you will find out this dashboard. This is somebody who looks right. You're going to have logo or a heading at the same time, you'll have different data that you want to showcase through the dashboard. So it is an individual sheet containing all of those graphs and chiefs, okay? That is useful for us. At the same time, you can add slicers in there to make it more dynamic. You can add a slicer so you can get data 2020 to 201220132014, and all of those datas. We use key things to create this artboard. Firstly, what we can do is you're going to remove, going to, going to go, and we're going to remove all the borders from these. As you do it going to remove water or at the same time if you don't want to, you can. What I've done here again, go to grid lines and remote grid lines, okay. Same as a spreadsheet, okay, There's nothing. Okay, So here, if I click on this, I can show grid lines or remove gridlines from there. We'll have a plane space we wanted to, we can do is there are variety of shapes that we can in turn. I want to insert a blue color shape. Here. You can add your symbol. To add any photo you need to go to. You can use these icons to, and you can go to picture. And from this device or PIM, I'm going to use some icon. This icon. Let's just created right here. You can add a lot of variety of shapes. Not just the shapes. There are lots of shapes right here. You can check out whatever shape you need. Then we can also further header sake, we're going to create another rectangle. Maybe. Maybe you can do the formatting later on. You can change the color of it. If you click on the rectangle, you can get the shape and color. Okay? And I might even add text box. Text box. Text boxes is it's basically your take smoke. Okay. And I might even write down here total sales data. And then you have to format the size and everything. At the same time, I can even put sales data and at the same time below it, what I can do is I can write another function. If I wanted to get any data from T21 that I've created, what I need to do is I need to click here. Then I need to get interconnect differentiate. You need to write down Sheet one with equal to symbol, so it doesn't function then exclamatory mark. Then if we write B2, then you can see the B2 data will be visible right in front of us, which is Canada. They'll tell you work with it. At the same time I can align at and doorway to their task with it. We can increase the size of it, the color of it. We can remove our textbox. Sake of now. You can see that Canada has come right here, okay. You can also add another text box and maybe write down content into that. You can use it this way. You can use textbox. So basically in exome or do we do, is we have to design all of these things. We are getting all of these things, designing it, getting another row, then copy pasting our charge that we have created, you will have multiple cheats. You can create bar chart and one, or maybe have a few extra bar and pie charts in one sheet. And a lot more things. And then you can copy and paste everything right here. Then you can create some filters. You can create some filters. It is, I'll just go to our sheet for we created a shot. We'll go to our sheet two and paste it. Then you need to format everything in a way so it looks good. I hope you are understanding how we created. At the same time, you can create filters, slicers right here. But it can help make this more dynamic. We're going to go to our slicer. Sorry. Slicer will just go to this PowerPivot analyze, create slicer. And then we are going to pay it slicer on the business segment manufacturing cars. Product damage zone is, we'll have three sliders around and you can add your slicers right here. For a managed way in a formatted manner of L formatted manner. If you wanted to get. And then I'll delete this one. And we can see what will happen is you can format your data on the methods of that laser. See every segment data has been shown right here. So your charge will become dynamic. So in front of you are going, giving presentation of your dashboard in front of your company or any people in front of them. And you can literally create the slicer and show we have this Michelle's and enterprise government sectors. You don't have to create individual maps is static maps were studied data or charts for every one of those. I hope how you and I hope you understood how we create, how you can create these things. You can also edit your slicers if you go right here. And I already told you that whenever we create something in here, will always get a bar over the top and you can edit the color and everything. You're gonna get all the colors and everything of your slicer and I look offered, so make it look more nice and everything. That's all. And I hope you understood how to create slicers and help our financial advisors until now, because we have talked about it a lot of times. Then I hope you understood how to create a dashboard here. But the issue happens here. Instead, while you are creating dashboard, There's a lot of issues such as let's start with this is you need to flip it into your dashboard. Then you're going to create and you're going to drag and drop everything. You are basically creating everything, getting rectangles, pushing them here, then sizing them. Creating dashboards and excellent, especially in an episode, is a very hectic task. And it takes a lot of time because it might take you a lot of time because you need to go into data and work on there. And basically whenever you create a dashboard, there are three steps. You clean your data, then you prepare your data. You add vertically leading data. I mean to you, you basically format your data of $2 things, everything. You format all of your data, then you prepare your data, you create additional fee that you might require. You might have revenue and sales, you need profit, you create additional field. Then you, then you start creating your dashboard before creating as you have to create individual visualizations and then to your dashboard. Long task. That is why there are special software to do that. W is one such software which I've created a course on. I would recommend you to check out my profile and go to my tablet. Your course, very easy baby, I've talked about how to Blueprint your die for the starting from everything, starting from your dashboard. Like how, what is data analysis, every other thing, how to clean data, prepare data and then, and then visualize data, create dashboards. We have finally created even more than that course. The course name is data analysis using w, The Ultimate Guide, I would recommend you to assuredly go ahead and check it because that is the next step you need to take after their learning exhale. And after learning Excel spreadsheet, you will be able to understand all the concepts it very clearly see to make even dynamic, to make things dynamic and tab you, you'd be useful just to, you'll learn a lot. You'll get, you'll understand everything very well. So I would recommend you to go ahead and take this section in the course and get started with w, because it is a very powerful software to work on. I'll see you in the next section where we are going to talk about few additional features and I'll keep on updating that section. Fourthly, but it started by talking about macros. So I'll see you there. 20. Using Macros: Welcome back everyone. In this lecture we're going to learn about how to use macros and how powerful features microbes. Before using macros. Macros is basically used to automate your work, such as if you are getting if you're working in a sales department, darn some other department and you are getting the same data again and again and again. And you want to automate that task. So you don't have to prepare like clean data, like change the dollar symbols or do all of those formatting, data formatting, then you can use macros there, which can help you in doing that task where he is today and before doing that will just delete some of our data is more available. As you can see, this is our data. Let's just I'm format on it. Before doing anything. We can do that as I get to her home Home tab and then we're going to change the currency into something else, so we need to work with that. Okay. I don't see him time. I'm going to select my forest row. And what we're going to do, we're going to get the word color to black to black. And then we're going to change the background to white. As you can see, this is now not formatted, right? There's a lot of formatting that we have to do with this data. Let's get started. To get into macros, you need to go to your developer tool again. And I've already talked to you how to get developer tool. If you haven't, then you need to right-click somewhere and customize the ribbon, and then click on this development tool right here. We have already learned how to insert checkboxes are dots using the developer tool. And we're going to learn about how to do macros. Macros is basically it basically records search task. Okay? So what we're going to do is click here and click on the court necrosis. Last you the name of macro and a shortcut key. We'll just keep it as macrons three and press Okay. From now on, whatever we do is being recorded. We need to do everything well, okay. I will go to the Home tab. I'll take the first row, color to blue. And then we're going to change the word color to white as it was earlier. Then what I'm going to do, I'm going to delete this tab because we don't require it. It had no value that we're going to delete it. Then we're going to delete this whole tab bar. At the same time they go on and discount and we're going to delete that one, delete it, then we're going to delete this. And we're going to change back our Euro symbol to dollar. We're going to format this. So whereas our dollars, so here we are. Getting back to dollar. I'm going to change the overall size of our panels. Way. Paul, Do I want to decrease the width and the height of all the rows and columns. We want to do few more things that as I might want to add additional formatting. The three data control, leaf all of these. And then I'll go to conditional formatting and I'll write down top 10% of each, so it will be colored. We're going to get sales price and then highlighted rule. Or maybe just add color scales. As you can see, our data is prepared right here in front of us. Okay, what I'm gonna do is now go back to the developer to stop recording. After doing that, you can see all what I'll do. I'll just control Z or she'd get back to where we were. We have discount tab. We have all those tabs are going to discover the base so we can use macros another place too, Control C and put it into another sheet. And then what we're going to do now is we are going to get, there are several ways you can run a macro. First one is you can go to the macro like on your macro number three. And then as you can see, I like, like don't run, my sheet is formatted. So if I go to another sheet and go to Macros and click on Macros three and run. There are some activation method of range clause fill. Maybe I've Copied less data. Still is done, right? I hope you understood how we did it. Through this macros. You can basically duet with all of the data that you have. At the same time. What I want to do now is go back. And such as there might be a case that what happens is you have all your data. All your data is from other please not starting from display, then you have to use relative references to do macros. Because when you do macros, there is a, basically a visual code in-between and you can click on Module one. When you see this is the code that we will depend. Basically, EXL has recorded all the steps that we have done and then made it into a code. So as you can see right here, we have codes and you can actually wherever end of it. So this is one section of code. This is another section. This is another section of all the things that we have done, though. Yeah, and if you want to add any comments right here in the code, you can edit this code. But that you can edit this code also about we're not going to talk about that. Okay? So we can clearing cells, something like this. This will come in as a comment. Okay? You need studios, apostrophe and an exhale I've already talked about to add a comment. Okay, cut this part. I've talked about how to use it, backgrounds, Basic Visual Basics. We went into the coding place. You can learn a lot more about macros, lot of resources and our macros on various platforms. You should really check them out if you want to. Really powerful feature which can help you save a lot of time. Because if you are working with the same data over and over and over again, then you are basically being gifted with macros which can help you Recorded step and then implemented across various sheets which have similar data. At the same time. I want to do macro with a button. What you can do if you want to get too into the developer tool is really important. And this is where some advanced features of Excel that we use can create a button right here. And I can name that but onto macro three and press okay. I can edit the button name. Such as we can edit the button name to format data. Okay? So what they believe everything we can to the macros is working. You're going to click on this and move it to here. So basically you can add a button right here. And when you click on this button, we have given the function, but given the function to this button to work with that macro that we created, when we click on this Format Data button. This button by itself, that'll do the macro world. You can create these buttons and mark around with macros. I hope you understood how to record a macro. Easy. Then how to stop the recording, how to see macros so you can run it either by clicking here on the individual macro and done. Otherwise, you can insert a button and press on that button and weren't really important feature. You can also go and edit your code. You can check out where he is free sources onto it. If you wanted to do some editing in New York Road or some if-else formed and you can add those effects function so that your macro with a 100% work and there are no errors in the macro. So yeah, that is all for our macros. I hope you understood everything about macros. You can use them into Google sheet also, let me show you for checking this document to Google. And you can remember, and remember you can do a lot of editing like alternating colors and whatever we have learned all the formatting of Hodeidah, even in Excel, we haven't done it for now. So yeah, don't forget about that, that you can do all of those formatting your data. We have this Yukon it as simple macro, but you can record a lot more work in your macro, okay? And if it's sometimes good to get an error, you can recall it again or at the same time, you can edit your code to get in macro and Google Feed, what do you need to do is you need to play. I think it was, it was right here and extensions. And you can click on macro and you can record a macro. Basically, this is basically asking you to use absolute references are used, relative references. That is what we have been had. We had an Excel tool, I told you that you use, you need to use relative references if you want to have data which can work every day, because they're references work in this way that if your data is shifted from this column to Sunday, but the data is similar, then it will work. But at the same time, absolute references with only apply on their individual column or absolute polymer. Also, you can record a new macro right here and then work with macros. I hope you understood how to work with macros and ExpressRoute and exhale. Macros are really, really powerful feature as it can save a lot of time in formatting data hour maybe performed a lot of creating chart or some performance and basic task. You can automate all of those tasks. The Automation Talk. Remember about macros and in future if sometimes you need to use it, Go ahead of it that you can automate a lot of it. I hope you understood all of this. So take care. I'll see you in the next one. 21. Congratulations: Welcome back everyone. Firstly, I would like to congratulate all of you for completing the course. Your hardware kits finally paid off, and now you can competently showcase your skills to the world. I am really proud of you. We're putting in all the time and all the efforts and practicing every skill. And now you must be proficient in the analysis. I wanted to ask you a small favor. So if you like the board, then please don't forget to leave a review. It really helped me a lot at the same time, if you wanted to connect with me, you can find out my LinkedIn and my Instagram account in my profile. You can connect me on both of those planes and leave a review. There are even met with insomnia if you want to offspring. What next? Now, the next thing that you should do is you should go for exhale. You should apply learning appealing tablet, excellent spreadsheet. The next obvious that you should go for in your data analysis, your names W, it's a really powerful software where you can create dashboard. I must have discussed about it earlier too. And I have a question later to it. It don't forget to take it out. And we'll really enjoyed having you. And we have a well-structured floors where you're going to start with learning about data analysis. And then we'll move on to lord tab view. And it's really amazing. At the same time, every adjoining have to add one day. So I would like to wish you the very best of luck for your future endeavors and I pray you get whatever you see in your life.