Data Import & Export

How to Use Excel Consolidate to Combine Spreadsheets

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to learn how to merge or summarize data from various worksheets or workbooks into a single master layout using the Excel Consolidate feature.

Product: Excel
Device & OS: not provided
Scenario: Combining multiple datasets into a centralized summary spreadsheet for reporting or data analysis.
Observed behavior: The user must properly configure the destination worksheet, specify accurate source references, map row/column labels, and select a calculation method like Sum, Average, or Count.

Before you start

Before consolidating your data, ensure that all source spreadsheets are open and organized with consistent row and column labels to make the merging process seamless.

Solution 1Recommended

Use the Consolidate Feature to Merge Spreadsheet Data

Follow these steps to specify your destination sheet, define multiple source ranges, and apply a summary function to combine your data accurately.

Excel's Consolidate tool allows you to pull data from separate areas into a unified table. You can consolidate by position if your data is arranged identically, or by category if your data uses identical row and column labels.

Prepare the destination worksheet

Open the destination worksheet where you want the consolidated data to appear. Click on the upper-left cell of the area where you want the summarized data to be placed. This can be in the same workbook or a completely new one.

Open the Consolidate dialog

Navigate to the 'Data' tab on the top ribbon menu and click on the 'Consolidate' button located in the Data Tools group.

Select the summary function

In the Consolidate dialog box, click the 'Function' drop-down list and choose the desired calculation method, such as Sum, Average, Count, or Max.

Add source data references

Click inside the 'Reference' box, then navigate to your first source worksheet and highlight the data range you want to include. Click the 'Add' button to move it into the 'All references' list. Repeat this process for all additional worksheets or workbooks.

Configure labels and finalize

If your source data includes headers that you want to carry over, check the boxes for 'Top row' and/or 'Left column' under the 'Use labels in' section. Click 'OK' to execute the consolidation.

Dynamic Data Links: If you want the consolidated summary to update automatically whenever the source data is modified, check the 'Create links to source data' box before clicking OK. Note that this option is only available if the source data is on a different worksheet.

Efficient Data Consolidation

Consolidate Spreadsheets Quickly with WPS Office

WPS Spreadsheet provides a highly compatible and user-friendly Data Consolidate tool, allowing you to combine multiple worksheets effortlessly without complex formulas.

1. Open the master workbook: Launch WPS Spreadsheet and open or create the destination worksheet where the combined data will reside.
2. Access the Consolidate tool: Go to the 'Data' tab on the top menu ribbon and click the 'Consolidate' icon.
3. Select function and ranges: Choose your preferred mathematical function (e.g., Sum), click the reference selection box, highlight your source data ranges from other sheets, and click 'Add' for each one.
4. Generate the report: Select the appropriate label checkboxes to keep your row and column headers, then click 'OK' to instantly generate your combined report.

Fully compatible with Microsoft Excel (.xlsx and .xls) file formatsIntuitive interface for managing multiple data references across workbooksFree, lightweight, and fast spreadsheet software alternativeSupports advanced summary functions and dynamic data linking

microsoft office alternative - wps office

Frequently Asked Questions

Why is the Consolidate button grayed out in my spreadsheet?

The Consolidate feature may be disabled if you are currently actively editing a cell, if the worksheet is protected, or if multiple sheet tabs are grouped together. Press the Esc key to exit cell editing or unprotect the sheet to restore access.

Can I consolidate data without headers or labels?

Yes. If your data ranges are structured identically and in the exact same order across all sheets, you can consolidate by position. Simply leave the 'Top row' and 'Left column' label boxes unchecked in the Consolidate dialog.

How do I consolidate data from different workbooks?

First, ensure all the source workbooks and your destination workbook are open. In the destination sheet, launch the Consolidate tool, click the Reference box, and use your mouse to navigate to the open source workbooks to select and add the required ranges.

What happens if my source data changes after I consolidate?

If you checked the 'Create links to source data' option during setup, your consolidated table will update automatically when source numbers change. If you did not check this option, the consolidated data remains static, and you will need to run the tool again to reflect updates.

Excel & Data Problems