How To Use Excel Consolidate

Choose a function type which determines the consolidation method add references to be consolidated.

How to use excel consolidate. Click on the consolidate icon under the data tools section to open the consolidate dialog. On the data tab in the data tools group click consolidate. On the data ribbons select data tools and then consolidate. Select the consolidation worksheet. Combine duplicate rows and sum the values with consolidate function.

In the reference box click the collapse dialog button edit reference icon and select the branch a worksheet. You would use this feature when you have a single text column on the left and the column has many duplicate values. In the consolidate window make sure that sum is selected in the function drop down menu. Click and drag to highlight the data you want to consolidate. Microsoft excel 2010 and 2013.

Open all files workbooks that contain the data you want to consolidate. In the function box click sum. If a worksheet containing data that you need to consolidate is in another workbook click browse to locate that workbook. You can also type the reference to the cell range in yourself if you d prefer. In your new worksheet press data consolidate.

Click a cell where you want to locate the result in your. To consolidate branch a income statement data. Click the button next to the reference entry box to begin selecting your first data set this is the data you wish to merge. First you need to select a blank cell outside your data set and then go to data consolidate. Once cell c4 is highlighted we go to consolidate in the data tools section of the data tab in the ribbon.

Click the arrow icon or browse next to the references bar. 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. 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. When we do so the consolidate dialog box will appear.