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
You can see a full explanation of how to use Consolidate in our Expert Skills Books and E-books.
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.
Our Expert Skills Books and E-books fully explain how to customize the Ribbon and Quick Access Toolbar.
Using a keyboard shortcut
There’s a ‘secret’ keyboard shortcut that allows you to access the PivotTable and PivotChart Wizard.
First press <Alt>+<D>.
A small tip box should appear, indicating that you have pressed the ‘Office Access Key’:
When the box appears, press the <P> key.
The PivotTable and PivotChart Wizard should appear.
