HomeHow ToHow to Combine & Consolidate Data from Multiple Microsoft Excel Spreadsheets

How to Combine & Consolidate Data from Multiple Microsoft Excel Spreadsheets

Author

Date

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.

Merge Multiple Data from Microsoft Excel Files into One File
  • 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.

Related

More From

How to Use & Join FaceTime Calls with Android Phone or Windows PC Device

iPhone users are familiar with FaceTime. It is like iMessage, where communications come naturally. But, if you are an Android mobile or Windows PC...

How to Update Software on Samsung Smart TV

Buying a Samsung Smart TV gives you access to online TV channels and streaming services. But, to keep your smart TV working properly, you...

How to Completely Enable or Disable Allow Wake Timers Settings in Windows 10

Our Windows 10 PC goes into hibernation or sleep when it is not in use. It will save energy and resources, especially if you...

How to Download and Watch Netflix Movies and Shows Offline

Watching on Netflix can give you hours of pure entertainment. But, what if you have to travel somewhere without any network connection? Streaming on...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Recent Articles

Stay Connected

Trending