# 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:

- Locate the value in cell
**C2**in the headers of the table. - Return the entire column of results (
*1’s and 0’s*) below the discovered header in**Step 1**. - 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?

- The
**COUNTA**function is counting the number of items in the spilled list of filtered names starting in cell**C4**. - 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:

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

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

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

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

- 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:

- Select cell
**A3**on the**Calculation** - Select Copy (
*CTRL-C*). - Select the
**Dashboard** - Click in an empty cell (
*ex: E4*). - 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:

- Open the
**Name Manager**().*Formula (tab) -> Defined Names (group) -> Name Manager* - In the
**Name Manager**dialog box, select**New**. - In the
**New Name**dialog box, set the**Name**field to “pick” and the**Refers To**to the following formula:

=INDIRECT(Dashboard!$C$2)

- Select the linked picture and replace the original reference in the
**Formula Bar**(*=Calculation$A$5*) with the following reference:

=Pick

# 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.

# Additional Resources

If you would like more information about Dynamic Arrays:

XelPlus – Dynamic Arrays Course (*Beginner to Expert*)

### Dynamic Array Posts

Excel Dynamic Arrays – New Functions and Calculation Methods

Searchable Drop Down List in Excel (Very Easy with Dynamic Arrays)

LOOKUP ALL values b/w two dates (Excel Dynamic Arrays Filter Function to return many match results)

### XLOOKUP Function Posts

## Practice Workbook

Feel free to Download the Workbook HERE.

**COMPLETE Course about Excel Functions in 365 (Dynamic Arrays)**

MohammadDecember 22, 2019 at 12:41 pmHello Leila

I downloaded the file but it doesn’t work. When I change the selection session both columns B and C fill with #NAME?