Excel Expert Challenge with Wyn Hopkins of @AccessAnalytic : Votes and Data Quality

/in /by

Another Excel Expert Challenge! This time Wyn Hopkins of @AccessAnalytic stepped up to take on a challenge determining the top 4 color choices that were voted on. In this challenge Wyn had to make decisions about handling votes that didn’t fit the rules, and had quality issues.

These challenges are a way to show how other people think and work. Watch and listen as Wyn makes the decision to change directions with his solution.

You’ll see COUNTIFS, Power Query, Conditional Formatting and lots of other Excel features woven together for a solid solution.

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

Custom Sorting in Excel: sorting your data by season and by zodiac symbol

/in /by

Let’s go back to some basics. There’s a lot of cool, brand new stuff in Excel. But I still see basic goofs committed by longtime professionals. Recently I saw a lot of graphs that showed values in the order:
1. Afternoon
2. Evening
3. Morning
4. Overnight

That alphabetical order makes no sense based on what we know about real life and how a day works. 😵‍💫

In this video, we’re going to review Custom Sorting in Excel. Specifically:
– Sorting students by season/session
– Sorting your list of friends chronologically by their zodiac signs.

In this video I show you how to create a Custom List 2 ways:
1. the basic way in the Sort dialogue box and,
2. by importing a list that’s too big to type.

Also! You get to see XLOOKUP used with icons as the lookup values. 🥳

#CustomSorting
#exceltutorial
#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 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 – Helen Wall – Shifts & Overtime

/in /by

Next up! Helen Wall (linkedin.com/in/helenrmwall) as the Excel Expert who’s taking on an Excel challenge that she hadn’t seen before I hit the record button.

This time: we have data about scheduled shifts and several questions need to be answered about the data.
– How many distinct people are assigned?
– How much will be paid in regular pay?
– How much in overtime?
– How many hours have specific people been scheduled for?

We get to see how Helen reviews the situation and goes forward to answer the questions. She even shifts directions. Abandoning a formula-driven solution and jumping into Power Query.

Helen Wall at LinkedIn:
https://www.linkedin.com/in/helenrmwall/
Helen’s LinkedIn Classes:
https://www.linkedin.com/learning/instructors/helen-wall
0:00 Intro
5:59 The Challenge
17:13 Changing Directions – Power Query
41:20 Discussion of the solution
42:46 Outro

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

New: Semi-Joins in Power Query + a Brief Overview of Power Query’s Outer and Inner Joins

/in /by

Joins/Merges in Power Query are so useful but can be hard to understand. I’ve done videos on the Inner, Outer and Anti Joins.

Recently, Semi-Joins have been made available. Again: they can be tough to understand and see a reason for their use.

In this video you’ll see:
Intro: 0:00
Overview of Joins 0:23
Data Example 2:43
Inner Join 4:51
Semi Join 5:41
Outro 8:44

Basically:
If I have list A and list B
I want to know who is on both lists but I DO NOT want the data merged.

Examples for a Semi-Join:
– You have a list of actors and a list of movies with their details.
A semi-join can list the actors who appeared in the list of movies WITHOUT the movie details.
– You have a list of cars and a list of dealerships that have those cars.
A semi-join can reduce the list of cars to those that are available WITHOUT bringing over the dealership details.

#SemiJoin
#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

The New PIVOTBY Function + Warnings + a Weird Thing about COUNTA

/in /by

The Excel team has released a lot of functions. PIVOTBY is really slick and helpful because it’s dynamic and doesn’t require a refresh when your underlying data changes. But there are warnings.

You’ll see PIVOTBY with COUNT, PERCENTOF and COUNTA.
You’ll see strengths and limitations.
And! Something odd about COUNTA that I never noticed until playing with PIVOTBY.

For a list of my Excel courses at LinkedIn Learning:
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 Experts Live Challenge: Diarmuid Early vs. The Jumbled Course Completions

/in /by

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

/in /by

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

/in /by

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

/in /by

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