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

AUTHOR:  JASON MORRELL 

Jason Morrell

Reading Time:
  minutes remaining

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).

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

Office Legends Academy | Making Office Legends

Master Microsoft Office
in 30 days or less

more from

Office Mastery logo
Read More
SOS-Click: The Easy Automated Backup Tool For Home Users
Read More
6 Excel Templates to Help Organize Your Daily Activities
Read More
10 Ways Excel Consultants Can Help You Fully Leverage Your Business Spreadsheets
Read More
How to fix formulas: 21 Fatal Formula Fails (and how to avoid them)

JOIN THE DISCUSSION

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