Oz du Soleil: Risk Taking as a Content Creator | Global Excel Summit

If you missed the 2021 Global Excel Summit, full event content now available via the event website
👉 https://globalexcelsummit.com/

This video is my presentation: Risk Taking as a Content Creator

#GlobalExcelSummit
#ContentCreation
#RiskTaking

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

NEW FEATURE! Import Dynamic Arrays into Power Query + 8 Warnings

Just 2 days ago it became possible to import Dynamic Arrays into Power Query. 🙏🏼🎉
This is really cool. HOWEVER! There are 8 warnings that I take you through.

First. There is no longer “table-slash-range.” The icon has been changed to “From Sheet.” BOOOO! 🤪 Table/range seems more accurate.

Check out the video for the other 7 warnings. They’re too hard to explain. You just have to see them.

#DynamicArrays
#ImportFromDynamicArrays
#Power Query

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

NEW FEATURE! Import Dynamic Arrays into Power Query + 8 Warnings

Just 2 days ago it became possible to import Dynamic Arrays into Power Query. 🙏🏼🎉
This is really cool. HOWEVER! There are 8 warnings that I take you through.

First. There is no longer “table-slash-range.” The icon has been changed to “From Sheet.” BOOOO! 🤪 Table/range seems more accurate.

Check out the video for the other 7 warnings. They’re too hard to explain. You just have to see them.

#DynamicArrays
#ImportFromDynamicArrays
#Power Query

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

Splitting Multiple Columns in Power Query | Battling null values and line-feeds

In a previous video I dealt with a common need to split multiple columns of data. However, in that dataset the delimiter was obvious and all cells were filled in.

But! What happens when things get weird? In this video, the delimiter is a line-feed and there’s a problem with empty cells. We use Text.Split and Table.FromColumns in Excel’s Power Query; then we have to go back and get rid of null values.

Download the workbook: https://datascopic.net/SMR2

#PowerQuery
#Text.Split
#SplitMultipleColumns

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

An Excel Meditation with Tables, XLOOKUP, Dynamic Arrays in a Dynamic Dropdown List Solution

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

ExcelHash 2021 Bill “Mr. Excel” Jelen and Oz du Soleil discuss all 17 solutions

ExcelHash was a lot of fun this year. We opened it up to more participants and the solutions were brilliant.
In this video, Bill Jelen (Mr. Excel) and Oz du Soleil discuss each solution: the thrills, the chills, the funny moments and cool, clever Excel tips that blew us away!

0:00 Intro
6:13 Fred Le Guen – excelexercice
7:29 Wyn Hopkins – Access Analytic
9:52 Paula Guilfoyle
11:55 David Benaim

16:07 Bill Jelen – MrExcel.com

18:32 What does EVEN do?
19:50 Bill Jelen continued
21:06 Ajay Anand
23:22 Oz du Soleil – Excel on Fire
27:48 Alan Murray – Computergaga
29:30 Jon Acampora – Excel Campus – Jon

31:46 Chandoo
33:48 Sumit Bansal – TrumpExcel
35:43 Jordan Goldmeier
39:05 Abiola David – Excel Jet Consult
41:11 Fara Shaikh
43:03 John Michaloudis – MyExcelOnline.com
44:18 Cristiano Galvão – Excel Turbo
47:26 John MacDougall – How to Excel
49:41 Wrap up comments
53:19 Pick random winners for the gift certificates

#ExcelHash
#ExcelChallenge
#ExcelReview

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

Excel Hash 2021: The Facilitator | LET function | Power Query Custom Data Types | Excel Challenge

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

Bitten by Power Query: a lesson about knowing our data, our tools and what can go wrong.

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

Multiple Dropdown Lists | Dynamic Arrays | Filter Function

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