## Which Excel Function Are You?

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.

1. ROUND
2. VLOOKUP
3. MATCH
4. EXACT
5. COUNT

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.

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.

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!

_______________

## Dragging Pictures in Excel

Do you ever insert pictures into Excel? I add company logos occasionally, when creating a template for clients. They send me a jpg file, which I store in a folder in Windows Explorer.

When it’s time to insert that logo, I usually try to drag and drop it onto the worksheet. Of course, that doesn’t work, as I’ve discovered hundreds of times before. But I never give up!

It looks promising, because the pointer gets a little plus sign, as though it’s going to add something to the worksheet.

Maybe this capability was added in the latest Service

Continue reading Dragging Pictures in Excel

## Excel Roundup 20140303

Have you used Power Pivot yet? If you’d like a quick intro, and a few tips, watch this 15 minute video from Microsoft. Owen Duncan, Senior Content Developer for Power Pivot, takes you through some basics in this video, and talks about best practices.

If you’d like to learn more, their blog article has links to other Power Pivot articles on the Microsoft site.

Or watch on YouTube: Understanding Power Pivot for Excel

Contextures Posts

Here’s what I posted last week:

Instead of using the default icon sets in Excel, you can create colored Harvey Balls, or other

## Dynamic List With Blank Cells

If a list contains blank cells, the usual method for creating a dynamic named range doesn’t work. For example, you can use an OFFSET formula, which counts the entries in the column. The count is used to set the number of rows in the range.

When there are blanks, as in the screen shot below, the range is incorrect. There are 9 items, so 9 rows are included, but the last item is in the 12th row of the list.

If you create a drop down list based on this range, it includes blanks, and August is the last

Continue reading Dynamic List With Blank Cells

## Create Colored Harvey Balls in Excel

It’s easy to add conditional formatting icons in Excel, by selecting one of the built in options. These were introduced in Excel 2007, and improved in Excel 2010. However, you still can’t get all the icons in any colour. For example, you can show Harvey Balls (the 5 Quarters icon set), but only in black and white.

As a workaround, you can create your own icon set. I showed one way to do this last year, and you can see the details here. That technique used formulas to get a symbol from a lookup table, and the cells had

Continue reading Create Colored Harvey Balls in Excel

## Excel Roundup 20140224

If your laptop screen is too small, maybe you’re ready for an 82” touch screen, or start a bit smaller (and maybe cheaper) with a 55” version.

You can see Power Map in Excel on this giant screen, at the 7:15 mark, in the video below. My favourite moment is at 9:10, when the presenter says, “This is not the Excel spreadsheet I grew up with, that’s for sure.”

True! Excel was black and white only, with one sheet, when I started using it.

Or watch on YouTube: How Office Works on an 82” screen

Contextures Posts

Here’s

## Problem Breaking Links in Excel

In the screen shot below, there are two files. Cell B4 in the worksheet at the right is linked to cell B7 in the sheet at the left.

In the file with the link, I can go to the Ribbon’s Data tab, and click the Edit Links command,

That opens the Edit Links dialog box, and shows a list of the links in the workbook.

To break the link to the other file, you can click the Break Links button.

When the confirmation message appears, read the dire warnings, and make a backup, if you haven’t already

## Create Rounded Shapes in Excel 2013

If a workbook has macros, or needs navigation buttons, I usually create a button by inserting a rounded rectangle on the worksheet.

In Excel 2010, I can click on the Shape Styles drop down, on the Format tab, and change it to a beveled shape. This makes the shape look “clickable”, rather than just a flat shape on the sheet. My theory is that people are more likely to click something, if looks like a button.

That theory could be wrong, but the beveled button looks pretty too!

Excel 2013 Is Flat

Unfortunately, the Shape Styles n Excel

Continue reading Create Rounded Shapes in Excel 2013

## Excel Roundup 20140217

On the Power BI blog, Microsoft Finance Director, Marc Reguera, explains how his department is using the new Power BI tools in Excel. Will you be using them too?

You can read more at the Power BI blog, or watch the video below.

The video is also on YouTube: Interview with Marc Reguera

Contextures Posts

Here’s what I posted last week:

The heading text was filling in when I typed an "A" code in a column. See how I fixed the AutoComplete problem, and what caused it. Find the source data for an #Excel pivot table, to check that