Consolidate Data From Multiple Worksheets in a Single Worksheet in Excel

- Written by Puneet

In my starting days with Excel, I was really bad at consolidating data. If you asked me to capture sales data for four different zones, I would create four different worksheets for that.

Yes, I was that bad. But you always learn from your mistakes. Am I right?

And if you are doing the same mistakes as I did, I have an amazing tip for you. One of the best Excel options which I have learned about managing data is to “Consolidate Data From Multiple Worksheets in a Single Worksheet”.

Consolidate Data in Excel from Multiple Worksheets [Steps]

Here you have these four worksheets (DOWNLOAD this file from here) for four different zones which have the same structure.

consolidate data from multiple worksheets in a single worksheet four sheets with same data

Now here are the steps you need to follow to consolidate data from these multiple tabs.

  • First of all, insert a new worksheet and name it “Total” (or whatever you want), and select cell A1 in that worksheet.
consolidate data from multiple worksheets in a single worksheet four sheets
  • Now, go to Data Tab ➜ Data Tools ➜ Consolidate.
consolidate data from multiple worksheets in a single worksheet click consolidate
  • Once you click on consolidate, you will get a window like this (Just follow the steps, for now, I will explain this window in the second part of this post).
consolidate data from multiple worksheets in a single worksheet consolidate window min
  • Now, by using the “Reference” input box, select the data to range from the first sheet where you have data (north zone).
consolidate data from multiple worksheets in a single worksheet select reference
  • After that, click “Add”.
consolidate data from multiple worksheets in a single worksheet add reference for north
  • By using the same method, add references from all four worksheets.
consolidate data from multiple worksheets in a single worksheet add reference
  • Now from the bottom of the window tick mark “Top Row” and “Left Column” (As we have labels in the first row and first column).
  • Click OK.

Once you click OK, it will instantly give you a consolidated data table with the same structure.

consolidate data from multiple worksheets in a single worksheet copied data

Add a table heading in cell A1 and you can also use format painter to copy formatting from other worksheets.

consolidate data from multiple worksheets in a single worksheet add formatting

Finally, you have a worksheet with consolidated data.

Other Options in Consolidate

Consolidate is a handy tool to consolidate multiple worksheets into a single sheet but we have a lot of other things in this. Let me explain them one by one.

  1. Function: You can select a function from this drop-down menu (sum, count, average, etc).
  2. Reference: Select the data range from the worksheets you want to consolidate.
  3. Browse: If want to add a worksheet that is in another workbook, you can use this option.
  4. Add & Delete: Add or delete selected references to the list of sheets to merge.
  5. Top Row: Use the top row as a label.
  6. Left Column: Use the left column as a label.
  7. Create Links To Source Data: If you want to create links for your source data tick mark this option. This is useful if you want to make changes in your source data after creating your consolidated sheet.

10 thoughts on “Consolidate Data From Multiple Worksheets in a Single Worksheet in Excel”

  1. NEED TO CLUB ALL SHEETS IN A EXCEL FILE 2007
    PL….

    Reply
  2. Stumbled on thread to awesome dude!!! Can’t thank you enough glad you put ur picture as well. I hope you do great things and ur life is filled with righteousness.

    Reply
  3. Good shortcut to consolidate multiple sheets data.
    Thanks!!

    Reply
  4. You can also use Sum(‘North:South’!B2) for B2 cell and copy and paste it which way you want to go:)

    Reply
  5. Thanks, a great useful tip! How can this be done without losing the zones?

    Reply
  6. Great article. Does this also work with named ranges and tables?

    Reply

Leave a Comment