Month: August 2023

🔴 We’re back! Let’s answer your Microsoft Fabric and Power BI questions… (August 12, 2023)

Posted on by Annie D

Join us live as we answer your questions about Microsoft Fabric (including Power BI)!

💥 30 minutes: Open Q&A (Public)
💥 Then… Members Only Chat (Public can watch & Super Chat for Questions)

📢 RULES FOR Q&A: 📢

👉 Put a “Q:” in front of your comment to help us identify questions!
👉 Super chats take priority
👉 Do NOT re-post your questions! We’ll get to as many questions as we can.
👉 If you SPAM your question, you will be put in timeout

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available Continue reading “🔴 We’re back! Let’s answer your Microsoft Fabric and Power BI questions… (August 12, 2023)” →

Create Excel Master Table from Sub-Tables Using Power Query. Excel Magic Trick 1834

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1833-1834.xlsx
Learn how to take multiple Excel Tables in a Workbook file and create a single master table with all data.
Topics:
1. (00:00) Introduction
2. (00:22) Create Master Table from Sub Tables using Power Query Excel.CurrentWorkbook Function. Learn about pitfalls.
3. (04:39) Recursion Problem, How to Solve.
4. (6:07) PivotTable Report from Mater Table
5. (07:07) Load Directly to Pivot Table to avoid recursion and double refresh.
6. (08:38) Summary
7. (08:52) 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, #excelcurrentworkbook

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” →

XLOOKUP or VLOOKUP Based on Sheet Tabe Name!?! Excel Magic Trick 1833

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1833-1834.xlsx
Learn how to use the worksheet tab name as the lookup value in XLOOKUP or VLOOKUP..
Topics:
1. (00:00) Introduction
2. (00:24) CELL Function
3. (01:21) TEXTAFTER Function
4. (01:49) RELPACE and SEARCH functions.
5. (02:22) XLOOKUP or VLOOKUP
6. (03:27) Copy Lookup Formula
7. (04:13) Summary
8. (04:26) 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

Multiple Discount Lookup Tables!?!? LOOKUP & FILTER Functions. Excel Magic Trick 1832

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1828-1832.xlsx
Learn about how to rearrange multiple lookup tables to allow you to use the FILTER function to lookup multiple tables, then use LOOKUP to do approximate match lookup.
Topics:
1. (00:00) Introduction
2. (00:53) rearrange multiple lookup tables to allow you to use the FILTER function
3. (01:08) Filter Function to lookup multiple tables
4. (02:13) LOOKUP to do approximate match lookup
5. (02:52) Summary of five LOOKUP videos
6. (03:22) 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 #filter #filterfunction #tables #multiple

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” →

Multiple Discount Lookup Tables!?!? LOOKUP & SWITCH Come to The Rescue. Excel Magic Trick 1831

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1828-1832.xlsx
Create lookup formula when you have multiple lookup tables for approximate match lookup using the LOOKUP and SWITCH functions.
Topics:
1. (00:00) Introduction
2. (00:46) Multiple Discount Lookup Tables
3. (01:11) Link to other videos with many solutions
4. (01:17) SWITCH function to lookup multiple tables
5. (02:21) LOOKUP to do approximate match lookup
6. (03:14) 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 #switch #tables #multiple