How To Use Excel Consolidate

You can also type the reference to the cell range in yourself if you d prefer.

How to use excel consolidate. In the function box click sum. Click and drag to highlight the data you want to consolidate. In the reference box click the collapse dialog button edit reference icon and select the branch a worksheet. Choose a function type which determines the consolidation method add references to be consolidated. Please do with the following steps.

Select the consolidation worksheet. Use the drop down menu below function to select a method of consolidation. If a worksheet containing data that you need to consolidate is in another workbook click browse to locate that workbook. Once you have selected the consolidate option you will see the below window. Click on the consolidate icon under the data tools section to open the consolidate dialog.

Ensure the data is organized in the same way see example below. The screenshots below will help you see an example of how to use the excel consolidate function. The consolidate tool in excel is located in the data menu and combines values from multiple ranges into one new range. 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. First you need to select a blank cell outside your data set and then go to data consolidate.

In your new worksheet press data consolidate. 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. When we do so the consolidate dialog box will appear. And create links to the source data if it is located in an external target. This is the window we need to do the magic.

Open all files workbooks that contain the data you want to consolidate. In the consolidate window make sure that sum is selected in the function drop down menu. To consolidate branch a income statement data. Select the sheet or file with data you want to consolidate. The next step is to click into cell c4 which is where we want the consolidate tool to insert our data.