Consolidate Data in Excel

Excel allows you to summarize data from worksheets through Data Consolidation. Data can be consolidated by position when all the referring data is in the same location and order, but you must consolidate by category when location and order is not the same.

---

---

How To Consolidate Data by Position:

1. Click the upper-left cell of the destination area for the consolidated data.
   • This is where the consolidated data will begin.
2. From the Data menu, choose Consolidate.
3. In the Function box, click the summary function you want to use to consolidate the data.
4. In the Reference box, enter a source area you want to consolidate.
5. Click Add.
6. Repeat steps 4 and 5 for each source area to consolidate.
7. To update the consolidation table automatically when the source data changes, select the Create links to source data check box.
   • To create links, the source and destination areas must be on different worksheets. Once you create links, you cannot add new source areas or change the source areas that are included in the consolidation.
   • When you consolidate by position, Microsoft Excel does not copy the category labels in the source areas to the destination area. If you want labels for the destination worksheet, copy them or enter them manually.

How To Consolidate Data by Category:

1. Click the upper-left cell of the destination area for the consolidated data.
2. On the Data menu, click Consolidate.
3. In the Function box, click the summary function you want to use to consolidate the data.
4. In the Reference box, enter a source area you want to consolidate.
   • Make sure to include the data labels in the selection.
5. Click Add.
6. Repeat steps 4 and 5 for each source area you want to consolidate.
7. Under Use labels in, select the check boxes that indicate where the labels are located in the source area: either the top row, the left column, or both.
8. To update the consolidation table automatically when the source data changes, select the Create links to source data check box.
   • To create links, the source and destination areas must be on different worksheets. Once you create links, you cannot add new source areas or change the source areas that are included in the consolidation.
   • Labels in a source area that do not match any labels in the other source areas result in separate rows or columns when you consolidate data.

Summary Functions for data analysis:

 

Sum
The sum of the values. This is the default function for numeric source data

 

Count
The number of items. The Count summary function works the same as the COUNTA worksheet function. Count is the default function for source data other than numbers

 

Average
The average of the values.

 

Max
The largest value.

 

Min
The smallest value.

 

Product
The product of the values.

 

Count Nums
The number of rows that contain numeric data. The Count Nums summary function works the same as the COUNT worksheet function.

 

StdDev
An estimate of the standard deviation of a population, where the sample is all of the data to be summarized.

 

StdDevp
The standard deviation of a population, where the population is all of the data to be summarized.

 

Var
An estimate of the variance of a population, where the sample is all of the data to be summarized.

 

Varp
The variance of a population, where the population is all of the data to be summarized.

Top of Page

---

Consolidate Data in Excel
06-30-05 [jsw]