Combine Excel Sheets the EASY Way with VSTACK

today I want to show you how you  can combine several sheets of   data in Microsoft Excel into one sheet using one function if you want to follow along with today's  tutorial I'll put a link to this data Down Below   in the description then you can click on the link  download the workbook and follow along with me   step by step the goal here today is to create  a new sheet that will take all the information   from these four sheets into one and we want to  make sure it's Dynamic so I'm going to give you   some tips and tricks along the way the first thing  I'm going to do is create a new sheet so I'm just

going to click new sheet here and we're going to  call this total sales better spell that correctly   total sales now this is where I'm going to  combine all the data from these four different   sheets notice at the cross of all these tops that  it's all the same so what I'm can do here is if I   go ahead and select these headers and I go contrl  C for copy go over here and contrl v i can paste   it in I'm going to zoom up on this so it's easier  to see now the next thing I can do is if I wanted   to just make sure it fit I'll just select all  and just make all the columns fit there now this   is where I'm going to place my formula in here  and we're going to be using the function vstack   so let's go ahead and I'm just going to click in  here put my equal sign in and start typing vstack   this is it vstack vertically Stacks arrays into  one array just to let you know this is available   in with a Microsoft 365 subscription if you don't  have that let me post a different video up above   in the card that will show you a way that you can  combine different sheets and workbooks into one   and it's a simple method as well now I'm going  to choose vstack and all I have to do is choose   my arrays and they're on each of these different  sheets so if I go over to North and click in A2   here I'm going to give you a shortcut control  shift hit the right arrow then it stretches go   is automatically where the data ends to the right  and control shift down arrow and it will go to the   bottom and stop and that selected area if I go  up where the formula is being created and just   put a comma Now I can move on to South sales and  do the same thing click an A2 control shift right   control shift down add my comma again go over to  West sales click an A2 and do the control shift   right control shift down comma and we'll go to  our last one doing the same thing clicking the   same sale cell and control shift right and down  all I need to do now is end my with my bracket hit   enter so we have if I go down to the very bottom  you should see that we have we actually have 80   entries because the header is being counted in  the first row and each of these here have 20 in   them so all those different the 20 different from  each sheet got put into the total sales and I can   still format this so if I click in here and if I  want to do control shift down add my dollar sign   we can go through and format this the formula  is placed in A2 here it is across the top and   I just want to point out notice so the range is  A2 to F21 so if all of a sudden something   gets entered in any of these so if I go to this  one and we'll just go ahead and we'll copy uh   two of these so I'm just going to go contrl C and  V now does this go up anymore is it dynamically   adding them well no because those ones are out  of range let me show you in this next method how   we can adapt for additions to this and how we  can add another function to help make sure we   keep it clean if you get any zeros in there as  well I went to made another sheet called total

sales 2 I want to show you how you can use the  vstock function faster let's go back to A2 hit   equals and we'll go back to our vstack function  this time rather than going sheet by sheet if   I select North sales first hold down shift and  then collect East sales notice that they're all   highlighted and then take a look in the formula  it's north sales colon East sales so it's going   to be anything in between you could use the  control key to select certain Sheets if they   weren't all in a row like that now the next thing  I can do is just what I did before if I click on   A2 use my control shift right and control shift  down now I have that all selected so I can go   ahead and close this and all my information is  placed back in here so now the problem is it   doesn't add if I go ahead and try to add more to  each of these it's not going to reflect on this   total sales what I need to do if I look at eales  let's say notice it just goes to F21 at the end so   it's just going to do the 21st row I need to say  you know what I know I need at least let's say   40 rows I can change this in the form formula so  if I go back to this spot it's A2 to F21 if I go   and just click in the formula bar here and change  21 to 40 now it's going to search to those and I   could add more in it the issue is though look  at this we have some zeros that appear because

right now I don't have anything in those rows so  they get brought back at zero I can add another   function to keep this clean and it's the filter  function if I go up to my formula here and make   sure you're in the right spot so I'm in A2 and  click in here and just start typing filter I'm   going to add the filter function what I need to  do is do another vstack with the filter function   and I'm going to look up a specific row so I'm  going to select this right hip here select the   same thing contrl C I'll put a comma and contrl  V but I don't want it to be searching A2 to F40 I   just want it to look in the one column which  is going to be a so instead of f here I'm just   going to go put the a here now I wanted to look  up and if there's nothing in there I just want   it to be not returned return blank so I'm going  to say doesn't equal and return blank just like   this and I'm going to go and close the bracket  up again I need a double bracket hit enter now   if I take a look at this we can go scroll down  and all those zeros are gone and I have 83   here now if I go let's do a little test on this  I'm going to go and take these rows from this   one here so I'll just go and select a bunch and  I'm going to go contrl C and so I've just added   more here contrl V if I go back to total sales  now notice that it just grew by that four that   I put in here now the other nice thing is I could  just bring a whole new sheet in here as well I've   just opened up a different one here and notice I  have Central sales I'm going to take Central sales   here and drag it in between on the one where I was  working with so in between the North and East I'll   drop it here so now that it's part of the range  because remember we went from North to East if I   look at total sales it just got all those entries  in there as well the reason why you're seeing this   formatted with these numbers is because that is a  date for format I can change this if I was going   to go control shift down and if you look at the  different types of dates if I was doing short   date I can change it so that's just a formatting  thing just like the dollar but that's how you can   use vstack to combine multiple sheets and then  using filter to even make it more powerful and   cleaner how to combine sheets in Microsoft Excel  is a very common question and I hope do you like   learning about vstack to take care of this problem  take a look at that other video that I mentioned   about how to combine sheets and even workbooks  in Microsoft Excel and that's using power query   and I'll put the link up above in the card  and down below in the description thanks   for watching this time on teachers Tech I'll see  you next time with more Tech tips and tutorials