Summarizing Data in Excel Using Subtotals
This is the first of a five-part series on Summarizing Data
This week, I will cover Subtotals, Remove Duplicates, Advanced Filter, Consolidate, & Pivot Tables
How to summarize with subtotals:
1. Sort the data by customer
2. Data, Subtotals. At each change in customer, choose four columns. OK.
3. Click #2 Group and outline button
4. From Customer heading, Ctrl+Shift+Down+Right
5. Alt+; to select visible cells. Ctrl+C to copy. Ctrl+N for New. Ctrl+V to paste
This is the first of a five-part series on Summarizing Data
This week, I will cover Subtotals, Remove Duplicates, Advanced Filter, Consolidate, & Pivot Tables
How to summarize with subtotals:
1. Sort the data by customer
2. Data, Subtotals. At each change in customer, choose four columns. OK.
3. Click #2 Group and outline button
4. From Customer heading, Ctrl+Shift+Down+Right
5. Alt+; to select visible cells. Ctrl+C to copy. Ctrl+N for New. Ctrl+V to paste
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2187: Summarize with Subtotals.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I was doing a seminar down in Dallas and doing one of my classic tricks, where we take this data-- 563 rows of data-- and summarize it down to a nice one-page summary using the Subtotals command.
Alight?
And so, we start out-- we want to make sure that the data is Sorted by Customer-- choose one cell in the Customer column.
On the Data tab, click the A to Z button to sort by Customer, then we're going to use the Subtotals command.
So, from this point: Data, Subtotal.
Subtotal always wants the subtotal by the leftmost column so we have to open this drop-down, choose Customer, Sum, and then we're going to choose those four columns.
Choose OK.
And what we'll get is at each change in customer, they've added a new row with the total for that row-- beautiful.
Now, click the number 2 Group & Outline button; these are added by the Subtotal command; we now have a list of all of our Customers.
Start here from cell D1, Ctrl+Shift+Down Arrow.
I don't let go of Ctrl+Shift, I press Right Arrow-- selects all of my data.
I want only the visible cells-- that's Alt+Semicolon, Ctrl+C to copy, Ctrl+N for New, Ctrl+V to paste, and we have our one-page summary.
I guess we could double-click here to make it wide enough-- like that.
Now, I was doing this trick, and Adam from Dallas says, "Hey, there's a faster way to do that." Alright, and I realized there's actually five ways to do that.
So this week, say this week we'll be taking a look at a different way.
I'm starting out on my Summarizing Data week, using my favorite method using Subtotals; but each day, we'll take a look at a different way to go.
Now, all of these methods are in my new book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
Click that "I" on the top right-hand corner to be taken to a link for the book.
Alright, wrap-up: Today's Episode, we're talking about summarizing data in Excel using Subtotals; this is the first of a 5 part series on summarizing data.
We're going to look at Remove Duplicates, Advanced Filter, Consolidate, and Pivot Tables throughout the week.
To summarize the data with Subtotals, we have to sort the data by Customer; and then go to the Data, Subtotals, and each change in Customer, choose the four numeric columns; click OK; number 2 Group & Outline button; and then from the Customer heading, Ctrl+Shift+Down, Ctrl+Shift+Right; Alt+Semicolon to select the visible cells; Ctrl+C to copy; Ctrl+N for new workbook; Ctrl+V to paste.
About 17 clicks to solve the problem.
Well, hey, I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I was doing a seminar down in Dallas and doing one of my classic tricks, where we take this data-- 563 rows of data-- and summarize it down to a nice one-page summary using the Subtotals command.
Alight?
And so, we start out-- we want to make sure that the data is Sorted by Customer-- choose one cell in the Customer column.
On the Data tab, click the A to Z button to sort by Customer, then we're going to use the Subtotals command.
So, from this point: Data, Subtotal.
Subtotal always wants the subtotal by the leftmost column so we have to open this drop-down, choose Customer, Sum, and then we're going to choose those four columns.
Choose OK.
And what we'll get is at each change in customer, they've added a new row with the total for that row-- beautiful.
Now, click the number 2 Group & Outline button; these are added by the Subtotal command; we now have a list of all of our Customers.
Start here from cell D1, Ctrl+Shift+Down Arrow.
I don't let go of Ctrl+Shift, I press Right Arrow-- selects all of my data.
I want only the visible cells-- that's Alt+Semicolon, Ctrl+C to copy, Ctrl+N for New, Ctrl+V to paste, and we have our one-page summary.
I guess we could double-click here to make it wide enough-- like that.
Now, I was doing this trick, and Adam from Dallas says, "Hey, there's a faster way to do that." Alright, and I realized there's actually five ways to do that.
So this week, say this week we'll be taking a look at a different way.
I'm starting out on my Summarizing Data week, using my favorite method using Subtotals; but each day, we'll take a look at a different way to go.
Now, all of these methods are in my new book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
Click that "I" on the top right-hand corner to be taken to a link for the book.
Alright, wrap-up: Today's Episode, we're talking about summarizing data in Excel using Subtotals; this is the first of a 5 part series on summarizing data.
We're going to look at Remove Duplicates, Advanced Filter, Consolidate, and Pivot Tables throughout the week.
To summarize the data with Subtotals, we have to sort the data by Customer; and then go to the Data, Subtotals, and each change in Customer, choose the four numeric columns; click OK; number 2 Group & Outline button; and then from the Customer heading, Ctrl+Shift+Down, Ctrl+Shift+Right; Alt+Semicolon to select the visible cells; Ctrl+C to copy; Ctrl+N for new workbook; Ctrl+V to paste.
About 17 clicks to solve the problem.
Well, hey, I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
