19 Excel Shortcuts you NEED to use more [with FREE PDF 📃]

the first and most important shortcut when it comes to working within data in excel is to take the data and make it a table and the shortcut for that is ctrl t it's a very easy to remember shortcut because t stands for table and you just have to select any cell in the data and press ctrl t to open the create table box just click ok making sure that if your table has headers you have enabled that option and instantly your data turns into a table when your data is in a tabular format it is very easy to handle all data related tasks like analyzing all the data together writing formulas or making a pivot report from it not only that but many of the common things like applying formats or printing or displaying this data becomes really beautiful and elegant because tables come with many default good looking styles when you scroll down on a table excel automatically uses the table headers for the column headings so that you will always know exactly where you are my name is chandra i have been using and teaching excel for the last 15 years in this video i will show you 18 more powerful and useful excel keyboard shortcuts when it comes to data analysis or reporting work i will also be giving away a free excel keyboard shortcuts poster watch the video until end for the details on that once your data is in a tabular format the next set of shortcuts that you want to know are the navigation shortcuts most people would know these shortcuts but i will repeat them nevertheless the shortcuts are ctrl arrow keys and ctrl shift arrow keys so for example from the very first row here if i want to go all the way down to the last row i can press ctrl down arrow and it will take me to the very last row before there is a blank you can see that here there is a blank value so it stopped there but if i keep pressing ctrl down it will keep jumping if there are no blank values then it will automatically take you all the way down if i want to select an entire column you can also use ctrl shift down arrow and again this will do the same thing as ctrl down arrow it will go all the way down until there is a blank value within a table if you want to select an entire column you can also use a special mouse shortcut you can place your cursor on the first row and when it turns into the black down arrow click on the column and it will select that entire column for you the next shortcut is if you want to select everything within a table or a group of data you can press ctrl a a for all to select all the data another shortcut that i commonly use for this purpose is control asterisk or control star and it will select all of the data including the headers anytime you want to edit a cell you can press the f2 key function key2 and it will place the cursor right inside the cell at the very end this is very easy to remember shortcut and as you start using more and more excel for data analysis it becomes imperative that you remember f2 this is a lot faster than double clicking the cell to edit you just have to press f2 and start typing or editing the values if you want to change a bunch of values let's say i want all of these values to be after lines you can select all the cells and press type after lines when you finish typing if you press ctrl enter excel will type the same value into all the selected cells the best part of this trick is that you don't have to select the cells together so for example i can select multiple cells holding down the control key and once all of these are selected i can change all the values in one go so let's type india here and press ctrl enter and you can see that excel has now replaced all the values with india to apply currency formatting on a bunch of numbers select the numbers so for example i want to turn all of these amounts to currency so i'm going to click the amount column to select everything and then i can press ctrl shift 4 this is also a very easy to remember shortcut because shift 4 is the taller symbol so ctrl shift 4 applies the currency formatting with two decimal points if you have got some dates in a range and you just want to turn this into dmm yy format you can use the shortcut ctrl shift 3 this will take the dates and turn it into this format let us say you like the shortcut so much that you've selected some dates and applied that with ctrl shift 3. now you want to do the same on some more dates elsewhere on the spreadsheet you don't have to press the same shortcut again you can select the dates and press f4 to repeat your last action function key 4 is a very powerful shortcut that is useful for repeating the last action that you have just done it could be anything it could be applying date formats it could be making a cell yellow color and you just select some more cells and f4 it will do the same yellow coloring for you mind you it will only repeat the last action so if you perform a series of actions it will just take the last step and repeat that when you are looking at some data filtering and analyzing the filtered portion is one of the most important tasks that we do you can apply and remove filters very quickly with the one shortcut it is ctrl shift l when you press it once excel will add the filters and if you press it again it will remove the filters so here is how i normally use it i apply filters i do my analysis so for example i'm only looking at 70 and 85 percent dark bytes and then do something something something and when i'm done i again press ctrl shift l this will remove the filters it will go back to my original data the next shortcut is control 1. this is one of the most powerful shortcuts and a very easy to remember shortcut it will open the format options for whatever you have selected so if you select this entire customers column and press ctrl 1 it will open the format cells because you have selected the cells you can do some of the formatting from here so for example you could add some decimal points or you can change the alignment from general to left control 1 is a generic shortcut so whatever you select it will format that so if i select the awesome chocolates logo and press ctrl 1 i'll get the format picture panel on the side if i have a chart on my spreadsheet i could again use ctrl 1 to format this chart or any portion of it so for example to format the horizontal axis select the axis and then press ctrl 1 and it will open the format axis box here is a trick that not many know when it comes to formatting charts once you open the format panel you can click on other aspects of the chart and the panel will automatically readjust so if i click on the data series it will change to format data series if i click on the grid lines it will format the major grid lines so this is a powerful way once you've got the panel on the right hand side you can just leave it on and do all your formatting without having to press multiple keys or closing and reopening it multiple times this next shortcut is kind of like excel's super power you can use the flash fill feature of excel to extract data based on a pattern so for example i have my sales persons here and i just want to print their initial so kunar shoot will be gc chess bonnell will be cb gigi bowling will be gb so we can add a initial column and just type the first few values gc cb gb and once you finish typing a couple of values you can press ctrl e to trigger the flash fill this will automatically figure out what you are doing here and try to follow that pattern down the line you can insert current date in a cell with control semicolon this is one of the common shortcuts that you can use to insert the latest date as per your system but if you want to get the date that is also updating every time you your date changes or you reopen your spreadsheet you can also use the two day formula you can insert the current time with control colon and that will add the time as of right now but if you want this time value to also change you can use this formula now and it will give you the same value as that but because it is a formula you can also recalculate it and the time will change to recalculate simply press f9 key let us say you're typing something in a cell and you want to insert a new line after something you can place your cursor where you want a new line and press alt enter to insert a new line character so that the cell value splits into two rows mind you it is still all in that one cell but at this point excel has added a new line between typing something and in a cell right now it appears in three lines because this column is too narrow but if you make it wide enough it will appear just in two lines let's say based on this data you're calculating some totals so i want to calculate the total amount which would be sum of the amount column of this table so it's table one we have not really named this table amount amount column and we get the answer now i want the same formula here in the next cell as well but maybe i want to make some changes to it you can of course ctrl c and ctrl v and you'll get a copy of that but here is a handy technique that i use especially when i'm working with formulas or values and i want a copy of that in the next cell i use this shortcut ctrl d d for down and it will take the value from cell above copy it down let's say got some values here in a bunch of cells and in this very last cell i want to be able to select one of these values you could use the alt down arrow and it will kind of show a small pop-up in the cell from which i can pick one of the values that are already previously entered in that column this alt down arrow trick is a very powerful one you can use it in other situations too so for example if i'm just using this table and i want to filter for after nines or something i can use my keyboard to navigate to the product cell and then press alt down arrow to trigger the drop down for that filter and from here i can use the space to deselect all the values and then just select after lines with space again and press enter to filter after nines of course this would be a lot more steps than using your mouse but for whatever reason if you don't want to leave keyboard and place your hand on the mouse you can use this approach as well let us say you have all these values and you want to make a copy of this somewhere else on the spreadsheet normally we say ctrl c ctrl v but here is a handy technique that i often use select your data hold down the control key and place your cursor on the edge of the range it will show mouse pointer with a plus symbol next to it now if you click and drag you will be able to make a copy anywhere else just leave the mouse button and you'll get a copy there this is a powerful way to make copies of anything let's say you got a chart you can hold down control on the chart and then drag it and it will let you make a copy of the chart elsewhere this next one is interesting do you know that you could use emojis and special symbols and currency codes in excel to insert an emoji all you have to do is go to a cell and press windows and dot key we'll add a column to this table to check if an amount is more than two thousand dollars or not you can say if amount is greater than two thousand double quotes and then windows dot key this will open the emoji keypad will print a smiley symbol close the double quotes and if it's not more than 2000 we will print let's say a crying emoji and we get all these nice little emojis in the cell unfortunately they won't be as colorful as on your mobile phone or on the browser but they're still emojis oh well that reminds me if you are enjoying this video and finding all these shortcuts useful give it a thumbs up i really appreciate it many times when you are working within excel you might find that the ribbon on the screen is taking up a little too much real estate and blocking some of the important values that you may want to show you can collapse the ribbon or quickly expand it with the same shortcut it is ctrl f1 press it once and the ribbon will go away press it again and it will come back up my last shortcut in this series of shortcuts is the all powerful alt key anytime you want to go to any of the ribbons and do something you don't even have to use your mouse you can briefly hold the alt key and excel will show you all the shortcuts so for example i want to insert a pivot table from this data but i don't want to use my mouse i can hold down the alt key i know that the pivot is in the inserter bun so i'll press n and then i can see that v will get me a pivot table so i'll press v and i'll get a create pivot table dialog when you press enter now you will instantly get a pivot table it is another story that we do now need to use mouse to build this pivot table but we were able to get all the way here without using the mouse let me present you with two more bonus shortcuts number one is deleting cells let's say we don't like chess bonnell in this names list and i want to delete chess i can select the cell and then press ctrl minus and this will open up the delete cells box using the arrow keys i can pick up an option of delete that i want i can delete an entire row or just the cell in this case we just want to delete the cell we don't want to delete anything else on the spreadsheet and when you click ok chest bundle is gone let's say we change our mind we want to introduce a new cell there but maybe put someone else's name you can press the control plus and that will open up insert option again asking you how you want to insert do you want to insert an entire row or just shift the cells down in this case i want to shift the cells down and i'll get a blank cell for me to play with as promised here is the free keyboard shortcuts poster download it from the link that is shown up there and if you want to learn how to clean your data quickly and effectively using excel techniques check out my top 10 data cleaning tips video as well i wish you an awesome day ahead thank you

Channel: Chandoo

Views: 126,682

Keywords: chandoo, chandoo.org, Excel, spreadsheets, important excel shortcuts, excel shortcuts for data analysis work, which excel shortcuts should I learn, microsoft xl shortcuts, shortcuts and tricks for excel, excel shortcut keys, excel shortcuts, excel shortcuts and tricks, excel shortcut keys f1 to f12, advanced excel shortcuts, keyboard shortcut keys, keyboard shortcut keys windows 10, keyboard shortcuts

Id: Q8fghk3DQxY

Length: 14min 35sec (875 seconds)

Published: Tue Aug 31 2021