Excel: Stop Copying—Use Power Query

Key insights

• VSTACK combines tables and ranges so you stop copying and pasting between sheets.
  Use it to create one clean list from multiple sheets and save hours of manual work.
• Work with Excel Tables and dynamic arrays so formulas update as new rows arrive.
  Structured tables keep links and references intact while making consolidation reliable.
• Remove blanks and zeros using the dot trick or by wrapping your VSTACK output in FILTER.
  This cleans empty rows and prevents zero values from breaking your stacked results.
• Analyze stacked data directly with GROUPBY and CHOOSECOLS to summarize or pick specific columns.
  These functions let you aggregate and reshape the combined data without extra sheets.
• Use 3D referencing and dynamic ranges (Start and End placeholder sheets) to pull monthly or repeated sheets automatically.
  Selecting the first and last sheet with Shift lets a single VSTACK formula span many worksheets.
• Follow Microsoft-aligned alternatives like Move or Copy, Power Query, and grouped worksheets instead of manual copies.
  These approaches reduce broken formulas, preserve links, and make troubleshooting copy/paste failures easier.

Keywords

excel link sheets, consolidate excel sheets, power query combine sheets, reference cells on another sheet, avoid copy paste excel, dynamic formulas between excel sheets, excel data consolidation without copying, auto update data across sheets excel