In this video, let me share with you one trick which I have found extremely beneficial in my working with Excel. And in fact, wherever I've gone in terms of corporate training, this trick has been the most popular amongst all. Now, let's find out why. On the left hand side lies a series of data and notice there are several blank cells. The point is all these single line items are supplier name and the respective numbers and a lot of respective transactions against them. Now, unless I fill this blank cell with the appropriate data point, which you saw in the first line, will not be able to apply filter or source or pivot table correctly.

Why so if I choose this data set and apply filter, you'll notice at the point of filtering the list with one particular supplier number or name, let's say out metallic power. One transaction appears before me and not the three or four transaction which you are supposed to be associated with this particular supplier name. Very simple reason, because in my data set, there was only one occurrence of that particular name. So unless I fill these blank cells with the appropriate name, I will not be able to apply filter, I will not be able to correctly apply sorting. And eventually when you start working with pivot table, you will not be able to create those wonderful reports that pivot table has to offer. So, the point is, do I do this manually, of course, I can tell you a shortcut key which helps you do that fast that is simply choosing the data and the blank cells below it and pressing Ctrl D. It simply copies and pastes the data automatically.

A lot of people do that. But the point is in case you're working with a very large organization, and the data set is quite large, you cannot expect to do that for 500 It means or maybe 5000 names. Let's see a better technique that we go back few steps where we had started. So I choose the entire block of data, restricting myself to only column A and column B. Now how do I do that using a shortcut key, I put my cursor on the fourth supplier number, I press Shift, right arrow key. So shift right arrow key will help you jump one step at a time.

So shift right arrow, and appointed if you press Shift control, and then down arrow, Down Arrow, Down Arrow, it goes one step at a time. If you have 5000 names, it may be a little tedious. So let me share the best trick. Starting Point, holding the first supplier number cell, Shift Ctrl N the key on the top right corner of the keyboard you'll find empty shift Control Enter. it chooses the last use cell. Now the advantage is you have been able to cover significant area of the data.

Now you have the power of shift left left, shift left left left until you are holding the correct region you are supposed to after this Ctrl G will go to special will choose blanks. Now the logic is all these blank cells should hold formula which says wherever you are, feel free to borrow the value from the above cell. Now, what I've done is I've started to put an equal to please note that still at this point, all my blank cells are selected equal to above cell. And if I press Ctrl, enter, what just happened? It created a chain of formula and every blank cell says please follow the value from the above cell immediately above sell. The logic otherwise would have been executed.

Like this equal to about sell, enter equal to above sell enter, but that would have taken significant amount of time. So do we go very quickly cap How did I achieve this, I choose before cell Shift Ctrl N key, shift left, left left left Ctrl G, I'll press S, because that will help me click on the button special or an esky. Thereafter, the option blanks the key letter key has been underlined, I simply press key and I'm going to press enter. So far, I have not used the mouse. Now immediately I press an equal sign and I choose the value from the above sale. Now the last and final part Control Enter works, although I will still have one recommendation for you, in case you ever encounter this problem, that is these cells which you currently filled are still holding formula.

If the first cell gets deeded, the chain gets broken You might be able to see 0000. If you don't want that to happen, make sure that you've also paste special value. So I select the first number, shift right, Shift Ctrl down. I copy this, and I'm going to use the shortcut key Ctrl, Alt, V all pressed together, immediately it gives me a special box. The third item values the letter V has been underlined. So I press V from the keyboard, and there I go, enter.

Now, none of these cells are containing formulas they are pure and pure data. Now Feel free to use sort filter pivot table, we stops you. So let's see how this has an advantage compared to where we started off with the filter. I now click on the same vendor name of Mattel power. This time, I get exhaustive number of transactions against that particular supplier name. try this out.

Very importantly,