Working with separate worksheets can be stressful, especially if they contain important data. You can quickly solve this issue by consolidating data from different worksheets in a master worksheet. Consolidating data means that you assemble data for easier updating. Therefore, using Excel's Consolidation tool, you can combine and summarize data from multiple files or sheets.
The article below will explain to you different ways of consolidating data in Excel using other methods. Let's get started.
Using Excel's Consolidation tool to consolidate data
1. Open the worksheet you want to consolidate your data. After opening the spreadsheet, ensure the data is in a consistent list format in every spreadsheet
2. Open a new empty spreadsheet file that will act as your master spreadsheet. It is advisable to do this when you are working with data from different files. In case you do not want a new spreadsheet on your current worksheet, press the plus (+) button to create an empty new worksheet. The button is at the bottom of the window.
3. In your master spreadsheet, click on the upper-left cell of the area where you want your consolidated data to appear.
4. On the main ribbon menu, click on the Data tab.
5. Under the Data Tools group, click on the Consolidation icon to display a dialog box.
7. Under the Function section, select one from the drop-down list. Make sure that Sum is chosen here. Note that you can use any function in the consolidation option.
8. In the Reference box, click the collapse button to shrink the panel and select the data in your worksheet. After this, click on the same button to expand the dialog box panel. Note that you can manually type the reference to the cell range on your own.
9. In the 'All References' section, click the Add button and repeat the step above to add all the ranges you want to consolidate.
10. Click to enable the Create links to the source data box. It will ensure that any changes made in your original worksheet are reflected in the master spreadsheet. Please uncheck the box when you prefer to update it manually.
11. If your data has a column or row label, on the 'Use labels in' section, check the 'top row' and 'left column' checkboxes. Remember that if you don't want labels, do not check the boxes.
12. Click OK to consolidate your data in the master spreadsheets.
Conclusion
I believe the above steps are straightforward and easy enough to follow when you need to consolidate your data in Excel. Working with different worksheets should not be a traumatic experience when you can easily consolidate all your data in a master worksheet. I hope you find this information helpful.
Apart from using the Consolidation tool to consolidate data in excel, you can do this using the below methods.
- Copy and Paste
- Using the Power Query Editor
- Move or Copy Sheet feature.