Excel Tutorial | 20 Tricks & Shortcuts That Can Make Anyone An Excel Expert

hello and welcome today I'm going to tell you 20 tricks which can make anybody in Excel export yes exciting isn't it let's see how we can do it first is to click and select all yes I know all of you remember that control a shortcut key yes that makes selection easy but this makes easier for example you're working on the schita you're selecting multiple times and now you need to select all of them rather than going to your keyboard you can quickly go in the corner and press click there you go select it isn't it fast so when you are on the sheet it's rather faster to go on the corner and click it open Excel files in bulk yes a lot of files if you want to open them it's very easy just go to the desktop of the place where you saved them select all of them together like this and just press ENTER select and enter the moment you will enter all of the files will open then you can see up buzzing the name so yes all of them are open now how to shift between two excel files yes ctrl tab I know all of you remember that there's an alt tab but remember this thing that alt tab takes you through all the files which are open irrespective those are Excel or not but ctrl tab will take you in between the excels only let me show you so now there are certain files which are open so I'm pressing ctrl tab control tab and you can see the exams are moving only I am rotating myself in between the excels not among the other files so that is easy for us ctrl tab just remember this how to create a new shark menu yes quick access toolbar let me show you we all of us have it but we can use it more conveniently more fast to add the joist values there the choice options so we have to go to the quick access toolbar more commands and there you go you'll find a lot of commands over there so you need to find the area where from you will select so I will select properly let's say out of some which is already there in my sheet click no it's already there so I cannot read it so okay so I'll choose another one now which is not there so let's say for example where's the cut cut cut yeah there it is so what I'll do is I will add cut to be put there I will click ad and it's there so once I press ok it is it visible over there so I can quickly use this cut in my work that I'm doing so it's making my work very fast adding a diagonal line in the cells sometimes most of us need this diagonal line to crush some cells and I'm sure a lot of you guys have been working with the cells on the lines on the borders but this is different so if you go onto the alignment and click this is the format cell and over there in the borders you can see this box so in the box in the middle if you click you will see that there will be a diagonal line and this is really going to be reflected in your excel sheet so let's see how this cell looks like now I believe you can imagine what can be the utility of this particular diagonal line then we can add more than one new columns or rows yes it is irritating to click one line and add another one and then going for another column a row by selecting clicking insert then insert and then and so it's really irritating so the easier way I will go and show you how you can do it so rather than going for insert and using this menu like this let me remove this line and let me highlight one of these lines so that I can see how it's impacting so let's say for example I choose a certain number for example 8 rows I've selected you can see so I will simply go in insert so it has added above this 8 rows so it's faster to add quickly a number of rows which we need to be added then speedy move of copying data yes copy and then going to this place and paste no no more need of this whenever you go on your excel and you select a column or row there is a plus sign you can see this is the plus sign and when it is click and drag it and there you go no need to copy and paste you can simply drag and drop it is simple isn't it then speedy delete blank cells yeah that's also very important some default data will be blank and for various reason it's it creates problem for you to calculate so the best way to do filter it so whenever there is something blank and you need to clear it and make it really blank so you have to go and select from the filter go down and there you go it's blank so select it and now I will use the quick access toolbar to select visible cells only so I will delete them now so that's how I can quickly access delete and use this tool yes wait search with the wild cards yes we can use white card as well let me show you how you can use them in your cells so for example we have certain data over here some alleys a mark so let me highlight Ally for differentiation so yes let's go and you all know this is ctrl F so you go there and you find certain characters so either that there was a al but nothing I don't know at the end so al and at the end whatever is starting with Al it is visible here then I will use static with mark so you can see all the markets appearing so static means that whatever is before mark I don't know before K so I have to include it there so static is for including the whole character and star is for the digits let's move generate a unique value in a new column that's also very interesting so let me show you how you can generate a unique volume all of you must have used this filter today we are going to use advanced filter that is also very convenient so click on it copy to another selection and we'll copy unique numbers from this file so unique records only will select this file and copy to this column and there you go it has copied only the unique numbers from this this was a quick illustration of advanced filter then we have input restriction for data validation function so there are certain columns or rows where we need welded data we need to make sure that the users whatever the enter in the sheet should be and range or numbers so let's say for example we have an age constraint we want to put here so simply you will go to the data tab and data validation click there and select whole numbers whole numbers and ignore blanks that's very important that it's not applicable on blanks then between let's say for example the data should be between 18 and 60 years of age and anything outside this range should not be accepted so let me put some sample data over here for example 19 so it is between the range yes accepted mmm 59 that is also within the range of 78 this should not be and there you go the excel will say that it's not a valid number and not within the range so enter a correct number so that's how you can quickly validate your data when you get input from the users fast navigation with ctrl + arrow button yes when you are in your excel it's very easy to move with the control and the arrow you see I can move with the control and an arrow across the valid sheet so you can easily go to the other end of the sheet with the control and an arrow it makes fast to navigate then transpose data from one row to one column that makes a data representation different let's say how we can do it so we have certain data select it and we need to put it there so first we need to copy it so select it and press copy and then select the place where you want to pull it down paste and there is one option transpose click on it and the data will be pasted in a different dimension so this gives you an opportunity to analyze the data differently it's the same data but presented in a different way from rows to the columns hide the data thoroughly I know all of you know how to hide the data let me show you how you do it for example if you have a sheet and you hide C column by going in the options hide you can see anyone can find it out that there is something hidden in between yes but there's another way from which no one will figure it out that it's hidden so just select the data and simply go to the number tab and open the cell format and go to the custom and in the type you have to put semicolon three of them and that's it now your data is hidden but you can see it if you click on the data in the formula bar there you go so you can see in the formula bar your data but anyone who will see that I will not be able to really see this because it's hidden but totally hidden then we have to compose text with and there are certain formulas that you use to concatenate for example is equal to concatenate it's long formulas such I just forget it let's make it easy just select the first one then and sign and then go to the other one then and sign and then to another one so we have captured three cells in one with only the M sign it's faster rather than going for the formula etc it makes life easier then transform in the case of the text yes there are some formulas you should really really remember like for example upper which makes the data uppercase all of it and then then there is a another one that you can say lower that makes all the data in the lower case so you can select and make it lower but this is not the ideal condition so ideal condition is that it should be gaps the first character and then the novel is small so you simply write proper and then text it will make it perfectly as we want it there you go so upper lower and proper these are the two commands then we have input values string with zero that's interesting that when an input value starts with zero it is normally not visible Excel doesn't show it for example let me put some numbers over here 0 0 0 0 0 and 1 2 3 set will show only 1 2 3 zeros are not visible if you want to see the 0 you will have to go clay and single quote not double coat but single coat so when you press the single code put zeros as much as you want and Excel will show them that's a short key single : to show the numbers and of course the seed rows behind up then speed up and put in complicated terms and autocorrect yes so let me show you how autocorrect works so go of the file and then options and the options there is one proofing so in the proofing you can find this autocorrect option click on the autocorrect and there you can add something that you want to make it fast and don't you don't have to write but Excel will write rather so you love whenever you write sq Excel will write so lemon a licorice a add it press okay there you go now whenever I will write sq Excel will automatically complete it and understand that I want to write slalom on a licorice Acer such complicated words which you want Excel to write it for you quickly you can make it fast with the help of autocorrect then we have one click to get more stairs yes most user know how to check the data status in the bottom of the excel sheet but let me show you how you can make it fast let me delete this first and I select it and in the bottom you can see a lot of status like what is the sum of this selection count average you can add more as well if you click right and there you go there are a lot of other things like you can see the statuses so for example let me add something from over here minimum maximum so these are the ones I want to see on the end the power so you can see in the status bar you can see more things then only the count of the sum rename the sheet using double click yes most of the user when they go in Excel and want to change the name of the sheet they go right click and rename and then write it yes but there's a faster way to do that just go on the Excel name and double click you can see and write that was it from my side and I hope you enjoyed it thank you very much for joining bye-bye you

Channel: Excel to Excel

Views: 1,178,534

Rating: 4.6754446 out of 5

Keywords: mastering excel, Microsoft Excel (Software), bulk, hungarian, budapest, tanul, táblázatkezelő, excel expert, short cuts, Tutorial, Tips, Tricks, how can i, magic, incredible, amazing, Cool, How-to (Website Category), cell, row, column, formula, rename, fantastic, grow, how, magyar, cheat, cold, easy way to, Adele's, Nokia, apple, Phone, formatting, movie, song, music, games, how to boost, video, globe, science, Mars, space, illusion, first, easy, time, date, always, successful, hot, buzzfeed, ‪#‎PrayForParis‬

Id: MLz1gjnQfes

Length: 13min 23sec (803 seconds)

Published: Sat Oct 31 2015