MrExcel's Learn Excel #375 - Ad-hoc Reporting

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Oct 29, 2009.
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!
maxresdefault.jpg


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!
 

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top