Category: Excel

Excel Python Appending Data Frames From Multiple Worksheets – Episode 2621

Posted on by Annie D

Microsoft Excel Tutorial: Appending data from multiple worksheets using Python in Excel

To download today’s workbook: https://www.mrexcel.com/youtube/3rAJPcjO6Ok/

Today, a question about creating a Python data frame from multiple Excel sheets. I use the CONCAT function in Python but then realize that the headings are repeated.
So I show how to use .tail(-1) to remove the top row from each data frame except the first.

Table of Contents
(0:00) Problem Statement
(0:29) Defining 3 data frames
(1:32) Python CONCAT function
(2:20) Python Tail Function
(3:10) Wrap-up

Excel Python 3D Scatterplot – Episode 2620

Posted on by Annie D

Microsoft Excel Tutorial – Drawing a 3D Scatterplot using Python in Excel

Download the workbook: https://www.mrexcel.com/youtube/TMyAAxbUU7s/

How to plot a circle in an Excel XY Chart
Converting degrees to radians in Excel
Building X, Y, Z values for a series of circles in Excel
Using Chat-GPT for Python Code for a 3D Scatter Plot
Adapting the copied code
Marker choices for Python Charts

Table of Contents
(0:00) Problem Statement – 3D Scatterplot in Python
(0:14) Excel formulas to plot a circle in Excel
(0:45) Convert degrees to Radians in Excel
(1:15) Formulas to Create a 3D Spiral in Excel
(2:45) Finding Python code from Chat-GPT
(3:10) Adapting Python code for Excel
(4:45) Continue reading “Excel Python 3D Scatterplot – Episode 2620” →

Excel Python XLOOKUP 2619

Posted on by Annie D

Microsoft Excel Tutorial: VLOOKUP in Python for Excel

How to do a VLOOKUP or XLOOKUP in Python.

To download the examples in this workbook: https://www.mrexcel.com/youtube/h6__fB7vo4k/

Along the way, you will see:
101: Doing a VLOOKUP
Not specifying the key field!
What if a customer is missing from lookup table? How to IFERROR()
Limiting which fields are returned.
What if headings don’t match?
What if a customer is duplicated?
Lookup on two fields.

Table of Contents
(0:00) Python lookup overview
(0:25) Comment indicator in Python
(1:00) VLOOKUP 101 in Python using pd.merge
(2:36) Leaving off the On field
(2:50) IFERROR when customer missing with .FillNA
(3:23) Limiting lookup table to needed fields
(4:04) Headings don’t match Continue reading “Excel Python XLOOKUP 2619” →

Excel Display Only Last 4 Of Social Security Number – Episode 2618

Posted on by Annie D

Microsoft Excel Tutorial: Hiding part of Social Security Number

Download this workbook from: https://www.mrexcel.com/youtube/MeCptGsHsYY/

Someone from the Veterans Administration is getting data downloaded that includes the entire social security number (SSN). They only want to display the last four of the SSN. But, sometimes, they need to be able to go back and see the entire SSN.

I have two solutions today, but I bet you have something better.

First, it would be nice if Excel offered a custom number formatting code that said “There is a digit here, but we don’t want to display it”.

My first solution is a pair Continue reading “Excel Display Only Last 4 Of Social Security Number – Episode 2618” →

Excel Python Custom Function – 2617

Posted on by Annie D

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

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 Continue reading “Excel Python Custom Function – 2617” →

Dynamic Range in SUMIFS & SUMIF functions using XLOOKUP Function. Excel Magic Trick 1836

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1835-1837.xlsx
Learn about how to create a dynamic range in the SUMIFS and SUMIF functions. Trick works in SUMIFS, SUMIF, MAXIFS, MINIFS, AVERAGEIFS, COUNTIFS or COUNTIF.
Topics:
1. (00:00) Introduction
2. (00:15) Goal of formula
3. (00:28) CHOOSECOLS and XLOOKUP to lookup a column
4. (01:12) CTRL + C, C keyboard shortcut
5. (02:10) SUMIFS with XLOOKUP
6. (02:53) SUMIFS with XLOOKUP
7. (03:18) SUMIF with XLOOKUP
8. (04:02) SUMIF with INDEX
9. (04:29) Array inside SUM
10. (04:44) Summary
11. (05:06) Lookup row in SUMIFS and SUMIF
12. (05:32) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial Continue reading “Dynamic Range in SUMIFS & SUMIF functions using XLOOKUP Function. Excel Magic Trick 1836” →

Python Pivot Tables In Excel -Episode 2616

Posted on by Annie D

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 Continue reading “Python Pivot Tables In Excel -Episode 2616” →

Gaining Confidence With Python In Excel – 2615

Posted on by Annie D

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: https://www.mrexcel.com/youtube/GMF1E0dmjWs/
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
(2:49) Continue reading “Gaining Confidence With Python In Excel – 2615” →

Excel Magic Trick 1835 Part 02: Lookup ROW with CHOOSECOLS, INDEX, XLOOKUP or FILTER function?

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1835-1837.xlsx
Learn about how to lookup a row using CHOOSECOLS, INDEX, FILTER or XLOOKUP functions.
Topics:
1. (00:00) Introduction
2. (00:19) Viewer Question
3. (00:38) CHOOSEROWS and XMATCH
4. (01:23) XLOOKUP
5. (02:06) INDEX and XMATCH
6. (03:00) FILTER
7. (03:37) See that all these methods work in SUM function to add sales for the month
8. (04:01) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp #lookup #xlookup #vlookup #vlookupformula

Python In Excel – Getting Started – 2614

Posted on by Annie D

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

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 Continue reading “Python In Excel – Getting Started – 2614” →

Lookup Column with CHOOSECOLS, INDEX or XLOOKUP function? Excel Magic Trick 1835

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1835-1837.xlsx
Learn about how to lookup a column using CHOOSECOLS, INDEX or XLOOKUP functions.
Topics:
1. (00:00) Introduction
2. (00:10) CHOOSECOLS and XMATCH
3. (01:10) XLOOKUP
4. (01:43) INDEX and XMATCH
5. (03:20) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp #lookup #xlookup #vlookup #vlookupformula

Excel Stale Value Formatting – Episode 2613

Posted on by Annie D

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

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
(4:25) What’s your Continue reading “Excel Stale Value Formatting – Episode 2613” →