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

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.

Share this article

Recent Articles

Understand modern data analysis

Excel modern data analysis

This article describes a new way of working with Excel called “modern data analysis” enabled by the new Get & Transform and Power Pivot tools.

Understand Get & Transform and ETL

Excel Get & Transform overview

Get & Transform (or Power Query) is an advanced ETL tool. ETL is an acronym for Extract, Transform and Load. This article simply explains this amazing tool.

Table tools design tools group

Table Tools, Design Tools Group

This article explains the Table Tools > Design tab on the Excel Ribbon, how to access it and how to reset the Ribbon if the tab has been disabled.

Leave a Reply

Your email address will not be published. Required fields are marked *

Which tutorial do you need?