Excel irresistible and easy tips and tricks everyone should know

Excel irresistible and easy tips and tricks everyone should know

If you are a beginner and have little or no knowledge about excel then after reading this post you feeling definitely not feel like that.

So without wasting any further time

Let's get started.

Click anywhere inside yourdata set, use Ctrl+Shift+L to add a filter to this, and then you can go about using the usual filter options, filter for names, presenter, that's my list. To deactivate the filter,click inside the data set, Ctrl+Shift+L takes it away. It's a toggle Ctrl+Shift+L puts it back Ctrl+Shift+L takes it away.

Click anywhere inside the data set and press Ctrl+T. The table has headers, click on OK. Design tab is activated, you have a lot more table options. You can choose the design you would like , you'll add a complete row to the present , summarize with a pivot table or remove duplicates. Now there are many more advantages to tables. If for a few reason you would like to convert this back to a variety , just click on convert to range.

To jump from one side of your data set to the bottom, use Ctrl+Down arrow. To jump to the right, Ctrl+Right arrow To jump to the left, Ctrl+Left arrow Jump back up, Ctrl+Up arrow. This is much faster than using the scroll bar. Now if you wanna jump to the bottom but highlight everything in between, use Ctrl+Shift+Down. Ctrl+Shift+Right arrow, highlights everything on the right side. I can click on the home tab to take me to the top of the data set so if I had some text here and I used Ctrl+Shift+Right arrow, it highlights everything there, it doesn't jump to D,because D is in fact empty, this information is sitting in E.

If I wanted to format all of these, I'm gonna use Ctrl+Shift+Down arrow to highlight them, Ctrl+1 which takes me to the format cells options, I can decide on my formatting and press enter. If I'm not using an Excel table and I wanna add a sum to this, just click on the cell below or above your data set, use Alt+Equals sign,that automatically inputs the sum function for you and it tries to work out the range that you simply probably want to sum, so during this case it has correctly figured it out. If I had a number here,and I use auto sum here, it think that I want to add these two values, and press enter. So it's quite smart in trying to figure out what you want to sum.

If you would like copy and paste your formula, but you wanna have your numbers as hard-coded, so you wanna paste it as values without the formula behind this, you need this shortcut,so first Ctrl+C to copy, and then Alt+E+S+V to bring up the paste special dialog box, all you have to do now is press enter, and you've pasted it as values. Now you notice it's a difficult one to remember, the way I remember it is Eat Some Vitamins, ESV, you just have to figure out something that works for you.

To add a new line inside a cell, use Alt+Enter, so you have to be in edit mode because if you press enter here it just moves down one cell, to move down a line inside a cell, press Alt+Enter and now you can input your text, and you can adjust the formatting as you like.

To select the entire data in the current region, click anywhere inside your dataset and press Ctrl+A. Once your dataset is highlighted, to insert a default chart, click Alt+F1. The next one is a great one, let's say you wanna add email addresses to this, and you wanna repeat this pattern for the next ones It should be James.Willard and so on, I'm gonna hold down the Shiftkey, and the down arrow key to highlight this area, and then I'm gonna pressCtrl+E, that's flash fill magic. It tries to work out the right pattern supported the pattern that I gave it first. In this case I just gave it one option and it figured it out correctly, sometimes for flash fill to work properly you might need to giveit more sample data.

To highlight an entire row, use Shift+Space, to highlight an entire column use Ctrl+Space.

To insert a new row or column, use Ctrl+Plus, this brings up the insert dialog box, you can select insert anentire row, entire column, but because we already learned a shortcut before this we're gonna use that first. So for instance I wanna insert a whole column, I'm gonna highlight the entire column, what was the shortcut key? Ctrl+Space, Ctrl+Plus, that automatically is gonna add a column, it's not gonna ask you if you want a row or a column. Now let's repeat for the row, Shift+Space, insert a new row, Ctrl+Plus. To remove an entire row, Ctrl+Minus. To remove an entire column, Ctrl+Minus, but if you wanna avoid the dialog box, we're gonna highlight the entire column first, and then we're gonna do Ctrl+Minus. If you wanna drag an entire row and drop it somewhere else, so let's say I wanna move Richard Elliot, in between Robert and Paul, what I'm gonna do is highlight the entire row, now if I drag this and let it go, it's gonna try to replace this data but I don't want it to replace it, I wanna actually put it in between those two. So here you drag it, but while you're dragging it, you're gonna hold down the Shift key, and that's gonna insertit between those two.

To hide a column use Ctrl+0, to hide an entire row, use Ctrl+9. When I first receive a newworkbook from someone else, I wanna check if anything has been hidden in there or not. The shortcut key I use is Alt+;. That highlights the visible cells only, and this way I get a visual indication that some rows andcolumns have been hidden. If nothing comes up, nothing is highlight, it means that no rows or columns have been hidden.

Now for instance I've updated this information here, I wanna add a timestamp to the present , I'm gonna use the shortcut key Ctrl+;, and if I wanna add the time to it I'm gonna add a space anduse Ctrl+: and press enter.

One bonus tip: when I receive a new file, and I wanna highlight all the constants in that file, I press F5, Alt+S, O, X, Enter.

If you have any query or want any help related to excel feel free to join our Telegram channel and message us..

Channel link :

https://t.me/msexcelmaster101