Can You Automatically Combine Several Sheets Into One Continuous

Publishers of the world's most comprehensive and up-to-date Excel tutorials

Excel tutorials

The only Excel books you will ever need

The only constantly updated Excel 365 titles more…
Thoroughly covers Power Pivot and Power Query more…
Covers business intelligence and OLAP features more…
Simple, easy, understandable, 2-page lessons more…
Available as both printed books and e-Books more…
Used by schools, colleges and universities more…
Available for 365, 2021, 2019, 2016 and Mac versions more…

How to consolidate data and automatically keep it up to date in Excel

Consolidating multiple worksheets into a single one is a problem faced by many Excel users, and it's a problem that has many potential solutions.

Using Copy and Paste

The simplest way to combine multiple worksheets together is to copy and paste the data into a single worksheet. Copying and pasting is an essential Excel skill that's covered in our Essential Skills Books and E-books.

The problem with using Copy and Paste is that it needs to be done manually and can't automatically refresh when data changes. Despite this, it's often the best choice as it is the least complicated and can be done by almost all Excel users.

Using the Consolidate tool

Consolidate takes data from multiple worksheets and provides a consolidated summary. It's capable of linking to the source data, enabling it to refresh when the source data changes, but it only provides summaries rather than consolidating the data itself.

You can find Consolidate on the Excel Ribbon, under: Data > Data Tools > Consolidate

Using Get & Transform

All Excel versions since 2016 now include the new Get & Transform tool (previously called Power Query), which allows you to combine data from multiple tables in a form that can be refreshed. It offers the most complete solution to the problem, but most Excel users don't have the skills needed to use it.

You can join multiple tables together using Get & Transform's Append feature.

Get & Transform is a true 'expert' feature, and is covered in great depth in our Expert Skills Books and E-books. If Get & Transform would be useful to you, the Expert Skills course would be a very worthwhile use of your time.

Get & Transform can be found on the Excel Ribbon, under: Data > Get & Transform Data > Get Data

The 'secret' consolidated pivot table feature

One final way to consolidate Excel data is to use a pivot table. Standard pivot tables can only use data from a single table, but there is a 'secret' feature that enables pivot tables to consolidate data from multiple tables.

Pivot tables are only useful for summarizing data, so they will only be useful if you are trying to create a consolidated summary.

To do this you'll need to use the PivotTable and PivotChart Wizard. Officially this is a legacy feature that has been replaced by Get & Transform, but some users still find it useful as an easier way to create consolidated pivot tables.

The PivotTable and PivotChart Wizard can be accessed in one of two ways:

Customizing the Quick Access toolbar or Ribbon

Excel has a huge array of features that aren't displayed on the Ribbon, including many 'legacy' features such as the PivotTable and PivotChart Wizard.

You can access these features by adding them to your Quick Access Toolbar or Ribbon. You'll find the PivotTable and PivotChart Wizard under All Commands.