A reader asked me recently to help him with an Excel-reporting challenge that might sound familiar.
His challenge, he said, is that he wants to create monthly reports for 27 different divisions. He asked whether my dashboard reports offer the ability to select a division and a month from a list. That way, he can set up one report that applies to any division for any period.
What he DIDN’T WANT, of course, was to create 27 different reports from scratch each month. That would send him to Excel Hell in record time.
What he NEEDED — but didn’t know it yet — is a dynamic Excel report. This is a report that adapts automatically when we change the settings for dates, divisions, products, departments, regions, and so on.
That is, he wanted his reports to update as though they were linked to a PivotTable or to another source of external data, but without necessarily using those tools.
We Excel users face this need all the time in a business setting. In fact, if you use Excel to report or analyze business data you probably need several dynamic Excel reports right now.
The web offers very little information about solving this need. So I’ve been working on a training course that will teach you the basics of dynamic Excel reporting. But in the mean time, my reader has a problem that’s easy to solve.
So to get him started, in How to Select Values from a List in Excel I explain how to select settings from a list. And in How to Select Dates and Regions in a Dynamic Excel Report Linked to an Excel Database I explain how to use two criteria to report from an Excel database using a simple helper column.