hey everyone welcome today we will learn Exel tutoring but before we begin if these are the type of videos you'd like to watch then hit that like And subscribe buttons and the bell icon to get notified just for a quick info if you want to upscale yourself and master data analytics skills to land your dream job or grow in your career then you must explore ours cohort of various data analytics programs our offers post graduate program from poro University in collaboration with IBM through this program you will gain knowledge and work ready expertise in skills like prescriptive and Predictive Analytics regression classification and over a dozen others that's not all you also get the opportunity to work with multiple projects and learn from industry experts in Top Tire product companies and academic from top universities after completing these courses thousands of Learners have transitioned into a data analytics role as a fresher or moved onto a higher paying job and profile if you are passionate about making your career in this field then make sure to check out the link in the pinned comment and description box below to find the data analytics program that fits your experience and areas of Interest now without further delay let's get started what is Microsoft Excel so Microsoft Excel is a software product designed and developed by Microsoft storing data in an organized way that is rows and columns and Microsoft Excel is also capable able to manipulate data through some mathematical operations followed by that Microsoft Excel is also used to extract the insights from the data and represent it in the form of visually appealing graphs and charts now we have a basic understanding or an overview of what a Microsoft Excel software product is now moving ahead we will understand the fundamentals of Microsoft Excel so following are some fundamentals that you need to know before getting started with Microsoft Excel so basically when you install the Microsoft Office in your computer you will have various Microsoft products out of which Microsoft Excel is one of the product so we will be dealing exactly with that particular product that is Microsoft Excel so when you get started with Microsoft Excel this is what you will see in the first page so this particular page is called as Microsoft Excel homepage where you will be having various varieties of sheets Microsoft Excel will give you some suggestions based on the type of sheet you want to work with we will see this in a better way through the Practical session so once you get started with the sheet you will have some more options so this particular option is called as the toolbar menu you will have the file home insert draw page layout formulas data review View and help so these are the tools that you will be using to work on your data using Microsoft Excel furthermore we have a toolbar ribbon so when you select some of the other option from the file home insert draw page layout formulas data review View and help buttons you will have a ribbon so for example you can see that I have selected the Home Tool here so when I press on the Home Tool this is the ribbon which Microsoft Excel gives me so this ribbon has some options in it which can perform various operations now in a further more detailed way we will have toolbar groups so when you see in the previous slide we have a complete toolbar ribbon so this particular ribbon is segmented into groups so you can see the first group as paste Cut Copy format painter etc etc and the second group is the font the size of the font and to increase the size of the font to decrease the size of the font bold italic underline etc etc and here you can see the text alignment so each and every group has separate functions so each set is called as a group and I think you can see a small Arrow option over here so this Arrow option is used into the toolbar groups when the group is not able to fit all the operations or all the functionalities in one single provided section so when you click on this particular Arrow Mark you will have another dialogue box so this is called toolbar more options so you can see that when I clicked on this icon you can see a new dialogue box which opens me a new set of operations which are not able to be fit in this particular group so we will also see more about this in a better way in the Practical session and now moving moving forward we have cell and address so when you open a Microsoft Excel sheet you can find boxes so each and every box is named as a cell and each cell has its own address for example the highlighted cell over here has an address B3 so B is the column name and three is the row name and apart from that you can have the sheet tracker in the bottom left corner of the Excel sheet where you can navigate through different sheets and in the bottom right corner you have an option of increasing or decreasing the sheet size so these are the basic fundamentals of Microsoft Excel that you need to keep in mind before getting started so we will have more on this in the Practical session if getting your learning started is half the battle what if you could do that for free visit scaleup by our click on the link in the description to know more so now we enter the demo inventory and macrosoft Excel so we will be using Microsoft Excel to create a sheet of the employees in a company so basically an employee in a company has employee ID name and designation salary etc etc so we will be trying to create the same table using Microsoft Excel but before that let us understand the fundamentals of Microsoft Excel through the Practical demo first so I have started my Microsoft Excel and this is how the homepage of Microsoft Excel looks like so you have a blank workbook over here if you want to create a new workbook you can select new Option so Excel will provide with various variety of sheets you can see money in Excel adjustable meeting agenda streaming showers small business cash flow and many more if you're not able to find what you're looking for then you always have an option of selecting the particular type of sheet what you're looking for so you have various options if it's business if it's personal if it's planners and trackers list bdet charts etc etc so let's imagine that you wanted something from business so just by clicking at business option the Excel will load a variety of sheets related to business opt option so this might take a while so you can see that the Excel is loading few types of sheets so you can see that Excel has provided us with some online varieties of sheets for example any calendar business expenses Channel marketing budget budget summary report blue product list etc etc you can see construction proposal envo you name it Excel has got it so Excel will provide you with some variety of options based on your requirement now for this session let's get started with a plank workbook which looks something like this since this tutorial is based on the fundamentals we'll go with the blank workbook now over here you can see the toolbar that we discussed earlier that has the file home insert draw page layout formulas data review View and help so this is the toolbar and under the toolbar I have selected home and you can see this is the particular ribbon what we discussed about this particular ribbon belongs to homepage and when you select file option you'll get back to home and if you select insert option you'll will have another different ribbon with different groups etc etc so every particular tool has different ribbons in them and remember the extra options that we discussed when you press over this Arrow key this is it so when you press the arrow key you'll have few more settings which cannot be fit in this particular section of groups so you can have some variety of options over here of changing the font changing some effects to the text font size and font style etc etc apart from this we have also discussed about the cells in every sheet so this particular cell has an address so you can see the address over here which is B3 so B happens to be the column name and three happens to be the room name now we also had a discussion about the sheet tracker right now we just have one sheet if you want multiple sheets you can just feel free to select on the plus option which will always create you some extra sheets and you can navigate through sheets just by pressing on the sheet name and when you get back to the bottom right corner you have an option of increasing and increasing the cell size or the sheet size now let's keep it default with 100% now these are the few fundamentals that you need to keep in mind before getting started with Microsoft Excel now that we know the fundamentals of Microsoft Excel let's get started with a practical session which is about the employees details in a company now let's select this particular cell and let's type in employee details yeah we have the cell now an employee details table will have the information related to employees so the information will be about name it will be about employee number it will be the designation and maybe salary and maybe blood group as well and uh let's take another one which is phone number yeah so so far so good and uh you can see that we have some problem with this particular column the designation the name uh the the name of the designation is practically good but it is not visible so when you uh when you're not on that particular cell you can see that the name is incomplete over here so you can always fix that you can just you know manually change the size of the row or cell or you can also feel free to you know double click on that cell which will automatically you know set the size of that particular cell and same goes to the blood group just let's try to double click on that and same goes to phone number great so you can see that the employees details are just confined to the first two cells it's supposed to be somewhere in the middle right so no problem we can do that as well we can select all the cells and we have an option of merging them you can just select this one which will help you with merging and centering that particular data to the center part so that's how we do it now let's get started by adding the names of the employees uh let's add the names Joe John uh Mary Mark Susan and then Jennifer let's type in Mike let's type in them Jeff Jeffrey yeah we have a couple of employees now let's type in the employee numbers yeah we have the employee numbers now let's type in the designation okay let's choose uh Joe to be the CEO of the company and John as the software developer and Mar as tester Mark and finance Susan also in finance and Jennifer in testing and Mike in uh marketing same goes for 10 and again Jeffrey into software development and uh Morgan into test testing again now let's click on C so that it gets you know resized according to the length of the text now it's done so let's get into salary uh $110,000 and $115,000 and $199,000 let's increase the salary of a CEO so let's keep it $1 lakh and finance again 20,000 so yeah the salaries are allocated again the blood group e yeah we have the blood groups now let's type in the mobile numbers [Music] yeah now we have typed in some random mobile numbers as well so uh yeah this is how you can add in some data into your table and now let us imagine that you forgot to add or remove a column so let's imagine that we wanted to add a serial number as well but somehow we forgot to add it now you can always add a new row or column for example here we wanted to add a new column so we just have to right click on a select the insert option here we have a new row now now let's type in serial number and let's type one now can you see the small box option over here if you just drag it you can you know copy paste all those over here and now let's right click and fill series now we have the employee number starting from 1 to 10 that's how you do it and apart from this you you can also uh you know change the font of the entire row we can change the font to say uh aroni and and you can always also change the font and same goes to the employee table you can select it bold it and you can also increase the size and again select a color for the text maybe a different color green would be better and uh you can also select the entire cells and align them to the center looks more good and you can select or double click the row names so you'll have the proper spacing between all the rows and columns so we have double click on the column right yeah so basically that's how you make things happen now let's save this so I'd like to go to the save option and uh EMP data let me save this in my local location and just save it's done so that's how you work on your Excel file with some basic data and to learn more don't don't forget to get subscribed to ours YouTube channel and don't forget to hit that Bell icon to stay updated so you are the first person to get an update on any technology not just Excel now we are on the Microsoft Excel and this particular spreadsheet is related to employee details yeah now the screen is visible a bit better now let us imagine that this is our confidential data and you want to edit only two columns that is the designation column and phone number column now let us imagine that the company has finished one annual year and now there are some promotions happening in the company and let us also imagine that the phone numbers of the employees provided by the company have undergone some changes so now the minor edits that you want to make are related to the designation column and phone number column now you can see that all the columns that is the blood group new salary salary hike current salary and name of the employee serial number employee number everything is editable so in such kind of scenarios when you pass on this data there might be a possibility where your colleague or subordinate might end up making some mistakes right so you want to avoid that so you can do that by locking the CES now let us rename the sheet now the sheet has been renamed successfully so now you can log the sales by the following process so in this process you have two major steps first one is to log the CES and the next one is to protect the CES by a password now when you select all the cells in this particular sheet you can just click on this edge here and you have all the cells selected and now right click somewhere on the sheet and you can see an option called format CES so in this particular option you have some options provided which is number alignment font and you want to go into the protection part so you can see by default Excel keeps all the cells locked now we want the phone number and designation to be unlocked okay so let's cancel it for now and get back to the sheet and select the columns D and I and now let's right click and go into format Cs and in the protection option make sure that you uncheck the loged icon now select okay and now now okay so you cannot include the merged cell fine we have a cell here which is merged okay let's remove that okay now again select the entire column right click get into format cells and uncheck the lock option select okay now we have finished the first part that is locking all the cells which you don't want to get edit and unlocking the cells where you want to make some edits now the second stage is protecting the sheet now right click on the sheet name and you can see an option called protect sheet click that and now here you can see select locked sales and also select unlocked sales so let us provide a password here so let us use some simple password so that we don't forget that so I'll be selecting 1 2 3 and now select okay re-enter the password to proceed now let us type 1 2 3 again now select okay and now the sheet is protected now we have successfully locked and protected all the cells in Excel now let us verify that the cells you wanted to protect are really protected and locked or not so we have unlocked phone number and designation and everything else is locked now let me try and edit the blood group so when I double click or try to edit this particular sheet then I'll be receiving a warning from Excel it says the cell or chart you're trying to change is on a protected sheet to make a change unprotect the sheet you might be requested to enter a password so it says that this particular cell is locked and you cannot edit it now let us try to edit the phone numbers which we kept as unlocked so you can see I can really edit the unlocked cell here now let me try to change this phone number let me enter some random phone number so you can see the cell is editable now let us go through the designation column and try to edit this particular colum now since I said that there's a promotion happening in the company so the deputy CEO is now the current CEO of the company and the software developer happens to become the senior software developer and the tester as well let us imagine that he will become the senior and so on okay so this proves that this particular sheet is editable not the entire sheet but only the Cs that we kept in the unlock mode okay now this is how you lock and protect your sales in Excel so to understand the page setup in msxl we will go through a demo inventory in Excel where we'll be considering some restaurant data now without any delay let's get started with the Practical demo now we are on the Excel sheet and this is the data I was talking about the restaurant data where we have the First Column that is Rank and next we have the restaurant name followed by that we have the sales happening through that restaurant and then we have the segment category that is what kind of service does that particular restaurant offers now this is the list what we made and uh what if we wanted to represent this data in the form of a printed paper right so I guess you can see all the columns and rows here so you can see that we have almost like 250 rows so there is no chance or there is no way to print all those 250 rows in one page of course it is practically possible but when you try to read the content it'll be so tough to read Because all the rows will be clued together so closely so that you cannot read it so uh you need to print them Page by page so and you need to also take care of the data is aligned properly according to the page size everything and even you need to take care of the margins of your page right so that's what page set up basically means so we had a detailed overview of our data and what actually we're trying to make now let us see how page setup is possible in msxl so to go to the page setup menu or go to the page setup options we have three methods let's start with the simplest method so the simplest method is by just clicking on file and go to print option and you'll see all the page setup options right over here and you can also go go to print menu by pressing a shortcut method that is contrl p so you'll directly end up on the page set of options now let's look at the second method so the second method is you can see the toolbar over here right which has file home insert draw page layout formulas data review View and help so the second method we can use page layout option from the toolbar and you can see when you click on the page option from the toolbar you have a new ribbon over here so this ribbon is all about page setup options so it will offer you margins it will offer you orientation size print area brakes background print tiles etc etc right so all these options or menus in this ribon belong to page setup now let us look at the third method so the Third method is something similar you have view option on the toolbar so just select View and you can see we have few page setup options over here as well the normal page break page layout custom views and we have grid lines formula bar heading zoom in zoom out select to zoom free certain panes right so all these are also the page setup options so we have the three methods to enter the page setup options now we we are familiar with those now let's get started with the Practical mode on page setup for that let me use the simplest method that is holding the control key and pressing P which navigates me to the print option now we have various set of options here we have no scaling normal margins letter portrait orientation collated print active sheets and note one for Windows you can see this option is the printers option for example example if you're connected to a printer which is the hardware printer then your msxl will show the printer's name here so other than printer you can also make some other arrangements where you can just directly print your complete data in the form of PDF and then you can export your PDF to your recipent that's also possible okay now let's get started with the first option that is print active pages okay to understand this better we need to create a new sheet not a problem so you can see that we just have one sheet over here so let us kind of select all the items and create a new sheet and try to paste it over here or let's try to paste it over here okay now let's try to extend this yeah this should be fine now let's rename this sheet sheet uh or let's rename it as restaurant sheet two for our reference now let us get back to the print sheet option so I'll use the shortest method that is holding the control key and press P now you can see that here we have six pages in total from the active sheets so the current active sheet is the top 250 sheet so this particular sheet is the current active sheet so if we print the entire data in this particular sheet then we will end up printing six pages now let us select the other option where you have to print the entire workbook right so now you can see that there's a change in the number that is 12 pages so what is a workbook so basically workbook is the collection of entire sheets in your Excel homepage so in this particular Excel homeage which you have the first sheet that is top 250 and the second sheet that is the resturant sheet two right so the combination of these two sheets make up a workbook so when you select the entire workbook option you'll have 12 pages so that's how we use this setting where you select the active sheets the entire workbook and there is another option where you can only print a selected item so if you want to do this then let's get back here now what if your customer or what if your client wanted the top 10 restaurants only so you can select the top 10 restaurants by doing this now you have the top 10 restaurants let's go to print so you can see Excel has automatically selected the selection only print the selected content only so you can see we have only the top 10 restaurants or top 11 restaurants that we have selected in our sheet and it is ready to get printed so this is how we use the first option now let's get back let's not select that and let's make the pages normal now get back to the print option again now you can see the next option that is cated right so in cated you have different different options that is 1 2 3 1 2 3 and 1 2 3 and the next one which is uncollated 1 1 1 222 3 33 so why are these numbers present here and what does colled mean anyway so this is your question right now the answer for this question will be a little similar to your examination Hall okay let us imagine that you are an examination Hall and you are the invigilator and you have 10 students in your room so you wanted to provide question papers to all the 10 students okay so you have your question paper distributed into three sheets that is you have marked questions and your questions are fit into three papers right in this scenario we have 250 rows and all the data is split into six sheets that is sheet one or page one page two page three page four right now if we wanted to print 10 copies then the arrangement of papers will be page 1 page 2 page three page 4 page 5 Page 6 in continuous order right that is cated if you wanted 10 copies of different different ways where you wanted to print the first sheet first then you wanted to print the second sheet second and the third sheet third that is you'll have 10 copies of first page 10 copies of second page 10 copies of third page and so on that particular approach is called uncollated approach and if you're printing the pages in continuous order for 10 times then that particular approach is called as collated approach so this is what we had to learn about collated and uncollated now let's get back to the third option now let's press again print now we have our data over here there's something wrong select all the elements control p yeah now we have our data back now we have the third option that is the portrait orientation okay so when we click that we have two options that is portrait and landscape so you might be having a good idea like when you try to click a picture in your phone we have two modes as well that is portrait mode and the landscape mode so when you click on the portrait mode this is how your data will be looking now to understand the difference let's click the landscape mode so when you click the landscape mode this is how your data representation will change you'll have a wider page to print so this is why we use the page orientation for now let's get the page orientation as portrait orientation and we have now the fourth option so what is the fourth option we have various sets of options in here that is letter and A4 size there are also some more page options over here where you can select the page types you want to print so basically you'll be having few more options that is A4 A5 A3 Etc but here we just have two that is A4 and letter so basically you'll get those options and followed by that next we have margins so here you can see we don't have any margins for our data okay so now we can add some margins by setting up this one as an option like last custom setting or you can also choose normal wide narrow right so your page will be changed according to your margin size right so this is how you can change the margins now let's keep it as default let's set the normal one now we have the next option that is scaling option so here you can see we are trying to fit the page so when you're trying to print these sheets using no scaling then the actual size of the page will be printed now you can also modify that you can fit and print these sizes so you can remember the first option that I said of printing all the 250 rows in one single sheet this is how you can fit it so fit the sheet on one page where you can see all the 250 rows are fitting into one page but the data is not readable so but we don't want this type of an approach right so let's keep it as the default one which is no scaling so these are the variety of options in print now if you wanted to print the all the six pages into the PDF format and mail it to your client you can also do that I think I have explained it in the first place so you can see we can do it by just selecting this option where you can print all the six pages just and into PDF format and then you can mail them to your client and you can also select the number of copies you want you can increase the copies to two three and any number of uh copies you want for now I'll keep it as one [Music] copy now let's print this now let us select the location rename it and publish now let's get back to documents and see if the page is printed or not here you go here we have the resturant data in the form of PDF so we have our entire six pages of data in the PDF format now we are back on the hom page now we had some limited functions in the print function over here we had try to export the data so you can also export the data or try to print it from the print option over here you can just select that as well F PDF yes the pages are printed now there you go we have the restaurant 2 PDF [Music] data so that's how you can print all your data you can either choose the export option or you can either choose the print option now when you see into the print option we have limited number of PID setup options over here now let's check out what we missed so we are in the pce layout here so we had gone through the margins we had set all the margins and then we have also gone through the portrait and landscape now you can see this size when we were in the print menu option we had only A4 and letter but here you can see there are a wide variety of options that is other than letter we have tabloid legal statement executive A3 right we have A5 and many more other options and if you want some more paper options you you can go to more and you can always select a few more options that's how you do it and yeah we didn't had the break option so remember when we had been through the print option we did not have the break option there let's select this to normal A4 so you cannot see the break option over here right so here we have the break option so what does break option does right you can actually break the page uh remember we spoke about the top 10 things or thep top 10 rows you can always do that so you can just keep the top 10 restaurants in your first page and then break the remaining and to set page breaks you can select the cell now I need the top 10 so I'll select this particular cell which is in the 11th position and now I'll try to implement the page break insert a page break and now you can see on my sheet there is a thin line which is separating the two pages that's how you can see the indication of a page break implemented onto your page now how do you see it you can see it by SEL in control P where you can see the first top 10 restaurants being printed on your sheet so there you go we have the first top 10 restaurant printed on our sheets so that's how you use the page break now let's try to eliminate all the page braks so you can select breaks and reset all page breaks so the page break has been eliminated now you can always choose a background for your data as well you just uh if you want you can add a picture to your background I think that's not available for now okay you can work offline as well let us select this one so you can see that we have added a background to our data we can select all the text files and then we can change the color so that the text becomes a little visible yeah that's how you do it now if you don't want the image to be added on your file you can also remove that delete p background and there you go all the images are gone now the next important part that is the print titles now why do we need print titles for that let's try to print this so now you can see the first page in the first page we have the title rank restaurant sales segment category but if you go to the second page you don't have that title right you don't have the title rank name of the restaurant sales and their service category so if we want that then we need to add titles now for that let's select the print titles option now let's select the row to repeat on top now for that we need the first row select the first row and this particular row will be repeating into all the pages now let's try to print it so yeah now you can see the titles are being added to all the sheets you can see the page two page three page four page five and page six so you can see the titles added to all the sheets so that's how use the print titles option so now we are on the Excel sheet and you can see that in this particular Excel sheet we have some student details we have the serial numbers names role numbers class blood group and some subjects like math science computer statistics social GK and the final marks obtain person percentage and total marks right so let us imagine that uh a company has come for an interview so all these students are going to attend an interview and uh the minimum percentage to attend the interview will be like 75% so you can see that we have 75 here and all the students have got above 75 except for the one student which is Mike right so for Mike to attend the interview he needs 75 percentage or above so uh he can make some modifications in one of these subjects right so there is always an exam after the exam called as Improvement exam so if you can like to score some extra marks in the subjects which you have scored less so that you can make up to the percentage what you're expecting you can do that so here in this particular sheet uh Mike has six subjects right and in SE subjects you can see he has scored 76 91 45 71 94 and 62 so out of these he have scored Less in one subject compared to all the subjects one subject is really low that is computers so what if he can give an improvement exam and increase his percentage to 75 right so that can be done so we can do some root Force methods like we can change the marks like 55 and then check this particular one so it's getting 74 right so using the Brute Force method will be a little bit lengthy so you can do this because it is just a small uh table which has least number of data and you just have one single cell to modify so that can be done in small tables but what if you had some table with hundreds of rows or thousands of rows that will be timeconsuming right so for that we have some inbuilt functionality in Excel that is called goal seek in Excel so for now let's eliminate the marks here so we have eliminated the marks and the upgraded percentage is 65 now we need the target as 75 okay so let's uh write it as Mike's Target right we need mik so Mike's new Target as 75 [Music] okay now to make sure that we need a Mark here which can get mic to 75 percentage we will use the data operation so here on the tool bar which has file home insert Etc we have one other option called as data so when you click on data from tool bar you'll have this ribbon and in this ribbon we have various options so when you come into forecast group we have an option called what if analysis when you click on what if analysis you'll have a set of options out of which we have goal seek now for goal seek we need to select a table cell that is this particular table cell that is M9 so we need the value 75 here right so what is the cell that you need to change here for that you need to select this one and select this particular cell here right and now now you can select okay now you can see that Excel has automatically run all the permutations and combinations and has come up with a number so that the overall percentage of mic will be 75% right so the expected marks that Mike should be getting in his computer's Improvement exam should be equal to 56 or greater than 56 to get the final Target at 75 so that he can attend the company's interview so this this is how we use goal set or goal seek in Excel so here you can see columns and rows so especially the fourth row and the column C right so uh currently we have data on our uh spreadsheet and it is exceeding the number of rows currently visible on our screen right so when you scroll down you can see that the header or the headers of all the columns are getting vanished or scrolled up along with the data right and you might want to keep that so that you'll have reference to every single aspect of or every single cell data you're referring to right so for that reason you might want to freeze this and also you might have a doubt what if uh even even I had this data in the column section right and what if I had to freeze that and you know when when you scroll this and keep that data uh set on the column as it is right so we can also do that let's look at both of them so there is a simple process to do that all you have to do is select the cell or for which you want to uh you know set the or freeze the column adjacent to it or the row adjacent to it right so currently I want to let me expand it so currently I want to freeze this particular row right and this particular column so the cell I would be selecting is the first cell here that is D5 right so when I select this particular cell I can be able to freeze this particular row and this particular column so now you need to navigate to the toolbar and then select the view menu and inside view option you can see there is an option of freeze pains right so click on the drop down so you can see three different options as we discussed earlier we can either choose to freeze an entire row that is a second option or you can freeze the ENT entire column that is the third option or you can freeze both that is the first option right freezing rows and columns so let's select this one and see if our rows and columns are frozen or not right so now let's scroll down and you can see our row is successfully Frozen there right and similarly scroll towards right and you can see our column has been successfully Frozen right so this is how you freeze rows and columns or you can freeze pains in Excel now let's quickly get onto the Practical mode where we will have some data sets over which we will be trying to apply the charts now there are various types of charts in table and those are the pie chart the colum chart the bar chart column versus line pavier chart and Spar line charts so we will discuss one after the other first let us discuss about the pie chart so as you can see we have a small data set on my screen which I have personally created and uh this deals with the companies and this deals with the shares of the automobile industry so now for this let's create a pie chart select all the data and then get into the toolbar and inside the toolbar you can see various options and inside that we going to use the insert option and when you select the insert option you will have a ribbon here and inside the ribbon you can see groups tables illustrations addin and here you go these are the charts so now we needed a pie chart so select on that icon and you have various types of pie charts you have two dimensional pie charts three dimensional pie charts donut type of charts Etc right so I would like to go with the three-dimensional pie chart as it looks a little more appealing and easy to understand and now there you go this is the title of the chart and these are the legends of the chart and this is the chart area and the these are the values now we can uh you know kind of add the data labels as well so you can just have to press the plus icon over here and you'll have the options you can add the data labels which will give you a lot more interesting values and uh you can also turn this around as you can see we on double clicking any part of the pie chart you'll get this option which is called as format data point and uh let's imagine that you wanted to show the information related to this particular one which is Volvo which has the biggest share so this is in front right now now what if you wanted to show the information related to data which is over here right for that you can select this particular Arrow key and you can just twist it and there you go it is coming in front this will be uh a great way of presenting your data to your clients right yeah and there is another option where you can directly explode this like you can pick it out you can see this right Point explosion where you can split it out and you can show that especially like you can highlight it and show it so that's the you know a great way of presenting your data so that's how you can work with pie charts in Excel now let's quickly move to the next one that is the column chart and in the column chart we have the data related to some company and uh the profits of that company based on a year so you can see that the year has started from 2008 and it has gone until 2021 and these are the profits of that company year after year so since we are dealing with column chat I'll tell you the simplest way to create a column chat there is a shortcut key for that which is alt F1 and there you go you have the chart right on your screen now you can uh expand the chart just by dragging it like that and you can see that there is some problem with this chart that is we have numbers here instead of es right you can see that the First Column is the es which is 2008 to 2021 but here you can see we have 1 2 3 4 which is not proper right so to change the axis that is you can see the axis which is 1 2 3 4 from 1 2 3 4 to the years 2008 2009 2010 11 Etc you just have to make one change here and also you can see I have pressed the plus icon over here which will open the option where you can include the access titles if you have and data labels everything right so for now I think I'll keep it as it is so you can include the data table as well as it is here and you can include that in the chart there are various options that Excel provides you so now let's uh focus on changing the AIS from 1 2 3 4 to the ear for that you need to select the chart design and inside the chart design you have an option called select data press that and here you have various set of options you can see profits you can see ears now we'll select ears and we will remove that and now you can see the edit option here right select that and now it will ask you which column to be selected for the access range for that let's select this and now let's select the ears here and press okay and there you go again press okay so there you go you have the ears over here instead of the numbers which you had in the first place so that's how you create the column chart and we have the next type of chart which is really simple now you know it kind of change this chart directly into bar chart there is no big deal in that so uh for that you just need to select the change chart type option over here you're just changing the chart type and you can get the bar graph over here column and bar graphs are completely same to each other only that you can see these uh bars in the horizontal way rather than in the vertical way let's do that let's not create a new thing for that so uh yeah there you go when you select the change chart type option you have various set of chart type options on the menu bar over here so what we needed was the bar chart now let's select that and there you go let's select okay and you have the bar chart right and you if you want you can include the data labels which look a little bit more interesting you can also include the data table as well okay for now I think we have to expand the chart to get the Clear View yeah that's how you do it right now let's move to the next type of one yeah we dealt with the bar chart so we'll directly enter into the column versus line chart so we have similar kind of data we just had year and profits in the previous part now we also have included the expenses right we needed another column to showcase the line chart compared to the columns right so for that I've included a new column that is expenses Al as well now just let's select the data and let's select the option alt F1 to create our chart right now automatically uh Excel has provided us the column chart we have The Columns of expanses we have the column of profits and we also have ears now let's change the data as we did before select uh year and remove it and then edit s edit and select the year that is from here to 2021 select okay and we have the ears over here now what we needed to do is change the chart type right select the change chart type option and since we wanted to create a column versus line chart it is a combination whenever you want one or two types of charts included in one single chart window then it is a combo option so here we need to select the combo option and inside that we have various types of combination charts we have clustered column we have clustered line on secondary axis we have clustered stat areas clustered column and the other one is custom combination so let's go with the first one that is clustered line let's select that and there you go we have the line graph with the column so that's how we create the column versus line and if you want you can include the data label tables and you can include the data table as well yeah okay I think uh we need to expand the canvas a little more to get a clear view yeah so you have Legends over here you have the data labels and access everything and chart title you can always change the chart title so for now let's uh rename it as profits versus EXP es yeah that's that's how it goes done okay I think I made a spelling mistake here yeah it's clear now now the next type of chart we're going to deal with is the P chart so uh we have dealt with pie chart column chart bar chart and column versus line now P chart is a little bit different it's like you're going to create some active chart which responds to the variations you're making to your data so to understand that better let's create a practical chart over here now select all the data okay yeah I have selected the data now to create a pivot chart so let's select the insert option from the toolbar and inside the ribbon we have charts group and inside that we have PIV chart now let's select the PIV chart option and there you go now it's asking for range and we have selected the range over there and uh we are not going to create a new worksheet for the entire chart we're going to create the uh chart inside the existing worksheet itself so for that let's select the uh area where we want to select the uh chart to be present now for that let's select this and yeah select okay okay there is some error let's cancel this and try again let's select the data and prev chart again we have selected the range yeah that's how you select the range and now select the position where you want to create it existing worksheet we have selected that and press okay yeah that's that's the way we have got our PR chart over here and you can see our pevious chart is completely blank right now it will respond to the changes you make to your P chat for now we have uh serial numbers and U these are the names of the guys who have okay so these are the names of the guys who have some dealerships for various car makes such as Ford Ferrari Honda Kia Alpha Romeo BMW etc etc so uh we're going to change or we're going to see um you know we're going to make some charts of the persons who own the cars or the car brands or dealerships in various regions so we're going to work on that now let's drag in the name and uh let's drag region and cards and units yep so we have our uh chart over here column chart which has the different different names and the dealerships and some of cars over there right so you can see that we can also change some values over here what if we wanted to know only uh the details regarding Jennifer you can do that you can unselect everything and you can just select Jennifer and press okay now you'll have the details of only Jennifer of having the car brands so we have the regions as well so Jennifer owns dealerships in two different regions that is South and West and uh the sum of units and count of cars as well so that's how the PV charts are used in real time and now let's select sales and drag the sales as well yeah it's been drag and you have the sales of U Jenifer the details of her sales and let's move the chart a little bit and you have the plus icon where you can in load the data labels for a better information yeah so that's how you use the P charts so we are on the last type of charts which is the spark line chart Now to create a spark line chart we need to get into the insert option and inside we have the ribbon and inside the ribbon we have the group that is spark lines now to generate a spark line chart we need to select this particular column and then select the type of spark line you want to create for now let's create the column type of spark line chart yep we have to Club these cells together first we forgot that because we're going to insert the spark line chart right above here now for that let's March the cells [Music] yeah so uh we wanted to create the spark line chart of column type and now it's asking for the location where we want to create the spark line chart so this is the location now let's select okay and there you go we have the spark line chart right over here now you can see that we have a negative Point here so if you want you can change that from negative to positive and it will automatically update that and if you want to add the access to this particular chart you can select the option Spark and there you go you have certain options like High Point low point and if you wanted to add some margins you can also do that add the data type access and there you go you have the access show access yeah you have the access now and you can have the high point and low point for that as well which is the you know the biggest change and the smallest change in this particular segment that is few Furniture yeah Furniture had the highest sales in January and uh Home Appliance had the lowest sale and you can also change the color and uh let's type of change the color again yeah we have a different color over here which resembles a little bit more better or we can also go with this one and if you wanted to add the same to all these tables you can do that just by dragging it and you have it for all the 6 months now if you wanted to add some new data in this row or you wanted to add a new complete row then you can do that as well and change that and bring that change implemented in these charts as well now let us try to add a new row here which includes the miscellaneous we have that data right over here let's select that copy and uh let's paste it over here so there you go we have the extra row here so yeah now we have added the miscellaneous row and right now we have the spark line chart for only four columns or four rows that is Furniture technology home appliances and office needs and miscellaneous is missing now to add that as well let's select spark line and inside spark line just select edit Group location and data and now let's select the entire data and now select okay there's something wrong again spot line eded data select range this should be helping yeah there you go we have all the five rows now including furniture technology home appliances office needs and miscellaneous as we speak you might wonder what it takes to become an expert data analyst if you are an aspiring data analyst with at least one year of domain experience and looking for online training and certifications from prestigious universities and in collaboration with leading experts then search no more ours postgraduate program in data analysis from P University in collaboration with IBM should be your right choice for more details on this course head straight to our homepage and type in postgraduate program in data analysis or simply click on the link in the description box below with that in mind over to our training experts so conditional formatting is a method to visualize your worksheet we already have the charts in Excel to visualize our Excel sheets in graphical form but what if you had to see or visualize the data in your worksheet as it is so that is where the conditional formatting comes handy now let's get back to the Microsoft Excel and try to implement some conditional formatting now we are on the Excel worksheet this particular data set is based on a store now we have various columns in this particular data set we have R ID order ID customer name Etc and finally we have quantity discount and profits now let's go to profits uh let us imagine that we have provided this particular store with a target of minimum 15% profits now let us find out the stores that have hit the 15% Target and the ones which did not so for that let us select the entire column now you can find the conditional formatting option in the home tool bar and in that ribbon the ribbon with Styles has the conditional formatting option click on that and you have various options over here highlighting the cells top bottom rules datab bar data scales and icons so datab bar are actually the bar graphs which highight themselves on each and every cell if you implement them and these are the color scales if you want to implement some colors you can go through that and if you want to represent your CS with icons you can also do that for now let's highlight the celles so select that and inside this select the greater than option and here you can provide the percentage that is 15% and the cells or the stores which hit the target should be highlighted with green color and select click okay and there you go you have your stores that hit the 15% Target and the ones which did not hit the 15% Target now if we want to highlight the ones which did not hit the 15% Target you can highlight them with the color red so conditional formatting highlight the Cs less than 15% with color red and text dark and there you go it's done now now let us consider another example so this particular example is based on train data set and here we have the passenger ID survived passenger class name Etc now we have a train accident incident over here now one is indicated that they are alive and zero is indicated that they are not alive now let us use this column and conditional formatting highlight the cells containing the text as one with color green okay and highlight the cells that contain text zero as red that indicates they are not alive and let us select the icons now we have the icons over here the X indicates that they're not alive and the Green Tech Mark indicates that they're alive now that is how you use conditional format matting in Excel now here on my spreadsheet you can see some data so this particular data has some color markings so we have used color codes to Mark the designations of all the employees with different colors so for example blue for manager purple for senior and so on right now our duty is to count the total number of employees present in a company based on the color code this can be done using filters but let's try with color CS now we don't have a readily available function in Excel to count sales B some of their colors but we can make use of macro now to make use of macro you might want to enable the developer options so by default these will be disabled in Excel to enable them right click on the toolbar and you can see this particular option click on the customize ribbon option and you will be provided with various set of options now on the right side you can see developer options so make sure the checkbox is clicked to enable developer options and press on okay to completely enable developer window right now when you click on developer options you can see various options here visual Basics macro Etc now click on the macro to create a macro now just give a name to the macro function so I'll give simply long and press on create to create that particular macro function so we have already created a macro that is Count colored cells which we will be using in our current spreadsheet for counting the colored cells now let us erase the newly created function now getting back to the original code so how does this particular count colored cells works so let us understand the function first then we will try to make use of it in the spreadsheet so the function name is Count colored cells as you can see and this particular function will have two parameters current cell and spreadsheet area so the current cell will be the cell address where the color you want to count is present and then the spreadsheet area so what is the range of cells where you want to locate that particular color and count the number of reputations that is the spreadsheet area and then comes the variables used colored cell range color code and color cell count so the colored cell which particular cell is having that particular color and what color code is in involved in that colored cell and colored cell count so what is the total count what is the total number of colored cells you have in that particular range that is colored cell count now let us consider color code is equals to current cell interior color that is current cell do interior dot color so this is the function which will identify the interior color of a selected cell let us imagine that initially color code is equals to the current cell's color code okay now we will have the for Loop and that for Loop will include colored cell which is the current cell in the spreadsheet area and if the colored cell the currently selected colored cell do interior do color is equals to the color code that we have selected then colored cell count the variable which is used to count the number of colored cells is equals to + one initially by default it will be zero in case if this particular if condition is true that is the current cell color and the color code given is equals to same then the value of colored cell will increase by one and this particular if Loop will run as many number of times the condition is true so let us imagine that the condition will remain true for five times then we have obtained five similarly colored cells in the given range of cells in a spreadsheet and once the condition fails then the latest value present in colored cell count variable will be displayed which will be a final result so this is how the macro function works now let's close the macro window and get back to our spreadsheet now let us try to make use of the macro function that we just created so that is equals to count colored cells press tab to select the macro function and the first one is the current cell so this is the current cell where the current color is being provided comma the cell range so our cell range will be from C2 to c31 right so this is the range where we want to count the similarly colored cells that is sky blue colored cells close the function and press enter now you can see the function has identified six repetition of sky blue color in this particular range of cells now you can drag the same formula across all the cells and it will be providing the results of the same so six senior colors that is pink or purple color then two reputations of this particular color two reputations of trainy color five for dark blue and six for green right so this is how the macro function works and the key point to remember here is this particular macro will work only for manually colored cells so there are situations where we have used conditional formatting to color a single cell let us imagine that we have used conditional formatting to recognize the employees with salary above 30,000 as green color and if we try to use that particular green color to be counted by the color count cells function no it will not happen it will not consider that color as a feedback or an input now in future we will also try to design a macro which can recognize the colors made by conditional formatting as well but this particular function will be exclusively used for manually colored cells only so what is data validation in Excel so it is a feature by Microsoft Excel where it can restrict data entry into certain cells by using data validation and it will will prompt the users to enter valid data in the cells based on the rules and restrictions provided by the creator of that particular sheet now this would be a little confusing to understand so to understand it in a much better way let's get back to the practical way where we will start entering some data validation rules to our Excel sheet and try to enter data into it now we are on the Microsoft Excel and this particular sheet is based on employee ID employee name employee Department employee salary fiscal year and work timings of the employees now we will be applying some data validation rules to each and every column and try to enter data based on the rules we have entered now for the first one which is the employee ID we'll enter rules based on the employee ID number so to apply the data validation you need to go into the data tab on the toolbar and inside the ribbon you can see data tools group and in the data tools group you have the option of data validation now select the entire column go to the data tools group select data validation option and you will have this particular popup menu and here you can see different types of options so since we are entering employee ID it would be a whole number so let us select whole number option and now here you can see the option of between not equal to equal to not between greater than less than Etc so so let us select between and let us assume that your employee ID has five numbers and uh it should be between 10,000 to 11,000 so let us provide 10,000 and 11,000 now select okay and now the new rule has been applied so now the minimum value of each and every employee ID should be equal to or more than 10,000 and less than 11,000 and it should have five digits now let's try to enter a wrong employee ID that is just one there you go you can see that Microsoft Excel is not allowing you to include a wrong number it says this value does not match the data validation restrictions defined for this cell so now you can press retry and try to enter some number which is in between 10,000 to 11,000 now it is 10,901 and this will take the entry now let us assume that because I'm the creator of this sheet I know what should be entered here but what if I give this sheet to you and you're the person who's trying to enter the number and it shows the error and you don't know what was the error right to to avoid this kind of confusion what you can do is you can do some uh ways where you can provide the message to the user so that can be done by okay let's select the sheet again and go to the data validation and here you have the same settings now here you have another option saying input message so before input message let us go to error alert now here the error title would be data entered is not valid please enter this is the error message please enter em ID between 10,000 to 11,000 press okay now when you try to enter the wrong number it will show that particular error alert message see data entered is not valid please enter employee ID between 10,000 to 11,000 now this is fine but how long this will be you know what if there is an option where you just hover over to this cell and it automatically tells you to enter the value between 10,000 to 11,000 without having to face the error yeah even that can be done so you can select the entire column go to data validation and here this is the input message this is where you can do that so here you can write the message name valid data please enter data between 10,000 to 11,000 now when you hover over to any cell in this particular column it will automatically show you this message where you can avoid all the errors just select okay see when you select any cell in this particular column it will automatically tell you please enter data between 10,000 to 11,000 so here you don't have to face any errors right now let us try to enter the valid data one 9 02 and it will take it as the correct value and again 1 903 now in case if you try to provide the wrong number it will show the error see so that's how it is now we have finished the whole number part now let's get into the employe name where we have to provide uh the second type that is text length now let us imagine that uh in your company you're trying to provide ID cards to your employees and you know that ID card is really small and inside that ID card you need to include the employee photograph and your company name employee ID blood group phone number address everything and also you need to include the name of the employe so what if there is an employee with very lengthy name like 30 characters 40 characters yeah in India it is really possible that you might have a lengthy name so what you can do is you can provide the text length where the text is limited to like 15 or 20 uh um characters so you can include that name in the ID card so you can do that by allowing the data validation criteria with text length uh minimum can be anything so minimum can be one and maximum here you can provide 15 and press okay okay minimum will change it to at least uh two and yeah let it be one so minimum is one and maximum is 15 characters and press okay and uh let us also provide the input message enter character type character data please enter valid name and error alert invalid data please enter valid data okay please enter characters less than 15 press okay now let us try to enter a random name okay uh we'll enter characters more than 15 here quy Z uh yeah it's like 1 2 3 4 5 11 okay now this is more than 15 okay I think it's not more than 15 let's try to enter a little bit more characters yeah now we have entered more than 15 characters and it is showing please enter characters less than 15 so now we can retry and try to enter some valid name um now we have characters less than 50 so it will take as the proper name now let us try to enter another name I'm just entering some random data so another name maybe great now the next type of data validation is also done now let us get into employee Department employee department is something really superb so I'll tell you about it it's actually a list now let us finish the other ones first that is employee salary physical year and work timings after that we'll learn about uh the list one now let's come into employee salary so salary is something where you have to include decimal points so now let us go to uh data validation and inside the values we have already dealt with whole number uh text length yeah now we'll uh deal with employee salary that is decimal now minimum is okay what is uh minimum can be [Music] 1.0 and maximum can be 1 lakh or let us put that as 10 lakhs press okay okay not not just them enter valid salary okay now let's change it to minimum is 10,000 so the employee minimum salary will be 10,000 and input enter valid salary enter salary between 10,000 to 10 lakhs let us include a comma here to now error alert invalid salary please enter between 10,000 to 10 laks press okay now the message and data validation conditions are applied to this column now let us try to enter some invalid data first it will show the error now we need to enter the valid data yeah now it is taking the valid data let us provide 20,000 and this has 35,000 now the next one is fiscally let us imagine that we wanted to you know work on the employees for current working year that is 2020 to 2021 and or you can consider 2021 to 2022 not more than 2021 and not less than 2021 for that you can provide the year option as well so for that you need to select the entire column go to data validation settings and inside here you have an option of date so uh let us provide the date option as between and start date is 01/ 01/ 2021 and end date is 01/ 01/ 2022 and input message let us provide data as current fiscal year Current financial year enter date between 2021 to 2022 let us copy this and go into to error alert error message will be seen invalid dat select okay now the error message and uh the data input message and the data validation rule has been applied for this column so let's try to enter some date here you can enter uh 02 of February 2021 it will take as the correct data now let's try to enter some wrong data which is apart from 2022 02 02 20 22 it will not take this it will throw error so that's how it works now let us try to enter as 2021 and this is March it will take it now another data 04 this time and 2021 so that's how your uh date data validation will work now coming into the next type of data validation that is time so we have time here let us imagine that you are providing some work timings to your employees that should be from 9:00 a.m. to 5pm not more than that so you want to keep your work life balanced for all your employees so you just want them to work between 9 to 5 and that should be fine and anything apart from that time should be invalid so you can do that by selecting uh time option between it should be in between and the start time will be 09 0 0 and this should be uh 17 that is 5:00 p.m. in the evening and input message please enter time between 900 a.m. to okay 5 PM this should be the input message no error alert invalid time error message okay now okay work timings now this should be okay we can do this as two columns actually let us cancel let us copy this and paste it here you can do it as login login timing and this should be okay this should be a log out so this should be okay so this will be considered as login and log out times now let us provide the login time this should be the right time and uh let's try to provide wrong timings 08 now it will not take it it will throw as error so it's working fine let us provide 10 now let us provide the log out time it should be below 5:00 p.m. so let us provide five it will take it okay let's try to provide something just 1 minute less than five that is 59 okay there is some problem in this let's check it okay it is taking uh three that is including seconds okay let's provide seconds as well 05 0 0 0 0 okay still there is some problem [Music] okay we have entered four we should we are supposed to enter 24hour timing right 14 okay uh it should be 17 I think this will work yeah now we are following 24-hour timing here so so let's provide another timing that is 16 30 0 0 this will be valid and 1530 yes this is also valid now we have finished almost all types of uh data validation things that is we have finished with any value whole number decimal date timing test length and the last one is list so this is where things get interesting now let's cancel this now select the employee column now if you're running a company then definitely you'll not have only one Department in it you might have multiple departments right so let us consider that you have a software development company so in that the basic departments will be software development team and software testing team so what you can do is you can provide a list so using that list you can just uh over over to that cell select the drop down menu and inside that you can select the option so we will try to work something like that if we have selected the entire column selection okay yeah let us select the entire column again go to data validation the selection contains some celles without data validation settings do you want to extend data validation to the sales yes now let us provide the list option and here Source you can provide as developer and tester press okay okay now input message select one select one error aler invalid data select from dropdown only press okay now when you select the cell you can see small drop- down icon right so when you press it you will have developer and tester option so you can select developer for first one and for second one you can select uh tester right okay this is good for uh one or two options maybe three or even maybe like five but what if you have more departments okay let's go to this uh second sheet where we have the department data so here we have like 14 departments no 15 departments or 15 employee type names so the first one is CEO second is developer tester quality analyst system analyst Finance human resource so so many options are there so you cannot keep on typing all of them right so here it is 15 what if you have like 20 five like 50 departments right it will be tough so for this you have another option so let me select all this data and copy this from this sheet to our employee sheet let us paste it somewhere here let's expand this okay now we have our uh list over here now let us remove the data validation from this column clear all okay now everything is cleared let's select okay now let us erase this data as well now let us apply the list data validation again from scratch go to list now here we can select the source select this particular cell and drag it until here and it has been loaded now go to input message data options select one from the menu or the drop down copy error alert select only from the drop- down menu invalid entry yeah now let's select okay fine now you have the icon here you have all the provided options you can scroll down and select any one out of these you can select knowledge transfer for the first one and second one would be system analyist and the last one would be Human Resources now in the list everything is fine okay this is showing you the menu options and everything so but what if you provide this sheet to your new joiny or new employee in your company and by mistake he messes up something like deleting this okay so we have deleted systems analyst and when you click on the options here you don't have systems analyst in that place you have blank space right so there is a way where all your um you know data can be messed up so to avoid this what you can do is you can actually um save this data in a different sheet like I did here like Department data you can hide this sheet or you can protect this sheet with password or something so that nobody can mess this up so now let us get back to the employe data and select this column and uh clear everything press okay and let us also clear this and this as well this one too now let us eliminate the rowes or CS the employee yeah everything okay now we have basically removed this data from here now let us try to apply the data validation once again now here let us select as list and uh here let us provide the option let us go to next sheet let us select all these options and press okay okay let's provide input message data options or Department options that yeah now let's select okay now let's hide this sheet now the sheet has been hidden and you still have the options developer or let us provide CEO then we have developer then we have test what is Excel V lookup V look up stands for vertical lookup V lookup lets you search for specific information in your spreadsheet it allows you to search for a particular value in a column and returns another value from a different column but of the same Row V lookup takes advantage of vertically aligned tables to quickly find data associated with the value the user enters now let's look at the syntax to write a vlookup function so V lookup takes four parameters or arguments you can see it here there are four arguments in the vook of function so the first argument is called the look of value the look of value represents the value that you're searching for next is the table array the table array represents two or more Columns of information the third argument is called the column index number now this represents the column number in the table from which the value must be returned the First Column is 1 the second column is two and so on and then we we have the fourth parameter which is called the range lookup now this parameter is optional now range lookup is used to find an exact match so if you want to find an exact match you need to enter false or zero meanwhile if you want an approximate match you need to enter true or one if getting your learning started is half the battle what if you could do that for free visit scale up by our click on the link in the description to know more now let me give you a brief overview on what's going to be in our demo so for our demonstration we'll use a sales data set you can see the data set on the left using this data set we'll understand how we lookup performs an exact and approximate match we'll see how we lookup is case insensitive then we'll perform a wild card character search we'll understand how to handle errors when a match is not found and finally you will look at two-way lookups now here is a small example of how vooka works so we want to find the unit sold by Morgan using the data set on the left so here you can see I have the data set it has some rows and columns so these are the columns which are order date region there's a representative column item units there's unit cost and total and from this table I want to find the units that were sold by Morgan if you look at at the table at the bottom you can see the representative name Morgan and Morgan has sold 30 units now I want to find this value using the V lookup function so here on the right you can see I have my lookup value which is Morgan and this is my vooka formula and using this formula I'm getting the result as 30 now let's go to our Excel workbook and perform all the demos one by one so this is my Excel workbook that we are going to use for our demo and and here is the data set we'll be using now this is the same data set that you saw a while ago so this data set has seven columns you have the order date the region Representatives name item there units unit cost and total which is basically the total or the amount of sales that was generated we are going to use a chunk of this data set and perform our V lookup analysis so let's see our first example and the problem we have at hand let me go to the exact match tab all right so here I'm going to use this chunk of data which is actually taken from the original data set so we have taken the first 11 rows from the data set you can see from row two till row 12 is going to be our data set that we'll use so we have a question here we want to find the it item sold by Thompson so here Thompson is my lookup value which means looking at Thompson I want to find the item that is sold so if you consider our table here you can see we have the representatives name Thompson right here and from the table you can see Thompson sold pencil and I want to get this value which is pencil using my V lookup function so let's go ahead and write our vlookup function I'll consider this as my lookup value and here under item I'm going to write my V lookup formula to write the V lookup formula I'll write first equal to and I'll write vlookup if I hit tab it will autoc complete all right now let's give the parameters so first is going to be the lookup value which is nothing but Thompson so I'll just select this cell which is j17 comma now it's asking to give the table array so the table array is the table from which you want to retrieve a value one thing to keep in mind is that the value you are looking for should be the first column of a table and you can select the table array till the end of the data set or till the column which has the value you want to find so here I'll select my table array starting from the representatives column till the end of the item column which is going to be my table array then I'll select the column index number so the column index number is based on the table array you have selected in this case my column index number is going to be two because we have selected just two columns and the value which I'm going to find is in the second column so I'll give two comma Now the range lookup I'm going to give is false because I want to find an exact match so either you can hit tab or you can write zero which means false if I close the bracket and hit enter it will return me the item that Thompson had sold which is pencil you can verify from the table that Thompson had sold pencil all right now let's try to change the table array and see what result are we getting so this time I'll slightly change the table array so again we are trying to find the item that Thompson had sold so I'll write my formula equal to V lookup I'll hit tab to autocomplete I'll give my lookup value as Thompson comma this time I'm choosing my table array starting from the representatives column till the end of the data set which is till p12 again my column index number is going to be two because if you consider this table array the item column is the second column in the selected table array so I'm going to write it as two comma the range lookup I want to give as false because I want to find the exact match if I close this and hit enter you can see it has returned me the same result that we saw earlier now suppose let's say this time I want to start my table array from the region column which is this column so I want to select my table array starting from the region column now this is going to show me an error because your table array should always start from the lookup value column so if you consider Thompson as the lookup value my table array should always start from the representatives column so let's just try this example so I'll write V lookup my lookup value I'm going to give it as Thompson comma the table array as I mentioned earlier I'm going to choose from the region column and let's say I'll select till the item column give another comma and this time my column index number is going to be three because since the table array has three columns and my item column is the last column so I'm going to give my column index number is three comma I want to get the exact match so I'll give zero if I close the bracket and hit enter you can see it has given me an error okay now let me just show you another method to use vlookup in Excel so here on the top you can see there's a formulas Tab and under formulas tab under function Library you see we have an option to select look up and reference if I click on this and if I scroll down below you can see there's an option called V lookup let's choose this all right now here you can give your lookup value your table array column index number and the range lookup under this function arguments dialogue box so here I want to select my lookup value as Thompson my table array is going to be from the representatives column till let's say the end of the data set my column index number is going to be two I'll write two and range lookup I'll write as false because I want the exact match if I hit okay you can see it has given us the exact SE result so this is one of the other ways to use V lookup in Excel now let's move to the next sheet which is regarding approximate match okay so sometimes you might have cases where you don't have the look of value present in the table in those cases you can tell V lookup to return an approximate match now one thing to keep in mind is that your lookup value column should always be sorted in ascending order while trying to get an approximate match so here we are going to use this table and my lookup value column which is going to be the representatives column is already sorted in ascending order and let's see the question we have here so I want to find the item sold and the unit cost for mat so my lookup value is going to be Matt now if you see this table I don't have the representatives name mat anywhere in the table now using an approximate match I want to find the item and the unit cost so let's do it so here under item I'll write my vlookup formula so I'll type vlookup I'll hit tab to complete it and this time my look of value is going to be matte so I'll select this cell comma my table array I'll select from the representatives column till the end of item column because I want to find the item value for Matt I'll give a comma my column index number as per the table array is going to be two comma my range lookup this time I'm going to give us true or one so I'll write one close the bracket hit enter all right it has return returned me binder was the item sold by Matt now let's try to figure out why we lookup returned binder as the item now let's say you were asked to insert the name Matt to the representative column in alphabetical order so where would you place it ideally you should Place between KL and Morgan since this column is already sorted Matt should appear before Morgan and after Kel so in an approximate match we lookup will consider the previous highest value in this case the previous highest value before Matt is KL and hence we look up returned the corresponding item purchased by Kel which is binder now similarly if you wanted to find the unit cost for Matt again V lookup will return the unit cost of KL which is 125 let's just try it out so I'll write bookup my lookup value is Matt comma my table array for this time is going to be from Representatives column till the unit cost column I'll consider I'll give a comma Now the column index number as per the table array is going to be four I'll give another comma and this time I'll double click true because I want an approximate match if I close the bracket hit enter you can see it has returned me 125 which is actually kl's unit cost okay we have another question and this time I want to find the units sold by Rob now Rob is going to be my look of value if you see the representatives column we don't have Rob now let's see what V lookup is going to return as the item for Rob so I'll write V lookup I'll give my lookup value as Rob comma my table array is going to start from the representatives column I'll select till here I'll give a comma the column index number is two I'll give another comma I'll double click on true I close the bracket hit enter you can see V lookup has written me pen now let's understand by V lookup returned pen for Rob now if you are asked to insert the representatives name Rob into this column so ideally Rob would appear somewhere between Morgan and sorino because since this is sorted in ascending order Rob is actually greater than Morgan and is less than sorino so as you know we look up in an approximate match Returns the previous highest value which is Morgan here and the corresponding item for Morgan is pen and hence we look up returned pen for Rob all right now moving to the next sheet which is another property of V lookup which tells V lookup always looks to the right this means that your lookup value must always be on the left and the value you are going to find out should always be to the right of the table now consider this scenario where your lookup value is on the left in this case it is the representatives value and I have taken svino for an example and we want to find the order date for svino now if you consider this table your lookup value which is svino is actually this column the representatives column and we are going to find out the ordered date so your representatives name is on the right of the table and the value we are going to find out is on the left of the table now as per the property we look up only looks to the right so if I use the vlookup function for this example here I'll write vlookup I'll give my lookup value as sorino comma my table array I'll consider from the order date because I want the order date till the representatives column I'll give a comma and this time my column index number is actually the first column if I consider my table array as the first three columns selected and my range lookup is going to be false if I close the bracket hit enter it returns me an error the reason is V lookup always looks to the right and this time the value we were trying to find out was to the left of the table now if you consider the opposite case suppose you have the order date and you want to find the representatives name you can do that easily because your lookup value which is the order date is on the left and your representatives column is on the right so let's just try this I'll write V lookup my lookup value is going to be sorvino's order date so I'll select it from the table I'll give a comma my table array will start from the G column let's say I'll select the entire table comma and my column index number will be three if you consider these as my table array comma my range look up is zero if I close this hit enter you can see we have got svino corresponding to the order date all right now let's move to the next tab now this tab says we look up returns only the first match so we have a question at hand the question is find the units sold by Jardine if you see this table we have the representatives name Jardine twice you see here and if you look down we have Jardin here as well but the item sold by jardan at the first instance is pencil and at the second instance it's desk now as per the property V lookup always returns the first match now ideally when we write the V lookup function and look for the units sold by Jardine you will see V lookup will return 36 instead of three let's just write out I'll write the V lookup function my lookup value is Jardine comma my table array I'll select from the representatives column till the end of units column I'll give a comma my column index number based on the table array is three I'll give another comma Now I want the exact match so I'll select zero close the bracket and hit enter now as you can see it returns me the first match of Jardine which is 36 so we look up did not consider the second match now similarly if you consider this table we have Jun repeated Thrice so you have Jones here you have Jones here for the second time and also we have Jones for the third time and let's say you want to find the unit cost for Jones so if I write the vooka function I'll consider my look of value as Jones comma my table array I'll select from the representatives column till the end of unit cost column I'll give a Comm comma based on the table array my unit cost column is at the fourth index so I'll write four comma the range lookup is zero if I click on this and hit enter you can see it here V lookup has returned the first instance of Jones and it did not consider the second and the third instance so our unit cost for Jones is 1.99 because we lookup only Returns the first match okay moving ahead to the next example we have here is V lookup is case insensitive if you consider this table I've added another row in green so if you see here I have a representative name called Gil which is in proper keys or popularly known as sentence Keys here I have added Gil's name in capital letters are all in uppercase so I want to find the total revenue gener ated by Gil the total revenue is basically the total column let's do it I'll write V look up my look of value is Gil which is actually in upper case I'll consider my table array from the representatives column till the end of total column or the sales column based on this selection my column index number is five comma the range lookup is false so I'll just double click on false I close the bracket ET hit enter you can see it here our vook of function has returned the total as 5 39.73 which is actually the value for this row so clearly V look up did not consider Gil which was present at the bottom of the table in upper case so this shows that V lookup is clearly case insensitive all right now moving to the next example the VCA function supports wild cards which makes it possible to perform a partial match on a lookup value for instance you can use vlookup to retrieve values from a table after typing only part of a lookup value to use wild cards with v lookup you must specify the exact match by giving false or zero for the last argument that is range lookup now we have a question here I want to know how much sales did the representative whose name starts with Ki make now here sales represents the total column actually so I want to find out the person whose name starts with Ki so if you consider the representative column I have one representative whose name starts with Ki that is KL so using Ki as my lookup value I want to find the total so let me show you how to use wildcard characters and find the value using we lookup so I'll write my function V lookup okay now in lookup value I'll give Ki after that I'm going to write Amber scent followed by double inverted commas then I'm going to write a star and close the inverted comma this means the value starts with Ki Amber sent actually represents concatenation and star represents anything followed by Ki so this is going to be my lookup value in total I'll give a comma next I'll select my table array starting from the representatives column till the end of total column because I want to find the Sals I'll give another comma my column index number based on the table array we have selected is five comma the range lookup is zero because I want to find an exact match I close the bracket hit enter you can see it here V lookup has returned me 99.5 which is actually qals total now there's another way to do it instead of selecting the cell in which we have the lookup value I can directly write it in the v lookup function so I'll write the V lookup function something like this so my lookup value I'll pass Ki within double inverted commas because Ki is a character value So within double inverted commas I'm writing Ki followed by Amber sent which is for concatenation followed by a star within double inverted commas so this is going to be my lookup values this in total means anything that appears after Ki will be considered as the lookup value again the table array I'll select as these rows and columns comma my column index number would be five comma the range lookup is zero if I close the bracket hit enter you see we have the same value so this is another method or another way of using vlookups okay now moving ahead let's explore another feature of vlookup so if the vlookup function cannot find a match it returns a hash na error now if you want you can use the if na function to replace the hasna error with a friendly message so suppose you want to find the region in which an order was placed on 10th of May 2018 now if the order date is not found I want to return a message that is invalid date so if you look at the order date column we don't have any value for 10th of May 2015 you can see these values we don't have any value corresponding to this date so if my vup function cannot find this lookup value I'll return invalid date so let me show you how to write this I'll type my vup function I'll give my order date as 10th of May 2015 as my lookup value and then I'll consider my table array starting from the order date column till the end of region column I'll give another comma column index number would be two this time based on the table array we have selected comma I want an exact match so I'll give zero close the bracket and hit enter there you go it has returned me an error now I want to convert this error to a friendly message saying invalid date so let me show you how to do it I'll do it here so to return a valid message I'll use the if any function and within if any function I'll give my lookup function so let me type if na I'll hit tab now for the value parameter I'll use my V lookup function so my lookup value is going to be the order date comma my table array I'll select from the order date column till the end of region column comma my column index number is going to be two comma my range lookup is zero I'll close the bracket give another comma now if this expression results in an error I want to display a message that is invalid date so I'll put invalid date within double codes since it is a text message I close the bracket hit enter you can see the result which is invalid date now moving on to the final section of the demo we have two-way lookup now in the vooka function the column index parameter is normally hardcoded as a static number however you can also create a dynamic column index by using the match function to locate the right column and this technique allows you to create a dynamic two-way lookup matching on both rows and columns now let me first show you how a match function works so suppose I want to find the index where unit cost column is present so here I'll use the match function I'll give my lookup value as the unit cost column name comma suppose I'll choose my lookup array starting from Representatives column till the unit cost column comma I'll give my match type a zero for exact match if I close the bracket hit enter it returns me four because my array was selected from the representatives column till the unit cost column and in this array unit cost is at the fourth index all right now using this idea let's solve the following problem I want to find the cost of each item sold by Morgan so let's write our vook up formula write V lookup my lookup value is J5 which has the representatives name as Morgan comma my table array I'll select from C2 till F12 I'll give a comma and this time I know that my column index number is at 4 but I'll be using the match function to find the same so I'll write match my lookup value is unit cost comma my lookup array is from C1 till fub1 comma my match type is zero I close this bracket give another comma and I'll give my range lookup as zero close the bracket hit enter you can see for Morgan the unit cost is 19.99 you can verify from the table for Morgan the unit cost is 19.99 all right now we have another question here I want to find how much sales did and Andrews make in the central region so here I have two lookup values one is Central one is Andrews now looking at these two values I want to find the total to solve such problems let me first create another table I'll place the table somewhere here okay so let me just paste the table here all right now I'll create another column where I'll merge the values of region and representatives so here let me create another column okay I'll just name this column as region rep all right now this column I'll use the concatenation operator so I'll Select East use Amber Cent and then select Jones I'll hit enter now the same thing I'll just duplicate it for the other rows as well so I have my new column created Now using this table I can easily solve the problem now to find the total I'll write my vlookup formula so my lookup value will be a combination of j11 Amber sent k11 comma while table array will start from the region rep which has the values of region and rep combined till the last column and from this table array my column index number is five and my range lookup is zero if I close the bracket hit enter you can see for central region which is this one and for Andrew's representative the total is 149. 25 all right now now we are done with our demo let's have a quick glance at what all we did in this demo okay so first of all we saw our data set which was a sales data set that we used then we started with our first example to see how vooka performs an exact match we also saw how using the formulas Tab and lookup and reference you can write we look up in another way so you can use the function arguments to give your vook up parameters next we saw how vuka performs an approximate match so we had Matt and raw which were not present in the representatives column after that we saw an important property of V lookup that is V lookup only looks to the right followed by V lookup only Returns the first match following which we saw that V lookup is case insensitive so here we had added a new row where Gil was present in upper case but still we look up found the total revenue for Gill which was in sentence case or proper case next we saw how we can use Wild Card characters to use V lookups and here we saw how to use an if and a function along with v lookup and finally we saw how to use two V lookups choose from over 300 in demand skills and get access to 1,000 plus hours of video content for free visit scale up by our click on the link in the description to know more now we are on the sheet of Microsoft Excel and here you can see some data on my sheet that is employee name employee ID and salary so here we are going to perform our horizontal lookup but before that let me show you that this particular drop- down menu so this particular drop- down menu has all the names of the employees in this particular table and we have created this using data validation in Excel if you want to create the drop- down menus just like these then feel free to check out our data validation in Excel tutorial link to which is in the description box below now let's try to implement H lookup in Excel so here we have employee name but we want to find out the employee ID so for that let's create the H lookup now for that equals to hookup now the first one is the lookup value so this one is the cell which is the employee name now after that the table add array we have to select all the table arrays here now the row index number so the employee ID is in row index number two according to the table so for that provide two and close the bracket and before closing brackets you might want to give the exact match so for exact match select false and close the bracket press enter and there you go and if you select Jack then you'll find the employee ID of check and if you select Jerry you'll find the employee ID of Jerry reflecting here now similarly let's find out the salary of all the employees using hookup so equals hookup and lookup value that happens to be the name of the employee now the table array and next we have uh row index number that is salary happens to be the third row in the table so three and exact match close the bracket and enter so now if you change the values in this particular drop down menu you'll find the employee ID and their particular salary so Jack happens to be carrying the employee ID as 10001 and his salary is 10,000 so similarly let's try to find out Peter's employee ID and salary so there you go 105 and 50,000 as salary so that's how you use H lookup in Excel here you can see the first chart so this particular particular data is converted into a table format to avoid confusion while you're fixing the data so here you can see the name of the employee employee ID Department salary and date of joining and here you can see the salary column and the bonus percentage of each and every employee based on their salaries now coming to the new data table and here on the table two which is an updated table here you can find we're going to find out the department of the employee and the current Department of employee and the bonus person percentage based on his or her salary so why are we finding current Department again so here you can see that we have an intern Emily who has joined in the year 2020 but recently she got promoted as a developer so her department has been changed from Department inter to Department developer in 2021 so that's what we're going to find out here in the current Department using xlup now let's start to implement X lookup so for that equals to X lookup and for lookup you can see we need to find out the lookup value lookup array return return array and all the other two are not important right now but you can use them to get some exact matches in the future we'll also use that in the future for now let's try to use the first three parameters that is lookup value lookup array and return array so the lookup value is the name of the employee Mary and lookup array is right here which is this one now we are going to select the lookup array then comma and uh we're going to look up the department so this is the department lookup array and that's all we need close the bracket and press enter okay I think we missed out the lookup value so the employee or the lookup value which we required was missed so I've added it here so that is the lookup value the first one which is Mary and there you go press enter and you have the department here now let's add it to all the rows and here you have all the rows and all the data related to all the employees now we will find out the current Department as you can see here you have Emily as intern which is the old data but what if you needed the latest data that is the developer in the year 2021 right for that you might want to to the X lookup formula a little bit so let's try that X look up look up value which which is the same and comma Now where you have to look for the array that is look up array this one and the return array which happens to be this one and next you can find the exact match for this one so that's all right exact match and here comes the important part where you have to search L to First first by default X lookup searches for first to last that is like this in the starting place to the last place so that's the reason why we had Emily as intern so now if we give last to first then it retrieves the latest Theta where Emily will be shown as developer now select that and now you can close the brackets and press enter so let's add that to all the rows there you go now you can see Emily Clark Peter and James all they have got there updated job profiles as you can see in the original data Emily is a developer now so is Clark who was originally a tester and he got promoted to development team in 2021 similarly Peter became a senior Finance executive in 2021 and James the manager of sales now let's try to find out the bonus for all these employees based on their salary so this one is quite simple so X lookup now the lookup value is their existing salary comma and the bonus part so the lookup array is the salary range then here is another important point so here you might want to find the exact match but finding the exact match will not help you here you might want to declare another type of value where you can get exact match or next smaller item so you'll understand it why I'm selecting that so now let's close the bracket and enter so so now we we have the bonus values now let's add the bonus value to all the factors all the rows I mean so you can see we have different variety of salaries that is 38,000 22,000 39 Etc but here in the ranges we have provided 10 20 30 40 and 50 so what if the value is between 30 and 20 that's when you want the less number right that's when you want the number which is equalent to 30 or less than 30 which happens to be 15% so that's how it has worked here that is 0.15% bonus to the actual salary now we are on our Excel sheet here you can see the employee names and employee Ides of different employees from different zones let us imagine that the blue one is from East Green one is from West and the black one is from or the purple one is from South and the orange one is from North so this data is a little small but what if you had a data with hundreds or thousands of rows including all the employee names employee IDs or another relevant data type like sales of an employee and his employee ID and you were supposed to combine all the data from all the four different zones into one single sheet it would consume a lot of time right so recently Microsoft has released some latest updates based on arrays and datas so one of those is vstack function in Excel so how how to use it since it is a latest function I recommend you to go to the file menu and in the file menu you can navigate to account and in the account you can see the option of update options then you can select update now and your Microsoft Office will be updated to the latest version and you'll get access to the latest functions in Excel and all the other office related things like B on presentation Etc now we going to implement the vack function in Excel it is completely simple all you need to do is equals to stack and then select the data remember to select the data from column A and cell two or the row two we are not selecting the employ and employe ID we are just selecting the data now separate them by comma then again this one here this set and again this one and again lastly this particular law set now close the bracket and press enter and there you go you have all the employee names and their respective employ IDs all together in one data set so this is how you use wack function in Excel as we speak you might wonder what it takes to become an expert data analyst if you are an aspiring data analyst with at least one year of domain experience and looking for online training and certifications from prestigious universities and in collaboration with leading experts then search no more ours postgraduate program in data analysis from Pur University in collaboration with IBM should be your right choice for more details on this course head straight to our homepage and type in postgraduate program and data analysis or simply click on the link in the description box below with that in mind over to our training experts some PDF data into Excel now you can see the PDF document which I'm looking to convert from PDF to excel so this particular PDF document has a table so this is the statewise GST collections that happened during March of 2020 now we need to convert this Tabet data from PDF format to excel format now we are on the Microsoft Excel now to initiate the process we might want to choose a blank workbook for this so now we are at the blank workbook and the complete sheet is empty now let's go into the data option in the toolbar and in the data option of the toolbar we have the ribbon of get and transform data in this ribbon you can see get data option select that and you can see various options here from file from database from ezure from other sources Etc now we need the first option that is from file and in that we have a drop down and in that dropdown you might want to select the PDF if you have a Json file you can also choose fromjson and if you have the data from XML you can choose that and even you can extract the data from text or CSV as well now we need the PDF option so select the PDF Now navigate where your file is existing so my file is on the desktop and now let me select the PDF document select import now Excel will automatically analyze the tab data in the PDF format and give you the results now according to excel there are multiple tabular of formats table one table two so basically this table is one in the same so all the 29 states are fixed in one table itself but since the PDF is divided in sheets this particular document is considering the first table as a separate table and the table which is present in the third page as a separate table so we have table one table two and there are few more tables which Excel is assuming that it might be table but it's not and another table which is page number 002 it is a table and another one is right here now there is an option of selecting all the tables all together at once or you can select only one table which you want to select so if you select multiple items Excel will automatically give you an option of choosing your tables you can just tick and select the tables you want or you can directly select the table you want on your Excel sheet so right now let's try to select multiple tables so I'll be selecting 1 2 4 and 5 and this particular one is not a table so I'm eliminating that now you might want to choose the load option so Excel is now loading the [Music] data now all the data has been successfully loaded now yeah it is giving us a notification that all the data has been successfully loaded and now just right click on it and here choose the option load two and here select table option and if you want the data to be U you know loaded to a new worksheet you can choose that and select okay now you can see all the data is been successfully loaded in the form of tabular format in the new Excel sheet similarly let's try to load another page in the table format new worksheet select okay and the third sheet now get ready to impress your Bo by converting all the PDF data into Excel sheet just in a matter of few seconds with few steps and there you go all the four tables have been loaded successfully in the form of excel in just a few steps now this is how you convert PDF to excel now for this particular tutorial we will be using the student data set now let's get back to the Practical mode and start our Microsoft Excel now we are in the Microsoft Excel and as you can see on my sheet okay let me expand this so as you can see on my screen we have the data set belonging to students so we have 10 students and every one of them has name role number class blood group and marks Etc and at the end we have the percentage and as you can see we have the percentage in terms of float values and we have a lot of decimal values like eight and above so what if you wanted to just have one or two digits after the actual percentage like 80.6 or 80.6 6 right it would be a little bit more or good to read right so that's what round off actually means so now let's work on having rounded off values for the percentage now let's create a new column let's name it as um round of percentage great so to actually perform the roundoff uh formula we do have a predefined method for that for that you just have to type in equals to round and there you go we have round up and round down so both of them perform the same operation let's select a round up and this is the m column and the row number is three so we'll have to press M3 and the number of values after the decimal point for now let's select um one and there you go press enter and the value should be rounded off now 80. 66 is being rounded off to 80.7 now when you Dr drag the cell to the all the ruls then you can see all the values will be rounded off here you can see 75.1 16 has been rounded off to 75.2 and 82.3 3 is been rounded off to 82.4 so we end up with having grounded off values so for example uh let us imagine that you wanted to create one single cell for all the employees in your company which does the first name name second name or last name including the email IDs at one place right if you want to do so like let us let us let me show you how it looks like so let me copy this here then that's the first name now it's your second name along with the email ID now let us imagine that you wanted to store all the employees in the same way right so you can do this by using combine operation in Excel now all it takes is just a simple formula that is equals to and the first cell address that is A2 and you can use an enter sign now you can use the next cell reference that is B2 and then colon and the last cell address that is E2 now press enter so when you press enter or tab you will get this particular answer but this data is completely uh in a state where you can't understand it right the first name last name and the email ID has got combined all together so you want to use some spaces right so all you need to do is just make a little modification to this particular existing data so you can press another ENT and include spaces and then you can separate the email ID and the name with some colon so now we have separated the first two cells that is the first name and the last name with the space using the amples and then again we have used an ENT to include the colon and see that we have also included a space between the colon marks that it gets separated cleanly and now let's try to press Tab and see the result so there you go the first name and the second name is separated by space and the email ID and the complete name is separated by a colon now you can use this to all the sales and there you go all the first name last name and the email IDs have been printed here so that's how you use the combine operation in Excel let's get started with our onepoint agenda that is stated F function in Excel now we might have to switch to the Practical mode for that now we have started our Microsoft Excel and here we have our empty worksheet so dated IF function is majorly used to find the differences between two individual dates so it is also called as dated if by a few people and a few people also reference it to date diff that is date difference so anything is good now to find the difference between any two dates let's select a few dates so the first date would be uh let us imagine that you're an employee and you wanted to find out how many years that you've been working with an organ organization so for that you might want to need today and the date where you join the company right so it might be date of joining and today so what's the difference right so let us imagine that you join the organization somewhere around 2010 0 1 05 2010 and today so for today can you can give any particular date let us imagine that uh today is first of January 2021 or 2022 now we have our both the dates that is the joining date and the current date which can be the current date so the day can be anything so you might be watching this on a different date as well so the date of that day for today would be a little different so we'll imagine the today's date as an imaginary date now we might want to find the difference that is in terms of ears so you wanted to find the difference in terms of ears so here you can find the difference in terms of years days and months as well so currently let's try to find out the difference in terms of years so for years you might want to specify the third parameter in the data function as why if you wanted months you have to give the M as the third parameter and if you want to find the D you might want to give d as the third parameter uh so we have discussed the third parameter so what are the first two parameters right so the first two parameters are the first date and the last date so the first date in this situation is your date of joining and the last date or the final date which you want here as your today so let's find the difference so for that equals to so it can be dated if or date diff so date if and the first date which is the A2 and comma the last date that is B2 and your parameter third parameter in terms of ears that is Y close the bracket and press enter so there you go you have your date here so it's been like 11 years that you're working with your organization so that's how you find out the differences so we have 11 years because uh we did not finish the 12th year completely so if we had given the month as U maybe six where we have finished our total years then it will automatically change to 12 years so you can see on my screen we are on the Excel spreadsheet and on my spreadsheet you can see dates in column A and A different formats in column C D E and F respectively Now by default the data type or the data present in the a column will be considered as general and there are a few situations where even if you try to format the date in your format like you know the customized format you may not be able to do it because it's still under the general data type so for that for being on the safer side what we will do is select the data and navigate to the data toolbar and in that you have the text to columns option press that and remember to the delimited icon you know you might be having the dash as your icon or slash as your icon for the delimited thing and select that and next next and here you can see right it's in general mode so you can press on date and DMI will be your date format that is date month and year now click on finish so it's been formatted now now let's copy the same data and paste it in all the four formats so that we'll see the different formats that we can change to or customized to right so there you go let's expand the columns a little bit now we have day month and year so now you might want to change a couple of things right for example you wanted to change okay let's see the menu so the way is to select the CES and right click and select the format sales option and in here you can see the date function and in here you can see different types of modifications that you can do to your data right right so you might want to also change your dates based on the location so right now we are in India and imagine if you wanted to you know change something based on us or if you if you're having your client in us and he wants the dates in US format you can also change that so here you can see English United States and press on that press okay and you can see it's been changed to us format where you have years in the first place months in the second place and dates in the last place right and now let us imagine that uh your client does not want the dates all he needs is just months right you can also do that get back to the format sales option go to custom and here instead of uh you know uh month date and year all you can do is just write down month and year and select okay so you'll have only month and year not the dates now your client is being a little more you know he wants a little more detail and he wants you to add a lot of granularity to your data which includes day time Etc everything right so you can also do that so the same way go to format sales custom and here I think it's in date itself so let's navigate a little yeah here so you have the day date month and year press okay so there you go right so that's how you can do it and now let's try a little bit more if you have a few more options pending so go to format sales and custom and you also have timings so here you have date month year and timings press okay and there you have all the timings as well so since these dates are you know I created them just a few moments ago to keep it simple so I did not have to add the timings here so if you are Keen about adding the timings you can also do that for example if you wanted to add the timings of the employees loged in and log that you can also do that using the same format we have four different sheets of the sales so the first sheet is the West Zone next is South Zone East Zone and central zone so all this data belongs to one single sheet that is Superstore data set so I have categorized or I have taken a part of the data which belongs to Central region South Region west region and east region now we are going to use all these four sheets and create one single sheet which is the pivot table of all the four sheets now without further Ado let's start the process now for that we might want to use the shortcut key that is alt D now press P now this starts the pivot chart wizard now here we have to choose the option multiple consolidation ranges then click on next and here you can see the pivot table is clicked by default now we'll go to next now here click I will create page fields or the pivot chart wizard will automatically create if you want that to create for you now click on next now here let's select the range now I'll be selecting the range add it now next we'll select e right Follow by that we have South and finally we have West now we will name these according to the regions so the first one will be West next one will be South next one will be East and the last one will be Central click on next and here select new worksheet and finish and there you go you have the pivot table here now you might want to change the submission value so click anywhere on the pivot table and change it from count to sum so that you have the summation of the total number of sales subcategory sales and grand total of sales Etc now here you can see all option now let's select Central and you'll get the details of the central region the grand total and and the sales happening quantity of sales Etc now here you can see the furniture office supplies technology and the grand total all in one single sheet now let's get back to the original sheet so as you can see this is the original sheet and in the original sheet you can see various more categories which is the which is the shipment mode customer customer segment country City State uh and you also have uh product names subcategory Etc so if you had added all these into this sheet you can also create a separate option of adding Min new details to your pivot table so that is how the pivot table is created using multiple sheets so slices in Excel are software filters used along with Excel tables or Excel pivot tables over a large amount of data not just filtering out data but slices also help you with an easy understanding of the information being extracted and displayed on the screen now Microsoft Excel slices are compatible with Windows and Mac and Tosh operating systems now let us understand how to implement slicers in Excel so for that we might want to get back to the Practical mode that is starting our Microsoft Excel now we are on our Excel sheet and as you can see on my screen there is some data available on the Excel spreadsheet and this table is not just any table this table from Microsoft Excel is been converted into an actual table as you know by default Excel considers all the data which has been inserted into the spreadsheet as a database and to implement this license in Excel we might want to create or convert the format of database into a normal table for that you can select all your data and just press contrl T and that will allow you an option called convert the data into table now since this table is already converted as a table we can directly start implementing or inserting slices into the spreadsheet now for that select all your data then going to the insert option and in the insert ribbon you can see the filters group and in the filters group select the option called slicer now you will be provided with multiple options since we have the columns employee ID employee name Zone designation Department salary employees date of birth and employees date of joining so we can choose any of the options available according to our columns and create the slices now let me select the first one that is Zone next designation department and let's also select employee name now press okay and there you go we have our slices now let us try to rearrange these slices all you can do is just select and drag them up and done now let us imagine that we want to see or take a look at the employees who are working in the Easter Zone then we have all the information displayed on the screen who are working in the East Z they are Jack Tony bner Fred Etc and their Department designation etc etc all on one single screen so this really helps you while you're presenting your data in a presentation now for a change let's try to select the department now if your client wants to select the data from only analytics Department then you might want to select on the analytics key on the department slicer and there you go you have an employee called luk hops who works in the East Zone and his department is analytics and his designation is contract based and the salary he is getting is 65,000 now similarly uh you can see that we have only selected analytics and your client also wants data from itop now you can see that analytics is gone but what if you want both analytics and it support then you have an option called select multiple then you can select analytics and it support and if you want HR you can also do that and in the zone if you want West along with East then you can do the same east and west and you have all the employee details and in the designation let's try to select U senior trainee okay select multiple senior traine and manager so that's how you do it so that's how it's done and that's how you implement slicers in Excel to simplify the filtering options using slicers during a presentation and with that we have come to an end of this tutorial on slicers in Excel Excel supports a number of shortcut keys that you can use to speed up your work and make your work efficient these shortcuts make working with spreadsheets a little easier now the question arises do I need to memorize the shortcut keys the answer is no the keyboard shortcuts in Excel helps you perform data analysis better and faster but it would be an advantage if you can remember a few of them now let's begin by looking at some of the important workbook related shortcuts so we will see how to create a new workbook open an existing workbook and save a spreadsheet so that you don't lose any data or calculations that you have done towards the end you will see how you can toggle between different sheets in a workbook now to open an Excel workbook on Windows you can just search for Excel here you can see this is the Excel app I'll click on it and you can see there's the Home tab new tab from here you can open any existing workbook as well so let me go to the new tab and open a blank workbook now Mark here on the top it says book one- excl now if you were to create a new workbook the shortcut is control+ n now Mark this so if I hit cr+ n you can see it has opened a new workbook called book two I can show it to you here can see there are two workbooks book one and book two so the shortcut to open a new workbook is control+ n now if you want to open an existing workbook that is present in your system the shortcut is crol O So if I hit crol o you can go to browse and check for your Excel file that is present on your system let me go to the desktop and search for Excel workbooks you can see I have two Excel files here let me select the covid-19 file I click on open I'll enable editing there you go so we have our existing workbook opened on Excel now if you want to save a workbook or a spreadsheet the shortcut is control+ s now this is very simple you use it in other editors as well now suppose I duplicate this column I use contrl C go on top and hit control V now to save this spreadsheet I'll just press contrl s you can see the workbook is saved now you can also click on this one which says save control+ s so I don't need this column actually so let me go ahead and delete this column and now to close the current workbook the shortcut is control+ W so if I hit control W it will ask me do you want to save this file or not I'll say don't save and it will close that current file next if you want to close Excel the shortcut key is crl F4 now if I hit contrl F4 you can see my book one is closed so I can go ahead and close the workbook now let me go ahead and open a workbook again so I'll search for Excel and click on it I'll go to a blank workbook now if you want to move the next sheet you can use control page down now in this current workbook we have only one sheet now let me create two more sheets and come to sheet one if you want to move to the next sheet the shortcut is control page down so if I hit control page down you can see I'm in sheet two now if I again hit control page down I have come to sheet three next if you want to move on to the previous sheet the shortcut is control page up so let me hit control page up you can see I'm in sheet 2 now and if I hit control page up once again I've come to sheet one now to get a little more clarity on this let me open a workbook that has multiple sheets or multiple tables so I'll hit control o and I look for my sample Superstore data set click on it you can see this data set has three tables the first one is the orders table the second one is the returns table which has the detail about the orders that were returned and the next table is of people table that basically has person and the region to which the person belong to so let me come to my audits table now if I want to move to my returns and people table for that I can hit control page down which is to move to the next sheet so if I hit control page down see I am in the returns table now if I hit control page down once again I've come to my people table if you want to move back I'll hit control page up you can see I am in my returns table if I hit control P once again I've come back to my audits table now there are a few other shortcuts that I want to tell you here so suppose you want to switch between different tabs that are present in the ribbon you can do that as well with the help of shortcut keys so in Excel this is the ribbon which has different tabs like the file tab Home tab insert draw page layout and so on now currently we are in the Home tab if you want to move to the data tab the shortcut is alt a so if I hit alt a you can see we have come to the data tab now if you want to go to the view tab the shortcut is alt W you can see we have come to the view Tab and if you want to move to the formula tab the shortcut is alt M so if I hit alt M you can see we have come to the formula tab now let me open another Excel file while we have listed all the shortcut keys that we are covering in this video so let me go ahead and do that so I'll go to file and under open I'll click on browse and under desktop I look for my Excel shortcut keys file I'll click on open so this is the Excel sheet we have created for you for your reference you can pause the video to note the shortcut keys we have covered so far if you want to get access to this Excel sheet please share your email address in the chat section we'll send it to you over email so far we have covered all the workbook related shortcuts so these are the shortcuts we have covered so far that I'm highlighting here now let's move back to our slides and see what we have next so you can see we have our sell formatting shortcuts up next now here I will tell you the different shortcuts that can be applied to a cell such as editing a cell aligning cell contents adding a border to a cell adding an outline to all the selected cells Etc so let's go ahead and do these operations so I'll go to my book one and the first shortcut that we'll see is how to edit a cell for example there are certain values such as 10 20 30 and if I scroll down like this and now if you want to edit the cells you can just select that cell here I have selected A4 cell you can see on the top and hit f 2 to edit the cell so if I hit F2 you can see I can edit the cell and make it say 40 similarly I'll hit F2 again and make this as 50 and so on now you can also edit the formula that is present in a Cell so let me use a formula say We'll calculate the sum and I'll choose A1 to A8 and find the the sum now if I select this and click on FS2 I can change the formula as well let's say average I'll hit enter you can see it has calculated the average from A1 to A8 now if you want to copy paste cells the shortcut is very simple isrl C and contrl V now suppose I want to copy cell A4 I can just hit contrl C and I'll hit contrl V to paste it similarly you can also select a range of cells and hit crl C and crl V to paste it similarly you can also copy paste different cells from other worksheets so I have come to my sample Superstore workbook and let me select a few cells and I can copy it by hitting contrl C I'll move back and I can paste it control V next if you want to bold and italicize the font values in a Cell you can use contrl B and control I respectively so if I want to bold suppose the headings or the column names I can use control B can see it's Bold And if I want to italicize certain values let me select this one I can use contr I to italicize it I'll hit CR I you can see it's italicized now you can also underline the cell contents so let me show you how to do that for this purpose we use control+ U so let me select this cell and I'll hit control U you can see it has added an underline similarly let me do it for one more cell I'll hit control+ U you can see it has put an underline to this cell as well now if you want to fill colors to certain cells the shortcut you can use is alt plus h plus h so let me show you if I select this cell and hit alt HH you can see it has it has prompted me to select a color I'll select this blue color and you can see you have filled a color to this cell similarly you can fill color to a range of cells as well I have selected these cells and let me hit alt HH and this time I'll select this green color you can see we have filled all the cells with green color now if you want to add a border to a cell the shortcut is is alt + H + B now let me select this cell 40 and hit alt+ H+ B so it has prompted me to select the type of border that you want now let me select I want a top border you can see here it has put a top border to cell B5 and now let me choose cell E6 and again hit alt HB and this time let's say I'll select all borders you can see the cell has all borders across it now if you were to remove the outline border the shortcut is control+ shift plus underscore so if I want to remove this top border from cell B5 five I'll hit control shift underscore you can see the top border is no more there similarly let's remove all border from cell E6 I'll hit control shift uncore you can see the border is gone now if you want to add outline to selected cells the shortcut is contrl plus shift plus Amber sent so let me select the cells that I had colored just now and if I hit control Shift Amber scent you can see it has added a border to the selected cells I can show it to you here as well let me select it and hit control shift erson can see it has added a border to all the selected cells another thing I want to show you here is if you want to move to the previous cell the shortcut is shift tab so if I select this cell and hit shift tab it will take me back to the next cell which is E8 if I hit shift tab again it takes me to C8 next is B8 then it's A8 now if you want to move to the next cell the shortcut is tab so if I hit tab it will take me to the next cell now if you want to select the cells on the right the shortcut is control shift right arrow so initially if I select cell H2 and now I want to select all the cells on the right I can just hit control shift right arrow so as you can see it has selected all the cells on the right similarly if you want to select all the cells on the left the shortcut is control shift left Arrow as you can see it has selected all the cells now if you want to select the column from the selected cell to the end of the table the shortcut is control shift down arrow so I'll hit control shift down arrow you can see it has selected the entire column now if you want to select the data above the selected cell the shortcut is control shift up Arrow so I'll hit control shift up Arrow you can see it has selected all the cells that were present on top of it yeah let me again take you to the Excel sheet that is all the list of shortcuts discussed in this video so I'll go to file and I'll open Excel shortcut keys so now we have looked at all the self formatting shortcuts that I'm highlighting again if you want to get access to this Excel sheet please drop in your email ID we'll share it over email next we'll be discussing about some additional cell formatting shortcuts so up next we will see some additional cell formatting shortcuts here we will have a look at how to add a comment to a cell so comments are used to give extra information about a cell value you can also learn how to find a value and replace it with another value in the spreadsheet then we'll see how to activate a filter insert the current date current time and also add a hyperlink to a cell finally we'll see how to apply a format to the data in a Cell now let me go to my sample Superstore data set so this is my sample Superstore data set now if I need to add a comment to a cell the shortcut key I will use is shift F2 so let me select Brina Hoffman and hit shift F2 you can see my comment box has appeared now let me give a comment like Brina is from LA or Los Angeles you can see Brina Hoffman is from Los Angeles now if I select this you can see our comment next if you want to display find and replace a value from a cell or a range of cells you can use control+ H so let me select control+ H you can see our find and replace box has appeared and here if I want to change the name of say suppose Brina to something like Angela I'll first select for Brina so here I'm typing what I need to find that is Brina and I'll replace it with Angela suppose now I'll hit replace all you'll get a popup say all done we made 48 Replacements if I click okay and select close you can see the name Brina was changed to Angela X is another important feature which is to filter your data based on certain values now let me show you how you can apply a filter so I'll select this city column and I'll go to my data Tab and here you can see we have filter I'll choose this now we have applied a filter to our city column to show you more you can see here it has all the city name listed and if I uncheck this select all and I'll select certain cities and click okay it will filter my data only for those cities use contrl Z to undo it and now if you want to activate a filter the shortcut key that I'll use is alt down arrow so if I hit alt down arrow you can see see my activate filter box has appeared choose cancel if I click on a particular cell in this city column and hit alt down arrow again you can see the list of cities here now I'll move back to my book one and I'll show you how you can insert the current date now to insert the current date in a particular cell the shortcut I'll use is control+ semicolon so now if I hit control + semicolon it will give me the current date you can see this is our current date that is 9th of June similarly you can go ahead and get the current time as well so the shortcut for that is control shift colon so you you hit control shift colon it will give you the current time as well also Excel allows you to insert a hyperlink now in order to insert a hyperlink you can use control+ K so if I hit control+ K you can see the insert hyperlink box has appeared now you can give any address here so let me go to Google and say W3 schools I'll copy this link and I'll paste it in the address section and here on the top where it says text to display I'll write W3 schools website now if I hit okay you can see we have inserted a hyperlink and if I click on this it will take me to the W3 schools website so it opens up in my Google Chrome you can see this is the W3 schools website similarly let me insert one more link so I'll go ahead and click control K and this time let's say I'll choose ours website I'll select this website address and here in the address section I'll paste the URL and on top I'll write our website which is my text that it will display I click on okay if I click on this cell it will take me to my ours website going back now I'll tell you how to apply certain formats so let's see how you can apply a currency format so you can see we have some numerical values here I'll select this values and if I hit control shift dollar now these numbers have been converted into currency form format we have all in the form of rupees similarly if you want to convert a particular cell value say3 into percentage you can use control shift and percentage symbol so it has converted into 30% suppose it's 0.6 and on this if I select control shift percent AG it has converted into 60% another shortcut key that you can use is alt Q or alt+ Q this will go to the tell me what you want to do box so if I hit alt Q you can see it has taken me to the tell me what you want to do box if I select a peot table it will ask you to give the range and select where you want to place your pavot table I'll hit cancel now moving ahead if you have enjoyed watching this video so far consider subscribing to our our YouTube channel and hit the Bell icon to never miss an update let's see what we have next so now we look at some of the important row and column formatting shortcuts here we will learn how to delete rows and columns hide and unhide selected rows and columns as well as group and ungroup rows and columns I'll go to my sample Superstore data set now if you want to select an entire row I'll click on a particular cell and if I want to select row 7 I'll just hit shift space so you can see it has selected my entire row similarly if you want to select an entire column you can hit control space so if I hit control space it has selected my order date column now to select the entire worksheet you can use control shift space so I'll hit control shift space you can see it has selected my entire worksheet if you want to move to the edge of the worksheet you can use control right arrow so this is the edge of the worksheet now if you want to delete a column you can use alt + H + D+ C or alt HDC now let's say I want to delete a specific column me choose a column say the discount column I want to delete it so I can use alt HDC so let me press alt HDC you can see the discount column is gone I'll use control Z to bring it back now if you want to delete delete a row you can use shift plus space to select that entire row so let me hit shift space now I've selected my row 10 and then if I hit contrl minus it will delete row 10 and see row ID 9 is deleted if I undo it I have it back moving ahead now if you want to hide a selected Row the shortcut is control 9 so let's say I have selected row 11 and I want to hide it I can just use contr 9 to hide it now you can see we have hidden that particular row now if you want to unhide that selected Row the shortcut is control shift 9 so I'll hit control shift 9 to unhide it you see we have that row back similarly you can also hide a selected column now to hide a selected column let's say I want to hide first name column so I can use control 0 so if I hit control 0 you can see we have hidden the first name column now to unhide that selected column we can use control+ shift + 0 or control shift 0 so if I hit control shift 0 you see the shortcut did not work let me try it once again control shift Z it's not working so for that you need to change the settings by going to the control panel so let me search for my control panel I'll select it and now I'll search for language here let me search for language I'll click on it I'll go to advanced settings and then I'll click on change language bar hot keys in this here I'll select change key sequence and then under switch keyboard layout I'll choose not assigned and click on okay I'll apply and click on okay and then I'll close it now if I hit control shift 0 it will unhide my selected column moving ahead now if you want to group rows or columns the shortcut key for it is alt shift right arrow now suppose I have selected these two cells and I want to group these cells so I can hit alt shift right arrow so you can see the group box has appeared and I have selected rows if I click on okay can see here we have grouped both the rows now if you want to ungroup the rows the shortcut for it is alt shift left Arrow so I'll hit alt shift left Arrow row and select rows I'll click on okay to ungroup the rows now let me take you back to a reference sheet so now we have covered all the row and column formatting shortcuts that you can see here now let me move back to our slides now now we are into the final section of our shortcut keys here we will cover the shortcut keys you can use in a pivote table in Microsoft Excel a pivot table is a table of Statistics that summarizes your data for example you have sales data for different regions and for different products using a pivote table you can summarize the data by region and find the average sales per region the maximum and minimum sales per region total sales made for each region and for each category of products Etc it allows you to summarize and show only relevant data in your reports I'll create a PO table and show you different shortcuts to group poot table items ungroup poot table items then we'll see how to hide pivote table item create a pivote chart on the same sheet and create a pivote chart on a new sheet now let me go to Excel and create a pivot table using my Corona virus data set [Music] so I'll select file and I'll open a file I'll choose my covid-19 data set so you can see we have our Corona virus data set it has the date column the day column month year the total number of cases that have come up in each country the total number of deaths that were reported country territory code and we have the continent as well now let me show you how you can create a PO table first so I'll select my table and under insert I'll choose P table I have my table range selected then I'll choose existing worksheet to place my P table I'll select a cell and click on okay just squee this and I'll sort in this column as well now I want to drag my countries and territories onto rows so you can see we have all the countries here and then I'll drag the kis column onto values and similarly the deths column onto values as well if I close this and let me squeeze it further you can see we have our peot table ready it has all the country names and the total sum of the cases and the total sum of deaths that were reported in each country let's use this peot table and apply some shortcut keys now first to group peot table items the shortcut key that I can use is alt shift right arrow so let's select a few items from this poot table I have selected four rows and if I hit alt shift right arrow you can see we have created a group it says group one and it has four countries and the total cases as well as the total number of deads now if you want to ungroup the peot table items you can use alt shift left Arrow so let me use alt shift left Arrow so first I have to select this group and use alt shift left Arrow can see we have ungrouped the pivote table items now if you want to hide a pivote table item you can use control minus so let's say I want to hi hide the values for Afghanistan I'll use control minus to hide it see Afghanistan is no more there in this pboe table to unhide let me just use contrl Z it's back similarly if you want to create a pivote chart based on the pivote table on the same sheet you can use alt F1 so let me hit alt F1 close this and let me hit alt F1 there you go we have our P chart ready on the Sham sheet itself so you can see there's a blue line and there's orange line as well the Blue Line represents the number of cases and the Orange Line represents the sum of deaths this chart depicts that United States has the highest number of cases as of now and then you can see we have Hungary Iraq then there's Portugal and other countries as well let me delete it also if you want to create a pivote chart on a new worksheet the shortcut you can use is f11 so you just have to hit f11 so let me hit f11 you can see here it has created a new pivote chart on another worksheet it has the sum of the cases and the sum of the deaths with that we have covered all the shortcut keys that can help you speed up your work in Excel if you think we have missed out on any shortcut keys then please put it in the chat section as well as in the comment section of the video now let me go back to my slides now let's have a question and answer session where I'll address a few questions so please feel free to put in your questions in the chat section of the video there's one question which I can see here is how important is Excel for data analytics so Microsoft Excel is one of the top tools for data analysis it is recommended for those who don't know programming Excel supports a wide range of functions to short and filter your data perform calculations and apply conditional formatting as well you can also create P tables and P charts perform wtif analysis and derive valuable business insights by visualizing the data using reports and dashboards another question I can see in the chat is is it necessary to memorize all the shortcut keys not at all you need not memorize all the shortcut keys it is hard to remember all of them shortcut keys are only useful for performing your tasks better and faster but it would be an advantage if you memorize a few of them also if you continuously use it while working on Excel then it will be easier for you to remember them let me check a few more questions okay the next question I can see here is can Excel be integrated with other software of course it can be Excel data can be imported into Microsoft exess Microsoft Word and PowerPoint use Microsoft Excel to create tables within the docum docents powerbi desktop uses Microsoft Excel to analyze data similarly Excel can also be integrated and used with Microsoft SQL Server Python and R programming now let me check if there are any more questions okay someone has asked how to secure information in Excel so Excel has a cell locking feature that prevents other users from changing the information in the cell additionally the entire worksheet of workbook can be password protected if there are certain parts of the workbook that should not be seen by unauthorized people you can place the password on those parts of the workbook or the entire workbook itself now I think I have covered most of the important questions if you have any more queries put it in the chat section our team will reply to it as we speak you might wonder what it takes to become an expert data analyst if you are an aspiring data P list with at least one year of domain experience and looking for online training and certifications from prestigious universities and in collaboration with leading experts then search no more ours postgraduate program in data analysis from Pur University in collaboration with IBM should be your right choice for more details on this course head straight to our homepage and type in postgraduate program and data analyses or simply click on the link in the description box below with that in mind over to our training expert SPS so what is sum in Excel now you might want to calculate uh the total sum of sales right so let us assume that this is your entire store's sales data and you wanted to calculate the entire sum of sales so there is a su function in Excel where you just have to you know select all the cells and apply some function onto it and you'll get the sum of all the sales right so with something like this you'll select all the cells and then you'll apply a sums function and you'll get the sum here and average everything right so this is fine but what if you just wanted to find the sales that are happening in only western region as you can see our column f is based on region so we have four regions South West East and Central so your manager asks you to find the sales happening in suppose say west region so how do you calculate it would you manually go into each cell and check if it's West and calculated that will be time consuming right so what if you had a method which could do it for you just in a matter of few steps or few clicks right so that's exactly sums so it will basically add a condition calculate the sum of sales where the region is equals to West simple to like or similar to Cal query right so that is exactly what we're going to do in Excel today that is using summs now let me add here Sales of West Region now in the next cell let's add a sums function remember there are multiple sum functions in Exel so when you type in suum you can see so many options based on sum right you just have some followed by that you have some if it is just some if not some ifs today we are concentrating on some ifs so not just some if we'll go to some ifs so we'll be having an option of placing multiple if conditions there so let let's also try that more on that later now let's press tab to select our sum FS function now okay we forgot another important step so before we apply sufs we need to convert our table into a table format yes it looks stable but right now it's not in tabula format it is in the format of database we know that Microsoft Excel considers its data as a database by default so to convert the database into table just click anywhere on the data and press PR contr T so that you'll have a new dialogue box and it will ask if your table has headers just make sure that you have Ted in it our table does have headers so select okay now our database has been converted into a perfect table and it's ready for applying sums function so now you can see our database has been successfully converted into a table now let's begin with our summers now again remember we are using some ifs here not some if or some so select some ifs and press Tab Key and we have our sus function now the first step which column are we looking at what are we finding out right which is a sum range what sum do you want for now we need sum of sales so select the sales column and we have a simple key function here let's hold control again press shift and hold shift together so we're holding control and shift key together and now we pressing lower Arrow key once yes the entire sales column has been selected comma Now where do you want to look so we want to look I mean which part of the table you want to fetch right so this is the region part we want to fetch right now so again hold control and shift together lower Arrow key the entire column is selected now the next last part so we want to look into the both of the columns that is sales column and region column now which type of data you want so for now we want Western type of data that is the sales data which is happening only in West right so we'll select this cell with the data as West or you can also manually enter it for now let's select the data so I'm selecting the cell F7 which has uh data as West right now let's close the parenthesis and press enter so there you go you have the sales happening in the western region now you can also try to format it in the form of currency so let's use uh dollars since it is based on American States so let's use dollars so there you go now we are having the sales data of West now let's try to copy the same to all the for Cs and uh for now here let's type in East and this can be South right and this can be your central there you go right now you can also copy the same formula and make minor edits to it so here instead of f8 you can select uh East so where is East here and press enter so you have the sales related to East now let's try to edit F9 with South select South Region enter so you have South sales and what remaining is Central so let's edit Central as well and press enter so there you go right now you have all the separate sales data from west east south and Central now again uh you might want a bit more granularity in data right or in your reports right like you wanted to find the sales data happening in West and you wanted to find out the category based on only Furnitures so your manager will ask you okay a fantastic job so now just give me another minute detail on the data I want to calculate the sales happening on West based on category Furniture right so he wants furniture or he wants office supplies only right you can also do that it's not an issue at all now let's try to copy this cell and paste it here and sales invest let's add another input here on furniture right now you wanted to calculate the sales happening in west region only on furniture so you can also do that now we can use the same formula as well just copy paste the formula here right and you can add few things here when you try to add a comma it will automatically generate the next criteria it is expecting that you are looking for adding another criteria right now which column you want you want category so let's select category you want to select all the cells same trick control shift lower key and again press comma and you want it to select only uh Furniture so select on furniture your C there you go now press enter so there you go we successfully have the sales data from west region based on category Only Furniture so that's how you do su in Excel you can also add a few more details to it like in Furniture you can also look only for tables so here you can see in west region we have furniture sales data and we have sales data of West Region based on furniture which includes only tables you can also do that using suus function in the same way all you have to do is select the subcategory column and select the cell which has the data as tables so this is how you use sums in Excel now without further Ado let's get started with how to create drop down list in Excel now for that we might want to switch to the Practical mode now we are on our Excel spreadsheet and here you can see I'm trying to create an calendar for the employees in the company where you might have to you know write down the day and month and year or week of your birth date or maybe your joining date or maybe also your last working day right this might be useful for all the employees to you know uh avoid them from writing it down manually so how do I create it so it takes a few simple steps all it takes is some data validation methods and also there is an exclusive video specifically made on data validation in Excel link to which is in the description box below or you can search it on our official YouTube channel now you can see that uh the word calendar is on the first cell that is A1 but we have four cells below it that is A3 B3 C3 and D3 so you might want to add this to all the cells right like maybe in the middle so for that you can do the option of mergin Center also there is a tutorial on mergin Center about all the options available in this particular thing so you can go through that also and and uh yeah we have done the first step now all we need is day month year and week so in a calendar or in a month you'll have days from 28 29 30 or 31 days right so let's create a list or the drop down menu for that so that whenever you click it right so whenever you click on that particular cell you will have the list of all the days available right so let's get started with that let's create cre a new sheet so why are we creating a new sheet you won't want that all the numbers in your calendar sheet right over here right so for that so we are creating a new sheet and all the data will be available here so here let's write down the days first and here the second number also you can use the option of Flash Fill so that you can you know uh reduce the effort here so you can see the flash full option over here all you need to do is Select this El and drag them down until you get your required dates right so let's keep it till 301 days there you go you have the days so these will be your days and here now the next option was month right yeah month and yeah now using the same flash F option you will you know add the months into your sheet so this will be Sunday so Excel has this smart flash F option where you can just drag the data and everything will be filled according to your requirements so Sunday to Saturday now the next one right that is your month so same as the before process we'll create January you can use the FLV option and all the way up to December now the last one was the year so 1990 and 1991 so you can select the cells and Flash Fill Up to 2022 maybe yeah 2022 should be good enough now this is the FL flash option you have here and also we have an exclusive tutorial on flashfill Al together you can go through it for a detailed explanation as well now coming back to the original sheet so this is where you create your drop down list right so select the cell where you want to add the dropdown list navigate to data and then here is your data validation menu so we have a variety of data validation options over here there is an exclusive tutorial on that you can go through it for now we'll look into the list option only because we want to create a drop down list right so here it will ask for Source click on the source here the source bar right now navigate to sheet number two where we have the days options select the range so this will be your range and select okay also you can add some message here right what you can do is uh in case if there is an error or something right you don't want that to be happening so all you need to do is uh write down and select from dropdown select from drop down only so this could be a message select from dropdown only the title is Select options or just options in case if there's an error you can write down your title as invalid data and you can write on a messages please select from drop down only so um in case of someone tries to write as story2 or 33 to check the Integrity of your data validation then they will encounter an error and an error message and then they must be understanding what exactly they try to do and if it's correct or wrong right so this is to make sure that whatever the data you enter is correct and valid so okay and here you can see select from the drown list only options with option list as title and let's try to enter to2 right and when you press enter you will see an error so the error messages invalid data please try to select from drop down only so you can go for a retry and select the drop down right so let's eliminate them and now select something from the drop down so let's take it as maybe 10 right so this is how you cleared the day one now let's try to do with the month same data validation option settings will be list and Source will be this one think I made a mistake here so let's cancel this and go back to sheet two and rename this as our week yeah now coming back to the month again it will be a list and the source will be in sheet two navigate to the sheet 2 and select your range and input message the same options select from drop down only and error alert title will be invalid data this one will be please l from drop down on it's okay and that's done now let's continue with here as well let's quickly do the same it will be a list Source will be in sheet2 this is your list press okay and lastly the week same process it will be a list Source will be in your sheet two input message options ble invalidator let drop down only right so your drop down list is ready now so the month will be anything So currently we have me and here can be anything anywhere so let's take it as 997 and week can be you know the adjacent week and so what was 10th of May 1997 let's check our calendars a Saturday so it was a Saturday and that's how you create drop down list in Excel and if you want you can also add borders to your calendar right so that's how it's done so on my screen you can see an example Excel budget template theme so on my screen you can see a column where you have the debit amount so in the I column we have all the deit amount what we are spending for a month and on the right column will be the balance amount or the running balance of that for example you can see that there is a row number five where the debit amount is 8,000 for a jeans Levis and it is done through UPI payment now if I change this value of 8,000 to 4,000 something then you can see that the running balance will automatically change you can see that currently the running balance is 73,000 and if I change this value from 8,00 ,000 to 4,000 then all the running balances will change let's do that so for that let's press enter there you go you can see that the number has been changed successfully and all the values from the running balance are also changed until the last day which is the March 30th now how is this working and not just that you can see that we have also included what is the total debit amount in January and what is the running balance that is the total available balance after 1 lakh has been debited into the account in the month of February as February income now the current available balance is 13,200 and the total expense in February is 1 lakh 4,500 right how am I doing all this looks so much interesting right now let's do this practically so for that I have already created a new sheet and I have included all the data that we have used previously the income of that particular month and all the expenses of that particular month now without further Ado let's begin to implement the Excel budget template now our first step happens to be converting all the data into a natural table so you know that in Excel all the data values are stored in the form of a database by default so it happens to be our duty to convert the database type table into the pure table type table so for that select all your data and press contr T and there you go we have an option to convert this data into a table and remember to check this particular check box and tell your Excel that your table has headers so we do have headers here so now press okay and your table will be converted into a natural table now let's type in the formulas so now let's select the cell J2 and type in the formula so what are we doing here is we are trying to add the sum of G2 + H3 so which is this particular cell and removing I3 that is this particular debit amount so this formula is used for checking the running balance let's press enter and there you go so it means that you received 1 lakh rupees in the month of January and out of that 1 lakh you spend 15,000 rupees for something maybe your educational Lo or something and then the remaining running balance out of 1 lakh Rupees is 85,000 rupees now let's copy the same formula to all the Sals and there you go now the running balance has been calculated for all the sales here now remember the monthly income available balance and debit for January and final balance now let's try to calculate that for all the three months so here we have three months that is January February and March now let's do a simple calculation to find out the available balance of that month and the total debit made by that person in that particular month the formula is really simple all you have to do is use a sum function and calculate the sum of all these values and done right there you go so the total expense of the month January is 93,00 rupees now let's calculate the total D of February month same formula that is calculating the sum of all the expenses made in the month of February there now let's also calculate the sum of all the expenses in the month of March done so we have the final values of all the total expenses made in those particular months now let's calculate the final balance now I think I'm forgot one important thing so before we calculate the final balance we need to find out the available balance that is the available balance after 1 lakh Rupees is credited into our account and along with the savings that you have from your previous month right for that we have a simple formula now which all we doing is just adding the uh income sell with the balance sell that's all so there you go now after crediting 1 lakh rupees and adding up the uh balance of that particular month that is we have 13,200 remaining from the month of January and the month of February we have income of 1 lakh so the total amount is 11,3 no no no it is 13,200 yeah now we have the final balance that is the difference between the available balance and the debit made in that particular month so in the month of February we had a total balance of 13,200 and the total debit of 1 l4500 so the final remaining balance is 8,700 similarly the same formula has been applied for the month of March and the final balance we have is 37,300 so that's how you implement budget template in Excel all simple formulas and you have all the numbers at your FS so first let's understand what exactly is misis report in Excel so Mis stands for management information system Mis reports provide data on different categories for accurate decision making so Mis in x is a procedure to create interactive report using Microsoft Excel so Mis reports help management to access the performance of organization and allow faster decision making so if you know to create the Mis reports using various business intelligence tools such as Excel then you might find a very good job opportunity in a well established company now let us try to learn how to create Mis report using Excel in a practical mode so let's get back to the Microsoft Excel IF getting your learning started is half the battle what if you could do that for free visit scaleup by our click on the link in the description to know more so here you can see that I've got some sales data of various car manufacturing companies and I've also got some slicers so using these slicers I can get the realtime information from the dashboard so I'll select an ER from the first slicer and I'll go into the second slicer and I'll select a company now I'll select BMW and SUV type so we have the data of all the SUV types cars manufactured by BMW car manufacturing company in the year 2018 now let's start and create something similar to this one so how did I create this mis report let's go through this in a step-by-step way so on my screen you can see that we have created a completely new Excel workbook and we have some sample data with five rows and 20 columns so so this particular data is based on the manufacturer year and the car manufacturing company and the type of the car manufactured and number of cars manufactured in that particular year and also the price of that particular car now to create Mis reports we need to play around fav tables so we also have tutorials on fav tables and you can go through that in our our YouTube channel and also we will try to link those in the description box below now let's select the entire data and and go into the insert ribbon and there you can see the first option that is pivot table so after selecting the pivot table option it will show this particular dialogue box where it will ask you for the range of cells you need to select and the next one is to create the P table in the same sheet or a new sheet so we'll take a new sheet here now select okay and there you go you have the pivot table here so first you have an empty pivot table and on the right side you can see we have pivot table Fields now you can either drag these options into the rows and columns or you can also click it so now let's drag and drop these first we will select here and car followed by that we will take type quantity and price to columns so there you go we have our pivot table now let's select all the data in the pivot table and again let's get back to the insert option and here you can see an option called pivot chart select that and you will have various pivot chart options here now you can select any one which you can prefer or which you like or whichever you think can represent your data in a best way possible so now let's go with column chart first let's select okay and we have our column chart over here now this happens to be our first chart now let's create a few more charts so to create few more charts we'll go back to the same original data select again insert and pivot table now again a new worksheet so here we have another Mt pivot table in the similar way let's drag the same data into rows and columns there you go and again we will select all the data and create a pivot chart go to insert option pivot chat and now let's try to select pie chat select okay and we have our pie chart over here now let's try to create one more same procedure select table select pivot table then create new worksheet for the new pivot table and now drag and drop the data now let's go to insert again let's select all the data go to insert option and pivot chart now let's try to select the bar chart select okay and there we have our bar chart now remember the first chart we created that is in sheet two this one so we will try to move all the charts to this particular table or this particular worksheet so let's get back to the sheet number three right click and you can see an option called pivot chart analyze so from here you can and move the chart select the option of moving the chart and don't select the first one select the object in option and select the sheet number to which you want to move this chart for now we want to move this chart to sheet number two select that press okay now we have the chart moved to the sheet number two and in the same way let's go to the sheet number four and let's move this particular chart to sheet number two there you go the chart got moved to sheet number two now you can insert the slices so we have selected the third sheet or third chart and inside the pivot chart analyze option we have an option of inserting the slicer now we will insert the slicers for here car and type now select okay and you will have all the three slices onto your sheet now let's rearrange them so I'm just organizing them in a neat way there you go and you can also customize your designs for all the charts so that they look a little more better now if you see if I make some changes only one chart will be interacting for this right so if I select Ferrari and year 2020 and option as Sports you can see only one chart is interacting to these particular slices why because all these slices are connected to just one chart here so you need to make sure that all these slicers are connected to all the three charts what we created so let's erase all the filters and select one slicer at a time right click and you will see an option of report connections select that and you see that the connections are only connected to the pivot table 3 so select the remaining pivot tables as well select okay and in the same way select the second slicer select the report connection option connect pivot table 1 and two and select okay and in the same way the third one so now all the slices are connected to all the pivot charts now if you make some changes here they will respond automatically in the same way now let's select the for company the year 2019 and hatchback option and you have all the cars which were made in the year 2019 from Ford Company in the segment of hashack so this was the sample data set that we work done now you can also work on some complex data sets such as this Superstar data set now one thing you need to make sure that when you working with Excel Mis they need to make sure that all the macros are disabled on your data set and you convert the entire data into the regular table format of excel right now Excel is considering this particular data as a database so for that you need to select all the datar press control T and then convert the entire data in the form of tabular data and also don't forget to click this icon which reads my table has headers so the first row is the headers now let's select okay and your entire data will be converted into tabular form now now let's reset this to the normal type which has the clear color not all the fancy colors now the entire data is converted from the dbms to the Tet form and now you can start implementing the Mis reports and PIV tables so that's how you work on Mis report in Excel power query is an advanced feature of Microsoft Excel that allows you to prepare your data for analysis you can perform numerous text computations and numerical analysis to make your data more powerful and informative ative Excel power query is a data preparation and transformation engine it allows you to carry out the extract transform and load operations on the data sets from multiple sources now let's look at the challenges solved by power query earlier in Excel there was difficulty in data connections Now using power query you can connect to a broad range of data sources such as relational databases web files text CSV Json files and even fetch data in the cloud volume variety and velocity are the characteristics that define Big Data it was a major problem to handle such data now power query enables you to transform your data to an appropriate size it also allows you to work on any shape of data from any Source earlier updating your data and refreshing it in real time was an issue using power query a repeatable process query is adopted to update the data in real time and in the future in Excel it was not so easy to reape transform and manipulate data but using power query provides a highly interactive experience and sophisticated tools to prepare your data now from Excel 2016 onwards power query on Windows has been fully integrated into Excel but in Excel 2010 and 2013 for Windows powerquery is a free addin you can go ahead and download the link once installed the power query tab will be visible in the Excel ribbon now coming to the features of power query so Excel power query allows you to clean transform manipulate and process your data for analysis it helps you to automate repetitive task that you want to do it over and over again you can search for data sources and make connections as in when you want Power query helps you to prepare and shape the data in the right format for performing analysis and finally once your data is ready you can share your findings or use your query to create interactive reports and dashboards now let's have a glance at the demo that we are going to work on in this video so we look at how to load data from different sources you will understand how to extract tables from web files so we'll extract tables present on Wikipedia Pages then you'll learn how to sort and filter data up next you'll see how to group your data how to split a column into multiple columns and then pivote and unpivot your table then you're going to work on date columns and make some Transformations understand how to append tables and merge tables vertically and horizontally now let's open msxl and start with importing a simple text file so you can see here I have my Excel file opened and in the middle you can see I have my employee txt file now this is a comma separated file meaning that the values are separated by commas as you can see it here The Columns have been separated by commas and all the values have a comma between them now this text file has information about the name age company and the city to which the employee belongs to let's see how easily you can import this EMP text file into msxl here I'm using Excel 365 or Office 365 where power query on Windows has been fully integrated into this version of excel so what I'll do is first let's go to the data Tab and you have this section called get and transform data so I'll click on get data under get data I'll go to from file and select from text SL CSV once I click on it it will ask me to give the location where the file is there so my file is on desktop I'll click on power query files and here you can see I have my employee txt file you can see the type here it says text document click on EMP and hit import now this will take some time to import the file onto Excel it's establishing a connection you can see there you go so here you can see the file name em. txt you can see the file origin as I said the D limiter is comma and we have the data type detection and here you can see we have our text file so Excel power query feature has automatically detected the column names so we have name age company and city as our columns and these are the values now let's hit load so this will load all the rows and the columns on to excel there you go it was really quick Excel has automatically loaded our text file now you can see here we have another tab called queries and connections so we have made one query and have loaded the employee data it gives a preview also you can see it here and if needed later you can go to edit and change some values that we'll see later now you can use this data to create simple visualizations so let me show you how to do it so let's first select the data and we'll go to the insert tab under insert tab I'll click on recommended charts let's say I want to know how many employees belong to a particular City so I'll click on the second chart which says the count of Name by City And I'll click on okay here you can see we have a nice clustered bar chart and you can see in Bank Bangalore there were five employees in Hyderabad we had two and in nask we had one you can see the count here this is a pivot table if you go back to our actual data here you can see in Bangalore we had Vidya Sonam ainash vun and himani and there were two more people from hyad and one from nask so you can use the Excel power query feature to import data onto Excel and make some visualizations now if you want to fetch some data that is present on the web or on the Internet Excel power query features and functionalities can help you import those data as well we will now see how to import data specifically a table that is present on the web here I'll be using a Wikipedia article on the list of European Cup and UFA Champions League finals let me show you the page first so this this is my Wikipedia page on the list of European Cup and UFA Champions League finals if I scroll down you can see all the details here and I would like to import this table which has the list of all the finals so we have columns like season winners score line Runners up venue and attendance let's import this table onto Excel so I'll copy this URL first now let me open a new sheet I'll go to sheet one and then on the data tab under the get data section I'll go to from other sources and here I have from web which allows me to import data from the web either you can follow this path or if you see here there's an option to get data from the web if I click on this it is asking me to enter the URL so I'll paste the URL of the Wikipedia page here and let's click on okay now it's navigating and okay you can see Power query feature in Excel has given us a list of tables which you can see here we have something called is document there's a key table and here you can see there's one table which was extracted from the Wikipedia page and the table which I interested in is this table which has the list of European Cup finals you can see it here Excel power query feature has automatically detected these rows and it has given a list of columns as well you can see these are the columns now we'll explore the transform data tab present in the power query now if I click on transform data it will take me to the power query editor Let me just click on refresh now this is the most important section using the power query editor you can clean your data filter your data manipulate your data and make it ready for analysis now let's explore a few tabs available here so we have a Home tab that has a query section you can see you can reduce rows manage columns sort the data transform the data combine different tables now there's another tab called transform which allows you to select your data type transpose your row and columns pivote and unpivot your columns and here you can see you can find out some summary statistics and you can manage your date values as well now if you want to add some new columns to your data you can do that as well and you also have a view tab now here if you see the first two rows the values are the same you can see season season these are all repeated which we actually don't need and these are pretty similar to our column names similarly if I scroll down you see there are some rows which have null values so actually these rows do not add up any value or do not add add any value to our data so we'll clean this data first so let's see how to do it now if you want to remove certain rows in the table what you can do is go to the Home tab and under reduce rows click on remove rows and then choose remove top rows if I select remove top rows it will ask me how many rows do you want to remove from the top can see it here I'll give I want to remove the top two rows now let's click on okay you will see that the Excel power query editor has removed the first two rows and on the right you can see the steps that were applied you can see it here previously we had these two rows which were redundant and once we applied the step it has removed the first two rows similarly let's go down and remove the last 1 2 3 4 5 so we are going to remove the last seven rows from the table so again I'll go to reduce rows and click on remove rows now this time I'll select remove bottom rows and here I'll choose I want to delete seven rows from the table from the bottom I click on okay if you see the last seven rows have been deleted now let me show you the last row which is this one so the last season of Ufa Champions League was held in 2020 which is this year and Bayern Munich won the Champions League against a French team that was Paris St Germans by a score of 10 now if you see the last value that is the attendance you can see the value is zero which means there were no Spectators in the stadium it was because of the covid conditions now let's do some more manipulation to our data suppose this time I want to add a new column let's say a stadium name by extracting values from the venue column so let me just show you the venue column so this is our venue column so the first value is the stadium name then we have the city in the stadium is there and finally we have the country name so I want to extract only the stadium name so you can see we have some stadiums like Santiago babo there's bemble sanso and another stadium so I want to extract only the stadium names so let's see how to do that I'll click on the venue column and go to add column tab under add column here you can see we have extract if I click on this drop down there's an option to select text before D limiter so if I choose text before D limiter here I'll give my d limitter as comma so everything before the first comma will be considered as the stadium name now let me click on okay now here you can see under applied steps it says inser Ed text before D limiter if I scroll to the right you can see the last column has our stadium names let me double click on this and change this to stadium name column and hit enter even this step is applied here you can see renamed columns similarly let's explore a few more features now I want to add a new column called stadium city so if you consider this venue column whatever is there between the two commas is the stadium city for example Santiago bernabo is in Madrid similarly Wembley Stadium is in London sanso is in Milan so these medial values I want to extract into a new column called as stadium city so let's see how to do it so I'll click on the venue column and go to extract and again I'll select the extract Tab and here now I'll choose text between D limitter and my first D limiter I'll give is a comma and a space you can see here all the values have a comma and a space and my end Del limiter will be another comma then I'll click on okay this will add a new column to the extreme right of the table you can see here we have text between D limitter and it has extracted the stadium city let me go ahead and rename this column as stadium city and hit enter you can see the applied step here now I want to split the score colum into two columns so here we have the score column and the left value presents the number of goals that were scored by the winning team and the right value presents the number of goals that were scored by the losing team so here I'll split the column into two columns as winner score score and loser score so what I'll do is Select this column and go to the Home tab under the Home tab we have split column I'll select split column and then choose by delimiter so automatically Excel power query detects that this Dash is my delimiter and I'll split at each occurrence of the delimiter let's click on okay now you can see the score column has been split into two it has renamed the column as score do1 and score do2 what I'll do is I'll go ahead and we'll rename this column as winner score and this will rename it to Loser score I'll hit enter and I have renamed it successfully next let's change the winners team and the runners up team values to uppercase so suppose I want to change all the values or the club names of the winners team to upper case so what I can do is I'll select this column and go to the transform tab under transform tab I have this option called format so here I'll click on format and then you can see I can change the case to lower case upper case capitalize each word so here I want to make all the winner teams as upper case I'll select uppercase there you go we have successfully converted all the winner team names to upper case similarly let's do it for the runners up team as well so here I have my Runner sub team I'll go to the transform tab click on format and and select uppercase now we saw how to do some simple manipulation of our data so we created a few columns split a few columns now to save all this I have to go to the Home tab and then click on close and upload this will take some time and load our data onto this Excel sheet you can see here it's loading the data this will take a bit of time there you go Excel power query feature has successfully performed some manipulations on our data some calculations on our data and then it has saved the final version and loaded it on to excel Now using this clean data we can do some analysis let's say I want to find the seasons in which the winners team scored more than three goals so we have a problem statement at hand where we want to find the seasons in which the winner team had more than three goals scored so what you can do is select any cell in this data and go to the insert Tab and click on pivote table here I'll click on existing worksheet and then I'll give my location I'll place my pivot table somewhere here and click on okay all right now since I want to know the seasons I'll drag season onto row and I'll also drag the winners team column onto rows then I'll choose my winner score under values here you can see we have the pivote table ready now we need to filter this table to see all the winning teams that scored more than three goals so what I'll do is I'll select this winner score column and place it under filters and and here you can see I have my filter let me click on this drop down and I'll select multiple items from this multiple items I'll choose four 5 and 7 because these values are greater than three and click on okay here you can see I have filtered my P table and to the left you can see the season and the winner team that had scored more than three goals in the finals all right similarly you can perform some more analysis suppose I want to know how many times Real Madrid won the championship so let's see I'll click on one cell in the data set go to the insert Tab and click on pivote table I'll choose existing worksheet and give my location here let's say I want to place my pivote table here I click on okay all right so the question we have is how many times have Real Madrid won the championship so I'll choose the winners team column and place it under row and let's say We'll select the winner score as well and let's convert this winner score from sum to let's say count and click on okay and since I want to check only for Real Madrid so what I'll do is I'll go to the insert Tab and I'll insert a slicer here I'll choose winner teams as my slicer and click on okay and out of this I want to choose only Real Madrid so I'll select Real Madrid you can see it here Real Madrid have won the championship 13 times okay let's say you want want to compress your data and remove unnecessary columns without losing any information you can do that using a feature in the power query editor called unpivot to perform this task we'll use a sensus data of India from Wikipedia so let me first show you the Wikipedia page so this is my Wikipedia article which says list of states in India by P population and if I scroll down you can see it here there's a table which says by P population from 1947 to 20111 so there are a few columns like rank this State of Union territory and we have population starting from 1951 till 2011 which was our last census here you can see if I scroll further there are nearly 29 States and we have seven union territories so we will extract this table and load into Excel first okay so let me click on a new sheet and we'll follow the same drill I'll go to my data Tab and click on get data under get data I'll go to from other sources and click on from web here it will ask me to provide the URL link of the Wikipedia page so I'll paste the URL here and click on okay now once I have done that it will load a few tables onto X Excel you can see there are a few tables here I'll click on the first one so we have our table here now let's do some transformation to this data this opens in the power query editor okay I'll click on refresh first this will take some time to refresh the entire data okay we are done now if you see this data clearly the first row in this table is not necessary at all because these are our column names so we also have our column headers already present so let's go ahead and delete the first row so we'll go to the Home tab under remove rows I'll click on remove top rows now I'll give my number of rows as one so we want to remove the first row only I'll click on okay you can see the first row has been deleted if I scroll further I actually don't need the last row as well which is the total so what we can do is we can also remove the last row from the bottom so I'll select one click on okay you can see the step has been applied and you don't see the last row that was the total row anymore so the task that I want to do here is I want to compress all these columns which are basically the population columns so I'll select my state or Union territory column and go to the transform Tab and here I have the option to unpivot columns so I'll click on this drop down and select unpivot other columns you can see here the step has been applied and all the population column from 1951 till 2011 have been unpivot if you want you can go ahead and rename these columns let's say I'll write it as population column and let's say this is I'll rename it to total population value all right now we are done with our preparation of data let's go to the home Tab and click on close and load now this will take some time to load the data onto Excel all right so we have our Census Data here so first you can see rank one is utar prades and we have the population starting from 1951 till 2011 then we have for Maharashtra if I scroll down you can see the other states we have Tamil Nadu Rajasthan this Karnataka if I scroll further we we have udisha this Telangana Kerala now if you see here the population values from 1951 till 2011 for the telengana state are all na which means there was no data available now this is because telengana was only formed in 2014 so there was no census for this state let's continue with our demo and let's explore a few more features and functionalities of powerquery editor now the next table we have going to use is an adventure Works customer table now this data set is provided by Microsoft for practitioners who want to learn powerbi Excel or similar Technologies and want to do some manipulation some calculation some data analysis stuff so let me go to a new sheet and let's import the adventure Works customer data set it's a CSV file onto Excel first so I'll go to my data Tab and click on get data from here I'll go to from file and choose from text SL CSV you can see it here in my power query files folder I have my adventure Works customer table I'll select this and click on import this will take some time to load the data set you can see the preview of the data set here so we have columns like the customer key we have the prefix of the customer name the first first name the last name of the customer date of birth marital status if I go to the right we have annual income total children education level occupation and homeowner column as well so let's click on transform data here we'll learn a few more features of power query okay we have our data on our power query editor so first what I'll show you is let's change the prefix column the first name column and the last name column to proper case so you can see it here the prefix first name and last name columns are all in uppercase now if you want to change the values to proper case just hit control and select the three columns and go to the transform tab under transform tab you have format so click on format here you can see we have lower case upper case now proper case is to capitalize each word so I'll click on capitalize each word you can see it here now we have converted the prefix first name and last name column into prop case The Next Step I'm going to show you is let's merge all the three columns the prefix column the first name column and the last name column into one full name column so what I'll do is I'll select the three columns and then let's go to add column and under add column we have this option called merge columns let's hit merge columns okay now it's asking you to give the separator I'll select my separator as a space and click on okay okay so before click on okay I wanton want to change my new column name from merged to let's say full name and now let's click on okay you can see here it says inserted merged column and if I go to my right you can see it here we have a full name column now if you want to shift the location of the full name column you can do that as well just hold this and keep on dragging to the left this will move the entire table to the right and you can place it wherever you want so I want to place it let's say here okay now actually I don't want all these columns so let's delete it so click on this and I'll right click I click on remove you can see we have removed the prefix column similarly let's remove the other two columns you can either right click and do or go to the Home tab and then select remove columns all right now let's say we want to add a domain name column from AWS customers by extracting the characters between at theate andom so actually I'm talking about the email address from this email address we want to create a new column called as domain name for that we'll extract characters that are present between AD theate and doom so let's see how to do it I'll select the email address column and then I'll go to add column under add column I'll click on extract and this time we won to extract between two delimiters Al so I'll select text between delimiters and I'll give my starting delimiter as at the rate and my end delimiter would be now let's click on okay so this will insert a new column to the extreme right you can see we have our domain name let's go ahead and change the column name to domain name and hit enter all right you can see the step has been applied now we are done with our preparation of customers table let's just just go to the Home tab and click on close and load so all the transformation that we did in the power query editor will reflect here so you can see we have our full name column and if you see we have our domain name as well okay using the power query editor you can perform some statistical analysis now let's explore those statistical features for this we'll be using another data set called Adventure Works product data set again this data set is also provided by Microsoft so let's go to the new sheet and here I'll go to the data tab click on get data under from files I click on text SL CSV here you can see we have Adventure Works products I click on import let's click on transform data so we have our data loaded onto the power query editor the data is mostly clean so let's not alter this data let's straight away go ahead and explore some of the statistical features that we have here now let's say if you want to find the total number of product names in the product table we have this product name column and say if you want to find the total number of product names in the table how to do it so what you can do is click on this product name column then go to the transform tab now in the transform tab you have an option called statistics click on this drop down and select count values now this will open another window that will return the total number of products in the table you can see the value here it says 293 now to move back to the query editor we have to cancel this step here so let's just click cancel or close we are back again all right now let's say you want to calculate the average product price from the product table so if I move to the right you have a column called Product price so let's see what is the average product price from the product table so I'll select this product price column go to the transform tab under statistics I'll select average so this will give me the average product price which is 714 4373 you can consider any unit you want let's say this is in dollars again we have to cancel this step to move back okay now if you want to find the maximum minimum product price you can do that as well so let me select my column product price and go to the transform tab under the transform tab we'll click on this statistic drop down and let's say I'll select minimum so this will give me the minimum product price which is 2.29 similarly if you want to find the maximum product price so select this product price column go to the transform Tab and then choose maximum so this is the maximum product price in the product table we have let's cancel this step okay now you can also round the product cost and product price column to two decimal places so if I show you both the columns you have your product cost and you have the product price column you can see the floating values or the decimal points are not constant it's varing so let's limit it to two decimal places what you can do is Select both the columns and then go to the transform tab in the transform tab you have here rounding I click on this drop- down and select round now here I'll give my decimal places as two if I click on okay you can see here both the columns the product cost and the product price column have been rounded up to two decimal places I can scroll down you can see all the values have been rounded up to two decimal places all right now let's say you want to add a column called discount price column by multiplying 0.9 to the product price column and let's say you also want to round that new column to two decimal places so how to do it so we want to give a discount of 10% so I'll select this product price column go to add column and then I'll choose custom column here we'll write a formula so I'll give my new column name as discount price and my formula would be I'll select product price I'll click on insert and then I'll multiply this product price by 0.9 so this will give me my 10% discount on the product price and click on okay you see here we have a new column added which is Discount price so if the product price is 34.99 if you give a 10% discount it's 31.4914 now the next question was to change the or round the decimal places to two so I'll go to the transform tab under rounding I'll click on round let's say I'll give two and click on okay so even this has rounded the discount price column to two decimal places now we are done with our mathematical operation on this product data set so we saw how to find some average count how to round of values how to add a new custom column so let's go ahead and close it and load it onto Excel you see here we have our new product table added here and the last column if you see it's the discount column that we added okay now it's time for us to explore another feature so we'll use a table called Adventure calendar table which has basically a date column and let's see how using power query you can prepare that data as well and make some manipulations some calculations so let me go ahead and import Adventure Works calendars table again this data set is provided by Microsoft so I'll go to my new sheet and I'll go to the data tab click on get data and from here I'll click on from text SL CSV and you can see I have my calendar table here I click on import now this will open in the query editor you see here this data set has only one column which is a date column so let's click on transform data okay now if you see here the first row is actually the column name so let's do the transformation here let's push this as the column name name so I'll go to the Home tab and under Home tab you have this option called use first row as headers I'll select this you see here we have the column name as date now before making any operation let's see if all the fields are available you see here if I click on the date drop down year month quarter week day day everything has has been shaded out and I can't access this the reason is the date settings are not correct so we'll change the regional settings let me show you how to do it so we'll go to file and I'll select options and settings and go to query Options under query options we have something called as Regional settings here under Regional settings we'll select English United States instead of English India so I'll just scroll down and here we have English United States and I click on okay now once this is done I'll go to my data type and I'll select date as my data type I'll choose replace current okay now you can see my date column has been formatted if I go to the date tab you can see I have access to all this now let's do some operations on this date colum let's say you want to find the earliest and the latest date from aw calendar table or the adventure Works calendar table so what you can do is Select this column go to date drop down under transform and here we have the option of earliest and latest so if I click on earliest this will show you the last date which was 1st of January 2015 let me close this similarly you can see the most recent date I'll go to the date drop down and click on latest and you have 30 June 2017 as the latest date I'll close this now let's add a new column say d name start of the week and others so I'll select my date column go to add column here under add column I have my option to select the day name the week name and others so I'll click on this date dropdown and under D let's say I want to choose name of D this Returns the D name similarly let's say I'll want to find out the start of the week you can see we have the start of the week column or start of week column now one thing to notice here is in this power query editor the week starts on a Sunday now suppose you want to start your week on a Monday you can do some Transformations on this formula tab so here in the formula bar you can add a one here to make sure your week starts on a Monday so if I hit enter you will see all this values will change you can see it's 28 4 and 11 this will become 29 5 and 12 let's hit enter you can see it here your start of the week is on a Monday now there's another method to make sure your week starts on a Monday let's cancel this step okay I have to insert it once again so I'll go to date and here I'll choose start of week now the week starts on a Sunday so one more method is to add day dot Monday you can see automatically power query is giving me a suggestion so I'll hit tab to finish it and I'll hit enter now you can see the values have changed and our week starts on a Monday now let's say we want to add a few more columns like start of month name of month start of year and the year value so you can just click on the date column or select the date column go to the transform tab okay not the transform tab let's go to the add column Tab and here let's say I'll choose year so we have all the year values and similarly let's say I want to know the start of the year now you can do a few more Transformations let's say I want to know the month now here it gives us one which means January that's February March and so on and so forth let's say we'll do one more I'll click on this go to my date drop down and here I'll choose let's say I want to know the day of year all right now we are done with all our transformation and preparing our data on a date field so let me just go to the Home tab I'll click on close and load now this will load the data set onto Excel you can see it here we have the data ready now you can use this data to make some analysis draw a pivote table draw a pivote chart and do a whole lot of things now while working on a project it is possible that not all your data will be in a single file it could be stored in multiple files so it's important to combine and bring all your data together now we will see how to join your data vertically I have my data present in a CSV folder so let me show you the folder first so this is my CSV folder let me open it and I have some files these are named as project 1 2 3 4 and 5 let me open just one of the project files it has a very small quantity of data you can see it has a month column and an amount column let me close this and let me show it to you again let's open project 4 all the files have the same number of columns you can see this also has a month column and an amount column now we'll combine all this data together and load it into Excel let's see how to do it so I'll open a new sheet and I'll go to my data Tab and click on get data then I'll go to from file and this time I'll choose from folder now this will ask me to give the folder location or the path location where my CSV files are so I'll choose browse and here you can see I have my CSV file I'll just double click on it and click on open now it has selected my file path or the folder path then I'll click on okay you can see these are the files you have project one. XLS project 2. XLS and so on I'll click on this combine drop down and I'll hit combine and transform now this will give you the preview of one of the data files you will see it now you can see it here it says sheet one okay now it's processing all the data files present in that CSV folder and this will be uploaded onto our power query editor now you can see here on the power query editor all my data files have been combined vertically here you can see the month column on the extreme right it's the amount column and to the extreme left we have the source name or the source file where the data came from so first is Project one then we have project two if I scroll down we have project three files project 4 and similarly we have project 5 now this is one way in which you can mge your tables vertically so we are done with it let's just go to the Home tab and click on close and load all the Transformations that were applied you can see it here we have our final table and we have successfully combined five Excel files again you can also join your data horizontally this would be like an SQL join where the data is present in multiple files or sheets based on a common key column you can join the tables so you can perform a left join a right join an inner join based on the problem that you are trying to solve so let's merge two tables based on a column I'll show the data set first it's an Excel file which has three worksheets so here is my Excel file which we'll be using to merge our data horizontally you can see there are three worksheets the first one is year 11 which has data regarding the student name the gender of the student and the course the student had opted for similarly we have another for 2012 or year 12 we have the student name gender as well as the course and finally we have a courses table or a courses sheet which has all the details regarding the course so we have the course name the teacher who teachers or teacher who taught that course we have the lesson type the number of credits and the assignment type now we'll use this data sets to load it onto Excel using power query so I am on my Excel sheet let's just open a new sheet I'll go to the data Tab and here I'll click on get data and this time I'll choose from Excel workbook and I'll select my file which is students and courses I click on import so we'll just see how to import two tables and join them horizontally so let me first select the courses sheet and I'll click on transform data now this has loaded the courses table onto powerquery editor the table looks fine we have the course column teacher everything is fine now let's load one more table and then we'll merge it so here under the Home tab you have a section called new source I'll click on file I'll again select my students and courses and click on import and this time let's choose another table let's say year 11 which has the column name as student gender and course I click on okay it has successfully loaded this table onto power query editor now if you say this the first row is actually are column names so I'll go to the Home tab and select use first row as headers so this will push the first row to the column names you can see we have done it successfully we have my student name the gender column and the course column now let's merge it so if you see here in the Home tab we have a section called merge queries let's click on this drop down and select merge queries here I have my table which is year 11 and let's choose one more table that is courses now the kind kind of join I'll choose as left outer join which means it will take all the rows from the first table and matching records from the second table here I need to select the common key column so if you see both the tables we have the course column as the common key column so I'll select this and now you can see there's a tick mark which means it has selected the rows and the columns successfully you can see it says the selection matches 175 or 175 rows from the first table and let's click on okay let's just expand this and click on okay if I scroll to the right you can see I have successfully merged both the tables now if you want you can remove unnecessary rows or columns suppose if you see I have the code column from the year 11 table and here also I have the course column now this is redundant let's just remove one of the columns I'll just select this column I'll go to remove columns and I'll select remove columns okay the rest looks fine we have successfully merged both the tables by using a left outer join let's just click close And load now this will take some time to load our data onto Excel you can see it here we have successfully loaded it onto Excel now we are done with our demo part now let's just see what all we have done in our demo so I'll go to my first sheet here you can see we had imported a simple text file first and then we plotted a graph which is a pivote chart then you saw how to upload a file from the web so here we imported a UA Champions League table which was present on Wikipedia and then we plotted some graphs and charts you can see we made some analysis using pivot tables and then we imported another web file which was based on a population data and we used the unpivot option or the unpivot feature in the power query to reduce the number of columns then we made some calculations to our customer table which was from Adventure works then we used another table called the products table here we saw some statistical calculations and we added a column called discount price where we used conditional operations and then you saw how we manipulated a date column and then we saw how to append and merge multiple tables as we speak you might wonder what it takes to become an expert data analyst if you are an aspiring data analyst with at least one year of domain experience and looking for online training and certifications from prestigious universities and in collaboration with leading experts then search no more ours postgraduate program and data analysis from P University in collaboration with IBM should be your right choice for more details on this course head straight to our homepage and type in postgraduate program and data analysis or simply click on the link in the description box below with that in mind over to our training experts let's begin by understanding what is a pivot table a pivote table is a tool that summarizes and reorganizes selected columns and rows of data in a spreadsheet to obtain a desired report it does not actually change the spreadsheet data it simply pivots or turns the data to view it in different perspectives pivot tables are specially useful with large amounts of data that would be timec consuming to calculate manually now let's understand the different components of a pivot table so there are four main components first we have rows when a field is chosen for the row area it populates as the first column in the pivot table similar to The Columns all row labels are unique values and duplicates are removed columns is the second component when a field is chosen for the column area only the unique values of the field are listed across the top then we have values each value is kept in a pivot table cell and displace the summarized information the most common values are sum average minimum and maximum finally we have filters filters apply a calculation or restriction to the entire table so let's jump over to Microsoft Excel and let me show you the data set that we will use in this demo so with India being ready for its 16th census in 20121 that is next year it is a good time for us to analyze India's last census data from 2011 and see where different states and cities across India stood in terms of population literacy and other socioeconomic factors we will analyze this data by creating different pivot tables in Excel and explore some of its features so let's begin first I'll show you one of the features that Excel offers us so suppose I click on any cell and hit control+ Q you can see our entire table is selected and at the right bottom there's an option of quick analysis now you can see by default Excel has prompted certain features such as formatting we have charts totals and there's one more called tables now Excel by default has created some pivot tables for us now the first one you say sum of district code by state names next we have sum of sex ratio by state name then we have sum of child sex ratio some of male graduates and some of female graduates by state name and there are others before creating our pivote table so let's have a final look at our data set so First Column you see is the city column so there are different cities from different parts of India then we have the state code we have the state name district code we have the total population followed by male and female population next you can see we have the total literates from each City then we have the male and female literates next we have the sex ratio then we have the child sex ratio next we have total number of graduates and finally you can see we have male and female graduates so using this table we'll create several P tables now first of all let's create a table to find the total population for each state and sort it in descending order so you can see here we have the problem statement so our first P table will have the total population for each of the states in descending order so to create a peot table you can click any cell in your data go to the insert Tab and here left you can see we have the option to create a pivote table so let me select pivote table now my range is already selected the entire table and here I'll choose existing worksheet because I want to place my pivote table in the same worksheet and I'll give my location I'll point to cell Q5 now let me click okay you can see the P table Fields appears here on the right now since we want to find the total population for each state so what I'll do is I'll drag my state name onto rows so here in our P table you can see we have the different state names listed now we want the total population for each of these states so in the field list I'll search for total population which is this one and drag it under values you can see we have a sum of total population for each of these states by default Excel will sum any numeric column you can always change it to average minimum maximum anything you want now we want to sort this column in descending order so I right click go to sort option and choose Zed to a that is largest to smallest you can see here in 2011 Maharashtra had the highest number of population or the total population in Maharashtra was the highest then it was utar Pradesh we had Andra Pradesh and if I come down we have nagaland and andman and nicobar Islands towards the end so this is a simple peot table that we created now the next problem we have is we want to find the total sum of lats in each City belonging to a certain state so let's see how to do it I'll click on any cell go to insert and here I can click on pivot table my range is selected I'll choose existing worksheet and give my location which is Q5 I click on okay now here we want to find the total sum of literates so what I'll do is first let me drag total literates column to values you have the total sum of literates from all the states next I want to see the sum of total literates based on states and cities so let me first drag state name onto rows and then we'll draag City on two rows you can see here we have our P table ready to the left of the pivote table you can see we have the state names and the cities per state and on the right you can see the total number of literates from each City if I scroll down we have Assam then you can see we have Bihar and if I keep scrolling we have all the states harana himanchal prades there Jammu and Kashmir which has now become a union territory we have jarand Karnataka and other states as well moving on okay so the next thing we want to see is what is the average sex ratio and the child sex ratio for each state with that we also want to find the states that had the highest and lowest sex ratio in 2011 so let's create a p table for this I'll click on any cell go to insert choose peot table click on existing worksheet I'll select cell Q5 and click on okay now since we want the average sex ratio and the child sex ratio so first I'll drag those columns either you can manually scroll and drag it or here you have the option to search for it so if I look for child you can see we have the same column listed can just drag it from there let me delete this and I also want the sex ratio so I'll place it on top of child sex ratio next we want to see it based on different states so what I'll do is I'll take state name and put it under rows so here you can see we have our P table ready on the left you can see we have the different state names listed and on the right we we have the values now we want to find the average Now by default Excel will sum the numeric columns you can see it tells you sum of sex ratio and child sex ratio so what you can do can click on this drop down and go to Value field settings and here summarize values by you can choose average you can see the custom name it says average of sex ratio click on okay okay our entire column is now giving us the average sex ratio similarly for this column let me convert it into average I'll again click on the drop- down go to Value field settings click on average and click okay and you can see here we have the average of child sex ratio for each of the states now the next question says which states had the highest lowest sex ratio so we'll consider this column so we'll sort it in any order you want you can do it either ascending or descending let me sort it in descending order you can see we have our column sorted now so in 2011 Kera had the highest sex ratio and if I scroll down to the bottom you can see himanchal Pradesh had the lowest which is around 8818 up next let's explore one more feature of peot table so suppose you want to see the top or bottom few rows of a peot table you can do that as well so here we have a question at hand we want to find the top three cities with the highest number of female graduates so let's see from the entire pivote table how we can filter the top three cities so I'll go to insert click on the P table option go to existing worksheet click on Q5 and hit okay now since we want to find the top three cities I'll drag City column onto rows and then we want the female graduates so in the search bar I'll look for female and I'll choose this column that is female graduates and drag it here onto values so I have the sum of female graduates for each of the Cities now since we want to find the highest number of female graduates in the top three cities so let me first sort this column I'll sort it in descending order now we have it sorted now from this you can see that Delhi greater Mumbai and bangaluru are the top three cities but it's displaying all the cities for us so let's filter only the top three so what you can do is right click and go to filter filter under filter you have the option of top 10 I'll select this here I only want the top three so either you can go down like this or you can directly type three your column is already selected let me just click on okay there you go we have the required pivote table ready and it only displays as the top three cities with the highest number of female graduates now the next thing we want to see is how to use a Slicer in a peot table so we have a question here What's the total population for all the cities in Rajasthan and Karnataka so let's create a p table for this and see how you can use a slicer to filter the table click on existing I'll click on a location this time q6 click okay now since I want the total population so I'll drag total population onto values and then I'll select the city onto row and then the state name also I'll place it on top of City so you have in the pivote table all the states and their cities and on the right you can see the total population for each of these cities but our question is we want to find only for Rajasthan and Karnataka now for that what you can do is go to insert and create a slicer either you can create from this option or you can go to pivote table analyze option and here you have the option to create or insert a slicer I click on this and since we want to slice the table based on state that is Rajasthan and Karnataka I'll choose state name as my slicer field you can see okay this is my slicer here now you only want the data for Rajasthan and Karnataka so I'll search for these two so here we have Karnataka so let me select Karnataka first and I also want for Rajasthan so let me select Rajasthan also you can see in our pivot table we only have data for Rajasthan and Karnataka so this pivot table shows you different cities from Karnataka and the total sum of population from each of the cities and similarly we also have for Rajasthan moving ahead now we will see another very interesting feature of pivot that is how you can create percentage contribution of a table for example we have a question here what's the percentage contribution of male and female literates from each state now we want to see in terms of percentage and not as sum or average let's do that I'll create my pivote table click on existing and I'll select an empty cell okay now here since we want to find the percentage contribution of male and female literates so first I'll drag male lit onto values followed by female literates onto values by default it has summed up the male literat and female litat value and also I want to drag State column to row so here you can see the sum of male literates and female literates per state I want to convert this as percentage contribution so what we can do is I'll select any cell and I'll right click and I'll go to so value as and here I have the option to select percentage of grand total so I'll select this you can see we have the percentage contribution of male literates through the total now if I sort this you will get to know which state contributed or has the highest percentage contribution so so we have Maharashtra for male literates then we had utar Pradesh in 2011 if I come down we had migala nagaland and andan and nicobar Islands as two states which had little or minimal contribution to male literates similarly let's do it for female literates I'll go to so value as and select percentage of grand total so you can see here also Maharashtra UT prades then Gujarat and all had the highest percentage contribution to female literates so this is another good feature to convert your data and seat and terms of percentage now moving ahead let's say we want to find the bottom three cities from each state that had the lowest female graduates we can do that as well I'll go to insert click on pivot go to existing worksheet select an empty worksheet and click on okay now since I want to see based on States as well as cities so let me drag the state name first onto rows and let's drag the city column onto rows next we want female graduates so let me look for female graduates in the field list I'll drag it onto values now we have the list of states and the respective cities and to the right of the P table you can see the sum of female graduates from each City now first I'll sort this column I'll right click go to sort and click on sest to largest now we have sorted our female graduates from shortest or smallest to largest now since I want to find the B three cities from each state I'll come to the cell right click go to filter and select top 10 now I'll replace top 10 with bottom and I want the bottom three cities from each state I have my column selected that is some of female graduates if I click on okay you can see here some of the states don't have three cities so you can see andan and nicobar islands has only one city that is Port player while the remaining you can find the bottom three cities with the lowest number of female graduates so Andra Pradesh had these three in Assam we had Naga then there was tiar and Sila similarly if I come down in harana we have pwal kathal and zind if I come further here you can see for Karnataka this gangavati this ranii benur and this scholar similarly you can see for carala as well now moving ahead now in the next example I'll tell you how you can create a calculated field or a calculated column in Excel with the help of a pivote table so in a pivot table you can create or use custom formulas to create calculated fields or items calculated fields are formulas that can refer to other fields in the P table calculated Fields appear with other value fields in the pivot table like other value fields a calculated Fields name May proceed with Su of followed by the field name so here we have a sales table that has columns like the items which has different fruits and vegetables and those have been categorized as fruits and vegetables we have the price per kg and this is in terms of rupees and we have the quantity that was sold now let's see if you want to find the sales for each item in the T you can create a calculated field so your sales column is going to be the product of price per kg and quantity so let me show you how you can do that with the help of a pivote table I'll create a pivote table first click on an empty cell hit okay now if you see on the top under pivot table analyze and under calculations we have the option Fields items and sets if I click on this drop down I get the option to create a calculated field or insert a calculated field I click on this I'll give my field name as sales and I'll select my formula I'll first click on price per kg and hit insert field I'll give a space hit shift 8 to give the product symbol and then I'll double click on quantity now this is my formula for sales that is price per kg multiplied by quantity I click on ADD and I click on okay if you see here there's a calculated field that is present in the pivote table fields which is Sals but it did not added to our original table our original table is the same but here we have added a calculated field which is present only in the pivote table list now we can use this it has already taken it under values now let's say I want to find the sum of seals for each item under each category you can seat here we have our category fruit and we have our category vegetable and under that we have different items like apple apricot banana similarly in vegetables we have broccoli the carrots corn egg plant and others so this is how you can create a calculated field in a pivot table table now this's one more good feature that Excel offers us in pivote table is to create a pivote chart so you can use your pivote table and create different charts so I'll show you how to do that if I go to insert here I have the option of recommended charts I click on this Excel gives me some default charts which you can use let's say I'll select this let me drag it a bit to the right here you can see I'll close this pivote field list this is a nice bar chart that Excel has created this is called a pivote chart now here you can see the category fruits and vegetables and the different fruits and vegetables or the items in the y axis you can see the total seals if you see from the graph guava meet the highest amount of Sals now if I sort this let's sort this first you can see it here fruit guava meet the highest amount of sales now since I sorted and changed my P table the P chart also automatically gets updated similarly there are other charts also that you can create let's go to the insert Tab and let's click on recommended charts again let's look for a pie chart so this is a pie chart that you can create let me click C on okay so here is our PI chart and each Pi represents a certain item and the pi that has the highest area represents it had the highest amount of seals in this case you can see it is quava and similarly we have other items as well this is fruit banana that's corn and we have spinach and others let's explore a few other charts so first I'll click on my pivote table go to insert and under recommended charts let's now select a line chart if I hit okay move it to the right so this is a line chart you can see it starts from guava which had the highest amount of sales then it drops and in the x-axis you can see the different items similarly when it starts with the vegetables broli made the highest amount of sales with 2 2,800 rupe and our lowest was eggplant at 900 rupees for fruits papaya sold the least at 700 rupees let's take another chart I'll go to insert under recommended charts let's see this time we'll see a bar chart now this is a horizontal bar chart and not a vertical one we just saw a vertical column chart like this this is an horizontal bar chart now you can always increase and decrease the size of these charts let's explore a last chart let's take the area chart for now so this is an area chart again it looks similar to the line chart it starts with guava which had the highest amount of sales similarly papaya under fruits had the lowest amount of sales under vegetables it was broccoli and finally eggplant made the lowest amount of sales under vegetable now let's go to our first sheet and summarize what we have done in this demo for pivote Tes in Excel so we had our data this is a 2011 Census Data from India we had the different cities the state names and we had the total population total literates female literates male literates we had the sex ratio total graduates and other information so we began by understanding how to create a simple pivote table where we calculated the total population for each state and sorted it in descending order we found that Maharashtra utar Pradesh had the total population in 2011 then we saw another preot table where we calculated the total sum of literates in each City belonging to a certain state so you can see we had the different state names and the cities under each state then we saw another feature where you could calculate the average of a certain numerical column so here we calculated the average sex ratio and the child sex ratio for each state and found out which one had the highest and lowest sex ratio after that we saw how you could find or filter tables we saw how to find the top three cities with the highest number of female graduates we found out the Delhi greater Mumbai and bangaluru with the top cities with highest number of female graduates next we saw how to use Slicer in a poo table so we sliced our table based on Rajasthan and Karnataka State and saw the total population for all the cities in Rajasthan and Karnataka in the next sheet we explored another feature that was to find the percentage contribution of male and female rates from each state the then here we saw how to find out the bottom three cities for each state having lest female graduates one thing marked that some of the states did not have three cities for example Andaman had only one city that was sport player but the others we found out the bottom three cities that had the lowest female graduates finally we looked at how to create a calculated field in a pivote table so we saw how how to create a calculated field called seals and then we explored how to create different charts and graphs so this was an area chart that we saw this a column chart we also saw or looked at a bar chart that was a horizontal bar chart similarly we saw how to create a pie chart as well and with that we have reached to the end of this session on Excel tutoring fill C should you need any assistance PPT or any other resources like data sets Etc please do let us know in the comment section below and our team of experts we'll be happy to help you as soon as possible until next time thank you
No comments:
Post a Comment