Excel - Master Excel Consolidate: Summarize Data by Customer | Excel Tutorial - Episode 831

Microsoft Excel Tutorial: Master Excel Consolidate: Summarize Data by Customer.

Welcome back to the MrExcel netcast! In this week's episode, we'll be diving into the world of Excel #831 - Consolidate. But before we get started, I want to take a moment to talk about our Facebook page. If you haven't already, be sure to check out our page by searching for mrexcel.com on Facebook. By becoming a fan, you'll have access to exclusive content and be entered into a draw for a free book. So don't wait, click on BECOME A FAN and join our community today!

Now, let's get back to Excel. In this episode, we'll be exploring the consolidate function. This powerful tool allows you to group data from multiple rows into one concise summary. I've been using this function since the days of pivot tables and it has never failed to impress me. So, let's take a look at how to use consolidate to summarize data by customer.

First, we'll need to select the data we want to consolidate. In this example, we have data for different regions, products, dates, customers, quantities, revenue, cost of goods sold, and profit. We want to consolidate this data to show one line per customer. To do this, we'll go to the DATA menu and choose CONSOLIDATE. Then, we'll specify the range of data we want to consolidate and select the customer field as the leftmost field. We'll also make sure to include the headings and select to use the labels in the top row and left column. With just a few clicks, we now have a concise summary of our data by customer.

But that's not all! In tomorrow's episode, we'll take a look at a different way to use consolidate - to add up similar worksheets. So be sure to tune in for another netcast from MrExcel. And if you're in Atlanta this Wednesday, don't forget to stop by and say hello at the SouthEastern Accounting Show. Thank you for watching and we'll see you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


During a recent seminar, someone asked me to show the audience how to use the Consolidate command. I launched into the technique shown in Episode 831 - using consolidate to collapse a single range down to one line per customer. In tomorrow's podcast, we will look at alternate uses for consolidate.

Table of Contents:
(00:00) Introduction and Facebook page announcement
(00:30) Encouraging viewers to become a fan on Facebook
(00:47) Upcoming events and seminars
(01:03) Introduction to using consolidate function
(01:20) Using consolidate to summarize data by customer
(02:16) Preview of tomorrow's topic on using consolidate for different purposes
(02:26) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Become a fan
Consolidate
Data analysis
Facebook
Free book giveaway
mrexcel.com
Pivot tables
SouthEastern Accounting Show
Summary by customer
Upcoming events

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152039/