MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Consolidate Sheets


October 11, 2017 - by Bill Jelen

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

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode 2046
  • consolidate worksheets I'll be
  • podcasting all of my tips from this book
  • click the eye 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 in 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 five 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
  • four months alright so the first one
  • goes a 2d second one a through f third
  • one a through e different names some of
  • the names are the same like michael c
  • lee is there in all of them but other
  • names come and go alright this is an
  • amazing feature it's been around for
  • ever I remember doing this back in 1995
  • even data consolidates all right we're
  • going to use the sum function I've never
  • used any of the others but I guess
  • they're there some 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 Q to
  • select these columns click add and then
  • q4 select these columns all right 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 great links the
  • 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 three lists the months
  • there in any of the three lists and we
  • now have this amazing superset all right
  • annoyances this is a great feature but
  • here's the things that just hacked me
  • off they don't give me the label na one
  • 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 zeros all right to
  • fill the blanks with zeros home find and
  • select go to special choose the blanks
  • click OK type A zero control enter will
  • fill those in easy enough to sort the
  • data data A to Z and it will sort the
  • data all right create links cheese it
  • never works right all right create links
  • for create links to work it has to be in
  • an external workbook all right so I'm
  • going to click browse here I created a
  • work book called other workbook and the
  • data is in a 1 colon d7 click Add all
  • right and there's the first one the next
  • data is in G 1 2 l8 so I'll browse other
  • workbook g 1 2 l8 click Add alright so
  • now I have two references to other
  • workbooks top row left column create
  • links to source data the help 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 all right first thing it
  • looks like it gave us the results it did
  • give us the results but there's an extra
  • column be here and we have group and
  • outline and when we go to the number to
  • view so ideally what you would have is
  • you'd have one book work book called
  • January in another workbook called
  • februari and it's going to show you
  • here's January here's februari here's
  • the total for Mike Seeley these are
  • formulas pointing into those sales and
  • then here's a sum of those two all right
  • 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 all right consolidate though
  • when we're consolidating sheets from the
  • current workbook awesome awesome trick
  • tomorrow we're going to compare the
  • the multiple consolidation range pivot
  • tables but all of these tricks are in
  • the book click the eye 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 a one is blank column a is not
  • sorted and there's blanks in the data
  • use go to special blanks type of 0
  • control enter to fill those blanks and
  • then that final example there
  • consolidate can point to external
  • workbooks might be useful all right hey
  • I want to thank for stopping by we'll
  • see you next time for another net cast
  • from MrExcel

Download File

Download the sample file here: Podcast2046.xlsm

Title Photo: 4volvos / Pixabay


Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.