Microsoft Excel is an excellent tool for organizing and consolidating your data and information. It works more effectively if you have more data to arrange in one excel worksheet file. You will see everything in its proper order and a clean form.
Working on Excel will allow you to create multiple workbooks if you want to categorize your data and separate spreadsheet entries that belong to the same category. But, there comes a time when you need to merge all these data and combine them in one MS Excel sheet file.
Fortunately, Microsoft Excel enables you to integrate all data from two or more sources into one sheet. You can use the Consolidate command to merge all information from every workbook into one master worksheet. It works when you want to see everything in one place or dealing with regional and local data entries.
How to Consolidate & Merge Multiple Data from Different Microsoft Excel Files into One Spreadsheet
There are two ways to consider when you want to consolidate and merge Excel sheets data into one master spreadsheet file. You can combine and merge them by position or category.
Not all spreadsheets work the same in terms of the order. If your data is not arranged similarly when it comes to the layout but has the same labels, you can consolidate them by category. But, the entries have the same order and labels, you can consolidate them by position.
- First, open the spreadsheet where you want to become the master sheet or where you want to consolidate all the data.
- Next, choose the cell where you want to add the combined data.
- Go to the main menu and navigate to the Data tab.
- Head over to the Data Tools section.
- Click the Consolidate button or the two-boxes-merging-to-one-box icon.
- Under the Function option, select the Function that you want to use.
- Go to the Reference section, where you need to add all the data you want to consolidate.
- Navigate to the first cell and highlight all the cell ranges containing all the data you want to consolidate. The Consolidate window will remain open as you move from one sheet to another.
- After highlighting all the cells, click the Add button.
- Go to the next sheet and highlight all the cells. Click the Add button.
- Do this to all the cells in your workbook.
- Once done, go to the Use labels in section.
- Check the boxes next to the Top row, Left column, and Create links to source data. You can do this if you are consolidating by category. You can skip this part if you are consolidating by position.
- Click the OK button.
How many spreadsheets do you need to consolidate into one master sheet? We are curious to know! You can share your answer with us in the comment section below.
