Category: Excel

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

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

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

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

GROUPBY & PERCENTOF Functions Not Work With Text Data!?!? Here Is Solution!! Excel Magic Trick 1870

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1870-1871.xlsx
Learn how to use the GROUPBY function with the eta lambda function COUNTA and PERCENTOF to create frequency distribution that works with text data. Create a count column and a % of Column Total Column with the GROUPBY Function.
ExcelLambda from YouTube supplies awesome solution!
Topics:
1. (00:00) Introduction.
2. (00:21) PivotTables vs. GROUPBY or PIVOTBY Function
3. (00:57) First attempt at using GROUPBY Function. See functions GROUPBY, HSTACK, COUNT and PERCENTOF.
4. (02:00) Why PivotTable correctly calculates % of Column Total for Text Data
5. (02:29) Algorithm for PERCENTOF Function and how it is different than a PivotTable % of Continue reading “GROUPBY & PERCENTOF Functions Not Work With Text Data!?!? Here Is Solution!! Excel Magic Trick 1870” →

No More VBA! Amazing WRAPROWS Function Converts Column of Records to Table. #Short Excel Trick 61

Posted on by Annie D

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch11/ExcelShort60-62.xlsx
Learn how to convert a column of records to a proper table without VBA, using the Microsoft 365 Excel Array Function, WRAPROWS function. See Two Examples.

Learn #excel #excelformulas #excelfunctions #Short #array #dynamicspill #arrayformulas #office365 #office365training #data #datascience #database #datamodelling #tablestructure #table #peopertable #microsoft Excel #excelisfun #wraprows

Best Formula for % Change (Daily Stock Returns). #Short Excel Trick 60

Posted on by Annie D

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch11/ ExcelShorst60-62.xlsx
Learn how to create a dynamic spilled array formula to calculate the daily returned for three columns of stocks. Learn that the easiest formula for 5 change is End/Begin – 1 , not (End – Begin)/Begin.

Learn #excel #excelformulas #excelfunctions #Short #365excel #office365 #office365training #excelfinance #percentage #dailyreturns #excelisfun #change #changeyourlife

Stock Portfolio Expected Return & Beta with single cell array formula. Bonus Trick Too! #Short 59

Posted on by Annie D

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch10/ExcelShort57-59.xlsx
Learn how to create single cell array formula that allows you to skip many intermediate steps so you can calculate the expected return and beta for a portfolio of stocks using array calculations and the SUM function

Learn #excel #excelformulas #excelfunctions #Short #excelfinance #beta #returns #portfoliomanagement