Monday Morning Mulling: April 2025 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend.  On the Monday, we publish a solution.  If you think there is an alternative answer, feel free to email us.  We’ll feel free to ignore you.

The Challenge

Last Friday’s challenge invited to create a searchable data validated drop down box using a method that would work in a perpetual licence version (e.g. Excel 2019) of Excel. 

A list was provided, containing a set of values:

You could download the original question file here. 

Remember, these were the guidelines:

• the solution must work in a perpetual licence version of Excel (rather than Excel 365 or Excel online)
• you cannot use dynamic arrays
• you cannot use VBA, Office Scripts or any other programming code.

Suggested Solution

You can find our Excel file here. which shows our suggested solution.  The steps are detailed below.

This Final Friday Fix is not our hardest, but sometimes simple solutions do not occur to us.  It’s a simple, yet valuable, addition to your Excel repertoire.  To start, select any cell on the list:

Go to the ribbon, select ‘Insert’ tab and click the ‘PivotTable’ icon:

You may also click on the ‘PivotTable’ dropdown menu (blue) and then select ‘From Table/Range’ to arrive at the same next step:

This will open the ‘PivotTable from table or range’ dialog.  Click ‘OK’:

Focusing on the ‘PivotTable Fields’ pane, drag the Listfield down into the Filters area:

Then, apply proper formatting.  It will create this, which is the successful completion of this month’s challenge:

As an addition, you may utilise this trick to create a searchable box for a slicer to enhance user experience:

The dropdown menu will open in front of the slicer:

This concludes the Final Friday Fix for this month.  How did you do?

The Final Friday Fix will return on Friday 30 May 2025 with a new Excel Challenge.  In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business working day.