How to Consolidate Data in Excel (2 Methods)

• Unito home /
• Blog /
• How to Consolidate Data in Excel (2 Methods)

Published in Collaboration and Productivity on 21/06/2023, last updated 08/11/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.

FAQ: How to consolidate data in Excel

How do you consolidate data in Excel?

You can consolidate data in Excel in one of two ways:

• Use Excel’s built-in consolidate feature: From the Data menu, hit the Consolidate button. Make sure the Function field is set to Sum, then add the data you want to consolidate in the Reference field. Choose the data range you want to consolidate from and hit Add. Once that’s done hit OK and you’ll have consolidated your data!
• Use an integration solution: Unito’s integration for Excel creates a two-way relationship between multiple Excel workbooks. That means any time you update data in one, it’ll automatically be updated in the other. It’s the best way to collaborate across spreadsheets without manually copying and pasting data over.

How do you combine data from multiple sheets in Excel?

There are a few ways you can do this:

• Using Excel’s built-in consolidation feature, though this requires some manual work.
• Using automation solutions like Zapier can automatically push data between spreadsheets, but they won’t be updated automatically.
• Using two-way sync solutions like Unito, which automatically updates all your data no matter which spreadsheet it’s in.