About this article
This article was created using an automated generation workflow utilizing generative AI. We reviewed the specifications of Microsoft's official HSTACK and VSTACK functions and prepared samples to test the results when connecting tables with different numbers of rows and columns. Verification on an actual Excel device has not yet been performed.
Verification Status: 📘 Confirmed by Microsoft Support, Unverified on Actual Excel Device
When arranging two tables in Excel, what happens if you horizontally combine tables with different row counts, or vertically combine tables with different column counts? The Microsoft 365 / Excel 2024 dynamic array functions HSTACK and VSTACK allow you to combine tables using formulas. However, when combining tables with different widths or heights, #N/Amay be populated, and simply hiding them with IFERRORcan sometimes cause you to lose track of the root cause. Using the same two dummy tables, we will observe the differences and explain what to verify before applying them in practical work.
Creating sample tables
Enter the following two tables in a blank Excel sheet. The left table is A1:B4 and the right table is D1:F3. First, verify that there are no other values in the spill range of the formulas.
Left Table A1:B4
| Name | Department |
|---|---|
| Aoki | Sales |
| Sato | General Affairs |
| Suzuki | Sales |
Table on the right D1:F3
| Code | Quantity | Price |
|---|---|---|
| P01 | 2 | 100 |
| P02 | 3 | 150 |
Excluding the header row, the data on the left is 3 rows by 2 columns, and the data on the right is 2 rows by 3 columns. The sizes are intentionally varied this time. For the cell to input the calculation formula, select a cell such as H2 that does not overlap with the source tables.
HSTACK: What happens when you combine horizontally
=HSTACK(A2:B4,D2:F3)
HSTACK arranges each array horizontally. The number of columns in the return value is the sum of the columns of each array, and the number of rows is the maximum number of rows. In this case, it becomes 3 rows by 5 columns.
| 1 column | 2 columns | 3 columns | 4 columns | 5 columns |
|---|---|---|---|---|
| Aoki | Sales | P01 | 2 | 100 |
| Sato | General Affairs | P02 | 3 | 150 |
| Suzuki | Sales | #N/A | #N/A | #N/A |
This#N/Ais an area added because the data row in the right table is one less than that in the left table. This does not mean the file is corrupted. Official MicrosoftThe HSTACK functionHowever, missing rows#N/Aare explained to contain.
VSTACK: What is the difference when combining vertically?
=VSTACK(A2:B4,D2:F3)
VSTACK is a function for arranging data vertically. This time it will be 5 rows by 3 columns. Since the table on the left has only 2 columns, it is expected that #N/Awill appear in the 3rd column of the top 3 rows.
| Column 1 | Column 2 | Column 3 |
|---|---|---|
| Aoki | Sales | #N/A |
| Sato | General Affairs | #N/A |
| Suzuki | Sales | #N/A |
| P01 | 2 | 100 |
| P02 | 3 | 150 |
Simply stacking tables vertically does not mean the table semantics will match. Here, the two columns on the left are "Name and Department" and the three columns on the right are "Code, Quantity, and Price", soeven though they can be combined structurally, the column meanings are mixed. For business use, the column meanings must be aligned first.
This problem leads to a design error similar to carelessly executing a UNION in SQL on data with different column names. HSTACK and VSTACK combine by row and columnposition, and do not understand and map header names.
Changing just one part
Next, instead of deleting the last row of the left table (A4:B4), reduce only the reference range of the formula by one row.
=HSTACK(A2:B3,D2:F3)
Since both tables will have two rows, HSTACK will no longer have a shortage of rows, and the calculated area should become 2 rows by 5 columns. The advantage is that you can verify the behavior by changing only the reference range without editing the data itself.
After restoring the reference range, change the display using IFNA.
=IFNA(HSTACK(A2:B4,D2:F3),"")
The missing position#N/Achanges to an empty string. Microsoft's function documentation includes examples combining IFERROR, but IFERROR also conceals other types of errors. IFNA allows you to target#N/Aonly
Note: If there are actual errors on the source data side,#N/A they will also be hidden by IFNA. Always verify the quality of the source table separately and use this function solely for the purpose of improving visual presentation.
If #SPILL! occurs
HSTACK and VSTACK are dynamic array functions where a formula is entered into a single cell and the calculation results spill into the surrounding cells. If there is text or another formula in the area where the results are intended to expand,#SPILL!errors may occur.
Instead of writing data around the source table,always place formulas in an empty rangeas a general rule. In business reports, it is safer to prepare a dedicated sheet or area for the results.
Furthermore, calculations cannot be performed at all in older versions of Excel that do not support these functions. The products supported by Microsoft Support are Excel for Microsoft 365 and the Excel 2024 series. If collaborators use older versions of Excel, consider pasting values or using an alternative method.
Decision-making for business use
| Objective | Appropriate approach |
|---|---|
| Align different sets of information horizontally | HSTACK. However, verify the row correspondence beforehand. |
| Stack monthly data with identical column definitions vertically | VSTACK. However, standardize the column order and data types. |
| Mismatched column counts | #N/ACheck the location of occurrence and meaning |
| Pre-existing errors in input data | Do not hide them with IFNA; investigate the root cause of the error first |
| Business forms with constantly increasing rows | Comparison of schema management methods using Excel tables and Power Query |
What is particularly important is thatarranging tables side by side is different from correctly linking data.Since HSTACK simply aligns row numbers from different tables, for purposes such as "linking by employee ID", consider key-based methods like XLOOKUP or Power Query merges.
The final formula and how to create the input table are also saved inGitHub Sample: Matrix Size Differences in HSTACK/VSTACK.
