A Dashboard with 4 Strange Features (Excel Hash Episode)


I’m back in the kitchen with another episode of Excel Hash. If you’ve no idea what I’m
talking about don’t worry. It’s just the second
episode in over a year. So Excel Hash is a challenge between me and my online MVP colleagues. Each of us needs to make a video that includes four ingredients. We’re free to use other
ingredients to get the job done, but these four have to be integrated. The ingredients are at least one icon, at least one Excel dynamic array function, Excel’s XOR function, and linked picture. Quite interesting. I think they’re going to
make a nice Excel dashboard. Let’s try it. Let’s assume I’m holding
an Excel conference and a masterclass. I have the list of
people in an Excel table called Names that are attending the masterclass and the conference, and I track them using a
flag of ones and zeros. So if a person has two ones
it means they’re attending both the conference and the masterclass. And when you create an
interactive dashboard that allows me to see who’s
attending the masterclass, the conference, just one of these sessions or both of these sessions. Let’s use the XOR function to figure out who is attending either of these. (bubble sound effect) We get a list of true and false values, so we get a true if we just
have a one on either side. We get a false if we have a one for both. Now if you’re familiar
with Excel’s logical test you know that any
non-zero number is a true. So in this case we don’t
need to say equals one. To turn these into ones and zeros let’s add a negation here. Let’s use a similar logic to calculate who’s attending both sessions. Let’s go and set up our dashboard. On the dashboard page I
already have a dropdown for the different sessions
that I’d like to track. Let’s use the filter function together with the XLOOKUP function
to get the list of people attending the session selected. My array is the list of
names from the data tab. For the include argument
I’m going to use xlookup. Lookup value is the value
selected in the dropdown. Lookup array are the headers in the table. And the return array is a dataset here. Remember, any non-zero value is a true. Filter is going to return the true values, close bracket two times,
second ingredient is used. A dynamic array function, and we’ve even combined it
with the XLOOKUP function. Let’s add an index column here. (bubble sound effect) I’m going to use the sequence
function to get this done. Now if I change the selection to both this is a list of people who
are attending both sessions. Now let’s add an icon for
each of these sessions. Under calculation tab
here I’ve already added the icon or the picture
I would like to have. So this one would be for the conference. This one should show up if I just select one from the dropdown. Now let’s add an icon for the masterclass. Let’s use whatever ingredients, insert icons. To make this look better
let’s covert to shape. Let’s give the series a different color. We have one ingredient left. It’s the linked picture. Let’s create that now. I’m going to copy the sell,
go to the dashboard side, paste, linked picture. The picture currently is not dynamic. It’s just showing me what is in the cell. So in cell A4 I have
that conference picture. In cell A5 I have award art. I want to make this dynamic,
so I need to work with names. Let’s go back to the calculation sheet. I’ve already given names to these cells. You can see them in the name box here. The name has to be identical to my dropdown that I have here. If you’re using spaces you can
use underscores in the names. You just have to work with
the substitute function later on in name manager. As a last step to make this dynamic, let’s go to name manager
and create a dynamic name. We’re going to use indirect. Since our names are identical we’re going to reference
our dropdown cell. Now on the dashboard side
instead of referencing to a specific cell we’re
going to use the new name. If I switch this to one the icon changes. Both, these are the
people that are attending both the conference and the masterclass. That would also be nice
to add a chart to this. Let’s add the calculations
we need for it right here. This is the total number
of people attending. And this is the number of
people attending the session we’ve selected from the dropdown. Let’s finalize the chart. Now if you switch our view to conference, this is the number of people
attending the conference. And the number of people
attending just one session is 29. So there it is. All ingredients together
in an integrated solution. We even made it spicer by
adding in a dash of XLOOKUP. Now here’s where I need a favor from you. Watch all the videos in
the Excel Hash play list. You’ll find the link below
and in the cards provided. Then vote for your favorite. I’ll see you in the next video. (upbeat music)

62 thoughts on “A Dashboard with 4 Strange Features (Excel Hash Episode)

  1. Many thanks for your great videos. But I have been struggling with something in Excel sheet and until know I couldn't get a solution and I am doing it manually. Let me give you and example. In the excel sheet I got so many activities. Let's say five activities, but the there are 30 description of the same of those five activities. So basically I want to sperate or get each one activity according their description. Such as;
    Description Activity
    1_ good A1
    2- good
    3- bad. A2
    4- bad

    Drag or put each activity according to the different description of the activity.

    Thanks in advance. If you didn't get my point. I will like to inform you in the Skype what I mean exactly

  2. Convert-to-Shape, Named Range, Doulbe-Unary … Brilliant solution to all these weird ingredients.
    Soooo nice! Thanks for playing Excel Hash!

  3. This is an action- and feature-packed quick snack with an exotic touch. Love it!
    Can’t wait for the dynamic array functions (new calc engine) to come to us O365 Outsiders!
    That’s my New Years wish list no. 1 item, right there.

  4. Convert to shape was really cool. Thanks, Excel hash is really challenging and fun. Thanks from you and all of MVPs member in this challenge.
    How can we know the result of votes?

  5. Very nice indeed. Thanks so much. But when are we lesser mortals (with regular 365) getting the FILTER function and XLOOKUP????

  6. Hi Leila.. I like it.. very clean and neat.. useful too. Thanks for sharing your tips and tricks (convert icon to shape, FILTER with XLOOKUP, INDIRECT in a named range to make the image link dynamic).. all great. I give it a thumbs up!

  7. I am totally confused. I just updated my Excel on Iphone and most of new formulas are there unique filter inex. I also ran update on my desktop 365 montly and STILL no new formulas? Can ANYONE please give me an update on what Microsoft is doing?

  8. Amazing…its so clear…you make it seem easy and flawless…5*** for you. I have been struggling with something though..its a little bit off this topic. Is it really possible to print a document containing unique code/numbers for future reference in Excel without having to use VBA

  9. Great ideas! Thanks for sharing. Maybe a sort function before filter would make the data even better displayed, in alphabetical order

  10. How to hide The Digits After decimal in a cell with the help of format cell. For Example, if I have a value of 45.56 Now I want to hide only .56 How it will be possible.

  11. Ms gharani, you added a minus before XOR. That makes it just an OR. Should we consider it cheating? In your defense, there is not much practical application to a XOR function. Lets see what Bill Jelen comes up with. He seems like a guy with tricks.
    (But you are a better instructor anyway!)

  12. Unfortunately, some of the function presented here are not available to most O365 users. I am talking about XLOOKUP and FILTER, this should have been clarified at the beginning.

  13. although all other videos are made with the same concept , but yours is the most elegant, simple and efficient. … have voted for you.

  14. Great tutorial Leila ! if the filter and xlookup is not available in older version, is there any useful formula can be used to replace in this case to index the name of attendance ? Or we just do it by an "old traditional" way ? Thanks !

Leave a Reply

Your email address will not be published. Required fields are marked *