Category: Business

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

Saying goodbye…

Posted on by Annie D

It’s time to say goodbye and move on.

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

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

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

— http://twitter.com/guyinacube
— http://twitter.com/awsaxton
— http://twitter.com/patrickdba
— http://www.facebook.com/guyinacube
— https://www.instagram.com/guyinacube/
— https://guyinacube.com

***Gear***
🛠 Check out my Tools page – https://guyinacube.com/tools/

#GuyInACube

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

🔴 Let’s talk Microsoft Fabric – LIVE w/ Matthew Roche (Aug 19, 2023) (Member Chat 2nd Half)

Posted on by Annie D

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

Connect with Matthew:
Mastodon: https://techhub.social/@SQLAllFather
YouTube: https://www.youtube.com/channel/UCpsilPn-2qFlrYYuvyFkpPQ
Website: https://ssbipolar.com/

💥 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 Continue reading “🔴 Let’s talk Microsoft Fabric – LIVE w/ Matthew Roche (Aug 19, 2023) (Member Chat 2nd Half)” →

Using Figma to wireframe your Power BI reports

Posted on by Annie D

You care about how your Power BI reports look. Sekou joins us to explore using Figma to wireframe your report design and get that look you want before doing all the hard work in Power BI Desktop!

Figma
https://www.figma.com/

Connect with Sekou Tyler
https://www.youtube.com/@SQLSekou
https://www.linkedin.com/in/sekoutyler/
https://twitter.com/sqlsekou
https://lovelytics.com/power-bi/

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

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

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

— http://twitter.com/guyinacube
— <a href="http://twitter.com/awsaxton" Continue reading “Using Figma to wireframe your Power BI reports” →

Enabling Power BI Paginated Report Drill!

Posted on by Annie D

Power BI paginated reports support drillthrough! Patrick walks you through how to configure the reports within Report Builder to get them working.

Drillthrough reports in a paginated report (Power BI Report Builder)
https://learn.microsoft.com/power-bi/paginated-reports/report-design/drillthrough-reports-report-builder

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

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

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

— http://twitter.com/guyinacube
— http://twitter.com/awsaxton
— http://twitter.com/patrickdba
— http://www.facebook.com/guyinacube
— https://www.instagram.com/guyinacube/
— https://guyinacube.com

***Gear***
🛠 Check out my Continue reading “Enabling Power BI Paginated Report Drill!” →

Is Data Wrangler the Power Query of Python?

Posted on by Annie D

Have you tried Data Wrangler within Microsoft Fabric or Visual Studio Code? It’s a great way to start cleaning your data and have it write your python code for you! Really getting the Power Query vibes and we love it!

How to accelerate data prep with Data Wrangler in Microsoft Fabric
https://learn.microsoft.com/fabric/data-science/data-wrangler

Introducing the Data Wrangler extension for Visual Studio Code
https://devblogs.microsoft.com/python/data-wrangler-release/

Connect with Eren Orbey:
https://www.linkedin.com/in/orbey/
https://twitter.com/erenorbey

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

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

Want to take your Power BI skills to the next level? We have training courses available to help you with Continue reading “Is Data Wrangler the Power Query of Python?” →

🔴 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