Excel Hash is an Excel competition dreamed up by fellow Excel MVP, Oz du Soleil. It’s based on the popular cooking show, Chopped. The one where Chefs are given a mystery box of four ingredients to make a meal from.
The four mystery ingredients we’ve been given are:
- MAX function
- FREQUENCY function
- Form Controls
- 3D Models*
*3D Models are new in Office 365 so not everyone will have access to these.
Our task was to come up with an Excel tool that used all four ingredients in an integrated way. We were free to choose the data and could use other tools from the Excel pantry!
You get to vote here for the entry you think integrates the four ingredients into the best solution. There is a link below to the playlist containing all entries, but first...
Below is my finished product; an interactive menu for a new bug restaurant that was having trouble educating diners in the delicacies of bugs 😊
Note: Restaurants that serve bugs are real and growing in popularity. And although my menu is a bit of fun, you could apply this concept in the real world to other scenarios. For example; provide sales people with 3D models of products that are otherwise too big to carry around or stock in store.
Anatomy of the Interactive Bug Menu
The restaurant kept track of the orders and collected ratings from each customer. Below is a snapshot of the data:
I found the maximum rating for each menu item with the MAX function. And grouped the count of ratings into bins 1-2, 3-4, 5-6 and so on using the FREQUENCY function. You can see the summary table below that supports the information displayed in the interactive menu:
I added Sparklines (see column O above) to give diners an at a glance understanding of the ratings distribution. A bit like Amazon do with their star ratings system.
The 3D bug models can be rotated using form control buttons:
There are some simple macros behind the scenes controlling the bug movement.
Video Explanation
The video below steps through the different elements used in the making of the interactive menu. It explains how the FREQUENCY and MAX functions work, where to find 3D models and how to use Form Controls to rotate the bug models.
Competitors and Voting
My fellow Excel MVP competitors are:
- Mike Girvin - Excel Is Fun
- Bill Jellen - MrExcel
- Leila Gharani
- Jon Acampora - Excel Campus
- Oz du Soleil - Excel on Fire
Please watch each video in this playlist, then go here to vote for the entry you think built the best and most integrated solution.
Using SUBSTITUTE Function to Find and Count Text
Ron MVP
I like the 3D images. Could then be left in a state where they could be dragged to re-orient rather than using buttons?
Mynda Treacy
Yes, the 3D models are still intact and can be moved, rotated etc. using the pull handles on the model. The macro buttons don’t remove this functionality, they just add another entry point for rotating them.
Ron MVP
Could you add a link to download the menu file so we can look at it.
Mynda Treacy
Hi Ron,
I didn’t include a download link for the file because it’s 200MB and users who don’t have access to 3D models in their version of Excel will only see the images as flat picture files. Users who do have access to 3D Models will be able to insert and play with them themselves. Hope you understand.
Mynda