Using Excel VStack to Consolidate Datasets
Let’s start with this, why would you want or need to consolidate datasets in Microsoft Excel? Because your Excel programmer decided to place like data across multiple tabs and multiple workbooks. They could have just as easily if not more easily, put all of the data in one easy-to-reference vertical Excel Table. Or you can consolidate Excel datasets using the new VStack Array Function.
Using separated data sets creates a huge challenge when you want to reference that data. The problem is that your data is now in multiple data ranges, instead of just one, how do you easily pull all of that back together?
Luckily, the Microsoft Excel Team noticed the issue programmers were creating, and they created the new and highly powerful Microsoft Excel VStack Dynamic Array Spill Function as a quick and easy-to-use workaround.
Using this VStack Spill Array Function allows you to quickly consolidate your data into one virtual table. Now you can reference all of your data in one place, in one range. Note, you are referencing a virtual table.
The VStack function is one of the new Dynamic Array Functions, these Spill Functions simplify Excel programming and are available in Office 365.
Important Note: VStack Tables are in memory and not an actual table of values.
A little Background on Excel Dataset Placement
Less experienced Excel programmers love to separate their data across multiple tabs. Not sure why they think this is a good idea, or even where the idea comes from. But it is more common these days to see data across multiple tabs and multiple files than to see a proper vertical Excel Table. This is an ongoing problem, one that can easily be avoided.
Instead of having all your sales data in one proper Excel Table, many inexperienced Excel programmers choose to have one tab per week, or per month, or per year of sales data. Often having multiple Excel workbooks with multiple tabs. Please don’t do this! Why would you do this?
This is like walking out your front door, turning right, and walking all the way around the block, to get to the house on your left side. You could have just walked out your door and turned left.
The Expert’s Opinion on Separating Data Across Tabs.
Excel Expert’s Advice on data placement: But that is not how you do it, folks. You should store your data vertically, in one named Excel Table. All your Sales data should be in one Sales Table. If there is too much data, store it in an Access database. Do this and life is easy; do it across multiple tabs and files and life is hard.
Why do it this way, one vertical Excel Table? Simplicity and ease of use. How will you report on and analyze your sales data when it is across many tabs, even many files? Sure, you can use code and write formulas to pull it all back together, like you do with Excel budget files, but why would you?
Excel Expert’s Question on using separate data tables: Why did you break your data apart in the first place? Why is this a good idea? How are you going to pull all that data back together? I ask so that you think it through.
The Excel Data Challenge – Were to Place it:
If you have 52 tabs in your Excel workbook, how do you easily create reports and analyze them? If you have data tabs across multiple Excel workbooks, how do you create a custom Excel Dashboard with them? How do you put them all into one Pivot Table, easily? The challenge is where to place the data. Do it vertically and you eliminate the challenge.
Sure, there are many workarounds in Excel, and many formulas you can combine to do this. You can write some VBA as well, but all of that takes time and effort, as well as the skills needed to implement it.
There is literally no need to do it this way. If you put all your sales data in one Excel Table, we would not be having this conversation. But we are. Luckily Microsoft released the VStack Dynamic Array Function as a proper workaround to this all too common of problem.
Our Firm’s Specialty is helping you to get the most out of Excel and Access.
Our firm’s specialty is programming custom Excel files that are fully integrated and fully automated. Point-n-click is intuitive to use. The user interface is extremely important. With every bell and whistle, considerable consideration of how the user will interact with the file is given. Many come with a custom Excel Dashboard with Pivot Tables, Pivot Charts, and Slicers.
Enter the Microsoft Excel VStack Spill Array Function:
The VStack Function allows you to consolidate data from multiple tables into one vertical data table. This is a virtual table, in memory. This is quick, easy and powerful!
While this function is very useful in fixing people’s programming issues, I would first recommend combining all your sales tables into one master sales table, and then you do not even need to read this post. But if that is not the case, then yes, read on.
The nice thing about the VStack function is that you can use this function to retrofit your workbook if you are not allowed to rebuild the workbook. There are many instances where rebuilding the workbook is not allowed, this solution allows you to quickly get past that.
In an ideal situation, you would take all the sales data tables, and you would put that data into one vertical Excel Table. You would delete the other sales data tabs as they would no longer be needed. This simple approach would make programming this file a whole lot easier.
Microsoft’s Excel Team sees how poorly many programmers use Excel; they might have duplicates in their data, unprotected formulas, and so forth. So, the Excel Team at Microsoft has come up with some amazing new functions and features to solve the problems these programmers created. Enter the new VStack Array Function.
We will not teach you all you need to know about this function as there are more than enough videos on the subject. Personally, we recommend the Trump Excel site and its videos.
About using the Using VStack to Consolidate Excel Datasets:
The VStack Array Function will stack two or more Excel data tables into one Dynamic Dataset. It will now function more like a proper Excel table, though this table is built via formulas, and not data.
Now that your data is in the desired vertical format, you can quickly use the XLookup or SumIfs Functions to populate your reports and analysis. Use TextJoin, Concat, or Concatenate to get more out of the dataset.
The Benefits of Using the VStack Spill Array Function:
- The virtual table has all of the data in one range.
- Now you can write functions against this in memory table.
- You should be able to mostly use just the XLookup and the SumIfs Functions w/ TextJoin.
- Now you can write functions against this in memory table.
- The table will be dynamic and self-updating, if the source tables are.
The downside to Using VStack to Consolidate Excel Datasets:
- Future data will need to be added to the new tabs in the file.
- Instead of to the bottom of the existing table that automatically grows.
- You will need to name the new range each time that you create one.
- The new named range will need to be added to the VStack Function, each time you add one.
- Another unnecessary step.
- So, there is an effort to make, even when using the VStack Function.
- Instead, just put all your data into one Excel Table and be done with it.
Conclusion – Using Excel VStack to Consolidate Datasets:
This is a great new function and I use it all the time. I wish I did not need to, but I do, so I am so grateful to have this function available, thank you Microsoft.
The VStack Spill Array Function allows you to get around issues quickly and efficiently with separate data. If you have data across multiple tabs and even workbooks, the VStack Function is a great workaround.
It would be best to pull all your like data into one single Excel Table. You eliminate all the issues and efforts discussed here. But if you are not allowed to make changes to the structure of the file, no worries, this function is amazingly powerful, and it should be a dominant tool in your Excel toolbox.
Other Posts on how to use the new Excel Spill Array Functions for Business.
Microsoft Excel Spill Array Functions for Business – what to do with them.
1.Related Post: Using ChooseCols Function in Excel Reports – Excel Reporting Just Got Easier.
2. Related Post:
3. Related Post: Using Unique Function in Validation Controls – Users Love Drop Down Lists
4. Related Post: Using Transpose Function in Excel – Transposing Excel Then and Now