Show All

Consolidate data

1. Examine your data and decide whether to consolidate it with 3-D references in formulas, by position, or by category.

   Formulas    You can use 3-D references in formulas for any type or arrangement of data. This is the recommended method.

   Position If you're planning to combine data that's in the same cell in each of several ranges, you can consolidate by position.

   Category    If you have several ranges with different layouts, and you're planning to combine data from rows or columns that have matching labels, you can consolidate by category.

2. Do one of the following:

   Consolidate the data with 3-D references or formulas

   1. On the consolidation worksheet, copy or enter the labels you want for the consolidated data.
   2. Click a cell that you want to contain consolidated data.
   3. Type a formula that includes references to the source cells on each worksheet that contains data you want to consolidate.

      For example, to combine the data in cell B3 from worksheets Sheet 2 through Sheet 7 inclusive, you could type =SUM(Sheet2:Sheet7!B3). If the data to consolidate is in different cells on different worksheets, enter a formula such as this: =SUM(Sheet3!B4, Sheet4!A7, Sheet5!C5). To enter a reference such as Sheet3!B4 in a formula without typing, type the formula up to the point where you need the reference, click the worksheet tab, and then click the cell.

   Consolidate by position or category

   1. Set up the data to be consolidated.

      How?

      • Make sure each range of data is in list format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list.
      • Put each range on a separate worksheet. Don't put any of the ranges on the worksheet where you plan to put the consolidation.
      • If you're consolidating by position, make sure each range has the same layout.

        If you're consolidating by category, make sure the labels for columns or rows that you want to combine have identical spelling and capitalization.

      • Name each range: select the entire range, point to Name on the Insert menu, click Define, and type a name for the range.
   2. Click the upper-left cell of the area where you want the consolidated data to appear.
   3. On the Data menu, click Consolidate.
   4. In the Function box, click the summary function you want Microsoft Excel to use to consolidate the data.
   5. Click the Reference box, click the sheet tab of the first range to consolidate, type the name you gave the range, and then click Add. Repeat this step for each range.
   6. If you want to update the consolidation table automatically whenever data in any of the source ranges changes, and you're sure you won't want to include different or additional ranges in the consolidation later on, select the Create links to source data check box.
   7. If you're consolidating by position, leave the boxes under Use labels in blank. Microsoft Excel does not copy the row or column labels in the source ranges to the consolidation. If you want labels for the consolidated data, copy them from one of the source ranges or enter them manually.

      If you're consolidating by category, select the check boxes under Use labels in that indicate where the labels are located in the source ranges: either the top row, the left column, or both. Any labels that don't match up with labels in the other source areas result in separate rows or columns in the consolidation.

Note  Other ways to consolidate data include creating a PivotTable report from multiple consolidation ranges.