MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Summarize Data with Consolidate


April 12, 2018 - by Bill Jelen

Summarize Data with Consolidate

Your manager needs a summary of total revenue, cost, and profit for each customer in a large data set. Today I look at using the ancient Consolidate command to solve the problem.

The goal is to summarize this 563-row data set so you have one row per customer.

Create a summary with 1 row per customer
Create a summary with 1 row per customer

This week will feature five different ways to solve the problem.


Watch Video

Here are the steps:

  • Click in J1. This should be the top-left corner of your output report. Everything below and to the right should be blank.
  • Data, Consolidate. Specify D1:H564. Click Top Row. Left Column. OK.

    The mighty consolidate dialog
    The mighty consolidate dialog
  • At this point, you technically have the solution, using just 9 clicks! In reality, you will want to fill in the Customer heading in J1.

    Almost the answer
    Almost the answer
  • Copy the heading from D1 to J1.

Although I rarely use Consolidate, it is the surprise of the week. I am not sure that pivot tables in tomorrow's article can create the summary report any faster.

This week is Summarizing Data week. Each day, we will look at five different ways to solve a problem.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"To Excel is human. To figure it out, divine."

Title Photo: Bethany Legg / Unsplash


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.