How to colllect the data values from many sheets and collect in one sheet one below each other..

Question

How to colllect the data values from many sheets and collect in one sheet one below each other..

Chaturvedi, Santosh 430

Hello

I have to fillter the values in one final sheet from many sheets (like sheet_1, sheet2, sheet 3 etc) present in in one workbook

All teh values shall be filtered one below each other.

please let me Kuow how to do this collect or filter data.

Siphelele Klanisi 0 Reputation points

2026-09-17T11:01:11.0533333+00:00

Hello Chartuvedi, you can collect the data from multiple sheets into one final sheet by combining the data ranges and appending them one below the other.

If all the sheets have the same column structure, this can be done easily using Power Query or the Vstack function in Excel. For example: =VSTACK(Sheet1!A2:D100,Sheet2!A2:D100)

This will combine the data from Sheet1 and Sheet2 into one continuous list on the final sheet. If you also wantto filter the data you can add a Filter function or apply the filter in Power Query.

3 answers

Your answer

Siphelele Klanisi 0 Reputation points

2026-09-17T11:01:11.0533333+00:00

Hello Chartuvedi, you can collect the data from multiple sheets into one final sheet by combining the data ranges and appending them one below the other.

If all the sheets have the same column structure, this can be done easily using Power Query or the Vstack function in Excel. For example: =VSTACK(Sheet1!A2:D100,Sheet2!A2:D100)

This will combine the data from Sheet1 and Sheet2 into one continuous list on the final sheet. If you also wantto filter the data you can add a Filter function or apply the filter in Power Query.

Answer 1

Chaturvedi, Santosh 430

Hello --

The folowwing formula is working,

=SORT(VSTACK(Collective_1!C21#,Collective_2!C21#,Collective_3!C21#,Collective_4!C21#,Collective_5!C21#,Collective_6!C21#,Collective_7!C21#,Collective_8!C21#,Collective_9!C21#),"1",-1)

However

I just want that the VSTACK values from source table, and also want to takes the Colour format from the table.

Please advice

0 comments

Answer 2

Chaturvedi, Santosh 430

Hello I have applied this formula--> perfectly working

=SORT(VSTACK(Collective_1!C21#,Collective_2!C21#,Collective_3!C21#,Collective_4!C21#,Collective_5!C21#,Collective_6!C21#,Collective_7!C21#,Collective_8!C21#,Collective_9!C21#),"1",-1)

I just want that the VSTACK values if it takes the colur format from the source sheet. Then it will more valuable

Example see below as an example..

Please advice

User's image

Heimerdinger 630 Reputation points Independent Advisor

2026-09-18T11:44:40.8733333+00:00
Hi @Chaturvedi, Santosh,

Your formula is working correctly for stacking and sorting the values. However, VSTACK and SORT return cell values, not the source cells’ fill colors or formatting, so the original colors will not automatically follow the sorted results.

If the source colors represent a rule, such as status, category, priority, or value range, recreate that rule on the final spill range.

Select the output range, for example C21:C1000.

On the Home tab, select Conditional Formatting.

Select New Rule.

Create the same rule used in the source sheets.

Choose the required fill color and select OK.

For example:

“Completed” > Green

“Pending” > Yellow

“Delayed” > Red

Use formula-based rules such as:

=$C21="Completed"

=$C21="Pending"

=$C21="Delayed" Set Applies to to a sufficiently large destination range, such as:

=$C$21:$C$1000

Because your formula sorts the combined values, directly copying colors by the original row positions would not be reliable. After sorting, a value from Collective_7 might appear above a value from Collective_1.

Therefore, the safest arrangement is:

Use your existing SORT(VSTACK(...)) formula for values.

Use Conditional Formatting to generate colors based on each returned value.

Note: If the colors were applied manually and do not correspond to any value or rule, a normal formula cannot reliably reproduce them in the sorted spill result. In that situation, you would need to add a helper field such as Color Category to each source dataset and use that field for Conditional Formatting in the final sheet. VSTACK is documented as combining arrays vertically into a larger array.

Thank you again for your time and understanding.

Answer 3

Hi @Chaturvedi, Santosh,

You can combine the rows from multiple worksheets into one final worksheet by using the VSTACK function, provided that each source sheet has the same columns and layout. VSTACK appends the selected ranges vertically, placing the rows from each range one below another.

Combine the data into one final sheet

Create a new worksheet and name it Final.
In cell A1, enter a formula similar to the following: =VSTACK(Sheet_1!A1:D100,Sheet2!A2:D100,Sheet3!A2:D100)
Adjust A1:D100 to match the actual location and size of your data.
Include row 1 from the first sheet to bring across the column headings. Start the ranges for the other sheets from row 2 to avoid repeating their headings.
Press Enter. The data from the selected sheets should appear one below another and update when the source values change.
To filter the combined list, select the results, and then select Data > Filter.

You could refer to this article : Filter data in a range or table in Excel | Microsoft Support

Combine data from multiple sheets | Microsoft Support

Please make sure that the columns are in the same order on every worksheet. If your version of Excel does not support VSTACK, Power Query is another option, particularly for larger datasets.

To better understand your requirement, could you please confirm:

Do all the worksheets have the same column headings and column order?
Do you want to combine all rows, or only rows that meet specific conditions?
Are there blank rows in the source sheets?
Approximately how many worksheets and rows do you need to combine?
If possible, could you provide a small example of the column layout and the expected result, without including any sensitive information?

Thank you again for your time and understanding. I really appreciate your patience, and I’m here to help. Looking forward to your response.