How to consolidate data in Excel: Everything you need to know

Excel is still the workhorse for most finance and accounting teams. Entire businesses can be run effectively and efficiently with the expert use of spreadsheets. Consolidation is a tool in Excel that lets you apply formulas across multiple sources and consolidate that calculated information. In financial accounting, this function can alleviate slow manual calculations, automate some tasks, and consolidate your data for better reporting.

What does it mean to consolidate data in Excel?

Consolidation in Excel is a function that allows you to pull data from multiple sheets or tables and combine it with a formula. You can choose the exact cells, the function, and the labels to be used in the end product.

Why might you need to consolidate data in Excel?

One example is the creation of a total expense sheet. By consolidating multiple expense sheets with a sum formula, you create a single table with the totals of all the others. Because this is a formula, any changes in the sources will be reflected in the total.

How to consolidate data in Excel

Excel has a built-in consolidation feature. This lets you quickly take large amounts of data and apply a function to it. Keep in mind that this functionality is currently only available in the desktop version of Excel. For those working with Excel Online, you can achieve similar results using a Pivot Table. Microsoft’s documentation on the consolidate function is a little thin, so let’s get a little more detail.

Introdução

First, select the top left cell of the destination for the consolidated data.

Next, select Data from the top bar.

And find Consolidate.

This will bring up a little Consolidate window with some options.

For all the following examples, these first steps will be the same. The way you select your references and labels determines how the data will be displayed at the end.

Examples of consolidating data in Excel

Here are a few examples of how to use the consolidation function in Excel for different use cases.

Consolidate data from multiple rows

Here we have a simple list of items, and their quantities. Though we have only two columns here, this can work with more than one column.

Note that the “Item” column has many disordered repetitions. To quickly identify the total quantity of each item, we’ll consolidate all these rows.

Since this is all going to happen on the same sheet, we’ll copy over our column names and select D2 as our starting cell for consolidating the data.

Selecting Data > Consolidate will bring us to our Consolidate window,

When the window opens, your cursor will be in the Reference field by default. If it isn’t, click inside the empty Reference field before selecting your reference cells or entering them manually. For this example, we manually selected the cells before clicking Add.

Lastly, we chose to use the labels in the Left Column before clicking OK.

The resulting consolidation gives us a quick and easy summation of the selected cells, organized by type.

Consolidate data from multiple sheets

To consolidate data from multiple sheets, we’ll start by creating a new sheet that will be the destination for our consolidation. Once this is created, we’ll follow the steps above until we have the consolidate window open as shown below.

With the consolidate window open, make sure your cursor is in the Reference field and navigate to the first sheet in the workbook you wish to include, select the cells as before, and click Add. However, instead of clicking OK, go to the next sheet and repeat this process, selecting and adding all the fields you need before clicking OK.

Once all of the desired data has been added, select the Labels you wish you use, in this case, Top Row and Left Column, and click OK.

This should automatically return you to your destination sheet, as well as populate it with your consolidated data. We can clearly see total numbers for our three employees.

Consolidate data from disconnected workbooks

To consolidate between separate workbooks, there are two methods. The first is by using the Browse option in the Consolidate window:

This will let you use your file explorer to select an entire workbook to use.

If you only need to use a portion of data from another workbook, you can open the two workbooks at the same time, open the Consolidate window from the destination workbook, and select your references in the other:

Here, Pam's data has been moved to Book 1 but has been selected in the original workbook. You will know that the selection is outside of the workbook when the name of the workbook is referenced first. If the selection is from the same workbook, it will be the Sheet name that appears first in the reference address.

Bonus: Consolidate data with Prophix One™

While Excel is the backbone of business finance and accounting, it doesn’t mean that ‘the way it’s always been done’ is necessarily the best or most efficient way. Managing massive amounts of data with spreadsheets, even when using all of the formulas and features it has to offer, still leaves room for improvement.

A purpose-built financial performance platform like Prophix One offers data integration and automation so you don’t have to do the manual work of consolidated data from spreadsheets. Instead, you can take your unwieldy workbooks as your data source, integrate with Prophix One, a Financial Performance Platform, and reap the benefits of an agile, efficient, and automated process.

Challenges of data consolidation in Excel

As useful as Excel is, there are limits to what the software can do all on its own. The formulas and consolidations we’ve seen here are reasonably simple. Though they can be applied to much larger data sets, they can quickly become difficult to manage. Consolidating financial statements in Excel presents several challenges:

• Manual errors and inconsistent data formatting.
• Handling multiple sources or business entities can quickly become complicated.
• Managing large data sets and frequent updates manually takes a lot of time.
• Complex formulas are difficult to track back if there are broken links.
• Managing multiple users and file versions manually is difficult and time-consuming.
• Manual processes and limited system integration means there is little opportunity to leverage automation.

Common questions about using Excel to consolidate data

The data consolidation function lets you create amalgamated data very quickly. These functions are not limited to one sheet, or workbook, allowing the possibility of using an Excel consolidation with data from multiple sources. These are best suited for small sets of data, and for tasks that may not be repeated frequently. For repeat tasks, you may want to consider using Excel’s Pivot Tables.

What if the data sources have different items?

Depending on the kind of consolidation options you choose, items can be treated differently. In this example above, the item names were used as the labels for the total count, but the different items remained separate in the consolidated table. If the items themselves are different, the Excel consolidation will keep each different item discreet.

This can be a nuisance when the item names are nearly the same. Fortunately, Excel will successfully consolidate items with capitalization differences.

How to consolidate data from multiple columns?

Columns are oddly different from rows when it comes to consolidating in Excel. In fact, they are much easier. Instead of using the consolidate function like the above examples, all you will need to use is a formula.

First, select the cells you wish to calculate, as well as the empty cells you wish to fill with the result of the calculation. The selection below will add all the cells in a row and total them in the F column.

Once the cells are selected, click Formulas on the top bar, and then select AutoSum.

This will give you the summation of the rows in the empty cells you included in your initial selection.

If it is not a sum you wish to calculate, you can select a different operation, like Average, from the drop-down menu below the AutoSum icon.

Solve the challenges with Excel using Prophix One™

The challenges you face when managing huge sets of data in spreadsheets can be solved. Prophix One is a financial performance platform that integrates with your existing data sources, like Excel sheets, and provides in-depth automation and analysis tools that accelerate every stage of your finance and accounting workload. With data integration to bring all of your data together in one place, there is no more hunting down the reports you need. And with tools and automation to turn that data into insights, reports, forecasts and more, Prophix One gives your financial and accounting teams the confidence they need to make data-driven decisions.

Conclusion: Using Excel to consolidate data vs. Prophix One™

Excel is a powerful tool in your workspace, but its lack of in-depth automation means your teams still need to do a ton of manual work. With a purpose-built platform like Prophix One, you can integrate your data sources and automate your error-prone and tedious tasks, freeing up your finance team to invest themselves in value-add activities that will continue to propel your business forward.

Empower your financial teams to do more and do it better. Want to see how? Check out this video!