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:
=PickTesting 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!C2Cell 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
Excel XLOOKUP Explained (Goodbye VLOOKUP!)
Left Lookup in Excel with XLOOKUP (made easy)
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
