Look Who it Was Random video

Excel - Mastering Excel: Create Dynamic Reports with Drop-Down Selections - Episode 1163

503 views · Published 22 December 2009 · 3:46 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Mastering Excel: Create Dynamic Reports with Drop-Down Selections.

Welcome to the MrExcel netcast, where we bring you the best tips and tricks for mastering Excel. In this episode, we will be tackling a question from one of our viewers, Shaun, who wants to know how to create a report based on a drop-down selection in Excel. This is a common task that many Excel users struggle with, but fear not, as I will show you a simple and efficient solution.

Shaun has a drop-down menu that allows him to select a month, and he wants a report from the corresponding section of the Year worksheet to be displayed. The report is quite large, spanning 27 rows and 34 columns. To set up for this task, I will first copy the January report and paste it in the desired location. Then, using the Paste Special function, I will adjust the column widths and date formats to match the original report. This will save us time and effort in the long run.

Next, we will use the powerful MATCH function to find the starting row of the selected month in the Year worksheet. We will then subtract 1 from this row number, as we want the report to start from the row above. This will give us the correct starting row for our report. Now, here comes the cool part. We will use the OFFSET function to select the entire range where we want the report to appear. By pressing Ctrl+Shift+Enter, we will create an array formula that will display all the data in one go. This is a neat trick that will save us from having to enter the formula for each cell individually.

Now, when we switch to a different month, the report will automatically update to show the data for that month. However, there may be some blank cells in the report that will show up as zeros. To avoid this, we can go back to the original spreadsheet and add spaces in those blank cells. It's a small step, but it will make the report look more polished and professional.

It's important to note that this solution has its limitations. The report is not editable, and any changes to the original data, such as adding new employees, may cause issues with the report. However, with a little bit of tweaking, this method can be a great way to quickly generate reports based on drop-down selections. Thank you, Shaun, for your question, and thank you for tuning in to another episode of the MrExcel netcast. Don't forget to subscribe to our channel for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Shaun's Question Show Selected Month
(00:12) Setting up the Report
(00:23) Report Size
(00:33) Copying and Pasting
(00:43) Using a Complicated Formula
(01:00) Choosing the Report Range
(01:38) Using the OFFSET Function
(02:30) Testing the Formula
(02:58) Potential Issues and Solutions
(03:28) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Array formula
Blank cells
Column widths
Copy and paste
Data validation
Downsides of the solution
MATCH formula
OFFSET function
Print report
Year worksheet


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152778/


Shaun has a large worksheet with 12 monthly reports on it.  On a summary worksheet, he would like to show one particular month based on a dropdown. Episode 1163 discusses Paste Special Column Widths, Array Formulas, Match, and OFFSET.

More from this channel