How to Consolidate Data from Multiple Worksheets into One in Excel

How to Consolidate Data from Multiple Worksheets into One in Excel

In this tutorial, you’ll master the technique of consolidating data from various worksheets into a single worksheet. This method is compatible with all versions of Microsoft Excel, but we’ll primarily be referencing the latest version.

Why Consolidate?

One of the most efficient ways I’ve discovered to manage data in Excel is the “Consolidate Data” feature. It effortlessly merges data from different worksheets into one.

In this tutorial you will learn to Consolidate Data From Multiple Worksheets in a Single Worksheet. This tutorial is relevant for all versions of Microsoft Excel. However, latest version has been refer

One of the best Excel options which I have learned about managing data is to “Consolidate Data From Multiple Worksheets in a Single Worksheet”.

Demo File Available for Download

To aid your understanding, we’ve provided a sample file containing four distinct worksheets, each representing a different zone. All the zones share a similar data structure. Download the demo file here.

 

Step-by-Step Guide to Consolidating Data:

 

  • Setting the Scene: Begin by inserting a new worksheet. Name it “Total” or your preferred title. Navigate to cell A1 of this worksheet.

  • Accessing the Tool: Head to the Data Tab → Data Tools → Consolidate.

  • Initiating the Consolidation: Once you select “Consolidate”, a window will pop up. (Details about this window will be elaborated on later).

  • Selecting Data Range: In the “Reference” box, pick the data range from your first sheet (e.g., the “north zone”).

  • Adding More Data: Hit “Add”. Repeat this process for each worksheet you intend to merge.
  • By using the same method, add references from all the four worksheets.

  • Configuration: Ensure you tick “Top Row” and “Left Column” at the bottom of the window. This is because our labels reside in the topmost row and leftmost column.

  • Completion: Click “OK”. Voilà! Your data is now merged.

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

Styling Your Table: Add a table title in cell A1. You can employ the “Format Painter” to replicate the formatting from the original worksheets.

Finally, you have a worksheet with consolidated data.

 

Digging Deeper into the ‘Consolidate’ Tool:

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 it one by one.

  1. Function: Choose functions like sum, count, average, etc., from the dropdown menu.
  2. Reference: Specify the data range from the worksheets to be consolidated.
  3. Browse: To merge a worksheet from a different workbook, use this option.
  4. Add & Delete: Easily add or remove references for sheet consolidation.
  5. Top Row & Left Column: Use these as labels.
  6. Create Links To Source Data: If you want dynamic linking to the original data, select this. It’s handy when your source data might undergo changes post-consolidation.

 

Final Thoughts:

The “Consolidate Data” feature is a game-changer. It’s a swift and efficient way to combine information from numerous worksheets. Manually consolidating can be tedious, involving repetitive tasks or complex formulas. With this feature, Excel does the heavy lifting for you. Happy consolidating!

Free Excel Templates

Student-Comprehensive-Progress-Report

Student Comprehensive Progress Report

Student Comprehensive Progress Report Excel Template with chart analyzes subject-wise …

2-page-monthly-planner

Monthly Planner 2 page

Our free printable Monthly Planner 2 page template was designed for …

Marketing Dashboard Template in excel

Marketing Dashboard

Transform Your Marketing Strategy with Our Free Editable Excel Marketing …

Latitude_Longitude_Converter_V1.0

Latitude Longitude Calculator

To use this latitude longitude calculator is simple, just fill …

We Create Your Life Easy With Our Free Excel Templates & Spreadsheet Dashboards

Use free Excel Templates, Tools and Dashboards to create and draft a professional looking dashboards and computation sheet for your personal and business use. You can Explore Calendars, Activity Planners, Invoice Templates, Sales Forecast Sheets, Budget templates and various business information delivering Templates in Excel and Spreadsheet at XLSX Templates. 

These templates, dashboards and tools are useful in various occasions. You can also customize these templates as per your requirement. Modification of fields and data source helps in making the template more relevant.

Learn Microsoft Excel : Blogs and Articles

Learn about various tips and tricks in Microsoft Excel and Spreadsheet. Create best templates and dashboards using free tricks and tutorials in excel and spreadsheet. These tutorial posts are useful for everyone who wants to master the skills in excel and spreadsheet.