consolidate multiple (188) tables, all formatted the same, into a single worksheet. The tables are A

Copper Contributor

I have a file that I want to consolidate into a single worksheet for purposes of analysis. It is in the form of 188 tables all formatted the same and of consistent size. The tables are columns A to DL and each is rows 8 to 59.

Can you suggest a formula or process to create the single sheet?




9 Replies


If we speak about structured tables perhaps Power Query is the easiest way. Start with blank query, add = Excel.CurrentWorkbook()

as the first step, if necessary uncheck other than 188 table names and expand the content.

@Ray_Nord -My two cents


  • Power query is an ideal option!
  • VSTACK formula also does the job here. Of course you need to adjust the range and sheet name.
  • You can do it with the help of Command Prompt too!


VSTACK() assumes some logic in tables location, not only the same structure; or we have to pick-up somehow all 188 table names.


@Sergei Baklan 

The tables are simply named "table 1, table 2, table 3" all the way to table 188


If names are predefined such way, back to the sample


you may generate the spill by formula as

=REDUCE(Table1[#Headers], "Table" & SEQUENCE(6), LAMBDA(a,v, VSTACK(a,INDIRECT(v) ) ) )

, the only in your case it'll be 188 instead of 6.

Or return structured table by Power Query with 4 steps. Generated script is

    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Table")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}),
    #"Expanded Content" = Table.ExpandTableColumn(#"Removed Columns", "Content", {"A", "B"}, {"A", "B"})
    #"Expanded Content"

In general no coding is required, practically all could be done from UI.

@Sergei Baklan We can use 3D reference since tables are structurally same.





Perhaps, but we don't know details how exactly tables are located. Let assume all 188 tables are in only one sheet, or in different sheets but locations are different? The only we know for now all sheets have the same structure, i.e. same number of columns and same column names. And they are structured tables, not ranges.