MrExcel Excel Videos

Episode 2492 The Calc team at Microsoft released three changes to new functions in late May 2022. The improvements to TEXTBEFORE and TEXTAFTER give you options for dealing with situations where the delimiter that you are searching for is not found. Table of Contents (0:00) Former problem with TEXTBEFORE (0:37) New syntax (1:11) Using Match End (1:31) Using If Not Found (1:47) Why both? (2:12) Thanks Excel Team (2:27) Nancy Faust (2:34) Like & Subscribe (2:41) Excel Video Courses

Episode 2491 - While using SmartRoster software, you try to export a report to Word. The export does not start and you get a message that says, "Microsoft Word Security Notice. Microsoft Office has identified a potential security concern. Microsoft has blocked macros from running because the source of this file is untrusted." I recently posted episode 2485 that attempted to allow macros by using the Unblock checkbox in the workbook properties. That method won't work here. Instead, you can temporarily set up a trusted location. This video shows you how. Table of Contents (0:00) Microsoft is blocking macros from running. (0:53) SmartRoster problem (2:29) Behind the scenes (3:11) Demo of solution (3:45) SmartRoster working again (4:03)...

A new pink bar warning is appearing in Excel, Word, and PowerPoint telling you that there is a security risk and that your VBA macros have been blocked. This warning is designed to make you ask, "Is there a valid reason for this person to be sending me a macro?" If there is, I will show you the easy steps to unblock the workbook. This is called the Mark of the Web. Kevin Lehrbass Word Game is here: Word Game in Excel | My Spreadsheet Lab Table of Contents (0:00) Kevin Lehrbass has a Wordle game in Excel but the macros are blocked with the pink bar. (1:08) Look at the code (1:33) How to unblock the macros (2:10) Dealing with the normal yellow bar.

Episode 2490: The Android phone has had it for a long time, but the new Data From Picture has finally reached the Office Insiders beta channel on desktop Excel. Bill gives this new feature a spin with various pictures of data. Table of Contents (0:00) Speech Recognition 1989 to today (1:10) From Picture near Power Query tools (1:35) Creating data picture (2:00) Data From Picture no gridlines (3:40) Cleaning the results (4:15) The numbers all match (4:30) Gridlines don't help (5:35) Right-align headings (7:20) Need to proof (7:50) With real photo test 1 (8:40) From clipboard test 2 (10:03) This will improve

A question from Elijah at the UCF Accounting Conference. I was showing how to use the UNIQUE function to get a list of products. Elijah asked if I could then get the total sales for each product. Plus a heading row and a total row. With the items sorted high to low. The final result uses several new Excel functions: UNIQUE, SUMIFS, HSTACK, SORT, VSTACK, and LET. This lets you create something that is like a pivot table, but there is no need to refresh as the data changes. Table of Contents (0:00) Welcome (0:16) Formulas to Values (1:08) UNIQUE list of products (1:35) SUMIFS by product (2:12) HSTACK and SORT (2:44) Headings and Total Row (3:25) VSTACK into final report (3:53) With fewer helper columns (5:01) Using LET and no helper...

Today's Excel question from Willie at the UCF Accounting Conference: I am using a vlookup to get some information from a vendor info database that comes from the credit card website. My issue is that the vendor name sometimes comes with additional info at the end like …. “Contoso - cadet.biz” and in the table where I do the lookup the name is just “Contoso”. I was wondering if you think I can use the “TEXTBEFORE” function with the Vlookup formula to make the formula to look in the table only for the first word of the info coming from the credit card “Contoso - cadet.biz”. Table of Contents (0:00) UCF Accounting Show (0:39) Table with Vendor dash URL (1:15) Use TEXTBEFORE with VLOOKUP (1:50) Use TEXTBEFORE, XLOOKUP, for lookup...

This video is all about the Excel function for WORKDAY.INTL. It allows you to make sure that you are always scheduling an event to occur on a Saturday, or Tues/Thurs or Friday. The secret here is the alternate syntax for the Weekend argument. Table of Contents (0:00) Find a particular Saturday in Excel (0:25) WORKDAY introduction (1:37) Find a particular Saturday in Excel (2:32) Find the next Tuesday (5:02) Find next Tuesday or Thursday (5:46) Handling holidays (6:58) Wrap-up

Uncle S is trying to have a formula copy to new rows. So a table seems like a great way. But he also has merged cells and the table unmerges those. In this video, two surprising Table tricks: First, a table can contain Center Across Selection, which is better than Merging Cells in Excel. Second, a surprising discovery from the Excelforo channel about new headings in a table. Table of Contents (0:00) Auto-copying formulas to new rows in Excel (2:31) Can a table have a merged cell (4:00) New table columns using custom lists in Excel

You have a pivot table in Excel. The months and weekday names are appearing alphabetically instead of in calendar sequence. This happens when your pivot table is based on external data or the data model. There are two solutions. This video prefers using the Custom Sort Order option. This takes 8 clicks to fix the months and 8 clicks to fix weekdays. If your Pivot Table Slicers are in the wrong sequence, then you should use the Data Model setting to sort one column by another column.