In this article, you’ll learn how to consolidate worksheets in Microsoft Excel.
Data Consolidation is a feature in Microsoft Excel that lets you combine data together from various worksheets into a single worksheet so you can work on them at the same time.
How to consolidate Worksheets in Microsoft Excel?
To consolidate your data, follow the below steps.
Go to the Data tab and select Consolidate option Data Tools group.
NOTE: You can consolidate data only if there is data entered in various worksheets.
A dialog box opens up when you select the Consolidate option.
Now in the References field add the data from whichever worksheets you want.
You can add the data by selecting and dragging the cursor over the cells and clicking on Add to add the data. Similarly to add data from other worksheets.
The function has 11 various functions such as sum, product, count, average, maximum, minimum, etc… These are all the operations that can be performed during the consolidation of the data.
Checkboxes Top row and Left column under User labels in, if you want to point to the locations of the labels i.e. the cell headers.
Checkbox Create links to your source data if you want your consolidated data to be updated automatically when you make changes in your actual data.
Click on OK to save changes and proceed.
This is how the consolidated data looks like after the process completion, here we’ve summed up the total sales of the flowers of three months.
This is how you consolidate data in Microsoft Excel.