🔁Transpose (Flip Around) Data in Excel AND keep the formatting ☝️

If you were unfortunate enough to catch sight of the British government's spreadsheets during the COVID-19 pandemic, then you were probably just as horrified as I was that they were laying their data out running horizontally across the page, much like the data we have in this spreadsheet. And you might already know of a little function in Excel called transpose that helps us flip our data so that it's running vertically down the page. And this obviously works vice versa as well. So if you already have data running vertically and you want to flip it to horizontal, I don't know why you would do that, but you can also use this method. So we're going to take a look at the two different ways that we can transpose our data. And I'm also going to show you a little trick so that when you transpose, it keeps any formatting that you've applied. If that sounds like something that you want to know, then keep watching. So here I have some basic data. We just have month revenue profit, and currently we have it in a very horrible format running horizontally across the page, which makes it really difficult to read. So we want to flip this round so that it's in columns instead. Now, the first method we can use is just the transpose formula. And this is a really straightforward formula. So let's type in equals, and we're going to go for transpose. And the only argument we have here is the array. So we just basically select everything that we want to flip around. So for me, that is B3 to N5. Let's close the bracket, hit enter, and it flips that data around, and we now have it running in columns. Now, remember, once you've done this, your data now basically has a formula underneath it. Remember, you can remove the underlying formula simply by, again, selecting the cell range, Control-C to copy. And then if we go up to the paste dropdown, we can simply paste the values. That basically gets rid of that underlying formula and just leaves the data. So that is one method we can use to flip our data around. But notice one of the little issues here. It hasn't brought across any of the formatting. So I've lost the formatting that I have on the header rows, and I've also lost the currency formatting as well. So how can we do this and keep all of that stuff to save us a little bit of time? Well, let's try this again. I'm going to delete out everything that we've done so far, and we're going to use a slightly different method. So this time, we're going to select everything, Control-A. We're going to Control-C to copy. We're going to click where we want to paste it, but instead of using a formula, we're going to jump back up to the lower half of the paste button again and go into paste special. Now, this paste special area has two different parts. So we have the paste area and the operation area. And the paste area is where we can choose exactly what we want to paste. Do I want to paste formulas? Do I want to paste just the values? Do I want to paste only the formatting and not the values? So we have lots of different choices in here. Now, if I want to keep the formatting and transpose it, what I can do is select all using source theme. And then right at the bottom, I'm going to choose transpose. When we click on OK, it flips that data around, and it also keeps the formatting. So if you find yourself in the same position as the British government, and you need to flip your data around to make it look readable, then those are the two methods you can use. If you like this video, then smash that like button, give me a little follow, and I will see you next time.