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
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
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
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
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
In my previous video I uncovered something odd about VSTACK. Turns out, VSTACK is doing what it’s supposed to do. I show that in the video. And that leads to something fascinating: EXPAND.
The EXPAND function on its own seems strange and pointless. However, in this situation EXPAND saves the day in a practical example.
We have a start date and number of days and want to list each date. Once their listed, we’d like them to be compiled in a single array. The problem is, the rows don’t have an even number of dates. That’s where EXPAND comes in! EXPAND makes all rows equal so that the data can be both, dynamic and compiled in a single array.
You’ll also see the TEXT function used to convert a number into a date.
#DynamicArrays #EXPAND #ExcelTips
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
This video includes the weaving together of 7 dynamic array functions in the creation of a solution.
Wyn Hopkins, fellow Microsoft Excel MVP, presented a challenge recently:
If you have a list of people and their ticket numbers:
Lisa Tickets: 300-303
KC Tickets 390-390
Aaron Tickets 772-778
How can you get a list of the individual ticket numbers alongside the name of the ticket holders? E.g. 4 rows for Lisa with tickets 300, 301, 302, 303.
I show you a solution using:
FILTER, SEQUENCE, TOCOL, TEXTBEFORE, TEXTAFTER, HSTACK, VSTACK
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
Power Query does some odd things because it’s so case-sensitive.
If you want to sort this list:
iPhone
Ice Cream
USB cable
Power Query will return:
Ice Cream
USB Cable
iPhone
If you need to do a merge with:
De Soto and de Soto
Power Query will not get those matched.
In this video I show you how to use Text.Upper to get the sorting right and Fuzzy Matching and Similarity Threshold to get the merge right.
Thank you to Ed Hansberry for his blogpost that shows details on merging and ignoring case-sensitivity:
https://www.ehansalytics.com/blog/2020/4/27/case-insensitive-merges-in-power-query
Plus! I share with you where I’ve been for the past month and invite you to Excel Days in Bulgaria on 11NOV22.
0:00 Introduction
0:15 Roadtrip Overview
1:05 Excel Days in Sofia Bulgaria
3:03 Power Query: sorting without case-sensitivity
7:16 Power Query: merging and ignoring case-sensitivity
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 Excel challenges that comes out every Friday for beginners and every-other-Monday for power users.
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
There’s a new function in Excel–a function that we’ve been waiting and hoping for for a long time.
The IMAGE function.
IMAGE makes it easy to bring images into Excel and use them as variables. However, you can count on me to not only show you how Excel features work, but also issue warning. This time, the thing to worry about: Link Rot.
And here’s where you can download the file: https://datascopic.net/imagef
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