A combination of six cool tricks allow you to take detailed transactional data and produce a chart of the top five customers in about 12 clicks. Episode 762 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
A cool trick today.
We have a data set here of transactional data and our manager wants a graph of the top 5 customers.
It's amazing how easy this is although it requires several different tricks.
First thing we want to do, of course, is sort by CUSTOMER.
So, I choose one cell in the CUSTOMER column and click the AtoZ button.
Now we're going to add subtotals.
So, DATA, SUBTOTALS, AT EACH CHANGE IN CUSTOMER, we're going to use the SUM function and add that to the REVENUE column, click OK.
Now, once we've added the subtotals in it -- of course added subtotals at every change in customer -- we're going to collapse down to the number 2 view, and choose one cell in the revenue column and SORT DESCENDING, ZtoA.
Now what we should have is all of our customers, one line per customer, and they're ordered in descending sequence by REVENUE.
I'm going to choose the top 5 customers and press ALT and ;. ALT+; is the cool trick that says, hey, we're going to select just the visible cells and not the hidden detail rows.
Now that I've done that, one more keystroke, F11, and Excel will give us a chart with Walmart, General Motors, Exxon, Ford, and Citigroup -- our largest 5 customers.
Now, in theory, this same trick could be used to produce the top 10, top 20, although you have to be careful.
Eventually, you have a non-contiguous selection that's too large for the series formula.
Works great with 5, doesn't work with 20 or 25.
So, it's a cool trick but with a couple of limitations.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
A cool trick today.
We have a data set here of transactional data and our manager wants a graph of the top 5 customers.
It's amazing how easy this is although it requires several different tricks.
First thing we want to do, of course, is sort by CUSTOMER.
So, I choose one cell in the CUSTOMER column and click the AtoZ button.
Now we're going to add subtotals.
So, DATA, SUBTOTALS, AT EACH CHANGE IN CUSTOMER, we're going to use the SUM function and add that to the REVENUE column, click OK.
Now, once we've added the subtotals in it -- of course added subtotals at every change in customer -- we're going to collapse down to the number 2 view, and choose one cell in the revenue column and SORT DESCENDING, ZtoA.
Now what we should have is all of our customers, one line per customer, and they're ordered in descending sequence by REVENUE.
I'm going to choose the top 5 customers and press ALT and ;. ALT+; is the cool trick that says, hey, we're going to select just the visible cells and not the hidden detail rows.
Now that I've done that, one more keystroke, F11, and Excel will give us a chart with Walmart, General Motors, Exxon, Ford, and Citigroup -- our largest 5 customers.
Now, in theory, this same trick could be used to produce the top 10, top 20, although you have to be careful.
Eventually, you have a non-contiguous selection that's too large for the series formula.
Works great with 5, doesn't work with 20 or 25.
So, it's a cool trick but with a couple of limitations.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
