10 Amazing (Less Known) Excel Tricks You Should Know

hello and welcome to this excel tips video i'm sumit bunsel and in this video i'm going to show you 10 amazing excel tricks that you can use in your day-to-day work and save a lot of time so let's get started here i have the monthly data for these 100 stores and i want to print this data where i want these headers to repeat on all the printed pages now the fastest way to do this is you simply come here select the first row the one that you want to repeat go to the name box here and type print underscore titles and hit enter and that's it now when you print this data every page would have the first row as the header see what happens when i hold the control key then press the p key to get into the print preview option here you can see this is the first page that would be printed i have the headers here and when i go to the next page i have these headers that repeat if you want to repeat multiple rows simply select those multiple rows and then enter print underscore titles in the name box if you want to repeat a row and a column then select the row select the column then enter print underscore titles here here i have this data that i want to quickly convert into percentages so i want 25 to become 25 and 92 to become 92 now if i select this and i use the traditional method of going to the home tab and clicking on this percent icon here then it is going to convert these into actual percentage values and then i will have to divide this by hundred let me show you a really fast way to do this so in any blank cell enter one percent copy the cell select this range right click go to paste special and then click on multiply and then when you click ok instantly all these numbers would be converted into percentages with the same value that was there in the cell so 25 converted into 25 here i have this data and i want to quickly filter this data and get all the records for projector now the traditional way of doing it would be to first apply the filter and then filter it based on the projector but let me show you a really fast way select any cell that has the word or the item based on which you want to filter the data right click go to filter and then click on filter by selected cells value and as soon as i click on it instantly it applies the filters and it filters based on that cell's value now i'm using microsoft 365 so i'm not sure if this is a new feature because i don't remember using it earlier but try it out if you have this one this trick would be useful for people who need to manually enter the data and their data has decimal point so for example let's say you're entering the price of the products or accounting data and you always have two digits after the decimal point then you can make this data entry a lot faster so let's say i have to enter data such as 100.54 or 321.65 now i have to manually enter the decimal point but if this is the kind of data this consistent format where you have two digits after the decimal point here is how you can make it faster go to the file option click on options in the excel options dialog box that opens up click on the advanced option here in the pane and then check this option automatically insert a decimal point and you can specify how many digits you want after the decimal point so in this case i'll keep it as 2 click on ok now you do not need to manually enter the decimal point simply enter the number and excel would put that decimal point for you so if i enter 1 2 3 4 5 it automatically inserts a decimal before the last two digits if i enter any number here you'll see that excel would take care of placing the decimal for me once you are done with the data entry you can go back and uncheck the same option so that it does not happen later when you are entering the data as is i have this data with the name and the address and i want these different elements to be in a separate line within the same cell so i want the name then the street then the city then the state and the pin code now if i do this manually then that is going to take me a lot of time so let me show you how to quickly do this using find and replace so i select all this data i hold the ctrl key and press the h key which is to open find and replace dialog box and here i'm going to replace the comma with a line break so i enter comma here because i want to find all the commas and i want to replace it with a line break now how do you enter a line break there's a keyboard shortcut for it hold the ctrl key and press the j key now you may not see anything entered in the field in the replace with field but you may see a blinking dot here i don't know if you're able to see this one it's really really tiny but know that it is there because you do not see any cursor you just see a blinking dot now click on replace all and instantly it is going to make all the replacement and your comma has been changed with a line break but i still don't see the entire data here that is because i need to make sure that this data has the wrap property enabled so to do that i would select this data go to the home tab and click on wrap text and when i do that you can see instantly i have all this data right here where each of these elements are now in a separate line so this could be useful if you need to create address labels and you want to print these then you can easily remove comma and replace it with a line break excel has many useful options that are sometimes hidden within these tabs in the ribbon or in options in dropdowns and if you want to quickly access these options then you have to go to that specific option that specific tab and then click on it but what you can also do is you can add these to the quick access toolbar and that gives you access to these options in a single click so here i have this data for the top movies and i want to quickly clear the format i only want the text i do not want the hyperlink or the cell color so the way to do it would be select this data go to the home tab and here in editing group click on clear and then click on clear formats now this is the longer way of doing it let me show you a much faster way while you are here right click on this option and then click on add to quick access toolbar and as soon as you do this you'll see that this option is now added to the quick access toolbar which is always visible so now if you want to clear the format you simply come here select these cells and click on this option now this is fast but there is even better way to do this so let me press ctrl z to get the data now in this case if i want to quickly access this option i do not need to even take my cursor there i can use a keyboard shortcut as soon as i press the alt key you would see these numbers appear in these icons in the quick access toolbar and i can access each of these options by hitting that number so clear format option is seventh in my quick access toolbar and if i want to access it all i need to do is use the keyboard shortcut alt and then seven so let's see from the beginning i come here select this entire range hit the alt key press seven and it's done so this is how you can speed up the process of some of the things that you need to do quite often you can easily add any of these options to the quick access toolbar simply go to the option that you need and click on add to quick access toolbar if you need more options that you cannot find here then you can click here go to more commands and here in the excel options dialog box you get access to more commands here and even more commands if you go to all commands so if you have time just go through this list and you may find some really interesting things that you can do easily with options being added here in the quick access toolbar here i have this large data set and if i want to go through this data set then you'll see that as i scroll to the right or i scroll down then first of all the headers disappear and secondly i have absolutely no idea what data was to the left of the data that i'm currently viewing so the first thing i would do is i would come to this cell which is the top left cell below the headers and to the right of the column header go to the view option here and then click on freeze panes and when i do that you'll see that the headers are now always visible so when i selected this cell anything to the left of this cell and above the cell would always be visible when i go to the right or when i scroll down now this is all right but what if i want to compare the data of day number 10 with data of day number 30. now you can see i cannot have these two day data in the same view of my screen so what i can do is i have the option to split the screen and to do that i would again go back to the view tab and click on split and see what happens as soon as i click on split you would see these dark thick gray lines appear and i have one vertical line and one horizontal line because i want my screen to be divided vertically i would drag this vertical line and place it right next to day number 10. you can place it anywhere and to the horizontal line i would simply because i do not want this i would take this upward so that it goes away so i just have a vertical line but what happens is in this case with this option xl has divided my screen into two different sections two different screens so here you see every screen has its own scroll bar and i can manage these individually so if i want to compare day number 10 with day number 30 i simply bring day number 30 here next to day number 10 and i can compare this data side by side which was earlier not possible i can also make changes here and that change would be reflected in real time here so for example let's say i come here and i make the change and i color it then that is being reflected here in real time because these are actually the same file once you are done with it you go back to the view tab and click on split again and the split panes would go away so this is a really cool method if you're working with large data set and you want to simultaneously view data that are far apart here i have the device data for these 15 stores and i want to show you how to quickly move rows and columns in excel so let's say i want to move day number three and bring it before day number one now the traditional way of doing it would be i would come here insert a column then i would come here copy this data paste it here and then delete this but i don't want to do this i'm going to show you a much much faster way so let me press ctrl z to get the original data back now if i want to move day number three before day number one i would simply select this data you can also select the entire column if you want but in this case i'm going to go with this data i would hold the shift key and then place the cursor at the edge where i have this green outline and when i do that you can see that my cursor changes into this four pointed arrows and now i would hold the mouse left key and drag it to the left and when i see this thick green line in the position where i want this data i would leave the mouse key and as soon as i do this it is going to move my data from here which was column number d to column number b and at the same time there is no copy pasting needed everything has automatically rearranged day number three moved here and rest of the data moved to the right let me show you again how to do this let's say i want to move day number six now at the beginning i select this data hold the shift key bring my cursor to the outline of this selection hold the left mouse key drag it to the left when i see this thick green outline in the position where i want this data i leave the mouse key and instantly day number six has come here you can also move multiple columns if you want so you can select both of these columns let's say i want to take them till the end so i again hold the shift key get the cursor at the outline drag it till the end and leave it there so this is the fastest way to move rows and columns the best thing about this method is that in case you have data here at the bottom or somewhere in the right that data would be unaffected this column rearrangement is only happening within this data set because i only selected this much data so if i select the data and then move it using this method then it would not disturb everything which is around it similarly you can do it for rows if you want to bring store number five row below store number ten again do the same thing drag it down and when you see this horizontal green line leave it there and it would move the row here i have the device data for these stores and i have multiple sheets with similar data so you can see i have the same format of the data so the structure is the same there are 15 stores and 10 days and that same structure is followed in all these sheets now what i want to do is on all these sheets apply some changes which would be let's say applying the bold format to the titles and giving it a color and adding a row where i would have the sum for each of these days now instead of doing it individually for all these sheets i can actually do it in one go and to do that i would have to group these so to do that i would select the first one here hold the shift key and then select the last one in case you have a sheet somewhere in the middle and you do not want that selected then instead of holding the shift key you hold the control key and then individually select these tabs now when i selected these tabs these are all grouped anything that i do in the active sheet would also be done in all the remaining sheets see what happens now i select this and i make all the changes that i want to make in the active sheet so i make this bold i color this supply this color now let's also apply this color here and also add the sum so i would add the sum here and copy this for all these cells and just to let you know the keyboard shortcut to get the sum in the column is alt equal to and as soon as you do this it will automatically give you the sum of the entire column that has the numbers so i have these here let's also format this a little bit make this bold maybe apply a thick bottom border or maybe a thick upper border maybe something like this so this is the final formatted data that i get now when you go to data number two sheet you can see that the same changes have also been done in sheet number two and sheet number three so this is how you can easily group these sheets and if you have the same structure and you want to make the same kind of changes in multiple sheets you do not have to do this individually you can do it in one go here i have a workbook with the name example.xlsx and i have three sheets data one two three and within each sheet i have device data for these 15 stores now what i want to do is i want to compare the data in sheet number one which is data one with sheet data two now i can select this data and see one sheet at a time but i cannot have these side by side and compare the data so to do that you can go to the view tab here and here you have an option called new window and when i click on it see what happens as of now the name of my file is example.xlsx but now when i click on new window it becomes example dash 2 which means that this is the second instance of the same file what has happened is excel has opened another instance of the same file so that i can now have both these files open and compare the data side by side so here i have the second instance you can see that i have example one open and example two open now all i need to do for these files to be compared side by side is again go to the view option and then click on arrange all and here i would arrange these vertically you can also use other options but in most cases i found that arranging these vertically makes it easier for me to compare the data and now when i click ok you can see both of these files example 1 and example 2 have been arranged vertically and i have the data right next to each other now here i have selected data 1 i would go to this instance and select data 2 and let me zoom this a bit so that i have similar looking data now if i want to compare data one with data 10 here then i can easily do this you can see the scroll bar for each of these files is different now this is still the same file if you make any change in example 2 then that change would also be reflected in example 1. excel has done this just to make comparison easier for you and when you close any of the instance of these files then you would have your original file back so in this case let's say i've done the comparison and i come here and i close this file you'll see that i have my original example.xlsx back so if you have multiple sheets in the workbook and you want to quickly compare these side by side then new window is the option that you can use that's it in this video i hope you found this useful also if you're liking these videos please subscribe to this youtube channel and click on the bell icon so that you never miss out on any new excel tips video i come up with thank you and have a nice day

Channel: TrumpExcel

Views: 382,904

Keywords: excel tricks, excel tricks and tips 2020, excel tricks and tips, excel tricks advanced, excel tricks for data analysis, excel tricks for accountants, advanced excel tips and tricks, advanced excel, excel, excel basics, excel tips, excel tutorials, learn excel, sumit bansal, trump excel

Id: KdTSjdUSANw

Length: 17min 34sec (1054 seconds)

Published: Tue Jan 12 2021