MrExcel Excel Videos

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?

Microsoft Excel Tutorial - How to draw a line through text. Some people call it Strikethrough. Others call it crossing out text. How can you do it in Microsoft Excel. This short video starts with a memory aid about taking inventory with a mechanical pencil and making hashmarks. You might start with |||| to mean 4. After the 4th vertical hashmark, you cross out the lines to indicate 5. It is the 5th line that crosses the others out. This is how I remember that Ctrl+5 is Strikethrough! Download the workbook from Excel - Draw a Line through Text (Strikethrough) #shorts Sample Files - MrExcel Publishing Table of Contents (0:00) Counting with Hashmarks (0:12) Cross out text with Ctrl+5 in Excel (0:22) Strike through one word in a cell in...

Microsoft Excel Tutorial: Adding a Variance inside of a pivot table. Keani has a pivot table with current year and last year across the top. She wants a variance. Her current solution is a formula outside of the pivot table which points inside of the pivot table. This video shows three methods for adding a variance inside the pivot table. 1. Regular pivot table, add Revenue twice. Change calculation to Difference From, Years, and (Previous Item) 2. Regular pivot table. Remove Grand Total. Use 2 Excel Calculated Items to calculate Variance and Total 3. Data Model Pivot Table. Add four DAX Measures to calculate last year, this year, total, and variance. To download this workbook: Excel Variance Inside The Pivot Table - Episode 2605...