Reading Time: minutes remaining

How to Consolidate Data in Excel: 2 Easy Ways to use Excel’s Consolidation Tool

Jason Morrell

by Jason Morrell 
March 20, 2020

In this post you'll discover why it's useful to consolidate data and see 2 different ways to use Excel's Consolidation tool.

1. Why do you need to consolidate data?

There are many situations where you need to summarise or gather data from a number of different worksheets. For example, if monthly data was stored on separate worksheets, you may wish to total up the figures from each worksheet into a new quarterly worksheet.

If all else fails you could copy and paste data, although this introduces many problems.

It is not difficult to create formulas which collect the relevant data. However, when the data comes from different sources (e.g. different people, departments or branches) there is often data missing or a table may have a different layout or be in different positions with a worksheet. This slows down the process of creating the correct formulas.

Excel provides a neat feature called Consolidation. It can be used in two ways:

  • Consolidate data by position – the data on each worksheet is based on identical layouts, such as when templates are used.
  • Consolidate by matching labels – the data on each worksheet can be in any order in any position on the worksheet, so long as the column headings and row labels are consistent.

2. Consolidate data by position

Consolidate data by position

When every worksheet has an identical layout, you can consolidate data by position. It doesn’t matter if the column headings or row labels contain slightly different spellings as this method works only on the position of the data.

To consolidate data:

1.  Either select the whole range of empty cells that will house the consolidated data, or simply select the upper-left cell of the range.

2.  Select the Data tab.

3.  Click the Consolidate icon on the Data Tools group.

4.  Choose what to do with the data by selecting a function from the list. The default is SUM.

5.  Position the cursor in the Reference box.

6.  Select the worksheet and range of the first set of data to be consolidated.

7.  Click Add

8.  Rinse and repeat for any additional ranges to be consolidated.

9.  Leave the Top Row and Left Column boxes un-ticked.

10. Choose whether or not to create a link to the source data.    

  • If you tick the Create links to source data box, every source data value will be pasted into the consolidated table, each data range will be outlined, and a formula will generate the consolidated totals.
  • If you do not tick the Create links to source data box, the consolidated totals are generated but inserted as static values.

11.  Click OK.

Consolidating data by position

The consolidated data has been inserted into the original data range that was selected.

Consolidating data by position

If the Linked box is ticked in the Consolidate dialog box, then the source data is pulled through and then manipulated (i.e. SUMMED or whatever function you choose).

Consolidating data by position

3. Consolidating data by matching labels

Consolidating data by matching labels

When the data that you want to consolidate does not have a consistent format, you can consolidate data by matching labels instead.

This method works by matching the olumn headings and row labels of the summary table with the ranges being consolidated. When Excel finds a match, the data is consolidated.

While position is not relevant, the labels themselves are. In order to find a match, the labels must have matching spelling and capitalisation. Labels do not have to be in the same order within the table and some worksheets may omit a label altogether. Where Excel finds a match, it will use the data.

To consolidate by category, the process is largely the same as consolidating by position, with a few key differences:

1.  On the summary sheet, select the cells that will house the consolidated data. You must include the row labels and column headings.

2.  Select the Data tab and click the Consolidate icon.

3.  Choose a function. The default is SUM.

4.  Place the cursor in the Reference box, if necessary.

5.  Choose what to do with the data by selecting a function from the list. The default is SUM.

6.  Position the cursor in the Reference box.

7.  Select the worksheet and range of the first set of data to be consolidated.

8.  Click Add

9.  Rinse and repeat for any additional ranges to be consolidated.

10. Tick the Top Row box and the Left Column box.

11. Click Ok

Consolidating data by matching labels

4. Key Takeaways

  • There are 2 ways to consolidate data – by position and by using labels.
  • To consolidate by position, all input data sources must be the same size and shape and in the same position.
  • To consolidate using labels, data can be in any size shape or position as long as the top and side labels match.
  • The Consolidate tool is found on the Data ribbon.
  • Consolidated data can be pasted (for a static snapshot) or linked (to collect data dynamically).

I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.

Have a fantastic day.

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell

Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.

He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.

Purely for amusement he sometimes talks about himself in the third person.


more from

Office Mastery logo
Read More
Excel Screen: A Beginners Tour
Read More
5 Excel Functions for Beginners: Simplify Your Formulas and Release the Magic


{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}