Master the Excel Consolidate Feature in 10 Mins [Free Guide]

To use the Excel Integration feature, you need to go to the file path Data > Data Tools > Integration. A window will then appear from which you choose how to integrate the data.

If you need a little more help, don’t worry. The following guide provides step-by-step instructions with screenshots on how the integration feature works in Microsoft Excel. Read this article to master this aspect of spreadsheet creation.

What Is the Excel Consolidate Data Feature

Consolidating data in Excel allows you to take data from multiple worksheets and edit it into one centralized worksheet. This creates a “master” worksheet where you can access the data summarized in a clearer format, making the information much easier to understand.

However, in order to integrate the data into Excel, certain conditions must be met. There must be one row and one column of labels and headings. The tool may not work as intended if the table has multiple labels. While this seems like a serious drawback of this function, most tables in your spreadsheet may already follow a specified format.

You can also add a link to the source worksheet by using Excel’s integration function. This allows the consolidated table to be updated when the information in the source worksheet is updated.

Why Use Data Consolidation in Excel?

Excel’s data consolidation features help you organize your data.

Many financial analysts spend most of their day collecting and organizing information from various sources. One such example is creating an organizational budget by merging accounts from separate worksheets into her single worksheet. This essentially frees you from the hassle of running the process manually.

The true potential of Excel’s consolidation function is revealed when you use it to combine large data sets. You could copy the information and paste it into a new table, but this would require a lot of manual work and time. Another advantage of using the merge function is that the data in the merge worksheet is automatically updated when changes are made to the source worksheet.

Excel is much easier to integrate than other spreadsheet programs. However, both Google Sheets and Excel are great spreadsheet apps that offer a lot of functionality to their users. Here’s a detailed comparison between Google Sheets and Excel, and which spreadsheet software may benefit you in 2023.

Ways to Consolidate in Excel

There are multiple ways to consolidate data in Excel. Before we look at how to consolidate data in Excel, let’s look at some ways to consolidate data in Excel.

1. Position: This technique of consolidating in Excel works best when the worksheets in your workbook follow a similar layout. This means using a specific template for your data or using the same rows and columns. The information in the spreadsheet should be in a similar arrangement and the details should be in the same place. Use the By Position method to move values ​​from the source sheet to the target worksheet and add labels.
2. Category: This method works best when the worksheets to be merged have similar labels and headings, but different coordinates. The order of the sheets may vary. In such cases, Microsoft Excel copies all labels and headings to ensure that the data in the source sheet is the same as the data in the master sheet. This is different from the positional approach, which requires adding labels to the reference data. Another thing to note here is that case sensitivity and spelling mistakes can lead to inaccurate results. Before using this method, you must specify the data to join by selecting the left column label and upper right label.
3. Formulas: When consolidating data using this method, you should use three-dimensional references that also refer to the sheet from which you are retrieving the data, rather than the traditional cell references that cover rows and columns. This technique is ideal for consolidating data from multiple worksheets into a single Excel workbook.

How to Use Consolidate in Excel

To demonstrate the Excel consolidation function, let’s have a workbook with three worksheets containing three years of sales data from 2020 to 2022. I would like to create a “master” sheet that contains the sum of the data from the yearly worksheets. Let’s see how to use Excel’s integration functions:

1. Open an Excel workbook that uses the integration function. Go to the footer bar and click the plus (+) icon. This will create a new worksheet and add it to the footer bar.

1. You can rename the sheet to distinguish the master worksheet from other worksheets. To do this, right-click the sheet you want to rename and click the Rename button in the dropdown menu.

1. From the top bar, go to the Data tab, find the Integrate button in the Data Tools section, and click it. This will open a new integrated window in the center of your screen.

1. In the integration window here, select the function you want to perform. Functions that can be performed include Sum, Count, Max, Average, Min, Count Numbers, Product, StdDevp, StdDev, Varp, and Var.

1. To add a reference, click the up arrow to the right of the References text box. This will open a new window and change the cursor to a plus (+) icon. Navigate to the table you want to add a reference to and click and drag to select the table. With the table selected, click the arrow below to add cell references to the table.

1. Click the Add button to insert a table reference into your integration function. Repeat steps 5-6 to insert additional tables.

1. Now that the table reference has been added, you can choose whether to add the left column and top row. To do this, see the “Using Labels” section. Here you can see the options in the top row and left column for your needs.

1. You can optionally create a link to the source data. Click this option and Excel will create a link to the source data spreadsheet. When the source is updated, the integrated data is also updated and the outline is created.

1. Once you have chosen your preferred settings, click OK to save and apply your changes.

There are a few things to keep in mind when using the integration features:

• You can create a link to source data only if the source data and summary data are on two different worksheets. If both datasets are on the same worksheet, the merged data will not update automatically.
• If you do not select the Rows Above and Columns Left options, the function will merge all cells that are similarly positioned.

Many people who use Excel and spreadsheets know that adding references to other cells is the most important part of using spreadsheet software. You may be wondering if you can reference another sheet in Google Sheets. Check out our article to find the answer.

How to Automate Consolidation in Excel

To automate the integration in Excel, create a new worksheet in your workbook and go to the Data tab. Click the Consolidate option to open a window in the center of your screen. Select the function you want to perform here and add the cell reference. If your data has labels, you can add a reference and check the “Top row and left column” option. To automate the integration, click to enable the Create link to source data option. This ensures that the data in the consolidated table is updated whenever changes are made to the source table.

Frequently Asked Questions

What Is Consolidate in Excel?

Excel’s consolidate function lets you combine data from multiple sources, such as ranges of cells or entire worksheets, into a single worksheet. Consolidation functions allow you to specify a source range or worksheet and select the consolidation function to run.

Why Consolidate Data in Excel?

Consolidation in Excel can be very beneficial if you want to organize your data. This function is useful when you want to combine large data sets to help you get a comprehensive overview for corporate budgeting, or when you want to save multiple Best if you have a worksheet.

How Do You Consolidate Data in Excel From Multiple Rows?

Consolidating rows is very beneficial in Excel when multiple rows in a worksheet have the same entries. After selecting the functions you want to perform on your data and cell ranges, you can select the labels you want to keep. For multiple rows, be sure to enable the “Left Column” option.

Wrapping Up

We hope that you have understood everything in this article and understand how Excel’s integration features work.

If you found this article helpful, please also check out our premium templates. These are primarily for Google Sheets, but can easily be exported to Excel as well. If you find something you like, don’t forget to use code SSP to get 50% off your purchase.