MS Excel Basic to Advanced Course for Daily Work

1. CLASS INTRODUCTION: Hi there. Welcome to the MSXL basic to advance course for deliver your ultimate guide to Mastering Excel for real world task. Hi. My name is Mahaviir. I'm thrilled to be your instructor for this exciting course. Whether you are a beginner, just getting started or someone looking to velp your skills, this course is designed to help you work smarter, faster and more confidently in here's what you will learn in this comprehensive course, navigating the Excel interface and customizing your workspace, managing data tables, formatting and alignment for professional looking spreadsheet, mastering essential functions like lookup, H lookup, If statement, and more, using advanced tools like named ranges, data validation and error handling, and creating interactive dashboard, managing large datasets, and troubleshooting with E. Every lesson is packed with practical examples, hands on experience and tips that you can immediately apply to your daily work. By the end of this course, you will have the skills to transform raw data into meaningful insights, creating stunning reports and save hours of work with Excel powerful tools. So what you are waiting for, let's unlock your potential with Excel and make your daily task easier than ever. I wish to see you in the course. Thank you. 2. EXCEL INTERFACE: Hello, everyone. Welcome, once again. In today's video, we will discuss about the Excel interface. On the tab, we can see so many tools which we are going to use in the Excel. These all are divided into the groups, and these groups are available on these tabs. So the top area in which all tools are grouped is called tabs. We have different type of tabs based on their requirement. In the home tab, we have most frequently used tools related to the formatting. We can use it for inserting new row column, delete formatting, autosum. So these are the most frequently used tools, and we have some other tabs as well. Let's say, insert page layout, formula, data, and so on. We will discuss each and every tab and all tools details in this course. In the tab, we have groups, and after the groups, you can see a small arrow button. So if you click on it, we will get the advance option of the related category or the groups. So if I clicked over here, we got the formatted sales and we have some advanced options over here. So we will discuss all these later on. And in some tools, you can see a small arrow button as well. So if I click on it, so we will get some more options related to this tool. So we will also discuss these later on, and we also have some options over here in which we will find more options. So we have some tools in which we have advanced option over here. And in the arrow, we can find some more options. Now we are moving to the formula bar. This is called formula bar. As you know that spreadsheet program are used to do the numeric calculations, statistical calculations, and text calculations, et cetera. So we have predefined functions in the excel which we can use. So before learning about the formula bar, we have to learn about the cells. This area is called sheet in which we have columns, which is categorized by the A, B, C, D, E, FG, and so on, and these are called rows. These are represented by the numbers one, two, three, four, five, like this. And here we can see a table. This table have some fields which is called cell, and every cell has a name. This cell is available at column number H and row number three. So the name by default is H three, and this is the cell in which we are going to enter the Dra. So for using the function, first of all, we have to select a cell then we can enter the function or our formula or the value over here. Let's say I want to enter a name, I will enter, and that is in text only. So if I enter, the selection of this cell will come down. I'll let you know all these options when we have to move to the right, left, up and down, how we can use all the shortcut options. So this is the formula, and all formulas or functions will start from the equal sign. So whenever we enter any data without equal sine, the Excel understand its value only. But if we want to enter any function or formula, let's say, I want to do some calculation. So we have to select a cell, then go to the formula bar, press equal and one plus one. It will work like a calculator this time. So if I press Enter, we will get the answer too. So it is working as a calculator. Will discuss in details all the functions and the numeric or the text calculations. So if you want to delete any data from the cell, you have to press delete button from the keyboard, that will be deleted. And now I'm going to delete this one as well. Now we are moving to the right hand side section. This is called horizontal scroll bar, and this is the vertical scroll bar. As you know that, we have so many millions of cells and the rows as well. If you press Control and write arrow key, we are on very left hand side of our table. And this time, you can see our scroll bar got small size. Similarly, we have so many rows. If you press Control and down Aoki, right now we are on the last cell of our Excel. So you can see the scroll bar size is very small. Now I want to go to the first cell. I have to press Control home. And if I have entered any data in it, the scroll bar will be adjusted accordingly. Below side, we have some Zoom option. If I click on the plus sign, the screen of the Excel will be Zoom in, and if I press on the minus sign, it will be Zoom out. I can click on the percentage tab. Here I can select a value, select 100%, 75%, or you can enter any custom value. Once you're done, click Okay to accept it. Screen of the Excel will be adjusted automatically. We have different type of preview as well. This is the default preview which we are going to use in everyday task. But if we want to use another, we can select page layout view, so we can see header, footer and each page wise, whatever the page size we have selected will be displayed accordingly. And here we have web view that is also very good option for the formatting and the page adjustment that I will teach in details in the coming videos. So I'm going to accept this one. And here we have option for the sheets. By default, the sheet one is coming. If we want to insert a new sheet, we have to click on the plus sign, the sheet two will be inserted, and we can rename the sheet by clicking on by right click on this. So we have some options that I will explain in details in the coming videos. In the downside, we have option called status bar. Here we can see ready. It means we can enter any data. This is for recording the macros. If I click on it, macro recording option will appear. So I'm going to cancel right now. We will discuss later on in details about the macros. So this is how we can use the interface. We have one more option called file. So this is called Bk Stage view. Here we have some options for saving for printing, sharing, export, closes. And if we want our account details, we can click over here. And we have some more options, and we can use that. And we have one more option that is called action options. Here we have all our different options which are categorized. These are the generals formula, data proofing, save language, and so on. So we will use these options later in this course. So this is the interface. In the next video, we will learn more about the Axl. 3. CREATE NEW FILE AND SAVE: Hello, everyone. Welcome, once again. In today's video, we will learn how to create our first worksheet and how to save in the Excel format. For creating a workbook, first of all, we have to open an Excel. By default, we will get the book one. On the top, we can see the name Book one. We have to save this after doing some work. Otherwise, we will lost our all work if we will not save this file. For creating a new workbook, first of all, we have to click on File button, then come to the new. And here we have option if we want blank workbook, we do not want any type of formatting, text, data, so we can select a blank workbook. So if I click on it, I will get a new workbook, and as I already created Book one, the Book two has been generated. If I create a new blank workbook, then Book two, three, and it will go on. So I'm going to close this one right now. Again, I'm going to click on this option and using the new option. And below that, we have some templates provided by the Microsoft. These templates are available free of cost from the Microsoft, and these are categorized in different categories like business, personal, planner, ter, list, buzzards and on or if you want a specific type of template you can search from here. Let's say I want invoice, I will type and press Enter. Then system will search online Microsoft different invoices and we can see the preview. So we have to select a template. Let's say, I'm going to use this one and system will ask to download, so we have to click on Create and we got it. It is not yet saved. We can see the simple business invoice number one. Now we have to save this one. So for saving, we can use shortcut key Control S, or we can come over here on the file menu that is called backstage view. We have two options called save and save as. First of all, we will use save here we have option, we can save this file into the PC and these are the folders. These are the recently used folders, and this is for the last week used folders. So we can select that one or we can browse from here. We can select any folder of our choice. We can give any name of our choice, then we have to select a file type. If you click on it, we have different type of options. Normally, we use Excel workbook or macro enabled workbook, which is used for VBA. Right now, I'm going to use Excel workbook. Here we can give the author name, tag, title, et cetera. If we want, these all are not mandatory. It is optional only. So after doing that, we have to click on save, and now our invoice got saved. We can close by clicking on the X button. I'm going to save this book one as well. So pressing Control S, shortcut key. This time I got this option. We can choose a location from here or click on more for more options. So I used recently this folder, and this is my first work, and I'm going to give the name my first workbook. And I'm going to save this one. So these are the first thing you have to learn how to save and how to create the new file. In the next video onwards, we will start entering the data and about the formatting in the Microsoft Ax. 4. QUICK ACCESS TOOLBAR: Hello, everyone. Welcome, once again. In the last video, we discussed how we created our first workbook and we saved it. So before going ahead, we need to understand about a very good option called Quick Access Toolbar. On the top of the tab button, we can see some tools, save do. If you click on this little arrow button, we can see some more options. So this access toolbar is very useful for daily used or your favorite tool. If I use any tool here, I need to click on the tab, then come to that particular group, and then we have to select. Let's say for removing duplicate, we have to click two or three times, then only I can come over here. But if I assign this tool in the Quick Access tool in a single click, I can access that tool. Let's say I want to add this remove duplicate here. Before that, I can add some available tools, let's say, email, so I'm going to just click on it, that tool will be available. So in a single click, I can select that one. Even I can move this from top to the bottom of the so click over here and it is show blow of the ribbon, so I can use this here as well. And the second thing, if I press Alt, I will have one, two, three, four, five numbers. So if I press Alt two or Alt four, the email option will be unable. So this is a shortcut key. This is called Alt options. If I want to add remove duplicate to the Quick Access Toolbar, just click and click on add to Access Quick AxssTolbr. It will be added here and we have one more option, click over here. Go to the more commands, that Excel option will appear. Here we are on the Quick Access Toolbar category, and here we can find all popular commands. If we want a particular command from a particular tab, so we can select by clicking over here. So I'm going to select, let's say, Data tab, and here I have all the tools used in the data tab. Let's say I want to use Gold Seek option and selecting that tool and click on ad, that will be added here, and after that, you can click on Okay, or you can use these options to move up and down. Once you're done, click on, that will be available over here. So this is called Golsek. And if I want to remove just right click on this tool and remove from the Quick Access toolbar, that will be removed from here. So this is how you can use this Quick Access Toolbar. I use this tool very frequently because it is very useful for doing your work quickly, and it will increase your productivity as well. 5. BASIC DATA ENTRY IN EXCEL: Hello, everyone. Welcome, once again. In this video, we will discuss how we enter the data in the axle. For entering any data, first of all, we have to select any cell, then we have to start typing. There are three types of data we can enter in the Excel. The first one is numeric values or the numbers, which is called one, two, three, four, five, six or like that. So these are called numeric values. The second one in the text values. So let's say I'm going to insert my company's name, tutorial name, last name. These all are called text. And the third one is date which actually is a number, but it is represented in a date format, but every date has a unique number. But we do not remember any name. Let me show you how we can enter any date. We have to enter a particular date in the date format. Let's say today is 24th of July 2024. So I used slash for bifurcating my date, month, and the year. And once I press Control Enter, my cursor will be in the same cell in which I entered our date. But before that, I was using the Enter. So this is a chip which you can remember. First enter your data, then press Control Enter, so that particular cell will be selected. And now we have three types of data, number, text, and the date. Now let me show you how we can see the actual numeric value of this state, come to the home tab and come to the number grouping and come to this option and select general. So as I told you earlier, every date has a unique value. So that value is 405-40-9745, 497. That is the date value. But if I want to convert this into the date format, again, I have to come to the home tab number group, and select the date option from here. We have different type of dates available. We will learn in details about the formatting of all type of values later on. So I'm going to select short date right now. So these are the main formatting options we have type of data, but we have other type of formatting available in the Excel as well. Let's say I want to use currency, so I have entered one value, let's say 100,000. I think it is more than that and I have currency options as well. So if I click on this little arrow button in which account number format, accounting number format, if I click on this, we have Rupees, India, English, USD and UK Euro. If I want more accounting formats, just we have to click on the more and then we can select we can select the symbol as well. So all country symbols are available, which we can use, and the first one will be depending on the regional settings you have selected in your Windows system. So I'm going to select Europes, and the decimal place would be to, and this is the sample preview. If I click on Okay, so that would be converted. And the width of the column would be adjusted automatically. But in other case, it won't. And I have some more options, let's say, sorry, 80%, I can enter the value in the percentage to select any number and click on this percentage style, so that would be converted into the percentage. We can convert in the coma. And we can increase the decimal places from here and we can decrease using this option. So we have some more options for formatting of the numbers by using this option. So we have general, which can be a text, which can be a number that is called general. And this is numeric values which can be used for doing the calculation in the system. And this is for currency which we already discard. This is accounting, same, short date, long date. This is the time as well and percentage fraction fiction and we can find more number formats by clicking on this option. So all are categorized in the general. Then we have so much predefined values, currencies, accounting, date, time, percentage, fraction. So we can use any of them. So let me tell you what is the difference between the text and the numeric date. We cannot use the text data type for doing any calculation. So that is the main problem. I want to do any calculation, we have to use equal sign. So right now, let's say I entered 25 and here 54. System is taking these values as a data or the numbers. But if I want to tell the system that I want to do some calculations, so we have to start with equal sine, then we have to select any value. Right now, what I'm going to do is do the addition of the number entered in the cell value C 11 and C 12. We can give the reference for doing any calculation. So we have to enter the eco sine, then we have to select the value of the cell. Then we have to enter the plus sine and we give the reference of the next cell NII press control, we will get the calculation over here. So we have given the reference of that cell. So right now, if I change any value of this cell, so we will get the desired result automatically. So this is the main use of the Excel. We can do a lot of calculations using the functions and the formulas, and we will just change the data in the cells and we will get the desired result. So in the next video, we will explore some more options related to the Excel. 6. CREATE BASIC DATA TABLE: Hello, and welcome once again. In this video, we will create a new table. But we will also learn how we can open any workbook file from the local hard drive. For this, we can use our shortcut key Control O, or we can come over here, click on File Menu and then click on Open button. Here we will find the recently used files or click Browse to find or locate your file in your local hard drive. Go to your folder, select your file and click. Okay, that will be open. So you can open this file, 010 phone or data. Now in this video, we will learn how to enter this kind of table in our Excel five. That is very easy, but we will use the smartest way we can enter. Let's say we have a paper from which we are just going to see and we have to type this kind of data. So I'm going to enter all my header in capital format. So I just start typing. I selected the sale number 820. I just start typing row ID, press tab, it will move to the right hand side instead of down. If you press Enter, then it will move to the downside if you press tab, it will move to the right hand side. Now enter Order ID, press tab, then order date, press tab, ship date, press tab, ship mode, customer ID, tab, customer name, press tab, then segment, and then press tab. Entry press tab. Now, there is no column left to the right hand side. I'm going to press Enter this time. See the magic. My cursor will move to the first column we have started in the next row. So now we can start entering our data like that. So this time also I have pressed tab. So like that, you can enter the data. Okay? Now you can see I have enter all the data in the capital format. I'm going to use my function, the first function in which I want to convert this into the proper format. So as I already told for using any calculation or using any function, we have to start the value from equal sine. I will start typing my first function. You can note down it's proper. So I have to select. I can see the list. I start typing. So proper, then press tab, and then we have to give the reference of the cell to a 20 to the next one, and I have to close the bracket and I have to press Control Enter this time. Now we can see the values of Row one has been converted into the proper format. Now I'm going to copy the cell value. For this, we have to press Control C, then for pasting the values, first of all, we have to select the next cell. For this, I have to move the cell from A 19 to the B 19 by using write Aoki, then press Shift and write Aoki to do the selection and then control to paste the values and we can see the function worked properly and we got our values in the proper format. And now I need to change this as a value. So I'm going to select this function data in one go for the shortcut key is Shift Control write Aero key. And now I'm going to and now I want to delete my 20 number row. If I delete, then the reference will be deleted and the values will be remitted from the cell in which we have enter our function. So I need to convert this function into the values. So I have selected the cell ranges and copied, and now I'm going to the Home ta clipboard and click on this little arrow button, and this time, I'm going to select paste values. And if you select any cell in the formula bar, we cannot see any equal sine. We can see all the values as a data in the cells. Now I have to delete this row number 20, right click on it, and left click on the delete button, that will be deleted. And now I want to change this ID to the proper eps. So for this, we have to double click on it, use the left and right Aoki, delete and change the values. So this is how you can enter the data and save your time. In the next video, I will explain how you can do the selection of your data table and how you can quickly move from one cell to another without using mouse, and this is the best practice, and you must learn this one. So stay tuned to the next video. 7. SELECTION AND MOVEMENT TIPS IN EXCEL: Hello, everyone. Welcome once again in Excel 2019 course. In this video, we will discuss about the move options and how to select the data in a particular range. In the last video, we learn how to delete single row. If I want to delete the multiple, then we have to select by using the mouse, multiple rows at a time using the left click of the mouse. Then right click and then delete. For doing any formatting, first of all, we have to select a particular cell or range of cell. If I select A one, single cell has been selected, and we can see the name A one has been selected. If I select multiple, then we can see the first cell, but we can see the two cells are highlighted. It means these are the selected cells. So for doing any formatting, copy, paste options, we always have to select the cell or range of the cells. So we can use our mouse. We just left click and hold the mouse button and we can move the Curtin and we can select the range of the cells, and we can press Control C, and then we can do the paste to somewhere else. But if you use mouse, that would be very difficult for you and time consuming. So here we have only few data, the range is very less, but if we have thousands of data, which I'm going to provide in this video, we have another file, simple do files in which we have 10,000 rows of data. So how we can move our cursor from one place to another cell that we are going to learn in this video. So whenever you are just press Control Home, you will be on the first cell of the work sheet. That is a one. But if you have used freeze row, that I'm going to explain in coming videos, but I will show you we can use the freeze rows. So I want to freeze my first row. So let's say I have only 34 rows displaced over here. If I go down, so I cannot see the heading, but I want to see. For this, we can freeze this first row. For this, we can use view. Go to freezepan and the freeze row. So the first row has been freeze so I can easily see my first row whenever I come down. So this is also a very advanced option. But if you are here or maybe here, if you press control home this time, you will be on the A two cell. So it will not count or consider the freezed rows. You have to keep in your mind this thing. So I'm going to unfreeze this time. Now, again, press control home. I'm on the A one cell. So now, note down my second shortcut key which is Control writer O key. But before that, we will use woke press, so my cursor will move to the next cell to the right hand side. But I want to go to the last cell in one go. So I will press Control WROky. I want to go to the last field cell to the downside. For this, I can use Control down Aero ki. But if I press without control, it will move to the one cell only whether it is filled with the data or not. But if I select control, it will go to the downside to the field cell only. Why I'm telling you the field cell, let's say I remove this one. And if I press control up Aoki this time, so I will be stuck in between. So you should have filled all the data in complete row in all the cells. Then only you can use left right up and down keys with the control to move your cursor in all table. I'm moving like this. I'm going to undo my filter data. Now I press Control Home, control right, control down, control left, control up. One, two, three, I'm on the fifth row, then control right, and then I can move like this. Now it is time to learn how to go to the first cell. If I press home, then it will come back to the left hand side. If I press end key, and I press write Aoki after that. And you can see the mode has been unabled over here. It will be moved here. If I press home, it will move to the back. If I press and then Aoki, it will move to the right hand side. But if I press, then left arochi, it will move to the end of the left hand side, press and then arochi and the down heroky. So this is the shortcut key. You can use the end aroky of the keyboard. Now I'm going to open our large file, sample data of superstar order file, which have thousands of rows. And let's see how we can use. So this is the main file. If I press Control down aro key, I'm on the last. So we have 10,000 approximately 10,000 rows of data. If I press Ueroki, it will move to the top control with the right arochi, then control down arochi, control left arochi. So this is how you can use these Aokies now we are going to use for selection of this data. So if I press control home and if I want to select it rose only, so let's say only five rows, so I will press Shift and the write Aoki, but it takes a lot of time and effort. So what I will do shift control, write Aoki. It will select the complete row. Then I will press and hold the shift button from the keyboard and one time and press down Aoki to select the second, third, fourth, fifth, like that. So you can select like this. So if I press Control home, I want to select the complete table, and the table should be connected by the data. Let's say all the data in the first row filled with the data. It is not missed. So let's say I'm deleting the city, if I go here, if I press shift control riero ki, so it will be selected like this. So we have to again press Shift Control Aoki, then again. Then only it will be selected. So I'm going to undo and now I'm going to select this complete table in one go. For this, you have to select Control A. The complete data will be selected. The range. If I press again, Control A, the complete sheets data will be selected. I'm repeating, I'm going to home by pressing Control Home. Now I'm pressing Control A, that table will be selected. Okay, I'm moving to the shorter side. Here. If I press Control A, we can select a particular table, which is connected by the cell and the rows. If I again press Control A, the whole spreadsheet will be selected. Now we can copy or paste. Now press Control home. I'll give one more tip, Shift Control, press and, and that will also select that particular table. These are the few options which you remember, and you have to do the practice of the shortcut keys, which is very useful for your daily routine work. If you will use your keyboard frequently, instead of mouse, your speed in the axle will work like anything. So this is the way you can move your cursor and you can do the movement using the shortcut keys. If you have any query related to that, just let me know. I will try my best to give the answer as soon as possible. 8. ALIGNMENT: Hello, everyone. Welcome, once again. In this video, we are going to use how to do the alignment of your data. For this, we will use the same sheet, 0104 order data. You can just open from your provided sheets. Now we are going to create the duplicate of this worksheet as well. For this, we have to just right click over here and we have to select move and copy option. So I'm going to reduce the size, and you can see move and copy, select that option, and we have to select this option, create copy, and then click Okay. And then you can rename it by using this option, alignment. Presenter to accept it, and now I'm adjusting the screen of Excel as well. Now let us begin all the options. So first of all, we have to use alignment. For that, I already discussed, we have to select the cell for which we want to apply any formatting option. So let's say I want to apply the alignment of this column to center. But before that, I need to increase the width of this column. So I just move my cursor in between B and C. I can see my cursor of mouse has been changed, so I'm moving this to the right hand side. So this is the way you can adjust the width. Then we have to select the complete column by click on the top of the B column. Then we have to select the alignment. Like right now it is aligned to the left, the center, and the right. So you can use alignment option. Now I want to increase the height of the row. Put your curson between the four and five. The cursor sign has been changed and click and drag to the downside, the height would be adjusted automatically. Now this time, I have to select in complete row. For this, we have to select this one, or we can use the shortcut key shift space bar. Now I need to select this option. So this will be used for middle align, and this is the top align, and this is to the bottom align. And now I'm going to select a single sale, and we will use this option, which is orientation. Just click on this little r button and we can use different type of alignment like counter clock, angle clock, vertical, rotate text type. Rotate text down and then format cell alignment. Once I click on it, I will be on the advance option. From here, we can manage completely, and we can adjust using this and we can see the degree, and this is totally customizable. We can come again over here and we have some more options like horizontal, vertical, and we can do the dent as well. So after doing all your formatting, click on Okay or cancel to cancel your formatting. We can undo all the formatting which we applied, and now I'm going to use rap text. So just understand what is rap text. So let's say I have less width for my text, and I cannot see the complete line which is standard class, we can use rap text. If I select these all data by using this time I'm going to use shift control down okey, and if I go to the rap text and click on it, all overlapped text will be collapsed into this cell and the height of the row will be adjusted according to the text we have. If I adjust the width, it will be adjusted, but row will not adjust automatically. The shortcut key to adjust the row in one go, just click on this conjection of this corner, double click in middle of any row, the height will be adjusted automatically. But if I select the data, if I enter over here, if I enter one time space two time, three time, and then four time, and if I press Enter, the height will be adjusted automatically. So this is the use of rap text. I'm going to deselect this option. Okay. So Double click, it will be adjusted like this, automatic. So you have to adjust the width by double click in middle of the two columns. Now we are going to learn about increase or decrease indent. So first of all, I'm going to select this and I'm going to decrease the indent and then increase the dt. So these are the options related to the alignment. We will discuss more formatting options in next video. 9. FORMATTING TABLE: Hello, everyone. Welcome, once again. In this video, we are going to learn about formatting. Here I have one table, which is not looking so good. My task is to give some professional look. So I will give you some tips how you can use your shortcut keys, keyboard shortcut keys. So you have to note down one by one that is very important for doing all your formatting things and selection of data using the keyboard only. Very rarely I use mouse where I cannot use. Then whenever possible, I use only my keyboard. Instead of mouse. So first of all, I have to select this table to move downside. For this, I will use Control A to select Control X to cut the data. Then I will move my cursor and I want to paste it here. So you can use cut and paste like this. Now I want to adjust the width of the column. For this, we can use a shortcut key using the Alt. So now I'm going to use my Ault sequence shortcut keys, which is very useful. Press Alt from the keyboard. We can see characters are appeared on the top of the tab, press H to select the home tab. Now I have come to the format option here. For this, I have to press just O. Then I have to select AutoFit column width, which is I. So you have to remember this Alt H O. I'm going to undo this Alt H, oi. That will save a lot of time. So see when you do the practice with these shortcut keys, which is very frequently used, it will not take time, but if you use your mouse, that takes a lot of time. So let me undo. If you use your mouse, see how quickly I can do if I know the shortcut keys. So I'm going to select Control A, Control X, move the cursor down sign, paste now allult HI. This is how you have to do the practice. Now I have to change the format of the date. You can see all are in numbers. So I'm using my up and down arrow keys. I'm here. Control Shift down aro key. Now Alt H N for formatting, then Alt down aro ki and select the short date and I want to center it, which is already centered. I want to feed some color in the heading for this shift control write Aoki for selection of the heading row. Now I want to feed the color alt H H, and select the color. Let's say, I'm going to select this one presenter and control B to make it bold. And now I have to select some columns. Now we can use mouse and we can adjust the width of the columns by using the mouse like this. Now the width of selected columns are same. Now we can use, again, shift control like this. I want to do the changes here. So now I can use the mouse if you want, because some work you have to do with the mouse as well. I'm going to reduce the decimal places. I'm going to select and reduce the decimal places of these. And for this, I want to make it like this discount, and I want to make it center. And I want to increase the width and like this width adjust the width looking quite good. Make it center, adjust the width, adjust the width of here, make it center, center. And now I want to give some border, select all the table, come to the hon tab, come on the font group, and select this option, and apply all border. We can apply only left, top, bottom or no border if you remove all borders from your table. And now I want to use I want to highlight these particular areas separately. So let's say I'm going to use this one, come to the border. I want to use thick outside border. And now I can apply the last formatted tool by pressing F four from the keyboard. Control S, do not forget to say by pressing Control S. And now I would like to see the preview. Before that, I need to adjust the page layout, come to the page layout. Here we can adjust the orientation to landscape. And once I select that one, we can see a dotted dash line over here. So it means it is outside from the page one. I want to remove this cell from here, and I want to select this option and this option. And we can see the page one, and this is the page to select that blue line, move to the right hand side. Now it is fit in the page one only. Again, click over here to see the preview. Control to save. Now want to see the print preview exactly. Press Control P. And this is the first page which we are going to take printout. We can click on Page button to take the printout. We can make the copies from here and these option will be explained in the coming videos. Let's escape to come out from here, and now I want to give some heading of it. So let's say my first table, select that cell, come to the home tab, do the alignment. Now I want to make it highlight. So just use tiles, which is provided by the Microsoft, just click come to the Hon tab, click over here, and we have some good options. Predefined format like heading one, two, three, total, and you can use anyone which you like. I'm going to use heading one only, and I'm going to select some more CL and then I will apply. You can do some more formatting if you want, select that cell and do the formatting. And now I want to do one more task. I want to do the total of these cells. I'm going to use function called sum this time. So as I already explained, for using any function or calculation, it will be started from equal sign, and I will start typing sum press tab and now give the range of the cells for which we want to do the calculation. So this time, we have given the range, close the bracket, Control plus Enter, save this come to the profit side, we want to do the same in quantity, then pase the function, paste the function over here, and select this row and come again to the styles and select the total. Increase some font size, save this one, and now see the preview by pressing Control P. I think it is looking quite good. So we will discuss some more options about the formatting and the shortcut keys. So whatever I discussed in this video, you have to note down and do your practice with the provided data. 10. EMPLOYEE TIME SHEET TABLE: Hello, everyone. Welcome, once again. In today's video, we are going to design this employed timesheet in Excel, and we will also discuss some more formatting options and tips and tricks as well. So let's get started. First of all, I have to adjust my screen so that I can easily see this preview, and I can work in the Excel as well. So I'm going to adjust now I can start designing the table. So first of all, we will design this part, the table, then we will design this top part and the heading and then photop. So let's get started using the cell. I would like to type date, then start time and time then regular hours overtime hours. And when we have any mistake and we move to next cell, just press Shift tab, and press F two and do your changes. Now again, press tab to move to the next cell and then total hours. Now I need to adjust equally. And I need to select shift control write Aoki Alt H, AC for center, control B for bold, that's all for now, and now I will select some down side as well. So I will press Shift and down Aoki, and I will select this type of rows, and then I will press Shift space, and I will adjust the height as well. Okay, and I will select this option called middle alignment. And now, again, I'm going to select this area. And now I'm going to put all borders. And on outside, I would like to mention the thick outside border. And here I have totals and it is in center and bold as well. And this is outside border, and I would press a four to apply all the borders. So it looks quite good. Now I'm moving to the top part. So here I will select this one, and I will come to the homepage, and here I will select this option called Merge and Center. So it will merge all the cells, and now I can enter my heading in it. So employ Time sheet, center. Again, I'm going to select, and I would like to make it align left. And then I will see the style cells, and this time, I will select heading four. I will increase the size. I'm going to make it italic, and I can change the font from here if you do not like it. And we can change the color from here, automatic. And now I'm going to enter employ name. And I will increase the height of the row as well and selecting this complete area and make it center, and I have to put the bottom border. I will select bottom border now. Similarly, I will copy this. This time, I'm going to paste over here. And downside as well. I will adjust the height. So I'm selecting these three rows by using my mouse and I will put my cursor and I will increase a bit height. So the height of the rows will be adjusted automatically. Then I will change this text to employ and here supervisor. And I will make it in small and here department, and here tension. I think all looking quite good. We can merge these cells as well. I'm selecting and I'm pressing F four to apply the same formatting as I did before. And I would like to save this Me option. Excel course file, then Chapter one, and here time sheet, give the name presenter to save this file, and I would like to see the preview Control P. So total hours are coming. We need to adjust this area. So I'm going to press here, move this line over here, control as to save, come back, and it's looking quite good. Now going downside, and I would like to copy this. So this time, instead of typing, I would like to copy. So I'm selecting this area. Control C to copy, select this area, and go over here and paste presenter, and you can see this area is looking same as above. So what I will do instead of doing all those things, I will copy, and I will paste over here, and I will adjust the height. First of all, we have to select the rows. So I will use shortcut key shift space to select all the rows, then increase the height. And I will remove the border from here, after selection, come over here and select no border and change the text, employ signature, and then supervisor signature. And here date and date. So now I need to remove this merge. Again, I'm going over here on home tab, and I need to select unmerge and here as well, unmerge, and here four F four. And now I will copy and paste, I will delete this. I will select this one and then write alignment, and I put sorry have four, enter the semicolon, then signature area. I think everything is fine. We can put our header as well. So sorry, I need to save this file control as now come to the view and remove these grid lines. The grid lines are not printable. It is used just for display purpose only, so you can bifurcate between all the cells. So once you are done, you can click over here to adjust the height and width and select Control P to C the form design. So it's absolutely great. It's almost matching from here and with this sheet. So that way you can create the form and do your practice with some other forms. In the next video, we will learn advanced form designing. 11. FORM DESIGN ADVANCE: Hello, everyone. Welcome once again. In this video, we are going to design some advanced form using a spatial trick. If we design any form, we can do the calculations automatically, so it is very easy for using the forms and creating the forms in the Excel. Let us get started. In the previous video, we designed a simple time sheet in which we selected the cells, but I selected the cells in higher width. Now I'm going to reduce the width of the cells. So what I can do, I have to click over here the conjunction of the rows and the columns, and I need to decrease the size. I mean, the width of all the cells. It looks like this. So here, First of all, we have to save. I already saved this file. You can see this file in the Chapter one, art to form design advance. And again, I have to press Save. Now what we will do we will use the merge option in this form designing. So what we can do, we have to start from the top. So the top we have form wet or two. But before that, we need to understand how many cells we required so that we can merge. Or what we can do, we can click over here and then come back over here. Now I can see the page line over here. So within that page line, I can design my first page form, and now I know my limitation. I will select the complete area of that page and then I will merge it. And then we will type form R two, center, and in a similar way, I will select like this. This time, instead of copy paste, I will select Control D. So if you use the Control D, what will happen? The top cells data will be copied to the current selected cell. So in that case, it was form where two. I had mistaken typing, so press F two and do the rectification form tar two. So what I did, I just selected the area and then press Control D, that will copy the data from the top row in the current selected cell. Now I can delete, I can type C Rule 16 subsection two. Now we have dt over here so we can type D, pretap D, then M, I need to leave some space. And then Y, Y. So just select all the data, control X, and then we can move or we have one more trick to moving the data, select the cells, and now put your cursor on these lines. So if the cursor has been changed like this, so you have to just click and drag to the desired area. I'm going to select once again, make it center and then here I would like to type origin duplicate. This time, I'm going to select Control C, click over here, and then use spatial to paste, and then I have to enter. I need to give some space. Instead of space, I will type and as dealers. Identity. I'm going to use this option and make it bold, and then I will type name and style of business, then address, then tin, change the name, F to press home, then N and change and I will select like this, and then I will select this area. I would like to increase the area so that you can see. Now for the border, I have to select this area and come to Home tab, select border and select outside border. And in the date side, I have to select complete area and then come to this all border. And from here, I need to select the area and then outside only. And in the address here we have to enter contact number, and this will be outside area, and this is also outside area and then 1006, make it center and select this area and select all border, then economic activity code. Select this area outside border, and here all border. Now moving to the next part, which is description. Instead of description, I would start from here, list and one, two, three. I will use two rows and I will increase the width of second row like this and I will select this area and then I will merge it and this is the text I'm going to copy, click and paste and here we have to merge the B value of goods. You can decrease the size of the font and then on the top, we have to merge all the area and type A description center, then increase the indent and this area we have to merge left and then copy this area and then paste and then copy the next line. Again, coming over here, come to the last point, click Enter, select that cell, click on rap text, decrease the size. It is adjusted now, and here I need to type A and click on this. I think we need to adjust this area a bit more no. So we can increase the height to adjust looking quite good. Now it's time to put the border. Again, I'm going to select this one. Then outside this time, I'm going to press a four F four and four, and here four, here also a four, Control S, I'm going to remove grid lines for some time, selecting this one and then come over here all border and this area, this all border. Sorry. Here, we need to select outside border and fill with the color, select this area, select this option to fill the color, and here two B, then dductions select this area and outside and then outside by using four and we will merge this area by using this and using this, I need to see the grid lines. I will select this area and click over here. And I will merge this area as well, merge and left, and this will be same. I'm going to select this area and then bottom border. Now I'm going to copy, select four rows, control V, and this area, I would like to put outside border outside border and here outside border, save. And then you can type like this, copy, paste. And if I have extra space, we can delete by selecting this and presenter, and now I will enter one end. You will use this type of information, sometimes you will get the wrong information. Due to Excel will consider number in other way. So if you are entering any number within the bracket, so always use this sign, then type anything, it will considered as text. So you can copy and paste, and then you can change the number four. And you can copy paste all the data over here. So now I'm going to see the preview. It looking quite good. And now, what you have to do, I have completed my task. Now it is your time to do the practice and you have to design this form again or complete to all the pages. So that is very good practice for designing any form. So thank you for watching. If you have any problem, just let me know. I'll try my best to give the answer as soon as possible. Thank you. 12. DATA TYPES IN EXCEL: Van. Welcome once again. In today's video, we are going to discuss about the data types used in the Excel. We already discussed in the Chapter one, but here also, I'm going to explain data types. Basically, there are three data types used in the Excel. The first is numeric values such as numeric values. These all values are used to do the calculation in excel such as some addition, deletion. We will use these number values to the more complex functions or the calculations using the inbuilt functions of the axle. We will also use text type, that is our second data types. We use the characters display in the strings. Basically, text data include any combination of letters and numbers like names and addresses, et cetera. The third and the last one is date and time values. These let you record your specific date and hours making it easy to track, schedule or dat lines. So by using these three data types, you can organize, analyze and display your information effectively in Excel. Let me explain one more thing. Dates and the time values will be recorded in numeric values, but displayed in the date and the time format. For example, I have entered one date format over here. We used date then a separator, then month and the year and time in this format. We can enter like this. Even we can enter date with the time in the excel. These all are numeric values, let me display. First of all, I have to select any particular date, now come to the home tab, then come to the number group and select this option and select general. Each date have separate unique number values. This is the internal values. But if you want to convert any number into date, just you have to select and go to the format, select long or short date type or we can get more options related to the display of date and time we can select like this and we have so many options over here we can select and once we are done, click Okay to display that number in particular format. Let's say I'm going to display this number into a date, so that would be changed like this. So this time is also represented in the background as a number, so I'm selecting this one and going to general. So that is in the numeric field only. So because we want to do some calculation using the dates, sometimes we want to calculate the days between particular dates. So we have to convert and use this format in the number values only then only algorithm of the Axl can do the simple or complex calculation. So these you have to use in the excel, so you have to remember these three datypes in the ax. 13. INSERT SPECIAL CHARACTERS: Hello, everyone. In this video, we will discuss about special characters in Excel. These special character in Excel are symbols that are not letters or numbers, but can still be entered into Excel. These include punctuation mark like comas, periods, and quotation marks, as well as symbols like at the rate of percent, dollar sign, percentage sin, and many more. Special Correct can be used to format data, create labels, or add emphasis. For example, you might use $1 sign to represent money and imposit to join two text strings or an asterix sign to highlight important information. Spatial characters can also be useful in formulas and functions to perform specific tasks. Like separating values or indicating range. By understanding and using spatial characters, you can make your Excel data more organized and easier to read. These are some special characters which we can directly use from the keyboard, but some spatial characters are not available in the keyboard, but we can still insert into our Excel cells. So where we can find these special characters, just come to the Insert tab. On the very left hand side, we have symbols. Click on it. Here we have one dialog box called symbols. Here we have two options, symbols and spatial characters. So these are the spatial characters we can insert directly from here. Okay. Let's say sometimes we want to use the copyright or the trademark. So we can use directly from here, double click. It will be inserted in the selected cell. We want to use any particular symbol which is not available in the keyboard, so we can use from here. On the bottom, we can see recently used symbols. We have to just select and click on insert or just double click on it. It will be inserted as many as times you want. So we have different font types available. So if you select, so these are the different font types we have. So we can use from here as well. So from here, normally we use symbols only. So let's say we want to use this one, just double click, it will be inserted. I'm going to display one more option. For example, my V tutorial is a trademark. So now I want to insert. You can see all other tools are grade out, but only symbols are available because I'm editing mode in the cell. So I'm selecting symbol. Now I can use trademark or the copyright and then insert and then close press Enter, that will be added like this. So this is the special character we can use. In the next video, we will discuss some more options related to the Excel. Thank you. 14. AUTOCORRECT IN EXCEL: Hello, everyone. In this video, we will discuss about auto correct option in Excel. Auto correct in Excel is a feature that automatically correct common typing mistakes and misspelled words as you type. It helps you to save time and avoid errors by fixing things like capitalization mistake and common misspelled words. For example, if you type TH instead of the. Let's take example TH. If I type TH, but I'm going to type the, if I press tab, it will be corrected automatically. Similarly, if I type some other words which is included in the option of Autocorrect in Excel in the background, then only it will be corrected. So this feature is very useful for ensuring that your data entries are accurate and consistent, making your work in Excel smoother and more efficient. So let's go to the background of the Excel. For this, we have to use file option, Guru option, and from here, we will come to the proofing option, and here we have autocorrect option. In the auto correct options, we have three options. First is autocorrect, autofmat Azo type actions and the math correct. Here we will discuss about autocorrect only. So let's say we have some options, so autocorrect option button. If we want to enable this option, so we can use this one, unable this option, correct two initial capital letters, then capitalize first letter of sentence, capitalize name of the days. So these are some keys which we can replace automatically. Here, whenever we type something, this is a replace and width. It means whenever we type small small brackets, so it will be converted into the copyright. If we type E, it will be converted in the urosgn and trademark, if we type like this, if you go down, we will have all the replacement width. Let's say if I type ACN it will be converted into the can automatically. If really we want ACN, then we have to type once again and press Enter, then it will not convert. So we can also define our own words. Let's say my company name is tutorial, I want to replace it with it. Whenever I type t and press space, it must be converted into the tutorial. So I'm going to add t and here I would like to type Weir tutorial and click on Add Button. It will be added over here, now click Okay and Okay. So now whenever I type VTN press space, it will be converted into the V tutorial. That is very good option whenever you want to enter a specific line everyday tasks. So you can use a small combination of some characters or some numbers. So whenever you enter that particular combination of characters, your full line. It can be a paragraph, your company name, your address, your email ID, or anything which you can think useful for you. So this is a very good option. You must use it. 15. AUTO SUGGETIONS FOR DATA ENTRY: Hello, everyone. In today's video, we will discuss about a very good feature in MSExl called Auto suggestion in Axl, which is actually a timesaver. Auto suggestion in Axl is a feature that automatically completes a text entry based on the previous entries in the same column. It's a handy to speed up data entry and reduce errors, especially when dealing with repetive data. Let me explain how it works. As you start typing in a cell, Excel looks for matching entries in the column above. If it finds i match, it suggests completing the entry, you can accept the suggestion by pressing Enter or select a different option from the drop down list. Let me explain. I have full name in this column. Now I would like to add some more data. Maybe I would like to use the same name again here. So let's see how this auto suggestion work in Excel. So first, I would like to enter Benjamin Turmer. When I start typing, it suggests me a name. If I would like to enter the same name, then I can press Enter or I can use a drop down list. For this, you have to press Alt down Aeroke I will get the list of all names available on the column above. I can select as per my choice. Let's say in this case, it is Lily Green and I can press Inter. We can see David Wilson and David Smith. Let's type the David. I'm not getting any suggestion because David is coming two times. But whenever I start David WIL, I'm getting suggestions from the Excel whether you want to use David Wilson or not. Yes, I would like to use David Wilson. I'm accepting by pressing Inter. So this is a very good option for data entry, which can speed up your data entry, reduce errors, enforce data inconsistency, and improve your efficiency. You must use it in your daily routine. 16. AUTOFILL: Hello, everyone. Welcome, once again. In today's video, we will discuss about the autopil in Exile. This autofil option in Excel is a feature that help you to quickly fill cell with a series of data based on the pattern you established. It saves times by automatically continuing the sequence you have started. For example, if you have entered January in one cell and February in the next cell, you can use autofil to drag and fill the following cell with the March. So autofil can be used for date times, text patterns, or even formulas, making it powerful tool for efficiently managing data in the Al. Let's begin with the example. I have a table over here, and here I need to enter the serial number, one, two, three, and so on. We have only few rows of data, but just think whenever we have 10,000 rows of data, how difficult it is to enter the manual serial number. For example, if I enter one, two, three, four, then I have to type. But Excel provide autofil option that will solve your problem. So there is no need to enter so much of data. Just select the data. First, I'm going to it center the alignment. So first, we have Enter one or two. These are the numeric values. Now I want to generate rest of the numbers, so I have to come over here this plus sign. The cursor will be changed into the plus sign, and we have to click and drag to the downside up to the last row. So system will automatically generate a series. So now we have series in which only the value of one will be added. But if we have different series, we can create. I'm going to delete. Let's say I entered five over here, if I select this one, if I drag to the last one. So each number will be increased by the four. So one, five, nine, like this. I'm going to under. So Excel will consider the series automatically in case of numbers. So we can also use it for month and the weekdays, let's say I entered Sunday and Monday and if I select and if I drag to the downside. So we can see the preview of the list as well, generated by automatically. If I release the mouse, the series will be generated. Similarly, we can create January fab, and it is in the short form as well. But since how system is getting these series automatically, that is already filled in the background. I'll show you. But before that, I want to show you one more very good example. Let's say we have 10,000 of rows. So it is very difficult and time consuming to drag your mouse to the downside. So just type your series, select the data, and just double click on it. It will move to the last cell. Let me show you in the sample file. So here are the orders data in which I have 10,000 rows of data. So now I need to enter my series. So I will type one, then two, I will select it and just double click. That's all we need to do. Now press Control down heroky to come down. All data will be filled automatically. So that's very great feature. Now let me explain. I'm going to close this one. Let me show you where we can maintain our series. So you have to go to the file, then options and come to the advance tab and come down. And here we have general category, select Addit custom list. Here we can manage our list Monday, Sunday, January, February. That is the text series which already entered over here. We can manage any series from here. Let's say I want to manage from these 23 words. If I enter and import, it will be inserted over here, and then add and then okay. Next time, click Okay. Next time, whenever I use this one and select, sorry, select and drag, the third name come automatically. So this is just an example. So you can create your own custom series if you want. And one more very good opera example over here. Let's say I change the color. I did some formatting like this, not looking good. I'm increasing the size of the font. Now I used format painter. The format painter is very good option to copy paste the formatting of any particular cell. Let's say I want to copy the formatting of this cell. I just select this one and if I select another cell, this will be applied automatically. I'm going to display my example related to the fail. So this time, I am going to remove this one, this one as well, and I'm going to select this one. And if I drag to the downside, I will have some options if I release. I will have one more option. If I click over here, so let's move to the oposide and now I have some options, copy the cells only. So fill the data without formatting. If I select this one, so we will not get the formatting. We got only the series. I'm going to drag once again, just click over here. If I fill months only, and this is the flash fill which we are going to cover in the next video. So these are the options for autofil you can use. It's very good for time saving whenever we need to enter the data automatically. 17. FLASH FILL: Hello, everyone. Welcome, once again, in today's video, we will discuss about the flesh fill option, which is a part of autofill, which we discussed in the last video. Flesh fill in Excel is a tool that help you automatically fill in data based on the pattern you provided. It is very useful for quickly formatting and organizing data without using complicated formulas. For example, I have complete name in the column A, and I need to extract the first name, last name, and the initials from here. I can use text functions provided by the Excel, but Excel also provided flash fill. That is very useful in these cases. So let's say first, I just type the first name in the same manner. And once I press Enter, it will move to the next cell downside. Again, I will enter the same. So the name is same, so I will accept and I press Enter and now move to the third S and press Control E from here. You will see the magic. It's amazing. If you cannot see the option of las fay this is the shortcut key, actually, press Undo and then come to the DataTab and select the flash fill from the Data tool option. Click over here, that will be worked like a magic. Now the last name, I will type the same and then the second one. And this time, we got the suggestions, autofil suggestions provided by the Microsoft. That is very useful. And once we think it is everything which we want, then presenter accept it. If you go down, all data will be filled automatically. Now I want to retrieve the initials means the first character for both first and the last name, then A and then B presenter, then A H presenter. Case you will not receive the suggestions, then select fill option and see the magic. We can also use this in the numeric values. For example, I want to extract the first four characters from here. So this time, I'm going to use this one and then 16 18, and we got the suggestions based on the data provided. But one thing you have to remember over here is that this is not dynamic. It means whenever you change any data from here, it will not convert it over here automatically. This is static data, and this is not dynamic data. Say we want to extract the date from here. So let's say I want to extract here. So we got the suggestion. In case we will not get, then again, come to the data type flash Vail. Boom. So that is very useful for extracting the data. You must use it for your data analysis. So if you have any question related to that, just let me know. I'll provide my answer as soon as possible. Thank you, friends. 18. INSERT AND DELETE ROWS AND COLUMN: Hello, everyone. In this video, we will learn how to insert or delete columns or row and shortcuts. Here I have table. Now I want to insert the row on the left hand side. So the first way and the easiest way is to just use your mouse. Just select this column and then right click and then we can click on insert it. Then we will get the column in between. And for deleting, just right click and select the delete button. Similarly, if you want to insert a row, select a row, right click and insert, right click and delete. If you want to insert multiple on the left hand side columns, select the multiple columns, right click insert, and similarly, select the multiple columns, then right click and delete. Similarly, select the multiple rows, right click insert rows above, and then select the multiple rows and right click and delete. Now we will use shortcut keys for selecting a row from the keyboard, the shortcut key is control space, press shift right or left arrow keys to select the multiple columns to the right or the left. Now press Control Shift plus to insert and for deleting Control plus minus sign. For selecting a row, use shift space, then shift up and down Aoki to select upper side or the lower side of rows, and for inserting the new rows on the above, select Control Shift plus or Control minus to delete. If you do not want to select the row or column, you can use simply Control Shift plus sign to a cell. We will get the insert pop up and select what you want to do. Let's say I want to insert entire row, select this option and presenter or simply press R from the keyboard and presenter the new row above will be inserted. If you want to delete, Control minus sign, select the entire row, and you can see the heading is delete right now and press Enter. We can also insert the cell. For this, we can use Control Shift plus sign, and we can shift the cell to the right. Select this option and click Okay. Cell will be selected. Control and if I selected multiple cells, press Control Shift plus, and shift cell to the right, these all selected cell will be moved to the right hand side, do. If I want to move to the downside, select sell down, select this option and presenter. All cell will be moved to the down side. Control Z, if I want to delete, then select Control minus sign, sorry, Control minus sine. And this time we can shift cell to the left hand side, so all will be deleted. Do I I'm selecting over here and press Control minus shift to the left. So the data in the selected cell will be deleted and data from the right hand side move to the left hand side. Undo the same will happen to the downside, shift cell up. So the current selected cells will be deleted and the data bring up from the selected cells. Press Ndo save. These are the shortcut keys using the keyboard and the mouse, we can also do the same by using this option, come to the home tab and come over here cells. Then after selecting the particular row column or the cell, you can take the action. You can insert cell. You can insert sheet rows or you can insert sheet columns. Let's say I want to insert the row, click Okay, then all row will be inserted, undo. We can insert column do. We can also do the same by using delete. We can delete the row column and we can delete the sheet from here. These are the option related to the inserting or deleting the row or column in the Excel sheet. 19. HIDE UNHIDE ROWS COLUMNS: Hello, everyone. Welcome once again. In this video, we will discuss how to hide and hide the row and columns in Excel. Whenever we have large data, sometimes for display or for analysis purpose, we have to hide some columns or rows. For this, we can use our mouse or the keyboard shortcuts or the tab options. First of all, we will discuss mouse option. Simply select the single or the multiple columns you want to hide. Let's say I want to hide order details. Then right click and here we have option height. Once you click on it, we can see A, B, and then directly E. So D C or D column is hidden right now. So for unhide, we have to select B and E and then right click and unhide. In a similar way, we can hide or unhide single or multiple rows. I'm selecting multiple rows, right click and click on Height. Select the rows five and 11 because all the rows in between these rows are hidden. Right click and then click on Unhide all rows will be unhided. So we can use the shortcut key. First of all, we have to select the column by pressing Control Space bar the shortcut key for hide any column, single or multiple, select those rows. Let's say I want to hide these columns. So I selected the cells only this time, and we can press Control zero this time to hide. And if I want to unhide to unhide, we have to select and then right click and unhide, there is no shortcut key for unhide. We can hide the row as well. I'm going to select multiple cell. Then I'm selecting Control nine to hide the row. And for unhide, we have to select by using shift with Space bar and then right click and Unhide. We can use the option from here as well. So let's say I want to hide this particular column. After selecting the column, come to the home tab, then format, and from here, hide and height and height columns. For unhide, we have to select, then format and then unhide. Column, similar way, we can hide the row and we can unhide after selection, come to the format, hide anhid and unhide rose. 20. SPREADSHEET MANAGEMENT: Hello, everyone. Welcome once again. In today's video, we will discuss about the worksheet management. As you aware that workbook contain multiple worksheet. This is the filename of this sheet is datatype. That is a complete workbook in which we have multiple spreadsheet or the worksheet, we can say, and those are available over here. If you click, then the sheet will appear, and then we can do our calculation. We can enter our data over here. So now in this video, we will discuss how we will manage and what are the different options related to these worksheets. If you right click on any worksheet, we have multiple options called the first one is insert. If you select this one, a new dialog box will appear. From here, we can create a new worksheet. So these are the multiple options. Don't worry about it. Just select a simple worksheet over here, a blank worksheet will be inserted. And we can go from the templates from here. We can select time card, sales report, personal loan, loans. So these all are available. If you want to download more template, then go to this link and you can download. Which we already discussed in the earlier videos. So I'm going to cancel this one. And if you want to delete this right click and select delete, it will ask for deletion, then you have to select, then it will be deleted. We can rename by using this option and we can move and copy. This option is very important. Then select this one and move and copy dialogues will appear. From here, we can select before sheet name. Let's say I want to make a copy of flash fill and where I want to insert it before data type, before spatial Cctor or before fill autofill list. Select that particular sheet, select create a copy over here and click Okay. Now we have a duplicate flash finch sheet over here. And just before the autofil list. And now I'm going to delete by using this option. So we got this dialog box, I can delete. Make sure before deleting, there is no option for retrieving the sheet. We cannot undo this action, so that will be deleted permanently. And right click again, come to the move and copy. And this time, if you want to move this data types or any particular worksheet to open workbook. Workbook mean a separate file, which is open already. If the file is not open, then you cannot transfer. Just click over here, the list of all open Wbol will appear, then we have to select. So the file in which we are working is data type. The next which is open is sample Superstone, select that one and select the sheet from we want to appear. You can create a copy by selecting this option. If you disable this option, that this particular spreadsheet will be moved to the worksheet we have selected here. And right now, I'm going to create a copy, so I'm selecting this option and click Okay, and that will be moved over here, the flashFlFle. So we can delete from here because I do not want. And we can close by pressing Control W. That is a shortcut key, and you have to select Save option, and here also we can save and then press Control W. These all options are also available in the tab as well. Come to the home tab. Come to the insert for inserting a new sheet. The same option will appear. So you can delete by using this option, select delete, and we can have more options here, like rename sheet, move and copy, and tab color. That is also a very good option. Let's say we have different type of sheets and we want to bifurcate by using the color, so I can use color from here and the above option. From here format and come to the tab color and we can select a particular color like this. Or also, we can hide a particular worksheet, this right click and click on Hide. If you want to unhide, then right click and unhide, we will get the list of hidden sheets, we have to select and then press Okay. All these options are also available over here. Hide N and hide the sheet. Once you hide any sheet, then only the unhide sheet option will be appear. 21. FIND AND REPLAC: Hello, everyone. In this video, we are going to discuss about the find feature. That is a very good option. The find feature in Excel help you to quickly look at specific data in your spreadsheet. You can use it to search text, number, or even special characters. So to use Find simply press Control F, which is the shortcut key or from the home tab menu come to the last Find and Replace and click on this find option. So that find and replace option will appear. Here we have two options, find and replace, and first we will discuss about the find. So you need to just type your keyword for which you want to search. Let's say, in this case, I want to search any name like Emily and press Enter, that cell will be selected automatically. If you find next, just press Enter, so you will find another cell or another cell. These are the options. It's very easy. You can simply enter any date and particular number. Let's say I want to select this number. So wherever it is, the cell will be selected. You can click once again or press Enter to search another cell. But if it will be not available, then it will be here only. And we have some advanced options for searching, just click on this option button. So here, let me explain all the options one by one. The first one is the search criteria. Win. It means just click over here, we have sheet or the workbook, where we want to search within this sheet, the selected sheet only, or in the complete, the entire workbook will be searched for that particular keyword or number or the text whichever we have entered over here. Okay. So this is the first thing, and then we have search by how we want to search by row or the column means, for example, I have entered this number. First, row will be searched like this, then it will move to the next row. We can also search by column if we want. Where we want to look this value in the formula, value or the command. We will discuss about the command as well. But if you have already completed our MS Word course, we already discussed about the command. But here we have a little different option for searching. So we can select comment. But in the Excel, we have a different option for commenting. So you can select formula, value or comment. Also we can select the match case, whether it's in the capital letters, small letter, proper letters. By default, if it is disabled, then it will not search by the case and whether you want the entire cell contained. Let's take one example. I have entered 242.48 if I remove this and if I search, wherever our system will search 24, whether it's complete or the entire cell or partially. It will display. But if I select this option, make entire sell content, then if I have 24 in any cell, it will search. Yes, we have the entire. Can also set the formatting, select this option and select the color, font, border, or any type of formatting we can select. So that is very advanced option. Now I'm going to explain about the replace. I'm going to disable this option. I'm going to disable this option as well. And now we are moving to the replace option. So here we have two options, find what and replace with. So we can find any particular keyword and then we can replace it by a particular number, word or anything. Let's say I have corporate over here, I'm just going to copy and I'm going to paste over here. I want to change it to the LLC. Now click on replace. It got replaced, replace, replace, replace. If I select replace all, all will be replaced in single go. Total 3,017 replacement has been done by the Microsoft Excel. See how easy it is. We also have advanced options which we already discussed over here. So if you click over here and you can select and then change, you can do the find and replacement. That is very good option. We will discuss some more options in coming videos. Thank you for watching. 22. INTRODUCTION OF CALCULATION: Hello, everyone. Welcome, once again. In this section, we are going to start functions or formulas, how to use function and formulas in Excel. As we have learned in our school, we use some symbols to do mathematical calculation, for example, plus for addition, minus for substraction. In Excel, we also use same symbols such as plus for addition, minus for substraction, at in for multiplication, division, percentage, exponential. Then for comparison, we use equal to less than equals to or so on. We have already do some basic calculations in our earlier videos. Here also, I will show you. Let's say I want to do the addition of two plus two. I have written two plus two in two different cells. Now simply I can do the calculation. This can be done in two ways. First, I will write both the numbers in a particular cell by using eqosine. Already explained whenever we start any function in built function or formula or do any calculation, we always start by eqosine in the cell. So we have selected F six cell. Now we are going to start ecosine and here I can type my number. It is very similar to the calculator. When we enter the number in the calculator, we type two, then we type plus sign, then we type second number. In this case, it is two only. We got our answer when we press Enter or Control Enter four. So this is the one way. But here, the problem is whenever I want to change or do the new calculation, every time I have to edit that formula. Press F two, four, and then edit the number and press Control Enter or Enter, then we will get the answer. But in order to get the dynamic results, it means what I want whenever I will change the values in this cell, the calculation will be done automatically, and I should get the dynamic results immediately. How can we achieve that? In that case, we can give the reference of the cell, as we know that every cell has a name, which we can see over here, F four. The cell we have selected is F four. This is F five. So we can give the reference or the link in our formula. So here I'm going to cut and paste here, and here I'm going to enter my calculation by starting equal sine. After that, I can use my up Aoki to move that cell. We can see in the blue color F four has been written. Now type plus sine and give the reference of F five as well, and now press Control Enter, we got the same results. But this time we have given the reference means the link of that particular cell. So whenever I change the value of any cell, we will get the dynamic result in this cell. I'm going to change the value like six. Here, if I change the value, the answer will be changed in the next cell as well. So this is the beauty and the benefit of using the excel. We will get the desired result in dynamic way. This is the basic calculation we have done. In the next video, we will learn how to use our function in multiple calculations. 23. USE OF INBUILT FUNCTION: Hello, everyone. Welcome to the new video. In this section or video, we will learn how to copy or paste the formula used in single calculation. Let's take the example. We have quantity and the rate over here. So we would like to calculate the total amount in order to get this value. As we know that we have to multiply quantity and rate. And we have value in different cell. And in the last cell, we want to calculate the total of the amount as well. This must be dynamic means whenever we change any value in the quantity and rate over here, that should be changed here as well. So as you know that we discussed here also I will start by equalsine then go to J five, the first quantity, then we will use Asterix sine to do the multiplication. Then we can click on K five, and now press Control Enter, we got our result. As we discussed the use of autofill option in Excel, where we use to drag or stretch the series of number on Sunday, Monday, the data will be filled automatically. Here also we can do the same. So we have created our first function in the first cell. Now just come to that point, our cursor will change to plus sign, and we can just double click or stretch to the last cell, the function, and the links will be changed automatically. If I go to the next cell, let's say here, if I press F two, I can see the link. J six, the reference of the cell has been changed automatically. Similarly, the reference of the rate has been also changed. So this is a beauty of X we use function in first cell, and then we dreg the function to the last cell and we got our value. We have some another ways to do that. I'm removing after selection, select the cell and then double click. This is the easiest way actually and press delete. Now just copy and paste over here. And at the last, I have used in built function called sum, which is used to do the addition of a range of cells. So this time, I'm going to use over here. So what should I do? I will start from ecosy. Then I will start typing SUM. As I started typing the function, we can see the suggestion list of all related functions by the name of sum. So we are going to use sum in this case, so we can press tab from the keyboard. And here we have to give the argument. I will explain about these functions and their arguments one by one. So here we can see the number one number two, number three. So we can give like this, number one, then, number two, then coma number three, or simply we can give the range of particular cell. So we are going to select L four, two, L 11 and then close the bracket and press Control Enter to get the sum automatically. Now if I change the value, let's say here I change the value, so we can see the amount has been changed and total is also changed automatically. So this is the way we can create a simple calculation. In coming videos, we will dive into the complex calculations and we will use multiple functions in a formula. So stay tuned to the next video. 24. CELL ABSOLUTE REFERENCE: Hello, everyone. Welcome once again. In today's video, we will discuss about cell and range references. In the previous section or video, we discuss about the calculation of some numbers in which we have given the references. So this is the advanced version of giving the references. So you must give your full attention because that is very important for doing the calculation. There are four type of references we can give. The first one is relative reference. That reference is fully relative. When you copy the function or formula, the cell reference adjust to the new location. For example, here, we have to calculate the subtotal amount. So we started by equosy. We give the reference of one cell, then rate, then control enter, copy, and paste. So here, this is a complete relative reference. It means the reference will change automatically whenever we copy the function to the new cell. The next, which is very important, the absolute reference. The reference is fully absolute when you copy the formula. The cell reference does not change. It means whenever we give any reference of the cell, the reference will not change whenever we copy the function or formula in another cell. So let's take the example over here we have calculated the subtotal. Now we have to calculate the sales text, and the text rate has been given in the L three cell. If I use relative reference, what should I do? I give the subtotal amount, multiply it by the percentage and press Control plus Enter. And if I copy and paste, I'm using the relative reference. You can see in the first cell, I'm getting the correct amount, but in the second result, I'm not getting the desired result because the reference of the L three has been changed to the L four, but we have to fix this. So for that, we will use absolute reference. We will start our formula using equal sine, give the reference of subtotal, then Etic sine for multiplication, then come to the L three, and this time we are going to use F four. You have to note down this for make absolute reference or lock the reference. We always use F four, press F four, and you can see $2 sign has been assigned to this value before the L and before the three. It means that cell reference has been locked. So whenever we copy that function to another cell, the reference of the six will be changed because that is relative reference. But L three, which is related to the sale tax will not change. I'm going to press Control plus Enter, then copy and paste. And if I press F two, you can see there is no change in the L three. It is locked. And now we can use the relative reference to do the total amount. So that's very easy. Now we are moving to our third, which is row absolute, and this is the example. So I'm going to teach this using the example. Here I have month wise sales of city and here are the incentive which we have to give to the related salesperson. So here, if I use relative or the locked function, if I use this as a lock means the absolute function, and sorry. And now I give the reference of city one. And if I copy and paste over here, the calculation is perfect. But whenever I copy this here, so you can see the reference of the city has been changed. But the rate of incentive, which was in the L 12 is fixed, but we do not want like this. We have to move to the next column. So for next column, if we want, so we have to remove lock from here. I'm going to remove all the formulas. I will start from scratch. So I'm going over here. I'm pressing F four, and I'm removing my dollar sign from the L. Now, tic sign for multiplication, select the cell reference for sales of city one, which is L 14 and now press Control Enter and we can change this are using this. Now copy and paste. Let's see what we are getting. So we are getting perfect result. And if I copy this to remaining all, let's see whether we are getting the perfect result or not. Yes, we are getting perfect result. So what we did, we put the lock in the row. So that is that means it becomes the row absolute. So the rows are not changing, but columns are changing. Now we are coming to our next example which is column absolute. So it is the just opposite of the row absolute. In the column absolute, we have to calculate the amount from here. Let's see, I want to calculate the multiplication of amount in the number one. In this case, if I press and copy paste, I'm getting the perfect information which I want. But now, amount must be same, and here I want to apply the number two column, which is seven. So if I copy and paste, it is relative reference. That is why everything has been changed. But if I use column absolute. It means here, if I lock this one by pressing F four, and I'm removing dollar sign from here. This time, the column would be fixed, but row will not be fixed. I'm pressing, sorry, I have to give the reference of multiplication to q 12, press Control Inter. Now if I copy paste, I'm getting all the results as per my desire. This is how you can use Row absolute, column absolute, and the absolute. That is very important. You have to do the practice and your concept must be clear about these references. Otherwise, you will not get the desired result. If you have any query related to that, just let me know. I will try my best to give the answer as soon as possible, and you can do the practice by download this formula file and sheet number cell reference. That is very important, actually. 25. ERROR IN FORMULA: Hello, everyone. Welcome, once again, in today's video, we will discuss about the error in formulas in Microsoft Excel. Whenever we use any function or formula, it may possible we will get the error. So these are the all errors available or you may face when we give the wrong reference, or there are so many reasons which I'm explaining in this video. The first one is dev zero. Whenever we divide any value with the zero, we will get that answer. As we know that we will get the indefinite answer any amount divided by zero, so we get that error. So whenever you face this error, it means there is some mistake and you have done wrong calculation. The next one is the formula uses a name that Excels doesn't recognize. We will discuss how we can assign a custom name to a particular cell or the range of the cell. For an example, I have written 1,000 over here and the cell name is L five, which is written over here, we can assign a custom name. Let's say, in this case, I'm going to assign amount, and now I'm going to divide this by 20. That is very easy. We can refer AMT. We can see over here, so it will take the amount, but instead of AMT, I have by mistake, I return ANT and divide it by 20 if I press Control Enter, so I will get the name error. So this is the second error, and this one is whenever the formula refer directly or indirectly to a cell that uses the not applicable function to signal unavailable data. That require a lot of data, which I will explain in coming videos whenever we face such problem. The next one is null. The formula uses an intersection of two ranges that don't intersect. That I will also explain in coming sections whenever we face such problem. The next one is num a problem occurs with a value. That one also will be explained later on, and this is the formula referred in invalid cell. Yes, this one, we can explain over here. Let's say I have one value over here and second value over here, and I need to do some calculation like this. And we got our result. But by mistake or intentionally, if I delete any reference cell, so we will get this type of error. That time, the ref error is coming. It means the reference to the cell is invalid. The next one is the formula, include an argument or operate of the wrong type. Let's say I have one value over here and I have written a wrong. Instead of zero, I typed O. And if I divide with this type, I will get the value. So we cannot divide a number value with the text value. We always need a numeric value to do the calculation. These are the error types you may face in the Excel. So that is also very important for learning if you want to use Excel properly. 26. CIRCULAR REFERENCES: Hello, and welcome once again. In this video, we will discuss about the circular references. That circular references occurs when a formula referred to its own value, either directly or indirectly. So what is it? First of all, we have to understand what is the circular references. Let's say we have some values and we calculated the sum of the. So the shortcut key for calculating the sum is t equal, and then press Control Enter. And here, I have to divide. I am just calculating one more value that depend on the sum of this, and it is divided by the five. Now this calculation depends on these values. These are the main values, then we did the sum and we calculated that about. But if I remove some value from here and if I give the reference of this value, that can be any type. It may be multiplication, addition, or any, so that creates a circular reference error. Let's say I'm going to multiply it by one if I press Enter right now, so I will get the error. Because actually we cannot do the calculation because this cell value depends on this value and the value of this cell is depend on this data, and we did the calculation that depends on the this calculation. So that is actually wrong. So that is why we are getting error. If I press Okay, so we will get this error and circular reference, so we have to rectify this to get the correct result. So I'm going to remove and if I press Okay, and safe, then I will get the proper result and I will not get any error. You have to take care always. Whenever you get this type of error, you have to check it carefully. 27. VALUE PASTE: Hello, everyone. Welcome once again. In today's video, we will discuss about the value paste. We discussed in previous videos that we can copy paste the value of particular cell. If we have any formula in it, that will be copied to the next cell or wherever we are going to paste. But sometimes we have to paste the values, not the function or the formula we have used. Say, here we calculated the subtotal in which if I press F two, here we can see we have given the reference of C and D different cells, and here we are doing some calculation. Now, I want to paste these value somewhere else, maybe in this sheet or some other sheet or other workbook, but as a value, not the formula. For this, we can use the value paste option from the paypatial option, which is available in home tab. So first of all, we have to select a cell where we are going to paste and now come to the home tab. Under the home tab, we have clipboard option. In the clipboard, we have paste. If I just click over here and use Control V, the function or the formula we have used will be paste. But we have to use the value only. Just click over here and you can use this option. Paste value as value and the source formatting. Or we can use value if we want value, then we can use value and the number formatting, and this is used for the value and the source formatting. And if we click on this spatial value, we have passpatial option. From here, simply select the value and click Okay. And if you select this cell, we can see there is no reference. These all will be treated as separate values in the cells where we pasted these values. 28. DEFINE NAME OF CELL AND CELL RANGE: Hello, everyone. Welcome, once again, in this section, we are going to start named range. The named range in Excel are a powerful feature that allows you to assign a named to specific cell or range of cells. This can simplify the process of referencing those cells in the formulas and enhance the readability of your spreadsheet. Let's take the example. We can define the name of any cell, cell range, shape, chart, and so on. In day to day life, everybody has a name. In a similar way, we can define the name of any cell cell ranges in the excel for their identification. Excel provided the identification of any cell in the cell ranges. For example, the cell I have selected, having the name F three, which we can see over here, if you hover the mouse, we can see the name box, and here the name is F three. But we can also define any name as per our choice. Let's take one example for better understanding. We have reason wise sales over here and the commission rate over here. The sale address or the name is C 17, c17. Now I can define the custom name which explain its purpose for better understanding. Then I can use the reference of that name in any function or formula in this worksheet. Not only worksheet that can be used in complete workbook. The scope of uses can be defined in the name manager, which you can find in formula tab. Come on the defined name option. Here we have name manager. First of all, we have to define that's very easy. For easy understanding, first, we will take a very basic simple example. We will define the name of this commission, select that cell and come and click over here and type any name. For example, commission rate. Commission rate. It should start from a number or text, and there should not be any space. Once you click Enter, that is defined. Now come to formulas tab and click on Name Manager. We can see the name manager over here, and this is referred to. Concept means the sheet name and the cell name. Then we have to close this one. Now, traditionally, how we calculate the commission, equal sign, we select the reference L, then multiply and we select this, and we can see now C 70s is not coming. Their name is coming. COM rate, which we defined over here. When we press Enter, and if I copy and paste, we will get our desired results. There is no need to define any constant, there is no need to define dollar sign which we discussed previously. Now we will see one more example. Now we are going to define the name of the range. This is the total sales, so I need to identify the commission on all sales. So what we can do, we just select the range of the sales, then come over here, then we can define the sales data presenter. Now we have to calculate the commission. So first of all, we have to calculate the sum, then we will apply commission on it. For this, first of all, we will use sum, and now we will type the sales data. We can see the icon before the sale data, and it is coming in the suggestion, function formula or the ranges. So press Tab to accept this and close the bracket, then multiply COM. Rate. We can start typing directly instead of giving the references by clicking on the cell. Then we have to press Control, Enter to accept it, and we can see 7,500 commission is coming. If I do the sum of this commission, it must be the same. So this is how we can use named ranges in the Axl. We will discuss more about in details incoming videos. Straight 29. DEFINE NAME USING NAME MANAGER: Hello, everyone. Welcome once again. In the last video, we discuss about defining the name. In this video, we will define the name using the name manager. Come to the formula tab, define name, and click on Name Manager. Click on New Button, give the name. This time, I'm going to define the name of sales of North region over here. I'm giving the name North Sales. Here we can define the scope default, it would be the workbook so that we can use this name in complete workbook. Click on this option. We will get the list of all sheets available in this workbook. We can define the scope. So I'm going to selecting workbook. We can give some any command for the references and select this icon to give the reference or sale name. So we can see concept means I selected the commission calculated, so I have to select C 15 in this case. And we can see the concept miss the worksheet name is coming and the cell references is Cs 14. Press Enter and click Okay to Define. And we can edit it by clicking over here after selection of any name defined in the name manager, and we can delete by using this option. After closing, now I'm going to insert a new sheet, and here I would like to calculate the commission. So there is no need to remember the cell reference as C 14 in the concept spreadsheet. You just type the name North sales. So here I'm typing the text North. Sales. And then if I type equal North sales so we can see over here in the suggestion function bar, we have a different icon that refers to the name range, press tap, and now we need to calculate the commission so we can use the commission if we have defined. The scope of commission rate in the defined name is workbook, press tap to accept. Let's see what we are getting presenter. We got our commission over here. If we simply want to know the amount of sales, so we can simply type North Sales and presenter, the amount will come over here. So that is a very good option. You must use it in the large workbooks. 30. RETRIEVE VALUE FROM OTHER WORKBOOK: Hello, everyone. Welcome, once again, in today's video, we will learn how we can use the cell value of one worksheet in another worksheet. We define the name in this worksheet called Name Manager. Here we define some name like North says then the range and the commission rate. I want to use these values in another workbook. First of all, I have to create a new workbook, so I will press control and for the new workbook, and then I will just like this, I will increase some size. Let's say I want to retrieve the North seals, so you have to type equal sign, then use single coma sign and then type the name of the sheet. In this case, it is name manager dot cells X, then again, this coma exclamation sign and then type the name North Sales and presenter. We got 45,000. This is the one way if you remember the name of the file and the name of the name defined in that particular workbook. There is no need to define any worksheet name, spreadsheet name, or the cell references like C 14 or C 15. So you have to remember only two things. The name of the workbook you are going to use, and the name defined in the name range. That's totally very easy. The second easiest way is to just press equal sign, and you have to open that workbook, go to that particular cell and select commission in this case and press Enter. So if you select this cell, we can see the name range, the same function which we used over here, that is written over here, and here also, there is no complete path of that file. Only the name of the worksheet is coming and the name defined in the name manager is coming over here. We can see is 0.05. We have to change its formatting in percentage. So if you select that one and click over here, that will become the 5%. 31. NAMING CONVENTION IN DEFINE NAMES: Hello, everyone. Welcome once again in today's video, we will discuss about the conflict of name in the name manager. Let's take one example. Here, I'm in the concept seat, and here we have to define total sales, and let's say I'm calculating the total sales over here. So this is my total sales, and I would like to define this total sales. At worksheet level. What I will do, I will select this one. I will click on New and this time, I will give total sales and I will select the scope to concept. That is, worksheet level. And here we can see concept C 16. Concept C 16 has been selected, click Okay. Close this one. Now, if I go to the sheet one, and if I start typing total sales, it won't come because the scope of that name is limited to that particular worksheet. Now, if I defined a new sales, let's say, I will change it over here, and I will change this data to, let's say, 30,000 here, 40,000, sorry, 45,000 and equals to sum. So is that total sales is one leg 75,000. Now I'm going to use name manager to define the scope. Before that, I would like to select that sell name manager and click New. And here, I will use total sales as well. But this time, I'm going to select workbook, and you can see concept, worksheet. And C 24. That's fine. And now I'm going to click Okay and then close it. Now, if I come to here in sheet one, if I type total sale, this time, I will get the total sales. 175000. Let's see what we will get over here in the same worksheet where we have two total sales values. So I would like to type total sales, that is workbook, total sales, worksheet level. So here we can use the total sales, which is workbook level and the worksheet level. That's create total confusion and you will get not desired results. To get rid of this problem, we can use the naming convention. So we have two total sales values. One is workbook label, so we can define WB then total sales. And second is Ws then total sales. We can create name of such combination of characters, so we can easily identify. There will not be any confuson. I'll go to the name manager. I will select concept, this one, add it, and this time, I will type Ws. And this time I'm going to select this one. I will select this WB label and click Okay and closes. So now there will not be any problem, so I can select WB Total Sales and I can search Ws Total Sales. Hope this hope this trick will help you a lot. If you have any question related to the naming convention or the named range function, I love to help you out. Thank you for watching my video. Please come back again. 32. INTERSECTION: Hello, everyone. Welcome once again. In today's video, we will learn some advanced trick using the named range function. Here I have sales data month wise, east, west north, south, and these are the months. So here we will define the range of sales each month for whole year. And this time, I'm going to use keyboard, not the mouse, so I will use Alt sequence shortcuts. So first of all, I will press Shift Control down Aero key, then press Alt, press for formula tab. Then again, M for defining the name and D for final define the name. And the name is coming, scope is coming, and we can see the range. So now I'm going to save it by pressing Inter only. I'm going to west, Shift Control down arochi Alt MMD, Enter. North, Alt MMD Enter, Alt Shift control down arochi, Alt MMD, Enter. See how quickly it is. Now I'm going to define the range of sales for month of January. I'm selecting Shift down arochi Alt MMD, Enter. Shift Control arochi Alt MMD, Enter. Now we have defined the all ranges, come to name manager, and we can see April to August, all data has been saved. Now I'm closing this one, and now we can retrieve any data based on the reason and the month. For example, I would like to know the sales of June month for the North reason. For this, we would like to start the function using equalsge and type July or June, whatever the month you want to use. So in this case, it is June and give some space, and then type North and press Tab and presenter. We got the sales, press F two. We can see 799.31 is coming as intersection of this data of this range. That is very useful for MIS report. If you would like to, you can use this advanced trick in your professional work. Thank you for watching. If you have any queries related to that, just let me know. I'll try my best to give the answer as soon as possible. 33. DETERMINE CELL VALUES ARE TEXT OR NOT: Hello, everyone. Welcome, once again. In today's video, we will discuss how to determine whether a sale contained text or not. For achieving this, we will use in built two functions. The first one is is text, second is type. When we use any function, we have to provide the argument. Argument is nothing but to provide the values to calculating or doing the calculation of that particular function. In case I have to provide a value, we can type directly into the function or we can give the reference of particular cell that we have already discussed. So is text, return the value true if the value is text, otherwise false. And the type return one if the cell value is text, otherwise, two. So we have written three data types. These two are text types that we know. This is the number and this is dt. Let's see how we can use this function for achieving determining the cell values. Then I will show you the practical way how we can use these functions in our real world situation. I'm going to use equal sign is text. Start typing, you will see the list of suggestions function in built functions. Press tape to accept this. Now we can provide the value we can type directly within that bracket or we can provide the reference of the cell. So this time, I'm going to select this one. I'm closing the bracket and press into. We got the true value. As we know that, this is the text only. Pi press Control D. It will copy the same value from the topmost cell of the selected cell. So here, it is a relative reference that is why we are getting our results. Again, I'm going to copy and then I'm going to paste over here. So this time, we are getting false as we know that this is the number and this is the date, and this is true and false. So now I'm going to use type function. By equal sign then press type, then give the value, control D, copy, and paste. So these are text. That is why I'm getting two otherwise one. Now we are taking real world scenario. I have simple adopile of superstore. Here I have profit. Now I want to determine I have 10,000 so rows. I want to determine the cell values, whether these are the text or not because we are doing calculations in the profit. But if some values in particular cells are in text format, we will not the correct result. That is why first we have to do the check. So for this, I will use his text. Then I will give the reference of this. I'm closing the bracket and Control D, and I'm moving to the next sale by pressing tab here I'm going to start eqosine then type, press tab, come over here and give the reference and press Control D without closing the bracket. Excel will automatically close the bracket. Now I'm going to select these two cells values coming over here, press Control down aro key. To come to the end of this table. Now move your selection to the right hand side cell by using right Aaroky. Now press Shift we Aoki. Then again, shift control up Eoky and now control V to page the formula in all the selected cells. Now click inside the table. Now come to the data tap, click on filter. Click over here. Here we have two values, false and true, so we have to disable this and click Okay, and we got the results. These values are text, remaining all are in number format. This is how we can achieve this. And here also we are getting two values. I'm going to remove this filter by using my sequence keys. So this time I'm going to use Alt AT. Again, I'm going to press Alt 80, which you have to remember for applying the filter. Now I'm moving over here, Alt down Rok, and by using the arrow keys from the keyboard, I can up and down, press page bar to deselect all. As we know that the two is representing the values are in text format. I'm selecting this one, presenter. This time, I'm going to use my keyboard only. I have not touched my mouse. You have to do the practice using your keyboard only. So these all our values, and this is how we can use our two functions to determining the cell values, whether these are the text or not. If you have any query related to that, just let me know. I'll try my best to give the answer as soon as possible. 34. DETERMINE CELL VALUES ARE IDENTICAL OR NOT: Hello, everyone. Welcome once again. In today's video, we will determining whether two strings means the text are identical or not. Means we have some data in two different cells, and we need to compare. Both are same or not, and we are comparing the strings, means the text values. Here we can achieve this by using two methods. First one, without any function. We will just compare the two cells by using the equal sine and then the second method we can use in built exact method in which we have to provide the references of the cells or we can just try. So what is the difference between these two methods? In the first one, when we use the equosine, it will return to if the cell value are same, otherwise falls. But here, the upper case or the lower case will be ignored. In the second, we will get the true if the cell values are same, otherwise we will get the false. But here, upper case and the lower case will not be ignored. Let's take the example. Here we have some text in different cells. I'm going to use equal sine. I'm giving the reference of this cell, then equal sine and giving the reference of the second cell. Now press Enter, we are getting true because the text are same, but in this method, we cannot compare the lower or the upper case. So if I copy or paste over here, then we will get the same results. But if we will use the exact function, equal EX ACT, here I did typing mistakes, so you have to rectify that. And then I have to provide the two argument, text one or two. We can type in inverted commas or we can give the reference of the cell. This is the best way actually, I'm giving the reference, then coma and I'm interim over here and then Enter Control D. In this case, we can see that text are same, but this value in lower and this value in uppercase, here we are getting false, but here we are getting true. Now let's take the real life example. For example, we are working in accounts department, and we already entered some customers data, some master data in our database. So my task is to compare from the sources provided by our customers. That is very important because it's related to the bank. So here I have data customer code, customer name, and it is a bank name. So what is important for international or domestic remittance is the IBA number in case we are in Europe and the bank account number. The name of the bank is also important, but the name of the customer is ignored. Only two data will be enough for doing the bank remittance. For this, we will use first method by using equal sign, then we will use the exact inbuilt function. So here we need to compare data source ERP. We have downloaded the data master data, and that is Ban number, and this is the bank account number. And this is entered by some executive IBN and the bank. Now we need to compare. Here I'm typing equal sign and here exact. So let's compare. I'm typing equal, and then I'm comparing my IBN number first, then equal IBN, press control, Inter. I'm getting fs. It means both are not matching. Now I'm pressing Tab and this time I'm going to use exect. I'm giving reference of the IBN, then Coma, and the second cell and I'm pressing Inter. Now I'm going to select by pressing Control we aro key, and this time the list is not so big, so I can just double click on the functions will be applied. So let's compare one by one here. Both IBNs are not similar, so we can see and what is the difference we can find out? We can see here is seven triple zero, and here it's nine triple zero. So we can compare both the values like this, and these all are true. But here, these are not matching. So let's see. It's true. I I press F two, I can see the link, first link, and this is the second link means the argument. And here, if you see, all things are similar, but some text here are in lowercase. That is why it is showing true over here, but here is the false. You must be aware that the I band must be in the uppercase. So we need to rectify over here. Let's see, I'm going to change it. A, B and A, caps A, B and A. I'm pressing Control Enter, yet it becomes true now, and here both values are false. So this is the best way to do the comparison of two values using the equal or the exact. I already explained what are the difference, and now it is your choice which one you are going to use in your real life office or professional work. Thank you for watching. If you have any queries, just let me know. I'll try my best to give the answer as soon as possible. Thank you. 35. JOINING MULTIPLE TEXT IN SINGLE CELL WITH REAL LIFE PRACTICAL EXAMPLES: Hello, everyone. Welcome, once again. In today's video, we will discuss how we can join two or more cell values which are actually text values in Microsoft Excel. We have two methods for this. First is using the Ampersent and the second is using the inbuilt function called concat. We have some old or the legal C in build function called Concat Net, which is less efficient than Concat Net. Concat is the latest and more efficient function. So first of all, we have to understand how we can use it. Let's say we have this text in one cell, and this is another cell. Now our task is to join both the values. Here we can use percent, as you know that all functions or formula start with equalsine. Then we will give the reference of first text, and then we will use percent, and then we will give the reference of the second cell. Now press Control plus Enter. The text has been joined, but we cannot see any space in between. So for solving this problem, we will use quotation mark, so use quotation mark and give the space. Again, quotation mark, and then use percent once again and press Control Inter. So this is the first method. Now we will use the Concat net press tab, and here the argument is text one, text two or text three, or we can give the range of cell values. So there is no need to give the reference one by one for each cells. We can give the reference of all cells or the cell ranges in one go. So first, we will do by using reference of each cell separately. So I'm giving reference of this one. And coma. But this time also we want to use space between these two taxes. So I will use quotation mark, give space, then quotation mark, then coma, and this time, I'm going to give the reference of this cell and press control enter. We got our answer. Now the real life problem, we have to provide the maximum value from these values, and we have to write down like this the maximum value is. And after that, the value should come over here and it should be dynamic. Means whenever we change any value in this list, that should be updated here as well. So for this, what should we do? First of all, we should know how we can calculate the maximum. For this, we can use the maximum function. So let me show you max function, tab, and give the range and then close the bracket. So this is the number. The maximum number in this range, it is 10,000. Now we will use this function over here. I'm going to start. I'm just copying all this data, and now here I'm going to use, and you must know that whenever we enter any text or string value in any function, we have to use quotation mark always. I'm going to start quotation mark because this value will be fixed, and I'm going to give the space after the is as well. Here, then I'm closing this sentence by quotation mark. Sorry, I forgot to input my function that is concat. Okay? That is fine. The first value I have entered now Coma, and now I will use my function max. So now I'm using two functions in one formula. So I'm using max and I'm giving the value over here. I'm closing the bracket, and I'm closing the bracket for Concat net as well. Plus enter. Let's see what we get. The maximum value is 10,000. So we can remove these values from here. And if I change, let's say I will enter this one. So that has been changed automatically. Now we are taking second real life practical example. So I have some full names over here. My task is to join in one cell. So for this, we can use the Concat Net function. So let's say I'm going to use over here. So Concat and I'm giving the range of these cells, I'm closing the bracket presenter. We got the names, but the problem is there is no space or Coma after each name. So what is the solution for that? For this, we will create one helper column using the Posent. I'm using equusine and I'm giving the reference of this name and I'm putting Posent now I'm using quotation mark, then Coma space, closing the quotation mark and Inter. I'm using this function, I'm copy I'm going to paste over here. And now this is the main range which we have to use. I'm going to change the range here, Control Enter, and we got our list. And after each name, we have comma and then space. So this is what we got, and this is how you can use these functions in your real life practical examples. If you have any query relative to that, just let me know. I'll try my best to give the answer as soon as possible. Thank you, friends. 36. REMOVING EXCESS SPACE AND NON PRINTING CHARACTERS: Hello, everyone. Welcome, once again, I today's video, we will discuss how to remove excess space and non printing characters in Microsoft Excel. For this, we can use to inbuild functions called trim in which we have to provide the single argument only, which is the text. We can copy paste the unformatted text or we can give the reference of any cell. Similarly, in the clean, we can copy paste the unformatted data or we can give the reference. We have to use for cleaning the trailing space and non printing characters by using both functions in a cell. So let me tell you how we can do that. But before that, we need some data. I could not find any non printable characters from the Internet or from sources, but let me explain what are these. Sometimes we import the data from some old ERP systems, some DOs Bt systems where we have tabs unwanted extra space. So let me open the MS Word file. Let's say we have this sentence and we have imported this from ERP system which has multiple tabs and enter, and then we have some extra spaces like this. So our task is to remove these in one go using the trim and clean functions in the Microsoft Excel. First of all, we have to copy this. Then I'll minimize this. I'm selecting this cell. So I'm going to use the formula bar to paste, Control V recenter, and this is the data. And here we have to use trim function first. Then we have to use clean inside it. And then give the reference, then close and close. I I press Control Enter, we will get our result. This cell is used rap text if I remove, so we will get our answer in desired format. So these are the only two functions which use to remove all leading and trailing spaces, and it replaces internal strings of multiple spaces by a single space. Clean is used to remove all non printing characters from a string. These garbagecharrectors often appear when we import certain types of data. So you can use these. This is a very good function for cleaning up your data. So if you have any query related to that, just let me know. I'll try my best to give the answer as soon as possible. Thank you. 37. COUNTING CHARACTERS USING LEN FUNCTION: Hello, everyone. Welcome, once again, in today's video, we will discuss how to count characters in a string. For this, we can use an function in which we have to give only single argument in which we can enter the string directly inside the bracket or we can give the reference. The lane function is used to count all the characters in the string, including unwanted space. Make sure it will count unwanted space as well. Here, I have example of IBN. I have a list of IBans and my task is to calculate the length or the total arrectors in this Ivan, including the space and without the space. Right now, all the Iban having the proper space, but I will include some more space over here. In few Iban, I will inserted some extra unwanted space. So let us try, and we will use some other functions with the lane function to count the total length or the charrecors of these Bands. So I will use an function to count in which the unwanted space will be considered. I'm giving the reference of this cell, closing the bracket, Control Enter, so we can see the 31 characters. So let's count. If I remove any unwanted space, yes, it is counting. So now I have total 30 characters. I'm going to copy and I'm going to paste over here. Now my second task without unwanted space means some extra space must be removed. For this, we will use trim function which we discussed in the last video. For this, we will use lane function, and inside it, we will use trim function, and then we will give the reference. Then I'm going to closing the bracket for trim function, then closing the bracket for lane function, Control plus Enter. So we got 27 characters. So we have removed all unwanted space. Now my third task is to calculate the total characters without any space means we do not want any space. So for this, we will use substitute function with Lane. So first of all, I will use an function, and Lane. And inside that, we will use substitute function, which we will discuss in details in later video. So in the substitute, we can give the reference of a particular text, so I'm giving the reference. And the next argument is the old text, which we want to search first. So we want to search the space. So I'm giving space in quotation mark. Then coma, then the new text would be two quotation mark only without any space. And the fourth argument is optional. I'm going to close the bracket for substitute, then for the lane and pressing control enter, and I'm going to copy this And now we can see the difference. So here I'm getting the length with space, including unwanted space, some extra space. Here without unwanted space means one space is considered valid. And here without any space, only the characters will be included. So here I use substitute function. So this is the real practical example which you can use in your real life. Thank you for watching. If you have any query related to that, just let me know. I'll try my best to give the answer as soon as possible. Thank you. 38. PADDING A NUMBER: Hello, everyone. Welcome once again. In today's number, we will discuss about the repeat function. And here I will take the real life practical example for printing the values. Normally, if you have seen in the check printing, we have some space for providing the numeric number. But the number is printed with some asterix signs. You can see over here, so we can use repeat function to achieve this task. The amount is that is completely dynamic. Whenever we change any amount over here, this will be changed. So we can padding a number to the left or to the right hand side as per our choice. So as already told you for this, we will reuse the repeat function. So first of all, we need to understand what is the repeat function. Repeat function is used to repeat a particular character or characters any number of times. It has two argument. So let me explain by using it. I'm using RE PT and then press tab, and here we have text, which we are going to repeat. That can be character or number of character, any word, any sentence or any kind of string. We can type directly here or we can give the reference. Right now I'm taking the asterix sign over here. And you please note that when we provide any string in the argument or any function, that string must be in the quotation mark. I'm providing steric sine, closing it, then coma. The second argument is number of times. How many times we want to repeat it? Let's say ten times, so I'm closing it and pressing Enter. So Etic sine has been repeated ten times. Now we are going to use this function. This is used to repeat the steric sine left hand side of the amount. So I'm going to close these all and now equal sine. So first of all, we have to give the reference, or we can use the concateNt function, which we already discussed, or we can use the percent for combining the two strings. This time, I'm going to use percent. I'm giving the reference of this cell so that the number comes automatically. And I'm going to use repeat function in it. I'm going to repeat asters. Now I have to provide the second argument number of times. That must be dynamic because we cannot increase the number of characters in the provided space on the check or any document. Here, first of all, we need to count length of the numbers of this cell, and we need to define the maximum range of the document or the check. Let's take example, it is 24, 24 is the maximum length. I'm starting a new bracket, 24 minus. What would be the length, the length? Of this cell. I'm going to close this one. So the lane function is closed now. I'm closing this bracket. Now we have to close the repeat function. Now, let's see what we get. Let's Enter. Sorry, I inserted eight instead of astric side, so I need to remove this to Asterc now Control plus Enter or Enter, we got our results. Now I'm going to use pedding to the left hand side. For this, we need to use repeat, so we will use all the argument same as we used above, starting 24 minus N giving the reference of this cell and closing the bracket for repeat function. And I'm going to use sticine and then giving the reference of this and closing the bracket finally, and now Control plus Enter, something is missing. System can automatically add or remove closing bracket if we have done any wrong. This is how we can use. Let me try the Concat net function as well. Now we are going to use Cct and here, first of all, I need this one, then coma, and then I will paste the repeat. I'm closing the bracket, Enter. So we got our answer. So we can use both Concatent to combine the numbers in this cell and the repeat function. It's totally depend on you how you can use. So if you have any query related to that, just let me know. I'll try my best to give the answer as soon as possible. Thank you. 39. CHANGING CASE OF TEXT: Hello, everyone. Welcome once again. In today's video, we will discuss how we can use changing the case of a text. Let's take the example. I have my company's name tutorial in capital format. I want to convert in the proper format. Proper format means the first letter in each word is capitalized. Then I have tutorial in lowercase. Or which I want to convert in per case, and we have uppercase, which I want to convert in the lowercase. For this, I will use two in build functions. The first one is proper, upper and the lower. So let's start equal sine, proper, press tab, give the reference, close the bracket presenter. Equal sine, uppercase, tab, give the reference, losing the bracket, presenter. Equal sine, lower tab, give the reference closing the bracket placenter. Very simple. 40. EXTRACT CHARACTERS FROM STRING: Hello, everyone. Welcome once again. In today's video, we will discuss how we can extract the particular characters from the string. We have three imbiled functions for extracting the characters from a string. The first one is left, which has two arguments, text, and the number of characters which we want to extract. Then write. This also has two arguments, character and the number of characters, it will return a specific number of characters from the end of the string. Then mid, here we have three argument text from which we want to extract the characters, then the starting point and the number of characters which we want to extract. So let me take the example and then we will do our real life practical example for extracting and changing the case of this sentence. So first of all, we have to apply the left right and the mid, uh, functions over here. So we are starting from ecosine then the left, giving the reference of this sentence. And we want to extract four characters from the left hand side, closing the bracket, presenter. So four characters are VR, which is extracted from here. Then the right this time, I'm going to extract eight characters, closing the bracket, presenter. So Tutorial is coming. Now I want to extract the five characters from the middle so I have to provide starting point and the number of characters which we want to extract. So in this case, I'm using MIT, giving the reference, starting point would be the five, and I want to extract the four characters. So going to close this one and press center. So we got three characters, TTO, and the space as well. So if I increase starting points, so I have TUTO these are very useful when you have to design the user IDs. So you can define any particular logic for calculating the user IDs of the users in your organizations. This is just an example. Now our task is to convert the first four characters of the sentence in the percaseR now, all sentence is in percase but it may possible later on we may get any sentence in the lower or the proper case. And the remaining all characters of this sentence must be in lowercase. So we have to use left, right, and lower and upper and the lane function, which we have already discussed. So now I'm going to start but here I'm going to dividing this function or the formula in two parts. First, we will convert the first four characters in the uppercase. So for this, we will use uppercase function, and I need to give the reference of the text. But before that, we need to provide which text for this, we will use left function and we will provide the text, reference from here, and then we will provide a number of characters or four. And then we are going to close this bracket and presenter. So we forgot to provide the closing bracket for the upper case. So Microsoft Excel is suggesting, do you want to accept this correction? Yes, we want to accept. So this is also a very good option. Sometimes if you forgot to provide closing bracket of any function, so Excel will provide, but not in all cases, some cases, if Excel algorithm catch that you forgot. So now I'm going to use lowercase function for converting the rest of the sentence in lower case. So here, I'm going to use write function, and the text would be this one and the number of characters, how many number of characters? So first of all, we should know total number of characters of this sentence. For this, we will use lane function, which we have already discussed. I'm going to give the reference of this cell, closing the bracket for lane. Now system knows the total number of characters in this sentence, and we have already converted the four characters in the upper case. So if we will minus the four characters from here, we will get a total remaining number of characters which we want to convert in the lower case. I think the function should work. So I'm going to close my right function first, and then I'm going to close lower function, and I'm going to press Enter. Yes, we got our answer. Now we will use the Concat net to combine both the sentence. Now if you change this sentence to another sentence, let me going to change with this one. Are we getting correct or not? Yes, the return. You can see the first four characters are converted into the uppercase and remaining all are in the lower case. So this is just an example you can explore more and you can just search some more examples in which you can apply these text or string related functions for your own practice. I will also try to give you some practical examples for your own practice. So if you have any query related to these functions, just let me know. I'll try my best to give the answer as soon as possible. Thank you. 41. EXTRACTING FIRST WORD USING FIND FUNCTION: Everyone. Come once again in today's video, we will learn how to extract the first word of a string. For this, we will use fine function. And let me explain what is the fine function first. The find function in Excel is used to locate the position of a basic text. Within another string, it returns the starting position of the text you are searching for as a number. And this is the syntax in which we have three argument. The first is find next the text you want to find. Next, within text, the text where you want to search. And then optional starting number, the character number where you want to start the search. If it is not provided, by default value would be fun. Let's take the example. I have one string over here. Now I want to find the position in number of free. So how I'm going to use fine I'm typing my function over here, fine. Now I'm giving find text, free. It must be in the quotation mark. Then coma. Then within the text, provide the reference of this cell, then coma and then starting number would be one. Then closing the bracket, press enter. It is 17. Let's count one, two, three, four, five, six, seven, eight, nine, ten, 11, 12, 13, 14, 15, 16 and 17. So it is starting from the one we have supplied. If we increase the number of places, now we are going to use this function with our example left. I'm going to use left function for extracting the first word. The problem here is we do not know the number of character in the first word. So what is our logic? First of all, we have to identify the position of the space, starting from the left hand side. So for this, we will get the total number of character means, the position, and then we will use the left function to extract the text from the particular string, and number of characters we can calculate using the fine function. So I'm giving the reference of this cell. Then we are using find. And this time we have to find the space first. So I'm giving the space in quotation mark, then coma and I would like to provide the text reference of this cell, then coma and it will be one. I'm going to close fine function and closing the left as well. Now pressing inter, so we got the destination. Now let's try to enter something else. I'm going to copy from here. I'm going to copy and then I'm going to paste. So in this sentence, we can see the first character. In this sentence, we have space at the beginning of this sentence, so we have to remove the presenter. Then we will get our correct answer. So this is how you can use the fine function for extracting the first word of the string. So you can also apply this in another situation. Just let me know if you find any difficulty. I'll try my best to give the answer as soon as possible. 42. UNDERSTANDING THE DATES IN EXCEL: Everyone. Welcome once again. In this section, we are going to start how to manage the dates and time and its related functions in Microsoft Excel. But before going ahead, we need to understand how Excel manipulate the date and time. So in Excel, dates are stored as a serial number to make calculations easier. Each day is given a unique number starting from the January 1, 1,900, which is assigned to number one. For example, January 2, 1,900 is given two and so on. So this system allows you to add, subtract and compare dates easily. When you start typing a date in the Excel, Excel convert it into a serial number, but shows it as a date. This makes it simple to do things like calculate the number of days between two dates and shot a list of dates. So let us begin in the Excel and understand the system. So I'm going to enter the date. We have already discussed how to enter the dates in my earlier videos in this course. But here also we will understand. To display in number in the excel, we need to separate the date month using the slash, backslash or design. We have different formats which we are going to explode in this video as well. So I'm going to enter first January 19 double zero. So when we start typing, it will be converted into the dates automatically. Which we can change from here. Come to the home tab, come to the number section, and here we have option called Number format. Just click over here. So in this, we will find short date, Long date. But if we want more formats of date, so we have to click on more number formats. From here, we have different categories, select a date. And here we have type. This is the preview section. And here we can select our location and the calendar type. So basically, we can select any particular format which suits our requirement. So I'm going to select this as our location in India, so I'm going to select this one. So in India, basically, first, we consider the date, then month and then here. Now I can see all the dates starting from the one to seventh of January 19 hundred. Now I want to convert this into the numbers. So I'm going to use equal sine. I'm going to select the value of this pressing Enter. I'm going to select this cell and double click on this sign. So here we can see the same value written in this cell. Now I'm going to convert the formatting of these cells by going to the number and selecting the option from here and I'm going to select general and I can see number one is assigned to the first January 19 hundred, and second number second January 19 hundred. So this is how Excel treat the serial number for a particular date and do the calculations in their functions and formulas. I hope the concept of managing the dates in the Excel has been clear. Still, if you have any problem, just let me know. I'll try my best to give the answer in the query section as soon as possible. 43. UPDATE DATE AND TIME IN WINDOWS: Hello, everyone. Welcome once again. In this section, we are going to start how to use date and time functions in the axle. But before we dive into the functionality, we need to understand the display formats of date and time. Sometimes we want to enter the date format as per our reason, but system doesn't recognize that format. It changed automatically because that depends on the reasonable settings of our Windows configuration. I am in the Windows 11, and I would like to show you how you can change the settings of your date and time formats. So you have to open the settings of Windows 11 and then find the resonal settings. You have to type reason settings, just click on it, and it will be open time and language, the reason and languages. I will start from the top. The first option to change the display language of our Windows which we can change from here, then come down to the regional settings, and here we can select our country and reason. In my case, it's India. And my reason format is also sets to English India. But if you're working in another country, so you can change from here, let's say, United States, so that will be available in the downside, so come down to the United States. And you can see the resonal settings has been changed automatically. This is the recommended, but you can change as per your requirements. So that totally depends on you, which one you're going to use. So I'm going to use India only, and we can set the date and time based on the resonal settings from here. You want to see what are the settings, you just click on this arrow button that option will be expanded and we can see the calendar date and short date, long date time formats here. If you want to change, just click on it. We can change the calendar from here. We can set the first day of the week from here. We can set the long and short date format using these options, and we can change the short time and long time using these options as well. But if you want more settings, just go back and select administrative language settings. And here we have format option come over here. From here also, we can change the format. First of all, we have to change the reasons from here. After selecting, you can come down. Here we can change the short date format. We have some formats. These formats will be available depending the default reason you have selected. We can change that long short or first day. These are all settings which we covered already in this option. But if you want more settings, just click on additional settings. Here, we will find advanced settings related to the formats of our Window system. So let's say we want to change the number settings. So in India, normally we use two digits for crows lack the thousands and then remaining numbers. But if you're working in ANC company, UK, US, or some other company in which the million and billions are used for the number formatting, so you can change it from here. We can select millions, and this is a separate format, and this is the India format. So you can change it from here, you can change the currency format from here. You can change the symbol from here and negative and the positive formats from here. You can manage the time and date using these two types. All formats are self explanatory. Let's say we discuss about the time. So here we have what is the notation means. If we use D single D that will be used for display that date only. If you use double D, it will be displayed as d. And if you use triple D or the four time D, that will be the day of the week. Means the complete name of the day will be used. If you use, that will be used for month and Y is used for here only. These are the self explanatory and you can change it from here. Once you are done, just click on apply then Okay, I'm going to cancel because I don't want to change my settings. And here as well, I'm canceling, but you have to select Okay if you did any changes. Once you're done, your resonal settings will be applied to your system. Now you can open the Excel and you can start entering your date. I have explained how to enter the date and time in the next coming videos. You have to follow this video and you can start entering the data in date and time functions. If you have any queries, just let me know in the question and answer section. I'll try my best to give the answer as soon as possible. Thank you. 44. TODAY FUNCTION IN EXCEL: Hello, everyone. In this video, we will discuss about the today function in Excel. The today function in Excel returns the current date. It is a simple yet powerful function that has very serious application in spreadsheet work. The today function update automatically whenever a worksheet is recalculated or reopen. The result of today functions is a serial number, but Excel typically display it as a date. We already discussed this in previous section. Can use the today function in calculations involving dates, such as calculating the age, tracking project deadlines, employees time of balance, invoices due date, subscription renewal notifications. So let us begin with the simplest example. Then we will discuss about these five examples for better clarification about today function. So simply you can start Today, and at the end, you have to close the bracket. There is no argument for this. Whenever we press Enter, we will get the current date of the system, and it is a dynamic function. It means, if I open this sheet tomorrow or after one week or after ten days or whenever, this date will be automatically update by the current date. Let's take the first example. We have date of birth in the column D and we need to calculate the person's age. We can use today's function to update the age automatically whenever we reopen or recalculate the sheet. For this, we can use different functions, but I'm going to use dated if we will discuss this function detailed incoming videos. Here I'm explaining about today's function only. So I'm starting my function using equal sine then dated I. Right now, it is a part of Excel, but it is not included officially in the list of functions. So you have to type dated I function starting bracket, then we need to give the reference in which the date has been given Coma. And the second argument is to give today's date. So we are going to give this one, and I want to make it absolute reference. So pressing F four from the keyboard, Coma, I need to type Y to calculate the number of years between these two dates. And I'm going to close the bracket and press Control into from the keyboard and just double click on it. We got the person's age over here. So this is the one way to use today's function. We will discuss our second example tracking projects deadline. Let's say the deadline is 31st of December 9, 2024, and I need to track the number of days from today's date. So it must be dynamic. Again, I'm going to use dated if function for this. So dated if and this time, I'm going to give the reference of today's date over here. We can also type the today's function inside this function, so Coma the next we have to provide the project completion date. So I'm selecting this one, Coma. The next argument is we have to provide days. So we want days between these two dates only. So if I presenter, we will get the answer, but it is in the date format, which we can convert it from here. Now total 150 days are remaining to 31st of December 2024. So if I open this access tomorrow, it will be updated to 29th of July 2024, and the number of days will be calculated accordingly. The next example is employed time of balance. We manage employee vacation balance and need to track how many days of vacation of an employee has left as of today. So what should be the solution? So by saving the time in this video, I have written my formula over here here I have used I function, which we are going to explore incoming videos. In this formula, I use today's function and comparing with B 24 cell number, which contain the date for which employee has to take the vocation. Otherwise, that vacation will be expired. So I have written the date over here, and here I have my function in which I use today's function, so it will be updated automatically. So if today's date is greater than B 25 value, the vacation would expired, otherwise, we have to calculate the days between the value entered in the B 24 cell and today's date. So we have used this formula with multiple functions provided by the excel. It is completely dynamic, it will be updated automatically. Similarly, we can also calculate the invoice due payments and subscription renewal notification. For example, we have some dates over here and the due dates. In a similar way, we can also manage invoice payment due dates, notifications, subscription renewal notifications using the today's function. So this is very simple and very powerful function. You must use it in your work. If you still have any question related to this function, just let me know. I'll try my best to give the answer as soon as possible. Thank you. 45. DATE FUNCTION IN EXCEL: Hello, everyone. Welcome, once again. In this video, we will discuss about the date function in Excel. Date function in Excel is used to create a date value from individual year month and the day component. This is especially useful when you need to construct dates dynamically based on the different inputs or calculations. So here is a syntax for the date function where we have to input month and the day separately. So we have three arguments. The first example we can take, we have some data in different cells and we have to construct a day. For this, we can use this date function. Let's say I have second February 1998. So this is my date, this is my month, and this is my year. So I need to construct a date. For this, I will use date function. So I will start by typing equal sign and the date, and I will give the reference one by one. So I will give the reference of her from here. OMA month from here and the date. I have to close the bracket and presenter. I got the value end date. That is dynamic. Whenever we change any day, date, or month here, that will be updated automatically. Now we will start out example. We have to manage a list of product with manufacturing dates, need to be calculate expiry date based on the fixed shelf life. The shelf life is one year and six month, not the days. So we have to calculate using the one year and the month only. So I have manufacturing date over here. So first of all, I need to extract the year from here. We can create a formula in a single cell, but I'm explaining it separately to avoid the complexity of using multiple functions in this calculation. So first of all, I need to extract the year from here. For this, I will use function. So eqsine here, then I need to give the reference of the cell, close the bracket, press there, then month. Give the reference, close the bracket, res step. I need to extract the day using the day function. Give the reference of this, close the bracket, presenter. So we get manufacturing year manufacturing month and the day. I'm going to copy and paste in the rest of the manufacturing dates. Now I need to calculate the expiry date using the year and the month only. For this, I will use date function. I'll give the reference of the year or I can calculate the year over here directly in this formula. As I told you, due to avoid the complexity for using the multiple functions in this formula, I'm using separate cell to calculate the year. I'm giving the reference or I can calculate the year here, and now I'm going to plus one here for calculating the expiry. So this time it is 2024, the expiry would be the 2025. Now I'm going to give the reference for the month this cell, and I'm going to add six over here, and the day would be same. I'm closing the bracket, presenter, and now copy and paste. This is the use of date functions, and I have given you the three real life examples for solving the day to day problems. 46. CONVERT NON DATE STRING TO A DATE IN EXCEL: Hello, everyone. Welcome, once again. In today's video, we will discuss about converting a non date string to a date. For example, sometimes in our ERP or some other software, we import some data. So I got the date in this format. Here has been written in the beginning, then we have month, and then we have date. But these are not separated by any character or symbol. So our task is to retrieve the year date, and the day, and then we have to convert this in proper date format. We have already discussed or learned about the text related functions in which we can extract the text from the left, right, or the mid range. And in the last video, we learn how to convert these values into the dates. So let's begin. We can create a formula using all these functions. But I really want to avoid the complexity for using the multiple functions in a single formula, so I'm using in the separate cells. So first of all, I need to extract the ear for which I will use left function, and I know there are four digit from the left hand side are ear. I'm going to use this one, press Enter or press tab from the keyboard. I got my ear and the mid value is starting from the five. And there are two values only. So I'm going to use mid I'm going to give the reference of the text, then starting number would be five, and I need to extract two numbers only closing the bracket, press step, and then write equal sign, then write. And I know from the right hand side, two digits are dates. We got our all values. Now I need to use date function. I'll give the reference of this cell for here. This sells a reference for month, and this is for the day closing the bracket, Control plus Enter. To get my date, now I'm going to select copy, selecting the rest area, Control V to paste and we got our date over here. So this is how you can use multiple functions in simple calculations to do the clearing of the text imported from Internet or from other sources in the row format. 47. Calculate days between two dates: Hello, everyone. In this video, we will learn how to calculate the number of days between two days in exile. Let's take one example. Then we will explore some more examples, and we will learn in details in this video. So I have start date and the end date January 1, 2024 and 31st of January 2024. I need to calculate the days between these two dates. So it's a very simple. It's just a substracton. We can use the substraction equalsine and date, the biggest number and the smallest number. Control plus Enter or Enter, we got the number of days 30. But you must be thinking it should be 31. So let me tell you one thing. We are just substracting one number from other number. As we have already discussed, each day has a unique number. So we are not doing anything just subtracting a number from another number. But if we want to calculate the total number of days between two dates, so there could be two situations, inclusive or exclusive counting means we want to consider the first day or not. So in this case, we have to add one for getting the proper result. What could be the situations in which we want to use inclusive counting? It can be a project duration. When calculating the total duration of a project, that include both the start and the end dates. It can be event planning when planning event, that's been multiple days, and the event include both first and the last day. We consider inclusive counting in the age calculation as well in some cultures or the context when calculating age in days or years, both the birth date and the current date might be counted. Let's take one example vacation days. Suppose an employee takes a vacation starting from January 1, 2024 and end with the fifth, January 2024, so if you want to know the total number of vacation days the employee will take, you would be inclusive counting. In the other cases, we can take the exclusive. At that time, there is no need to use plus one in the calculation. So here, I'm going to extract, and I'm adding one in all these cases. So you need to think before counting the numbers about your papas. The decision is to add one when calculating days between two date depends on whether you want to include both the start and end dates in your calculation. That is called inclusive counting or exclude the start in date, exclusive counting. Knowing the context and the purpose of your date calculation will help you determine which approach to use. 48. DAYS AND DAYS360 FUNCTIONS IN EXCEL: Hello, everyone. Welcome once again. In the last video, we discuss how to calculate the days between two dates using a simple substraction method in which we have not used any in built function. But in this video, we are going to use inbuilt functions for calculating the days between two dates. We have two functions, days and the days 360. Let's learn about days. Days function in Excel calculate the number of days between two dates, and it is very straightforward and based on the actual calendar days. Here, we need to enter two argument and date and the start date. And we will get the result. Let's take example. We have first January 2024 in the cell and 31 December. I want to calculate the number of days between these two dates. It's very simple start by equal sy and days pretap, give the end date first, and then give the reference of the start date. Close the bracket presenter. We got 365 days, so that's very straightforward and very easy. Now we are going to use days 360 in which we have three arguments. But let's learn what is the use of Y Excel provided an extra function for calculating the days. The days 360 function calculate the number of days between two date based on 360 days year, in which we have 12 months of 30 days each. This method is commonly used in the accounting and financial calculations only. Whenever you have such situation in which you need to calculate the days using the 365 days only, then only you have to use this function in which the month will be considered for 30 days only. Here we need to enter three argument, start date, date, and the method. Let's learn using the example. So I'm starting by ter ecosin then days 360, then press tab from the keyboard, then start date. So here we need to enter start date first, then Coma, then the end date and Coma, which is optional, not mandatory. So here false or true, false use for US where start end dates will approximately to the 30 days month. And we need to use true for the European where start date and the end date that occurs on the 31st of the day will be equal to 30 days of the month. So first of all, I'm going to use US method, press tab, and then press Enter. Sorry, I think we did some mistake. So I'm going to use again, 360, then start date, and then date and the method is false, going to close press Enter, 360 only, perfect. Now I'm going to use it again with the false. 360 and start date, and then date, and the method is true, closing the bracket presenter. So this time, I got the 359. So this is the major difference. So you have to understanding the difference between these two functions allow you to choose the appropriate one for specific use case, ensuring accurate and relevant calculation in your Excel worksheet. So when to use each function. So days, we have to use when you need to calculate the actual number of days between two dates, such as in project planning, age calculation or general date differences. And 365 days use in financial and accounting calculations, whereas standardized 30 day month or 360 day year are needed, such as in loan interest calculation or bond pricing. So these are the two inbuilt functions for calculating the number of days between two dates. 49. NETWORKDAYS CALCULATE WORK DAYS: Hello, everyone. Welcome, once again. In today's video, we will discuss about how to calculate number of workdays between the two dates. For this, we will use network days function Axl, which is used to calculate the number of working days between the two days, excluding weekends, and we have to provide holidays, which is optional. So this function is particularly useful for the businesses, project planning, where the focus is on actual working days rather than the calendar days. This is the syntax network days, and we have to provide three arguments, two are mandatory start date and holidays are optional. So we have to provide the start date for the calculation, the end date for the calculation, and we can provide holiday list separately, but that is optional. These are the applications where we can use project scheduling, payroll calculation or invoice due date, et cetera. It can be more than that, that depends on the work we required. We are taking the example. We have to calculate the working days in the month of July 24. Here we have first date, last date. Here we are going to calculate our working days. First, we will not provide any holiday list. So let's see whether it will exclude the weekend that is Saturdays and Sundays as well. So equosge network days, give the reference to start date, then the end date and close the bracket presenter. So we got 23 days, so let's check. So we have first day of July, and here we have last day of July that is 31st of July. So let's see. So Monday Friday, working days, five days, then next Monday to Friday. So total, ten days. Here 15, here 20, and now we have Saturday, Sunday, and then Monday, Tuesday and Wednesday. That is the 21st working day, 22nd, and 23rd. So result is we got as expected. Now we will provide the holiday list, which we already created here. Here, as you can see, I have entered first of August as well as a holiday, but we have two days in July as well. So let's see whether as per calculation, the system should not consider 1 August because we have provided a range for the month of July only. It should consider only holidays in the month of July. Let's begin ecosie network days. This is start date. This is date. And now we are going to provide this holiday list and closing the bracket presenter. We got 21 days. So it means it is considered only tenth of July and 17 July as a holiday, not considering the August. But if I increase it to the, let's say, fifth of August, so it is 23 days. So I think that would be the exact number which we were expecting. So you can check it, and you can do the practice for project calculation and the payroll calculations and invoice to calculations for your practice. Thank you for watching. If you have any queries, just let me know. In the query section, I'll try my best to give the answer as soon as possible. Thank you, friends. 50. NETWORKDAY INTL: Hello, everyone. Welcome, once again. In this video, we are going to calculate the work days between the two days. In the previous video, we already discussed about the network days. In this video, we are going to discuss about the network days dot INTL, which is the advanced version of Network days only. In the previous one, we discussed how we provide the start date and the end date and we provide the list of holidays which is optional. The Excel will automatically consider the weekends as Saturday, Sunday. But in some countries, it may possible they have different weekends. So to avoid that problem, Excel provide one more in build function called networkdays dot INTL in which we have to provide full argument, start date, date, and the weekends. So these are the argument list which we can provide here one for Saturday, Sunday, second for Sunday, Monday, three, four, Monday, Tuesday, and so on. So you can take that number and include that argument here. So system will consider these days as a weekends, and remaining all things are quite similar with the previous example. So that is also very useful for project scheduling, payroll processing or the invoice, overdue payment calculations. So here we are going to start our example. So I'm calculating the network days dot INTL network days dot INTL, then press Tab, provide the first day, then the last day. And this time, I'm going to have the list of all weekends. So let's say I'm selecting Sunday only, pressing Tab, coma and here I'm going to provide the list, closing the bracket. So I got 25 days working days between this period if I have only Sundays as a weekend, so it will not consider the Saturdays. So you can do the calculation here, so you can download this sheet and you can cross verify whether the calculation is okay or not. So if you have any problem or any query related to this question, just let me know. I'll try my best to give the answer as soon as possible. Thank you. 51. WORKDAY AND WORKDAY INTL FUNCTION: Hello, everyone. Welcome once again. In today's video, we will learn how to offsetting a date using workday function. So the workday function in Excel is used to calculate a date. That is a specified number of working days. From these working days, we can exclude weekends and holidays. So basically, it is opposite function of network days. In the network days, we calculated the workdays in which we excluded the weekends and the holidays. But here we have number of days and the starting date. We do not have the end date, so we have to calculate the end date, and we have to exclude the weekends and the holidays from that list. So this is the syntax we are going to use Workday, it has three argument, start date for the calculation, the number of working days to add to the start date. It can be positive or future date or a negative for past date. And then we can provide the holiday list that's totally optional. Let's take the example. Our project is going to start on first July 24 and we have given 250 work days. Now we need to calculate the date on which the project will complete. So let's see how we can calculate by using our function. That is work day. Here, one thing you need to consider the weekends are fixed Saturday and Sunday. So first, we will learn about this, then we will discuss the advanced version of Workday, that is workday dot INTL. First, we have to complete this example, equals sign, workday, and then we have to provide the start date and the number of days and holidays if we have. Yes, we have, so I'm going to consider this list and presenter. So 18th June 25, that project will complete. And now we are going to use the advanced version of this one, which is workday dot INT. I'm going to use Workday dot NTL, then the start date and number of days would be same. And here I have the list of weekends. So I have to select a number. If we have different weekend. In some reasons, we have Friday and Saturday, Thursday and Friday, so you have to select an option. So I'm going to select Sunday only this time, so we can input 11 only. That is the code for this weekend that is Sunday. So I'm going to press tab, then coma, and here we can define optional holiday list, if any. I'm closing the bracket and we got 22nd April 2025, which is earlier than the previous date because in the above, we have two weekend days Saturday and Sunday. Here we have only single weekend. That is Sunday only. So this is how you can use this and workday dot INTLFunction. So if you have any queries, just let me know. I'll try my best to give the answer as soon as possible. Thank you. 52. DATEDIF: Hello, everyone. Welcome once again in today's video, we are going to discuss about dated formula. This function is hidden in Excel, meaning it won't show up in the formula auto complete, but it is fully functional and very useful for date calculations. The date if function in Excel is used to calculate the difference between two dates. Can ten the difference in vari serous units such as days, months or years. This function is particularly useful for calculating the age, tenor and duration between two events. So this is the syntax in which we have to provide three argument tar date and date and the units. These are the units which we can use. If we want to calculate a complete year between two dates, we can use the unit Y M for month and D for days. If you use combination of these two units called MD, the difference between the day in start date and the date in the month and year of the dates are ignored, we will get the difference in day only. If we use YM, the difference between the months will be calculated and days and years will be ignored. If we press YD in case, in this case, we can calculate the number of days and the year and dates will be ignored. Let's take the example above I have starting date and and date. Let's see I want to calculate the number of here, in this case, I will use dated I as I told you, you will not see it in the autocomplete list of the functions in Excel. But still, you just type complete syntax, dated I start bracket and give the reference of first date. And this time, I'm going to make it constant by using dollar sign and then date, making it constant, then coma, and then we have to provide Y in the inverted comma. Closing the braskt press inter. As you can see, it's 2009 and it is 14. So we have completed five years, and now I'm going to copy paste, and this time, I want to calculate the month, 61 months. Then I'm going to copy, and this time I'm going to provide D only. These are the total number of days between these two dates, and this time I'm going to provide MD, so total single day. This is the second. This is the third day. Month and the year has been ignored. Now I'm going to copy and paste and paste over here, and this time, I'm going to use Y. This is the fab and March, so one month only. And in this case, I'm going to use YD. So total number of days are 29 only. So this is a very good option and function provided by the excel, you must use it. If you have any queries, just let me know. I'll try my best to give the answer as soon as possible. 53. CALCULATE LAST DAY OF THE YEAR: Hello, everyone. Welcome back. In this video, we are going to learn how we can calculate the number of days remaining in a particular year. And that should be dynamic means whenever I change this date, it should adjust automatically. I don't want to change anything in the function. So let's begin. So first of all, we would like to know what is the last day of any particular year. As we know that it is 31st of December of that particular year would be the last day of the on, but we have to update the year automatically. So for that, we will use the year or the date function. So I'm going to start date and here we need to provide the year. I'm going to use year function as well, and I'm giving the reference of this date. Now what is happening here will be extracted from this date automatically, and now I need to provide the month, that should be 12 and I need to provide that day. That would be 31st. Now I get the last day of the particular year. Whenever I change the date, the year would be extracted from this date and used in this function. Now we have to substract from this date. We will get the number of days remaining in this year. So I'm going to enter, and this is in the date format, so I need to change it to the general. So total if today is 29th of July 2024, total number of days remaining in this year is 155. I used today function over here. So tomorrow whenever I open this worksheet, that will be updated automatically, and the number of days remaining results will also update automatically. So that is a very good option you must use in your daily routine work. 54. UNDERSTAND TIME FUNCTIONALITY IN EXCEL: Hello, everyone. Welcome back. In this section, we are going to start time functionality in the excel. We already discussed how date functionality has been used in the excel. As we discussed in the previous video, that excel convert the date displayed in the cells into a particular number, and that number start from the first January 19 hundred. Means whenever we enter any date in let's say first January 1 nine double zero, that will be converted into the number one. And second, January 19 hundred would be considered as number two and so on. So let's say I entered current date, that is second Fab 2024. And if I convert it, I want to display what is the number behind it, so I will change it to the number format or general format. So 45324 is the number considered for second Fab 2024 in the Microsoft Excel. In a similar way, Excel convert a particular time entered into the excel into a number. Let's say I'm going to enter 11 30, and the hours and the minutes will be segregated using a semicolon. So you need to understand this and 30, and we can also enter the seconds if we want by using another semicolon and to the second value. We can also entered AM and the PM to bifurcate the morning and the evening time. If you want, so I'm going to enter as it is. So that has been considered by the Excel as time value automatically. So let's say I want to change this format. So we have some default formats available. For this, we have to use custom or sorry, number format, and we can use time format from here or we can explore more number formats from here. So you can select time options, and these are the available formats. So let's say this one, this one, and click Okay, so AMPM will be. Changed automatically, that depends what number we have entered. Okay? And so after doing that, so this is the static number. Whenever we enter any value in it, that won't change. But let's say I want to input current time stem in a particular cell. So for shortcut key, using this is control shift semicolon. So the current time will be entered in the cell, and that would be study. It means it won't change whenever we do any update. But let's say, first of all, I would like to change the format. So I'm going to select format painter. In this case, I'm going to change the format. So it is 11 41. So that is a current time in the cell. Now, let's say I want to enter a time which get updated whenever I open this worksheet again or I do any changes. So let's say for achieving this, we will use now function. So in this, we will not enter any argument, only enter now and press Inter. So we can see the current time with the seconds in this format. We can set the format from here, which we already discussed. But here, as we have already entered or change the format of this cell into the time, if we enter the same here in other cell where we have not set any format, if I enter, the date will come, but we do not want date and time. We want to display only the current time. So to get rid of this problem, we will use now function and today function. So let's say now and we will substract today. Function we have already discussed about today and now what the system is doing, it is substracting the date from the time, the value we got from the function of now. I'm going to press Enter, and that is considered as a number general number, now we can convert this into our time using this option. So now, whenever we do any changes in the system, that will change. So you can see these three values are getting changed every time I do any changes in the cell of Excel automatically. So this is the way we can enter live current time in the excel shat. Now we will use some options. Let's say I want to join this value, the current time value with my sentence like the current time is so and so. So I can use this function. So we already discussed this function in the previous section in which we displayed today's date, the current date. Now we want to display the current time. So we will enter the string into the inverted commas, then we will use Epos key. And then this time we will use the text function. Text function is used to display the string or any value in a particular format. We are going to cover this function in detail incoming videos. So we use now function, and then we defined our format over here. So I want to display this in hours and the minutes with the AMPM values. So that will be updated every time I do any changes, you can see the second values has been changed. Okay, so we covered these options. Now we are going to use one more inbuilt functions called time in which we have to define three arguments, our minutes or seconds. Let's say I have hour minutes seconds in different cells, I have to use time function to display the time using these cell references. So I'm going to use hour then minutes and seconds closing the braid presenter. So whenever I do any changes over here, that will be changed automatically. I used 60 minutes. That is why I can see 9:00 A.M. So 59, so that will be 8 hours 59, and seconds are not getting displayed because we have not set the format. So if I use format painter to change the format, now we can see am values as well with the second value. Now we are going to use a very good function called time value that is used to convert a time value which is in the text format. So this value is in the text format. Let's say I have a lot of data which I exported from my ERP system, and that has been exported into the text format by default. Now I want to do some calculations using this time, but I won't get the desired results because it is in the string format or the text format. As you know that, strings cannot use for doing the calculation. So I want to convert this value into the number or the time value. So I'm going to use time value, give the reference. Only one argument we have to give. We can type inside the brackets into the inverted commas and then press Enter. I got my result in the general format that we can change from here. That's all we need to do. Save the sheet. So this is how we can use the time functionality. This is the basic understanding. We will explore some more options in the coming videos. So stay tuned. If you have any query related to the time functions which we discussed over here, just let me know in the query section. I'll try my best to give the answer as soon as possible. Thank you, friends. 55. SUMMING OF TIME VALUES: Here, everyone. Welcome back. In today's video, we will discuss how to do the sum of time given in the Excel. So let's take a simple example. I have 9 hours, 30 minutes. Now I want to add 6 hours in this time. So if I type six over here and if I do a simple adding calculation using simple plus sine and giving the reference of these two cells if I presenter. So what I get nothing. Here, we need to understand one thing is that how Excel is recognized a number. This is a simple number integer number, and this is a time value. So both are different values. When system is going to sum these two values, we will not get the desired results. So for this, first of all, we have to convert each value of the cell at the same format at the same values, then only we will get the correct answer. Doing this, first of all, we have to convert this 6 hours into the time value. For this, first of all, we have to press F two and then semicolon and then double zero. Now it is considered into the hours. So six plus 9 hours is 15 and 30 minutes. And here we got the correct results. Now I'm changing this to a different value, let's say here at 10:30. And here 23 hours. Now I'm going to add these values, what we get wrong value. Actually, I'm getting correct answer, but Excel didn't display the correct format. So we have to choose the correct format for display the sum result. For this, select the cell and then come to this point, click on this arrow, select the custom and come down, and here we have to select this value. We H is in brackets and then click Okay. So now we got the correct results. If you don't want to display the second, so you can remove the SS from here, and click Okay. Now we got the correct answer, 33 hours and 30 minutes. Now we are taking the practical example that we have a person's working days of a particular week, Monday, Tuesday, Wednesday, and these are the hours in work. So now I'm going to use some function to get the results. I'm selecting the cell, come to the hon tab and select the autosum. You can press Alt eco sine from the keyboard, press Enter or Control Enter to get the result. But here we can see 17 hours, 45 minutes and 0 seconds. That is incorrect answer. No, answer is correct, but the format in which we are going to display is incorrect. That we have to change to the format we created for this cell, selecting that cell, come to this point, click on this arrow button and come to the customs and select that option. Which we have created for the hours and this one and remove the Ss, if you don't want to display the seconds and click Okay. So 41 hours, 45 minutes is the correct answer which we need to display, and then we can do the calculations. So you need to take care of the format for display the particular time or the date values. 56. CALCULATE WORKING HOURS IN EXCEL: Hello, everyone. Welcome, once again. In today's video, we will discuss how to calculate the working hours. For this, we have taken the two example, one for the day shift in which the shifts start in the morning and end in the same date by the evening. But in the night shift, the days start in the evening and end in the early morning next day. So here we will use one in built function called Modi mode. But for the day shift, we can calculate very easily using the substraction method. So we can start our function by equal sine, then the exit time minus entry time, press enter, make sure you have selected the correct format for your daytime, which we discussed in the previous video. Now, double click on it, select the last cell, control minus sin to sum. And here also, you have to provide the correct format for your time. Now we are moving to our second example for the night shift. For this example, we will use MOD function, so equalsine MOD presta and for number, we have to find out for this, we will start by exit, then minus entry time and coma divisor one. Close the bracket, Control plus Enter and double click on here to copy the function to all the rows available at the last Control plus eqosine Control plus Enter and Control B to bold and control S to save the sheet. So make sure you use the correct format for calculating the time. Then only you will get the desired result in correct format. Then you can enter the rate per hour or you can calculate the total amount payable to these workers. So these are the methods you can use to calculate the working hours in aN. 57. CONVERT INTEGER VALUES INTO TIME VALUE: Hello, everyone. Welcome, once again. In today's video, we will discuss how to convert integer values into the hours, which must be displayed in the time format in the axle. We are taking three examples. In the first, we have 9.5 hours, which is in decimal numbers. So we have to convert this into the hours and the format must be hourly basis, hour and minutes. And in the second example, we have 500 minutes which we need to convert into the hours. Also display in the format of hour and the minutes, not the seconds. And in the third example, we have 65,000 seconds, which we should display in the time format. If we have hours, then we have to divide by 24. If we have values in minutes, then we have to divide this value by the 1440. And if we have values in seconds, then we have to divide by 864 double zero. And then we have to convert the result in the time format. I'm going to use this simple function and give the reference of the cell in which you have values, then divide by 24 and make sure you provide the format of our and the minutes. Just click over here to change the format in H semicolon M. Then ok. And now I'm going to copy not copy. I'm going to again, divide it by 1440 and change the format. And then we have to divide this 868864 double zero. And 65,000 seconds becomes the 18 hours, 3 minutes and 20 seconds. And for this, you have to change the values in the seconds as well. 500 minutes mean 8 hours and 20 minutes and 9.5 hours, we can display as 9 hours, 30 minutes. So this is how you can calculate or convert the integer values into the time value. 58. ROUND UP TIME VALUES: Hello, everyone. Welcome once again. In this video, we will discuss how to rounding up the time values. For this, we are taking the example of minutes and the hours. Let's say I have given some time value in the seconds, my task is to convert or rounding up this time value into the nearest minutes. So for this, we will first convert into the integer value. For this, we will use 1440, and then we will divide it by 1440. But when we are converting into the integer value, we will round up this number or the time value. Then again, we will convert this by dividing 1440, then we will get the rounded number. The same function or the same method we will use for the hours, but this time we have to multiply and the divided by 24 only for rounding up to the nearest hour. So let's get started with the example. So I have given this hour, 1 minute and 23 seconds. So I'm going to start my function round. Then I'm going to give the reference of this STC for multiplication, then 140 coma I have to round up up to zero, then closing the bracket, and then we have to divide it by 1440. Control plus enter to accept this and we can see 23 has been removed from this current value. Now we are going to convert or rounding up this to the nearest hour. For this, we will use rounder function. This time also we are going to give the reference of this cell and multiply it by 24 comma zero, closing the bracket and divide it by 24 Control plus Enter. We got 1 hour only. This is a very good trick. You must learn because in the real time scenario in your office, you definitely are going to face this kind of problem if you are in the HR department or the finance department. Even in the production departments, also, you may face such problems where you have to calculate or round up the values given in the different time values. So these are very practical examples you must practice and learn in depth. Thank you for watching. If you have any query related to all time values functions, just let me know in the query section. I'll try my best to give the answer as soon as possible. Thank you. 59. HOW TO USE IMAGES AND SHAPES IN EXCEL: Hello, everyone. Welcome once again. In this video, we are going to start how to use image saps and related options available in the excel. So for example, I have downloaded one invoice format, and now I want to insert the logo of my company here. So for this, we have to input image. So that can be stored in the One Drive or in your local hard drive. Even we can search any picture from the Internet than we can download and use in our Excel. So first of all, I have to remove this logo goes here. That is a shape, actually. So we will also discuss about these shapes and then we will create a similar shape in this video. I'm going to select and we can see the outline of the shape has been highlighted, then we can press the delete button from the keyboard to deleted. Now come to the Insert tab, Illustration, then come to the picture, click on the little arrow button on the downside. Then we have two options, one for import the images from the local hard drive and this option for importing the online picture. First, we are going to use this device, select the image where you have stored, and then click on Insert button, that will be imported in your Microsoft Excel and the outline border has been selected and the highlighted with little circles. These circles are used to reshape or crop your picture. So if we use the side small circles, so that can be used to reduce the size from the left hand side, but the aspect ratio of the original picture will be distorted. So we always recommend it if you want to maintain the aspect ratio of the picture, always use corners circ buttals. So you can reshape as per your requirement. Once you're done, release the mouse and put your mouse over the image, that will be converted. It means we can select it, and then we can drag to the desired place in the sheet. And you can also adjust the height and width and the size of the picture by using the corners ca buttons, even you can use these ones. If you think you have to reduce the height and width, you can adjust it according to your requirements. So once you are done, you can click anywhere outside this image. That all options will be gone. But now we are exploring some more options related to the images, select this one and you can see the picture tool under which we have picture format tab. So if you click outside, it's gone. If you select the image, this will be available for you. Now we are going to explore one by one. These all options are similar to the word and the PowerPoint. In which we inserted the picture, all are similar options, but I will explain all options quickly over here. So now we have some predefined picture styles. Select the image, come to the picture styles and click on this arrow button to see the available styles. Just over the mouse, the style of the picture will be converted. So whatever fill your requirement you can select and you can click on it to accept it. Let's say I'm going to use this one and once I'm going to select, then we can manage these styles by using these options. So we can manage the border from here. So we can insert the color of the border from here. These are the standard. If we do not wound, then select no outline, then select this option to get the more options. We can manage the width of the borderline from here and style of the borderline from this option. You want more lines and click on this option, we will get some extra options. Just explore these all are self explanatory. Then come to the picture effect, and here we have a preset designed, so you have to just click over the mouse and see what is the effect. And if it is as per your requirement, you can select and you can change. Then the reflection, the glow, soft edges, bevels, all are self explanatory, just go each and every options and see if it is for your requirement. Once you are done with it, we can move to the picture layout, and we can convert this into the Smart art, which we are going to discuss in this video. And after that, we are moving to the adjustment section. Here we can remove the background. Just click over here, and the background will be removed automatically. But if you want to make some changes, so you can select this option, mark area to keep the marking area. And if you want to remove select this option, and you can select this option to discard all the changes. If you think the background is removed the system has removed the background as per your choice, and you can keep the changes. So let me show you one example, so we can click over here, then we have to select like this. Using these pencils, so we can mark area to remove from the background. And once you are done, just click on the keep changes. So I'm going to discard because I don't want to remove. This picture is already a PNG without background. And then we can make the corrections from here. So these all our options available. Then if you want to change the color, and if you want to change the artistic effect, then we can also compress the picture if you think the size of the picture is very high. Let's say two MB or three MB, that will impact the size of your Excel file as well. So if you transfer this file over the Internet, that takes a lot of size in your hard drive as well. So it's better to always compress by using this option, click on it and select the level you want to reduce the size and click Okay, the size of the picture will be reduced. And we can change the picture from here if you want, by using this option, the picture will be changed, but all the styles and the formatting we have done will be same. And the last one is to reset, if you think all styles which we applied are not good. If you want to reset, just click, all formatting will be removed in single click. So these are the options you can explore. We can rotate by using this option and we can align by using these options. And we can crop this. So let's say I'll give you one example. If you want to crop select this option, and we will get some extra options. So let's say you want to crop from the bottom, select and your cursor will be changed. Now move up aside. Once you're done, just click outside. The picture will be cropped. So I'm going to reduce the size and place it here. So these are the options related to the images. One more left, we can import the image from online source. So select that source, and one new window will pop up, which is provided by the Microsoft here, all images, which is copyright free are provided by the Microsoft. All are categorized, so you can select any picture. Let's say I'm going to select this one and you can see the option, creative commons. It means these all pictures are copyright free. You can use. So let's say I'm going to select this one, inserting. And here I would like to show you the background removal options. So once you select, then click on remove background, system will automatically remove the background. Let's say I want to keep this area, so I will use this pencil to mark on the head and this area as well. Remove it from here, all done. Now keep changes, and you can see the background has been removed. So you can resize. Now we can crop this as well. So using this option, now I'm cropping from right hand side. And from the left hand side as well and click outside. So you can see the size has been reduced. So now we can adjust and place it anywhere where we want. This is how you can import the images. Now moving to the next option, we are inserting the shape by using this option from the insert and same illustration grouping, select the shape, and all shapes are categorized, so you can select any of your choice or requirement. Let's say I'm going to select this this one rectangle box, and I'm drawing over here. Once I release my mouse, all shape related options will be available under the drawing tool. If I select any sail that gone from the tab, I'm going to moving this picture, I'm selecting once again, and we can increase the size by using the same cornered circle options from the outline of the shape. And come to the shape options. So here we have some in built shape styles. We can select any that will be applied in a single click. We can change if you want, and we can manage those formattings by using shape fill option and shape outline options and shape effect. All are similar to the images which we already discussed. So if we are going to insert some text, let's say, I'm going to enter your logo goes here, First of all, I will change it to this one. And now I'm going to click on this selecting this option, so that logo will be changed and I want to increase the size of this text. So I'm going to home. I'm going to and then perfectly all right, select this shape and moving now you can move it here and you can adjust. And we can reduce the size. So this is how we can use the shapes. I will explore some more options, click over here, so we can edit the shape, and we can insert some more shapes from this tab itself recently used, and all categories are available, so you can explore. I'm going to give one more option. Let's say, I'm going to use this star. And once I click on it, so we have some options to, you know, make some changes, and I'm inserting one more shape, let's say, star in this case. And this time we have one more option if you select this one, small yellow circle. So that is used to change the shape of your image or the shape you have inserted in few shapes if it is applicable. So you can use this option as well. So after selecting, you have more options like text fill option, text online, and TaxifecT is for bring it to the forward. And let's say I'm going to move up a site and I'm changing the Okay. Color to different one. And now use this option to send it backward and use this option to bring it forward. And this is for the alignment, and this is for the rotate, and you can manage the size manually from this option. So you just explore these options related to the shapes and the images, and these all are very similar to the word and the PowerPoint, which we already discussed in some previous sections. If you have not explored yet those videos, just go ahead and see I explained in details each and every topic in Word and the PowerPoint. Hopefully the concept of the images and the shapes are cleared. If you have any queries, just let me know. I'll try my best to give the answer as soon as possible. Thank you, friends. 60. DATA VALIDATION: Hello, everyone. Welcome, once again. In today's video, we will discuss about the data validation. Example, we have created one salary slip in which the user has to create the salary slips for each month and user has to just enter the employee code over here. All values will be changed in these cells automatically. For this, we have used the Lou which we are going to discuss incoming videos. So we have created this pay slip and whenever user change the employee code from here, these values retrieved from the database, which is available in the next sheet. So now, sometimes what happens if user enter wrong employee code, that will give wrong information. So we want to restrict to the users to enter the employee code within the particular range. For this, we can use the data validation option of the Microsoft Excel. So first of all, we have to check what kind of validation we want to apply. So let's say we have employee code from 13 times 02, one, two times zero, 1616, it means. So we have to restrict the user to select the numbers between these range only. For this, first of all, we select the particular cell in which we want to apply the data validation. Then come to the data tab, come to the data validation, select the data validation option, that data validation pop up will appear. Here we have three options settings, input message and the error message. Select allow option and select whole number, then select the data. We have to give the comparison. Let's say I want number starting from 14 times zero, one, two, one double 016. So that would start. So for this, I will use the between first and then minimum number would be this one and the maximum one double 016. I think I need to increase the one number as well. So once we are done, then we have to click Okay, I think I need to increase. Let me see. So we can select the number from here as well. So let's say this one, and we have to first remove, and then we have to select this one, presenter, and we can select this value from here and then click Okay. Now, if I'm going to enter some other value, system will provide a message. This is a warning message means the value doesn't match the data validation restrictions defined for this cell. So do you want to retry or cancel it? So the system will not accept that entry, and I'm going to cancel this one. This is how you can use the data validation. We have some more options related to the message provided for the users. For this, again, come to the data validation option, select this validation, and this time, select the input message. So that option must be unable, then we have to provide the title and the input message. Let's say, input and sorry, input and here, enter the code from two, one, 16 only. Think I need to remove some zeros. And then click Okay. And whenever we select or any user select this cell, so he will get the message. Input means enter the code from this range to that range. So that is one option. Now we are going to select also one mode option called error alert. It means the last message, the error alert we saw when we entered the wrong input in this cell. So we got the style, stop. It means you cannot enter. System will not allow to enter the wrong data. We have two more options, warning and the information. In the warning, we can give the warning title and a custom message from here. You have entered wrong employee code. Please into correct code within range, and then Okay. So this time system will allow to enter, let's say, I'm going to enter this one. So this time, I got the warning message. But this time, I have option whether we want to accept it, no or cancel. So I'm going to cancel this one. And if we want to give information only, then we have to give the title and the message then click Okay. And if I enter the wrong option over here, the sign has been changed. It means system can accept the entry if we press Okay, but we are not going to accept it. I'm going to cancel. So this is how you can use the data validation option. We have one more very good option. Which is called drop down option. So I want to provide a drop down list for the users, so user can select the value from that list only. So for this, let's say, I'm going to select the cell in which I want to provide it, and I'm going to select data validation. And this time, I'm going to setting. I'm going to select list option this time and source would be I'm going to click over here, come to database sheet, and we have to give the range for which we want to provide as a drop down option. Once you're done, presenter and select this option if you want to apply these changes to all the other cells with the same settings. So I'm going to select Okay. And if now user has option, if we select this option, this drop down option, click over here, and then user has to accept any value from here and data will be. Able. So that is a very good option for avoiding any kind of validation options. So user can select any value from the drop down if he selects. So this is the validation rule options. So you can explore more options like clearing the validation, and we have some more options like date, time, text length, and the customs as well. This is available in our advanced course of Excel on UM. So you can check and you can join if you want to learn in depth data validation in Excel. 61. LOGICAL FUNCTIONS: Hello, everyone. Welcome once again in today's video, we are going to discuss about logical functions. Logical functions are used to compare two values and give the results in true or false. Then we will use if functions to do a specific task based on the results we got it. These are very important functions for data manipulations and for MIS related purpose as well. So let's explore all the options. So we're going to cover five options, five in built functions, logical functions. The first one is. In the end, we can do the logical test, and if all the logical tests are true, then only we will get the true answer, otherwise, false. Second is, or if we use this option, and any of the argument is tree. Let's say we compare two logical test, and if any is true, then we got the answer in true. The third one is if error. Sometimes we do the calculations in which may possible we get the error, and we use that cell value calculated cell value in other function. If we get any error, it may possible in the next step, we also get the error. So to avoid this kind of situation, we use if error function that is very important and must learn function. The fourth is naught is used to reverse the logical by its argument. Let's example, we did some logical test, and it becomes true, but we want to convert into the false. Then we use not option. And the last, which is the most important logical function in the Microsoft Excel or in any spreadsheet, which is the I function, in which we will provide three argument. In the first, we did the logical test. If we got the value true, then we provide second argument, and if it is first, the third argument. So we will discuss about all the logical functions in this video, so stay tuned and stay focused as well. So the first one is t, and I have two values over here in different cells. Now I want to do some logical test. So I'm going to start my logical test by using equal sine. I type A, press tab, now do the logical test. So let's compare. This value equals to this value, close the bracket presenter. False. If I change the value to 300, we got the true. I will insert some more values over here. Now I want to compare, let's say, and, and this is my first logical test. This value equals to this, then use Coma to give the second logical test, give the reference of this value is less than 500. Close the bracket presenter. True. If I decrease this value, false means only one logical test is true. That is why we are getting false. If both logical tests are true, then only we will get the result in true. Now comes to the R here, we will compare, let's say, sorry, R and the first logical test 200 is less than 20, or we can give the reference. We can type the value inside this argument. And the second logical test is this equals to this. Close the bracket presenter, false because 200 is greater than 20. So if I increase this so that condition will true and we got the answer in true. But here we did the two logical test. These two values are not equal false, but 200 is less than 400. That is why we got the true. Now moving to the if error function. Let's say in some calculation or cell, I got the NA value, which is the sign of error. Now I want to convert into some message in text string or we can do anything like calculation, we can enter zero. We can retrieve some value or we can do any kind of action which is available in the Microsoft Excel. So if error. So we can retrieve this value by referencing any cell value, or we can also use other function to get the value. So use the simple test first, then go to the advanced one. I'm giving the reference of the error of above cell, the coma. So what is the value you want to enter, and what is the action you want to do when we get the NA or the error in the reference we had given? So what we want to we want to convert into the GOT error message, which must be in the GT error in the cell. That is the message I want to give. So that spring must be in the inverted commas presenter, and we got it. If I change it, then I got the one. Otherwise, I got the message which I entered over here. Now we will do the naught so I'm using naught tab, and here I'm getting true, that will be converted into the false. And now we are going to use I, which is very important. So eqosine I. So here we have three arguments. First is logical test. What is the logical test I'm going to use? I want to check whether the cell value is the equosine equals to hello. So this is the logical test I want to do. I want to compare the values of the cell C 14. If it is true, what result or what are the action I want to do? I want to give the answer. It is a string, that is why it is coming in the inverted commas. So what is the action I want to do or the if function found value falls? No. Close the bracket presenter. Yes, I I change it to another, no. I'm going to give one more example. I want to compare the numerical values. If cell value of C 41 is less than sorry greater than let's say 50 coma the cell value is true. The second, if I got the false, the cell value is not true. You can type any string, you can do the multiplication. You can do anything. You can use any function, any built function, you can plus minus. So you can do whatever you want to do in the second or third argument of the if function. So in the first, we do the logical test, then in the second, we do the action, what we want to do if the value is true. And in the third, we take the action if the value is false. So this is how we use the logical function in the Axl we have some advanced uses of the logical functions in our Advanced Excel course, which you can join in UDM. Just go and check and join for advanced uses of the Excel. Thank you for watching. If you have any queries, just let me know. I'll try my best to give the answer as soon as possible. Thank you. 62. VLOOKUP FUNCTION: Hello, everyone. Welcome back. In this section, we are going to discuss about the lookup formulas. The lookup formulas or the functions essentially returns a value from a table, and it search the value by another value. For example, telephone directory. First, we want to find out someone's name, then we retrieve the corresponding number. On the very left hand side, we have name, and then we have right hand side, their number or address or other information. Lou function will work in a similar way. It search the name on the left hand side and then provide the values from the right hand side. It has four arguments. Lookup value. First, we need to provide the value which we want to search. Then we give the table array, the range in which the complete data is available, which we want to retrieve, and then we give the index number of a particular column, the values which we want to retrieve. These three arguments are mandatory. A fourth one is optional. But it has two values which we have to provide true or false. If we omit the value, the approximate means the false value would be entered by default, and we have to provide true. So if we provide false, it will provide the approximate value. Otherwise, it will search the exact value. So let me explain what is the exact and the approximate value. So here first, we will take the example, very simple example of income tax rates. So here is my slab. If income is zero to two lac rupees, 200,000, we can say, the rate is 0%. Then next lab is 200,001 5,000,000 to 10% and so on. Now I have my income in the numbers here and now I want to retrieve the tax rate directly from here using the Lou function. So how I can achieve this, I will start function Vlookup, press step, then I will give the reference of my value, then COA then the range or the table in which our data is available, then COA I will provide the index number. So we have three columns one, two, and three. I want to retrieve the tax rate which is in the third column. So Coma three, and now I do not want to provide my fourth, which is the optional argument. So in this case, press Inter, I retrieve the value, 20%. Let's check. It's approximately 600,000. So which is coming in this slab and 20% is the tax rate. So let's say I want to reduce it to 1,000. So 0% ax is coming. It means the number value is approx not the exact value. The searching value. So we are giving this range and then number is coming within that range. So that is approximate value. So now we are moving to our next example in which we will use the exact value function. And here I have database of my employees. In the first cell, the lookup value is employee ID and the name is given in the second column. Here my employee ID, and I would like to use VLookup function. So this time, I'm going to provide lookup value. And coma and then table array. In this time, I will provide like this, then coma the second value I want to retrieve, then coma and I want to use falls, which is the exact number. Then press tab, close the bracket, Control plus enter, so we can see it's one double zero, one, two, five, six is not available. That is why I'm getting error. If I change it to one, so that name will be retrieved by Voca function. So this is how we can use our VLC up function. So if I provide let's say true. So that time I will not get any error. It will provide the approximate six. So it will provide the approximate value, which is Shiva. So it will considered as approximate value one double 05, which is the nearest value. 63. ADVANCE USE OF VLOOKUP AND HLOOKUP FUNCTION: Hello, everyone. Welcome back. In this video, we will discuss about the H lookup function. In the previous video, we learned about the V lookup, which is vertical lookup. H lookup means horizontal lookup. It is very much similar to the VL up, but here the value will be searched horizontally. So the same table which we discussed in the previous section was vertically. So I have income slabs vertically in these two columns, but these same values are available in the rows right now. And my third row having text rates, which we want to retrieve using the Achal. I'm going to use Acho up. All arguments are same. So I'm going to use H Lou, press step and give the lookup value, coma and give the table array, and we want to retrieve the third row, and this time, I'm going to use two approximate value, not the exact one because this time we are going to search using the range only Control plus Enter or Inter. So we can see the range, and the results we are getting over here is correct. So if I will change to 200,001, then it is 10%. It is coming directly from this row. So this is how we can use the AchilaFunction. Now we are moving to our next example. So in this example, I'm going to explain advanced uses of the VLookup using multiple functions or the formulas which we already discussed in some previous sections. So here is my example. We do not want a blank name over here or the amount in the search results. So in this case of this name, we have blank, and for this also we have blank amount. I do not want to display the blank. Instead, I want to use zero. In this case, I'm going to use lane function and the logical functions which we discussed in my previous section, I'm going to use if lane and the VLuca. Let's begin. I'm going to delete this one. So I'm going to start if now I'm going to check whether the amount is blank or not. Blank means there is no length. So for this, checking the length, I'm going to use an function, and I'm going to give the text and what is the text which we can retrieve from using the V up function? I'm going to give the lookup value coma than the range. And then COA index is second. Ca I want to retrieve exact match. So I'm going to use false this time. I'm closing the bracket for Vu, and if the lane is zero, so I got the text for which we want to see the lane and then closing the bracket for lane function and equals to zero. Then I want to display zero. Otherwise, again, we have to use Luca function. And coma second Coma falls, closing the bracket for Velca and closing the bracket for I now press Enter. So what we get zero over here. If I change the name so which we can use for retrieving the name, we can use the validation function list as a drop down. So I'm going to use data validation. Come over here, select the list and give the source to these names, press Enter and then Okay and select the value. So in this case, it is RAM. So Ram having 200,000 in the amount. So the exact amount I'm getting in case of Nagma and sham the value is zero. Not zero, it's blank. So I would like to put zero. Sorry, I want to change to Nagma. And in this case, if it is zero, it will be converted into the zero which we have given in the second argument of the I function. So this is the advanced uses of the La functions which you can explore, and you can provide me some advanced uses you can also try. If you face any problem, just let me know. I'll try my best to give the answer as soon as possible. Thank you.