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 Remove Duplicates
1. Copy the data to a new location
2. Data, Remove Duplicates. Click UnSelect All. Click Customer. OK. OK.
3. Click in the first number. Ctrl+Shift+Down+Right. Enter a SUMIFS and Ctrl+Enter.
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 Remove Duplicates
1. Copy the data to a new location
2. Data, Remove Duplicates. Click UnSelect All. Click Customer. OK. OK.
3. Click in the first number. Ctrl+Shift+Down+Right. Enter a SUMIFS and Ctrl+Enter.
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2188: Summarize with Remove Duplicates.
Well, hey, in yesterday's Episode, I showed how to summarize this data down to one row per Customer, using the Subtotal command.
I was doing that in a seminar in Dallas, and Adam-- Adam from Road 2-- said, "Hey, wait, why don't you just use Remove Duplicates?" And yeah, Remove Duplicates will work.
So we start here-- we don't have to sort the data to start here in D1-- Ctrl+Shift+Down Arrow, keep your hand on Ctrl+Shift, press Right Arrow, Ctrl+C to Copy.
We'll move over here to a blank cell and Ctrl+V to paste, and then Remove Duplicates-- right there-- we're going to choose Unselect All, choose Customer, click OK.
What we get are 16 unique Customers.
Those numbers over there are meaningless-- we're going to get rid of those numbers.
Click here, Ctrl+Shift+Down, Ctrl+Shift+Right, and then =SUMIFS.
I guess if I was really going to try to do this in the fewest number of characters, SUMIF would be one character or less, wouldn't it?
But, alright, so we're going to specify the Sum_Range.
So I'm going to press the left arrow-- 1, 2, 3, 4, 5-- 6 times; Ctrl+Shift+Down Arrow; press F4 1, 2 times to freeze the rows; and then criteria_range, we'll press the left arrow-- 1, 2, 3, 4, 5, 6-- 7 times; Ctrl+Shift+Down Arrow, press F4 once; Comma, and what do we want that criteria_range to be equal to, we want it to be equal to the Customer.
And J2, I would need to freeze just the J, so that's press F4-- 1, 2-- 3 times, closing parenthesis, Ctrl+Enter, and there you are.
Less steps, certainly less steps, although entering the formula requires many more keystrokes.
So, comparing yesterday to today, not sure.
I'm sure Adam can do his method faster; I can do my method faster.
Both methods discussed in this new book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
All day this week-- every day this week-- we're looking at 5 different ways to create that Summary Table.
Yesterday, Subtotals; today, Remove Duplicates; tomorrow, Advanced Filter; then Consolidate; and Pivot Tables.
How to summarize with Remove Duplicates?
Copy the data to a new location; Data; Remove Duplicates; Unselect All; choose Customer; OK; OK to close the dialog-- the results message box; click on the first number; Ctrl+Shift+Down, Ctrl+Shift+Right; Enter a SUMIFS formula; when you finish that with the closing parenthesis, press Ctrl+Enter.
Well, hey, I want to thank Adam for that cool trick, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, hey, in yesterday's Episode, I showed how to summarize this data down to one row per Customer, using the Subtotal command.
I was doing that in a seminar in Dallas, and Adam-- Adam from Road 2-- said, "Hey, wait, why don't you just use Remove Duplicates?" And yeah, Remove Duplicates will work.
So we start here-- we don't have to sort the data to start here in D1-- Ctrl+Shift+Down Arrow, keep your hand on Ctrl+Shift, press Right Arrow, Ctrl+C to Copy.
We'll move over here to a blank cell and Ctrl+V to paste, and then Remove Duplicates-- right there-- we're going to choose Unselect All, choose Customer, click OK.
What we get are 16 unique Customers.
Those numbers over there are meaningless-- we're going to get rid of those numbers.
Click here, Ctrl+Shift+Down, Ctrl+Shift+Right, and then =SUMIFS.
I guess if I was really going to try to do this in the fewest number of characters, SUMIF would be one character or less, wouldn't it?
But, alright, so we're going to specify the Sum_Range.
So I'm going to press the left arrow-- 1, 2, 3, 4, 5-- 6 times; Ctrl+Shift+Down Arrow; press F4 1, 2 times to freeze the rows; and then criteria_range, we'll press the left arrow-- 1, 2, 3, 4, 5, 6-- 7 times; Ctrl+Shift+Down Arrow, press F4 once; Comma, and what do we want that criteria_range to be equal to, we want it to be equal to the Customer.
And J2, I would need to freeze just the J, so that's press F4-- 1, 2-- 3 times, closing parenthesis, Ctrl+Enter, and there you are.
Less steps, certainly less steps, although entering the formula requires many more keystrokes.
So, comparing yesterday to today, not sure.
I'm sure Adam can do his method faster; I can do my method faster.
Both methods discussed in this new book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
All day this week-- every day this week-- we're looking at 5 different ways to create that Summary Table.
Yesterday, Subtotals; today, Remove Duplicates; tomorrow, Advanced Filter; then Consolidate; and Pivot Tables.
How to summarize with Remove Duplicates?
Copy the data to a new location; Data; Remove Duplicates; Unselect All; choose Customer; OK; OK to close the dialog-- the results message box; click on the first number; Ctrl+Shift+Down, Ctrl+Shift+Right; Enter a SUMIFS formula; when you finish that with the closing parenthesis, press Ctrl+Enter.
Well, hey, I want to thank Adam for that cool trick, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
