Excel Expert Challenge with Wyn Hopkins of @AccessAnalyticLearn : Votes and Data Quality Excel On Fire (Oz du Soleil) 2026-09-25 | Excel Expert Challenge with Wyn Hopkins of @AccessAnalyticLearn : Votes and Data Quality
Chandoo takes the Excel Expert Live Challenge | PALINDROMES! Excel On Fire 2023-04-15 | 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 Introduction1:04 Opening the challenge2:00 Thoughts about language: Telugu vs. English6:26 He's done a challenge like this before7:39 A benefit of being a content creator8:43 Starting the challenge9:30 Start with an easy one16:06 Removing spaces and punctuation27:23 Real world vs. hypothetical spreadsheet world27:55 VICTORY!28:50 Real world project30:53 Wrap-up discussionFor a list of my Excel courses at Lynda/LinkedIn:linkedin.com/learning/instructors/oz-du-soleilThere 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 Editionmrexcel.com/products/guerrilla-data-analysis-3rd-editionWebsite: http://ozdusoleil.comMy old blog: http://datascopic.net/blog-2-2
Victor Momoh takes the Excel Expert Live Challenge | Calculating Clopen Earnings Excel On Fire 2023-03-26 | Excel MVP, Drummer and Engineer, Victor Momoh (Victor's channel: ExcelMoments) 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? CLOPENIn 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. Find him at: youtube.com/@ExcelMoments/videos#ExcelChallenge #VictorMomoh#ExcelTutorial For a list of my Excel courses at Lynda/LinkedIn:linkedin.com/learning/instructors/oz-du-soleilThere are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.Website: http://ozdusoleil.comMy book: Guerrilla Data Analysis 3rd Editionmrexcel.com/products/guerrilla-data-analysis-3rd-editionMy old blog: http://datascopic.net/blog-2-2
Excel Expert Live Challenge with Bill Jelen: Reception Party Guests Excel On Fire 2023-03-01 | Mr. Excel, Bill Jelen, (Billl's Channel: youtube.com/@MrXL/videos) 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 rooms3. 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 Intro1:28 Bill opens the challenge for the first time3:40 Bill checks the data quality4:00 Building a lookup table7:33 Experimenting with INDIRECT10:34 Adding a real world curve-ball11:10 Troubleshooting15:30 Discussion18:40 Extra refinements19:29 Conditional Formatting to highlight a row20:30 Reflections and wrap-up#ExcelChallenge #MrExcel #BillJelenFor a list of my Excel courses at Lynda/LinkedIn:linkedin.com/learning/instructors/oz-du-soleilThere are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.Website: http://ozdusoleil.comMy book co-authored with Mr. Excel, Bill Jelen: Guerrilla Data Analysis 3rd Editionmrexcel.com/products/guerrilla-data-analysis-3rd-editionMy old blog: http://datascopic.net/blog-2-2
Practical use for Excels EXPAND function | VSTACK mystery solved | Dynamic Arrays Excel On Fire 2022-12-13 | 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:linkedin.com/learning/instructors/oz-du-soleilThere are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.Website: http://ozdusoleil.comMy book: Guerrilla Data Analysis 3rd Editionmrexcel.com/products/guerrilla-data-analysis-3rd-editionMy old blog: http://datascopic.net/blog-2-2
The Ticket Challenge from Wyn Hopkins | Convert a matrix into 2 columns using dynamic arrays Excel On Fire 2022-12-06 | 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-303KC Tickets 390-390Aaron Tickets 772-778How 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#excelchallenge #DynamicArrays #exceltutorial For a list of my Excel courses at Lynda/LinkedIn:linkedin.com/learning/instructors/oz-du-soleilThere are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.Website: http://ozdusoleil.comMy book: Guerrilla Data Analysis 3rd Editionmrexcel.com/products/guerrilla-data-analysis-3rd-editionMy old blog: http://datascopic.net/blog-2-2
Power Query: Sorting & Merging and ignoring Case-Sensitivity | Excel Days in Bulgaria | My Roadtrip Excel On Fire 2022-10-24 | Power Query does some odd things because it's so case-sensitive.If you want to sort this list:antArt MuseumUSB cablePower Query will return:Art MuseumUSB CableantIf you need to do a merge with:De Soto and de SotoPower 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:ehansalytics.com/blog/2020/4/27/case-insensitive-merges-in-power-queryPlus! I share with you where I've been for the past month and invite you to Excel Days in Bulgaria on 11NOV22.0:00 Introduction0:15 Roadtrip Overview1:05 Excel Days in Sofia Bulgaria3:03 Power Query: sorting without case-sensitivity7:16 Power Query: merging and ignoring case-sensitivityI also show the CODE function and offer insight into how Power Query does its sorting.#PowerQuery #CaseSensitivity #ExcelTutorialFor a list of my Excel courses at Lynda/LinkedIn:linkedin.com/learning/instructors/oz-du-soleilThere 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: http://ozdusoleil.comMy book: Guerrilla Data Analysis 3rd Editionmrexcel.com/products/guerrilla-data-analysis-3rd-editionMy old blog: http://datascopic.net/blog-2-2
Excels New IMAGE Function: the how-to and the gotchas Excel On Fire 2022-09-03 | 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: datascopic.net/imagefFor a list of my Excel courses at Lynda/LinkedIn:linkedin.com/learning/instructors/oz-du-soleilThere are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.Website: http://ozdusoleil.comMy book: Guerrilla Data Analysis 3rd Editionmrexcel.com/products/guerrilla-data-analysis-3rd-editionMy old blog: http://datascopic.net/blog-2-2
Creativity and Video Editing in Camtasia: A tour of a completed videos timeline Excel On Fire 2022-08-25 | I've received a lot of questions about- Editing techniques in Camtasia- Where my creative ideas come fromIn this video I take you on a tour of a recent video. Specifically, this one on TEXTBEFORE:youtube.com/watch?v=QbmgufI_suoLet me know if you have any questions about anything in this video.0:00 Intro0:31 Overview2:14 A Warning about Gear and Editing Video3:08 Transitions & The Library4:40 Text on Top of Video5:37 Layering Videos7:44 Text & Sound Effects11:16 Audio Fades & Curves11:54 Highlights & Enhancements13:41 Decision:"Is it good enough?"14:30 Layering Sound Effects16:23 Mood & Theme19:04 Sound Effects & Enhancements 20:41 Jeymes Samuel & Artistic Decisions23:25 Thinking about Music24:58 More Music, Effects, & Enhancements28:55 More Music & The Outro#Camtasia#Camtasia2022 #videoeditingtricks For a list of my Excel courses at Lynda/LinkedIn:linkedin.com/learning/instructors/oz-du-soleilThere are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.Website: http://ozdusoleil.comMy book: Guerrilla Data Analysis 3rd Editionmrexcel.com/products/guerrilla-data-analysis-3rd-editionMy old blog: http://datascopic.net/blog-2-2
DataRails and The Functionary: Excel Top 5 Wall of Shame Excel On Fire 2022-08-10 | Video was shot by videographer Will Snyder of Snyder Visuals in Portland, ORsnydervisuals.comProduced by DataRailslinkedin.com/company/datarails----For a list of my Excel courses at Lynda/LinkedIn:linkedin.com/learning/instructors/oz-du-soleilThere are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.Website: http://ozdusoleil.comMy book: Guerrilla Data Analysis 3rd Editionmrexcel.com/products/guerrilla-data-analysis-3rd-editionMy old blog: http://datascopic.net/blog-2-2
Create Variable Columns in Power Query - Goodly Challenge - The First Class Edition Excel On Fire 2022-07-27 | 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 criteriaCross JoinConditional ColumnPivot Don't AggregateAll handy features that can help you create dynamic solutions in Excel and Power Query.See the challenge as described by Chandeep: youtu.be/7Vow1L8Mu9gWyn Hopkins' challenge: youtu.be/7A43scSbxCk0:00 Intro0:54 Explaining the challenge2:15 Starting the solution4:39 Cross Join6:34 Merge queries with 2 criteria8:29 Pivot Don't Aggregate9:50 Add more data10:49 Outro#ExcelChallenge #FirstClass #PowerQueryFor a list of my Excel courses at Lynda/LinkedIn:linkedin.com/learning/instructors/oz-du-soleilThere are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.Website: http://ozdusoleil.comMy book: Guerrilla Data Analysis 3rd Editionmrexcel.com/products/guerrilla-data-analysis-3rd-editionMy old blog: http://datascopic.net/blog-2-2
Excel Campus License Plate Challenge: The Down & Dirty Edition Excel On Fire 2022-07-20 | 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 Editionmrexcel.com/products/guerrilla-data-analysis-3rd-edition#ExcelChallenge#ExcelOnFireFor a list of my Excel courses at Lynda/LinkedIn:linkedin.com/learning/instructors/oz-du-soleilThere are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.Website: http://ozdusoleil.comMy old blog: http://datascopic.net/blog-2-2
Excel Campus License Plate Challenge: Odd vs. Even Days & License Plates Excel On Fire 2022-07-18 | Jon Acampora of Excel Campus has an interesting challenge: youtu.be/-ByG6ULWOtAYellowstone 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.Guerrilla Data Analysis 3rd Ed. can be purchased at (printed book or ebook): mrexcel.com/products/guerrilla-data-analysis-3rd-editionFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 3rd Edition amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470743 My old blog: http://datascopic.net/blog-2-2
New in Excel: Get Data from Image | Excel vs. OneNote Excel On Fire 2022-07-06 | 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: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Discussion: Learning Excel & 3 Types of Mastery | Guerrilla Data Analysis 3 | Skirmishes Excel On Fire 2022-06-19 | 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 PDF0:30 Description of Guerrilla Data Analysis 31:00 Octopus Spreadsheets2:17 "I want to master Excel"2:48 Mastering a Tool3:23 Knowing EVERYTHING in Excel4:40 The Superhuman5:52 Mastery as in: "providing value"6:30 Value as a freelancer7:40 Telling the truth & breaking out9:51 Advice from Uncle Oz12:32 Bokeh EffectTo purchase Guerrilla Data Analysis 3rd Editionmrexcel.com/products/guerrilla-data-analysis-3rd-edition#MasteringExcelFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
The VSTACK Function | Dynamic Arrays | Stacking variable data into a single array Excel On Fire 2022-04-20 | 14 new functions were released in Excel recently. Today, we dig into the what's what of the VSTACK function. It allows you to take separate datasets and assemble them such that they behave as a single dataset.0:00 Intro1:03 Explanation of the challenge2:21 Demo of my solution3:54 Building the solution9:44 Outro#VSTA#DynamicArrays#TopTenFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Excel Challenge & Meditation | Isolate the winners of split-decisions | Dynamic Arrays Excel On Fire 2022-04-10 | You have a list of contests and the decisions made by the judges in 3 columns, like this:Contest1: Goblins - Sharks - SharksContest2: Penguins - Thunder - PenguinsHow 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#ExcelMeditationFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
TEXTBEFORE function | Splitting names from professional designations Excel On Fire 2022-04-08 | 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#SplittingTextFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
TOCOL and DROP | 2 of the 14 new Excel functions | Unwinding a grid Excel On Fire 2022-03-29 | 14 new functions were released into Excel recently. In this video I cover two of them:TOCOL: converts a rectangular matrix into a single columnDROP: 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.0:00 Introduction0:40 Microsoft History Lesson - Cyrillius J. Longfoot6:54 The Excel challenge - TOCOL function9:19 DROP function11:45 UNIQUE and SORT functions13:15 Dropdown List13:57 Conditional Formatting15:01 Outro#TOCOL#DynamicArrays#ConditionalFormattingFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Sum variable and custom ranges with XLOOKUP: the Victor Momoh Edition Excel On Fire 2022-03-02 | I was on a couple of Meetup Zoom calls and Victor Momoh was the guest. 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.0:00 Introduction0:24 My trip to Lagos3:35 Using XLOOKUP to create the sum of a variable range6:15 Using XLOOKUP to sum a variable range with variable start and end points7:20 Using XLOOKUP and OFFSET to calculate variable distances 9:00 Outro- Victor Momoh presenting at the Saudi Arabia Excel Meetup Group.youtu.be/GEtqkpc4uvI- See Victor Momoh explain XLOOKUP returning a range instead of a valueyoutu.be/x_4azNNwC9U?t=1833- Victor's YouTube channel:youtube.com/c/ExcelMoments/videos- Download the workbook in this video:datascopic.net/momoh#XLOOKUP #SumRange #CalculateDistanceFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Extract Bold Font from a String of Text in a Cell - Baffmasta Edition Excel On Fire 2022-01-24 | 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#RemoveRegularTextFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Mr. Excel Seating Chart Challenge: 100 people 10 tables 11 Sessions * Can everyone meet everyone? Excel On Fire 2021-10-27 | 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 roundsBill 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: youtu.be/0StppWCBgnYI 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: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Excel: Importing data from an image Excel On Fire 2021-10-02 | Recently, I've been getting mail that contains data that I need to get off of the paper and into Excel. Typing the data into Excel is one option. But I figured there must be better ways.In this video I show 2 ways to get data by taking a picture, then converting the text from the data into something that Excel can use. You'll see:- Save the image in Microsoft Word as a PDF- Import the image into OneNote, then use Extract Text from Image.NOTE: All methods involve some kind of clean-up. The trick is to figure out which method results in the least mess.0:00 Intro1:55 A comment about the Excel phone app2:20 Import work schedule4:37 Import workshop data7:16 Import from a magazine page8:35 Why not import PDF using Power Query?9:10 OutroFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Power Query: Split by Variable Columns (BONUS: Road Trip Summary) Excel On Fire 2021-08-29 | It's easy to use Power Query to split by, say, 5 columns. But, what if your data changes and needs to be split by 3 columns or 10 columns? That's trickier--especially since Power Query will hard-code that 5 in the query and will ignore future changes.This video shows how to get Power Query to cooperate. It takes a few steps, but we get it done!Also. You'll get to see where I've been during my time away. I finally went on a road trip that I'd been putting off for 7 years.#Roadtrip#SplitVariableColumns#SplitColumnsinExcelFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Power Query Challenge: Split by Multiple Delimiters | Merge All Columns Excel On Fire 2021-07-12 | Iranian Dr. Excel posted a challenge on LinkedIn:How can you split columns when there are multiple, different delimiters? Ex:Joe+Lisa+Francine/KathyRita&Sal&GeneSamantha/DeniseThis was interesting for several reasons:1. It's very easy to do if you want a 1-and-done solution with just 3 delimiters. However,2. If you want something truly dynamic, it involves solving 2 major problems with Power Query: splitting variable columns and merging all columns in a query.3. I show an ugly but effective solution that I call The Elephant Through the Front Door Move.First. When splitting columns with Power Query, a value gets hard-coded; e.g., if your initial split goes into 5 columns, the 5 is stuck, and if you later have 7 columns or 3 columns, that 5 is still there.I found a solution to this here:https://www.goodly.co.in/split-by-variable-columns-in-power-query/Second. Sometimes we want to merge all of the columns in a query, but there's no feature for that. But here is Power Query M-Code: exceltown.com/en/tutorials/power-bi/power-query-m-language/merging-of-all-columns-in-power-query-regardless-of-their-namesThis was hard! But the solution I show is truly dynamic. If there are more delimiters in future data, they get picked up; if the number of columns grows or expands, Power Query will cooperate.#SplitByDelimiters#MergeColumns#PowerQueryFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Excel NETWORKDAYS part 3: calculating custom weekends with Power Query Excel On Fire 2021-06-20 | In the previous 2 videos we've looked at calculating NETWORKDAYS and days off that aren't just Saturday and Sunday. Thanks for Bill Szysz, Wayne Edmondson, XLarium and others for leading a lively discussion and offering really cool formula-based solutions. (The conversation is on this video: youtu.be/njC1AvEU3fg)In this video I show you something I learned from Wayne and Bill. You can create your custom weekends by putting together a string of 1s and 0s that represent each weekday.1 = day off0 = workdayThe string starts with Monday.If you work (or go to school) only on Tuesdays and Wednesdays, your string would look like this:"1010111" Thus:=NETWORKDAYS.INTL(Start_Date, End_Date, "1010111", [Holidays])You'll also see Power Query, replace values and merge columns in order to simplify calculating net work days in Excel.#NETWORKDAYS#CalculateWorkdays#ExcelOnFireFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Calculating NETWORKDAYS when people work just 1 to 4 days/wk or have non-consecutive days off Excel On Fire 2021-06-10 | This video starts with a correction from my previous video. I was wrong about how NETWORKDAYS works and I should have used NETWORKDAYS.INTL because it allows for "weekends" that are other than Saturday-Sunday. Thus, if you work at a restaurant that's only open Tuesday thru Sunday, NETWORKDAYS.INTL will let you choose Monday as your weekend.Now, here's a challenge. What about part-time people who work Mondays, Wednesdays and Fridays? Or! When I was living in California, I worked 4 days, 10 hours each, for a full 40 hours. I loved working Monday, Tuesday, Thursday Friday. I was off on Wednesdays, Saturdays and Sundays.To calculate the net work days in such situations NETWORKDAYS.INTL can't help us.In this video I show how to achieve this by using Power Query (unpivot), FILTER and COUNTIF. I also make clever use of the MATCH function to identify holidays.#NETWORKDAYS#3DayWeekends#FILTERfunctionFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
NETWORKDAYS Function | Calculating Net Work Days in Power Query | Weirdness & Warnings Excel On Fire 2021-06-01 | Santosh asked about NETWORKDAYS and calculating net work days in Power Query. OUCH! This was rough.NETWORKDAYS is a weird function. If you work at a restaurant that's open 7 days/wk or a salon that's closed on Mondays and Tuesdays, NETWORKDAYS makes a mess. However, using Power Query can be complicated with a lot of steps, but it's more accurate than fiddling around with NETWORKDAYS.This video has several phases:0:00 Introduction2:22 The NETWORKDAYS function4:46 Calculating Net Work Days in Power Query12:40 OutroDownload the file:datascopic.net/wp-content/uploads/2021/06/NETWORKDAYS-PQ.xlsx#NETWORKDAYS#POWERQUERY#Anti-JoinFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Power Query: Handling Percentages & Percent Totals Excel On Fire 2021-05-25 | (NOTE: This replaces a previous video that had audio problems.)Calculating percentages in Power Query is not as easy as it is in native Excel. - First, when using the interface to calculate values in 2 different columns, you have to select the columns in the right order. - Second, If you have a column of amounts and want to get each entry's percentage of the total, that's not straightforward. We end up making a parameter of the total and then calculate the percentages using that parameter.#PowerQuery#Percentages#PercentTotalsFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Identify 2 partial strings of data in 1 cell | Text.Contains | Cross Join | Power Query Excel On Fire 2021-05-10 | Someone asked about identifying more than 1 partial string of text in a cell. Hmmm ... interesting.Well, we go back to our old friends Cross-Join and Text.Combine in Power Query to help us out.Download the workbook: datascopic.net/2Strings#Text.Combine#CrossJoin#PartialTextFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Oz du Soleil: Risk Taking as a Content Creator | Global Excel Summit Excel On Fire 2021-05-07 | If you missed the 2021 Global Excel Summit, full event content now available via the event website 👉 globalexcelsummit.comThis video is my presentation: Risk Taking as a Content Creator#GlobalExcelSummit#ContentCreation#RiskTakingFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
NEW FEATURE! Import Dynamic Arrays into Power Query + 8 Warnings Excel On Fire 2021-04-24 | Just 2 days ago it became possible to import Dynamic Arrays into Power Query. 🙏🏼🎉This is really cool. HOWEVER! There are 8 warnings that I take you through.First. There is no longer "table-slash-range." The icon has been changed to "From Sheet." BOOOO! 🤪 Table/range seems more accurate.Check out the video for the other 7 warnings. They're too hard to explain. You just have to see them.#DynamicArrays#ImportFromDynamicArrays#Power QueryFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Splitting Multiple Columns in Power Query | Battling null values and line-feeds Excel On Fire 2021-04-14 | In a previous video I dealt with a common need to split multiple columns of data. However, in that dataset the delimiter was obvious and all cells were filled in.But! What happens when things get weird? In this video, the delimiter is a line-feed and there's a problem with empty cells. We use Text.Split and Table.FromColumns in Excel's Power Query; then we have to go back and get rid of null values.Download the workbook: datascopic.net/SMR2#PowerQuery#Text.Split#SplitMultipleColumnsFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
An Excel Meditation with Tables, XLOOKUP, Dynamic Arrays in a Dynamic Dropdown List Solution Excel On Fire 2021-03-15 | During the Azerbaijan Power Bi & Modern Excel Meet-up last week, Faraz Shaikh presented and shared a lot of cool Excel tips. There was one tip that stood out for me: Extracting a table's headers into a Dynamic Array. 😲 It was amazing! It got me thinking about how this could be applied.In this video I show how to create a dynamic dropdown list that uses the table headers to extract specific data.You'll see: XLOOKUP, SORT, FILTER, TRANSPOSE in this solution.The Azerbaijan Meet-up: meetup.com/baku-power-bi-modern-excel-user-meetup-group#XLOOKUP#DynamicArrays#MicrosoftExcelFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
ExcelHash 2021 Bill Mr. Excel Jelen and Oz du Soleil discuss all 17 solutions Excel On Fire 2021-02-11 | ExcelHash was a lot of fun this year. We opened it up to more participants and the solutions were brilliant.In this video, Bill Jelen (Mr. Excel) and Oz du Soleil discuss each solution: the thrills, the chills, the funny moments and cool, clever Excel tips that blew us away!Here's the full playlist: 0:00 Intro6:13 Fred Le Guen - excelexercice7:29 Wyn Hopkins - Access Analytic9:52 Paula Guilfoyle11:55 David Benaim16:07 Bill Jelen - MrExcel.com18:32 What does EVEN do?19:50 Bill Jelen continued21:06 Ajay Anand23:22 Oz du Soleil - Excel on Fire27:48 Alan Murray - Computergaga29:30 Jon Acampora - Excel Campus - Jon31:46 Chandoo33:48 Sumit Bansal - TrumpExcel35:43 Jordan Goldmeier39:05 Abiola David - Excel Jet Consult41:11 Fara Shaikh43:03 John Michaloudis - MyExcelOnline.com44:18 Cristiano Galvão - Excel Turbo47:26 John MacDougall - How to Excel49:41 Wrap up comments53:19 Pick random winners for the gift certificates#ExcelHash#ExcelChallenge#ExcelReviewFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Excel Hash 2021: The Facilitator | LET function | Power Query Custom Data Types | Excel Challenge Excel On Fire 2021-02-03 | 0:00 Introduction1:06 My Solution5:13 Rabbit needs water7:26 Correction with the EVEN functionExcelHash is back for 2021! This year we added more participants. Check out the full playlist and think about how you would create a solution.youtube.com/playlist?list=PLHrPHBbDHgT1OUshE6AFf5AJqJdhKhJh_The premise:Take 4 random Excel features and bring them together into an integrated solution. It's pretty difficult and forces a person to justify their choices; thinking about the essence of a feature.ExcelHash 2021 Ingredients- A Cutout Person- EVEN functionAt least 2 from the following:- LET function- Dynamic Arrays- Custom Data Type- LAMBDA functionIn this video, I combine the ingredients into a workbook that helps manage and price dirty jobs in a post-apocalypse world of weirdos and chaos.#ExcelHash#ExcelChallengeFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Bitten by Power Query: a lesson about knowing our data, our tools and what can go wrong. Excel On Fire 2021-01-26 | For a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Multiple Dropdown Lists | Dynamic Arrays | Filter Function Excel On Fire 2020-12-18 | Dependent Dropdown Lists in Excel are pretty handy to know. But there's an interesting problem when you've gotta stack multiple dependent dropdown lists and the source data might add or remove data in the different stages. This video shows how to make this happen using Dynamic Arrays, UNIQUE and the FILTER functions.Also check out Leila Gharani's recent video that has a slight variation and she shows a Google Sheets solution. youtu.be/ku17vgq4Q14#DependentDropDownLists#DropDownLists#RoosterFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
8 Warnings about Excel Wolfram Data Types | Linked Data Types | Excel Rich Data Types Excel On Fire 2020-12-11 | 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 Entries2:12 Unexpected Translation3: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#ExcelDataTypesFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Segment a Dataset Automatically | Dynamic Arrays & Conditional Formatting | ExcelTurbo Challenge Excel On Fire 2020-11-27 | 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: youtube.com/channel/UCxy9ZMwZfP8ccgMrpbVtmTACristiano Galvão at LinkedIn: linkedin.com/in/cristianogalvao#DynamicArrays#ConditionalFormatting#FILTERfunctionFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 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 Excel On Fire 2020-11-20 | 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. 🤗🤩😁🌶#XLOOKUP #LEFTfunction #OzduSoleilFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
2 Custom Data Types | 1 Dataset | 1 Warning Excel On Fire 2020-11-19 | 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 challenge2:20 Going into Power Query to clean the data: Column by Example, Split Column into Rows3:29 Warning: merge before creating custom data types4:00 Merge: Left Outer Join4:37 Create the Students Custom Data Type5:32 Create the Advisors Custom Data Type6:46 Correcting and updating the data8:30 Detailed warning about creating Custom Data Types before merging datasets.9:41 OutroYou'll also see:- Column by Example- Split column by delimiter- Split columns into rows- Left Outer Join- Right Outer JoinDownload the workbookdatascopic.net/wp-content/uploads/2020/11/2-Custom-Data-Types.xlsx#CustomDataTypes#PowerQuery#OuterJoinFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
Creating a Custom Data Type with Power Query; including XLOOKUP and Dynamic Arrays Excel On Fire 2020-10-31 | 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#PowerQueryYou also see the FILTER function for Dynamic Arrays. GOOD STUFF! 🎉For a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Power Query: Identify & Retrieve Partial Strings of Text Excel On Fire 2020-10-06 | 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.ContainsIt's all here!Download the file: datascopic.net/IDTXT#PowerQuery#Text.Contains#PartialTextStringFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Excel Patience | Sort birthdays by the day and month | Insert 7 Rows Between Entries Excel On Fire 2020-09-27 | 0:00 - 2:17 Intro & Set-up2:18 - 4:48 A dark day in Arizona4:49 - 5:33 Sort birthdays by day and month5:35 - 7:55 Insert 7 rows between each rowThis 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, 12APR67Kim, 17JAN81Nettie, 12APR93Pete, 1NOV88Yukio, 29SEP96We 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#DynamicArraysFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Excel Dynamic Arrays with XLOOKUP & Form Controls (Radio Buttons and Tick Boxes) Excel On Fire 2020-09-17 | 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#ExcelFormControlsFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
DATEDIF & Dynamic Arrays | Calculating the months to complete a project | Dont Rush Edition Excel On Fire 2020-09-07 | 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 - 19FEBStep2 15JAN - 19JANStep3 30JAN - 27AUGStep4 5JUL - 15JULStep5 1AUG - 11AUGProblem 2:A stack of multiple projects with multiple steps.Solution involves:Dynamic ArraysDATEDIFUNIQUEMINIFS & MAXIFS#DynamicArrays#DATEDIF#UNIQUEFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Excel: Data Models | Add to Data Model | Power Query Excel On Fire 2020-09-03 | After working with data in Power Query there is the option to "Add to Data Model." What is that?A Data Model is helpful when it's necessary to bring multiple datasets into one. The data can be used in a single pivot table without having to physically merge the datasets into one place.This video shows how to create a Data Model using the diagram view in Power Pivot. I also show one warning about backward 1-to-many relationships. Download the workbooks: datascopic.net/DM2#DataModel#PowerQuery#ExcelFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.comMy book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Where INDEX-MATCH Wins Over XLOOKUP and VLOOKUP Excel On Fire 2020-09-01 | I was surprised with a situation where someone insisted that INDEX/MATCH was his only choice for a formula. I wondered "why INDEX/MATCH?" Why not VLOOKUP, XLOOKUP or a Power Query join?When I saw his data and the restrictions that were on him, this was clearly a context where INDEX/MATCH was the wiser choice.There are other functions and formulas that could be used to accomplish this task, but a small twist to the INDEX/MATCH function does the trick.For a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
Beyond Excel: People Processes & Tools (and XLOOKUP) Excel On Fire 2020-08-15 | A lot of times what we face in the real world with data and Excel:The Excel solution is easy but the situation is complicatedIn this video I discuss the importance of People, Processes and Tools. Excel is only a tool. And a tool won't save a situation if the process is janky (or there is no process, or the person is janky.In this case, an XLOOKUP needed to be added to modify an existing process. But it took a long time to think through.For a list of my Excel courses at Lynda/LinkedIn: 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. #XLOOKUP Website: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
XLOOKUP & Dynamic Arrays Celebration and Demos with MVPs Wyn Hopkins and Oz du Soleil Excel On Fire 2020-08-06 | XLOOKUP and Dynamic Arrays are finally available for anyone on Microsoft365 (formerly known as Office 365). In this video, Wyn Hopkins and I give you demonstrations of XLOOKUP and Dynamic Arrays working together to make simple but robust solutions. 0:00 Opening1:34 Wyn's Demo6:15 Oz's Demo10:40 Wrap-upYou'll see it all!XLOOKUPFILTER2-way lookupsSORTTRANSPOSETablesIt's one big party, y'all!#XLOOKUP#Dynamic_Arrays#DynamicArraysFor a list of my Excel courses at Lynda/LinkedIn: 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: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
