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.
- Function: Choose functions like sum, count, average, etc., from the dropdown menu.
- Reference: Specify the data range from the worksheets to be consolidated.
- Browse: To merge a worksheet from a different workbook, use this option.
- Add & Delete: Easily add or remove references for sheet consolidation.
- Top Row & Left Column: Use these as labels.
- 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!
