Combine Excel Sheets with *this* simple formula

so we have this budget spreadsheet that has the actual and budget information for every month one per sheet normally to combine this kind of a data I would either manually copy paste things or use power query but let me tell you a secret I haven't been using power query or the manual methods to do this that's right I'm using a really simple formula to solve this problem and that is the vstack formula it is a new formula added to Excel for vertically stacking the data so we write v stack Open Bracket and then go to the very first month select the first cell and the last cell by holding the shift so here I'm picking A2 to d26 range and then hold the shift and select the last sheet now my sheet reference is going to change to Jan to June and then the range reference becomes A2 to d26 these kind of references are called called 3D references that's right and when you close the bracket and hit enter we are going to get the entire budget data combined here in one go I mean how awesome is this now let me share a few more tricks with you let's say if I want to add July budget I can go here add a sheet and then name it as July for the sake of Simplicity I'm going to copy April values and paste it into July now you can see that even after adding the July data it's not added here in fact the reference still says Jan through June so to make the whole vstack formula Dynamic here is a really simple trick add one more sheet and call this as start then add another sheet and then name this as end and now move the end sheet all the way to the end to make it easy for us to spot I'm going to color these in this Orange color here so now every time I use the workbook I want to keep my budget sheets in between the start and end then I'm going to take my July sheet and move it to the end here and come back to my vstack formula delete it and this time I'm going to say v stack and as our data goes from A2 to d26 we'll just select it from Jan again and go all the way to the end so it becomes Jan to end and replace the word Jan with start so we saying go from start to end and get everything from A2 to d26 when you hit enter you're going to get this report again with all the data but now what about these zeros we can easily remove the zeros using a function like filter so to remove the zeros we can go back to the vack formula and then use a function like let and create a variable like allore data and keep the vstack value into that all data now that we have the all data variable I can use the filter function filter all data and then I want to check the very first column of all data and if it is zero that means we don't have a cost center ID and we can ignore that so we can say choose Columns of all data comma 1 that's going to pick the very first column so these zeros and CX 216 Etc is not equal to zero that's it when you close and hit enter you're going to get just the Consolidated data without those zeros the beautiful thing with this is if you add a new month so for example let's bring in August now and again this time I'm going to be lazy and copy the May data for August paste it here right now the August data is not going to be here because it's not between start and end let's make a note of how many rows is there we're going from row number two all the way up to 176 let's see what happens if I take this August and move it in between start and end those two colored Shades and when I go back here my data goes up to row number 2011 again just awesome really what do you think about this technique let me know in the comments and if you do want to learn a bit more about this filter function I've got a Beautiful video on the channel for you check it out it shows up on the screen I'll catch you there bye

Channel: Chandoo

Views: 28,229

Keywords: chandoo, chandoo.org, Excel, spreadsheets, how to combine data from multiple sheets, vstack example, excel vstack, consolidate data excel, combine sheets excel, club data from multiple sheets, excel formula to combine data

Id: eg53SjXxveY

Length: 4min 44sec (284 seconds)

Published: Wed Jun 05 2024