Transcripts
1. Lesson 1 Introduction to the Course: Are you new to Excel or need a refresher? Then this online course is designed to give you a solid foundation in the basics of Excel. Welcome to Microsoft Excel Basics, the course on everyday exile for the real-world. So why learn Microsoft Excel? The most impressive thing about Microsoft Excel is that it can be used anywhere for any kind of work. Made me Data Management and Analysis, Inventory, finance, business tasks, and even complex calculations. And while it may seem intimidating at first, once you have mastered the basics of Microsoft Excel, it can make your life a lot easier. So basically, Microsoft Excel is a spreadsheet program developed by Microsoft in 1985 with the sole purpose of helping businesses compiled all the financial data, yearly credit, and yearly debit sheet. Microsoft Excel can help you solve a lot of business problems. Microsoft Excel can help you get a lot of stuff done. Xl can not only make you better at your job, no matter what job it is, but having a know-how of Microsoft Excel can instantly increase your job prospects as well as the scope for your group. Now let us look at the applications of Microsoft Excel. Excel can be used for data entry and storage. It can be used for managing a large volume of data with a lot of 0s. It can be used for collection and verification or business data. It can be used for scheduling and forecasting purposes. Xl can actually help you build great charts and professional dashboard. It can help businessmen developing future strategies. The best part about Excel is you can have an online access to all your data 24 by seven. It not only helps in data analysis and decision-making, but also helps you in managing budgets, accounts, and other financial data. And that's not all. Excel can be used in any type of business and in every sphere of life. So let's learn a little bit about the course. This course will give you a strong foundation in Excel formulas and functions that transform Excel from a basic spreadsheet program into a dynamic and powerful analytical tool. So let's understand what will you learn in this particular course. The XL basic masterclass program is aimed at learners who seek to learn Excel from a ground-up. This is the first part, which is the basic part of the two-part series. And it is aimed at Introduction to Excel tools and techniques for daily use. No experience with Excel is necessary whatsoever. And while this course is meant for beginners of Excel, advanced users will undoubtedly bigger new skills and new tools from the course. The objective will be to make you comfortable navigating the Excel user interface, entering, manipulating, and formatting data. And it will make your capable of going ahead and creating easy to use worksheets. The course will teach you how to analyze data and present the results in a user friendly manner. And you will also learn how to go ahead and create XL charts and tables that effectively summarize your raw data. In other words, you not only get comfortable with the basics of MS. Excel, but also become confident in using it in your day-to-day life. Look at the topics that will be covered in this particular course. We will start off by getting familiar with the Excel interface, and then we will move on to learn creating and formatting spreadsheets. We will gradually move to creating formulas and learning absolute and relative cell referencing. And then we will learn how to write formulas. In exit, we will get introduced to some inbun functions in Excel. And then we will know sorting, filtering, and managing data through data tables. We will also get introduced to some basic charting and formatting techniques. And last but not the least, we will also learn how to go ahead and print Excel reports. The best part about this particular course is that every lecture comes with ample of resources, but solved examples and raw data for your practice and projects in a nutshell, in 20 engaging lectures, you will learn how to use navigating Excel, Matt, statistical formulas, logic, and text functions. As you participate in this course, you will clear away the pain of stumbling through your daily tasks as you will learn the basics of Excel in this course, you will lead out stepping stones of building a solid foundation for your Excel journey in the future. My objective in this course will be to teach you how to think like an XL.
2. About the Course and the Project Worksheets: Hey, welcome back. Now before we get into the lecture and start learning Excel, let me just tell you a little bit about the course and how you need to go ahead and approach this particular course. Now the course is basically divided into 20 different lectures. In every lecture you will come across a new concept and a new tool in Excel. The initial first few lectures are purely Introductory, which, which will get you familiar with the Excel interface. But if you're already familiar with the Excel interface, you can directly start off this particular course from lecture number six. Now, along with the course, you have ample of resources that you can actually use for your practice. So overall, there are four different resources that have been attached with this particular course. Let's look at them one by one. So the first file is basically something that you would use along the course. It has an index and the index follows the same sequence that of your lectures. While learning a particular concept, I would recommend that you use this particular excel sheet and understand the concept properly. Once you have understood the concept properly. That is a raw data file that you can use for your practice. Now the raw data file also has the exact same index sequence. However, it only has the raw data. So what you can do is once you've understood the concept, you can open up the raw data sheet and practice the concept exactly the way it was taught in that particular lecture. So that will actually give you a hands-on practice in terms of understanding the concept better. Finally, when you have gone ahead and understood all the concepts in sequence, then I would recommend you go ahead and open the project file. Now the project file basically has Levin different tasks that you would have to go ahead and complete. And you will observe that just next to the task, you have a hint in terms of in which particular topic you would find the solution for the same. I would highly recommend that you attempt this project because if you're able to complete this project, then you have got a good understanding of all the concepts that were thought in that 20 lectures in case you get stuck somewhere, then I would recommend that you refer to these topics once again and reattempt the project. Because only that will give you the confidence of approaching Excel with ease. And only when you have completed the project and attempted it all by yourself. And then I would recommend you go ahead and refer to lecture number 21 to see if your results exactly match to the solution shown in the final lecture. So that was a small tip of information that I wanted to give you before we go ahead and start jumping into Excel. So I'm really excited to see you a part of this particular course. So let's get started.
3. Lesson 2 Ribbon: Hey, welcome back. So before we get off the ground and start running with Excel, let us first get introduced to the interface of Excel and also understand a little bit on how we go ahead and interact with this interface and get things done in Excel. So first thing that we want to get introduced to in this particular section is what we referred to as the ribbit. Now first introduced in Excel 2007. The ribbon is a strip of buttons and icons located above we'll work area which is overused. Okay, this entire area over you is referred to as the Ribbon. Another ribbon actually replaces the menu and toolbars found in earlier versions of Excel. So in other words, ribbon is a row of tabs and icons at the top of Excel window that allows you to quickly find, understand, and use commands for completing certain tasks. It looks kind of complex at first go, but as and when you start working with Excel, you will get introduced to the different menus within your ribbit. Ribbit is basically made up of four components. The first one is what we refer to as the tabs. Now what are these tabs? Ok, so when I'm looking at my ribbon over you, all these things that you see at the top. Okay. Wherever I'm clicking. Now, all this is referred to as the ribbon tab. And basically a ribbon tab contains multiple commands which are logically subdivided into groups. So for example, this entire section forms one particular group. We're the font section forms another group, the Alignments section forms another group. So if at all, I want to go ahead and do some kind of a formatting with my font, then I will be interacting with this particular group over you. And if at all, I want to go ahead and do something with the alignment of the text and the values that I have in my Excel sheet, then probably I will deal with the alignment section. Now as and when I go ahead and click on any of the tabs, you will observe that respective menus and groups pop-up under the same. So in other words, it had been group is a set of closely related commands which are normally performed as a part of a larger task. Now let's look at something which we call as a dialog launcher. Now, what exactly is a dialog launcher? Now what happens is, let's say if I'm under this particular section and I'm doing some kind of a formatting with my phone. So I see the small arrow V0, which is pointing down. When I click on that, you will observe that a more advanced menu for granular level formatting will appear wherein, you know, I can't just go around clicking on the different tabs over your like number, alignment, fawn Borders, failed protection. And then I can go around and play with this menu. Go ahead and format my data as per my need. Now, under every group, that is something that we also referred to as the command button. Now what happens is when I click on a particular command button, it will show up a small tooltip menu and it will tell me what exactly this particular command button will do. For example, when I'm clicking on this particular command button over here, it tells me that it makes my text bold. Let's say if I'm going ahead and clicking over here, it says that it will center my content. So basically a ribbon has four sections. One, the tabs, and then it has a ribbon group that in closely related commands are grouped together. And then there is a dialog launcher option for granular level formatting. And then there is a command button. So when you go ahead and click on the command button, the tooltip will exactly tell you what the command button does. Now there are few more things that I want you to know about Rubin. Now if at all i go to my ribbon and right-click on the empty space anywhere. I get these two particular options over you. Customize the ribbon and collapsed the ribbon. Let's see what happens when I click on collapsed the ribbon. When I click on it, you will observe that my ribbon has now vanished. And now I have a bigger area to work with in case I want to go ahead and bring back my ribbon, I can just double click on any of the tabs over here. And my ribbon comes back. Now, if I go ahead and right-click on my ribbon over year, I also see one option which is customize the ribbon. And this gives me an option of being flexible in terms of what I want to show and not show on might've been. So let me just click on that. And when I click on that, it opens up this entire new pop-up window, wherein it shows me a lot of commands that I can go ahead and check and uncheck. And accordingly, they will be displayed, are not displayed on my ribbon. For example, I want this Developer tab to be seen on my ribbon so I can go ahead and put a checkmark on that. And then when I click on OK, you will observe that the developer tab has appeared. If I click on the Developer tab, the respective menu is also available. But currently I don't need this Developer tab, so I can always go back to my Customize the Ribbon option. I can go ahead and uncheck this option and then click on OK. And now my Developer tab has vanished. So in this way, you can actually go ahead and collapse the ribbon so that you get a larger space to work with. And you can also go ahead and customize your ribbon and make the various commands appear or disappear from your ribbit. Now, also in the resource sheet that has been provided with this lecture, I have gone ahead and given you a brief description about what exactly the different tabs are used for, which you can read at your leisure. And in case you don't understand everything in the first go, need not worry about that because as we progress through the next set of lectures, we will be gradually using these various tabs. And then gradually you will start getting familiar with the various tabs and the different groups and the buttons which are available under the various sections. So that was a brief introduction about ribbon. I hope you enjoyed this lecture and I shall see you in the next one. Exactly.
4. Lesson 3 Workspace: Hey, welcome back. So in this particular lecture, our focus will be on understanding the workspace. And it is the second most important thing that we will learn in Excel because this is where all the action happens, because all our work actually gets done on the workspace. So what I have on my screen over here is a small snapshot of the workspace over here. And we shall quickly go through the most important elements of the workspace so that we get familiar with the interface. And then working with Excel becomes easy for us. So the first thing that we have over here is what we refer to as the Quick Access Toolbar. Now what you see on the top left hand corner of my screen over here is what we refer to as the Quick Access Toolbar. And the objective of the Quick Access Toolbar is to make available all of those functions that we use most often. So we really do not want to go through all the different tabs and search for these functions over here. So all those commands that we need handy are available in my Quick Access Toolbar. If at all I click on this down arrow, it gives me an option that says Customize Quick Access Toolbar. And over here, I have a lot of options. So let's say if at all I want to go ahead and select Quick Print, and I want it to be available on my Quick Access Toolbar so that I can quickly print any document without really going through the entire print menu. So I can go ahead and add it over here. And you will observe that the pre-print option has appeared on my Quick Access Toolbar. Accordingly, there are also lots of other commands that you can go ahead and add. And for that, you can go ahead and click on the More Commands option. And when you do that, it will open up a window with a lot of popular commands and you can go ahead and add or subtract any elements from your Quick Access Toolbar according to your need. Then next is what we refer to as the title borrower you. So let me just go to a blank sheet over here. And when I open a blank sheet, what do you see over here is the title bar. So basically it tells me what is the title of the excel sheet over here. When I go ahead and save the sheet and give it a particular name, the respective name will appear a warrior. So right now the name of my sheet is new workbook basic copy of Excel. And that's why it is showing me this under the title bar section. Now the next thing that we want to understand is what we refer to as the worksheet window. Now you have to understand that basically excel is a spreadsheet program. So what you see over here at the top are the columns over you. And these are my rows. The intersection of the columns and the rows creates a cell. And any data that we go ahead and put in Excel goes into these cells. Now what you see over here is something that we referred to as the name box. And if I'm in this particular cell, it says C2, which means that I'm currently in column C and row number two. If at all i go ahead and select a certain area, it will always show me the first cell in the selection, and that is c2. If I move to this particular cell over here, it tells me the name of my cell is FTN, which means now I am in column F and row number ten. And the alphabets that you see at the top over your are referred to as my column headings. Now the next thing that we want to understand is the vertical and horizontal scroll bars. So let me just go to a blank sheet once again. And what you see on the right-hand side are my scroll bars over u. So this is my vertical scrollbar and this is my horizontal scroll bar. So let me just come back to this sheet so you will understand it better. When I click on the vertical scrollbar, I can actually move through my sheet in a vertical fashion. And when I use my horizontal scroll bar, you will observe that I will be able to move horizontally on my screen. Now somewhere at the bottom over here we have something called as the zoom control. Now the zoom control is basically used for magnifying and shrinking of the Active worksheet. So if at all, i go ahead and move my zoom control to the left. You will observe that the size of my worksheet decreases. And if at all I move it to the right, it will go ahead and magnify my worksheet. And when I bring it back to the center, it will come back to the default settings once again. Now let's look at something called as a sheet tabs. Now if I click on this particular sheet over here, it says the name of the sheet is sheet one. If I click over here, it says the name of this particular worksheet is workspace. So basically what a sheet tab does is it displays the name of the worksheet in the workbook. And whenever you open a new worksheet, you will observe that you would only have one sheet tab away you, but you can always go on adding more sheet tabs by clicking on the plus button. And in case you want to delete a sheet tab, you can right-click and just delete a worksheet in case there are a lot of sheet tabs in your worksheet. You can always use this growlers available over here. And you can scroll between your multiple worksheets. Whenever you click on any of the sheet tabs, it will open up the respective worksheet accordingly. Now, just like the alphabets that we have over here are called the column headings. The numbers next to each of the rows are referred to as the row headings. Now let's look at what is this Select All button. So you fit all like go to any of the worksheet over you. And let's say I want to go ahead and select the entire content of this particular worksheet that what I can do is I can click on this particular section or V0. And this is what is referred to as this Select All button. If I click away, you will observe that all the content on my worksheet and gets selected in one go. So this can come really handy in case you want to move or copy the content of one worksheet to another worksheet. And then there are some other benefits of the same which we will see in the due course. Now let's look at something called as the formula bar. Now the formula bar is something that displays the value or formula which is available in your active cell. Now what do I mean by that? So let me just go to a worksheet over here. And let's say I go ahead and click on this particular cell over here. Now if you observe this particular section of my worksheet, it displays the same value which is available in my active cell. An active cell is the cell where my cursor is currently pointing. And if at all, i go ahead and move the active cell, you will observe that whatever is the content of my formula bar, it changes accordingly. Now basically, when you go ahead and enter any formulas are functions over your, you will realize during the course that instead of showing the values, it will then go ahead and display the formulas on the functions which are available in that particular active cell. But as of now, since it is only holding a value or a text, whenever I go ahead and move my active cell, you will observe that the content of my formula bar is changing accordingly. That is about the formula bar. One last thing that I want you to know about the worksheet is something that we also referred to as the quick input Toolbar. Now let me just go to a worksheet over you and let me just select this entire data over you. And when I come down to the bottom section over here, it gives me some information. It gives me the average, the count, and the sum of all the values in my selection over a year. Now if at all, i go ahead and right-click over year, it gives me various options wherein I can go ahead and check and uncheck what really I want to go ahead and display in my quick info toolbar. So let's say I also want to know what is the minimum and the maximum value in my selection. And when I do that, you will observe that the minimum and maximum also appears in my quick in Fort toolbar. And that is a quick know-how about the workspace in Microsoft Excel where the actual work is done? So with that introduction, I expect that you must have got somewhat familiar with the Excel interface. And in the next section is when we will start actually working with exit. So I hope you enjoyed this lecture and I shall see you in the next one.
5. Lesson 4 Basic Navigation: Hey, welcome back. So this particular lecture is dedicated to basic navigation in Excel. Now you might wonder a whole lecture on purely navigation. And I might say yes, because there are several ways in which you can actually navigate through the Excel grid. And having knowledge of that can be a good to know information. Because that can help you get things done faster when you're actually moving through multiple cells and sometimes maybe through multiple worksheets. So let's look at them one by one. Now if at all I want to go ahead and select any particular cell on an Excel sheet. It's pretty simple. I can go to a particular cell and just left-click with my mouse and the cell gets selected. And in this way, I can actually select any cell on my end tile Grid overview. Now let's say if at all I want to go ahead and select a particular cell which is down, then I can always use my scroll bar and I can scroll down and select the cell of my choice. Similarly, I can scroll up using my scroll bar and I can select a cell up and down. Now let's say if at all, I want to go ahead and select a range, and I want to select a range from the cell L4 to N 15. So I can go ahead and select a cell by just left clicking my mouse and dragging it across the entire grid. But now let's say I want to go ahead and select another range which is non-contiguous to the range that I've already selected. Then how can I achieve that? It's pretty simple. All I need to do is just press the Control key on my keyboard and then select another range. Maybe another range. I can select another range, and so on and so forth. So all I need to do is just press the Control key on my keyboard and I can select any number of non-contiguous areas on my entire grid. However, there is no shortcut on the keyboard to achieve that, you will always have to use a combination of keyboard and mouse to do that. Now another way of going ahead and moving across my grid is by using the directional arrows. Now I can use the left arrow to move to the left. And every time I do that it moves one cell to the left. If I go ahead and press the right arrow key, it moves one cell to the right. If at all, i go ahead and press the up arrow key, it moves up and the down arrow key, it moves down. Now every time I go ahead and give some value in a particular cell and I press Enter, you will observe that it moves one cell down. And even if I don't give any value, and I keep on pressing Enter, it keeps on moving one cell down in the same column. Now, if at all I want to move one cell up, then I use the combination of shift and the Enter key. And it will move one cell above the active cell in the same column. Now let's say if at all I enter a value over here, but I want to move the right-hand side rather than going down. Then how can I do that? So I will just enter a value where you, and then I will press the Tab key. And I press the Tab key, whether I enter a value or I don't enter a value. Every time on pressing the tab key, my cursor will move one cell to the right. Okay? And every time I want to move back to the left, I can use a combination of shift and Tab. And it will move one cell to the left. Okay? And it will do this movement in this same room. Now let's say if at all I want to move one entire page up or down, then how can I do that? All I need to do is I just need to press the page up and page down key on my keyboard. So when I press page down, it moves one page down. When I press page up, it moves one entire page up. Similarly, if at all I want to go ahead and unmute my entire page to the left or to the right. What I can do is I can press the alt key on my keyboard and press beach down. When I do that, my entire screen moves once clean to the right. When I press alt and press Page Up, again, it moves the entire screen to the left. At any given point of time. I can go ahead and press the home key. And what the home key will do is it will move the cursor to the beginning of the row in column a, which means, let's say I'm in column K 18, and now I'm going ahead and pressing the home key. Now let's see what happens. And when I press the home key, you will observe that it moved completely to the left in the same row and went and settled down in column E. When I press control home, it will move my cursor to the home position, that is cell number A1, which is the beginning of this particular grid. So let me just place my cursor somewhere over here. And now what I'm gonna do is I'm going to press Control and I'm going to press the home key. And when I do that, my cursor actually jumps to the first cell in my grid, which is cell A1. So that is about a basic navigation in exit for somebody who is very familiar with Excel, this lecture might not have been a great value add. However, for somebody who's new to Excel, it is good to know information. And if you know these shortcuts in Excel, sometimes you can really accomplish things at a much faster pace.
6. Lesson 5 First Excel File: Hey, welcome back. So now that we are familiar with the Excel interface and the various buttons, commands, functions that are available on the Excel window. Let's go ahead and create our first excel file low. So in this particular lecture, we will understand how do we go ahead and create a new file? How do we add data to the file? And then how do we go ahead and save the file? So let me just go back to my desktop post. So I have a shortcut to the Excel application over here, so I'm going to double-click on that. And when I did that, the Excel menu has just popped up on my screen. And as you can see now that the title of my Excel file is Book Five, which is a default name. Now if I go ahead and click on file, you will observe that I have a huge list of files over you. And over here, I have a message that says Pin files you want to easily find later. Click the pin icon that appears when you hover over the file. So let's say I want this particular file to stay in my list and I want to use this file very often. Then what I can do is I can just click on this particular pin button over here. And when I go ahead and keep my mouse cursor over there, it pops up a small tooltip window which says spin this item to the list. And when I click on that, you will observe that it says this particular item is now pinned. So what happens is next time when I open up my Excel file, this particular file will always be available for easy access per me. Now, when I go ahead and click on new, it opens up this particular window in front of me. There in the first option is a blank workbook. And other than that, Excel also offers me a lot of ready-made templates that I can work with. And that actually makes my work easy because there are times when I would need some ready-made templates, like a business template or a presentation template, a planner. And using these templates can really make my work easy. But since we are going to go ahead and do things from scratch away or I'm going to double-click on a blank workbook. I did that. A new worksheet just popped up in front of my screen over you. Now what I'm gonna do is I'm gonna go ahead and create a small sports inventory worksheet. So let's say the name of my store is Max Sports store. And I'm going to have a few headings over here. So I'm going to call it itemName. Then I will add the quantity, and then I will add the price. Then I will go ahead and add some items to my item list over here. So let me just add bat, ball, hockey, Racket's, shoes and gloves. Let me just add some quantity over here. So let's say this is 45, this is 65, and 85. And you will also that every time I'm pressing Enter, it is just moving one cell below it. So I'm going to say in 90, I'm gonna say a 101150. And then let me just add some price over here. So I'll say maybe 89.9956.55. And the last one. Okay. And now you will observe that what is happening is whatever I have typed in this particular cell it is spilling over to the next cell. I can always go ahead and align that properly. So what I can do is I can just click on this particular button over here, which is the Select All button. And I can just double-click on this particular section over u. Ok? And you will observe that all the items within the cell are now arranged properly. Okay, so it's not spilling over to the next cell. And let's say as of now, this is the only thing that I want to go ahead and add to my file. So what I'm gonna do is I will just click on File, click on Save As. And I will say save this file on this PC. And I'm gonna save it on desktop. And I will call this file as inventory file. And I will save it. And now I can just go ahead and close this file. And when I do that, you will observe that this particular file is now available on my desktop. Let's go ahead and double-click on it and you will see that the file has just opened up. And this time the title of my files is inventory file because that is the name that we give when V went ahead and saved the file. So this was a quick lecture in terms of understanding how do we actually go ahead and add data to the Excel grid? And then how do we go ahead and create our first pile and then we save it. And once saved, you can always double-click on the file and it will open up your last saved file. So I hope you enjoyed this quick lecture and I shall see you in the next one.
7. Lesson 6 Basic Formatting: Hey, welcome back. Now spreadsheets are often seen as boring and PR tools of utility. It's true that they are useful, but that doesn't mean that we can't bring some style and formatting to a spreadsheet. Good formatting help the user find meaning in the spreadsheet. We're not going through each and every individual cell and says with formatting will always draw the user's attention to important details and will help him in making decisions. So our focus in this particular lecture will be on understanding the formatting basics in Excel. And we will understand various tools and techniques that we can use for formatting or fonts or background colors are says, alignment, angles, etc. So let's get started. So the first thing that we will start off with is understanding how do we change fonts. Now let's say I want to change the font of Max Sports store. So what I will do is I will just select this particular cell and then I will go to my home tab over you. And you will realize that whenever it comes to formatting, I'm gonna make use of the home tab most of the times because most of my formatting tools are available under the home tab. So when I click on home, I have this font section over you. So I will click on the drop-down over here. And then what I can do is I can select a font of my choice. So let's say I will select a real black. And the moment I did that, my font has changed over your, let's say I want to go ahead and select another phone style. So let me just go ahead and select maybe Bookman old-style. And the moment I did that, my font changes. So this is how I can actually go ahead and change the font in my Excel sheet. So now the next thing that we will learn is going ahead and changing the font colors and also the background colors. So what I will do now is I will just change the background color of my headers over you. Over here, I will just select all these three cells with the mouse click. Go to this particular option that says Fill Color. And let me just go ahead and select darker shades. So let me just select maybe a dark gray shade over you. And now what I will do is I will change the color of my fonts. And for that, I will come to this particular option that says font color. And, uh, we, I will select the color is white. So now it has gone ahead and change the color of my background as well as my font. Once again, I will just double-click over here so that everything fits properly. Okay? And now what we will also do is we will just change the format of our font. So what we will do is we will probably go ahead and just make it italics. Okay? And now let's go ahead and understand how do we go ahead and add some borders. But before that, I want you to understand how do we go ahead and now you will observe that this particular heading over yo is towards the left hand side. I want it to be placed in such a way that it is exactly in the center where you're just about my headers. So what I can do is I can just select all these three cells together. And then I will come to this particular option that says merge and center. And when I click on that, it just merges the three cells together and places that text exactly in the center. I can once again go ahead and change the alignment of my text by clicking on a line left, aligned center, or a line right. Now it looks better in the center, so I'm gonna go ahead and align it in the center. Once again. Let me just go ahead and change the background color over here. So let me just give it a light blue shade. Okay, now let us understand how do you go ahead and add borders, etc. So let's say I want a border for this Max Sports store over your. So what I can do is I can just select this entire cell, come down to this particular button over here that says bottom border. And I will select tick outside borders. When I do that, you will observe that now my Mac sports store has a border around it over you. I do not want the borders. So what I can do is I can just select these cells, come down to this particular option and I can see no borders. Ok, let's say we only want the border for the outside. Then I can just select this particular sin, go to the borders option. And I can say take outside borders. So now you would observe that all the borders within the cells have vanished. And now there is only one thick border on the outer section of my data over you. So in this way, I can go ahead and change the arrangement of my boarders. Now in case if I want to go ahead and change the font colors over here, I can go ahead and select the font. And then I can go ahead and select a particular color over here. So let me just select maybe red and let me just make it Italica. Okay, so now with that, we have understood most of the concepts of going ahead and formatting the data. Now, let's look at something called US alignment of angles. Now let's say I want to play something or V0. And what I'm gonna do is I'm going to select all these cells together. And I'm going to click on merge and center. And now let me just go ahead and type inventory stock or we'll ok. And now what I will do is I will give it a nice border. But I want to go ahead and change the alignment of the text that I have over you. So for that, what I can do is I can select this particular cell, and then I can right-click on this particular cell and click on this option that's is Format Cells. And then I will go to the alignment optional video. And you will observe that my current alignment is horizontal. If I click on this red dot over your, I can actually go ahead and change the alignment. I'm going ahead and making it a vertical alignment. And I will click on OK. And now I can also go ahead and change the alignment of the text over you. And now since this is what he couldn't text, I will go ahead and click over a year, which is middle alignment, and it comes exactly in the center. Now once again, I can select the cell, give it a nice background shades. So let me just select this one and let me just select a font color for it. So let's go with orange. And then what I can do is I can just make it a little bowl and I can increase the size. So this is for increasing the font size, and this is for decreasing the font size. Similarly, what I can do is I can always select the font and I can increase and decrease the size of my phone from EU. Now let's understand a little bit about formatting Excel worksheet. Now let's say I want to go ahead and create an exact copy of this particular worksheet. So there are two ways in which I can do that. One, I can go ahead and click on the plus button over you. Come to this particular sheet, click on select all. And then I will copy by pressing control C, go to sheet two, and then I will go ahead and press control v. So now I have an exact copy of my Sheet one. But that is another alternative way of going ahead and doing that. So let me just delete this particular sheet. So I'm gonna right-click on it and click on delete. So another way of going ahead and creating an exact copy of an Excel worksheet is I can select this particular sheet over here. And then I will press the Control key on my keyboard. And then I will just left click with my mouse, and I will just drag it to the right-hand side. And now there is another sheet which is being created and the name of the sheet is sheet one. And in the brackets there is a number two, which means that this is a second exact copy of my sheet, one. Okay. So that's the way I can go ahead and copy and recreate a worksheet. Now I can always move the data within my worksheet so I can always copy the entire data. I can press copy and I can go ahead and create a copy, or I can just go ahead and right-click, click on cut and then move my data to another section. Let me just go ahead and undo that now. Now let's say I want to go ahead and move this entire data to another worksheet. So I can always go ahead and cut this, create another worksheet, and then I can go ahead and press control v. And you will observe that my data has now moved from this particular sheet, two sheet number four. Now in order to go ahead and delete any sheet, what I can do is I can always go ahead and right click on the sheet, click on Delete. It will ask me whether I'd really want to go ahead and continue. So I will click on delete and you will observe that the sheet is now deleted. Let me just delete this particular sheet does when it's I will right-click, click on delete, and this heat is now deleted. Now one more thing that I want you to understand is the height and the width of the columns and the rows within your excel sheet are very flexible, which means that you can change the size and shape of these cells as per your choice. So how can I do that? So let's say I want to go ahead and change the size of these four columns. So what I can do is I can just select these four columns, right-click on it, and then I can go to column width. And let say I will select the column width as 20. And when I do that, you will observe that all the four columns now have a standard width and they are all of width 20. Similarly, for my rows, I can select the rows where I want to go ahead and change the height. And then I can just right-click. Click on row height, and then I will select the row height of my choice. So let's say if at all I make it 18, you will observe that now all the rows have a standard height of 80. Now let us say that after this item name over your, I want to go ahead and add one more column, which gives me the product ID. So how can I do that? Now, Excel gives me a wonderful option of going ahead and inserting rows and columns wherever I want within my Excel worksheet. Let's say I want to go ahead and insert a column over here. So what I'm gonna do is I will select this particular column and I will click on insert. And when I do that, you will observe that a blank column has been inserted over here. And now I can go ahead and add a product ID and start filling my data away you. But let's say later on I realized that, OK, I don't really need the Product IDs in my dataset. I can always go ahead and select the column and then click on delete. Similarly, four rows. In case I want to add a row, I can right-click and click on Insert row. And if at all I want to delete the row, I can right-click on it and then click on delete and my rho is deleted. So that's about adding and deleting rows and columns within your worksheet. Now one last thing that I want you to know in this particular lecture is Excel also gives you an option of going ahead and hiding, and I'm hiding your rows and columns. So let's say in my dataset over here, I want to go ahead and hide the price over you. And I only want to send the data to someone who should be only looking at the item names and quantity. And I just want to make this particular section as hidden. So what I can do is I can just right click on the column over here and then click on height column. In case I want to go ahead and bring back my column, all I need to do is just select these two columns over here, right-click and then click on unhide. Similarly, if at all I want to go ahead and hide something in my rows, then I can just select the rows. And then I can just right-click on this and then click on high Drewes. So you will observe that all my rows are hidden in case I wanted to bring them back. Just select the row one above and one below the hidden section. Right-click on it and then click on unhide. And just in case if I ever want to go ahead and change the name of my worksheet, I can always do that by default it will be Sheet one, xi2, xi3, et cetera. But let's say I want to go ahead and give this particular worksheet a name. So what I can do is I can just select this worksheet, right-click on it, and then click on Rename. And then let me just give it a name as inventory. And when I hit Enter, you will observe that the name of this particular worksheet has been changed in case I ever want to go ahead and give some kind of a formatting to my sheet tabs. What I can do is I can just select the sheet tab that I want to go ahead and format. Right click on it, click on Tab Color, give it a color of my choice. Then I will go to this particular sheet, go to Tab Color, and then I will give it this particular shape. So in this way, I can also go ahead and change the color of my sheet tabs. Now before we wrap up this section, there's one small thing that I also want to show you. Now this particular column has the price for all my inventory or were you? So what I can do is I can also go ahead and format all these numbers. So I can just right-click on it and then click on format cells. I will go to the Number tab over here. Click on currency, and I will select Australian dollar over here. You can select the currency of your choice. And I can also send that decimal places over u. So maybe I can just keep it to two as of now, and then click on, okay. So with that, all the numbers are now converted into currency. So that is how I can also go ahead and format my numbers. So that is about the formatting basics in Excel. But the objective was to give you some of the easiest ways to bring formatting to your spreadsheet with just a few clicks. So I hope you enjoyed this lecture and I shall see you in the next one.
8. Lesson 7 Using Cell Reference: Hey, welcome back. Now it's hard to get excited about learning maths. It's something that most of us spend our lives avoiding. And it also one of the best reasons to use Microsoft Excel pop perfect calculations every time. So do not think about Excel formulas as math for math's sake. Instead, imagine that these formulas can help you automate your life and skip the trouble of making manual calculations. At the end of this tutorial, you will understand how mathematical functions actually work in exit. So on my screen over here, I have some mathematical equations and we will learn how do we use Excel in going ahead and evaluating these equations. So the first equation that I have over here is seven plus five. So for that, what I will do is I will just say equal to seven plus five. I will just type this. And you will observe that when I'm doing this, my formula bar over your has this same equation. Okay? And when I press Enter, it gives me the value as 12. Similarly, now let's understand how do we do subtraction in Excel. So my equation over here is eight minus four. So for that what I'm gonna do is I'm going to type is equal to eight minus four. And then I will hit Enter. So I got the answer. As for now, let's go ahead and look at this particular equation. And if I apply the BOD mass rule, it says that first it should do the equation within the brackets. And then based upon the BOD must rule, it should do the division first, then multiplication, followed by addition and subtraction. So if I look at this equation over here, it's one plus nine minus two into threes. So in fact, the first calculation should happen inside the brackets. So the value will be ten. And then two into three is six. So ten minus six, the value should be four. So what I can do is in Excel, I can type the formula as equal to open brackets. Then I will type one plus nine. And then I will close the brackets, give a minus sign. Then I will type 24 multiplication. I will use the asterix is the shift and the number eight on a standard American keyboard. And then I will type the number three. And let's see if I get the right answer. So yes, Excel has done the calculation for us and it has given us the right answer. Now, let's go ahead and do the division over you. So my equation is minus 50 divided by five. So ideally I should get an answer of minus ten. So let's go ahead and type it is equal to minus 50 divided by five. And extent has done the calculation for us and return the value as minus ten. Now, all of this looks pretty simple, isn't it? But however, using hardcoded values has its own limitations. Let's understand what. So what I'm going to do is once again, I have the same equation over your and my inputs are 75. And I will say equal to seven plus five. And I will hit Enter. Now, let's say instead of seven, if I go ahead and change this value to ten, then ideally I should get an answer of ten plus five equal to 15. But Excel is not going ahead and doing that for me. Now, why is that? And that is because what I have done over here is instead of giving a cell reference, what I have done is I have given hardcoded values and that is very evident in my formula, bar over u. So this is when adding hardcoded values has a limitation. And we want to go ahead and take care of that. So Excel gives us a wonderful option, varying beak and give a cell reference. And Excel will do the same calculations for us. Let's understand that now. So what I'm gonna do is I'm going to delete the formula from you. I will change this value to seven. And now what I'm gonna do is I'm going to type the formula as equal to this particular cell, which is E 16. You can look at the formula bar over there and it says Is equal to E 16. I'm going to press the plus key on my keyboard. And then I will say add this particular cell, which is F 16, and then I will hit Enter. So once again, it has gone ahead and done the entire calculation for us. But now let's say fatale, go ahead and change this value from seven to maybe 70. And now you can observe that Excel has automatically picked up the new value and done the calculation for me, because now my values are not hard coded and I made use of the cell references while writing my formulas in this particular cell over you. Now similarly, four minus what I can do is I can say is equal to this particular cell minus this particular set. Okay? And you will observe that the formula bar now says is equal to E 17 minus 17. And I will hit Enter. And now if I go ahead and change this value, you will see that Excel does all the calculations automatically for me. Now let's go ahead and write down this particular formula. So for that, what I'm gonna do is I'm gonna type is equal to open brackets. And I will say this particular cell plus this particular cell. And I'm going to close the bracket. And then I will say minus this particular cell, okay, which is GAT. Then I will give an asterisk sign for multiplication. And then I will say multiply it with h. And then I will hit enter. And you will observe that it gives me the value as four. If I go ahead and change these values over here, let's say I instead of nine, I make it ten. Then it does the calculation automatically for me. I will just go ahead and change the value back to nine. And last, I will go ahead and do the division over here. So I'm gonna say is equal to minus this particular value, which is E19. I'm going to divide by F9 and I will hit enter and it gives me the value as minus ten. If I change this value from 50 to a 100, you will see that Excel does the calculation automatically for me. So the objective of this particular lecture was to make you understand, first of all, how do we go ahead and type formulas within Excel? And then how do we go ahead and give a cell reference so that Excel does all the calculations automatically for us as and when the values within those particular cells change. In the next set of lectures, we will understand some inbuilt mathematical formulas in Excel. And then we will also go ahead and understand the concept of absolute and relative reference. So I hope you enjoyed this lecture and I shall see you in the next one. The
9. Lesson 8 Absolute Vs Relative Reference: Hey, welcome back. So in our last lecture we understood the concept of giving this cell reference. Now let's move one step further. So I have this sheet over you, and I have ten different products listed in this particular sheet. I have their quantities and then I have the up prices. Now if I want to build a formula using the cell reference, what I can do is I can say that my revenue is equal to quantity multiplied by the price. Okay? And I will hit enter. So now it has gone ahead and multiplied ten with ten and given me the value as a 100. Now, one of the benefits of using a cell reference is I can just copy this particular cell over here. And I will just go ahead and select this particular range and paste the formula where you, and because I have used a cell reference over here, it has gone ahead and done all the calculations for me automatically. Okay, so 20 into 20 is 430 into 30 is 900, and so on and so forth. Let me just undo this. Now one more way of going ahead and copying the formula is I can go to any cell. And when I come down to the right hand side bottom of the cell, it shows a plus sign over here. I will just left-click and then I will just drag the cell and now just absorb the formula bar. Ok. And as I'm moving down, you will understand that it has gone ahead and picked up the relative formula. Now this is called as a relative referencing. Now let's say instead of this particular price over yo, okay, instead of the changing price for every product, let's say I have a fixed price and the price is 50. So can I go ahead and use the same formula? Let's give it a try. So what I will do is I would say equal to, I will select my quantity and then I will multiply it with this particular cell over you, cell C3, and I will hit Enter. So yes, it has done the calculation for me. So 50 into ten is 500. But let's see what happens when I go ahead and drag this particular formula. Oops, it is giving me an error because it is not returning the right values to me. Now, why is that happened? Now let's look at this particular cell over here. So over here the formula is C7 into S3. But as and when I went ahead and drag the formula down, you will observe that now because my cell in this particular column quantity is moving down by one row each time. So C7 became C8, C8 become C9. Similarly, you will observe that over a year, my reference for C3 is changing by one cell. So S3 becomes s4, and then C4 become S5. But there are no values over you. That's why it is going ahead and returning an error. And this is where absolute referencing comes into picture. And basically a cell reference that won't change when copied is referred to as the absolute reference. So how do I go ahead and give an absolute reference over you? So what I'm gonna do is I will go to this particular cell. I will once again type the formula. So I'm gonna say C7 multiplied by C3. But this time I'm gonna go ahead and press the F4 key on my keyboard. When I do that, you will observe that there are $2 signs that have appeared next to the column and the row, ok. So dollar c means that now my column is absolute, my column is frozen. And $1.3 means that now even my rho is frozen. So the dollar signs on both the rows and columns means that now this particular cell reference is frozen. Okay? And now I will hit Enter. And now if I go ahead and drag the formula, you will observe that now it has done the calculations perfectly. So this is the beauty of absolute reference. So relative reference are extremely useful. But there are times when you don't want the cell reference to change. And that is when you are absolute reference comes into picture. Let's understand that more in detail with the next example. So over here, I have a data. Now let's understand what I'm trying to accomplish over u. So I have each of the kids over here. So the age is 12345. And I have these many chocolates with me, 1020304050. So what I'm trying to accomplish over the year is in these respective cells over here. I want the product of age, the number of chocolates, which means if I go ahead and say equal to this cell multiplied by this cell, and it returns the value as ten. I wanted to go ahead and do that for my entire grid. But let's see if I go ahead and copy this formula, what happens? So I'm going ahead and dragging this formula, okay? And then I'm dragging it down over you. And it gives me such huge numbers. And that in fact, some of the numbers on odd-even visible in my cell. Now why is it that happened? Now? That is because it has picked up the values as a relative reference. Because when I dragged my C6 into B7 in this particular cell over here, you will observe that the value has been picked up as D6 into C7, Okay? So from C6 to D6 and from B7 to C7. So what it has done is it has picked up relative column and rows and done the calculation. So this thing does not work for us. So now let's move to our second scenario where you have, you will try to use relative columns and absolute rows. So what I'm gonna do is I'm gonna say equal to select this particular cell. And I will press F4. Because I want to go ahead and make my rows as absolute. So once I press F4 first-time, both my columns and my Rosa frozen. If I press it the second time. This time only my rows are frozen. Okay? And then I will go ahead and say multiplied by, and I will say this particular value over you. And once again, I will press F4 twice. So when I press it twice, you will observe that only my rows get frozen, but my columns are still relative. And when I press Enter, let's go ahead and drag the formula and let's see what happens. Okay? So again, this thing does not really work for us because it this time only our rows are absolute, but our columns are constantly changing. Okay, now let's go to the third scenario over u. So in this particular scenario, what I'm gonna do is I'm gonna go ahead and make both my rows and columns absolute. So what I'm gonna do is I will say equal to this particular cell and I will press F4. So now my columns and rows both are absolute values. Then I will say multiplied by and then I will give this particular cell. And once again I will press F4. So in this particular formula, both my columns and my rose, absolute. Let's see if it works for us. So I'm going to hit enter, and now I will just drag the formula and I will drag it away you, okay? Once again, does not work because what it has done is once again, it has picked up the absolute values that is 110. And in every cell it has gone ahead and copied the same reference. So I have got the same values as my answer over. Yeah, let's try using absolute columns and relative rows. And let's see if that gives us the solution. So I'm gonna say is equal to this particular cell. And then I will press F4. I will press F4 one more time, and I will press F4 one more time. So when I press F4 three times, my columns become absolute. And then I will use multiplication. I will select this particular cell. And once again, I will press F4 thrice, so 123. So in this particular formula, my columns are absolute, whereas my rows are relative. I'm gonna go ahead and hit enter. And let's see what happens. Ok, so not working. And if I go ahead and drag it over here, once again, even this particular method does not really work for us. So what we need to do over here is because this is a grid, we need to go ahead and make use of the mixed referencing over you. So let's understand how do we accomplish that. So what I'm gonna do is I'm gonna go ahead and select this entire cell. And oh yeah, I'm gonna go ahead and type the formula. And I will give the formula that says equal to. I will select this particular cell over here, which is R7. Now in this particular cell, what I want is I only want my column to be absolute, okay? And not my rows. So I'm gonna go ahead and press F4 thrice. Okay? And now I will say multiplied by this particular cell over here. And when I use this particular cell, I want my rows to be absolute and my columns to be a relative. So what I'm gonna do is I'm going to press F4 twice. So you will observe that what I have done over here is I have given a mixed referencing. So in the chocolates section, my column is absolute, whereas in the age section, my rows are absolute. And I will press the Control key on my keyboard, and I will press enter. And now you will observe that in my entire grid, I have got the right solutions at 20 multiplied by two gives me 45, multiplied by 50 gives me 250. So any of the cells within this grid has done the calculations perfectly and given me the right values. So that was the objective of this course to give you a fair understanding of how relative and absolute referencing works when you're actually using formulas in Excel. There are times when you will have to use relative references. There are times when you will have to use absolute references. And you may have to go ahead and free certain columns and rows to get the right values in your formulas and as and when you will start using these formulas in your Excel work, you will start having a much more better understanding of this particular concept. And you will also be able to use it with much more ease with practice. So I hope you enjoyed this lecture and I shall see you in the next one.
10. Lesson 9 Basic Math Functions: Hey, welcome back. Now working in Excel, you will hear the words, formula and functions used frequently, and sometimes even interchangeably. They're very closely related, but not exactly the same, not technically. A formula is any expression that begins with an equal to sign. Whereas the function on the other hand, is a formula with a special name and a purpose. In most cases, functions have names that reflect gain tended to use. For example, the some function which returns the sum of given references. Now one of the finest feature of Excel is Excel has a lot of inbuilt mathematical functions that make our life pretty easy. They not only go head and automate the process for us, but they're also help us to skip the trouble of going ahead and writing down the formulas every now and then. I'll focus on this particular lecture will be on understanding some basic mathematical functions because that is something that becomes really handy to us when we are going ahead and creating spreadsheets. So let's know them one by one. So the first function is the sum function that we are going to look into. Now some basically we'll go ahead and give me a sum of all the references that I will be selecting over here. So for example, I want the total of all these values over here. Then all I need to do is I just need to go ahead and select this particular cell. Go to my home tab. And I have this particular button called as the auto cell. When I click on that, it gives me various options like some average count numbers, max-min, et cetera. So I'm gonna select the sum option over you. And you will observe that when I went ahead and did that, the SUM function automatically has gone ahead and first of all, written down the formula for me. And it has also gone ahead and selected a specific range which is just above it. And if I go ahead and press enter, it gives me the sum of all the values in my reference sheet. Now one of the advantages of using these automated functions in Excel is that let's say fatale, go ahead and add additional row over you. And I go ahead and add another value over here. So let's say I add a 100. You will observe that Excel has automatically picked up some new value has been added in my range over here. And it has gone ahead and added it to myself. Okay, let's go ahead and undo that. The next function that we're going to look into is the average function. And we all know what average does. The average function will give us the average of all the values in our reference over you. So once again, I will just go ahead and select this particular cell over. You. Go to my autosome option over here, and then I will select average. And just as in the sum function, it has gone ahead and selected the range, automatically picked up the formula. All I need to do now is just press Enter and it has gone ahead and return the average value. Now, what does the count function do? Now the count function will actually give me the count of the total values that I have in my range over you. Let's understand how to use it. I'm gonna go to this particular cell over here. And then once again I will click on autosome. And this time I will select this particular function called count numbers. And now the formula has appeared in this said, it says Count and then it has picked up the range. When I go ahead and hit Enter, it returns the value is 13, because when I go ahead and count all these numbers, in all, I have 13 numbers over u. Ok, so in all I have 13 values over here. Similarly, if I go ahead and click on My quick and footstool away, it also tells me that the count is 30. Now let's move on to the max and the min function. Now basically what a max function will do is it will return the maximum value in your selected range. So what I'm gonna do is once again, I will select this particular cell, go to my autosome. And oh yeah, I will select MAX and press enter. So now it tells me that in the entire range that I have selected, the maximum value in this particular range is 98. And exactly opposite to the max function we have the MIN function. Min function will basically go ahead and return the minimum value in our selected range. So I'm going to select this particular cell, go to my audio cell and select min and hit enter. So now it tells me that in the entire range that I have selected, the minimum value is 11. Okay? So these are the basic mathematical functions. Now over here, I'm also going to look into some statistical function, some basic statistical functions like the mean, median, and mode, okay, I just need to make the change over here. So this is mode. So let's understand first of all, what is mean. Now mean is nothing but an average can mean is another name for average. So basically if I have to go ahead and find the mean for this particular range over here, then all I need to do is I just need to go to this particular cell, click on autosome, and then click on average and hit enter. And that gives me the mean for my selected range. Now let's find the median. So what exactly is the median? The median is the number in an ordered set of data that is exactly in the middle. Now what do I mean by that? So let's go ahead and first select this entire range and let's copy it somewhere over u. Ok. And now what I'm gonna do is I'm just gonna go ahead and sort this data. So I will click on data and then I will go to this particular option that says salt. And then what I'm gonna do is I'm going to go ahead and sort this data from smallest to largest. And then I will click on, okay. When I did that, you will observe that my entire dataset is now being sorted. And the smallest value comes at the top and the largest value comes at the bottom. Now what I need to understand over you is in all there are 13 values over u. Ok, so even if I go to my quick info toolbar, it tells me that I have 13 values in this particular range. So I'll just go ahead and start counting. So this my first value, this is second, third, fourth, fifth, sixth, 25 is my seventh value. And below 25, I again have six values. Okay, so about 25 that are six values and below 25 there are six values, which in other words, means that 25 falls exactly in the middle of my ordered set of data, and therefore the median of my data range is 25. And if at all, I want to use XML to go ahead and find the median for my dataset. Then I will go ahead and type is equal to median. I have an open brackets and then I will select this range. And then I will close the brackets and hit enter. Okay, so that's how you go ahead and find the median in Microsoft Excel. Now, let's go ahead and find the mode. Now, what do I mean by mode? Now Mode is the most common number in the set of data. Now if at all I look at this particular data set that I have over you. Ok? The number 11 occurs most of the time in this entire data range, okay? Because the number 11 appears four times. So in a way, the mode of my data series is the number 11 because mod is nothing but the most common number in the set of data. How do I go ahead and find mode? It's pretty simple. I will say is equal to mode. I will go ahead and open brackets. I will select the range, and then I will close brackets and hit Enter. And it gives me my mode. So that's about the basic statistical functions. And how do you go ahead and implement them in Excel? Now one last function that I want you to go ahead and understand before we go ahead and wrap up this section. And it is called the round function. Now basically what around function does is it rounds up the values returned after your decimal places. Let's understand that with an example over here. So now what I'm gonna do is first of all, I'm going to go ahead and say equal to and then I will go ahead and sum these values. Okay, so right now I'm just going at it and typing the sum function manually. Okay, you can always use the autosome, but I just want to show you that, you know, you can actually go ahead and type functions in Excel. So it gives me this particular values, 6.6112196. So o, you are after the decimal places. I have four digits, 6112. And now let's say I want to go ahead and round up these values then how can I do that? So what I'm gonna do is I'm just gonna go ahead and delete this function over here. And I'm going to use the round function. So I will say is equal to round. And now inside I will go ahead and give my sum function. Okay? I will open the brackets. And then I will say, I want you to round up this particular value. And now it is asking me for how many digits, okay, up to how many digits do you want to go ahead and round up these values. So let me say I want to round up these values to do digits. And then I will go ahead and close the brackets and I will hit enter. Okay? And now you will observe that it has gone ahead and rounded up the values up to two decimal places. Now if at all, i go ahead and change this to 20 and hit Enter. It has just gone ahead and removed the decimal place completely out of the picture. That is about the basic mathematical functions in Excel. In fact, there are hundreds of different mathematical functions that you can actually go ahead and use an Excel, but these are the most commonly used. Now this being a very basic lecture in Excel, we only want to focus on these particular functions. But as in when you start learning advanced Excel, you will come across a lot of functions. And these functions can come really handy to get things done much faster. So I hope you enjoyed this lecture on basic numerical functions in Excel, and I shall see you in the next one.
11. Lesson 10 Some More Mathematical Functions: Hey, welcome back. So in the last lecture we got introduced to some of the basic mathematical functions. And in this particular lecture, we will look into some more mathematical functions. So I have some identical type of data, but the data above is for beverages. And I have selected five products. So where u, and this is a weak wise sales for each of the products. Now, I have this next data. And similarly under the snacks sections, I have five different products. And this is the week wise sales for each of the products. Now, I want to go ahead and have the sum over u. So it's very simple. I want, I can do is I can just select this particular cell. And then I can go to my home tab, click on autosome, click on Somalia, and it automatically picks up my range. And then I will hit enter. Now I can always go ahead and drag this formula, so I will just go ahead and drag it. So now it gives me the week wise total revenue former beverages. Now let's do it for snacks. Now, since I have used the sound formula, I can easily go ahead and select this entire range, copy the formula from Yale, and then just paste it over here. And it does really well. And similarly, I can go ahead and use my sum function over here so that I get the product twice some as well. So once again, I will go do autosome, click on some, and then I will hit Enter. And once again, I can go ahead and drag this formula over here. So it gives me the sum for each of the product, says when. Similarly I can just copy this and I can then pasted over you. Okay, so everything looks fine so far. I have the required data, I have the product wise data, and I have my sum product wise, as well as the vk wise. Now over here, what I have is a grand total and what I want over here is the sum of both these revenues. So my first methodology of going ahead and doing that would be, I would say is equal to this particular value. And then I will use the plus sign, and then I will say this particular cell over here. So c 11 plus C19, and I will hit Enter. Works fine. I get my grand total. Now let's look at the second method of doing that. So what I will do is I will say is equal to sum. I will open brackets and then I will just select this entire range. And then I will close it and hit enter. And it returns the value as 9,538.7. But if at all, i go ahead and select this particular cell, press Control and select this one. And I look at my quick info tool over here. It says the sum is 4,769.35. So this value is exactly double of it. Now why is that? Now this is because when I used my sum function and selected this entire range, along with the totals, all these respective values also got calculated, which means. These values got calculated twice, once in the total over here. And then second time when I went ahead and used my sum function. And that is the reason I got this value twice. So how can I fix that? So what I can do is I will type equal to some open brackets and it gives me the small tooltip information when it says number, comma number. So what I'm gonna do is I'm going to select this particular number where you first. Then I will go ahead and give a comma, and then I will select this particular number away or so d 11 comma D9. And then I will close the brackets and hit. And so now it has gone ahead and given me the right value. If I go ahead and select my totals over here and look at my quick info to lonelier, the value matches with what it shows over here. So it's 4,769.35. So it gives me the right value. So this is another alternative way of going ahead and using your some function with a comma wherein you can go ahead and select non-contiguous values. Now that is another way of going ahead and doing the summarization, and that is what we are going to learn now. It's called the subtotal. So I'm gonna go to this particular cell and I'm gonna type is equal to subtotal and open parenthesis. And immediately it popped up a small maneuver you, wherein I have different options like average count, count, a max-min. I'm interested in going ahead and doing this sum over here. So I will double-click on this, which is number nine. I will give a comma. And now it is asking me done number reference. So what I'm gonna do is I'm going to select this entire range. And then I will close this and hit enter. And let's see what happens. So it returns a value as 12,662.8. Whereas if at all i go ahead and select this particular cell and this particular cell, the sum is actually 6,331.42, which means this value is exactly the double of it. Now, why is that? Now that is because when I went ahead and use the subtotal function over a year, it picked up this particular total and this particular total. But because this entire range was selected, it also went ahead and picked up the values which were already fed in my total over year. So in fact, these values got calculated twice and that's why we got double the result. So how can I go ahead and fix that? It's pretty simple. All I need to do is I will go ahead and change the sum function to a subtotal function. So I will go ahead and delete this function from Europe. And the SUM function, I will replace it with subtotal. And then I will double-click on the number nine because I want the sum. And then I will give a comma and then I shall give a range. Or were you just observe that immediately when I did that, this particular value just dropped. And now if I go ahead and change even this two subtotal, let's see what happens. Double-click on nine. Give a comma. Then select this particular range, and then close the parentheses and hit enter. And now it returns the exact value. Because now what subtotal does is it skips all those values which are already fed in the subtotal over a year and the subtotal over year. Okay? But if at all I go ahead and add some value over here, let's say 100000. You will see that my subtotal will change because this particular subtotal over your will only include the values which are not yet included. This particular subtotal and this particular subtotal, if at all it is included in any of the subtotals above, it will go ahead and exclude those values. Let me just delete this. Okay, now I can go ahead and drag my subtotal formula from year to year. Ok. And similarly, I will go ahead and drag my subtotal formula from this cell to this cell. And now I can do is I will go ahead and drag the subtotal formula in this particular cell, and I get the perfect values. So the objective of this particular lesson wants to go ahead and understand a few different functions in exit, which we can go ahead and use in different ways in order to do some basic summarization and use some mathematical concepts within Excel. So I hope you enjoyed this quick lecture and I shall see you in the next one.
12. Lesson 11 Text Functions: Hey, welcome back. Not transforming and cleaning text is an essential part for any analyst, teacher or anybody who deals with Excel worksheets. And luckily, Excel provides a lot of quick and easy ways to transform text in a spreadsheet using native functions. So, so far in this lecture series we've been focusing on mathematical functions, but now we're going to focus on some text functions. And these functions will actually allow us to extract or find certain pieces of text, a string of text. So let's learn them one by one. So we, yeah, I have a dataset and in this particular list I have the first name of certain people and then in this particular list, I have their last name. Now the first function that we will learn in the text function CDS, is what we call as the concatenate function. And the basic job of the concatenate function is that it joins together toward more takes doings. Let's go ahead and learn it. So OEO, what I'm gonna do is I'm gonna say equal to concatenate. And then I will open brackets. And then when I do that in the tooltip menu, it tells me enter that text1. Okay, then it gives a comma and then says entered text two comma. So innovate using the concatenate function, I can go ahead and join any number of takes strings, but, oh yeah, I have to. So let's go ahead and do that. So my first tech string over here is this C7. And then I'm going to give a comma. And then it says gamete takes two. So I'm gonna go ahead and give this particular text over you. Close the brackets and then I will hit enter. Okay, now what has happened is it has gone ahead and joined both the names together. Okay. However, there is no space in between. So how can I go ahead and add the space? So what I will do is I will just go ahead and modify my concatenate function a little bit. So I will just go to this particular cell and then I will go ahead and delete this. And I will say concatenate, this is my first text. Then I will give a comma. Then what I will do is I will go ahead and type double-quotes, give us pace. And type double-quotes again. Now what it will do is the space between these two comma will be considered as a text and it will be concatenated within this particular function. And now I want to go ahead and add my last name or we'll, I will close this and I will hit enter. So now what it has done is it has gone ahead and club the first and the last name. And now it is giving me the name in the correct format. Now all I need to do is just come to this plus sign away and I can drag this formula and it does it for my entire dataset. Now let's go to the next function. Now this function is what we refer to as the length function. And the basic purpose of the length function is that it returns the length of the sublime text string. Now let's understand what do I mean by that? So if at all i go ahead and type this function. And then I will open brackets. And then I will go ahead and select this particular cell lawyer, close brackets, and then I will hit enter. So now it returns the value as 15. Now, that is because if I go ahead and count the characters in this particular cell, it is 15 characters. Okay, so CHR IST vi, that is seven characters, and then M0 or another seven characters. So that's 14 characters. And then there is a space in between. So it is also considering the space as a text character. So now it has gone ahead and return the value as 15. So that's the basic purpose of the length function. I will just go ahead and drag this. It gives me the length of all the characters that I had over you. Now let's go ahead and learn the left function. Now, what is the purpose of a left function? Now my left function actually returns a specified number of characters from the start of a sublime text string. So let's understand that with an example of you. So if I start typing my left function over here and I open brackets. So first it is asking me, where is my text. So I will say my text is over here. And then I give a comma. Then it is asking me number of characters. So in other words, what it is asking me is, how many characters from the left-hand side should it go ahead and return? So let's say I want only three characters and I will close the brackets and I will hit Enter. So in this particular cell, it has gone ahead and picked up the first three characters, that is c, h, and r, and return them after using that function. I can go ahead and drag this. And you can see that it has gone ahead and given me three characters from the satellite text strings, not just like the left function, the right function. We'll go ahead and return to me the specified number of characters from the end of this sublime text string, that is from right to left. Okay, so what I'm gonna do is, I will say is equal to right open brackets. Once again, it is asking me for the text. So I'm going to supply the text, give a comma. And then it asked me how many number of characters. So let's say I want four characters. And then I'm going to close the bracket and hit enter. And you will observe that it is now going at and returning for characters from the specified string, or were you? Okay, let's go ahead and drag this. Okay, cool. Now let's come to the mid function. Now the objective of the mid function is that it returns specific number of characters from the middle of the sublime text string. Let's see how it functions. So we, I'm going to type Is equal to mid open brackets. And then it is asking me for the text. So I will supply the text. Then I give a comma and then it is asking me, give me the starting number. So let's say I want to start from string number three. And then it is asking me how many characters. So once it starts from the third character, how many number of characters should it return? So let's say I want. Fight characters and then I will close this and I will hit enter. Okay? So now based upon my formula, what it has done is it has picked up the third character and then return five characters that followed that particular character with it. Okay, so it started from the third character and give me an output up to the seventh character. Okay, if I go ahead and drag it, it gives me a similar output for all the other characters in my dataset. Now we're going to learn a very interesting function. And this function is this sewage function. And the objective of the search function. Return the position of a supplied character on a text string from within a sublime text string. And this is not case sensitive. So let's understand how do we go ahead and use the search function. So what I'm gonna do is I'm going to type Is equal to search open brackets. And I want to go ahead and find out where exactly is this base in this particular text string over here. Because after the first name, that is a space and then it is followed by the last tape. I wanted to go ahead and find out where exactly is my space. So what I'm going to do is to give a space, as we have already learned. We give a double quote, give a space bar, and then give a double quote. Because anything between a double good will be considered as it takes, and we haven't given anything between the double quotes. So it will be considered as a space which is created by a spacebar. So now it's asking me within which particular string it should go ahead and find my value. So I will say the wheel, and then I will close the brackets and hit Enter. And when I did that, it returned the value as eight. So what did this telling me over here is in this particular text string space exist on the eighth character because Christy is a seven letter word and after that there is a space. So it does return the value as eight. If I go ahead and drag the formula, you will observe that for Dan Gomez, It will return the value as four because Dan is a three-letter word and space exists after the characters. So let's go ahead and check that out. Okay, so that's how mice, so each function works. So now let's go ahead and learn the application of this search function. And over here, what we will try to achieve is exactly opposite to that of a concatenate function. Concatenate went ahead and club firstname and lastname to give us a complete name or where you are, what we will do is we will use the search function to separate the first name and last name out of this sublime text string. So how can I do that? So what I'm gonna do is I'm going to use the left function over you. And I will open brackets. And then I will say, I want the left from this particular text because it is asking me for the text over here. I will give a comma, and then it is asking me that number of characters. So I'm going to say, I want the number of characters that are equivalent to the sewage. Okay, so I'm gonna go ahead and close the brackets and I will hit enter. Okay, so now it has gone ahead and written the firstname. If I go ahead and drag this formula, you will observe that for the entire data range away out of the sunlight takes string, it has gone ahead and only pulled the firstname for me. And now what we will try to do is we will try to extract the last name out of this data string over you. So for that, what I'm gonna do is I'm gonna go ahead and use the mid function. Okay? So I would say equal to mid. Then I will open brackets. Then it is asking me that text. So my text is available over u. Then it is asking me this starting number. Where should I go ahead and start finding the characters from? So I will say started from this pediculus dot number, okay? The value that is returned by my search function, then I will give a comma. And then it is asking me, give me the number of characters. So let's say, I will say give me the number of characters, which is equivalent to the length. And then I will close it. And then I will hit Enter. And just to let you know, this particular way of going ahead and clubbing one formula within another is called as nesting of formulas. In fact, you can also go ahead and type these formulas in one go. But in this particular lecture, what I've done is I have gone ahead and found individual outputs. And now what I'm doing is in the last formula, that is my mid formula. I'm going ahead and using these outputs get me the required result. So I'm gonna go ahead and hit enter. So now you will observe that it started from this pis bar. That is the value that was returned by this search function. Okay? And then found the number of characters which is equivalent to the length of the string. And then it has gone ahead and return the last name in this particular string as my output. If I go ahead and drag this particular formula, you will observe that it has gone ahead and return the last name of all the characters in this sub light string. Now the last, but not the least, over a year at the bottom is something that we refer to as a COUNTIF function. Now the objective of count function in numerical values is to give me the count of the total number of values that exist in my data range. And counting does exactly the same for my text values. So count they will actually return to me the total number of text values that exist within my data range. How do I use the COUNTIF function? Very simple. I just delete the function from you. I'll say County. I would open brackets and then it will ask me where is my data range? I will say my data ranges over you. I will go ahead and close this and hit Enter. So innovators telling me that I have 14 text values in my selected data. There are many more text functions that we can actually go ahead and use an exit. But these are the most basic ones and come in really handy while going ahead and creating spreadsheets. As you start learning advanced exit, you may get introduced to some mode at Eastern takes functions in future. So that's it about the text functions as if numb. I hope you enjoyed this lecture and I shall see you in the next one.
14. Lesson 13 Conditional Mathematics: Hey, welcome back. So in the previous lectures we got introduced to some of the mathematical functions. And then we also understood logical functions. When we go ahead and use the combination of both, then we come across something called as the conditional mathematics. And a focus on this particular lecture will be on the same. So what we're gonna do in this particular lecture is we are going to understand two functions in Excel, quantize the submit function. And then the next one is a Countif function, which are also referred to as the conditional mathematical functions. So let's understand how do we go ahead and use them and what exactly is their implementation in Excel? So first of all, let's look at the data that we have at our handover you so I have this employee IDs and then I have employee names. And then I have the data for the incentive that has been earned by each of the employees. And in the last column, I had the experience in total number of years. Now what I'm interested in is I want to go ahead and find the sum of the incentive offered by the employees whose experience is less than or equal to ten years. Okay, so that is my criteria. I will do only some if the experience is less than ten years. Okay? So over here is where the SUMIFS function will come handy. So my SUMIFS function goes something like this, is equal to and then I will type sum if, then I will open the brackets, then it is asking me what is the range. Okay. So my range is o V0 because my condition is based upon experience. So I'm going to select this entire range over here. And then what I'm gonna do is I will freeze this particular range. So I will press the F4 key on my keyboard. Ok, and then I will give a comma. Then it is asking me what is the criteria on the basis of which it should go ahead and do this sum. Now, let's look at the syntax that I go ahead and use away or because it is slightly different than what we have used in our previous lectures. Okay, it's a little bit of a pain, but that is how it works. So what I have to do is I have to open double quotes. And then I will say less than or equal to. Then I will close the double-quotes. Then I will go ahead and type and which is the shift and the number seven on your keyboard. And then I will go ahead and type the number ten. Okay? So this is how I go ahead and give the condition in my SUMIFS function. Then I will go ahead and give a comma. And now it is asking me what is the range that it should go ahead and do the sum for? I will say go ahead and do this sum for the incentive or end. Okay? And then once again, I can go ahead and press F4 so that I can freeze this particular range. And then I will close it and hit enter. So now we want it has done, is it has gone ahead and given me the sum of only those incentives on for which the experience is less than ten years. So now let's go ahead and confirm if this particular value is correct. So what I will do is I will only go ahead and select. Those values for which the experiences less than ten years. So I'll go ahead and press the Control key. And I'm selecting only those values over u. Ok? And now if I look at my quick info to lower your, it says the sum is 4121, which is the exact value that has been written in this particular cell over u. Ok, so that's how might some if function works. Let's play with another criteria. We'll now, I want to go ahead and sum the incentive on of only those employees for which the employee ID is greater than a 105. So for that, what I will do is once again, I will type my sum function. And I would say some if I have an open brackets, it's asking me for the range. This time my range is in the Employee ID, so I will just go ahead and select this, and then I will give a comma. Now let's give the criteria over here. And once again, I will use the same syntax. I'll use the double quotes. And then I will type the greater than sign. Close the double-quotes, give the and sign over here. And then I will say one naught five, okay? And then give a comma. And then it's asking me for the summer range. So my summer ranges over a year. And then I will close the brackets and hit Enter. Okay, so what it says is the sum of the incentive for employee IDs greater than a 105 is 67,891. Let's see if it is correct. So what I'm gonna do is I will just select this entire range over here. And if I go to my quick info tune, it says the sum of the value is 67,891. Okay, looks cool. Now just like the sum function, we also have the COUNTIF function. The only difference being the summary function gives us the sum. That is the COUNTIF function gives us the count based upon this certain criteria. So now let's go ahead and use the COUNTIF function. And let's find out the count of those employees for whom the experiences less than or equal to ten years. So I will say is equal to COUNTIF open brackets. And now it is asking me for the range. So I will go ahead and give this particular range because my years of experience is in this range over here. I will go ahead and press F4 to freeze the range. I will give a comma. And now it is asking me what is the criteria. So once again, I will use double-quotes. And I will say less than or equal to. Use double quotes again, and then use the and sign. And then I will say ten. And then I will close the brackets and hit Enter. So it says that in all I have six employees whose years of experience is less than ten years. Let's see if that is true. So I have this one employee 23456, so it is returning me the perfect value. Now let us go ahead and find out those employees whose employee ID is greater than a 105. So what I'm gonna do is I will just go ahead and say COUNTIF. And then I will open the brackets. And now I will give this particular range because my years of experiences over you haven't press F4 to freeze the range. And then I will give a comma. And now it is asking me for the criteria. So the criteria is once again greater than a 105. So I'm gonna go ahead and use the double-quotes. Say greater than, get double-quotes again. Give the and sign and type 1-0 five, and then close the brackets and hit Enter. So now it says that the total number of employees whose employee ID is greater than a 105 is nine. So let's go ahead and check that out. And if I go to my quick info tool, it says the count is nine. So my answer is perfect. So this is how we go ahead and use the combination of mathematical and conditional functions to go ahead and create something that we referred to as the conditional mathematics. And this allows us to build some really strong conditions and a lot of different conditions to add and pull specific numbers or specific data out of a large cumbersome data. So this can be a very handy tool for going ahead and doing data mining, especially when we are dealing with large volumes of data. So I hope you enjoyed this lecture and I shall see you in the next one.
15. Lesson 14 Data Sorting: Hey, welcome back. So our focus in this particular lecture will be on sorting the data. Now sorting data in MS Excel rearranges the rows based on contents of a particular column. Now you may want to sort the table to put names in alphabetical order. Or maybe you may want to sort data by amount from smallest to largest or largest to smallest. So let us understand the concept of sorting through this example over here. Now what I have over here is a small database wherein I have the firstname and lastname of my employees. I have the region for which they work. I have the department salary there month of joining and their year of joining. And now I want to go ahead and perform data sorting on this particular database. Now even before I jump into data sorting, I want you to know that there are certain prerequisites before you go ahead and sort any data. Now the first thing is that the data should be arranged in a tabular format. In other words, it should look like a table. And our data where you're satisfies that condition, it looks like a table, isn't it? Then it should have consistent data types. By dad, What I mean is under every heading that I have over your data type should be consistent. For example, if it is text, then throughout it should be text. If it is numerical, Then it should be numerical throughout. If it is alphanumeric, then let it be alphanumeric throughout. It should not be a mix-and-match. Third condition is that there should be no blank rows or columns in my data. Because whenever I run assault on my table, wherever it finds a blank row or a blank column, it will immediately go ahead and hard the sort procedure whenever it finds that blank row or blank column. So in case I want to go ahead and run it on an entire database, I just have to make sure that there are no blind crews. Oh, blank columns in my database. One mode prerequisite is that your data should have headers. For example, over here I have firstName, lastName, region, department, etc. Now this is not compulsory. But then if you have it in place, sorting of data becomes very easy. And when we proceed through the lecture, you will understand why. Now that our database satisfies all these prerequisites, let's go ahead and start sorting this data. So let's say first I want to go ahead and sort this data on the basis of department. So what I will do is I will select any cell within this particular database. I will click on the Data tab over here. And then I will go to this particular option called sort. And when I did that, it popped up a sort menu. We'll now over here, it gives me an option of sawed by. And when I click on the down arrow over here, it gives me the list of all my headers over here. So you remember why I told you that having headers is very convenient when you're going ahead and sorting data. And just in case it does not recognize the headers that you have within your database, you can always go ahead and put a check mark on this particular option that says my data has headers and it will recognize the headers in your database. Now I want to go ahead and sort this data on the basis of department. So I will go ahead and click on Department. And I want to go ahead and sort this data from a to Z. Ok, which means anything that starts with a comes first, followed by b, c, d, and up to the letter Z. And then I will click on OK. And you will also have that the moment I did that, all my data has been sorted based upon the department and accordingly, the other columns have also readjusted them. So now this is something that we referred to as a single level of sorting. And you will observe that there are a lot of ties over here, which means that lots of people belong to the same department. Now what if I want to go ahead and add an additional layer of sorting to this, of course, that's possible. So lets say if at all there is a tie on the department, the next level that I want to add is sort on the basis of region. And even if there is a tie on region, then I want to go ahead and sort on the basis of salary. So how do I do that? Simple, I will just click on any of the cells if within my database, and then I will click on sought. You will observe that the initial sort that we put in place is already there. What I'm gonna do is I'm gonna go ahead and add another level to it. And what I'm gonna do is I'm going to say if at all there is a tie on the department, then sort on the basis of region. Okay. And how do I want to sort it in an a2 zz0 fashion? In case I want to reverse it, I can always do that, but I'll keep it to a to Z. And then in case if there is a tie on the region as well, then what I want Excel to do is sort on the basis of salary. And salary, I wanted to be sorted from largest to smallest. And then I will click on OK. And when I did that, you will observe that initial sorting happened on the basis of department. Then when there was a tie on the department, then the second level of sorting was applied wherein the sorting happened on the basis of region starting from a to Z. And then if there was a tie on the region, then sorting happen on the basis of salary, where the largest salary came first, followed by the lower salary. So in this way, I can actually go ahead and do my sorting at a single level or at multiple levels. In case I want to go ahead and D to a particular level within my sorting. I can always do that by modifying my sort table. I can go to this particular sort option over here. And let's say I want to delete this particular sorting by region. So what I will do is I will just select it and then click on Delete level and then click on ok. So you will observe that now the sorting is only happening on the basis of department and wherever there is a time, the second level of sorting is happening as per the salaries. Now, just so you know, that sorting is not just limited to text and numbers. If I go to this particular option over here which says cell values, you will realize that I can even go ahead and do the sorting on the basis of cell color, font color, and some conditional formatting icons as well. So that's a pretty powerful stuff of not just being restricted to text and numbers. So I hope you enjoyed this quick lecture on data sorting, and I shall see you in the next one.
16. Lesson 15 Data Filtering: Hey, welcome back. So now we're gonna go ahead and look into one of the most coolest feature in Excel, which is called as the data filters. Now filtering data in a spreadsheet means to allow only certain data to be displayed. Now this function is very useful when you want to focus only on a specific information in a large dataset or table. Now, filtering does not remove or modify your data, but it only changes what appears on your screen depending upon your filtering criterias. So let's understand that with an example on the screen over, you know, before we go ahead and jump into data filtering, there are certain prerequisites that we have to look into. Now these prerequisites are similar to that of data sorting, wherein your data has to be in a tabular format. It should have consistent data types and there should be no blank rows and columns within your data. Something that we have already learned in the previous lecture. So I'm not gonna go much into details of that straightaway. Let's go ahead and jump into data filtering now over yeah, I have asserted dataset. Now, this particular dataset has a date, it has the name of the product, it has the unit sole. Let me just correct that. Then it has the region, the commission earn, and then it has the name of this sales manager. And I want to go ahead and run data filters on this particular data now. So in order to go ahead and run data filters, what I'm gonna do is I will click anywhere on this particular dataset. And then I will click on this tab called data. And then I will click on filters. The moment I did that, you will observe that by headers now have this particular validation button, an arrow that is pointing downwards. If I click on that, that various menus over you. Now let's go ahead and first run a filter on the basis of sales manager, wherein I only want to go ahead and look at the sales done by sales manager Bob and Kevin. Then in that case, what I would do is I will just go ahead and click on this drop-down over here. And over here I have a lot of options, so I will go ahead and remove the checkmark on select all. And I will just go ahead and put a checkmark on Bob and Kevin and click on OK. The moment I did that, you can see that my data is now filtered and it is only displaying the results for sales done by sales manager Bob and Kevin. And whenever I run a filter on a particular column, you will observe that the drop-down menu over Yo, which looks like a validation arrow, turns into a filter over you. And when I go ahead and remove the filters, you can see that once again gets converted into a dropdown. So that's one of the way you would know that there is a filter that is running on a particular column or not. Now just like sorting, we can go ahead and apply multiple levels of filtering on our database. So let's say for example, I only want to go ahead and look at a stop seals done by Mark and Kevin. Then in that case, what I would do is I would go ahead and run a filter on desktop foods. So I will go ahead and uncheck this Select All button. Then I will put a checkmark on desktop. And now I will go to my drop-down over here. I will go ahead and remove this check mark from Select All. And I will say Kevin and mock. So now it will only display to meet the reports of desktop sales done by Mark and Kevin. So in a way, what I can do is I can go ahead and apply multiple levels of filters. Just like what we did in terms of sorting wedding BE applied multiple levels of sorting at any given point of time. If I want to go ahead and remove all the filters from my database, I can just go ahead and click anywhere on the database and then click on the filter button over here. Or I can go ahead and click on the clear button over here. Anyone is fine. So let's go ahead and click on clear. And you will see that all the filters have been removed. Now, let's look at some numerical values over you. If I'm looking at the commission earned over here, and when I go ahead and click on this drop-down, it gives me a lot of options over your legs sought from smallest to largest, from largest to smallest. So using filters, you can actually even go ahead and sort your data. Let me just go ahead and try sorting this from largest to smallest. And when I do that, you will observe that it has gone ahead and done this sorting for me. Once again, let's click on this particular drop-down over here. And when I come to this particular option that sees number filter, you will observe that a menu pops up and it gives me a lot of options like equals does not equal, greater than, less than. So integrate using this particular menu. We can go ahead and apply some kind of conditions over here. And based upon those conditions, our data will be filtered accordingly. Let's say for example, we I'm only interested in looking at those commission earned values where the value is greater than 12 thousand. So I will go ahead and click on this particular option over your bed and it says greater than. And when I do that, it opens up the custom Auto Filter window. And over here you will also observe that I can also give it and an OR condition. But right now I'm only going ahead and giving one condition. So I will just stick to it. And I will say the value is greater than 12 thousand. Only then display the values in my filter. I will go ahead and click on OK. And you will observe that all the other values, which are less than 12 thousand has vanished from this particular report. Let's once again go ahead and clear all the filters. So I will go ahead and click on clear filters. Now let's go ahead and look at sorting that text values within our database. So let's go ahead and click on product over here. And when I click on it, it gives me the option which is similar to the sort function, wherein I can sort from a to Z and from Z to a. If I click on any one of these options, you will see that my data is sorted accordingly. And just like the sort function, it also gives me a lot of custom sort options as well. I can go ahead and click on the custom Sort button and it pops up this sort menu that we saw in the previous lecture. And then we can use this for setting our filtering criterias. I'll go ahead and cancel this. Now one more thing that I want you to observe very closely over you is when I go ahead and run the filters on my dates, you will see that there is a beautiful chronology or were you when I see the year first, followed by the month, and then the dates. So in case if I have a large cumbersome database which is running into a hundreds and thousands of rows. Then I can go ahead and do some kind of a data mining. And based upon my requirement, I can go ahead and filter the data for a specific period. And similarly, if at all i go ahead and click on date filters, I have a lot of options wherein I can filter the data based upon the timeframe, like next month, this month, next quarter, this quarter, etc. Okay. I'll go ahead and click on cancel over here. Now though, we have already seen this in the lecture, but I would still like to reemphasize on the fact that if at all you ever want to go ahead and remove all the filters from your database, all you need to do is click on any of the buttons on your database over here. And then go ahead and click on the filter button over here, and the filters will vanish. So that's how we can go ahead and make use of the data filters. But going ahead and filtering the data and only looking at the specific data that we are really interested in looking at. So that's about data filters. In a nutshell, I hope you enjoyed this lecture and I shall see you in the next one.
17. Lesson 16 Using Data Tables: Hey, welcome back. So in this particular lecture, our focus will be on understanding data tables. And data tables is one of the most efficient way of going ahead and storing data in your Excel worksheets. Definitely because it has lots of benefits attached to it. And some of the benefits we will definitely look into in this particular lecture itself. Now, let's understand the process of going ahead and first converting your data into a data table. So I have this particular dataset over here, which is a kind of a sales report, maybe from a restaurant or a hotel, varying they're selling certain items. And these items fall into different categories. And these categories are further divided into groups of snacks and drinks. The sales is happening across various cities. And then we have the sales value, the units, the price, etc. Now, I want to go ahead and convert this data into a data table and the Java's pretty easy. All I need to go ahead and do is I just need to go ahead and click on any of the cells within my dataset. Go to the Home tab. And then I can go ahead and click on this particular button that's his format as data table. And I can select any data table style of my choice. So let's go ahead and select this one. And the moment I did that, it has also gone ahead and selected my data range over you. Now once again, all the criteria as that hold good for data filtering and data sorting hold good for table as well. One, your data should be consistent. Second, it should be in a tabular format. Third, there should not be any blank rows and columns within your dataset. For it's always a good practice to go ahead and give your data some headers. Okay? My data, which is first of all in tabular format, it does not have any blank rows and columns within it. It is in a consistent format. And then it also has some headers over you. And you will observe that when I went ahead and selected my data, it has already identified that my data has headers and there is a check mark on this. In case if there is no checkmark on that, you can go ahead and manually put a checkmark over there. Or else when Excel will go ahead and convert your data into a data table, by default, it will give it some headers like column one, column two, column three, etc. My data already has headers. So I will go ahead and click on OK. And the moment I did that, you will observe that my data is immediately converted into a data table. Now it's always a good practice to go ahead and give you a data table, a name. So what I'm going to go ahead and do is I will just click on this particular table over here. And it says that the name of my table is table for. I will go ahead and name it something like sales data. And I will hit Enter. So now the name of my table is sales data. Now what is the advantage of going ahead and giving your data table and name? Now let's say for some reason I moved to any other tab. And now I'm interested in going back to my table again. I can go ahead and click on the name box over you. And when I click on the drop-down over here, it shows the name of my table over here. So if I click on that, it will immediately rout me back to my data table over u. So that is the advantage of going ahead and giving you a data table, a name. So it's always a good practice that when you go ahead and create a data table, always give your data table unnamed. Now let's look at some other advantages of going ahead and using data tables. You will observe that the moment I converted my data into tables, my data already has auto filters in place. Okay, so I can use all the features of automatic order filters when my data is already converted into a table format. Another advantage of going ahead and converting your data into a table is that you have this particular alternate row bands over u. Now this was extremely difficult to go ahead and create in older versions of Excel. But now, after 2016 and onwards, the moment to go ahead and create a data table, alternate bands are generated automatically. Another advantage is you are not restricted to this particular type of table style only. You will observe that whenever you are within your table, there is a new tab that gets generated at the top, which is called as a table tools design tab. Okay? When you move outside the table, you will observe that that tab vanishes. And every time you come back on your table, you will see that the table tools design tab is visible, which means your cursor is right now pointing to a cell within a table. I can go ahead and click on the Design tab and then I can select the design of my choice. Currently I'm using a particular design. I can always go ahead and change the design and select the design of my choice as an when I want. So let's say I go ahead and select this particular design. And you will see that the layout and the style of my tables have immediately changed. Now let's go ahead and look at a few more advantages of going ahead and using a table. Now let's say I have the sales units and then I have this sales price. Let's say for some reason, I want to go ahead and add one more column to this particular table. So let me just go ahead and add one more column, which is profit per unit. And you will observe that the moment I added a new column table has automatically adjusted itself and it had gone ahead and added this particular column as a part of the table. Now let's say I will go ahead and put some formula or your, wherein I say that profit per unit is, let's say 10% of my sale value. And you will observe that when I went ahead and gave a cell reference, it is adopting a new style of cell reference over you. This type of cell reference is quite different from the kind of cell references that we have used in the previous lecture. This type of cell reference is also referred to as the structured table referencing technique. Okay? And if at all i go ahead and hit Enter, you will observe that not only it has done the calculation for this particular cell, but it has also gone ahead and done the calculation for my entire table. So whenever you go ahead and add an additional column to your database, you will observe that the table will automatically adjust itself and it will incorporate that particular column as a part of the table. If you do any kind of a calculation or give any cell referencing, it will go ahead and apply those changes for the entire table itself. Let's go to the bottom of my table over you. And you will observe that one more advantage of using a table is that no matter where I go on my data over u, I can always look at the headers for my data. So I will never be confused that okay, where is my store ID, where is my item code? Where is my category code? Because even if I scroll to the bottom of the data at any given point of time, the headers are always visible to me. Now let's go ahead and try something is now over here I have 300 records. Let's say if at all i go ahead and try to add the 301st record over here, and I will hit Enter. You will observe that the moment I hit Enter, it has also gone ahead and incorporated this new row as a part of my table. I can go ahead and copy something over u. Ok, or I can go ahead and add a new record altogether. But you will observe that as and when I keep on adding extra rows, the rows automatically keep on getting added to my table and it starts becoming a part of my data table. That's one more advantage of going ahead and using a data table. There are many other benefits of going ahead and using data in a data table format, which gradually you will come to know as and when you start learning advanced lectures in Excel. So definitely data table is by far the best way to store data today. And I would highly recommend that whenever you want to go ahead and save your data in a tabular format, please go ahead and start turning your tabular data into official Excel tables. So I hope you enjoyed this lecture on data tables, and I shall see you in the next.
18. Lesson 17 Bar Chart: Hey, welcome back. Now in this particular section of the lecture series, our focus will be on understanding the basic charts of Excel. Excel charts allow spreadsheet administrators to create visualizations of datasets by highlighting a set of data within an Excel spreadsheet and then feeding it into a chart tool. Users can actually create various types of charts in which the data is displayed in a graphical way. So let's get on and start learning these charts one-by-one. So the first chart that we are going to learn in this particular lecture series is what we call as the bar and column chart. Currently what you see on your screen is what we referred to as a bar chart. The only difference between a bar chart and a column chart is that the bar chart is oriented horizontally. There is a column chart is oriented vertically. When you go ahead and create them, you'll understand a few differences between the same. So let's start constructing these charts. So we will start with the bar chart first. So over here, I have this raw data and the same has been converted into a bar chart. And what happens is when it is converted into a bar chart, every element within my raw chart is represented by a bot, and the length of the bar corresponds to the numerical value assigned to it. So let's start constructing one Simvastatin. We jumped into my raw data over here. I will select my raw data. I will click on Insert. And oh yeah, I have the options of going ahead and creating the various charts that Excel offers me. I can also go ahead and click on Recommended Charts because this is a feature that is available from Excel 2013 and onwards. Then Excel itself will tell us which is the most suitable chart for our datatype. But currently I'm not looking at it, so I'm going to close this. And I will manually go to this particular option, which is inset column or bar chart. And I will click on it. Once I click on it, you will see that it gives me four options. Either I can choose between a 2D column or a 3D column chart, or I can choose between a 2D bar and a 3D bar chart. So currently I'm interested in creating a bar chart. So I can either choose a duty bar out a 3D bar. Let's go ahead with a 3D bar chart this time. So I will click on it and you will observe that it is so simple. The moment I clicked on it, my bar chart is almost ready. Now the only thing that I need to do is probably just customize it a little bit so that it looks a little professional and suits my need to do that. That is this plus button over here, just next to my chart. Now when I click on it, it opens up a menu or less chart elements. If you're using an older version of Excel, then all you need to do is just click on the chart. Go to the Design tab over here. And you will see that chart elements away your as well. And now this particular menu is exactly what you see. Why you. So now since I'm using Excel 2016, Let me just use this particular menu over you. So away. Oh, my first option is axis. If I go ahead and uncheck it, you will see that my vertical and horizontal axis has vanished. When I put a check mark on it, they appear once again. If I click on the arrow over here, it gives me the option to check and uncheck any one of them. Let's come down to access Titan. If I go ahead and put a checkmark on the axis title, you will see that the axis title appears at the bottom of my chart. Again, if I go ahead and click on the arrow over, it gives me some more options. I'm not interested in the axis titles right now, so I'm just gonna go ahead and uncheck it. The third option over your chart title, based upon which I can decide if I really want a title for my chart or I don't really want it. So I can go ahead and check or uncheck it. And then if I click on the arrow over here, it tells me vet exactly. I want my chart title do appear. Do I wanted above the chart? Do I wanted in the center? And there are some more options, then you can just put it on the left, right hand side or maybe at the bottom of your screen. Now, the most important thing away your data labels. If I go ahead and put a checkmark on my data labels, you will see that the numerical values that corresponds to the length of the bar just appears next to the bar chart. Now these values are very handy because when you actually looking at the bar graph, just looking at the BOD will not really tell you the exact numerical value it corresponds to. So having a data label in a chart is a very good practice. If I want to go ahead and hide the data labels, I can always go ahead and uncheck it. But as of now, I'm going to keep a check mark on that. Then there are some more options, late data tables, and then grid lines. Again, if I go to Gridlines, I have options wherein I can have major horizontal grid lines, I can have vertical grid lines. I can have minor vertical grid lines. I can have minor horizontal grid lines, depending upon the choice and the type of data that you have at your hand, you can decide whether you need them or you don't need them. As of now. I don't want any grid line, so I'm going to go ahead and hide it. The last option that you see over here is called as the legend. When I click on the legend, you will see that the legend appears on my chart over here. Okay, and since this is the C it's chart, it has gone ahead and picked up the legend As Cs. Again, there are a few more options. When I click on the right arrow next to legend, wherein I can decide whether I won my legend to be, do I want it at the top left? Bottom right, anywhere. I'm not interested in the legend as of now, so I'm gonna go ahead and hide it. One more option that I want you to really look at closely is effect all. I just want to go ahead and use some ready-made designs. Excel also gives you that as an option. So for that, what I can do is I can just click on the chart type that I have over you. I can come down to design. And then under designs, I have some various options within. I can go ahead and change the colors of my chart. Let's see what happens if I click on this particular one, or probably this one. You will observe that it picks up a different color combination altogether. I'm not interested in changing the color combination as of now, so I am going to leave it as it is. And then over here, there are some readymade chart styles that are available. Let's see what happens when I click on any one of them. When I click on any one of the chart style, my chart automatically gets modified and takes up a different shape and form. I like this one, so I'm going to select it. And let's see what happens. Now the moment I clicked on it, you will observe that my chart has picked up that particular style and it looks much more professional law. Let's go ahead and do some small modifications over you. I want my data to be sorted in this particular chart to you. So what I'm gonna do is I'm going to select this particular data video. I will go to my data option, click on sought, and I'm going to sort this data based upon seals. And then let me just go ahead and select this option as smallest to largest, and click on OK. You see the moment by data got sorted immediately. My chart is also sorted from the largest to the smallest. Now if at all I want to go ahead and customize any elements within my chart. I can always do that by selecting that particular element and right-clicking on it. You will observe that a menu will pop up. You can go ahead and explore this menu later on. And you can find out how you can go ahead and do the customization of your chart. Few things that I want to show you in this particular lecture itself is formatting the data series, wherein this particular option called as the gap width comes very handy while creating bar charts and column charts. Now if I go ahead and say increase the gap width over here, you will see that the gap between my Bars increases. So based upon my requirement, I can go ahead and increase and decrease the gap between my bar charts. If at all, I want to go ahead and format an element. Let's say something like a data label. I can highlight it, right click on it. And then when I click on format data labels, you will observe that the window pops up from the right-hand side, which will give me various options of going ahead and formatting might data labels. So depending upon your need and requirement, you can go ahead and select any element within the chart, right-click on it, and then it gives you various options. You can go ahead and explore them. And based upon your requirement, you can go ahead and customize your chart. Now let us go ahead and have a look at the column chart. So now let's quickly go ahead and create our column chart. So I have the same raw data in my data sheet over here. So to construct a column chart, I'm going to select this entire data set. Click on Insert. And then I'm going to click on this particular option that says inset column or bar chart. And then I'm going to select this particular option that's is 3D clustered column. And the moment I do that, my column chart is almost ready. Now, all I need to do is go ahead and format it to give it a nice professional look and feel as i c over a year. So how do I do that? So first of all, what I will do is I will just go ahead and click on the Chart Elements. And I do not want the vertical axis, so I'm going to remove the vertical axis from my chart. I will uncheck this particular option. I also don't want the grid lines over here. So I'm going to uncheck the grid lines as well. So I will select the chart and then uncheck the grid lines. And now I'm going to go ahead and use a ready-made design for my chart. So I will select this. Click on design. And I'm going to select this particular design over you. The moment I do that, you see that the design has been applied and my chart is almost ready. I'm just going to increase the size of this chart so that the data labels are visible very clearly. Now the only thing that I need to go ahead and change over here is the chart title. So I can give a chart by del off my choice. So we yeah, I can see that this is possibly a sales report. And with that, my column chart is ready. Now you must have realized that there is hardly any difference between a bar chart and a column chart. Now, bar charts are typically used to compare several categories of data. And bar charts are ideal for visualizing the distribution proportion of data items when there are more than three categories. But as column charts are typically used to compare several items in a specific range of values. Column charts effectively compare a single set of data points, but it shines when comparing multiple series. As in our case, we had multiple items and yes, CS value spread across three months. And because Excel uses a different color for each data series, it's easy to see how single CDS changes over time. Now, when to use a bar chart and when to use a column chart depends upon the type of data and the user preference. Now sometimes it is even worth to go ahead and create both the charts and then compare the results. However, you need to understand that bar charts do tend to display and compare large number of series better than other chart types. So that was about bar charts and column charts. In our next lecture series, we'll learn some new charts. I hope you enjoyed this one and I will see you there.
19. Lesson 18 Line Chart: Hey, welcome back. Now the second type of chart that we will learn in this particular lecture series is what we referred to as the line chart. And what you see on your screen right now is nothing but a line job, not the line chart is one of the most frequently used chart types and it is typically used to show trends over a period of time. The vertical axis will always displayed numerical values. And the horizontal axis we're display time or some other category. Let's quickly jump into it and let's learn more about it. So what I have over Europe is a data for a company called ABC furnitures. And I have the total revenue generated by selling two items, table and chairs. And this particular data is spread across the entire euro starting from January to December. And what I have to do is I just have to go ahead and create a line graph that does a kind of a nektonic comparison of the revenue of these two items. So I have the same raw data on my raw data sheet over you. Let's quickly jump into creating the Line Chart and let's understand the different elements of a line chart. So what I'm gonna do is I'm going to select this entire data. Over here. I will go to the Insert option. And over here I have this particular option where it says Insert Line or area chart. I'm going to click on that. And oh yeah, I have various options. So I have this particular option, callers Line with markers. But before we go into line with markers, let me just select the first option over here, the line charts. Let's understand it with the basics posts. So I'm gonna select the line chart over here. And you observe that the moment I selected line chart, my line chart is almost ready. So let me just place it properly so that we can look at it in more detail. Okay, so now let us look at all the elements within this line chart. So what it has done is it has gone ahead and plotted this particular revenue of both these items, where the blue line represents the revenue for my tables and the orange line represents the revenue for my cheers. Now this is very much evident in the legends that we see or what you. Now let us go ahead and look at the various elements of a line chart. Now over here, I have this y-axis and I have this horizontal x axis. Let us go ahead and do some formatting with it initially. So if at all, i go ahead and select my vertical axis over here and right-click on it, and click on format axis. A window will pop up from the right-hand side. That gives me an option of formatting my axis. Now, over here, if I observe the values of my revenue, I really do not have any value which is close to 25 thousand, which is the highest or the maximum value in my axis options. So what I can do is I can go ahead and reduce this value and maybe bring it down to 22 thousand. That goes ahead and gives a better look to my chart. Let me just close this over here. And now what I will do is I will go ahead and select a specific design for my chart. So I will just click on this particular option. That's his design. And I will select a ready-made, customizable design away you now the chart looks much better as compared to the previous one. Now let us go ahead and look at some other features of the lecture. Now for a better understanding, let me just go ahead and add some data labels to it. Let's go to Chart Elements and add some data labels. So when I went ahead and enable the data labels, all the data labels are now visible on my line chart over here. Let us go ahead and add some markers to it now. So what I'm gonna do is I'm gonna select the line chart over here and right-click on it and then go to format data series. Now if I go ahead and click on this option that says Fill and line. But when I have this option called as the mako, when I click on it, it gives me this particular option over here. It says marker options. Now over here, I can select the various types of markers that are available for my line chart. I will click on this option that says build in. And for my orange line, which is the line chart for my chairs, I'm gonna go ahead and select this particular marker. And then I will come back to my blue charts. And over here once again, I will click on building. And I will select this particular type of markers for my blue line over here. And then I will click on, okay. So with that, you will observe that the markers have been created and the same type of markers are also visible in my legend or we'll, One more thing that I want you to observe a warrior is if I click on this horizontal axis over Europe and right-click on it and click on format axis. And I come down to this option which says access position. Let's see what happens when I click on this particular option that says On Tick marks. When I click on it, you will observe that my indentation just moved such that it is directly on the tick marks. Or we'll, however, when I click on this particular option that is between tick marks, it moves in between that tick marks that are dead on my horizontal axis. I usually like to use the On Tick marks option. So I will go ahead and select that. And then I will close it. One more thing that I want to highlight for you is if you observe these two charts over you, you will see that orange line is in front of my blue line. However, if at all, I wish to go ahead and change the order, can I really do that? Yes. What I can do is I can just go ahead and click on the chart type. Go to select data. And, or you might tables data is in the first position, whereas the chess data is on the second position. What I will do is I will just select chairs and push it in the first place. And I will go ahead and push the tables down on the second position. When I click on OK, you will observe that the blue line is now in the front, whereas my orange line has moved behind my blue line. So you can actually go ahead and change the order or the sequence in which the lines are drawn on your line chart. Now, all I need to do is go ahead and give my chart a title. So I'm just going to see revenue comparison chart. And click anywhere outside the chart. And with that, my entire line chart is now ready. So you must have understood by now that creating a line chart is just a matter of a few clicks. And it is typically used to show trends over a period of time. Also, you must have realized by now that line chart is one of the perfect solution for showing multiple series of closely-related series of data. And since line graphs are very lightweight, as opposed to more complex chart types, they're the best when you want to display your data with a minimum stick look. So that's it about line charts for now. So I hope you enjoyed this lecture. And in the next lecture, we learn some new job.
20. Lesson 19 Pie Chart: Hey, welcome back. So another frequently used chart in Excel is an old pie chart. What you see on your screen is what we referred to as a pie chart. And this is what we referred to as a doughnut chart. These are the two types of charts that we will learn in this particular lecture, or were you? So a pie chart Eugenia represents a distribution or proportion of each data item. What a total value. Now, if I look at this particular data over here, and if you have gone through the lecture on bar charts, you will observe that this is the same dataset that I'm using a wheel. But the difference between a bar chart and a pie chart is the pie chart will represent each of these values as a proportion of the total value. So let us go ahead and understand how do we go ahead and construct a pie chart. And we will look into the other elements of it as we progress through the lecture. So I am going to use my same raw data. So let's quickly move on to our raw data sheet. And creating a pie chart is pretty simple. All I'm gonna do is I'm going to select this entire dataset over here. I will go to the Insert option. And oh yeah, I see this particular option that says insert by our donut chart. I'm going to select either a duty pie chart or a 3D pie chart. When I'm going ahead and creating individual charts, I prefer 3D charts. However, Just a tip for you whenever you're creating dashboards, tried to avoid 3D charts as far as possible. As for learning purpose. Let's go ahead with a 3D chart over here. So the moment I go ahead and click on it, you will see that my 3D pie chart has already created. Now let me just quickly go ahead and add data labels to it. And you will see that the data labels have been added. Now one good practice whenever you create a pie chart is to go ahead and give percentages to your data labels, as it will give you an idea of the exact proportion of each of the item out of that entire value. So let's go ahead and modify our data labels a little bit. So I'm going to select the data labels. I'm gonna right-click on it and click on format data labels. And over here I see this particular window pops up that says format data labels. So I'm going to click on percentages over you. And I don't want the values at this point of time. I am only interested in looking at the percentages, so I will uncheck the values and I will close this windows. So now I have the percentage for each of the proportions within this pie chart. Let me just go ahead and use a ready-made design. So I will just select the pie chart, go to my design option. And I'm going to select this particular design. And I will just go ahead and change the chart title. So I would say this is for ABC, for niches. Now let's go ahead and look at certain other elements of the pie chart. Now over you really cannot see my data labels, so let me just format my data labels a little bit. I'm going to select the data labels. I'm gonna go to my home tab and I will select the font color is black. So now my data labels are visible. Now let us go ahead and look at some of the formatting tips for up by China. So what I'm gonna do is I'm going to right-click on the pie chart. And I'm going to click on this option that says format data series. And when I click on it, a window pops up from the right-hand side of the screen and it says format, data, series, and overuse. I'm going to focus more on the two aspects which are very important in terms of pi chart. What is the angle of the first light? And second is the explosion. Let's see what happens when we use these options. So over here, if you observe the Angular first lice, currently the value is 0. If at all I use the slider over here and click on these values, you will see that my pie chart rotates. So based upon the type of angle that I want to go ahead and set for my pie chart. I can go ahead and use this particular option. I can even set a value something like 90 degrees or maybe a 180 degrees. And you will observe that my pie chart rotates accordingly. The second thing that I want you to observe over here is something that I referred to as the explosion. And once again, the value by default is set to 0% away you, let's see what happens when I go ahead and click on the slider over you. As the value increases, you will observe that the gap between each of the individual slices of the pie chart separate from each other. So sometimes people like looking at the pie chart this way. So this is just a good to know information. As of now. I'm just going ahead and changing it to 0. And I will keep my pie chart as it is. So that's it about a pie chart. Let's quickly understand what exactly is a doughnut chart. So I'm gonna go ahead and select the same data. Click on Insert, and then click on pie chart. And this time I'm gonna go ahead and create a doughnut chart over you. Now you will observe that this chart looks somewhat similar to my pie chart, except for the fact that there is a hole in the center over here, which gives it a look and feel of a donut. So I'm going to once again go ahead and click on design and select a ready-made design, OEO. And then when it comes to donut, let us understand what are the elements that we can go ahead and format over you. Once again, I will go ahead and add the data labels to my doughnut. And you will see the data labels have appeared. Now I won them in percentages, so I'm going to click on them and right-click on it, and then click on format Data Labels. And once again, I will put percentages over here. I will remove the values and I will close it. And while my data labels are selected, I will go to the Home tab and change the font to black. Just like my pie chart that a certain other formatting elements that I can use for my donut chart. Let's look at them closely. So I'm just going to move my donut chart over here a little bit. I'm going to right click on it. And then I will click on this particular option that says format data series. When I click on it, I get the Format Data Series window, which pops up from the right-hand side of my screen. And I have three options over here. First one is the angular four slice. And just like a pie chart, when I go ahead and click on it, it can actually go ahead and rotate my donut chart in a clockwise or anticlockwise manner. And said The angle as per my requirement. I'm just going to set it to default as of now. And now, let us go ahead and look at donut explosion. And this is once again similar to that of a pie chart. When I go ahead and increase the percentages, you will observe that the gap between individual elements or the slices of the donuts increases accordingly. So as I said earlier, it is a matter of choice. And some people love looking at the data from this angle as when just a good to know information, I'm just going ahead and setting it to default. And the third element that we will look at, OEO is something called as a doughnut hole size. Now, oh, you're by default that doughnut hole size is 75. Now I can once again go ahead and increase or decrease the doughnut hole size. And based upon that, you will observe that the size of the hole in the center of the donor increases or decreases in size. Let's go ahead and now put the doughnut back to its original place over year. So this is how exactly you go ahead and create a pie chart and a doughnut chart. Now, I'm not a very big fan of a pie chart or a doughnut chart, and I would always use it as the last option in my dashboard. But it is something that is a very good to know information because at times you might have to go ahead and use it in your dashboard. Now in the same dataset I have at my hand, if I'm looking at my pie chart against a Bar Chart, I feel that bar charts look more subtle and they give you a better insight in terms of looking at the data. But then as I said, it is again, a matter of choice. Some people might prefer a pie chart as against a bar chart, but the fact still remains that pie charts have a slightly bad reputation and they are known to be messy and sometimes hard to read. However, if you are trying to illustrate the percentage breakdown of smaller number of data points, then they can be very effective. For example, if you're trying to show percentage of people who prefer Burgos against sandwich and against rolls. Then in that case, maybe using a pie chart would be a good option. So I hope you enjoyed this lecture and I shall see you in the next one.
21. Lesson 20 Printing in Excel: Hey, welcome back. So in this particular lecture, we are going to understand how do we go ahead and print a document in Excel? Now you might wonder an entire lecture on just going ahead and printing a document. I mean, it's just simple. I just need to go to the File option and then click on print. But know when you're going ahead and printing and Excel documentation that a few things that you need to know so that you don't face any problem while going ahead and printing it. So I have this particular document in front of me and lets say if at all i go ahead and try to print this. So for that what I will do is I will click on the File option over here, and then I will click on print. When I go ahead and do that, it creates a kind of a print preview for me over you. And it shows that my document is actually running into 14 pages. Now let's go ahead and find out why. So if I go ahead and start clicking on this arrow you it shows that my document is going ahead and printing all the pages. But then from page eight, look what is happening. That is a partial page of my pitch that is getting printed separately. Ok, so if I keep on clicking on this arrow, you will observe that all these records are getting printed separately and that is exactly not what I want. So how do I go ahead and fix that? So let me just go back to my page once again. And when I come back to this page, you will see that there is a small dotted line over u. Ok, now this is something that we referred to as the page break. And what is the purpose of a page break? Let's understand that. If at all I click on the View tab and then click on the Page Break Preview. You will observe that when XL is going ahead and printing my document, it is going ahead and breaking the page over you. Okay, at this particular column and the remaining part of the page is being pushed to another page. So that is what is going ahead and creating a problem for us. So how can we go ahead and fix that? So what we can do over you is we will go back to the normal page view over you. And then let's come down to this particular option that says Page Layout. And under Page Layout, I have this particular option to go ahead and set my width. So right now it is set to Automatic. What I'm gonna do is I'm just gonna go ahead and click on the drop-down over here. And then I will click on one page. Okay, now let's go back file option and then click on print. You will observe that everything now fits to one single page. And instead of 14 pages, now my document is only running in five pages. Let's go ahead and look at some more features about printing. So if at all, I go back to my first page over you. You will observe that I can see my headers on the first page. But when I move to the second, third, fourth page, I really cannot see my headers. Okay. And let's say I want to go ahead and print my document in such a way that on every single page, I want to go ahead and see this particular title away. Oh, and I also want to go ahead and look at the headers. How can I achieve that? So let's go back to our page layout once again. And over here we have this particular option called us Print Titles. Let's look at these tabs one by one. So the first step that we have over here is the page tab. Now when I go ahead and click on the page tab, over here, I have two options, portrait and landscape. So if at all I click on landscape and then click on print preview. This is how my document would look. So in fact, what has happened is instead of printing the document in a portrait mode, it is going ahead and printing my document in a landscape mode. Okay, let's go back. And then once again click on print titles. I will go to the page option and I will click on portrait. And now what I will do is I will go to this particular option that says sheet. And the way I have this particular option that says a Rows to repeat at the top. What I'm gonna do is I will just go ahead and click on this. And I will select these rows from row number two, row number five. Okay? So what I want is I want to go ahead and repeat this on each and every page. So every page, I want the title and I also wonder headers. So I will hit Enter and then I will click on print preview. And you can see that if at all i go ahead and scroll between the pages. On every page, I can see my headers as well as my title loan. Okay, Let's go back once again and then let's click on the print title option over you. Now. Oh yeah. I also have some more options like Gridlines, black and white, draft quality, row and column headings. Let's see what happens when I click on row and column headings, and let's click on print preview. You will observe that when I do that at the top, just about my title, I can see all my columns. And then to the left-hand side of the page, I can also see my rows. Now that is definitely not the way I would like to go ahead and print my document. But it is good to know information just in case you need it. Once again, I will go to my Print Titles option over you. And I will go ahead and uncheck this. Let's go ahead and put a checkmark on grid lines. And let's see what happens. When I click on grid lines. You will see that just to the left of my page and towards the top I can see the grid lines visible. Okay. So as and when I'm moving across the pages, you can see that the grid lines are visible on each and every pitch. I don't want the gridlines, so I'll once again go to my Print Titles and then I will go ahead and uncheck the grid lines option. And when I go back to my print preview, the grid lines are gone. Let's look at some more features now. So if I go ahead and click on Print Titles again. So over here I have this particular option wherein it says page order when the option by default is down. And then I can go ahead and select over and then down. But what it does is when I select this down and then it will go ahead and print in a vertical fashion first. Then it will move up, and then it will start printing vertically again. Wherein if at all, i go ahead and select over then down, then what it will do is it will go ahead and print all the horizontal pages first and then it will move down. What do I mean by that? So if at all I click on view and then I click on the page layout. You can observe that how might be Jizya arranged, OK? And depending upon this particular arrangement, if at all I click on print ideas. It will go ahead and first print wages from top to bottom, and then it will move up. If I select this particular option, then it will go ahead and print all the pages horizontally, and then it will move down. Ok. Now let's go back to our normal view once again. So i will just click on cancel over here, go to the View tab, and then I will click on normal. Now once again, I will click on Page Layout. Good to print titles. And over here I have this particular option called as margins. Now using this particular scroller, I can go ahead and set my margins over you. But this is kind of a tedious task because you will have to go ahead and change the margins over here. And then once again, click on print preview and look at the margins. Once again, there is a shortcut method of going ahead and doing that. What I can do is I can click on print preview and on my print preview page itself, somewhere at the bottom, I have this particular option that says show margin. When I click on that, it pops up all my margins on the preview page itself. And what I can do is I can go ahead and manually set my margin over here itself. So I don't really have to go to and fro from the margins tab to the preview page. What I can do is I can just go ahead and set my margins over a year itself. Okay, let's go back. Let's click on print titles. And now I want you to know a little bit about the headers and the footers. Okay, so if I go back to the Print Preview over, yeah, I really do not have any headers or footers on my document. Okay. Let me just remove the margins. Now, what I want to do over here is I want to go ahead and give this particular page a title. And somewhere at the bottom in my footer section, I also want to go ahead and add the page numbers. So let's go ahead and achieve that. We'll go back and then we'll once again click on print titles. We'll come to this particular option that says header and footer. When I click on this particular button called custom header, it pops up a header menu. Over here. I can go ahead and add an header to the left section of my page. I can add it to the center. Or maybe I can go ahead and add it to the right section of my page. And also, I have a lot of options over your wherein I can just go ahead and type a title. Wherein I can just go ahead and insert a page number insert a number of pages that are there in my document. I can go ahead and insert a date, a time. Okay. I can even go ahead and insert the location of this particular page. I can go ahead and insert the file name. Then I can go ahead and insert the sheet name. I can even go ahead and add pictures to just in case if at all I want to go ahead and add some kind of a logo as a header. I can go ahead and do that as well. And then that is an option of going ahead and formatting the picture. So just in case you go ahead and add any picture to your headers, you can even go ahead and format it right now what I will do is at the center, I will just go ahead and add the file name. And when I did that, it has already picked up the file name and it has gone ahead and added it to the center of my header on the right-hand side over your okay. I want to go ahead and add the date and the time when this particular document was created. I will click on OK. And now I will go ahead and customize my footer. Now just like header, when I click on custom footer, it gives me a left section, right section, and a center section. Let's say towards the right-hand side, at the bottom of my page, I want to go ahead and add the page number. So I will just go ahead and click on this particular option. And then I'll say off. And then I will click on this particular option which is in certain number of pages is a innovate will say page one, page two or five, base three of five, so on and so forth. So I will go ahead and click on OK. V0, V0, and then click on OK. Once again, let's click on file and let's go to the Print Preview now. So if I click on print over here, you will see that it has gone ahead and picked up the head over u. So the name of my worksheet is new workbook basic copy of Excel. It has gone ahead and given the same name to my document which will be sent for printing. I have also gone ahead and added today's date and the time when this particular document was created. So it has gone ahead and picked up the system time and added it over there. Similarly, at the bottom, it has also gone ahead and added the footer. If I go ahead and click on this particular next page option over here, you can see that on every page I have a header and accordingly, even my footer is changing. So since I'm on the third page, it says page three of six. And while I'm on the printed page, let's look at some of the options that are available over a year as well. So over here, I have this particular option wherein it says print active sheets. It gives me an option of going ahead and printing and Entire Workbook, or I can even go ahead and print a selection. Let's quickly look at how we go ahead and print a selection. So let's say I only want to go ahead and print this particular area on my sheet. Then what I can do is I can select it, click on print area, and then I will click on Set Print Area. Okay? And then if I go to the File option and then click on print, you can see that it is only going ahead and printing us selection. And in case if at all I want to go ahead and revert the changes. And let's say I want to go ahead and print the entire document. I can once again go to the print area and then click on Clear Print Area. Okay? And once again, when I go to the File option and click on print, you can see that my entire document is selected. Now over here, I can select which particular pages do I want to print? So let's say I only want to print the first three pages of my documents. I can select the number one over here, and then I can select the number three. So it will only go ahead and print from page number one to number three. Over here, I have this particular option of going ahead and printing in a portrait mode or landscape mode. Okay, I'll stick to the portrait mode right now. And then I have some more options over your, wherein I will decide how do I want to print based upon the size of the paper that I'm using. So I can print it in a letter format. I can print in A4 format. If I click on mode options, I can even go ahead and optimize the size of my documents. So I can go ahead and increase and decrease the size of my document over here. So let's say if I go ahead and increase the size 235 and click on OK, you will observe that the size of my document increases, okay, so I will just go back and keep it to the default settings. And then I will click on OK. And let's say by any chance if at all there are a lot of diagrams or charts on my document. Sometimes I may have to go ahead and shrink the size of my document so that it fits in one page. And I can use the options from Europe to go ahead and achieve that. So I have options like fitted sheet on one page with all columns on one page, fit all rows on one page. Okay, so I can go ahead and use these options as and when I need them. So that's about the various options that we have when we are going ahead and printing a document. So that's about printing in a nutshell. And I hope you enjoyed this lecture.
22. About the Project : Hey, welcome back. So congratulations on completing all the lectures in the lecture series. And now it's time to go ahead and put everything together. So let's move on to our project. So the first thing that you would have to do now is download the project file. The project file is available in the project and Resources section of the lecture. Just go ahead and click on this particular file and you can download this particular file. The next thing that you would have to do is complete the task given in the project. Open the project sheet, you will see that at the bottom that are 11 different dust that you would have to go ahead and complete using the data that is provided in this sheet referred to the topics, again, if needed, if you look at your project sheet very carefully, you will observe that just next to every task is listed the name of the topic, which you can refer to just in case you get stuck somewhere. And last but not the least, only after completing the project, proceed to the next lecture. The next lecture will give you the solution to your project. But I would highly recommend you complete the project to see how close you are to the solution once you move on to the next lecture. So let's get going and all the best for your project.
23. Lesson 21 Project Solved: Hey, welcome back. Now I'm really excited to see you come so far in this lecture series. And if you're watching this particular lecture that I'm assuming that you have also gone ahead and completed your project. And the reason that you're looking at this lecture right now is to see how far you have come in terms of going ahead and excelling and exit. If you haven't yet completed the project, then I would highly recommend you pause this video year itself, go back, complete the project, and then come back. And now that you're watching this lecture from this point onwards, I'm assuming that you have already completed your project. And let's see how close you have come in terms of honing your skills in Excel Basics. So now let's look at our data. We, you, and we have some tasks at our hand. So the first task at our hand is formatting the worksheet and giving it a title. So let's go ahead and do that in our raw data file. So I have my raw data over you. And to go ahead and give it a title, what I will do is I will just go ahead and insert some rows. And if you remember to insert rows and columns, all I have to do is just right-click and click on Insert. And accordingly it will go ahead and add rows and columns to my worksheet. Now what I'm gonna do is I'm going to use the merge function, something that we learned during the formatting basics. So what I'm gonna do is I'm going to select these cells over here. Click on merge, and let's give it a title. So I will call this particular sheet as employee database. Okay? And now what I can do is I can give my title a nice border. So I will click on the border over you. I will go ahead and give it a background color, increase the font, and make it bold. And then I will also go ahead and align my title. So with that, we have gone ahead and completed the first task at our hand, that is formatting the worksheet and giving it a title. Now let's look at the second task in our hand. Now the second task that we have at our hand is changing the font of headers and making them bold. So our header is our u. So I'm going to select my headers. So let's go ahead and change the form. So I will go ahead and select, let's say, Bookman old-style. You can select the font of your choice. Okay, then I have to go ahead and make it bold. So with these particular cell selected, what I will do is I will click on bold and you will see that my headers are now bold. To go ahead and align my worksheet, all I have to do is select all and double-click anywhere between two columns. And it will go ahead and align everything properly. Okay, so we have gone ahead and change the font and even made them bold. So with that, we have even gone ahead and complete data task number two. Now the next task that we have at our hand is converting your data into a table. So for that, what I am going to do is I will just select. Any of the cells over here. And then I will click on the Home tab and then click on this particular button that says format as tables. And if you remember from the lecture, we can go ahead and select any style of a choice. So let's select this particular style over here. By default, it has gone ahead and selected our table. We will go ahead and click on OK. OK. Once again, we just align everything properly. So select the entire worksheet and just double-click between any of the columns. So with that, we have gone ahead and completed our task number three. So our data is now converted into a table. Now it's asking us to calculate the total incentives earned by each member. So each member has done certain number of sales and every sale has some incentive associated with it. Now we need to go ahead and find how much total incentive each of the employee has earned. So what we will do is we will go ahead and add one more column over here. And we will call it total incentive. We will hit enter. Okay? And you will observe that because we have gone ahead and converted our data into tables and other column has been added to the table over here. Now we need to go ahead and find the total incentive earn. So the total incentive is nothing but the product of the total number of sales. And then we will multiply it with the total incentive proceed. Ok, and because we have used tables over here, you can observe the way it has gone ahead and done the referencing. So if we go ahead and hit Enter, it has gone ahead and done the calculation for all the employees for us in one goal. And that was the benefit of going ahead and using tables. So with that, we have completed our task number four as well. Now the next task that we have at our hand is calculate the total incentive earned by all employees. Now the total incentive earned by all employees is nothing but the sum of these values. And to get that, what we will do is we will use the autosome. So I will go to this particular cell over here, go to my home tab, and my autosome is over here. So I will go ahead and click on some. And it gives me the total incentive on by all the employees. Another option of going ahead and doing the same is using the subtotal function. Okay, so with that, we have gone ahead and completed up task number five. Let's look at task number six, find the tenure of each employee in 2020. And it says, the hint for that is relative or absolute referencing. So let's go ahead and find the tenure. And as you can see, another column is automatically added. So to find the tenure of each employee in 2020, what I would do is I would say equal to two, I get 20 minus the year of joining and I will hit. Enter. So with that, it has gone ahead and found the tenure of each employee as of date. Now it says, solve the data on the basis of tenure and find the employee with maximum or minimum tenure. So over here, we can either use the max and the min formula wherein I can say is equal to max. I will open brackets. I will select this data and I will close the brackets. And now it tells me the employee with maximum tenured IS 34 years and that is Dan. But rather than using the max and the min function, let's go ahead and just saw the data and find out the solution. So it says sought the data on the basis of tenure and find the employee with Max and minimum tenure. So what I'm gonna do is I will click on the drop-down over here. And then I will click on sort from largest to smallest. The moment I did that, it clearly shows me that the employee with maximum tenure is Dan and the employee with minimum tenure is Kristy, That is just five years. So with that, we have gone ahead and completed our task number seven. Now, our task number eight says, If only employers with more than ten years are eligible for a bonus, find the count of employees who would receive the bonus. So for that, I'm gonna go ahead and use my conditional mathematics. And the formula that I'm going to go ahead and use over here is count. If I have an open the brackets and now it's asking me for the range. So I will select the range. And now I will go ahead and give a comma. And then it is asking me for the criteria. So for that, I will open up my double quotes and then I will say greater than, close the double-quotes. And then I will give the AND function. And then I will say the number ten. And then I will close the brackets and hit enter. So now it tells me that there are only 12 employees who are eligible for a bonus because there are only 12 employees who have the tenure greater than ten years. So with that, we have gone ahead and completed our task number eight. Now let's move to task number nine. The task number nine says, filter the data to find the employees with salary greater than or equal to 50 thousand. So for that, what I can do is I can just go to the salary column over here. I can go ahead and click on number filters. And then I can click on this particular option that c is greater than or equal to. And then I will say 50 thousand. And then click on. Okay. And with that, it has gone ahead and filtered all the employees for me where the salary is greater than 50 thousand. So with that, we have completed our task number nine. Now the next task that we have at our hand is going ahead and creating a suitable graph for the last month's sales figures. Now since we have gone ahead and applied some filters over here, we will go back to our raw data and try to create the graph over here. Let's not clear the filters over here. Of course, I can always go ahead and clear all the filters and create a graph over you. But what I will do is I will just try to go ahead and create the graph with my raw data itself. So what I will do is I will go to the raw data once again. Select this raw data. Come back to my project sheet, create a new sheet. I will paste the data. We I will rearrange the data. And now what I will do is I will go ahead and select this particular data, press the Control key and select this particular data. And then I will go to the insert tab, will do 2D column charts, and then I will select this particular chart over here. Similarly, I can go ahead and create a line chart for this particular data. Let's look at it later on. Let's first go ahead and format this data. So what I will do is I will just increase the size of this particular chart over here. And then I will click on the Design tab and select a ready-made format over here. Okay, and with that, my chart is ready. Let's write creating a line chart for this particular data. So I will select this data, press Control and select this particular data. Then I will go to the insert tab, would line charts. And let's select this particular chart over here. And once again, again, please, the charter will increase the size a little bit. Select the chart, go to design, and then select any particular design of my choice. I can even play with the colors over here, so I can go ahead and change the colors. And with that, even my line chart is ready. So with that, we have gone ahead and completed our task number ten as well. The last thing it is asking us to do is create a print preview of the worksheet and tried to fit everything in one page in a landscape mode. Okay, so what we will do is I just go ahead and delete this information from you. And let's try going ahead and printing this particular sheet. So for that, what I will do is I will click on the File tab and then I will click on print. And you can see that it is not printing my data correctly. Everything is not fitting on one page. So what we can do is we can go back to our page and then we can go to the Page Layout tab. And over here we will go to the width section. And this automatic, we will change it to one page. Okay. And since we have to go ahead and print this document in a landscape layout, we will go to orientation and change this to landscape. And now if you go to the File tab and then click on print, you will observe that everything now fits on one page. Ok, now this particular title is going a little outside the margin. So let's go ahead and fix that. So what we can do is we can just select these three columns and then delete it. Okay? And now if you go to the File tab and then click on print, you can see that everything fits perfectly in one single page. So let's bring back our task list over here. And with that, we have completed our task number 11 as when we have created a print preview of the worksheet and managed to fit everything in one page in a landscape mode. And with that, we have also gone ahead and completed our project. And I'm really sure that what you have managed to achieve in your project is very close to what we have managed to achieve. In this particular lecture. You might have used different tools and techniques, but getting to the solution is very important because in XML, you can approach a same solution via various ways. So I'm really happy on your coast completion and I hope the strong foundation that you have laid down in this particular course becomes a stepping stone for your future journey. Intuit wants to excel. So once again, congratulations on completing the course, happy learning and stabilise.
The Guruskool
