Advanced Excel tutorial | Excel Training | Edureka Rewind

Key Takeaways

it's really nice to manage huge data with just a few Mouse clicks and Excel is definitely one tool that will allow you to do this so in case you're unaware of the magical tricks of excel here is vaja from edureka with this Advanced msxl tutorial wherein you will be learning all the advanced topics in Excel so the first topic of discussion will be Security in Excel followed by themes templates Excel Graphics document printing options data tables in Excel graphs or charts to visualize your data pivot tables and pivot charts data validation filtering sorting cross referencing using vlookup and language translation so moving on towards the first topic of the session which is security security is one aspect that we need in every application that we use Excel provides three levels of security that is file level security worksheet level security and workbook level security file level security refers to securing your Excel file by making use of password so as to prevent others from opening and modifying your file so now to show you guys how to enable this file level security I'll jump on to Excel and over here from the ribbon tab I'll click on file and go to info from this protect workbook drop- down list I'll select the option of encrypt with password so as you can see over here Excel opens up a dialogue box wherein you'll have to enter a password so I'll just enter some random password and then I'll click on okay reenter the password as asked and click on okay so as you can see over here it says that a password is required to open up this workbook now what I'm going to do is just save this and I'll close this file so now if I try to access this file Excel will ask me to enter a password so I'll just open the file again so as you can see over here it says I need a password in order to open this file so I'll just enter the password that I had specified before and I'll click on okay okay so as you can see over here the file is now open so there are a few things that you guys have to remember when it comes to passwords do not forget your password as there's no password recovery available in Excel no restrictions are levied but Excel passwords are case sensitive avoid Distributing password protected files with sensitive information such as Bank details and protecting a file with a password will not necessarily protect malicious activities avoid sharing your passwords so now moving on towards the next level security which is worksheet level to protect the data present in a worksheet from being modified you can lock the cells and protect the worksheet not just this you can also selectively allow or disallow access to particular cells of your sheet to various users say for for example you have a sheet that contains the details for sales of different products and every product is handled by different individuals you can allow each sales staff to modify the details of only that product which he's responsible for and not of the others now to do this all you have to do is first unlock the cells that can be edited by the users and then protect the sheet so for the first step all you have to do is select the cells and then open up this font window click on protection uncheck the locked option then click on okay now from the review tab select protect sheet option from the given list over here check all the options that you can allow users to manipulate and optionally you can also enter a password over here so I'll just give a password and then I'll click on okay okay so I'll have to reenter the password so as you can see over here the command has changed from protect sheet to unprotect sheet which means the cells of this sheet have been protected okay now in case I have to unpredict this sheet all I have to do is click on this option and enter the password that I just gave remember guys that if you have not specified any password any of the users can unprotect the sheet by just clicking on this option so I hope you guys are clear with this so now moving on towards the third level that is workbook level security workbook level security will help prevent other users from adding deleting hiding or renaming your sheets so just to show this to you guys I'll just add on one more sheet over here and then from the review tab I'll click on this protect workbook option so as you can see on the screen a dialogue box has opened for me to enter a password also it's mentioned that this is optional so if you do not specify any password any user who wants to unprotect the sheet will just have to click on the protect workbook command again okay so I'll just enter a password and click on okay so as you can see over here I've protected my sheet and when I right click on any of these sheets all the options such as insert delete rename move or copy tab color height and unhide have been unselected which means I cannot do this anymore also you can see that this project workbook command has been highlighted which means that this workbook has been protected now in case you have to unprotect this workbook all you have to do is click on this command and reenter the password that you've just given so I hope you guys are clear with this so now moving on towards the next topic of this session which is msxl themes Excel provides a number of document themes to help you create formal documents using these themes it will be very easy for you to harmonize different fonts colors or Graphics you also have an option to either change the complete theme or just the colors fonts Etc according to your choice so using the msxl themes you can make use of standard colored themes create your team modify the fonts of themes change effects and also save your customized themes so now let's get back to Excel and see how this works themes will be present in this page layout tab so I'll click on page layout and from here to change the color of this theme I'll just open up the colors drop down list and I'll select some different color as you can see over here the color of this chart has changed now in case you want to customize your own colors all you have to do is open this drop- down list and click on this customize colors option from here you can choose any color of your choice so as you can see all the changes you make will be reflected over here so in case I want to change the color of the links I'll change it to Red so I hope you can see this the color of the links over here has changed so in case I have any links in my Excel sheet it will be reflected in this red color not just this you also have an option called as more colors using which you can customize your own colors once you're done with that just click on okay and you will see the changes are reflected in this sample pane over here Excel also allows you to save your te theme so just give some name to your theme I'll say theme one and I'll click on save so I hope you guys are clear with this so now coming towards fonts just like how you could change the theme colors Excel also allows you to change the fonts now to do this just go to the page layout tab again and open up the fonts dropdown list from here you can choose any font of your choice not just this you can also customize your own fonts for the headings and the body I'll choose some random fonts over here and then I'll save it with some name I'll say font style one and I'll click on Save okay so as you can see on the screen the fonts have changed accordingly apart from all this Excel also provides a huge set of theme effects such as lines Shadows Reflections Etc that you can add on to add effects all you have to do is open up this effects drop down list present in page layout and choose any effect of your choice so one final thing that I want to tell you guys is you can also save your customized theme to save your current theme click on the themes list over here and then click on the save current theme option please make a note that the theme you save will be saved in the document themes folder of your local Drive in the thmx format once you're done with that just click on save for now I'll just cancel this so the next time you'll have to make use of the theme you'll have to just go to the local folder and choose the theme of your choice so now moving on towards the next topic of the session which is templates a template in general is a pattern or a model that forms the base for something Excel templates help you increase your production rates as they help you save time and effort to create your documents now in order to make use of the Excel templates all you have to do is click on file and over here choose new okay as you scroll down you can see that Excel provides a number of templates such as the investment tracker the seasonal photo calendar Etc you can choose any template of your choice and you will see that your workbook will be of that pattern so now moving on towards the next topic which is Graphics unlike what many people think Excel does not just allow you to play around with data but also allows you to add graphics to it so to add graphics to your Excel sheet all you have to do is just click on this insert Tab and from here you can choose any graphic of your choice so let me just go to illustrations and choose some shapes so I'll draw a square over here so as you can see over here I've added a shape to my Excel sheet in case I want to format this shape all I have to do is select the shape and from here I can choose any fill color that I want any outline so for now I'll just say no outline and let me give some shape effect so as you can see over here I have a shadow I've changed the color of this and also I've removed the outline of this shape so there are many more Graphics that you guys can check out for yourself so similarly you can also add in images icons smart Arts Etc to your documents so now moving on towards the next topic of this session which is printing options Excel sheets provide a number of options to take appropriate printouts using these options you can selectively print your sheet in various ways now to open up the sheet options pan all you have to do is click on the page layout tab and from here open the page setup window from here click on sheet and you will see a number of options that are available to you the first option that is print area will allow you to set the area that you need to print the second option will allow you to set the rows and columns title to appear at the top and towards the left respectively when you check the grid lines option grid lines will be added to the print print out black and white will print the document as black and white or in monochrome the draft quality option will allow you to print the sheet using the printer's draft quality and the row and column headings will allow you to print the row and column headings so the next area deals with the page order down then over will print the down Pages first followed by the right pages and over then down will print the right Pages first and then the down Pages not just this the page setup tab also has margins the unprinted regions along the top down and the left right sides are referred to as margins all msxl pages have a border and if you have selected some border for one page then the Border will be applied to all the pages that is you cannot have different margins for each page so as you can see over here you can also configure the margin values so now coming towards the headers and Footers headers and Footers are used to provide some information at the top and at the bottom of a page a new workbook does not have a header or a footer in order to add it all you have have to do is open up this pan present in the page setup window and over here you have a number of options to customize the headers and Footers once you're done with this all you have to do is click on the print preview option and you'll be able to see how your page will look like when you print it so as you can see over here there are many more options that Excel provides such as the page orientation which will be landscape or portrait the portrait orientation is default and prints the page tler than wide on the other hand the landscape orientation prints the sheet wider than tall selectively you can also add page breaks to your printouts so to know more about the Excel printing options you can check out eda's Excel tutorial video that is available on its YouTube channel okay so now let's move on towards the next very important topic which is data tables data tables in Excel are created to experiment with different values for a formula you can create both one or two variable data tables in Excel data tables are one of the three types of whatif analysis tools available in Excel so in this Advanced tutorial I'll be showing you guys how to create both one variable and two variable data tables so as you can see on the screen I've already prepared some data here I have purchased 16 glasses at the rate of 20 each so this way I'll have to pay a total of $320 for 16 glasses now in case I want to create a one variable data table that will show me the prices for different quantities all I have to do is copy the value present in B3 to some other cell so I'll just say is equal to B3 so as you can see over here the value has been copied now what I'm going to do is write down different quantities for this item so I'll start with 16 17 I'll just copy the series guys and now I'll select this newly created range click on the data tab and then from the wat if analysis tools I'm going to choose data table as you can see over here a new window has opened wherein it's asking me to enter the row input cell and the column input cell since I do not have any row input cells I'll specify the input to be as a column input cell since I'm dealing with quantities over here I'll have to specify the target cell to be B2 and then I'll click on okay so as you can see over here I have a data table that has been created for different quantities at the rate of 20 per unit so there's one thing that's missing over here which is the dollar sign now to add a dollar sign what I'm going to do is select all these cells and then from the Home tab and the numbers group I'll click on the dollar symbol okay so I hope you guys are clear with this so now moving on towards a two variable data table just like how we created a one variable data table we can also create a two variable data table so now to create the two variable data cell what I'm going to do is copy this value again and for the rows I'll specify some quantities again and for the columns I'll specify different price ranges so once this is done I'll have to select the range just like before click on the data Tab and from the watf analysis tools select data table so now since I have both row input as well as column input my row input Target set will be B1 and the column input Target cell will be B2 and now I'll click on okay so as you can see over here a data table has been created for two variable values now to add the dollar sign I'll just select all these cells click on the Home tab and select the dollar symbol so I hope you guys are clear with this so now moving on towards the next topic which is charts charts basically give graphical representation to your data these charts can visualize numerical values in a very meaningful and easy to understand manner charts are a very essential part of Excel and they improved greatly with every new version of msxl so as you can see on the screen I already have a table for which I want to create a chart okay so all I'm going to do is Select this table I'll open up the charts pan and from all charts I'll select the column chart let me also change the design of this so now I'll click on okay so as you can see over here a chart has been prepared for this table so Excel will also allow you to filter this chart according to your preferences so in case I want to see only the amount and not the quantity all I have to do is just unselect this box also I can selectively configure my charts for particular recordings not just this you can further configure your charts by adding in different types of charts and giving it different colors so as you can see over here all the changes I'm making over here are reflected in this chart so I I hope you guys are clear with this so now moving on towards the next topic of this session which is Pivot tables and pivot charts in Excel a pivot table in Excel is a statistical table that condenses the data of those tables that have extensive information these tables help you visualize your data based on any of the fields present in your data table using these pivot tables you can visualize your data by changing the fields rows and columns add filters sort your data Etc so to create a pivot table all you have to do is select the data for which you intend to create a pivot table click on the insert Tab and from here click on pivot tables so as you can see over here it shows me the selected range and also it gives me an option to create the pivot table in a new worksheet or in the existing worksheet I'll create it in the existing worksheet and I'll just give some Target cell so as you can see over here an empty pivot table has been created also the pivot tables field window has has opened which contains all the fields that are available in my table over here now in case I want to add any of these fields to the columns rows values and filters all I have to do is drag and drop it in that area so what I'm going to do is add in the amounts and values I'll keep city as the filters name of the vendors in rows and the name of the item as columns so as you can see over here a pivot table has been created accordingly so in case I want to Interchange between the row labels and the column labels all I have to do is drag and drop those fields in the required places okay so as you can see over here my pivot table changes when I change these fields also in case you want to see any other figures other than the sum all you have to do is go to the value field settings and choose any of the options that available over here so let me just change it from sum to count and I'll click on okay okay so I hope everyone's clear with this let me just change it back to some again okay and then I'll click on okay so now in case I want to create a pivot chart for this pivot table all I have to do is click on this insert Tab and from the charts group I'll click on pivot chart so as you can see over here Excel provides a number of pivot charts for me I'll choose the basic type itself and I'll choose a different style in this and then I'll click on okay so as you can see over here a pivot chart has been created for me also make a note guys that the pivot chart created over here has a drop- down list for every field that is present in my pivot table to know more about pivot tables and pivot charts in Excel you can check out the pivot tables video of edureka which is available on edureka's YouTube channel so now moving on towards the next topic which is data validation one of the most important aspects of excel is data validation this feature as the name suggests allows you to configure the cells of an Excel worksheet to accept some particular type of data say for example you want want certain cells in your sheet to accept only dates you can do this easily using the data validation feature of excel so what I'm going to do is open up a new sheet so what I'm going to do over here is select a few CES and I'll make this specific to accept only one type of data now to do that I'll have to go to this data Tab and from the data tools I'll have to select data validation so as you can see over here I have a number of options so let me just choose whole numbers and I'll say whole numbers that are greater than one so apart from settings in this data validation window you have two other options that is input message and error alert now this input message section will allow you to enter a message for the user giving him some details regarding the acceptable data so here I'll just type in message as the title and I'll say enter whole numbers greater than one okay let me just copy this and I'll tell you why and I'll paste the same as an error message and with little modification I'll just say please over here okay the error message section will basically inform the user that he has made some mistake in giving the desired input so let me just give the title as caution and I'll click on okay so now if you select any of these cells you will first see a message asking the user to enter whole numbers that are greater than one so in case if I enter a value say 10 it has accepted this value now let me enter a different value and see what happens so now let me enter some decimal value and see what happens so I'll say 2.1 as you can see over here a new window has opened with caution as its title and it says please enter whole numbers that are greater than one so I hope you guys are clear with this so now moving on towards the next topic which is data filtering filtering data refers to fetching some particular data that meets some given criteria so as you can see on the screen I've already prepared a table now in case I want to filter out data only for Washington City all I have to do is Select this column and from this data tab I'll have to select this filter option so as you can see over here once I've selected this option I have a drop- down list that has been created over here when I open this drop- down list you can see over here that all the cities have been selected so I'll just unselect this and I'll just select Washington and now I'll click on okay so as you can see the data only for Washington City has been filtered out so here I've applied a filter just to one column in case I want to filter data based on multiple criterias all I have to do is Select those columns and then I'll have to add filters to them I'll filter out the data for New York and the item I'll say should be Apple's so as you can see over here I filtered out data based on the items as well as the city so I'll just undo the changes so now let's move on towards the next topic which is sorting data sorting in Excel refers to arranging the data rows on the basis of the data present in the columns so for example you can arrange the names from a to zed or arrange numbers from ascending and descending orders so for the same table that I've taken previously in case I want to sort the names from a to zed all I have to do is Select this name column and from the data tab I'll have to choose the sort command so as you can see over here it has opened up a window asking me if I want to expand the selection or continue with the current selection if I expand the selection it will select the complete table and if I have to sort based only on the current selection all I have to do is select the second option and click on sort over here next it will open another window where wherein you can add levels delete levels or even copy levels so what I'm going to do is sort from a to zed and the column will be C since I've chosen just one column it's going to show only one column in the drop- down list over here and then I'll click on okay so as you can see over here the names have been arranged from A to Z so there's one mistake that I made over here I shouldn't have chosen the name of this column so I'll just put it back so what I'm going to do is just undo this so I'll just select the SC again and I'll sort it so as you can see over here the changes I made have been reflected on my table so now moving on towards the next very important topic which is cross referencing in Excel in case you want to look for data present in multiple sheets of your workbook you can make use of the vlookup function the vlookup function in Excel can be used to bring forth required data from different spreadsheets as well however if you do not have the knowledge of this V lookup function you can refer to the V lookup in Excel video of edura present on edureka's YouTube channel so as you can see on the screen I've already created a table with the names of employees along with their IDs designations and the salaries however the salary column is not filled and the data for this is present in another sheet so as you can see over here my original table is present in sheet 7 and the data is present in sheet 8 now what I'm going to do is use the vlookup function to fetch the value from sheet 8 to the sheet 7 so over here the first thing that I'm going to do is type in an equal to sign and I'll write down the V lookup function now the first thing that I'll have to do is given a lookup value in the first case the name of the employee is John so I'll write down John followed by the table array now the table array is present in sheet 8 so I'll just go to the sheet 8 and over here I'll select this table once I'm done with this I'll have to specify where the salary is present so in this table salary is present in the third column so I'll specify three and since I want it to be an exact match I'll specify the Boolean value as false just to remind you guys if you do not have an idea of the vlookup function and its syntax please refer to the vlookup an Excel video of edureka present on YouTube so once I'm done with this I'll just execute this function and as you can see over here the salary for John has been retrieved now I'll just have to include a dollar symbol so I'll click on the Home tab and from here I'll select the dollar symbol so as you can see over here the salary of John has been retrieved as ,000 now in case I want to retrieve the values for all the other employees the first thing that I'll have to do is make a small change in this name over here and instead of giving a name I'll give the sell reference as a lookup value so what I'm going to do is just delete this and now I'll specify the lookup value to be as this target cell table array is present in sheet 8 column will be three and it'll be false for an exact match so as you can see over here the value is still retrieved when I specified the cell address as the target lookup value now what I'm going to do is copy this for all the other employees so as you can see over here my table has been completed now now moving on towards a very important topic that is macros macros are a must to be learned in Excel using these macros you can automate the tasks that you perform regularly by just recording them as a macro a macro in Excel is basically an action or a set of them that can be performed again and again automatically so in order to record a macro all you have to do is click on this view Tab and from here select macros from this drop- down list click on record macros macro and give a name to this I'll keep it as macro one also you can specify a shortcut key for this Macro for now I'm not going to give any shortcut key and I'll just click on okay so now Excel is going to record all the tasks that you perform be careful to record the exact tasks so in this table the first thing that I'm going to do is sort the names from a to zed so to do that I have to click on data and select the sort option so now my names have been sorted for the items and cities I'll just add in some filters and I'll filter out the data for say some two fruits apples and bananas cities I'll keep it as New York and Chicago so as you can see over here I sorted the data I filtered the items and the cities so now I'll just create a chart for this so I'll just select this data I'll open up charts and then I'll click on okay so basically the first thing I did was sorting then filtering and then creating a chart for the filtered data so if this is the final task that I have to automate all I have to do is get back to this view Tab and from this macros drop down list I'll have to stop recording you can also see all the macros that you've recorded from this view macros option that is present over here and in case you want to run any of the macros just open the macros pane select any macro that you've already recorded and click on run so I hope everyone's clear till here now moving on towards the last topic of this session which is language translation Excel brilliantly allows users to translate the data into different languages it can autod detect the language present in your data and then convert it into desired languages that is present in excel's list of of languages so now just to show this to you guys what I'm going to do is open up a new sheet and over here I'll just type in hello world now I'll open the review tab from the review tab over here I'll select translate and as you can see a translator window has been opened over here you can see that Excel has detected that hello world is in English and I have a drop- down list for me to select any of the languages that I prefer to see this in let me just choose a few languages and see what happens so when I select Arabic I have Hello World shown in Arabic over here there are a number of languages which you can choose from and whichever language you want to choose just select that option and you will see the required changes made over here so this brings us to the end of this session I hope you guys have enjoyed and learned something new goodbye and take care