A Dashboard with 4 Strange Features

(Excel Hash 2019)

Welcome back to another edition of Excel Hash!

Last year (that’s right, I said “last year”), myself and several other prominent Excel MVP online personalities participated in a challenge where we are given four Excel features and asked to create a solution that uses each of the four features.

This year, we are working with four different features:

  • At least 1 Icons
  • At least 1 Dynamic Array Function
  • The XOR (Exclusive OR) Function
  • Linked Pictures

We are free to include other Excel features, but each of the above four must be a major part of the overall solution.

Let’s get cooking!

Begin with the Data

Suppose we are hosting an Excel conference along with an Excel master class.  We have a table called “Names” that holds a list of the participants names along with an indicator that shows if the participant is attending the conference, the master class, or both.

We are using the flag of “1” to indicate attendance, and “0” (zero) to indicate absence.

If a person has two “1”s, they are attending both the conference and the master class.

The XOR (Exclusive OR) Function

We want to determine the number of attendees that are exclusively attending either the master class or conference or are attending both events.

Starting with the first participant “Latricia”, we will place the following XOR function in cell D2 of our table.

=XOR([@Masterclass]=1, [@Conference]=1)

The reason the above formula doesn’t display traditional cell references is because we are using an Excel table.  Excel tables use what are called Structured References instead of traditional cell references.

If we were using a standard table with traditional cell references, the formula would look like so:

=XOR(B2=1, C2-1)

The results of the formula appear as follows.

The XOR function returns a “True” response if 1 and only 1 test is true.  If more than 1, none, or all tests are true, the XOR returns a “False” response.

Getting Fancy

If you are familiar with Excel’s logical tests, you know that a 0 zero is interpreted as “false” and any non-zero number is interpreted as “true”.  Because of this, we don’t need to write “=1” in our tests.  We can simplify the formula by writing it as follows.

=XOR([@Masterclass], [@Conference])

Instead of displaying “True/False” as the results, we want to continue our flagging system with “1/0” as the results.  This can be accomplished by applying a negation operation to the XOR function (two leading minus signs).

=--XOR([@Masterclass], [@Conference])

To determine who are attending both events, we will create a similar formula in cell E2 that uses the AND function.

=--AND([@Masterclass], [@Conference])

Setting Up the Dashboard

(using Dynamic Array Functions)

On the Dashboard sheet, we have created a dropdown list (using Data Validation) in cell C2 that utilizes a List that points to cells B1:E1 on the “Calculation” sheet.

Let’s use the new Dynamic Array FILTER function along with the upcoming XLOOKUP function do derive a list of participants that correspond with the selection in cell C2.

In cell C4, enter the following formula:

=FILTER(Names[Name], XLOOKUP(Dashboard!C2, Names[[#Headers],[Masterclass]:[Both]], Names[[Masterclass]:[Both]]))

How does it work?

The XLOOKUP will perform the following steps:

  1. Locate the value in cell C2 in the headers of the table.
  2. Return the entire column of results (1’s and 0’s) below the discovered header in Step 1.
  3. Filter the list of participants in the “Names” column based on all non-zeros in the results of Step 2.

BONUS: If you wanted to return a sorted list of participant names, wrap the above formula in a SORT function.

=SORT(FILTER(Names[Name], XLOOKUP(Dashboard!C2, Names[[#Headers],[Masterclass]:[Both]], Names[[Masterclass]:[Both]])))

To add to the usability of our list of participants, we will generate a list of numbers to the left of the filtered names.  Select cell B4 and enter the following formula.

=SEQUENCE(COUNTA(C4#))

What’s happening here?

  1. The COUNTA function is counting the number of items in the spilled list of filtered names starting in cell C4.
  2. The SEQUENCE function is generating a list of numbers (starting at 1) and ending at the results of the COUNTA function in step 1.

If we change the selection dropdown on cell C2, the results list updates automatically.

Adding and Using Icons

On the Calculation sheet, we have inserted icons and images that represent the four ways a participant will engage our conference.

The icon used to symbolize a participant attending a master class was added using the following steps:

  1. Select Insert (tab) -> Illustrations (group) -> Icons.

  1. From the “Analytics” category, select the first column chart icon.

  1. To make the icon look better, right-click the icon and select Convert to Shape.

  1. Select individual parts of the icon and use the Shape Fill option (Shape Format ribbon) to change their colors.

  1. Place the icon in front of cell A3. (PRO TIP: Use the ALT key to “snap” the size of the icon to be the exact height and width of the cell.)

Creating Linked Pictures

To display the image located in front of cell A3 of the Calculation sheet on to the Dashboard, perform the following steps:

  1. Select cell A3 on the Calculation
  2. Select Copy (CTRL-C).
  3. Select the Dashboard
  4. Click in an empty cell (ex: E4).
  5. Click the lower part of the Paste button and click Paste Linked Picture.

The newly inserted linked picture is NOT dynamic (see the Formula Bar); it’s pointing to cell A3 on the Calculation sheet.

To have the image change based on the user-selection of cell C2 of the Dashboard sheet, we need to work with Named Ranges.

Apply Names to Cells “holding” Images

On the Calculation sheet, we have named the following cells:

  • A3 – “Masterclass”
  • A4 – “Conference”
  • A5 – “One”
  • A6 – “Both”

NOTE: The names must match the names used in the dropdown list in cell C2 of the Dashboard sheet.

The final step to make the images dynamic are:

  1. Open the Name Manager (Formula (tab) -> Defined Names (group) -> Name Manager).
  2. In the Name Manager dialog box, select New.
  3. In the New Name dialog box, set the Name field to “pick” and the Refers To to the following formula:
=INDIRECT(Dashboard!$C$2)
  1. Select the linked picture and replace the original reference in the Formula Bar (=Calculation$A$5) with the following reference:
=Pick

Testing the Interface

If we select an item form the dropdown, the list of names changes along with the displayed image associated with the user’s dropdown selection.

BONUS FEATURE: Charting the Results

To display a charted version of the results, we will go with a column chart that displays the total number of participants along with the number of those participants that are included in the user’s dropdown selection.

Creating a Data Preparation Table for the Chart

On the Calculation sheet, add the following formulas:

Cell D3

Dashboard!C2

Cell E3

=COUNTA(Names[Name])

Cell F3

=COUNTIF(XLOOKUP(Dashboard!C2,Names[[#Headers],[Masterclass]:[Both]],Names[[Masterclass]:[Both]]),1)

Creating the Chart

The finished chart involves several interesting customizations.

For an in-depth explanation of the techniques used to create this chart, check out this video.

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Excel NEW Dynamic Arrays Course 

BE THE FIRST to master Dynamic Arrays

From Beginner to Expert Level!

Start Learning