MrExcel Excel Videos

Microsoft Excel Tutorial: Using a function in Python for Excel calculations. The problem today: Count how many times a word occurs in a cell in Excel. To download this workbook: Excel Python Custom Function - 2617 Sample Files - MrExcel Publishing The first solution is a series of six formulas in Excel, including SUBSTITUTE, LEN, and more. While it is complicated in Excel, there is a much easier way in Python, using the .Count function. So, Python has a simpler version but how do you call the function from Excel? After the Python solution, I used the Excel Labs add-in to convert the original six formulas to a LAMBDA. Other topics here: Saving Python function in a cell Adding Text as line line of a Python script to appear in the...

Microsoft Excel Tutorial: Using Python in Excel to create Excel-like pivot tables. I love pivot tables in Excel. In fact, I've written an entire book on Pivot Tables. So when I saw that Python has a function to generate "Excel-like" Pivot Tables in a new data frame, I wanted to try it out. The Python Pivot Table is missing a few things: 1. Row Fields are called Index 2. Defaults to Average instead of Sum 3. Empty cells show as errors. Use fill_value 4. No Grand Totals by default! Turn on with margins=True 5. When you add Grand Totals, they are called "All" Unless you change them with Margins_Name 6. When you group by dates, you can't have grand totals 7. Does not sort by Custom Lists 8. Odd arrangement of headings when 2 row fields...

Microsoft Excel Tutorial: Python in Excel After using Python in Excel for 2 days, I am gaining confidence. Topics today: • Python Tips from Day 2 • Slow rollout of Python • Paying for Anaconda • Python Libraries to explore • How to write Python results back to Excel grid • Real-life K-Means clustering with 18K customers, 30K transactions, 300 products To download the first workbook: Gaining Confidence With Python In Excel - 2615 Sample Files - MrExcel Publishing The second workbook contains actual customer data and I am not sharing it at this time. Table of Contents (0:00) Welcome (0:45) Keyboard shortcuts for Python (1:22) Leila tip DF.Customer eliminates square bracket notation (2:14) Seaborn Library of Charts as shown by Mynda...

Microsoft Excel Tutorial - Using Python in Excel. Today, August 22, 2023, Microsoft will release a preview of Python in Excel. It is a big day for me... I've been trying unsuccessfully to learn Python for ten years. Once Microsoft added it to Excel, I finally have some cool things working. To download this workbook: Python In Excel - Getting Started - 2614 Sample Files - MrExcel Publishing In this video: a getting started with Python in Excel tutorial. How to open the Python editor in Excel Ctrl+Enter versus Enter in the Python Editor in Excel Returning a Value or a Python Object Referring to an Excel range in Python Using Variables in Python in Excel Plotting Data Using Python in Excel Which Python Libraries are loaded by default...

Microsoft Excel Tutorial: Which values are not yet calculated in Manual Calculation Mode? In August 2023, Microsoft is adding a new feature to Microsoft 365 Excel. The Format Stale Values will alert you when a cell needs to be recalculated. This is an application-level setting. When you turn it on, it will work for all open workbooks. To download this workbook: Excel Stale Value Formatting - Episode 2613 Sample Files - MrExcel Publishing Table of Contents (0:00) Excel Stale Value Formatting (0:23) Excel Manual Calculation Mode (0:36) Stale Value Markers (0:56) On-Grid UI choices (1:09) Customize Stale Value Formatting? (1:47) Why are new values stale? (3:14) Stale Value in Automatic Mode (3:40) Excel interrupt calculation with Esc...

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...