How To Use Excel Consolidate

Combine duplicate rows and sum the values with consolidate function.

How to use excel consolidate. Click the arrow icon or browse next to the references bar. Use the drop down menu below function to select a method of consolidation. On the data tab in the data tools group click consolidate. Click and drag to highlight the data you want to consolidate. Click on the consolidate icon under the data tools section to open the consolidate dialog.

The next step is to click into cell c4 which is where we want the consolidate tool to insert our data. In your new worksheet press data consolidate. Once you have selected the consolidate option you will see the below window. In the function box click sum. Microsoft excel 2010 and 2013.

In the reference box click the collapse dialog button edit reference icon and select the branch a worksheet. On the data ribbons select data tools and then consolidate. Select the sheet or file with data you want to consolidate. Click the consolidate icon with two cells with an arrow pointing to a new third cell. The consolidate tool in excel is located in the data menu and combines values from multiple ranges into one new range.

To consolidate branch a income statement data. Click the worksheet that contains the data you want to consolidate select the data and then click the expand dialog button on the right to return to the consolidate dialog. This is the window we need to do the magic. When we do so the consolidate dialog box will appear. Now go to data consolidate.

If a worksheet containing data that you need to consolidate is in another workbook click browse to locate that workbook. Ensure the data is organized in the same way see example below. In the consolidate window make sure that sum is selected in the function drop down menu. The consolidate is a useful tool for us to consolidate multiple worksheets or rows in excel so with this function we can also summarize multiple rows based on the duplicates. After creating the above template in the sheet summary select the cell b2 where we want to consolidate all the 4 different regions.