Category: Excel

Excel VLOOKUP Fails At Strange Hyphen – 2600

Posted on by Annie D

Microsoft Excel Tutorial – Deep Dive on diagnosing VLOOKUP errors.

To download the workbook: https://www.mrexcel.com/youtube/_q0VdM1ES1g/

This is an advanced look at troubleshooting hard VLOOKUP errors. After using TRIM, CLEAN, and making sure there aren’t numbers stored as text, then I use this method to look at the ASCII code, character by character to figure out why the two values do not match.

But today, the vendor part number is reporting that a hyphen is ASCII CODE 63 instead of 45. What is up with this?

I had to modify my workflow to add UNICODE functions in order to discover that the Continue reading “Excel VLOOKUP Fails At Strange Hyphen – 2600” →

Full Free DAX Class: Fundamentals of DAX in Power BI & Power Pivot. 365 MECS Class 17

Posted on by Annie D

Download Zipped Folder with All Files: https://excelisfun.net/files/17-M365ExcelClassVideoFiles.zip
50 pages of pdf notes: https://excelisfun.net/files/17-M365ExcelClassDax..pdf
This video teaches how the fundamentals of Columnar Database, DAX Calculated Columns, DAX Measures, Row Context, Filter Content, Context Transition, Overwrite Operation, DAX X Iterator functions, DAX CALCULATE function and much more!
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
This video covers.
1. (00:00) Introduction and video topics
2. (00:25) Topics in Video
3. (01:13) Why we use DAX, M Code & Worksheet Formulas: What makes Each Continue reading “Full Free DAX Class: Fundamentals of DAX in Power BI & Power Pivot. 365 MECS Class 17” →

Excel Labs Creates A LAMBDA From Existing Worksheet Logic – Episode 2599

Posted on by Annie D

Microsoft Excel Tutorial – Easiest Way to Create a Lambda function in Excel.

A new version of the Advanced Formula Environment from Excel Labs has been released. It has an amazing new functionality called Import from Grid.

There are many times where I will build a complicated Excel formula in sub-formulas. I finally combine all the sub-formulas into one mega-formula by scooping the subformulas out of the formula bar and pasting in to the final formula.

The new Advanced Formula Envionment, found in the Excel Labs add-in, offers to create a LAMBDA function from existing worksheet logic in the grid.

Table of Contents
(0:00) Continue reading “Excel Labs Creates A LAMBDA From Existing Worksheet Logic – Episode 2599” →

Excel for Mac New AddIn Brings More Data To Power Query – 2598

Posted on by Annie D

Power Query for Mac Excel is missing From Table/Range.
Power Query for Mac Excel is missing From Folder.
Power Query for Mac Excel is missing From XML.
Power Query for Mac Excel is missing From JSON.

A new More Data add-in from Suat Ozgur at MrExcel adds all four of these functionalities to Excel for Mac! Download the add-in from this article: https://www.mrexcel.com/board/excel-articles/morequery-for-mac.68/

The FREE, open-source add-in adds From Table/Range to Power Query for Mac. It adds From Folder. It adds From XML. It adds from JSON.

Table of Contents:
(0:00) More Data Add-In fixes Power Query for Mac
(0:37) Get Data From Table/Range Continue reading “Excel for Mac New AddIn Brings More Data To Power Query – 2598” →

Excel Technique Refer To Every Third Column From Bob Umlas 2583

Posted on by Annie D

Microsoft Excel Puzzle and Solutions.
Bob Umlas sent in the puzzle. How to refer to every 3rd column on Sheet2?

Table of Contents
(0:00) Bob Umlas Book
(0:22) Problem: Refer to every 3rd cell
(0:44) CHOOSECOLS with SEQUENCE
(1:16) Horizontal XLOOKUP
(1:46) Fill Handle from 3 Cells
(2:32) Delete Empty Cells
(2:54) R1C1 Method 2
(3:43) Replace = with x=
(4:12) Change R1C1 to A1
(4:21) Insert colums/rows to test

BI 348: Data Analysis with Microsoft Power Tools- Spring – 2023 Quarter Introductory Video

Posted on by Annie D

BI 348 is taught by Excel MVP Mike Girvin at Highline College. This is the intro video for matriculating students at Highline College in spring quarter, 2023. If you are not a matriculating student, watch class free at YouTube here is the playlist (videos #9-end): https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW

Excel Statistical Analysis for Business – Busn 210 – Spring 2023 Quarter Introductory Video

Posted on by Annie D

Busn 210, Excel Statistics for Business class taught by Excel MVP Mike Girvin at Highline College.
This is the intro video for matriculating students at Highline College in spring quarter, 2023.
To watch at YouTube here is the playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0m3oqpp1XcPuaxyM4Bpi0dN

code execution has been interrupted window

How to Make Excel Wait or Pause in a VBA Macro

Posted on by Bb

When running macros, I have often found the need for an Excel wait or pause, mainly so that other things can happen. DoEvents surely helps for waiting for things to happen within Excel, but sometimes you need to wait for processes to start or finish outside of Excel before moving onto other steps in VBA.

Why Wait?

I am a big fan of automating processes, especially with VBA in Excel. For example, I update and save a spreadsheet to SharePoint that a Power BI model is using. I have a Power Automate process set up that automatically refreshes the Power BI model whenever this particular spreadsheet is updated. The challenge is that Power BI temporary locks the workbook as it reads it, so if I am updating a few times in a short amount of time, Excel needs to wait until the SharePoint file is unlocked.

Also, I have another process where I call a Python script and once that is done, I continue on with VBA code to process the workbook Python outputs. I need Excel to wait while the Python code runs. Side note: let me know if you would like me to write more about these other automated processes.

The Solution

Good news – making Excel wait is very easy! Here is the simple code:

This will have Excel wait for 50 seconds since the time is represented as (H:MM:SS). You can adjust as needed.

Bonus

In my code, I don’t need it to wait every time – only when it runs into an issue saving the workbook on SharePoint. Here is how I have set my code using an error handler in my VBA macro.

I hope this helps you! Please comment or reach out if you would like to see other topics covered. I truly enjoy writing about Excel tips and tricks, especially VBA and automation!

Addressed in this post (to help others find this):

  • How to pause Excel
  • How to make Excel pause
  • Make VBA pause
  • Make VBA wait
  • Make Excel wait
  • VBA code wait, VBA macro wait
  • VBA code pause, VBA macro pause
  • VBA wait for another application

Mac Excel Power Query Three Missing Connector Workarounds – Episode 2597

Posted on by Annie D

Great news: Power Query debuted for the Mac in early 2023.
Bad news: It is missing four important connectors:
• From Table/Range – Fixed in this video
• From Web – Fixed in this video
• From Folder – Fixed in this video
• From PDF – no solution yet.

Thanks to Suat Ozgur – our resident Mac expert at MrExcel, we have solutions for three of those connectors.

Table of Contents
(0:00) Problem Statement
(1:17) From Table or Range
(2:44) How to edit query
(3:33) New icons in Applied Steps
(3:59) From Web
(5:17) From Folder
(9:45) No solution for From PDF yet
(10:27) Thanks to Excel Team
(11:15) Subfolders granted access
(11:24) Avoiding VBA

Dad & Son Skate & BMX Rad in Seattle on Sunday

Posted on by Annie D

Before making Excel videos during the week, I gotta fuel up on getting rad with my Son in Seattle, Skateboard and BMX style! On March 26, 2023 we went to Delridge Skate Park and Seattle Space Needle Skatepark. Excelisfun was on the BMX bike and Iceman was on the skateboard!
#bmx #bmxisfun #skateboarding #skate #skateboard #skater #oldguysrule #oldguyhavingfun #oldguy #dad #dada #dadson #seattle #seattlebmx #seattleskateboard

Excel Plot A Bell Curve in Excel – Episode 2596

Posted on by Annie D

Microsoft 365 Excel Tutorial – Plot a Bell Curve with 2 Formulas in Excel.

Download the finished spreadsheet from: https://www.mrexcel.com/youtube/QW3tbhe6Gks/

If you don’t have the SEQUENCE function, use this older video: https://www.youtube.com/watch?v=_PqnDYMO3lw

Search terms:
making a bell curve in excel
making a bell curve in excel from data
how excellent is thy name by lecreia campbell
how to make a bell curve in excel with data
how to find the shaded area of bell curve excel
how to make excel histogram a bell
how to create a bell curve in excel
how to make a bell curve in excel
how to create bell curve in excel
how Continue reading “Excel Plot A Bell Curve in Excel – Episode 2596” →