What Is Data Consolidation?: Definition & Using Excel

In general, data consolidation means combining information from separate sources into a single report, screen or spreadsheet. For example, to get the total inventory value from five different warehouses, you could look at five different reports and add them by hand, or you could consolidate the information into a single report. Microsoft Excel has a feature that automatically pulls information from different spreadsheets to produce a consolidated one.

Tip

When data is consolidated from several sources, such as reports or spreadsheets, it puts needed information in one place, saving time compared to looking it up in many different places.

To define what "consolidated" means, understand that the data with which you run your business may come from different sources. If all of it is in spreadsheets, you might have dozens or even hundreds of them. Alternatively, you might keep your data in FileMaker Pro, Access or SQLite databases, or you may have a hodgepodge of databases, spreadsheets and mobile apps.

When it’s time to run your monthly reports, do your taxes or handle some other important business function, it can be a time-consuming chore to chase down essential data if it’s scattered in multiple places. With consolidated data, however, you save precious time and reduce costly errors. The computer does the work of hunting down the details for you.

Data consolidation involves adding a process to your software that ties the specific pieces of needed information together into a single unit. Excel, for example, has a built-in consolidation feature you can use to combine data from multiple spreadsheets.

Before you do a consolidation in Excel, first open all the spreadsheets from which you want to pull data. You also need to select or create a separate destination spreadsheet and click in the top-left corner of the area to which you want the information to go. Then, perform the following steps:

• Find the Consolidate feature in the Data tab in the Data Tools group. Excel will display a list of functions.
  
  
• Select a function, such as sum, with which to consolidate the source data.
  
  
• Select the data range in each source spreadsheet. Click the Add button next to the All References listbox to add that set of data to the consolidation.
  
  
• Click the Create Links to Source Data checkbox if you're combining separate spreadsheets. Doing so will automatically update the consolidated data if the source data changes.
  
  
• Click OK when you’ve finished selecting the source data to see the results in the destination spreadsheet.

In Excel data consolidation, the consolidation function is the math you want performed on your source data to produce the consolidated data. In many cases, you use the sum function to create totals.

For example, say you have a spreadsheet of item sales and you want the total dollar amount. Note that the consolidation function is a drop-down list that in addition to sum also includes average, count, minimum and maximum among others. These are useful when you need to find an average selling price, the item with the lowest shipping weight or the total number of transactions in a month.

The pivot table is another time-saving tool available in Excel, which is similar to data consolidation. A pivot table automatically summarizes spreadsheet data using criteria you select. It can select a subset of data, such as a particular month, from specific columns you pick, producing subtotals and totals. It can also pull information from external sources, such as Access tables.

Though many small businesses keep all their data in spreadsheets, databases are another powerful data management tool that allows many users to share information at the same time. As with spreadsheets, data consolidation is also an issue with databases. A typical way to consolidate a database is with a view, which combines data from several different permanent tables on the fly into one temporary table.

Data consolidation tasks can be technical and tricky, especially when your sources come from several types. For example, dates in a spreadsheet and dates in a database can have completely different formats. Sometimes, the task may be too difficult to accomplish with Excel alone.

A software developer can create a custom app to handle low-level details such as these. Though it might be pricey, a custom data consolidation app can be worth it, especially if you have large amounts of data.