Segment a Dataset Automatically | Dynamic Arrays & Conditional Formatting | ExcelTurbo Challenge

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: http://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

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