Data Import & Export

How to Create a Summary Sheet from Multiple Excel Supplier Tables

Rana GarciaRana Garcia Nov 10, 2026 868 views

Question details

The user needs to combine supplier data from four different worksheets into a centralized summary sheet, returning only the product names and their required quantities when the quantity is greater than zero.

How to Create a Summary Sheet from Multiple Excel Supplier Tables
Product
Excel
Device & OS
not provided
Scenario
Consolidating product requirement data from various supplier worksheets into a unified, dynamically updating summary.
Observed behavior
A consolidated summary sheet is generated that automatically filters and lists only the required products with quantities greater than zero.
Before you start

Ensure that your source data ranges in all four worksheets are formatted as Excel Structured Tables so that your summary sheet will update automatically when new rows are added.

Solution 1Recommended

Use VSTACK and FILTER Functions (Microsoft 365)

The most efficient and dynamic method to combine and filter multiple tables in modern Excel versions.

If you are using Microsoft 365, you can utilize dynamic array functions like VSTACK and FILTER. VSTACK allows you to stack arrays vertically, effectively appending multiple tables into one continuous list. Wrapping this with a FILTER function ensures that only items with a quantity greater than zero are displayed.

1
Format data as Structured Tables

Select the data range in your first supplier worksheet and press Ctrl+T, or go to Home > Format as Table. Name the table in the Table Design tab (e.g., Table1). Repeat this process for all four supplier worksheets.

2
Stack the tables using VSTACK

Navigate to your summary sheet and select the top-left cell where you want the data to start. Type =VSTACK(Table1, Table2, Table3, Table4) to append all tables together.

3
Filter for quantities greater than zero

Modify your formula to include the FILTER function. Use a formula structure like =FILTER(VSTACK(Table1, Table2, Table3, Table4), VSTACK(Table1[Required], Table2[Required], Table3[Required], Table4[Required])>0) to extract only the products that need to be ordered.

Use VSTACK and FILTER Functions (Microsoft 365)
Dynamic Updates: Because you used Structured Tables, whenever you add a new product to any of the four supplier tables, the summary sheet will automatically expand and include the new required quantities.
Efficient Data Consolidation

Combine and Summarize Multiple Tables Effortlessly in WPS Office

WPS Spreadsheet provides powerful data consolidation tools and supports dynamic array functions like VSTACK and FILTER, making it exceptionally easy to merge supplier tables and filter required quantities.

  1. 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the file containing your four supplier worksheets.
  2. 2. Format your source data as Tables: Highlight your data ranges and use the 'Format as Table' option under the Home tab to ensure dynamic updating.
  3. 3. Enter the VSTACK and FILTER formula: In a new summary sheet, enter your combined VSTACK and FILTER formula to consolidate the tables and exclude zero quantities.
  4. 4. Instantly generate your summary: Press Enter, and WPS Spreadsheet will instantly calculate and display the dynamically filtered list of required products.
Free and lightweight office suite for daily productivityFully compatible with Microsoft Excel formulas, tables, and formatsSupports dynamic array functions for instant data consolidationClean, tabbed interface to easily navigate between multiple supplier worksheets
microsoft office alternative - wps office

Frequently Asked Questions

Can I combine tables with different column structures using VSTACK?

VSTACK stacks arrays vertically based strictly on column order. If the tables have different column structures, the columns must be manually rearranged to align first, or you should use Power Query, which appends data intelligently based on matching column headers.

Why is my VSTACK formula returning a #NAME? error?

The #NAME? error typically occurs if you are using an older version of Excel (like Excel 2016 or 2019) that does not support dynamic array functions. If you see this error, you will need to use Power Query to consolidate your tables instead.

How do I ensure my summary sheet updates when suppliers add new products?

By formatting your source data as Structured Tables (using Insert > Table) rather than standard ranges, any new rows added to the bottom of those tables will automatically be recognized and included in your summary sheet formulas or Power Query outputs.

Are there built-in tools for data consolidation without formulas?

Yes. If you simply want to aggregate totals across identically structured sheets, you can use the built-in 'Consolidate' tool found under the Data tab. It allows you to sum, average, or count data from multiple sheets based on row or column labels, though it does not automatically dynamically filter out zero values.