Hello, everybody, in this video session, we'll talk about the transpose formula versus the Paste Special transpose the difference between the two. And the advantage of one over another. Well, at a very basic level, if you want to convert this vertical data form into an a horizontal form exactly the way it is shown, I can do that very quickly. I can copy the data, I can put my cursor on one of the blank cells, I can say right click, Paste Special. And then values if you want values at this moment, I don't want any value I want, just the way it is. But the main thing is you must choose transpose, okay, and I do that.

Now watch. If I change one of the numbers in the January of 2008, notice it doesn't change in the corresponding cell of the horizontally please state data, but it does change in the other data output, which is the table number two on the right. Now how do I make that happen? Well, the procedure is somewhat like this, if you have a simple data somewhat like this one, two, and three. And once I make that posted on my screen, I wish that it must be pasted horizontally, at the same time, it should be connected to the original cell, so that whatever changes are made here should reflect in these horizontal cells. Well, what I'm gonna do is, I am going to count how many instances of the numbers are there, there are three, so I'll choose three cells.

Then I'll write equal to transpose, that's the formula. And I'll press the Tab key to finish the entire formula. Now it asks for array. So I choose the designated array. Now when I close the bracket, do not press enter, not even Control Enter. This is supposed to work like a group formula array formula, details of which I'll discuss more in detail later.

But at this moment, they'll be to activate an array formula, Ctrl, Shift, Enter. And once I do that, Notice if I change any one of the three values, it has an equal and effect in the final output. Something similar I'm going to do, but you might ask me, Look, this was a simple data. But the data that we are working with is slightly large. It's difficult for me to remember how many rules that I need to select horizontally. Correct.

So we combine the power of both p special transpose as well as transpose formula, how I'll first choose the data. Copy that I will right click on one of the designated cells PASTE SPECIAL transpose. Okay, now I No need this pay special transpose. I did this because I can delete the data and what I am left with is the selection. This selection is important because here I don't have to count how many cells I need to select. So once that is there on board, I will straightaway type in equal to notice when I typed in equal to the selection is still on, and that's what I want.

So do not click on a cell separately. Because that will take over the selection. So at this moment I continue with our formula transpose. Now I will choose the entire array, something like this not leaving any of the cells. Once I close the bracket, I will press Ctrl, Shift, Enter. And once I do that, the change whatever change I make let's thousand all of them.

Notice all the cells where I have changed it 2000 has an equivalent effect in the table with which have got transferred as transposed formula. So I'll show this quick trick once again. First, you copy the designated cells. You do a paste special PASTE SPECIAL transpose, okay, delete immediately. What you are left with is a selection I would want start with equal to transpose the cells are still selected. I made a mistake no problem.

I'll do this again. Copy perfect. Do a paste special pay special transpose okay. Basically delete the data is to transpose correct spelling of the formula, then choosing the entire designated area and closing the brackets. Finally, before I close this entire thing Ctrl Shift Enter. So there you go.

This was transpose formula versus pay special transpose