Category: Excel

Lookup Manager Name in Column Headers to Create Report: Excel Magic Trick 1866

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1866.xlsx
Buy M Code Book at Mr Excel site: https://www.mrexcel.com/products/the-… or Amazon: https://www.amazon.com/Transformative… Mike “excelisfun” Girvin’s 4th book: The Transformative Magic of Power Query M Code in Power BI & Excel. Book about Power Query M Code to help data analysts transform and shape data into useful, actionable information for decision makers.

Topics in Video:
1) (00:00) Introduction
2) (00:55) Worksheet function to create column of employee names from a table using TOCOL and UNIQUE functions.
3) (01:30) Worksheet function to lookup manager names and join with a comma between each name using Continue reading “Lookup Manager Name in Column Headers to Create Report: Excel Magic Trick 1866” →

Excel 365 to Make Your Teaching Job Easy: Dynamic Spilled Arrays, XLOOKUP, Power Query & PivotTables

Posted on by Annie D

Highline College Professional Development Day 2024 presentation by Mike “excelisfun” Girvin
Download Excel file: https://people.highline.edu/mgirvin/M365Excel-HighlineProDevDay.xlsx
Pdf file with video notes: https://people.highline.edu/mgirvin/M365Excel-HighlineProDevDay.pdf
In this video learn how to: Create Gradebook with Dynamic Spilled Arrays Formulas and the XLOOKUP function, Use Dynamic Spilled Arrays Formulas to Create Budget, Use Power Query to Import Student Grade Data from Canvas, Use PivotTables to Create Summary Reports from Student Survey Data.

Topics:
1. (00:00) Introduction
2. (01:25) Create Gradebook with Dynamic Spilled Arrays Formulas and the XLOOKUP function.
3. (12:05) Don’t be Tricked by Number Formatting
4. (16:05) BYROW and BYCOL functions to spill aggregate totals and Continue reading “Excel 365 to Make Your Teaching Job Easy: Dynamic Spilled Arrays, XLOOKUP, Power Query & PivotTables” →

DAME 07: How to Make Effective Dashboard. Three Examples in Power BI

Posted on by Annie D

Download files: https://excelisfun.net/files/DAMEwithMPT07.zip
Free YouTube Data Analysis Class about Microsoft Power Tools in 2024 taught by Excel MVP and Highline College Professor, Mike “excelisfun” Girvin.
In this video learn about the guidelines for how to build effective dashboards. See three examples in Power BI.
Topics:
1. (00:00) Introduction
2. (00:27) Guidelines for how to build effective dashboards
3. (09:03) Files to build Three Dashboards.
4. (09:35) Dashboard #1: Average Daily & Monthly Metrics across various attributes
5. (19:33) Dashboard #2: Grade Analysis Dashboard
6. (26:42) Dashboard #3: YOY Sales Dashboard
7. (31:27) Summary
8. (31:40) Conclusion

#dashboard #powerbi #powerbidesktop #visualization

Excel Slow Workbook Speeds Up Instantly With ForceFullCalculation Toggled Off – 2644

Posted on by Annie D

Microsoft Excel Tutorial: Make workbook calculate faster by toggling off the ForceFullCalculation setting.

Welcome back to MrExcel! Today, I’ve got an incredible tip that can make your Excel workbooks run dramatically faster. Have you ever heard of ActiveWorkbook.ForceFullCalculation = False? Probably not, because it’s a setting that’s rarely documented. But it can make a huge difference in calculation speed, as shown in this example sent by Matt from Chicago, a member of the MrExcel message board.

Matt provided us with a file containing 7,000 rows and 27 columns, which is just a fraction of the size of his client’s actual files. With Continue reading “Excel Slow Workbook Speeds Up Instantly With ForceFullCalculation Toggled Off – 2644” →

DAME 06: DAX Fundamentals in Power BI & Power Pivot: 2.5 Hour Free Class

Posted on by Annie D

Download files: https://excelisfun.net/files/DAMEwithMPT06.zip, pdf notes: https://excelisfun.net/files/06-DAMEMPT.pdf
This video teaches how the fundamentals of Columnar Database, DAX Calculated Columns, DAX Measures, Row Context, Filter Content, Context Transition, Overwrite Operation, DAX X Iterator functions, DAX CALCULATE function and much more!
Free YouTube Data Analysis Class about Microsoft Power Tools in 2024 taught by Excel MVP and Highline College Professor, Mike “excelisfun” Girvin.
In this video learn about
This video covers.
1. (00:00:00) Introduction and video topics
2. (00:00:25) Topics in Video
3. (00:01:49) Why we use DAX, M Code & Worksheet Formulas: What makes Each Special?
4. (00:05:30) Review data, data structure, Excel Continue reading “DAME 06: DAX Fundamentals in Power BI & Power Pivot: 2.5 Hour Free Class” →

Excel – RegEx Building Blocks – 2643

Posted on by Annie D

Microsoft Excel Tutorial: Learning the Building Blocks for RegEx in Excel
Learn the basic building blocks of RegEx in Excel.

To download the RegEx Cheat Sheet and workbook from today: https://www.mrexcel.com/youtube/fbHQgDM0P1E/

Welcome back to MrExcel’s YouTube channel! In this video, we’ll dive into the building blocks for mastering Regular Expressions (RegEx) and their integration in Excel. If you find this video helpful, don’t forget to click the Like button below – it helps us reach more viewers. We’ve got over 2,500 videos on various Excel topics, so there’s plenty to explore!

Our focus today is on RegEx, specifically the new functions Continue reading “Excel – RegEx Building Blocks – 2643” →

Excel RegEx Support For Pattern Matching – Episode 2642

Posted on by Annie D

Microsoft Excel Tutorial: Excel Adds RegEx Support for Pattern Matching in Excel.

🎥 Unlocking the Power of RegEx in Excel: New Features Explained! 📊

Hey Excel enthusiasts! 🌟 In today’s video, we’re diving into an exciting new feature: RegEx support in Excel for pattern matching. If you enjoy exploring new Excel functionalities, this is a must-watch! Don’t forget to Like this video to help it reach more people. 👍

🔥 What’s New? 🔥
Excel has introduced three powerful RegEx functions: RegExTest, RegExReplace, and RegExExtract. These functions are set to revolutionize how we handle data patterns. Plus, there are updates to XLOOKUP and XMATCH functions Continue reading “Excel RegEx Support For Pattern Matching – Episode 2642” →

DAME 05: Master Power Query M Code & Become a Data Shaping Ninja (The Secret Behind Data Analysis)

Posted on by Annie D

DAME 05: Master Power Query M Code & Become a Data Shaping Ninja (The Secret Behind Data Analysis)
Download files: DAME05zip Pdf notes to read online: DAME05pdfNotes
Alternative download site: https://excelisfun.net/files/DAMEwithMPT05.zip, pdf notes: https://excelisfun.net/files/05-DAMEMPT.pdf
In this video learn about all the fundamentals of the M Code language, the coding language behind Power Query. Learn all about the keys to M Code Mastery: M Code Values, Expressions, Data Types, Operations by Data Types, let expression, M Code Lookup, Custom Functions, and M Code functions such as: Table.AddColumn, Csv.Documnet, Excel.CurrectWorkbook, Table.Group and much more!
Free YouTube Data Analysis Class about Continue reading “DAME 05: Master Power Query M Code & Become a Data Shaping Ninja (The Secret Behind Data Analysis)” →

DAME 04: Data Analysis with Excel Worksheet Formulas: 4 Comprehensive examples.

Posted on by Annie D

Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/2024/Content/Week06/Video04Files.zip
Free YouTube Data Analysis Class about Microsoft Power Tools in 2024 taught by Excel MVP and Highline College Professor, Mike “excelisfun” Girvin.
In this video learn about when Excel worksheet formulas are the perfect tool for data analysis. An hour lesson in the power of Excel worksheet formulas and when they beat PivotTables, Power Query and Power BI.
Topics:
1. (00:00) Introduction
2. (00:40) 4 scenarios where worksheet formulas are better than other tools.
3. (02:33) Example #1: Enter Data In Worksheet & Need Analysis Off To Side.
4. (03:22) MOD function for hours worked formula
5. (05:19) Line Chart
6. (06:37) 7-Day Continue reading “DAME 04: Data Analysis with Excel Worksheet Formulas: 4 Comprehensive examples.” →

DAME 03: PivotTables Rule for Quick & Easy Reports! 34 Amazing PT Tricks.

Posted on by Annie D

Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/2024/Content/Week06/Video03Files.zip Pdf notes to read online: https://people.highline.edu/mgirvin/AllClasses/348/348/2024/Content/Week06/03-DAMEMPT.pdf
Free YouTube Data Analysis Class about Microsoft Power Tools in 2024 taught by Excel MVP and Highline College Professor, Mike “excelisfun” Girvin. This video teaches you the beauty and simplicity of PowerQuery yo get, transform, abd load the data to the location that you want/.

Topics:
1. (00:00) Introduction
2. (00:26) Free PivotTable Cheat Sheet in workbook file or pdf notes
3. (00:39) Toy sales Data Set
4. (01:21) Why use PivotTables?
5. (01:58) Basics of PivotTables
6. (02:35) PivotTable Cache
7. (04:02) Change Default PivotTable Layout
8. (04:40) PT Calculations: Summarize Values By, Continue reading “DAME 03: PivotTables Rule for Quick & Easy Reports! 34 Amazing PT Tricks.” →

DAME 2 (Updated): MS Data Analysis Tools: Excel, Power Query, Power BI, Data Flow, DAX & M Code!

Posted on by Annie D

Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/2024/Content/Week01a02/Video01-02Files.zip
Pdf notes to read online: https://people.highline.edu/mgirvin/AllClasses/348/348/2024/Content/Week01a02/01-DAMEMPT.pdf
Alternative link for zipped folder: https://excelisfun.net/files/Video01-02Files.zip
Free YouTube Data Analysis Class about Microsoft Power Tools in 2024 taught by Excel MVP and Highline College Professor, Mike “excelisfun” Girvin.
In this video learn about ALL the Microsoft Power Tools: Excel, Power Query, Power Pivot, Data Model, Power BI, Dataflow, Worksheet formulas, Dynamic Spilled Array Formulas, M Code formulas, DAX formulas, and much more!
Topics:
1. (00:00) Introduction
2. (00:13) List of MS Power Tools taught in video
3. (00:44) Preview of 7 examples
4. (01:48) Why use Worksheet as a data analysis Continue reading “DAME 2 (Updated): MS Data Analysis Tools: Excel, Power Query, Power BI, Data Flow, DAX & M Code!” →