MrExcel Excel Videos

How to split delimited into rows. I tried using Power Query. With Power Query I can do it only for 1 column. If I select more than 1 column, the Split Column is disabled in the Power Query Editor. Is there any other solution for splitting delimited into rows for more than one column? This video offers three solutions. Solution 1 is a formula such as =TEXTJOIN("|",,TEXTSPLIT(C2,",")&"-"&TEXTSPLIT(B2,",")) and then Power Query. The 2nd solution is M code written by Suat Ozgur. let fnSplit = (row as record) => let Invoice = row[Invoice], Product = row[Products], Qty = row[Quantities], ProductList = Text.Split(Product, "," ), QtyList = Text.Split(Qty, ",")...

Microsoft Excel Tutorial - Deep Dive on diagnosing VLOOKUP errors. To download the workbook: Excel VLOOKUP Fails At Strange Hyphen - 2600 Sample Files - MrExcel Publishing 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 hyphen - which the CODE function reports as ASCII 63 is really Unicode 8208. Why did the vendor decide to use...

Microsoft Office Tutorial - live link to Excel range in Word. Ashley wants to embed a range of Excel data in a Word document. If the data in Excel changes (including adding more rows), she wants the Word document to update. Annoying: If you use Insert, Object in Word, it captures the wrong range and truncates if you add more data. My method: (1) Set the Print Range in Excel, (2) Select the Print range and Copy. (3) Paste to Word, (4) Open the paste drop-down menu and choose Linked Keep Source Formatting. This reliably works. How do you solve this? Let me know down in the YouTube comments below. Table of Contents (0:00) Problem Statement

Generate an exponential growth curve that will generate 600,000 units of sales in 70 days. Brandon sent in this question. It turns out that this is fairly easy to do using the GROWTH function in Excel. Table of Contents (0:00) Welcome & Problem (0:21) Need to create an exponential growth curve that meets a certain size (0:50) Use SEQUENCE function (1:00) Add the word "Day" to each number using custom number format (1:30) Using the GROWTH function with B14# (1:50) Ctrl+* to select current region (1:55) Inserting a chart (2:25) Rejecting Goal Seek (2:50) Formula to scale up using a constant (3:45) Compounding the issue: four days are holidays with 0 sales (4:10) Using COUNTA function (4:30) Using WORKDAY.INTL function with no weekends...

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) Welcome (0:11) Build sub-formulas in Excel (0:29) Scooping sub-formulas into mega-formula (0:55) Advanced...

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: MoreQuery for Mac 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 on Mac Power Query (1:18) Get Data From Folder on Mac Power Query (2:43) Get Data from JSON or XML on Mac Power Query (3:27) Wrap-up

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

Microsoft Excel Tutorial: Returning many columns from a lookup. If you do not have the XLOOKUP function, then use the techniques from episode 1123: In the past, if you needed to return 4 quarters or 12 months or 52 weeks from a VLOOKUP, it required some trickery or helper cells. Today, the new XLOOKUP function makes it simple to return many columns from a lookup in Excel. How to apply vlookup in excel to get multiple columns values. How to return 12 months from a VLOOKUP in Excel. How to return multiple columns from a VLOOKUP in Excel. How to return 52 weeks from a VLOOKUP in Excel. Return all columns from VLOOKUP in Excel. Return an entire row from VLOOKUP in Excel. How to apply xlookup in excel to get multiple columns values. How...

Microsoft Excel Tutorial Someone deleted some cells in Excel Online. Using the Version History, it happened between 11:01 and 11:07 AM. But Show Changes is not showing who changed it? How could this be happening? One way is to overwhelm Show Changes by making 3000 changes with VBA. Another way might be changes by Power Automate Flows. Do you have another way?