Today's netcast talks about an easy-to-create reporting tool - one that allows you to show the totals for any subset of a dataset. Watch Episode 375 for the complete details.
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.
Here's a cool idea that I learned at one of my seminars last week in Boise, Idaho, that I wanted to share with you.
This is a cool way to basically create an Ad-hoc Reporting tool.
We have a data set here, with a large number of records, and I'm going to first turn on the AutoFilter option.
If we go to Data, Filter, and then choose AutoFilter, you'll see that we get a drop-down next to every heading, and from this drop-down we can select a particular customer, and see just the records for that customer.
Now here's a real interesting trick for Excel, and I talked about this in a recent podcast.
You can add a special formula here by hitting the Greek letter Sigma, but instead of adding a SUM function, Excel adds the arcane SUBTOTAL function, with a special setting to ignore filtered rows.
So I'll add that total, and drag using the fill handle to copy those totals across my dataset.
Now, once I have that total at the bottom of the data set, we then use Ctrl+X to cut the totals and move them up to a special area above our headings.
I'll use Ctrl+V to paste, put the word “Totals” up here, and now I've created a great Ad-hoc Reporting tool that I can give to anyone.
They can basically come along and choose any customer, and see the totals for that customer, and the totals are always at the top, you don't have to worry about having too many rows.
This was a great idea, to use the trick from the old podcast: Using the Greek letter Sigma to create the special SUBTOTAL formula, but then to bring those totals to the top of the worksheet above the headings, so that way we can always see the totals.
Give this to your VP of Sales, and he can always go in, answer his own questions, without having to call you.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Here's a cool idea that I learned at one of my seminars last week in Boise, Idaho, that I wanted to share with you.
This is a cool way to basically create an Ad-hoc Reporting tool.
We have a data set here, with a large number of records, and I'm going to first turn on the AutoFilter option.
If we go to Data, Filter, and then choose AutoFilter, you'll see that we get a drop-down next to every heading, and from this drop-down we can select a particular customer, and see just the records for that customer.
Now here's a real interesting trick for Excel, and I talked about this in a recent podcast.
You can add a special formula here by hitting the Greek letter Sigma, but instead of adding a SUM function, Excel adds the arcane SUBTOTAL function, with a special setting to ignore filtered rows.
So I'll add that total, and drag using the fill handle to copy those totals across my dataset.
Now, once I have that total at the bottom of the data set, we then use Ctrl+X to cut the totals and move them up to a special area above our headings.
I'll use Ctrl+V to paste, put the word “Totals” up here, and now I've created a great Ad-hoc Reporting tool that I can give to anyone.
They can basically come along and choose any customer, and see the totals for that customer, and the totals are always at the top, you don't have to worry about having too many rows.
This was a great idea, to use the trick from the old podcast: Using the Greek letter Sigma to create the special SUBTOTAL formula, but then to bring those totals to the top of the worksheet above the headings, so that way we can always see the totals.
Give this to your VP of Sales, and he can always go in, answer his own questions, without having to call you.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
