Category: Excel

Check First Letter in Each Name Against a List of Letters, Then Count. Excel Magic Trick 1850

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1850.xlsx
Learn about how to count how many names in a column start with the letters a, b or c. Then make formula dynamic so it can check any set of letters.
Topics:
1. (00:00) Introduction
2. (00:05) Formula that uses LEFT, an array constant {“a”,”b”,”c”} and the SUM function
3. (00:38) Why the two arrays must be in opposite directions
4. (02:22) Dynamic formula linked to a list in the cells.
5. (02:43) Sumamry, Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #count #left #excelfunctions #countifs

Excel Customers Paying Less Than Average – Episode 2639

Posted on by Annie D

Microsoft Excel Tutorial: Create an Excel Pivot Table to Identify Which Customers Are Paying Less than Average for a Product.

To download the workbook from today: https://www.mrexcel.com/youtube/4XrKZhiBB-k/

Hey Excel enthusiasts! 👋 In this MrExcel netcast, we tackle a fascinating question from Glenn, who joined our recent Nashville IMA webinar. Ever wondered which customers are paying less than the average? 🧐 We’ve got you covered!

🔍 Using a special pivot table based on the Data Model, we delve into the powerful DAX function CALCULATE. Follow along as we transform our data into a table named “Sales” and explore 12,000 rows of Continue reading “Excel Customers Paying Less Than Average – Episode 2639” →

Filter Feature CANNOT run an OR Logical Test Across Two Columns: 2 Solutions. Excel Magic Trick 1847

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1847-1849.xlsx
Learn that the Filter Feature CANNOT run an OR Logical Test Across Two Columns. Solution #1: Add a Helper Column with the OR function. Solution #2: Use the FILTER Array Function. Lean about logical tests in the FILTER function.
Topics:
1. (00:00) Introduction
2. (00:06) Filter Feature CANNOT run an OR Logical Test Across Two Columns
3. (01:03) Helper Column with OR Function to help the Filter Feature
4. (02:01) FILTER Array Function with OR Logical Test
5. (04:06) Summary
6. (04:20) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #filter #filterfunction

Excel Show Details In Excel Pivot Table – Episode 2638

Posted on by Annie D

Microsoft Excel Tutorial: Microsoft improves the Show Details feature in a pivot table and adds new entry points.

To download the workbook from today: https://www.mrexcel.com/youtube/d8kKJlZIr_g/

Unlock New Insights with Excel Pivot Tables! 🚀 In this tutorial, we dive into the latest updates from the Excel team. Discover the new Show Details feature, making data analysis smoother than ever! 📊💡

🔍 Show Details Icon: Spot the new icon on the Pivot Table Analyze tab – it’s a game-changer! Similar to the classic double-click, it opens a new sheet to the left with detailed information. Now, with a handy title indicating the Continue reading “Excel Show Details In Excel Pivot Table – Episode 2638” →

Excel Shortest Route To 148 Locations Around The Supernatural World Using Excel – Episode 2637

Posted on by Annie D

Microsoft Excel Tutorial: Finding best routing for a trip with 148 locations.

Hey Excel fam! 🚀 Bill Jelen here with an epic Excel challenge. 🏋️‍♂️ Joined forces with the Supernatural workout community to optimize a 148-location quest using Excel magic. 🌍 Check out the journey, from Tahiti to Antarctica, and witness the distance-cutting madness unfold. 🌐

💡 First attempt started in Tahiti, but the route was a rollercoaster. 🔄 Tried a radical pivot, starting in Antarctica. 🏔️ Improved the distance to 90,253 miles, but still seeking perfection.

🤔 Experimented with K-Means clustering, exploring different strategies. 🧩 Finally settled on a plan starting in Continue reading “Excel Shortest Route To 148 Locations Around The Supernatural World Using Excel – Episode 2637” →

Excel LAMBDA, LAMBDA Helper Functions & Eta-LAMBDAs: the Complete Story – Updated 2024: 365 MECS 10

Posted on by Annie D

Download Excel Start file: https://excelisfun.net/files/10-M365ExcelClassStart2024.xlsx
Download pdf notes: https://excelisfun.net/files/10-M365ExcelClass2024.pdf
Download Excel Finished file: https://excelisfun.net/files/10-M365ExcelClassFinished2024.xlsx
This is a 1:45 hour video about everything that the Excel LAMBDA function, LAMBDA Helper Functions & Eta-LAMBDAs can do. This video is also a complete lesson in Defined Names, the LET function and Spilled Single Cell Formula Reports.
Course taught by Excel MVP and Highline College Professor, Mike Girvin. Course is Microsoft 365 Excel Complete Story.
Topics in video:
1. (00:00) Introduction
2. (01:18) Overview
3. (03:09) Defined Names
4. (13:00) First look at LAMBDA to create a re-usable function
5. (19:25) Advanced Formula Environment
6. (24:08) Continue reading “Excel LAMBDA, LAMBDA Helper Functions & Eta-LAMBDAs: the Complete Story – Updated 2024: 365 MECS 10” →

Excel Publish Any Excel Logic As Web App Using Sheetcast While Protecting Your IP – Episode 2635

Posted on by Annie D

Microsoft Excel Tutorial: Embed any Excel Logic in a Web App Using Sheetcast.com.

Try this web app now to search my videos: https://c07.sheetcast.com/view/c79f93d2-0dcf-441f-97ac-b0c100f02a4f/~/d174333f-e3fe-4b14-a13b-ef6e384af5bb?embed=True&expand=true&showTitle=true

Welcome to episode 2635 of the MrExcel podcast! It’s been 25 years since I started sharing my Excel tips and tricks with all of you, and I couldn’t be more grateful for your support. Today, I have something truly amazing to show you – how to publish any Excel logic as a web app using Sheetcast while protecting your intellectual property. Plus, I’ll also be sharing how you can search through 25 years of MrExcel content Continue reading “Excel Publish Any Excel Logic As Web App Using Sheetcast While Protecting Your IP – Episode 2635” →

Excel PIVOTBY Function to Create Completely Dynamic PivotTable Formula Reports! EMT 1845

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1845.xlsx
You will not believe what you see in this video. See the basics of the new PIVOTBY function to make PivotTable Formula Reports, then see how to create monthly sales reports (with a hack), then see a completely dynamic Formula PivotTable Report where you can use cell inputs to change, Row, Column and Filter Conditions, Change the Function for the report and create a dynamic label that describes the report. Simply Amazing!!!
Topics:
1. (00:00) Introduction.
2. (00:22) PIVOTBY function, complete description and examples.
3. (05:50) Monthly Sales Report with PIVOTBY function.
4. (08:11) Fully dynamic PivotTable report with Continue reading “Excel PIVOTBY Function to Create Completely Dynamic PivotTable Formula Reports! EMT 1845” →

ETA-LAMBDAs for BYROW, BYCOL & SCAN to Make Spilling Row Totals & Running Totals Easy. EMT 1846

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1846.xlsx
Learn about how to use ETA-LAMBDAs to replace LAMBDA to make easier formulas to spill row totals, spill column totals, spill running totals and even spilling an average in each row.
Topics:
1. (00:00) Introduction.
2. (00:18) Spill Row Totals with the BYROW function.
3. (00:50) Spill Column Totals with the BYCOL function.
4. (01:06) Spill a Running Total with the SACN function.
5. (01:32) Spill an Average for each row with the BYROW function.
6. (01:53) Summary.
7. (02:12) Closing, Video Links For Full Free LAMBDA Class.

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #lambda Continue reading “ETA-LAMBDAs for BYROW, BYCOL & SCAN to Make Spilling Row Totals & Running Totals Easy. EMT 1846” →

2 Different Functions in GROUPBY & PIVOTBY Functions. Excel Magic Trick 1844 Part 2

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1844-Part02.xlsx
Learn about how to use the GROUPBY and PIVOTBY functions to make single cell formula reports that have 2 functions in a single report.
Topics:
1. (00:00) Introduction to GROUPBY, PIVOTBY and the new Lambda Replacement Functions.
2. (00:15) GROUPBY Function.
3. (01:08) PIVOTBY Function.
4. (01:36) Confusing Labels in Report.
5. (01:49) Two Fields in the Row Area.
6. (02:01) Two Different Functions with Two Fields in Row Area: Labels are correct!
7. (02:32) Summary.
8. (02:55) Closing, Video Links.
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #groupby #excelformula #excelfunctions #excelfunctions #excelformulasandfunctions #lambda #pivotby

Excel – Review 2400 YouTube Thumbnails Using 2 New Excel Functions – Episode 2634

Posted on by Annie D

Microsoft Excel Tutorial: Quickly download YouTube Thumbnails into Excel.

TubeBuddy Link: https://www.Tubebuddy.com/pricing?a=mrexcel

Formula for Edit Hyperlink: =HYPERLINK(“https://studio.youtube.com/video/“&B2&”/edit”,”Edit”)
Formula to Wrap Thumbnails into 9 columns: =WRAPROWS(FILTER(F2:F2417,G2:G2417=1),9)

Welcome to episode 2634 of MrExcel’s Net Cast! In this video, we will be exploring two new Excel functions that will help us quickly review over 2400 YouTube thumbnails. As we approach the 25th anniversary of MrExcel, I am grateful for the opportunity to connect with friends and followers from around the world through 67 books, thousands of articles, over 5 million posts on our message board, and 2400 videos on YouTube. If Continue reading “Excel – Review 2400 YouTube Thumbnails Using 2 New Excel Functions – Episode 2634” →

GROUPBY Excel Worksheet Function. Single Cell Reports Made Easy! 11 Examples. EMT 1844

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1844.xlsx
Learn about how to use the GROUPBY function to make single cell formula reports that can update instantly when source data changes.
Topics:
1. (00:00) Introduction to GROUPBY, PIVOTBY and the new Lambda Replacement Functions
2. (00:58) Compare arguments in GROUPBY and PIVOTBY functions.
3. (01:58) GROUPBY and the 7 arguments.
4. (01:58) 16 New Lambda Replacement Functions.
5. (05:43) New Lambda Replacement Functions as replacement for LAMBDA.
6. (06:17) PERCENTAGE of in GROUPBY Function.
7. (07:04) Why use formulas rather than PivotTables.
8. (07:17) Two Fields in Row Area of Report.
9. (08:05) Problem: No Label for Calculation Column. Look at field_header argument.
10. Continue reading “GROUPBY Excel Worksheet Function. Single Cell Reports Made Easy! 11 Examples. EMT 1844” →