Excel Consolidate Function How To Consolidate Data In Excel

Excel Consolidate Function

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Consolidate Function in Excel (wallstreetmojo.com)

How to Use Consolidate in Excel? (with Examples)

Example #1: Consolidate Multiple Worksheets into One

Before we explain the consolidating function in Excel, we would like you to download the workbook from the above link to practice along with me to learn this feature.

Assume we have received data from three worksheets in the same workbook, as shown below.

We have sales data of four zones in three different sheets.

Now we need to collate this data into a single worksheet. We have created a template named “Consolidated Sheet” for this in a separate sheet.

Wow is the word to describe, isn’t it??

Based on the type of consolidation we need to perform, we must select the desired function. In this example, we want to summarize the total sales of three different worksheets. So we will choose the SUM function in excelSUM Function In ExcelThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more, which is the default.

It is cool if we get the breakup of all the numbers and the total. So yes, we know what you need now. So let us create that as well.

Example #2: Dynamic Consolidate With Links to Source Data

How cool it is if we can create a link to the source whenever we make some changes in the source sheet. It shall reflect in the summary sheet instantly.

Below is the example screenshot of the same.

As we can see, some rows are grouped, which can be expanded by clicking on the “plus” button. So, Zone “E” has three different worksheet numbers. Then, in cell B5, we have a summary of all the three worksheet numbers.

To create a link while giving a reference, we need to check the box “Create links to source data.”

It will create a link to the sourced data cells. Now we can see this in the cell referencesCell ReferencesCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more.

Example #3 – Consolidate Data to Blank Worksheet

In the above example, we created a template in the “Consolidate Sheet” worksheet and consolidated the data. But, of course, we can also consolidate without creating the template.

First, we must add a blank sheet, then follow the same procedure as above. But while giving references, we need to select the entire data range.

We must select the complete data range instead of only the numbers column.

Then we must repeat the same action for all the three worksheets.

Once the reference is given, we need to click on the checkboxes of “Top Row” and “Left Column” to create labels.

Once this is given, click on “OK” on the blank worksheet. It will create a consolidated summary like the one below.

Like this, we can use the Excel consolidate function to consolidate the data from different worksheets.

Other Options in Consolidate

Function: This is the list of all the available functions in the Consolidate tool. We can use SUM, AVERAGE, COUNT, MAX, MIN function in excelMIN Function In ExcelIn Excel, the MIN function is categorized as a statistical function. It finds and returns the minimum value from a given set of data/array.read more, etcReference: This is where we need to select the ranges of different sheets to consolidate the data into a single sheet.Browse: This is the beauty of the consolidate option. Not only does it consolidate from the same workbook, but it can also fetch the data from other workbooks.Add & Delete: This where we can add and delete all the references.Top Row: We may use the top row as row headings.Left Column: We can use the left-hand side of the data as the column headings.Create Links to Source Data: This will make the report dynamic. The consolidated option creates the formula for all the other sheets to make the report dynamic.

Things to Remember

Under consolidate, option ranges of all the worksheets should be the same across worksheets.We can use any function in the consolidate option.