If you try to unstack data in excel from
one column to multiple columns, you’ve probably noticed it’s not as easy
as you might have thought. You might google it,
and you’re going to find many different solutions to this.
Starting from Vba to power query to control shift enter array formulas,
index formulas, and even excel add-ins that get the job
done. Today we’re going to take a look at the
laziest way you can unstack data. It might also end up being the fastest
way to unstack. So let’s get to it. This is the dataset that I want to
unstack, so everything is currently in one column
and I want to split it to three columns. The first column is going to be the APP,
which is going to be this one. So in the first row I’m supposed to have
this. The next one is gonna be the sales value
and then the profit. Okay,
so that’s supposed to be the next number in here and then profit,
which is the one below that. But notice if I pull this this way,
my formula, my reference moves this way,
right? It doesn’t move down.
So it doesn’t give me the correct number.
Now in the same way I can’t just pull this down because this ends up coming
one down. It gives me this number,
this gives me this. So instead of this,
I actually want to have Blend in here. Now here’s what you can do to get these
updates really quickly. I’m going to use the LG replacement
trick, so instead of saying equals A4,
and we’re going to put my initials lgA4 and I’m going to do the same for the
next ones. Now for this one as well,
let’s just do lg A7, right?
I’m going to do it for two lines of data so that excel can see a pattern when I
pull this down. Now you can of course use your own
initials. You can use any initial that you like in
here. So now what I’m going to do is pull this
down because notice what happens when I pull it down.
It goes to lgA10. So A10 is this one,
the next one is 13, which is this one,
right? Perfect.
So let’s see how much data we have. I have until the row 84.
So let’s bring this down until 84, maybe just a bit further.
So we allow for additional lines to be added.
So let’s go to A90. Okay,
so now here’s what you need to do is to go to home,
find and select and go to replace or use the shortcut,
key control H, and we’re going to look for lg or
whatever initial you put in there and we’re going to replace it with the equal
sign. Click on replace all and it made the
replacements. And check this out.
Everything got updated properly, right?
And it’s all dynamic. If you change this number here,
the 1166, to,
200, you see it reflected in here and on the
bottom we’ve allowed for additional space.
And if you don’t like to see the Zeros in here another trick,
we can do or a lazy trick we can do is just to hide the
zeroes out of view. So highlight the area,
go to format cells, or use the shortcut key control 1.
And under custom use a custom formatting that hides the Zeros.
Now I show this in my other videos, but quickly the rule is the first part
that comes before the semicolon is how positive numbers should be formatted.
And I’m just going to put the Hash sign for the number.
If you wanted it to have thousand separator,
we can go with this one. Then it’s how negative numbers need to
be formatted so I can actually just copy this and put the minus sign beside it.
And then it’s how Zeros need to be formatted for zero values,
I’m just going to leave it out. So I’m going to leave it empty and say,
okay, so notice that my text stayed the same,
these changed and the Zeros are gone. But now if I add in new data to this.
Let’s do bold, 200,
100. It gets reflected in here.
But keep in mind this was a simple unstacking example.
If your case is more complicated, so you need to check for special
conditions, then you need to use one of the more
complicated techniques. But for simple unstacking,
this technique comes in really handy so it doesn’t hurt to keep it in mind.
I hope you liked this video. If you did,
give it a thumbs up, and if you like to learn more about
excel, if you want to become more advanced in
Excel, consider subscribing to this channel.