New in Excel: Get Data from Image | Excel vs. OneNote

Excel finally has a built-in feature for extracting data/text from an image. In this video I have 2 demonstrations and compare the results to OneNote’s “Copy Text From Image.”

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

Discussion: Learning Excel & 3 Types of Mastery | Guerrilla Data Analysis 3 | Skirmishes

This question comes to me at least weekly: “Oz, how can I master Excel?”
In this video I dissect various ways that we can think about ‘mastery.’ And I close with some advice for no matter what you do in Excel.

0:00 Guerrilla Data Analysis 3 is now available as PDF
0:30 Description of Guerrilla Data Analysis 3
1:00 Octopus Spreadsheets
2:17 “I want to master Excel”
2:48 Mastering a Tool
3:23 Knowing EVERYTHING in Excel
4:40 The Superhuman
5:52 Mastery as in: “providing value”
6:30 Value as a freelancer
7:40 Telling the truth & breaking out
9:51 Advice from Uncle Oz
12:32 Bokeh Effect

To purchase Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
#MasteringExcel

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

The VSTACK Function | Dynamic Arrays | Stacking variable data into a single array

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 Challenge & Meditation | Isolate the winners of split-decisions | Dynamic Arrays

You have a list of contests and the decisions made by the judges in 3 columns, like this:

Contest1: Goblins – Sharks – Sharks
Contest2: Penguins – Thunder – Penguins

How can use Excel to show that the Sharks won Contest1 and the Penguins won Contest2?

That’s the challenge. This video includes a meditation and time to think about how you’d isolate the winners from 20 contests.

#DynamicArrays
#ExcelChallenge
#ExcelMeditation

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

TEXTBEFORE function | Splitting names from professional designations

The TEXTBEFORE functions was recently released in this video I show 3 uses for TEXTBEFORE, it’s strengths and limitations.

In this example we have names and professional designations. The problem is some people have more than one designations, some have 1 designation and some folks have no designations.

BUT! We have to be careful. We can’t target all suffixes for removal.
REMOVE:
CFP, VP, MD and DDS
KEEP:
Jr. and III

#TEXTBEFORE
#SplittingNames
#SplittingText
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

TOCOL and DROP | 2 of the 14 new Excel functions | Unwinding a grid

14 new functions were released into Excel recently. In this video I cover two of them:
TOCOL: converts a rectangular matrix into a single column
DROP: retrieves a rectangular array and drops (eliminates) columns that aren’t needed. One thing to know: you can drop columns starting from the beginning or end of a range. You can eliminate, say, the first 3 columns, but you cannot drop columns from the middle of the range.

I show this along with the UNIQUE and SORT functions, and a dropdown list and conditional formatting.

#TOCOL
#DynamicArrays
#ConditionalFormatting

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

Sum variable and custom ranges with XLOOKUP: the Victor Momoh Edition

I was on a couple of Meetup Zoom calls and Victor Momoh was the host. He showed some fascinating stuff by using XLOOKUP to retrieve a range of data. He then used it to make sums and all kinds of other cool things. I share some of that in this video with you.

– Victor Momoh presenting at the Saudi Arabia Excel Meetup Group.

– See Victor Momoh explain XLOOKUP returning a range instead of a value

– Victor’s YouTube channel:
https://www.youtube.com/c/ExcelMoments/videos
– Download the workbook in this video:
https://datascopic.net/momoh
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

Extract Bold Font from a String of Text in a Cell – Baffmasta Edition

My very first need for Excel’s VBA was back in 2009. I had a lot of data and the content I needed was the bold font in the cells. Some how, the non-bold font needed to go away.

Back then, some kind soul on an online forum wrote me some VBA code that I didn’t understand, but it worked. Today, I’ll show you how how I could have done this without VBA.

#baffmasta
#ExtractBoldText
#RemoveRegularText

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

Mr. Excel Seating Chart Challenge: 100 people 10 tables 11 Sessions * Can everyone meet everyone?

Mr. Excel, Bill Jelen, contacted me with a question that he’d been asked:
Can you arrange seating such that every person in attendance will sit at a table with every other person, at least once? The parameters:

– 100 people
– 10 tables
– 10 seats t each table
– 11 rounds

Bill concluded that there’s no way to connect everyone in only 11 rounds. He said the best he could achieve is 65%. Here is his video: https://youtu.be/0StppWCBgnY

I found this fascinating because it presents a real world scenarios where the ultimate goal can’t be achieved. We have to go back to the person who made the request and tell the truth. Then we have to ask if there’s any flexibility. Can we get more tables, bigger tables or add more rounds to the 11?

I came up with a solution that requires 19 rounds.

After thinking about my days as a wrestler, and round-robin tournaments, I could see adding people to 5-person teams, and then create an agenda that gets each of the 20 teams to meet, rather than try to work with 100 people.

In a real scenario, my 19 rounds would be a suggestion. The boss/client/friend/co-worker who made the request would have to decide if it’s an acceptable solution, or not.

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