MrExcel Excel Videos

From the May 2023 issue of Strategic Finance magazine, how to use TEXTSPLIT in Excel.

Microsoft Excel Tutorial - BOM Tool Suite from Main Sheet Solutions Do you deal with Bills of Material? Also known as BOMs. Any Structured List in Excel can make use of this add-in from Main Sheet Solutions: Download a trial from MainSheet Solutions | Time saving Excel Add In Download the two data sets that I used in this video: Excel Add-In To Streamline Bill Of Material BOM Analysis - Episode 2612 Sample Files - MrExcel Publishing Table of Contents (0:00) Bills of Material in Excel (2:02) Data in a typical BOM (2:21) Ribbon for BOM (2:29) Adding Grouping (2:46) Choosing highlight colors (3:17) Highlight rows (3:27) Indent BOM (3:36) BOM Rollup Calculations (4:30) Analyzing Mike Girvin Book Table of Contents (6:03) Adding Structure...

Microsoft Excel Tutorial - Troubleshooting when you double-click fill handle to copy a formula. A great question this morning: Is there a bug in double-click the fill handle to copy a formula? Someone was convinced that Excel was being fooled by the formatting. The behavior of copying a formula by double-clicking the fill handle changed in Excel 2010. Excel now looks at all columns to the left and the right when figuring out how far to copy down. Imagine if Excel does Ctrl+* to select the current region. Excel will copy down to the last row in the current region. There are two exceptions: a) If the cell immediately below the formula is non-blank, then Excel will only copy down to the cell above the blank cell in that column b) If the...

Microsoft Excel Tutorial: Embedding Excel Range in Microsoft Word How to create a live link from an Excel workbook to a Word document. This video is to accompany the article in the July 2023 issue of Strategic Finance Magazine: https://www.sfmagazine.com/articles/2023/july/excel-embedding-a-range-in-a-word-document Table of Contents (0:00) Problem Statement: Excel Table in Word Document as Live Link (0:22) Name the range to be embedded (1:32) Save as East, Change to West (1:45) Copy from Excel & Paste as Link in Word (2:20) Live link between Excel and Word (3:01) After you close Excel workbook (3:35) Re-opening Excel (4:10) Re-calc Word with F9 (4:40) Re-create the immediate link between Excel and Word Buy Bill Jelen's latest Excel...

Microsoft Excel Tutorial: Adding Filterable Text to a Picture in Cell The new Picture in Cell feature would not work with the Filter drop-downs. A new feature allows you to add text to each picture using the Alt Text panel. This Alt Text will appear in the Filter drop-downs, in XLOOKUP results, in the Pivot Table Filter, and in Pivot Table Slicers. To download this workbook: Excel Improvements To Picture In Cell - 2608 Sample Files - MrExcel Publishing Table of Contents (0:00) Problem Statement: Filtering by Picture (0:53) Use Alt Text to add Text to Picture in cell (1:17) Alt Text appears in Filter drop-down (1:44) Getting Picture from XLOOKUP (2:15) Use Image in Excel Pivot Table Filter (3:23) Image Alt Text in Pivot Table Slicer...

The Association for Professionals in Infection Control and Epidemiology is holding their annual conference in Orlando June 26-28, 2023. I will be presenting a half-day pre-conference session on Sunday June 25, 2023 from 8 AM to 1 PM. My goal in the session is to make you more comfortable with Microsoft Excel. In this preview, I download data from the CDC, do some simple clean-up. I use a few Excel formulas to add some useful metrics. I then display the results on a Map in Microsoft Excel. Everyone who attends my session receives a copy of my book, MrExcel 2021 - Unmasking Excel.

Microsoft Excel Tutorial: Use Excel VBA to replicate the IMAGE function for images stored locally. In episode 2606, I showed the new Insert Pictures in Cells and lamented that they did not change the IMAGE function to allow images stored on the local hard drive. Using the VBA Macro in this workbook, you can do it. The VBA is actually better because it remembers the image path and displays it in the formula bar. To download the workbook or copy the VBA: Excel Place Local Pictures In Cell Using Formula and a VBA Hack - Episode 2607a Sample Files - MrExcel Publishing Table of Contents (0:00) Enhancing Picture In Cells to use a formula (0:30) Behind Scenes of 2606 (1:00) Sort Order is different (1:33) Formula Bar shows only "Picture"...

Microsoft Excel Tutorial - new feature to place a picture in a cell in Excel. Can the Excel IMAGE function use a local hard drive? Not yet. New Excel VBA method: InsertPictureInCell Insert Many Pictures in Excel in one command. Feature Requests: Expose a VBA property to show original source of the image Allow IMAGE function to point to local hard drive. Allow Power Query to load in-cell images. To download this workbook: Excel - Picture Place In Cell - Episode 2606 Sample Files - MrExcel Publishing Table of Contents (0:00) New Picture In Cell feature (0:33) Pictures on drawing layer (0:47) Inserting image in cell in Excel (1:05) Fitting in Cell (1:20) Alignment (1:35) No apparent way to rotate image (2:02) Use Case from Jerry (3:48)...

Microsoft Excel Tutorial - is there a way to know at a glance if the macro recorder is in Relative Recording Mode or not?