Category: Power Pivot

Excel Add-In To Streamline Bill Of Material BOM Analysis – Episode 2612

Posted on by Annie D

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 https://www.mainsheetsolutions.com/

Download the two data sets that I used in this video: https://www.mrexcel.com/youtube/k4A1lg9pPlc/

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 ID and UID
(6:44) Applying colors to group Continue reading “Excel Add-In To Streamline Bill Of Material BOM Analysis – Episode 2612” →

Excel Fill Handle To Copy Formula Rules Deep Dive – Episode 2611

Posted on by Annie D

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 Continue reading “Excel Fill Handle To Copy Formula Rules Deep Dive – Episode 2611” →

Excel Battle on ESPN – Sketch Comedy – 3 Dimes on Diarmuid Early

Posted on by Annie D

The second ever Excel Battle to air on ESPN will happen on Friday August 4, 2023 at 7 AM Eastern time. This short is a bit of sketch comedy … wouldn’t it be great if Excel on ESPN were so commonplace that bookies would start to allow betting on the competition? That’s when you know that you’ve made it to the big time.

This fictitious sketch imagines a day far in the future when you can just call up a bookie and place some action on Diarmuid Early or your favorite Excel competitor.

For 2023, set your alarm and tune in Continue reading “Excel Battle on ESPN – Sketch Comedy – 3 Dimes on Diarmuid Early” →

Excel Esports Returns on “ESPN8: The Ocho”! on August 4, 2023

Posted on by Annie D

Mark your calendars for 7 AM (ET), August 4, 2023, as we gear up for an adrenaline-pumping Excel Esports Elimination Battle – a completely new Excel Esports game format will be revealed!

8 Excel enthusiasts will be competing in a fierce 30-minute battle, but every 5 minutes one of them will be eliminated: Andrew Ngai, Isaac Lee, Diarmuid Early, David Brown, Alana Reid, Brittany Deaton, Michael Holmes, and Emilie Williams.

Hosted by Excel rock stars Leila Gharani, Bill Jelen, and Oz du Soleil!

From precision plays to amazing strategies, the Excel world’s finest content is being brought to your screens.

Whether you’re a die-hard Continue reading “Excel Esports Returns on “ESPN8: The Ocho”! on August 4, 2023″ →

Leagues Cup Tie Probability / Will Messi Play in Orlando in August – Excel episode 2610

Posted on by Annie D

Microsoft Excel Tutorial: Tiebreakers in Leagues Cup Soccer Group Stage.

Lionel Messi’s debut in the US for Miami is in the Leagues Cup. This four-week tournament includes 47 clubs from across the USA, Canada, and Mexico. What are the odds of a tiebreaker in group play for the Leagues Cup? If Miami finishes first or second in their group, who will they play in the knockout stage?

To download this workbook: https://www.mrexcel.com/youtube/M6xbdRFASnI/

Table of Contents
(0:00) Problem Statement: Odds of tiebreaker
(0:21) Orlando City Soccer two home matches in Leagues Cup
(0:48) Lionel Messi’s US debut will be in Leagues Cup
(1:00) Leagues Continue reading “Leagues Cup Tie Probability / Will Messi Play in Orlando in August – Excel episode 2610” →

Embedding Excel Range in a Word Document – Strategic Finance Magazine July 2023

Posted on by Annie D

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

Excel Improvements To Picture In Cell – 2608

Posted on by Annie D

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: https://www.mrexcel.com/youtube/xZxKhm9_Xkk/

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 Continue reading “Excel Improvements To Picture In Cell – 2608” →

Excel For The Infection Preventionist – Preview of APIC Pre-Conference

Posted on by Annie D

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 Continue reading “Excel For The Infection Preventionist – Preview of APIC Pre-Conference” →

Excel Place Local Pictures In Cell Using Formula and a VBA Hack – Episode 2607a

Posted on by Annie D

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: https://www.mrexcel.com/youtube/0NArag0pV7I/

Table of Contents
(0:00) Enhancing Picture In Cells to use a formula
(0:30) Behind Scenes of 2606
(1:00) Sort Order Continue reading “Excel Place Local Pictures In Cell Using Formula and a VBA Hack – Episode 2607a” →

Excel – Picture Place In Cell – Episode 2606

Posted on by Annie D

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: https://www.mrexcel.com/youtube/mFd8HsHmfpA/

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) Continue reading “Excel – Picture Place In Cell – Episode 2606” →