Data Consolidation in Excel (+ Use Cases)

In the course of work, a person who uses Excel may have several tables that are located in several different files. And we need to bring all this data together in order to have a general idea of ​​uXNUMXbuXNUMXbwhat is there. To do this, there is a Consolidation tool. Today we will analyze in detail how to use it correctly and what chips and secrets Excel has in order to achieve maximum efficiency.

What is data consolidation

Consolidation is the same as consolidation. In Excel, this is a function that allows you to combine several ranges into one, connect several sheets that are in several files.

This can be useful for a marketer, as well as for representatives of other specialties. Let’s look at a few situations in which data consolidation may be in demand.

Suppose we launched contextual advertising, where there are such indicators as clicks, expenses, transactions and income. And we have created such a sign.

If we load all this information into Excel, we will see that many rows are essentially repeated.

The reason for this is that Yandex.Direct and Google Ads use the so-called broad match modifier. Because of this, we need to combine several strings into one, which is actually one keyword, and its effectiveness must be evaluated. 

Many people do it themselves, which requires a huge investment of time and effort.

Excel has a separate feature that allows you to do this called Data Consolidation. Some other ways of consolidating data are also possible. Let’s look at them in more detail. 

Consolidate data from multiple sheets

Suppose we need to merge data that is on multiple sheets. This can be done even if the tables are in different formats. True, the tools used in this case will differ somewhat. The exact sequence of actions to be performed in this case will be given below.

Consolidation of data from several tables into one

Several different tables can be placed on different sheets or on one sheet. In this case, there are features of how to work correctly. 

What Data Consolidation Can Be Used For

Data consolidation can be used in a number of areas, from accounting to life management. In fact, any area of ​​​​life that Excel has penetrated can be associated with this program. And as soon as there are several tables that need to be combined into one, it becomes necessary to use the appropriate functionality of Excel.

Representatives of which professions will benefit from the data consolidation function

There are a huge number of professions in which data consolidation can be used. In fact, this is any specialty related to information processing, including university education. In general, representatives of the following professions can use this opportunity in their work:

1. Accountant.
2. Investor.
3. Trader.
4. Mathematician.
5. A scientist of any specialty that requires the ability to process information statistically, from mathematicians to sociologists and psychologists.

As well as a number of representatives of other professions. Even if you are not on this list, it is still recommended to study. how to use data consolidation so that at the right time you don’t have to study this additionally, but already know by that point in time. 

How to Consolidate Data in Excel

And now let’s move on to the analysis of ways to perform data consolidation in spreadsheets. A lot of them. But first you need to sort out the universal criteria for compliance with which a table or sheet is always checked. 

Conditions for performing the consolidation procedure

Despite the fact that Excel is a very smart program that is unpretentious to the conditions, there are still several criteria that a table must meet in order for Excel to be able to combine it with another, the same one. 

Let’s list them in more detail:

1. Column names must be the same. In this case, slight deviations from the original format are allowed. For example, you can rearrange columns. Excel will still be able to grab the correct values.
2. There should not be a single cell that does not contain any values. 
3. In general, all tables should be organized around the same pattern. 

Consolidation by location

This is one of the main methods for combining data. In this case, the cells to be merged must be in the same position as the original one. It is very convenient to use this method to combine ranges that are placed on different sheets. One example where you can use consolidation by location is accounting, where four quarterly reports need to be combined into one annual one. With consolidation by location, you can create a budget once and then update it every quarter. This will take much less time than writing a new annual report from scratch.

Let’s try to do this in practice. To do this, there is a special command on the menu bar, which is called “Consolidation”. First, we need to make sure that all the conditions above are met. 

Next, we need to create a new workbook or sheet in which the resulting table with the combined data will be displayed. We open it (or it, if it is a book), after which we press the left mouse button on the upper left cell (if it was not selected by itself). It is important to make sure that there is absolutely no information below and to the right of it, because the consolidated table will take up as much space as it needs. After that, go to the “Data” tab, which has a button with the appropriate name. Click on “Consolidate”, after which a dialog box opens that looks like this.

We see that on the left is a list of functions that can be used when combining. These are a variety of calculations, such as the sum of values, unbiased variance, arithmetic mean, and more. We will choose the first option because it is the most commonly used and the easiest to understand. In this case, the cells in the appropriate places will simply be added to each other.

After that, we begin to fill in the next field – “Link”. To do this, put the cursor in this field. After that, open the first sheet (in our case, this is the data for the first quarter). We select all the data (including the header), after which the first range will appear there, which will be summed up with the next one. After that, click the “Add” button.

Next, add as many sheets as you need. In our case, this is the data for all other quarters. Just click “Add”, select the required ranges, and then repeat the cycle until the amount that we need is added.

All ranges that have been added for merging are displayed in the corresponding field, next to which there is an inscription “List of ranges”. You can also set the settings more flexibly by checking the boxes next to the items “Top Row Labels” or “Left Column Values” that are appropriate in meaning. With these options, you can determine what to use as names. There is also a “Create links to source data” button that allows you to automatically change the data in the consolidated table if something is edited in the source material.

Important! If you enter data that is outside of the original ranges into the original ranges, then there will be no automatic update..

In this case, only manual entry of new values ​​is allowed, for which you first need to uncheck the “Create links with source data” checkbox. To finally exit the consolidation menu and generate a ready-made table that combines several ranges at once, you need to click the “OK” button.

In this table, it is possible to control the display of individual rows by clicking the plus or minus sign to the left of the main content of the sheet.

Consolidation by category

In this case, column names or other labels will be used as a guide for joining. In this case, it is not necessary to ensure that the layout is perfectly the same. The data can be very scattered, but if it’s labeled correctly, Excel can merge it into a heap.

There are many similarities between setting up consolidation by category and creating a pivot table. True, the latter has several advantages over regular consolidation, since the PivotTable supports more flexible consolidation options. 

Let’s take a simple example of how this works. 

Suppose we have a store that sells goods marked with different indexes (1, 2, 3, 4, and so on). We have several tables describing them, and some of the names are repeated. What to do in such a situation?

The sequence of actions is very simple. First you need to open the Consolidation menu. We will select the “Sum” function in the panel that appears. You can choose another as the one that will be used to connect the information. After that, press the OK button.

We get such a result. We see that Excel was able to automatically combine data from tables based on the column names. 

What is the best way to consolidate? It all depends on how the table will be used. But experts still recommend the location option, since exactly the data that is fully consistent with each other in terms of layout will be ideally combined. 

Consolidate data in Excel using formulas

Suppose we have several tables that are thematically related, and they need to be merged. For example, such.

How can I sum them up using a formula? The simplest option is to simply enter a formula like this.

=’2001′!B3+’2002′!B3+’2003′!B3

You can also use the function SUM, which adds values ​​from certain ranges and returns the result to the cell where it is written. 

For example, so.

=SUM(‘2001:2003’!B3)

Using this method, you can combine data from several different tables. 

Data Consolidation in Excel: Practice

Suppose we have such a table.

In the case of it, the category method is best suited, since the table templates are the same. The tables to be merged are located in different documents. In total, we need to combine three books. The sequence of actions is as follows:

1. We open all three books that we have. In addition, we need to create another one, since the consolidated table will be placed in it. Next, click on the upper left cell and go to the Consolidation menu, as described earlier. 
2. After that, we make a consolidated summary of all tables using the “Average” function.
3. In the field with the link, write down the path using the “Go to another window” button on the “View” tab. We select the files of interest and add the necessary ranges from them. After that, click the “Add” button. 

You can also use the “Browse” button to specify the path to the Excel file. Or banal switching by standard Windows tools. 

As a result, we get such a table.

Data Consolidation in Excel: Examples

There were plenty of examples of data consolidation in Excel above for each case. But there is another one that was also good to consider. To begin with, we enter the names of the rows and columns from the ranges that we are going to merge in the future. The easiest way is to just copy them.

After that, in the first cell, enter the following formula.

=’Quarter 1′!B2+’Quarter 2′!B2+’Quarter 3′!B2.

After that, the corresponding cells of all three sheets will be merged. Then it remains just to copy this formula to the entire column.

This method of consolidation is convenient to use in situations where the data is located on different sheets. 

Thus, data consolidation is not difficult at all. If everything is done correctly, then you can make an annual report in just a few minutes.