I love Excel and wish more people would actually spend the time to learn the nuts and bolts behind it. Most people think of it as a band-aid solution used by people who don't know databases but to me doing things without coding using Excel's built-in functions is like those 1k/5k competitions - how much can you do with so little?

Here's my favorite problem that I'd like someone else to take a stab at:

Sheet1:A1-A999 (source) is an unsorted list of alphanumeric strings, with lots of repeats. Without using macros/VBA/scripting code, put all the unique strings in Sheet2:A1-A### (destination) such that any changes in source list is reflected in the destination automatically (auto calculate option is enabled). Destination list should have no more than 1 blank row. You cannot use any sorting/grouping functions manually. It has to be completely automatic. I want to delete the entire source worksheet, paste 399 items, insert 400 more, paste in another 300, and delete 100 of the rows randomly, and when I switch to the destination, it should have my list ready.

Bonus points if the destination list is sorted. Double bonus if you don't use any {array} functions.

Hint: You would be using functions like VLOOKUP/MATCH/ADDRESS/INDIRECT and similar.

So why is a problem like this useful? Because if destination is generated completely dynamically using items from the source, you can pull in dynamic web data into the source and have your Excel work reliably even if macros/scripting are disabled. Also, you can have users log in manual data into the source and you can generate the summary of their data in the destination without having to rely on any code or external service.