Data Consolidation

Using Consolidate you can summarize data from separate worksheets onto one master sheet.  Data Consolidation performs a statistical function (e.g. Sum, Average) on a series of ranges (lists). And the best part:  the lists need not have identical content.

Watch this 2+ min. video to learn all you need to know.

Performing the Data Consolidation

Logo_Microsoft_Excel_2013 Click  here to open Toy List Consolidation.xlsx sample spreadsheet

Prep: Sort each list by the first column and remove all blank rows and columns within the lists.

Tip: Use Define Name to set name each list.

  1. On the summary sheet, select the upper-left cell where the consolidation is to appear.
  2. On the Data tab, in the Data Tools group, click Consolidate.
  3. In the Function box, select function (i.e., Sum).
  4. In the Reference field, type the name or cell references of first list (including column and row headings), then click Add. Repeat this step for each list to be summarized.
  5. Check Use labels in Top row and Use labels in Left column.
  6. Click OK.

Cheers!

hɔuᴉnb

Related reading:

Comments and questions are always welcome!

Enter your email address to follow this blog and receive notifications of new posts by email.

Advertisements

Author: quincy harley jr

Quincy is a technophile, coach and Learning Development expert. He has an extensive legal IT background and is practiced in MS Office application support and product development. As a project leader he has been integral in numerous new application rollouts. Whatever spare time he has, is spent with reading, archery and watching his young sons grow.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s