MrExcel.com - Your One Stop for Excel Tips & Solutions

Summarize Data with Consolidate

April 12, 2018 - by

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

Video Transcript

Learn Excel from MrExcel Podcast, Episode 2190: Summarize with Consolidate.

Hey, this is our fourth attempt at our Summarize Data week, this one is so old I remember doing this back in, like, 1993, even. The Consolidate command. And it's going to be the surprise winner, so far, because it is easy and fast.

We're going to click here-- this is click number one-- in J1, a blank section. Everything below this, to the right of this, should be blank. Click number two, Data; click number three, Consolidate. They default to Function Sum, that's great. For my reference, I'm going to click in D1-- that's four; Ctrl+Shift+Down, five; Ctrl+Shift+Right, six; top row, seven; left column, eight-- okay, nine, and there is a hard result-- nine clicks. It's going to drive me crazy that Customer’s not there, so I guess ten. Ctrl+C, eleven; twelve, Ctrl+V paste. Done. What a fast, fast way to create that report. No subtotals, no Remove Duplicates, no Advanced Filter.

Consolidate, of course, is in this book, MrExcel, The 54 Greatest Tips of All Time-- MrExcel LIVe. That's the Roman numeral for 54 with a little "e" on the end. This is what I use in my live Power Excel seminars as I travel around the country. Click the "I" on the top right-hand corner for more information about that book.

Alright, this is our fourth of a five part series on Summarizing Data-- Consolidate. Easy. Select the top left corner in a blank area of your worksheet; Data; Consolidate; specify the input range; choose Top row and left column; click OK; and you're essentially done. Maybe copy the Customer heading over, or just type Customer-- whichever you want to do.

I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.

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