If you’ve been anywhere online in the past couple of years, you’ve probably seen those quizzes, such as Which Star Wars Character Are You?
Now, it’s time to play a new game – Which Excel Function Are You?
We don’t need an online service to create our quiz – we can build it in Excel.
Pick the Five Functions
Most of these games have 5 or 6 possible outcomes, so I’ll pick 5 Excel functions that have distinctive personalities.
On a worksheet named Admin_Data, I created a table with a list of the functions, and a personality description for each one. To add a bit of color to the quiz results, I inserted clip art to match each description.
Create the Questions
Next, on a sheet named Quiz, I created 4 questions, with 5 possible answers for each question. Here is the first question.
In column F, which will be hidden later, I put a function ID, to match each function to one of the answers. In column G, an INDEX formula shows which function was assigned. That isn’t necessary, but it helped me assign the IDs.
Add Option Buttons
Next, each question gets an option button, and the buttons are in a group. You can click here to read more about setting those up.
Each group of buttons is linked to a cell in column H, in the same row as the question. In the screen shot below, I clicked the 4th option button, and 4 is entered in the linked cell – $H$4
Find the Selected Function
The final step on the Quiz sheet is to calculate which function was selected. An INDEX function is added in cell G4, and it returns the function ID in the selected position. The 4th answer was selected in this example, and the LOOKUP function, with ID of 2, is in that row.
Count the Results
On the Admin_Data sheet, I added COUNTIF formulas, to count the number of times that each function was selected. It looks for the IDs in column G on the Quiz sheet.
Next, a MAX formula gets the highest total, and a MATCH formula finds the first row with that total. So, if two or more functions are tied, the first function will be returned as the result.
Then, based on the MATCH result, the Function name and description are returned in INDEX formulas.
Get the Results
The remaining sheet is named Results, and it uses simple formulas to pull the selected formula name and description.
Show the Function Picture
To show the picture, I named the Picture heading cell in the Functions table – PicStart. I created another name – PicSel – with this formula;
I copied the cell with the first picture, and pasted it as a Linked Picture on the Results sheet. I made the copy bigger, so it would be easier to see.
When the picture is selected, you can see its link in the formula bar.
To make the correct picture show, change that formula to: =PicSel
Press Enter, and the selected function’s picture will appear in the linked picture.
Finish the Workbook
To finish the workbook, clear out the numbers in column H on the Quiz sheet. Those cells are linked to the Option Buttons, so if no number is in the linked cell, no option will be selected.
Then, hide the working columns, F:H.
NOTE: If nothing is selected, the first function – ROUND – is shown as the result. You could build in some checks, so that no result is shown, unless the quiz is completed. If you are the EXACT function, you’ll probably get right on that!
And if you create new questions, or want to share your version of the quiz, please add a comment.
Download the Sample File
To see all the questions, and to take the quiz, please visit my Contextures website. On the Sample Excel files page, go to the Functions section, and look for FN0032 – Which Excel Function are You?
The file is in xlsx format, and does not contain macros.
You can add more questions, or select different functions, to create your own quiz. Or, use this structure to create a completely different type of quiz!