Say that you add automatic subtotals and collapse the data down to the #2 view. Is there any way to sort the largest customer to the top? Episode 619 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book.
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today I have a tip that I learned in Columbus in the end.
I was doing a seminar there.
Now, the last year when I was in Columbus.
I learned the incredibly cool way to right click, drag right, drag left and copy here as values only.
Today's tip comes from a Katherine.
I talked about subtotals a lot I probably haven't done, done them on the podcast recently.
So, we do data subtotals and say it each change in customer, we want to use the SUM function for cost of goods sold, revenue, profit and quantity, click OK.
And very very quickly Excel inserts new rows with those subtitles and then my next favorite trick is to go down and press the number 2, button in the group and outline buttons.
Which shows me just the customer totals, but then the question is. Okay well, we had this original data set.
We sort of the data alphabetically by customer but we want to see the largest customers at the top, is there any way to do that well?
It's amazing when we're collapse down to the number 2 view here just choose the revenue heading press the Z to A button and sure enough, Excel sorts the subtotals but not only that it sorts all the data within the subtotals and so that way we see the largest customer, Walmart at the top and then General Motors are right on down the way.
So, a great way to have a very large data set of detailed data and basically to sort that so that way the largest customers come to the top.
Thanks to Katherine from Columbus, Indiana for passing that on.
and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today I have a tip that I learned in Columbus in the end.
I was doing a seminar there.
Now, the last year when I was in Columbus.
I learned the incredibly cool way to right click, drag right, drag left and copy here as values only.
Today's tip comes from a Katherine.
I talked about subtotals a lot I probably haven't done, done them on the podcast recently.
So, we do data subtotals and say it each change in customer, we want to use the SUM function for cost of goods sold, revenue, profit and quantity, click OK.
And very very quickly Excel inserts new rows with those subtitles and then my next favorite trick is to go down and press the number 2, button in the group and outline buttons.
Which shows me just the customer totals, but then the question is. Okay well, we had this original data set.
We sort of the data alphabetically by customer but we want to see the largest customers at the top, is there any way to do that well?
It's amazing when we're collapse down to the number 2 view here just choose the revenue heading press the Z to A button and sure enough, Excel sorts the subtotals but not only that it sorts all the data within the subtotals and so that way we see the largest customer, Walmart at the top and then General Motors are right on down the way.
So, a great way to have a very large data set of detailed data and basically to sort that so that way the largest customers come to the top.
Thanks to Katherine from Columbus, Indiana for passing that on.
and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
