8 Warnings about Excel Wolfram Data Types | Linked Data Types | Excel Rich Data Types

/in /by

The new Wolfram Data Types are pretty interesting, but there are things that you have to watch out for. This video is a sampling of 8 things that are weird about the data types and how they work within Excel.

1:19 Fascinating and Incomplete Entries
2:12 Unexpected Translation
3:29 False Negatives. When looking for a movie, the result was “no data found.” But, I was able to find it.
4:15 Sometimes results take up several cells. You’ve gotta watch for the data to show vertical or horizontal.
6:04 No Sports Data. On the Wolfram site sports data is available. But it’s not available to import into Excel.
7:10 Watch Your Measurements. I imported the heights of celebrities. It was in meters and had to be converted to feet & inches.
8:17 Paste-as-Values and Flash fill don’t work with Wolfram Data Types.
9:47 Names don’t equal Names. Be careful. Do you want a celebrity’s birth name (Paul Hewson) or the name they go by (Bono)?

#Wolfram
#DataTypes
#ExcelDataTypes

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

Build a Solution from Scratch | Dynamic Arrays & Conditional Formatting | Excel Turbo Challenge

/in /by

NOTE: this videos shows how I build a solution from scratch.

Cristiano Galvão of Excel Turbo posted something fascinating on LinkedIn. He had a main table of data and one column showed each record as Active or Inactive. There were two interesting things about the spreadsheet:
1. If a record was Inactive, the font was automatically gray. If it went from Inactive to Active, the font returned to normal.
2. Inactive records automatically populated on an Inactive worksheet. Active records automatically populated on an Active sheet. If a record changed statuses, it would automatically move to the right sheet.

I was intrigued. 💡🤔

I wanted to recreate this solution but I also know that records don’t always fit into neat categories. So, the solution in this video includes a 3rd category: Unassigned.

In this video you’ll see me figure this out for the first time, and watch my development process. That is more important than the actual solution. I didn’t work the solution out ahead of time and I don’t start with any data.

You get to see the whole process … from the creation of fake data, to the final working model.

Excel Turbo: https://www.youtube.com/channel/UCxy9ZMwZfP8ccgMrpbVtmTA
Cristiano Galvão at LinkedIn: https://www.linkedin.com/in/cristianogalvao/

#DynamicArrays

#ConditionalFormatting
#FILTERfunction

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

XLOOKUP Partial Match with Numbers | the Multiply by 1 Trick | Getting Good with Excel Takes Time

/in /by

I got a plea one night from someone who needed to match some numbers. He had the right instinct but he stumbled up on a situation where you just have to have seen a lot of Excel over a long period of time to know about this hack.
He needed to match the first 4 numbers in an 8-number string.

He tried the VLOOKUP and LEFT functions. But he didn’t know about the “Multiply by 1 Trick.” You’ll see that trick in this video, but I use XLOOKUP. We’re all about being modern over here. 🤗🤩😁🌶

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

2 Custom Data Types | 1 Dataset | 1 Warning

/in /by

Custom Data Types in Excel and Power Query are pretty interesting. In this video I continue to explore them.
1. We add 2 Custom Data Types in a single dataset.
2. In situations where a merge/join has to happen, it’s best to do the merge first, and then create the Custom Data Type.

1:25 Introducing the data and challenge
2:20 Going into Power Query to clean the data: Column by Example, Split Column into Rows
3:29 Warning: merge before creating custom data types
4:00 Merge: Left Outer Join
4:37 Create the Students Custom Data Type
5:32 Create the Advisors Custom Data Type
6:46 Correcting and updating the data
8:30 Detailed warning about creating Custom Data Types before merging datasets.
9:41 Outro

You’ll also see:
– Column by Example
– Split column by delimiter
– Split columns into rows
– Left Outer Join
– Right Outer Join

Download the workbook
https://datascopic.net/wp-content/uploads/2020/11/2-Custom-Data-Types.xlsx
#CustomDataTypes

#PowerQuery
#OuterJoin

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

Creating a Custom Data Type with Power Query; including XLOOKUP and Dynamic Arrays

/in /by

The new Custom Data Types are starting to be rolled out and they’re … exciting … interesting … spooky.

This video will be the first of several that will explore the Custom Data Types, how they can be used and where to watch for GOTCHAs.

In this video we create a basic Custom Data Type using information about specialists. We have their names, contact info, specialties, birthday and school data. Later we add in their hourly rates by using XLOOKUP.

#XLOOKUP
#CustomDataTypes
#PowerQuery

You also see the FILTER function for Dynamic Arrays. GOOD STUFF! 🎉

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

Power Query: Identify & Retrieve Partial Strings of Text

/in /by

It’s one thing to identify if a partial strong of text exists in a cell. It’s another to identify it and also dig it out. That’s the challenge in this video.
1. Identify if an address contains SW, South West, NE or Northeast.
2. If one of those is found, populate what was found in the cell next to the address.

This requires thinking really slowly and planning.
The first step in this video is a cross-join. Along the way, we use a left outer join and the Power Query command: Text.Contains

It’s all here!

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 Patience | Sort birthdays by the day and month | Insert 7 Rows Between Entries

/in /by

0:00 – 2:17 Intro & Set-up
2:18 – 4:48 A dark day in Arizona
4:49 – 5:33 Sort birthdays by day and month
5:35 – 7:55 Insert 7 rows between each row

This video is primarily about patience with Excel and finding solutions. Often people will comment that I make things look so easy. In this video I take the pressure off. I reveal that a lot of solutions took a long time to find, and the 5-min tutorials mask the hard work.

So. If you take a long time finding solutions, that’s how this works. Be patient. It’s not easy.

This is demonstrated in a solution for a friend. She had over 2000 rows of data and needed to insert 7 rows between each of them. It took a half day to look for a solution. Back then I used VLOOKUP. Today I used XLOOKUP and SEQUENCE (dynamic arrays).

You’ll also see how to sort birthdays by the month and day, ignoring the year. Example:
Angelo, 12APR67
Kim, 17JAN81
Nettie, 12APR93
Pete, 1NOV88
Yukio, 29SEP96

We want to sort (ascending) so that Kim is on top because her birthday is 17JAN (earliest in the year). Pete should be on the bottom because his birthday is 1NOV.

If we were to sort the birthday column as-is, Nettie would be on top because her birthday is the most recent year, 1993. NOT WHAT WE WANT!
We use the TEXT function in order to sort and ignore the year.

#XLOOKUP
#SEQUENCE
#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: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Excel Dynamic Arrays with XLOOKUP & Form Controls (Radio Buttons and Tick Boxes)

/in /by

One way to get good with Excel is to play with it. In this video I decided to play with Dynamic Arrays and Excel’s form controls.The result is a pretty neat solution with tick-boxes and radio-buttons that allow the user to toggle a dataset between sorting ascending and descending.This video includes:
– XLOOKUP
– Tick Boxes
– Radio Buttons
– SORT
– FILTER
– IFERROR

#DynamicArrays
#XLOOKUP
#ExcelFormControls

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

DATEDIF & Dynamic Arrays | Calculating the months to complete a project | Don’t Rush Edition

/in /by

How do you calculate the number of months to complete a project? Here are problems:
Problem 1:

This project had 5 steps and each started and finished on different days …
Step1 12JAN – 19FEB
Step2 15JAN – 19JAN
Step3 30JAN – 27AUG
Step4 5JUL – 15JUL
Step5 1AUG – 11AUG

Problem 2:
A stack of multiple projects with multiple steps.

Solution involves:
Dynamic Arrays
DATEDIF
UNIQUE
MINIFS & MAXIFS

#DynamicArrays
#DATEDIF
#UNIQUE

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