Category: Excel

Data Validation List Remove Duplicates, Not Past 33,000? TRUE: Then Go Old School! EMT 1878

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1878.xlsx
See that the Data Validation List feature removes duplicates up to 33,000 rows. After that, we must do old school methods: SORT and UNIQUE functions, or even older school methods from the 2012 Ctrl Shift Enter Mastering Excel Array Formulas Book.
ExcelLambda from YouTube supplies awesome solution!
Topics:
1. (00:00) Introduction
2. (00:03) New Ability of Data Validation List to Remove Duplicates
3. (01:06) Example of New Feature When It Does Not Work Poast Rows 33,000
4. (01:27) Old Fashion Way to Overcome New Feature Limited Row Ability
5. (02:20) Summary
6. (02:29) Closing

#excel #microsoft #excelisfun #excelcourse #datavalidation #excelfail

Excel History: From Bricklin & Frankston to Joe McDaid & the New Excel Frontier. Short Excel # 70

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/ExcelShort69-71.xlsx

See the old method of Excel worksheet formulas and the New, easier, Microsoft Excel 365 worksheet formulas.
#excel #365excel #excel365 #spredsheet #visicalc #lotus #lotus123

Excel Pivot Table Number Format Copied From Source Data Episode 2674

Posted on by Annie D

🚀 *Unbelievable Excel Update!* 🚀

Pivot Table numeric formatting is now automatically coming from the grid! 🎉 Microsoft didn’t announce this (that I’ve seen), but it just *worked* this morning. No more manually formatting numbers in your Pivot Tables—Excel is pulling it straight from the source data.

✅ Thousands separator? ✔️
✅ Currency symbols? ✔️
✅ No extra steps? ✔️

Watch as I demo this game-changing update with a quick dataset. This small but mighty improvement is a *huge* win for efficiency!

💡 Have you seen this in your version of Excel yet? Let Continue reading “Excel Pivot Table Number Format Copied From Source Data Episode 2674” →

Data Set Random Sales Numbers: RANDBETWEEN or NORM.INV Function? Excel Short 68

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/ExcelShort68.xlsx

Learn how to randomize numbers with a normal distribution using NORM.INV function or use the RANDBETWEEN or RANDARRAY functions to randomize numbers across a uniform distribution.

#excel #excelfunctions #microsoft #excelisfun #data #randomize

PIVOTBY & LAMBDA Functions to Covert Bad Data Into Proper Table, When There Are Duplicates. EMT 1877

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1877.xlsx
Learn how to convert to bad data table with records of data in columns to a proper table structure with records in rows using the PIVOTBY & LAMBDA Functions. This solution deals with duplicate values.
This trick comes from ExcelLambda at YOUTUBE.
Topics:
1. (00:00) Introduction.
2. (00:24) PIVOTBY & LAMBDA Functions Solution. See COUNTA and IF Functions and the @ implicit intersection operator.
3. (01:55) Summary
4. (02:04) Closing
#excel #microsoft #excelisfun #excelcourse #groupby #pivotby #excel365 #arrayformulas #datamodeling #data #dataanalytics #datascience #dataanalysis #etl #etltools #cleandata #transformdata #lambda

Unbelievable PIVOTBY & SINGLE Functions to Covert Bad Data Into Proper Table. Excel Magic Trick 1876

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1876.xlsx
Learn how to convert to bad data table with records of data in columns to a proper table structure with records in rows using the PIVOTBY & SINGLE Functions. This trick comes from viktorasgolubevas at YOUTUBE.
Topics:
1. (00:00) Introduction.
2. (00:25) PIVOTBY & SINGLE Functions Solution.
3. (01:48) Summary
4. (02:23) Closing

#excel #microsoft #excelisfun #excelcourse
#groupby #pivotby #single #singlemom #singlelife #excel365 #arrayformulas #datamodeling #data #dataanalytics #datascience #dataanalysis #etl #etltools #cleandata #transformdata

No VBA Needed : ) Simple Power Query Table.Pivot Function Makes it Simple! Excel Magic Trick 1875

Posted on by Annie D

Link to Amazon M Book: https://www.amazon.com/Transformative-Magic-Power-Query-Excel/dp/1615470832 (Please write a review too)
Download Excel File: https://excelisfun.net/files/EMT1875.xlsx
Learn how to convert to bad data table with records of data in columns to a proper table structure with records in rows using the Pivot Column Power Query Feature. Lean about efficient M Code (functional language behind Power Query) also!

Learn about the difference between the Table.Pivot function and the Table.UnPivot function.

Topics:
1. (00:00) Introduction.
2. (00:26) Compare to what we did in Excel Short Trick 61
3. (01:20) Difference between Power Query Pivot Column feature & Unpivot Column feature: STAR!!!!
4. (01:47) Pivot Continue reading “No VBA Needed : ) Simple Power Query Table.Pivot Function Makes it Simple! Excel Magic Trick 1875” →

FILTER & XLOOKUP to Match One Column, Get All TRUEs in Column & Return All Row Headers. EMT 1874

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1874.xlsx
Learn how to do a complicated reverse lookup with a simple formula using the XLOOKUP and FILTER functions.
Topics:
1. (00:00) Introduction.
2. () Formula for reverse lookup with a simple solution using the XLOOKUP and FILTER functions
3. (10:31) Summary
4. (11:12) Closing

#excel #microsoft #excelisfun #excelcourse

LET Function to Define LAMBDA as f, then Perform Column Iteration With Vertical Spill. #Short 67

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 LAMBDA and BYCOL that will iterate columns in an array and spill multiple aggregate results vertically. LET will use the LAMBDA function to define a reusable function, where all you have to do is enter the aggregate function name and below each column the aggregate calculation will appear from a spill.
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 Continue reading “LET Function to Define LAMBDA as f, then Perform Column Iteration With Vertical Spill. #Short 67” →

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