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

You can see a full explanation of how to use Consolidate in our Expert Skills Books and E-books.

Using Get & Transform

Excel 2016 (and Excel 365) offer the new Get & Transform tool, 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.

Leave a Comment

Your email address will not be published.