How to Combine Data From Multiple Excel Sheets (2 Methods)

• Unito home /
• Blog /
• How to Combine Data From Multiple Excel Sheets (2 Methods)

Published in Teamwork on 21/06/2023, last updated 19/03/2024.

Whether they’re reports, databases, or project documents, spreadsheets are everywhere. Your team probably has links to dozens of them floating around, and whole organizations run on them. That means there’s a ton of data scattered throughout these spreadsheets, and anyone working with them will need to know how to combine data from multiple Excel sheets into a single place.

Here are two methods for doing that; one of them relies on built-in Excel features and another needs a little help from Unito.

First method: Consolidate data from multiple Excel sheets

Microsoft Excel — at least the desktop version — has a nifty built-in feature for combining data from multiple sheets: data consolidation.

While this method does have some limitations, it’s great when applied to specific scenarios. In this example, we’ll be consolidating deal values from multiple sales territories into a single master sheet.

Here’s how it works.

First, you’ll want to find the Consolidate button, which is in the Data menu.

In the Consolidate dialog, you’ll want to fill in the following information.

In the Function field, make sure to leave it as Sum.

The Reference field is where you’ll pick the data you want to consolidate. First, hit the arrow next to the Browse button: this will collapse the dialog and allow you to choose a data range in another spreadsheet.

So after collapsing the dialog, go to the spreadsheet you want to pull data from and highlight your data.

Once you’ve done that, hit the Add button. This will add your selected range to the All references list, from which all data will be pulled for your consolidation.

Now just do the same for the rest of the data you want to consolidate.

After setting up your references, there are a few more options you can set up.

• Use labels: If you want labels to be copied over from your source data, you can check these off. This is useful if you haven’t already replicated these labels in your consolidated spreadsheet.
• Create links to source data: One of the neat things about this method is you can set up a consolidation that automatically updates your master sheet whenever data in your reference sheets changes.

When everything’s set up the way you need it, hit OK. Here’s what you’ll get.

Since Excel’s Consolidate feature only supports numbers, you’ll get a sum of the values from your reference sheets. Note that any other data — like the contact info for customers in this example — aren’t added in.

Limitations of this method

So Excel’s consolidate feature is pretty simple. Click a few buttons and highlight some data and you’re done. But is it necessarily the best method for everyone? Here are some potential dealbreakers.

• Only available for Excel’s desktop version: As easy as this method is, it’s not necessarily suited for everyone. That’s because Excel’s consolidate feature is only available on the desktop version of the app. So if you’re using Excel on the Cloud, you’re out of luck.
• Only supports numerical data: While this method is great for rolling up financial data, product counts, and other numbers, it’ll do absolutely nothing for any other data.
• It only works in Excel: If you need to pull data from any tool other than Excel, this method isn’t going to work for you.

For some situations, consolidating data natively within Excel will work just fine. But if you need to sync more than just numerical data — and you don’t want to be limited to doing it in Excel’s desktop app — there’s a better method for you.

Second method: Combine data from multiple Excel sheets with Unito

Unito is a no-code workflow management solution with some of the deepest two-way integrations for the most popular tools on the market, including Excel, Google Sheets, Airtable, Notion, Smartsheet, and more. With a single Unito flow, you can automatically sync data from one Excel spreadsheet to another, meaning it just takes a few to combine data from multiple Excel sheets into one master sheet.

Here’s why Unito is the best way to do this.

• It works with Excel cloud: If you’re only using Excel’s online version, then Unito is the best way to sync data between spreadsheets.
• Unito supports all kinds of data: You won’t be limited to numbers. Whether you need to sync contact information, comments, or important updates, Unito has you covered.
• It supports more tools: Obviously, native Excel features usually only work with Excel. But Unito has over 30 integrations that cover project management tools, contact management platforms, and more That means you can centralize data from multiple sources, not just spreadsheets.

And that’s it! Now you just need to launch your flow and watch as Unito automatically consolidates data from multiple spreadsheets into one Excel master sheet.