Summarize Data with Consolidate
April 12, 2018 - by Bill Jelen
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.
This week will feature five different ways to solve the problem.
- Monday: Summarize Data with Subtotals
- Tuesday: Summarize Data with Remove Duplicates
- Wednesday: Summarize Data with Advanced Filter
- Today: Summarize Data with Consolidate
- Friday: Summarize Data with Pivot Tables
- Saturday: Summary of Summarize Data Week
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.
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.
- 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