Excel Productivity Guide

Get instant access to this free e-book fully loaded with Useful Excel Tips, 80+ Keyboard Shortcuts, and VBA Codes.

Consolidate Data From Multiple Worksheets In A Single Worksheet

    In my starting days with excel, I was really bad in capturing data.

    If you asked me to capture sales data for four different zones, I will create four different worksheets for that.

    Yes, I was really bad.

    But, you always learn with your mistakes. Am I right?

    And, if you are doing the same mistakes like I did, I have an amazing tip for you.

    In this post, I will show you how to consolidate data from multiple worksheets in a single worksheet.

    Steps To Consolidate Data from Different Worksheets

    We have these four worksheets here for four different zones which have the same structure.

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

    And, we want to consolidate all these worksheets in another worksheet.

    Download this file to follow along.

    consolidate data from multiple worksheets in a single worksheet four sheets

    • After that, select cell A1 in your new worksheet.
    • 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 about this window in second part of this post).

    consolidate data from multiple worksheets in a single worksheet consolidate window min

    • Now, by using “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 the 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).

    consolidate data from multiple worksheets in a single worksheet with consolidate option

    • 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

    consolidate data from multiple worksheets in a single worksheet add formatting

    Finally, you have consolidated worksheet.

    What Else in Consolidate Option

    Consolidate is a handy tool to consolidate data from multiple worksheets in a single worksheet.

    But, we have a lot of other things in this option.

    Let me explain it one by one.

    • Function: You can select a function from this drop down menu (sum, count, average, etc).
    • Reference: Select data range from the worksheets you want to consolidate.
    • Browse: If want to add a worksheet which is in another workbook, you can use this option.
    • Add & Delete: Add or delete selected reference to the list of sheets to merge.
    • Top Row: Use top row as a label.
    • Left Column: Use left column as a label.
    • 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 consolidate sheet.

    consolidate data from multiple worksheets in a single worksheet with links to source

    Conclusion

    In the end, I just want to say, it’s an awesome option to use to consolidate data. The best part is you can capture N number of worksheets with it.

    You also have different functions as well.

    Otherwise, if you go with the manual method to sum all the worksheets, it’s a pain.

    Have you ever did mistakes like I did?

    Please share with me in the comment box.



    • Lisa Gordon Delgado

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