MrExcel.com - Your One Stop for Excel Tips & Solutions

Consolidate Sheets

October 11, 2017 - by

Consolidate Sheets

Today, an ancient technique called Data Consolidation. If you have to combine data from multiple worksheets or multiple workbooks, Consolidation can handle the job if your data is in a specific format. Learn more about this ancient tool.

There are two ancient consolidation tools in Excel.

To understand them, say that you have three data sets. Each has names down the left side and months across the top. Notice that the names are different, and there are a different number of months in each data set.

Data Set 1
Data Set 1
Data Set 2
Data Set 2
Data Set 3
Data Set 3

You want to combine these into a single data set. Turn the page for a discussion of the two methods.

Illustration: Cartoon Bob D'Amico
Illustration: Cartoon Bob D'Amico

The first tool is the Consolidate command on the Data tab. Choose a blank section of the workbook before starting the command. Use the RefEdit button to point to each of your data sets and then click Add. In the lower left, choose Top Row and Left Column.

Consolidate
Consolidate

When you click OK, a superset of all three data sets is produced. The first column contains any name in any of the three data sets. Row 1 contains any month in any data set.

The Result
The Result

In the above figure, notice three annoyances. Cell A1 is always left blank. The data in A is not sorted. If a person was missing from a data set, then cells are left empty instead of being filled with 0.

Filling in cell A1 is easy enough. Sorting by name involves using Flash Fill to get the last name in column N. Here is how to fill blank cells with 0:

  1. Select all of the cells that should have numbers: B2:M11.
  2. Select Home, Find & Select, Go To Special.
  3. Choose Blanks and then click OK. You will be left with all of the blank cells selected.

  4. Type 0 and then Ctrl + Enter.

    Go To Special
    Go To Special

The result: a nicely formatted summary report.

Summary Report
Summary Report

Watch Video

  • Consolidate is an ancient feature in Excel
  • Specify multiple ranges to consolidate
  • Use labels in Top row & Left column
  • Annoyances: A1 is always blank, column A is not sorted, blanks in data
  • Go to Special, Blanks, 0, Ctrl + Enter
  • Consolidate can point to external workbooks

Video Transcript

Learn Excel from MrExcel podcast, episode 2046 - Consolidate Worksheets!

I'll be podcasting all of my tips from this book, click the “i” on the top-right hand corner to get to the playlist!

Alright, I have an awesome, awesome old ancient trick. I have three datasets here, Q1, Q2, Q3, they all have a similar shape, and that they have names down the left hand side, months going across the top, but not exactly the same shape. Q1 has Jan-Feb-Mar, Q2 has 5 months, I guess we got lazy and didn't figure out to do this at the end of June and someone finally did it at the end of August, and then Q4 has 4 months. Alright so, the 1st one goes A:D, 2nd one A:F, 3rd one A:E. Different names, some of the names are the same, like Michael Seeley is there in all of them, but other names come and go. Alright, this is an amazing feature, it's been around forever. I remember doing this back in 1995 even, Data, Consolidates!

Alright, we're going to use the SUM function, I've never used any of the others, but I guess they're there. SUM function, the first thing we're going to do is go back to Q1 and point to this range, those four columns, click Add, and then go to Q2, select these columns, click Add, and then Q4, select these columns. Alright, check this box for Use labels in the Top row and the Left column, that Browse button means that these data sets can be in different workbooks! Create links to source data, we're going to talk about that one at the end. When I click OK, they're going to get every name that is in any of the 3 lists, the months there in any of the 3 lists, and we now have this amazing superset, alright, annoyances!

This is a great feature, but here's the things that just hacked me off. They don't give me the label in A1, they don't bother to sort the data going down, and if someone didn't have a record in the first one, they give me blanks instead of zeroes. Alright, to fill the blanks with zeroes, Home, Find and Select, Go To Special, choose the Blanks, click OK, type a zero, Ctrl+Enter will fill those in. Easy enough to sort the data, Data, A-Z, and it will sort the data, alright. Create links, jeez, it never works right, alright, create links, for Create Links to work, it has to be in an external workbook. Alright, so I'm going to click Browse here, I created a work book called OtherWorkbook, and the data is in A1:D7, click Add, alright, and there's the first one. The next data is in G1:L8, so I'll Browse OtherWorkbook, G1:L8, click Add, alright. So now I have two references to other workbooks, Top row, Left column, Create links to source data. Excel help says that once you use Create links the source data, you'll never be able to edit those ranges, again click OK, and here's what we get.

Alright, first thing, it looks like it gave us the results, it did give us the results, but there's an extra column B here, and we have Group and Outline. And when we go to the number 2 view, ah. So ideally, what you would have is, you'd have one workbook called January, and another workbook called February, and it's going to show you here's January, here's February. Here's the total for Mike Seeley, these are formulas pointing into those sales, and then here's a sum of those two, alright, it's weird.

If you actually use this all the time, I want to hear from you in the YouTube comments, I'm sure there's an easier way to do it for me, never done it in my life, well once before today, and then today, just so that way I could explain it, alright. Consolidate, though, when we're consolidating sheets from the current workbook, awesome, awesome trick. Tomorrow we're going to compare the multiple consolidation range, Pivot tables, but all of these tricks are in the book, click the “i” on the top-right hand corner to get to that book.

Consolidate, ancient, ancient feature in Excel, you specify multiple ranges to consolidate, I always check the box for top row and left column. The results are great, but A1 is blank, column A is not sorted, and there's blanks in the data, use Go To Special Blanks, type a 0, Ctrl+Enter to fill those blanks. And then that final example there, consolidate can point to external workbooks, might be useful!

Alright hey, I want to thank for stopping by, we'll see you next time for another netcast from MrExcel!

Download File

Download the sample file here: Podcast2046.xlsm

Title Photo: 4volvos / Pixabay