Excel’s New IMAGE Function: the how-to and the gotchas

/in /by

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

Creativity and Video Editing in Camtasia

/in /by

I’ve received a lot of questions about
– Editing techniques in Camtasia
– Where my creative ideas come from

In this video I take you on a tour of a recent video. Specifically, this one on TEXTBEFORE:

Let me know if you have any questions about anything in this video.

#Camtasia
#Camtasia2022
#videoeditingtricks

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

DataRails and The Functionary: Excel Top 5 Wall of Shame

/in /by

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

Create Variable Columns in Power Query – Goodly Challenge – The First Class Edition

/in /by

Here is an interesting challenge. With a column of names, and a column of musical preferences and each person has multiple music preferences, how can the preferences be split such that you have TRUE if a person has that preference, and FALSE if a person does not?

One twist: what if there’s a person added/removed or a music preference is added/removed?

This challenge came from Chandeep Chhabra at the Goodly YouTube channel.
My solution in Power Query includes:
Left Outer Join with 2 criteria
Cross Join
Conditional Column
Pivot Don’t Aggregate

All handy features that can help you create dynamic solutions in Excel and Power Query.

See the challenge as described by Chandeep: https://youtu.be/7Vow1L8Mu9g

0:00 Intro
0:54 Explaining the challenge
2:15 Starting the solution
4:39 Cross Join
6:34 Merge queries with 2 criteria
8:29 Pivot Don’t Aggregate
9:50 Add more data
10:49 Outro

#ExcelChallenge #FirstClass #PowerQuery

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 Campus License Plate Challenge: The Down & Dirty Edition

/in /by

Jon Acampora of Excel Campus issued a challenge to designate if a license plate is odd or even. In a previous video I showed a Power Query challenge. In this video I show a quick solution that will work, BUT, you have to be wary.

This solution isn’t so sophisticated but it’s worth showing you because sometimes all you need is a down-&-dirty one-time solution. Other times, you do need a solution that is robust, dynamic, and future-proof.

In this video I use Flash Fill, ISODD and IF. They work!

My book: Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
#ExcelChallenge
#ExcelOnFire

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 old blog: http://datascopic.net/blog-2-2

Excel Campus License Plate Challenge: Odd vs. Even Days & License Plates

/in /by

Jon Acampora of Excel Campus has an interesting challenge: https://youtu.be/-ByG6ULWOtA
Yellowstone National Park has a system where cars with even number license plates can come into the park on even numbered days. Odd number plates can enter on odd dates.

But what if a license plate doesn’t end with a number? (4CE-3DQ)
What if it doesn’t have any numbers? (BIG-DAY)

In this video I show a Power Query solution that uses the Information feature and the Text.Remove function.

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 in Excel: Get Data from Image | Excel vs. OneNote

/in /by

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

/in /by

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

/in /by

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