Final Friday Fix: July Challenge

On the final Friday of each month, we’re going to set Excel for you to puzzle over so that you can get your “Excel fix”. Challenge your office colleagues to see who can solve the puzzle quickest. There’s no prizes at this stage, you’re playing for bragging rights only!

Data Filter Not Working

You need to download the attached Excel file for this month’s challenge. Imagine you have data as follows:

In this example, I have certain “amounts” for each item, and each item has been ‘auto-numbered’ using the formula

which is a cool formula to use for autonumbering visible data.  For example, if I highlight all of the data, go to the ‘Data’ tab on the Ribbon and click on ‘Filter’ in the ‘Sort & Filter’ grouping (ALT + A + T), I can select ‘USA’ in the ‘Country’ filter as follows:

and I will get

See how the auto-numbering accommodates the filtering? Anyway, it’s a nice, useful trick but it distracts from this month’s conundrum…

The Challenge

In the attached Excel file, I want you to select ‘Australia’ from the ‘Country’ field:

Huh? How come ‘USA’ is now part of the filtered ‘Australia’ subset? I assure you I selected all of the data for filtering – so what went wrong and how can you correct it?

Sound easy? Have a go. We’ll publish the answer in Monday’s blog. Have a great weekend!