Function Problems

How to Get Worksheet Names from VSTACK Results in Excel

Chanuka Geekiyanage Oct 10, 2026 868 views

Question details

The user needs to identify which source worksheet each row of data belongs to after merging multiple sheets using the VSTACK function.

How to Get Worksheet Names from VSTACK Results in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Combining data from multiple monthly worksheets (e.g., January through December) into a single master sheet.
Observed behavior: Excel's VSTACK function combines the data arrays but does not natively retain or display the source worksheet name for the returned rows.

Before you start

Ensure that all your source worksheets have consistent column structures and headers. If you plan to use Power Query, formatting your data ranges as Excel Tables (Ctrl + T) will yield the best results.

Solution 1Recommended

Use Power Query to Append Source Worksheet Names

Power Query automatically extracts and includes the source sheet name when combining multiple tables, making it the most reliable method for this task.

Instead of relying on complex dynamic array formulas that can break or slow down your workbook, Power Query natively handles metadata like sheet names during the append process.

Format data as Tables

Select the data in each of your monthly worksheets and press Ctrl + T to convert them into Excel Tables.

Launch Power Query

Navigate to the Data tab on the Excel ribbon, click on 'Get Data', choose 'From File', and then 'From Workbook'.

Select and transform data

Select your current workbook. In the Power Query Editor, locate the 'Name' column which contains the source sheet names. Expand the data columns while ensuring the 'Name' column is retained.

Load the combined data

Click 'Close & Load' in the top-left corner. Excel will generate a new sheet with your combined data, perfectly retaining the source worksheet name for every row.

Use Power Query to Append Source Worksheet Names

Highly Scalable: If you add a new month later, you can simply hit 'Refresh' on the Power Query table to include the new data and its sheet name automatically.

Solution 2

Add a Helper Column to Each Source Worksheet

A straightforward, manual approach without complex formulas, achieved by adding the sheet name directly into the source data.

Solution 3

Generate Names using LET and LAMBDA Formulas

For users who prefer a strict formula-based approach without altering source data or using Power Query.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Management

If complex Excel formulas and data combining limitations are slowing you down, WPS Office provides a free, lightweight, and highly compatible alternative. Enjoy a familiar interface and robust spreadsheet functions without the hefty subscription fees.

1. Download WPS Office: Visit the official WPS website and download the free version for your operating system.
2. Install the Software: Run the downloaded installer and follow the quick on-screen instructions to complete the setup.
3. Open Your Excel File: Launch WPS Spreadsheets and open your existing .xlsx workbook to continue your work without losing any formatting.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Supports advanced array formulas for complex data manipulationFree to download with a lightweight installationFamiliar tabbed interface makes transitioning effortless

microsoft office alternative - wps office

Frequently Asked Questions

Does VSTACK natively support outputting the source sheet name?

No, the standard VSTACK function only stacks the cell values from the specified ranges. It does not carry over file metadata such as the source worksheet name.

Can I use the CELL function inside VSTACK to get the sheet name?

While the CELL function can return a sheet name, attempting to use it dynamically inside a VSTACK array across multiple sheets usually returns the name of the active sheet rather than accurately labeling each source row.

Why is Power Query recommended over formulas for this task?

Power Query is built specifically for data transformation and appending. It automatically reads and integrates sheet metadata, making it much more scalable, easier to refresh, and less error-prone than complex dynamic array formulas.

Excel & Data Problems