During the Azerbaijan Power Bi & Modern Excel Meet-up last week, Faraz Shaikh presented and shared a lot of cool Excel tips. There was one tip that stood out for me: Extracting a table’s headers into a Dynamic Array. 😲 It was amazing! It got me thinking about how this could be applied.

In this video I show how to create a dynamic dropdown list that uses the table headers to extract specific data.
You’ll see: XLOOKUP, SORT, FILTER, TRANSPOSE in this solution.

The Azerbaijan Meet-up: https://www.meetup.com/baku-power-bi-modern-excel-user-meetup-group/

#XLOOKUP
#DynamicArrays
#MicrosoftExcel

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

0:00 Introduction
1:06 My Solution
5:13 Rabbit needs water
7:26 Correction with the EVEN function

ExcelHash is back for 2021! This year we added more participants. Check out the full playlist and think about how you would create a solution.

The premise:
Take 4 random Excel features and bring them together into an integrated solution. It’s pretty difficult and forces a person to justify their choices; thinking about the essence of a feature.

ExcelHash 2021 Ingredients
– A Cutout Person
– EVEN function
At least 2 from the following:
– LET function
– Dynamic Arrays
– Custom Data Type
– LAMBDA function

In this video, I combine the ingredients into a workbook that helps manage and price dirty jobs in a post-apocalypse world of weirdos and chaos.

#ExcelHash
#ExcelChallenge

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Dependent Dropdown Lists in Excel are pretty handy to know. But there’s an interesting problem when you’ve gotta stack multiple dependent dropdown lists and the source data might add or remove data in the different stages.

This video shows how to make this happen using Dynamic Arrays, UNIQUE and the FILTER functions.

Also check out Leila Gharani’s recent video that has a slight variation and she shows a Google Sheets solution. https://youtu.be/ku17vgq4Q14

#DependentDropDownLists
#DropDownLists
#Rooster

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

NOTE: this videos shows how I build a solution from scratch.

Cristiano Galvão of Excel Turbo posted something fascinating on LinkedIn. He had a main table of data and one column showed each record as Active or Inactive. There were two interesting things about the spreadsheet:
1. If a record was Inactive, the font was automatically gray. If it went from Inactive to Active, the font returned to normal.
2. Inactive records automatically populated on an Inactive worksheet. Active records automatically populated on an Active sheet. If a record changed statuses, it would automatically move to the right sheet.

I was intrigued. 💡🤔

I wanted to recreate this solution but I also know that records don’t always fit into neat categories. So, the solution in this video includes a 3rd category: Unassigned.

In this video you’ll see me figure this out for the first time, and watch my development process. That is more important than the actual solution. I didn’t work the solution out ahead of time and I don’t start with any data.

You get to see the whole process … from the creation of fake data, to the final working model.

Excel Turbo: https://www.youtube.com/channel/UCxy9ZMwZfP8ccgMrpbVtmTA
Cristiano Galvão at LinkedIn: https://www.linkedin.com/in/cristianogalvao/

#DynamicArrays

#ConditionalFormatting
#FILTERfunction

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2