How to use data consolidation in Excel: a complete guide

By Indeed Editorial Team

Published 6 April 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

A common problem in Excel arises when merging different formats from multiple worksheets to aid uniformity, better visualisation and analysis. Data consolidation helps solve this problem by allowing you to access data sources quickly when the need arises. Understanding how to use this feature is a great way to improve your skills in Excel. In this article, we discuss what data consolidation is, its benefits, how to consolidate data in Excel, the challenges of using this feature and ways to manage risk while using the consolidate feature.

What is data consolidation in Excel?

Data consolidation in Excel is the process of combining information from different worksheets or workbooks into a single one. In other words, it helps you to summarise information from separate worksheets into a master worksheet. Often, businesses collect data to produce useful information that may help them serve their clients better. For example, you may collect data on how customers react to a specific product, how well a landing page is doing or how often you make sales. Companies collect and analyse many other data to make informed decisions and develop innovative products or services when necessary.

When data from various aspects of an organisation appear in separate worksheets, there may be instances of duplication, resulting in data security issues and additional costs due to multiple software licenses. Microsoft Excel has a feature that allows you to gather information from different worksheets to produce a consolidated one. This feature makes it easier to analyse and manage data.

Benefits of using the data consolidation feature

Since the goal of many companies is to run a profitable business, data consolidation offers several benefits, making goals like this more achievable. These benefits may include:

Reduced operational costs

After a period of time, some organisations find themselves with multiple data centres when they may only require one or two. Often, various data centres exist not for business continuity but because businesses have not made efforts to rationalise the number of these centres. This measure may lead to additional cost of maintenance incurred through duplication of work, additional software licenses and the cost of staffing. The data consolidation initiative quickly solves these problems by concentrating computing resources into a single or small number of data locations. This effort may save organisations a significant amount of time and money.

Increased productivity

Productivity doesn't mean completing a set amount of work but working cleverly to achieve set goals. The presence of many data centres may encourage busyness rather than productivity. Data consolidation in Excel is a great strategy to ensure productivity as it helps to integrate data resources into a single location. This effort can eliminate time wasted when searching for data resources scattered across several worksheets.

With the application of this strategy, a company can spend less time searching for data and focus on solving other important business issues.

Improved decision-making

The massive amount of data generated daily serves as a tool for developing customer retention strategies in companies. Data consolidation helps analyse these data sets precisely by providing a platform for readily viewing and comparing data. A company can then use it to make changes for the market's growing needs and customer base.

Increased agility

Speed and adaptability are fast becoming essential qualities of a successful business. The consolidate feature helps companies quickly access data whenever they need it to speed up decision-making. Instead of spending too much time searching for information spread across various data sources and storage systems, the feature can make information more readily available.

Related: The importance of good communication in organisations

Methods of data consolidation in Excel

Two methods of consolidating data in Excel are:

  • By position: This means that data in the source areas have the same order and use the same headers. You may use this method to consolidate data from a series of worksheets.

  • By category: This method consolidates data from worksheets with different layouts but the same data header.

How to combine data from multiple worksheets in Excel

Here are the following steps to consolidate data in Excel:

1. Select a new worksheet as your master worksheet

Select a new sheet in the workbook as your master worksheet then open the worksheets you want to consolidate by clicking on the recent files you have used on the front page of Microsoft Excel. If you can't find the files you want to merge, click Open in the sidebar to the left. Click Browse, then navigate to the files you want to consolidate and open them. Ensure that you list the header on each spreadsheet in a consistent format with the rows and columns in the exact location, leaving no blank row or column anywhere.

2. Choose a random cell

In the master worksheet, select the upper-left cell of the region where you want the consolidated data to appear. You may want to rename the master worksheet at this point. This step may help you find the file more easily later on.

3. Click 'Data'

Go to data and click on the consolidate button from the Data tab. Afterwards, click on the consolidate icon in the data toolbox. You may do this on the ribbon.

4. Select a mathematical function

Use the drop-down menu below to choose the summary function you want Excel to consolidate the data. By default, the basis is a sum, but you can change this to what's suitable. For example, you can select count, average, min, max or any other function you wish to use.

Related: How to calculate mean in excel and why it's essential

5. Click on the reference area

In the reference box, click the collapse button and select the data in the worksheet. In the worksheet that includes the data you want to consolidate, choose the data and then click the expand dialogue button on the right to return to the consolidate dialogue box. If you wish to merge sheets within the same file, click the arrow icon to the bar's right below References. At the same time, if you desire to consolidate data from a separate file, click browse and select a file with data you want to merge and click Open.

6. Click 'Add'

Select Add at the right of the All references list box. This action adds the reference and range you select to the list of references. Repeat this by clicking on the next sheet, highlighting the data and clicking on the 'Add' button for features you wish to consolidate.

7. Update automatically or manually

If you want Excel to update your consolidation table automatically when the source data changes, click on the checkbox next to create links to the sources data box. If it is updating too often, you may want to change to manual consolidation. You can choose either of the two options depending on what you want to achieve.

8. Click 'Ok'

Finally, press 'Ok'. When you select the 'Ok' button, Excel generates the consolidation for you. You may notice that a summary of all data appears in your master worksheet.

Related: What are project initiation documents and their uses?

Challenges of using the consolidate feature

Some limitations that accompany the use of the consolidate feature include:

  • Data security issues: People may use any available technology to access a spreadsheet file even if it has a password.

  • Time: It takes a long time to consolidate data from various departments, and it takes even more time to trace data to solve issues that may arise in the future.

  • Collaboration issues: The feature may not allow users to work simultaneously. Sometimes, working together with a third party on a spreadsheet may be necessary. Breaking consolidation into multiple Excel files may result in broken links.

  • Lack of adequate resources to manage consolidation.

Related: What does collaboration mean in the workplace?

How to minimise risk when using the consolidate feature

It may be almost impossible to eradicate problems that arise from using the consolidate feature, but the good news is that you can minimise risk. The following are ways to minimise risk when planning to use the consolidate feature:

  • Conduct research to find out how much cost it may incur: This is a great way of minimising risk before starting a new project.

  • Invest in a joint enterprise resource planning (ERP) solution: ERP software provides an integrated and continuously updated view of main business processes using common databases to help track business resources, such as cash, raw materials and production capacity.

  • Document existing resources: In case of data loss or the need to fix errors in the future, documenting data sources can help make data traceable and aid data recovery.

  • Establish a quality communication routine: It may be necessary to communicate regularly with personnel involved in managing data sources. Proper communication can help to create a smooth workflow within an organisation.

Please note that none of the companies mentioned in this article are affiliated with Indeed.

Related:

  • How to make a graph in Excel: a comprehensive guide

  • What Is an XML File? (Plus Frequently Asked Questions)

  • How to analyse data: definition, steps, benefits and skills

  • How to create a budget in Excel (plus budgeting tips)

  • A complete guide to the Excel MOD function for professionals


Explore more articles