How to stack data above or beside each other using VSTACK and HSTACK Functions

Oct 13, 2022 | Excel Hints and Tips

The VSTACK and HSTACK functions stack data below or beside each other. If the data changes then the summary table produced will automatically update. The data can be on the same sheet, different sheets or in different files. This saves manual copying and pasting of data to create overview data files. This should save time and help minimise human errors that can occur. 

These functions are currently available for most Microsoft 365 users.  If they have not landed with you yet they should do shortly.  They are not available in other versions of Microsoft Excel. 

VSTACK

The VSTACK function stacks data below each other.  Each subsequent array is appended to the first blank cell at the bottom of the previous array. 

Structure 

=VSTACK(array1, [array2],…) 

The arrays are the arrays to append that contain the data. 

Example 

VSTACK

In this example the data from 3 monthly tables is added one below each other to form a summary quarterly table. 

HSTACK 

The HSTACK function stacks data side by side.  Each subsequent array is appended to the right of the previous array. 

Structure 

=HSTACK(array1, [array2],…) 

The arrays are the arrays to append that contain the data. 

Example 

HSTACK

In this example the data from 3 yearly tables is added side by side to form a summary table for the 3 year period. The calculation is only entered in the top left cell.  All the other cells populate automatically. 

Points to note when using VSTACK or HSTACK Functions 

  • The calculation is only entered in the top left cell.  All the other cells populate automatically. 
  • The output is dynamic so if the data in the table changes the summary table automatically updates too 
  • If the range of the data is likely to change then using Excel Tables will save having to adjust the range of the data every time it alters. 

Further Reading

If you’ve enjoyed reading how to stack data above or beside each other using VSTACK and HSTACK Functions, there’s some other blogs below that you might find useful:

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.