Data consolidation
Consolidation is the term that denotes the unification of several parts into a single entity. In practice, we use consolidation when we want to receive a summary report; for example, when we want to summarize the sale by months, quarters, years … The simplest way to do this is by summarizing the data entered in several worksheets in the last sheet using the formula. But what if the data in the worksheets is not uniform? Excel also has a solution for this scenario …
In order to apply the option of consolidation, we first need to have tables in several worksheets. Each table can have an arbitrary number of rows or columns, but it is important to note that these tables can have only one column and/or one row as the header of the table. Consolidation can be done by position or by category. In both cases, we do this by calling the Consolidate option in the Data ribbon.
Consolidation by position is done when consolidating tables of the same size. In this case, in the last table where we are consolidating the data, we need to mark the fields in which the consolidated values should be found, and then the option Consolidate is launched. The dialog box of the same name will appear in which we first select the function that we want to use for consolidation (sum, product, average, maximum, minimum, etc.). Then, we select the ranges of consolidation by first clicking on the Reference field, and then on the worksheet we will mark the fields to be consolidated. By clicking the Add button, we add fields to the selection, and by clicking Delete, we remove them. The goal is to add all the fields on those sheets for which we want to consolidate. By marking the Top Row or Left Column field, we can, when consolidating the data, also copy the table headers if they do not already exist on the last sheet. Also, by marking the Create links to source data field, we can create links to consolidated fields, so that after consolidation, it can be seen how it was created. After selecting all the desired fields, click OK to consolidate the data.
In addition to this simpler way, consolidation can be done by category. This is useful when we have different tables at the source. That is, tables that can have different rows, multiple columns, and even different column layouts. It’s only important to have unique headers, so Excel knows how to perform consolidation. The consolidated tables can also be found on the same list, although it is more practical when they are on different sheets because consolidation is usually used to combine more people’s work.
When consolidating by category, you just need to click on a blank field in the table where we want to consolidate. Selecting the Consolidate option from the Data ribbon will open the window with the same name, where we will add the areas that we consolidate. In the example, it is seen that the second table has a line more than the first, and that the third one has the replaced row values and one additional column. All this is not an issue, while the names of the headers in rows (names) and columns (salary, bonus) are unique. After we’ve added areas, it’s mandatory to highlight the Top Row and Left Column fields, since the area where we create a consolidated table does not have a header. After we confirm the selection, Excel will consolidate data.
