Excel Experts Live Challenge: Diarmuid Early vs. The Jumbled Course Completions

Another Excel Experts Live Challenge. This time the guest is Model-Off Champion and superstar in the Financial Modeling World Cup competitions.

This time we’re looking at a challenge I faced with a series of course details that got all jumbled and needed to be de-jumbled. Watch Diarmuid solve this in record time. And we get to know a bit about Diramuid himself.

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 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2

Use Excel to Count the Seconds in 2024 | Handling Belligerent Excel Requests

It’s great when people ask for Excel help. It’s not so great when the request is belligerent. In this video I show how I handled a belligerent request to count the number of seconds in the year 2024.

You’ll see Dynamic Arrays, SEQUENCE, date formatting, the DAY function and COUNTA … and insight on how to respond to nastiness.

For a list of my Excel courses at 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 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2

Mr. Excel SSN Challenge: Import last 4 of SSN | Process vs. Formula | Dynamic Arrays & Power Query

Bill Jelen, Mr. Excel, recently posted a video requesting input on ways to retrieve specific data while hiding sensitive data.

The challenge: how can you use a person’s last initial and the last-4 of their social security number to retrieve relevant details. HOWEVER! The full social security number should be available but not easily revealed.

In this video I use HSTACK, Power Query, Dynamic Arrays, LEFT and RIGHT to achieve this. But, rather than use formulas, I use simpler Excel features and focus on a process that imports the relevant data into the workbook where the lookups will be performed.

Using 2 workbooks, adds some friction to help separate the fully sensitive data and the daily use data.

#ExcelChallenge #ImportData #PowerQueryTutorial

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 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2

Retrieve the Adjacent Value from a Grid with XLOOKUP & Dynamic Arrays

You’ve got a grid and you want to input a code and retrieve the value tied to that code; e.g., input 33 and you want the name Trish.

This is easy if you have a single column of codes alongside a single column of values. That’s a plain VLOOKUP or XLOOKUP. HOWEVER! that’s not how this data is set up.

In this video I show how this can be done. But I also discuss the difference between using a single monstrous formula vs. taking intermediate steps.

We use TOCOL, OFFSET and IFERROR. So easy!

#XLOOKUP #DynamicArrays #OzduSoleil

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 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2

New Excel Feature: Insert Images into Cells

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 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2

Excel Expert Live Challenge | Celia Alves & The Cupcake Challenge

Celia Alves, MVP, Developer and content creator at @CeliaAlvesSolveExcel is the next one to step up for an Excel Experts Live Challenge!

Here, we have cupcake orders and flavors, and need to tally how many of each flavor needs to be made. But, it’s not that easy. There are twists. 😉

Check it out!

Watch how Celia uses color and borders to stay organized. She explores different randomization options, and regularly takes advantage of Dynamic Arrays to write 1 formula to generate multiple results. You’ll also see MATCH nested in CHOOSECOLS inside SUM! 🤯

FANTASTIC WORK!

How would you go about this solution?

#ExcelChallenge
#CeliaAlves
#DynamicArrays

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 co-written with Mr. Excel, Bill Jelen: Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2

Chandoo takes the Excel Expert Live Challenge | PALINDROMES!

Next in the series of Excel Expert Live Challenges we have world renowned Chandoo!
The challenge: Identify the palindromes (ignoring spaces and punctuation)

This is a fascinating experience as Chandoo shows some Excel mastery and mixes it with real world decisions about what’s worth chasing down and what’s not. It opens an important discussion about when it’s ok to be lazy when the alternative is spending time over-engineering a solution.

0:00 Introduction
1:04 Opening the challenge
2:00 Thoughts about language: Telugu vs. English
6:26 He’s done a challenge like this before
7:39 A benefit of being a content creator
8:43 Starting the challenge
9:30 Start with an easy one
16:06 Removing spaces and punctuation
27:23 Real world vs. hypothetical spreadsheet world
27:55 VICTORY!
28:50 Real world project
30:53 Wrap-up discussion

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.

My book co-written with Mr. Excel, Bill Jelen: Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
Website: https://ozdusoleil.com
My old blog: http://datascopic.net/blog-2-2

Victor Momoh takes the Excel Expert Live Challenge | Calculating Clopen Earnings

MVP, Dummer and Engineer, Victor Momoh from Lagos Nigeria steps up to take a live challenge. This time:

Calculate earnings based on clopens. What is a clopen? It’s when a person works the evening shift and closes a store, then is scheduled to return in the morning to open the store. Get it? Close, then open? CLOPEN

In this Excel Experts Live Challenge series I invite guest to solve a challenge they haven’t seen before. The goal is to take us into their minds and talk through their solution: what they see, what they anticipate, what they might try and why they won’t try other things.

Victor Momoh has a lot of interesting perspectives to share.

#ExcelChallenge
#VictorMomoh
#ExcelTutorial
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 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2

Excel Expert Live Challenge with Bill Jelen: Reception Party Guests

Mr. Excel, Bill Jelen, was gracious enough to be the first in a series that I call: Excel Expert Live Challenge. This is where I present a challenge to a Microsoft Excel expert and ask them to talk us through what they see, and the choices they make as they work toward a solution.

In this challenge Mr. Excel has to determine:
1. How many people are attending an event.
2. Calculate the size of available rooms
3. Determine which rooms are big enough–but not too big.

You’ll see: Text-to-Columns, INDIRECT, IFERROR, PRODUCT, Dynamic Arrays, UNIQUE, XLOOKUP, and Conditional Formatting.

0:00 Intro
1:28 Bill opens the challenge for the first time
3:40 Bill checks the data quality
4:00 Building a lookup table
7:33 Experimenting with INDIRECT
10:34 Adding a real world curve-ball
11:10 Troubleshooting
15:30 Discussion
18:40 Extra refinements
19:29 Conditional Formatting to highlight a row
20:30 Reflections and wrap-up

#ExcelChallenge #MrExcel #OzduSoleil

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 co-authored with Mr. Excel, Bill Jelen: Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2