Blog

Bonbon's Guide To Data & Tech > Blog

LET & BYCOL Functions to Spill Column Aggregate calculations Vertically. #Short 66

Posted on by Annie D

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch11/ExcelShorst64-67.xlsx
Learn how to create a single cell formula with the functions LET and BYCOL that will iterate columns in an array and spill multiple aggregate results vertically. The LET function defines an array and a function, which allows the array to be altered for calculations such as Geometric Mean. Thanks to ExcelLambda at YouTube for the Excel formula knowledge in this video : )

#excel #microsoft #excelisfun #excelcourses #excelcourse #excelfunctions #excelformula #excelfunction #365excel #office365 #office365training #exceltips #exceltutorial #exceltraining #exceltricks #excelmaster #excelmagictrick #excelmagic #short #excelshort
#bycol #let #letfunction #aggregate #spilling #dynnamicspilledarrayformulas

Clean Data with Copilot in Excel – What Works and What Doesn’t? Episode 2673

Posted on by Annie D

Microsoft Excel Tutorial: Excel Clean Data with Copilot

Microsoft Excel just introduced the Clean Data feature in Win32, bringing AI-powered data cleaning straight to the desktop! This tool, previously available in Excel Online since October 2024, promises to detect and fix inconsistencies in your dataset—like inconsistent text, formatting issues, and more. But does it really work as expected?

In this episode, I put Clean Data to the test with a variety of messy datasets. It does well in some areas, like recognizing spelling variations and fixing number formats. However, it struggles with company name inconsistencies, date issues, and partial data cleaning—sometimes catching Continue reading “Clean Data with Copilot in Excel – What Works and What Doesn’t? Episode 2673” →

Power Query: A Cleaner Way to Import & Appends CSV Files The Transformative Power of M Code EMT 1872

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1872.zip
Learn how to import multiple Csv files and append them into a table while extracting the file name and adding it to each Csv table as a new column. The Power Query User Interface methods can do this, but it creates a cluttered and messy queries. See how to streamline this process with a few lines of Power Query M Code (not hard to do). More importantly, learn how to use the let expression to avoid Custom Function syntax and define a variable that can access any scope variable in a formula with back Continue reading “Power Query: A Cleaner Way to Import & Appends CSV Files The Transformative Power of M Code EMT 1872” →

Spill Aggregate Totals with the AGGREAGATE Function. #Short 65

Posted on by Annie D

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch11/ExcelShorst64-67.xlsx
Learn how to create dynamic spilled array formula to spill multiple aggregate clauctae using the AGGREGATE Function (Excel 2010 Function). This is an old school function that can do amazing things in the Era of Dynamic Spilled Array Fomulas.

#excel #microsoft #excelisfun #excelcourses #excelcourse #excelfunctions #excelformula #excelfunction #365excel #office365 #office365training #exceltips #exceltutorial #exceltraining #exceltricks #excelmaster #excelmagictrick #excelmagic #short #excelshort

AGGREGATE Function, Excel 2010, Spill aggregate formulas, Spill Multiple Different Aggregate Calculations

Spill Aggregate Totals with BYCOL Function (GEOMEAN too), Across Bottom of Table! #Short 64

Posted on by Annie D

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch11/ExcelShorst64-67.xlsx
Learn how to create a spilled Excel array formula that can calculate an aggregate total for multiple columns. Learn about the BYCOLS function and even see how to spill Geometric Mean calculations for stock values..

#excel #microsoft #excelisfun #excelcourses #excelcourse #excelfunctions #excelformula #excelfunction #365excel #office365 #office365training #exceltips #exceltutorial #exceltraining #exceltricks #excelmaster #excelmagictrick #excelmagic #short #excelshort

Conquer Circular Dependencies: Sort Months Effortlessly in Power BI

Posted on by Annie D

Are month-sorting errors driving you mad? In this video, Patrick lifts the curtain on why your semantic model keeps throwing that frustrating circular dependency error. Watch step by step as he reveals the fix—plus a game-changing tip on heading off these issues upstream in Power Query or even at the data source. It’s time to banish those Power BI sorting nightmares and reclaim your data sanity once and for all!

Demo File:
https://github.com/guyinacube/demo-files/blob/master/video%20demos/PBIX/2025/20250227%20-%20Month%20will%20not%20sort.zip

📢 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 Continue reading “Conquer Circular Dependencies: Sort Months Effortlessly in Power BI” →

AI Doesn’t Know This: Best Solution for Excel Table Running Count Error! #Short Excel Trick 63

Posted on by Annie D

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch11/ExcelShorst63.xlsx
Learn how to create a simple and easy running count formula in an Excel Table. ROWS Functions with special trick.

Learn #excel #excelformulas #excelfunctions #Short #excelisfun #microsoft #datamodeling #dataanalysis #office365 #office365training

How I Fixed 1000+ Power BI Comments in ONE Day!

Posted on by Annie D

Join Patrick as he shares how he tackled some Power BI questions! These honestly answer thousands of questions we get every day. Following best practices solves so many head-aches!

Demo files:
https://github.com/guyinacube/demo-files/blob/master/video%20demos/PBIX/2025/20250226%20-%20Star%20Schema.zip

📢 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 Continue reading “How I Fixed 1000+ Power BI Comments in ONE Day!” →

BYROW, BYCOL, TOROW, TOCOL, FILTER, IFS & OR Eta Functions Do Lookup Magic. Re-do EMT 1426. EMT 1871

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1870-1871.xlsx
Learn how to lookup a value in a rectangle range and return the row or column header with the best and easiest formula ever!!! I did a video 7 years ago (EMT 1426) and the formula was much harder. But now with MS 365 Excel: WOW!!! Everything is so much easier : ) Thanks to ExcelLambda from YouTube for the rad formulas!
Topics:
1. (00:00) Introduction.
2. (00:54) FILTER & BYCOL functions with the Eta Lambda OR Function to create lookup formula that returns column header (Sales Team / Sales Rep Lookup Table example).
3. (02:49) IFS & Continue reading “BYROW, BYCOL, TOROW, TOCOL, FILTER, IFS & OR Eta Functions Do Lookup Magic. Re-do EMT 1426. EMT 1871” →

We’re Going to Try to Automate VBA Coding With AutoVBA!

Posted on by Annie D

***** Video Details *****
In this episode, we explore the power of AI-driven application development and how it’s unlocking new automation possibilities. Learn about AutoVBA, a VBA automation app designed to generate and manage complex code effortlessly, reducing manual work. From AI-enhanced user experiences to solving complex logic dependencies, these projects showcase the incredible potential of AI-first development. As AI continues to evolve, so do the opportunities to build smarter, faster, and more intuitive applications. Tune in to see what’s possible!

***** Related Links *****
https://lovable.dev/
https://supabase.com/

***** Learning with Enterprise DNA *****
FREE Courses – <a href="https://bit.ly/45fu3tw" Continue reading “We’re Going to Try to Automate VBA Coding With AutoVBA!” →

Excel’s WRAPCOLS Array Function to Undo Table Into a Specified # of Columns. #Short Excel Trick #62

Posted on by Annie D

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch11/ExcelShort60-62.xlsx
Learn how to use the WRAPCOLS function to take a proper table and move the data into a specified number of columns (how many items encountered in column must be moved to each new column..

Learn #excel #excelformulas #excelfunctions #Short #excelisfun #microsoft #datamodeling #dataanalysis #office365 #office365training