Name: Excel Magic Trick 1314: Array Formula To Create Sorted Unique List with Mixed Data @excelisfun
Uploaded: 2026-09-29T08:17:00-05:00
Duration: 8 min 17 s
Description: Excel Magic Trick 1314: Array Formula To Create Sorted Unique List with Mixed Data

Excel Magic Trick 1314: Array Formula To Create Sorted Unique List with Mixed Data

excelisfun 2026-09-29 | Excel Magic Trick 1314: Array Formula To Create Sorted Unique List with Mixed Data

Excel 365 to Make Your Teaching Job Easy: Dynamic Spilled Arrays, XLOOKUP, Power Query & PivotTables

excelisfun 2024-10-03 | Highline College Professional Development Day 2024 presentation by Mike “excelisfun” Girvin
Download Excel file: https://people.highline.edu/mgirvin/M365Excel-HighlineProDevDay.xlsx
Download csv files:
https://people.highline.edu/mgirvin/Busn430CanvasScores.csv
https://people.highline.edu/mgirvin/Busn430CanvasScores01.csv
https://people.highline.edu/mgirvin/Busn430CanvasScores02.csv
Pdf file with video notes: excelisfun.net/files/M365Excel-HighlineProDevDay.pdf
In this video learn how to: Create Gradebook with Dynamic Spilled Arrays Formulas and the XLOOKUP function, Use Dynamic Spilled Arrays Formulas to Create Budget, Use Power Query to Import Student Grade Data from Canvas, Use PivotTables to Create Summary Reports from Student Survey Data.

Topics:
1. (00:00) Introduction
2. (01:25) Create Gradebook with Dynamic Spilled Arrays Formulas and the XLOOKUP function.
3. (12:05) Don’t be Tricked by Number Formatting
4. (16:05) BYROW and BYCOL functions to spill aggregate totals and averages
5. (19:50) Dynamic Spilled Arrays Formulas to Create Budget
6. (25:17) Power Query to Import Student Grade Data from Canvas
7. (39:49) PivotTables to Create Summary Reports from Student Survey Data
8. (44:52) Cross Tabulated PivotTable is like Three Reports in One!
9. (51:16) Summary
10. (51:48) Closing and Video Links

Lookup Manager Name in Column Headers to Create Report: Excel Magic Trick 1866

excelisfun 2024-09-19 | Download Excel File: excelisfun.net/files/EMT1866.xlsx
Buy M Code Book at Mr Excel site: https://www.mrexcel.com/products/the-... or Amazon: https://www.amazon.com/Transformative... Mike "excelisfun" Girvin's 4th book: The Transformative Magic of Power Query M Code in Power BI & Excel. Book about Power Query M Code to help data analysts transform and shape data into useful, actionable information for decision makers.

Topics in Video:
1) (00:00) Introduction
2) (00:55) Worksheet function to create column of employee names from a table using TOCOL and UNIQUE functions.
3) (01:30) Worksheet function to lookup manager names and join with a comma between each name using the IF and TEXTJOIN functions.
4) (03:41) Links to buy new excelisfun Mike Girvin M Code book
5) (03:57) Power Query Solution. Keyboard to import Excel Table into Power Query Editor
6) (04:42) UnPivot to create a proper data table
7) (06:22) Group By to gather manager names into a table.
8) (07:00) Why to remove spaces from identifier names
9) (08:01) Edit Table.Group Function to extract a column from a table as a list using the Field Access Operator.
10) (08:10) What does M Code syntax Underscore mean?
11) (09:22) Text.Combine function
12) (10:23) Look at let expression
13) (11:03) Summary
14) (11:20) Closing and video links ()

#Excel, #PowerQuery #Mcode

Highline College Busn 218 Advanced Excel Fall, 2024 Introduction

excelisfun 2024-09-19 | For Fall 2024 matriculating students at Highline College in Seattle, Washington: this is the introduction to the class. For non-matriculating students, the class is free here at YouTube: youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW

Highline College Busn 210 Excel Statistical Analysis Fall, 2024 Introduction

excelisfun 2024-09-19 | For Fall 2024 matriculating students at Highline College in Seattle, Washington. This is the introduction to the Busn 210 Excel Statistical Analysis class. For non-matriculating students, the class is free here at YouTube: youtube.com/playlist?list=PLrRPvpgDmw0m3oqpp1XcPuaxyM4Bpi0dN

Approximate Match Lookup in Power Query: 2 Amazing Functions! Power BI or Excel. EMT 1865

excelisfun 2024-08-14 | Download Excel File: excelisfun.net/files/EMT1865.xlsx
Learn how to perform approximate match lookup in Power Query M Code by creating custom functions. The first function we build in a Custom Column using the Table.SelectRows function and the let expression. The second we build as a reusable function in the Advanced Editor using the Table.SelectRows function. The third we build as a reusable function in the Advanced Editor using the List.Accumulate function.

M Code book from excelisfun at Mr Excel Site: mrexcel.com/products/the-transformative-magic-of-power-query-m-code-in-excel-and-power-bi

M Code book from excelisfun at Amazon: amazon.com/Transformative-Magic-Power-Query-Excel/dp/1615470832/ref=sr_1_1

The M Code book is Mike "excelisfun" Girvin's 4th book: The Transformative Magic of Power Query M Code in Power BI & Excel: the book about Power Query M Code to help data analysts transform and shape data into useful, actionable information for decision makers.

Topics:
1. (00:00) Introduction
2. (00:04) Approximate Match Lookup
3. (00:45) Custom Column with Table.SelectRows function & the let expression solution
4. (06:08) Table.SelectRows function in Re-usable function solution
5. (09:08) List.Accumulate function solution.
6. (12:48) Summary
7. (13:22) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #powerquery #mcode #lookup

Power Query VS. Excel Dates: PQ Solves the Famous 2/29/1900 Date Error So Eloquently! EMT 1864

excelisfun 2024-08-09 | Here is link: mrexcel.com/products/the-transformative-magic-of-power-query-m-code-in-excel-and-power-bi or Amazon: amazon.com/Transformative-Magic-Power-Query-Excel/dp/1615470832/ref=sr_1_1
Mike "excelisfun" Girvin's 4th book: The Transformative Magic of Power Query M Code in Power BI & Excel.

Book about Power Query M Code to help data analysts transform and shape data into useful, actionable information for decision makers.

Remember:
Microsoft and Lotus though that 1900 was a leap year and so they added 2/29/1900 as a date in Excel. But if you import your dates into Excel or the reverse, all dates work.

Special Offer: New Power BI & Excel Power Query M Code Book from excelisfun!!!

excelisfun 2024-08-06 | Here is link: mrexcel.com/products/the-transformative-magic-of-power-query-m-code-in-excel-and-power-bi or Amazon: amazon.com/Transformative-Magic-Power-Query-Excel/dp/1615470832/ref=sr_1_1
Mike "excelisfun" Girvin's 4th book: The Transformative Magic of Power Query M Code in Power BI & Excel.

Book about Power Query M Code to help data analysts transform and shape data into useful, actionable information for decision makers.

GROUPBY & PIVOTBY Functions to Create a 1/2 Year Summary Report! Excel Magic Trick 1863

excelisfun 2024-08-04 | Download Excel File: excelisfun.net/files/EMT1863.xlsx
Learn how to use the amazing GROUPBY and PIVOTBY functions to create a sales report with categories to summaries by half year increments.
Topics:
1. (00:00) Introduction
2. (00:02) Viewer Suggestion
3. (00:18) IF Function to create ½ data attributes for the table of data.
4. (00:43) GROUPBY Function
5. (01:47) Conditional Formatting for Report
6. (02:22) GO TO Trick to find cells in worksheet that contain Conditional Formatting
7. (02:48) Mixed Cell References and Conditional Formatting for Total Row
8. (04:26) PIVOTBY Function
9. (05:04) Add New Data and Watch Formulas Update!!!
10. (05:37) Summary
11. (05:50) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #pivottable #grouping #groupby #formulas #functions #PIVOTBY

1/2 Year Summary Report in a PivotTable? Cool Formula to Help PivotTable! Excel Magic Trick 1862

excelisfun 2024-07-28 | Download Excel File: excelisfun.net/files/EMT1862.xlsx
Learn how to add a formula helper column to the data set to group the dates by half year increments in a PivotTable.
Topics:
1. (00:00) Introduction
2. () Viewer Question
3. () Helper Column Formula
4. () PivotTable
5. () Summary
6. () Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #pivottable #grouping #groupby #formulas #functions

Poem about excelisfun and MVP by Hiran de Silva

excelisfun 2024-07-19 | Here is a poem about excelisfun and the revoked MVP by Hiran de Silva.

excelisfun online form to fill out to regain MVP from Celia Alves

excelisfun 2024-07-19 | Form to fill out: https://lnkd.in/eT4jM3Bd
Celia's MVP story: linkedin.com/feed/update/urn:li:activity:7220049752489431040
Celia's YouTube Channel: youtube.com/c/CeliaAlvesSolveExcel

Introduction to the excelisfun Channel at YouTube: Free Videos, Files and Full Free Courses!!

excelisfun 2024-07-17 | Home page with free videos, files and full free courses: youtube.com/@excelisfun
This is an introduction to the excelisfun channel at YouTube, where the videos, practice files and full courses are all free! Mike Girvin created this channel back in 2008 and have posted over 3,700 videos, over 10,000 practice files and over 100 playlists and classes to learn from.

UNIQUE-SORT, not SORT-UNIQUE: Killer Array Formula Trick That Everyone Should Know. EMT 1861

excelisfun 2024-07-15 | Download Excel File: excelisfun.net/files/EMT1861.xlsx
Download Timing Test file: excelisfun.net/files/EMT1861TestResults.xlsx
When extracting a sorted unique list with a formula, it is more efficient to use UNIQUE-SORT, rather than SORT-UNIQUE.
Topics:
1. (00:00) Introduction
2. SORT-UNIQUE combination tends to calculate slowly.
3. UNIQUE-SORT tends to calculate quickly.
4. () Summary
5. (02:32) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #sort #unique #arrayformulas #excelarrys #dynamicspilled arrays

Amazing XLOOKUP Formula with Lookup Value From Within Description: Excel Magic Trick 1860

excelisfun 2024-07-12 | Download Excel File: excelisfun.net/files/EMT1860.xlsx
Learn how to extract a lookup value from a description and use it to lookup a value without getting an error.
Topics:
1. (00:00) Introduction
2. () Use lookup value from description to lookup a value. MID function and XLOOKUP with Plus Zero.
3. () Summary
4. (02:32) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #xlookup #exceltips

Microsoft Revokes excelisfun MVP Status!

excelisfun 2024-07-11 | Today, July 10, 2024, I got a letter from Microsoft that says I am no longer an Excel MVP. I posted about the same number of videos, files and free classes as the year before. I just passed 1,00,000 subscribers. I give everything away for free. But Microsoft says: NO MVP for excelisfun. So be it. I will keep making Excel and Power BI videos because it is fun!!!

excelisfun Boomerangs Back With Gold Plaque!!! and more!!!!

excelisfun 2024-07-08 | I was in an accident and have been missing for last month. But watch the unboxing of 1M Gold Plaque and Thanks Team!!! Go Team!!!!

excelisfun One Million YouTube Subscribers!!!!

excelisfun 2024-06-07 | Thanks for the amazing 16.25 years here at YouTube!!! 16.25 more to go.
Go Team!!!

DAME 07: How to Make Effective Dashboard. Three Examples in Power BI

excelisfun 2024-06-03 | Download files: excelisfun.net/files/DAMEwithMPT07.zip
Free YouTube Data Analysis Class about Microsoft Power Tools in 2024 taught by Excel MVP and Highline College Professor, Mike “excelisfun” Girvin.
In this video learn about the guidelines for how to build effective dashboards. See three examples in Power BI.
Topics:
1. (00:00) Introduction
2. (00:27) Guidelines for how to build effective dashboards
3. (09:03) Files to build Three Dashboards.
4. (09:35) Dashboard #1: Average Daily & Monthly Metrics across various attributes
5. (19:33) Dashboard #2: Grade Analysis Dashboard
6. (26:42) Dashboard #3: YOY Sales Dashboard
7. (31:27) Summary
8. (31:40) Conclusion

#dashboard #powerbi #powerbidesktop #visualization

DAME 06: DAX Fundamentals in Power BI & Power Pivot: 2.5 Hour Free Class

excelisfun 2024-05-27 | Download files: excelisfun.net/files/DAMEwithMPT06.zip, pdf notes: excelisfun.net/files/06-DAMEMPT.pdf
This video teaches how the fundamentals of Columnar Database, DAX Calculated Columns, DAX Measures, Row Context, Filter Content, Context Transition, Overwrite Operation, DAX X Iterator functions, DAX CALCULATE function and much more!
Free YouTube Data Analysis Class about Microsoft Power Tools in 2024 taught by Excel MVP and Highline College Professor, Mike “excelisfun” Girvin.
In this video learn about
This video covers.
1. (00:00:00) Introduction and video topics
2. (00:00:25) Topics in Video
3. (00:01:49) Why we use DAX, M Code & Worksheet Formulas: What makes Each Special?
4. (00:05:30) Review data, data structure, Excel files, Power BI Desktop files and pdf notes
5. (00:06:33) What is DAX?
6. (00:08:53) Comprehensive Discussion about Star Schema Data Model Components and how they interact with DAX: Columnar Database, Relationships, DAX Formulas, Hidden Columns
7. (00:15:25) Calculated Columns
8. (00:18:28) Row Context
9. (00:21:58) Measures
10. (00:24:04) Power BI Desktop Calculated Columns and Measures
11. (00:27:38) Data Model PivotTable
12. (00:28:41) Filter Context
13. (00:31:58) COUNTROWS Function (Super Charged COUNTIFS Function)
14. (00:32:51) Expanded Table Diagram
15. (00:35:25) Implicit Measures
16. (00:37:20) SUMX function and Iterator Functions to Replace Calculated Columns
17. (00:38:56) Compare SUMX One Step Method to Calculated Column SUM Two Step Method
18. (00:41:06) Row and Filter Context Work Together
19. (00:43:07) Using Measures in other Measures
20. (00:44:21) DIVIDE Function
21. (00:45:50) CALCULATE Function to Change Filter Context
22. (00:47:10) ALL Function to remove filters and get a Grand Total
23. (00:49:26) % of Grand Total Measure
24. (00:50:01) ALLSELECTED Function to get Filtered Grand Total and % of Filtered Grand Total
25. (00:52:31) % of Parent Total DAX Measure: 1) ALLEXCEPT Function and 2) ALLSELECTED & VALUES
26. (00:54:14) Compare ALL and VALUES Functions
27. (00:55:23) CONCATENATEX Function
28. (00:59:21) YOY Change Formula with SAMEPERIODLASTYEAR, CALCULATE, DIVIDE, HASONEVALUE and a special filtering Calculated Column in Data Table
29. (01:02:17) Variables in DAX using VAR and RETURN
30. (01:05:58) Calculated Helper Column to make Measure less complicated
31. (01:11:06) Boolean Filters
32. (01:13:21) Second Look at Filter Context
33. (01:14:36) Overwrite Operation
34. (01:16:26) FILTER & ALL for Boolean Filter
35. (01:17:54) Logical Tests in FILTER and CALCULATETABLE Functions
36. (01:20:10) FILTER & VALUES for Boolean Filter
37. (01:22:02) KEEPFILTER to Convert Overwrite Operations to an AND Logical Tests
38. (01:23:44) Boolean OR Logical Test with Double Vertical Bar Operator
39. (01:25:07) Self Filtering Report with KEEPFILTER
40. (01:25:36) Filter Context with KEEPFILTTERS
41. (01:26:21) Boolean OR Logical Test with IN Operator
42. (01:27:16) NOT Logical Test
43. (01:28:18) Context Transition in Calculated Columns
44. (01:32:18) Hidden CALCULATE in Measure
45. (01:34:26) Context Transition in Iterator like AVERAGEX. Calculate Average Monthly Sales.
46. (01:39:30) Context Transition and Filter Context
47. (01:41:03) Context Transition Error: Iterate Over Table with Duplicate Errors
48. (01:43:46) Context Transition: Correct Formulas and Incorrect Formulas
49. (01:46:23) Context Transition Error: Iterate over Fact Table
50. (01:47:28) Grain of Calculation & Iterator Functions for Transactional, Daily and Monthly Averages
51. (01:49:46) Average using DISTINCTCOUNT to make a faster formula
52. (01:51:49) Cardinality and Iterator Functions. See five examples of howto reduce cardinality and increase formula calculation speed
53. (01:57:47) DAX Studio to time formulas. EVALUATE Command.
54. (02:00:03) Complex Filter and Complex Filter Reduction Error (from Overwrite process): KEEPFILTERS or Data Modeling?
55. (02:02:37) KEEPFILTERS in Power BI Quick Measure
56. (02:03:58) 12 Month Moving Average DAX Measure: CALCULATE, AVERAGEX, DATESINPERIOD, IF, MAX Functions
57. (02:08:18) Table Filters in CALCULATE to go backwards across a Many-To-One Relationship
58. (02:11:28) Unmatched Items in a Relationship
59. (02:13:03) DAX Approximate Match Lookup
60. (02:17:43) DAX to create Date Tables in Power BI using GENERATE, ROW, CALENDAR and more
61. (02:19:44) Extract Data From Power Pivot Data Model using Existing Connections
62. (02:25:17) Query View in Power BI Desktop
63. (02:30:33) Video Summary and Conclusions
64. (02:31:20) Closing and Video Links
Song in video: Rock Intro 3 by Audionautix is licensed under a Creative Commons Attribution 4.0 license. creativecommons.org/licenses/by/4.0 . Artist: http://audionautix.com

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #datamodel #DAX

DAME 05: Master Power Query M Code & Become a Data Shaping Ninja (The Secret Behind Data Analysis)

excelisfun 2024-05-19 | DAME 05: Master Power Query M Code & Become a Data Shaping Ninja (The Secret Behind Data Analysis)
Download files: excelisfun.net/files/DAMEwithMPT05.zip, pdf notes: excelisfun.net/files/05-DAMEMPT.pdf
Alternative download links: Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/2024/Content/Week08/DAMEwithMPT05.zip
Pdf notes to read online: https://people.highline.edu/mgirvin/AllClasses/348/348/2024/Content/Week08/05-DAMEMPT.pdf
In this video learn about all the fundamentals of the M Code language, the coding language behind Power Query. Learn all about the keys to M Code Mastery: M Code Values, Expressions, Data Types, Operations by Data Types, let expression, M Code Lookup, Custom Functions, and M Code functions such as: Table.AddColumn, Csv.Documnet, Excel.CurrectWorkbook, Table.Group and much more!
Free YouTube Data Analysis Class about Microsoft Power Tools in 2024 taught by Excel MVP and Highline College Professor, Mike “excelisfun” Girvin.
Topics:
1. (00:00) Introduction
2. (00:32) Why M Code?
3. (02:32) Files to download and follow along
4. (03:20) Power Query Editor)
5. (04:00) 3 Places to edit M Code
6. (04:28) Introduction to let expression
7. (07:02) Define Expressions
8. (07:45) Introduction to the 15 M Code Values
9. (10:19) Data Types, Type value
10. (10:52) Operations and Data Types
11. (12:25) identify Expressions in a let expressions
12. (13:46) Change Data Type
13. (14:15) Group By and Table.Group function, first example. Why list within a list is so useful!
14. (16:00) Identifiers in M Code and why you never use spaces
15. (17:43) Hack Group By dialog box to make calculations not in dialog box
16. (19:10) Keywords
17. (19:50) Editing in Advanced Editor, including Shift + Enter
18. (20:30 Syntax for let expression
19. (21:38) All 15 M Code Values and Operators that are allowed for each M Code Value
20. (22:29) Null value
21. (23:48) Logical value and formulas
22. (24:28) Text value and formulas
23. (25:22) Number value and formulas
24. (25:52) Why it is important to use value type and not data type for determining whether an operation is valid.
25. (26:50) Relationship between Values and Data Types
26. (27:57) Colaesce operator or if expression when you have null values?
27. (30:20) Custom Column and Table.AddColumn function
28. (31:25) Time value and formulas
29. (32:46) Date value and formulas
30. (33:34) Date.AddDays function
31. (33:59) Duration value
32. (34:12) Duration.Days function
33. (34:31) Power Query Dates (1/1/0001 to 12/31/999) and how they Rule: many examples!!!
34. (38:41) Calculate hours worked through midnight. This is basis for custom function later in video
35. (40:23) Number.Round function vs. ROUNDDOWN vs. INT
36. (40:58) let expression to define variables in formulas
37. (43:26) Convert ISO Dates to serial number dates
38. (44:29) Using Locale feature: Convert dates and numbers from one locale (France) to another (United Sates)
39. (46:24) Duration.Days vs. Duration.TotalDays functions
40. (47:00) Datetime value and Datetimezone value
41. (47:44) Table, list, record values can hold more than one M Code value
42. (48:00) List value and formulas
43. (50:21) Aggregate functions require lists
44. (51:24) List to expand rows from improper data set with a range of years in cells
45. (54:12) Record value and formulas
46. (54:31) Generalized Identifiers
47. (55:14) Table value and formulas
48. (56:26) Binary value
49. (56:43) M Code lookup
50. (59:32) Row Index Lookup examples
51. (01:01:26) Key Match Lookup examples
52. (01:03:32) Excel.CurrectWorkbook function
53. (01:04:38) Primary Keys and lookup
54. (01:06:46) Lookup columns for aggregate functions
55. (01:07:43) Merge feature and Join Operations: Left Outer, Inner, and Left-Anti
56. (01:12:35) Function value: custom functions
57. (01:13:58) Hours worked custom function
58. (01:19:00) On Premine folder and file paths and Data Connections dialog box
59. (01:20:17) Fix and Append Text Files custom function
60. (01:25:00) Append tables with Table.ExpandColumns function
61. (01:25:37) Append tables with Table.Combine function
62. (01:26:30) each and underscore explained!
63. (01:32:30) Approximate Match custom function
64. (01:39:55) Table.Group function fourth argument: GroupKind
65. (01:42:40) Table.Group function fifth argument: Comparer as function
66. (01:48:05) Summary
67. (01:49:45) Conclusion

#mcode #powerquery #powerbi #powerbidesktop

DAME 04: Data Analysis with Excel Worksheet Formulas: 4 Comprehensive examples.

excelisfun 2024-05-05 | Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/2024/Content/Week06/Video04Files.zip
Alternative link for zipped folder: excelisfun.net/files/Video04Files.zip
Free YouTube Data Analysis Class about Microsoft Power Tools in 2024 taught by Excel MVP and Highline College Professor, Mike “excelisfun” Girvin.
In this video learn about when Excel worksheet formulas are the perfect tool for data analysis. An hour lesson in the power of Excel worksheet formulas and when they beat PivotTables, Power Query and Power BI.
Topics:
1. (00:00) Introduction
2. (00:40) 4 scenarios where worksheet formulas are better than other tools.
3. (02:33) Example #1: Enter Data In Worksheet & Need Analysis Off To Side.
4. (03:22) MOD function for hours worked formula
5. (05:19) Line Chart
6. (06:37) 7-Day Moving Average formula AVERAGE, IF, NA and ROWS functions
7. (11:12) TEXT function for day of the week formula
8. (13:00) Dynamic Spilled AVERAGEIFS function to calculate average hours worked per day
9. (14:08) Column chart
10. (15:11) Create Dynamic Spilled Array Single Cell Reporting Formula. Formula to create report that shows Total Hurs by Project Address
11. (16:36) LET function
12. (21:05) Array syntax
13. (22:17) HSTACK and VSTACK functions to join arrays
14. (23:42) Bar chart
15. (25:00) Conditional Formatting for Dynamic Spilled Array formulas
16. (26:53) Test Dynamic Spilled Array formulas with new data
17. (27:25) Example #2: GROUPBY function for single cell reporting. Basics.
18. (31:12) GROUPBY with two row conditions and two values columns
19. (33:23) Create an array syntax with F9 key
20. (34:08) Conditional formatting for GROUPBY with subtotals. Add bold to subtotal row and double-underline for grand total row
21. (36:08) GROUPBY with two functions
22. (37:09) DROP function
23. (37:50) PIVOTBY function
24. (41:02) Example #3: LAMBDA function to create re-usable reporting formula
25. (43:30) Text LAMBDA function
26. (44:28) TAKE function
27. (45:14) Load LAMBDA Function into Defined Name: Name Manager
28. (46:15) Test re-usable function
29. (47:03) Example #4: Build Lambda for Statistical Data Analysis to create a model
30. (51:21) Summary
31. (51:59) Conclusion

DAME 03: PivotTables Rule for Quick & Easy Reports! 34 Amazing PT Tricks.

excelisfun 2024-04-25 | Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/2024/Content/Week06/Video03Files.zip Pdf notes to read online: https://people.highline.edu/mgirvin/AllClasses/348/348/2024/Content/Week06/03-DAMEMPT.pdf
Alternative link for zipped folder: excelisfun.net/files/Video03Files.zip
Free YouTube Data Analysis Class about Microsoft Power Tools in 2024 taught by Excel MVP and Highline College Professor, Mike “excelisfun” Girvin.

This video teaches you all the tricks for a PivotTable. It shows examples of when the PivotTable is the best tool as compared to worksheet formulas, Power Query, Power Pivot and Power BI..

Topics:
1. (00:00) Introduction
2. (00:26) Free PivotTable Cheat Sheet in workbook file or pdf notes
3. (00:39) Toy sales Data Set
4. (01:21) Why use PivotTables?
5. (01:58) Basics of PivotTables
6. (02:35) PivotTable Cache
7. (04:02) Change Default PivotTable Layout
8. (04:40) PT Calculations: Summarize Values By, Show Values As and Calculated Fields
9. (05:53) Cross Tabulated Tables and the AND Logical Test
10. (06:23) What a Filter or Sliver does to PT calculations
11. (07:45) Name PivotTable
12. (09:00) Connect 2 PT to 1 Slicer
13. (09:15) Calculated Field
14. (11:07) Sort PT by Values
15. (11:30) Extract records from a PT cell
16. (12:06) Create PivotTable Styles
17. (13:37) What happens if you copy a PT?
18. (14:45) Group By Inconsistent Data Error
19. (15:38) Fix Text Dates with Hack in worksheet
20. (17:07) Group By Feature for 1) Integers Numbers or 2) Decimal Numbers
21. (20:56) Grouping persists in the PivotTable Cache
22. (21:27) Create New Grouping in new PivotTable Cache using 3-step PivotTable Wizard
23. (23:21) Modify PivotTable Styles
24. (23:42) Show Values As Calculations: % of Column Total, % of Parent Total, % of Row Parent Total
25. (26:05) Show Values As Calculations: Difference From and % Difference From
26. (27:18) Show Values As Calculations: Running Total, % Running Total
27. (29:00) Summarize Survey Data
28. (29:41) Create Cross Tabulated Report and Visual
29. (31:20) Create and Use a Joint Probability Table
30. (34:42) Load 7 million rows of data to PivotTable Cache to make simple Pivot Report
31. (37:01) Append csv files into PivotTable Cache using From Folder and the C
32. (42:22) Create 5 reports with a single click: Show Report Filter Pages feature
33. (43:17) Summary
34. (44:11) Conclusion

DAME 2 (Updated): MS Data Analysis Tools: Excel, Power Query, Power BI, Data Flow, DAX & M Code!

excelisfun 2024-04-24 | Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/2024/Content/Week01a02/Video01-02Files.zip
Pdf notes to read online: https://people.highline.edu/mgirvin/AllClasses/348/348/2024/Content/Week01a02/01-DAMEMPT.pdf
Alternative link for zipped folder: excelisfun.net/files/Video01-02Files.zip
Free YouTube Data Analysis Class about Microsoft Power Tools in 2024 taught by Excel MVP and Highline College Professor, Mike “excelisfun” Girvin.
In this video learn about ALL the Microsoft Power Tools: Excel, Power Query, Power Pivot, Data Model, Power BI, Dataflow, Worksheet formulas, Dynamic Spilled Array Formulas, M Code formulas, DAX formulas, and much more!
Topics:
1. (00:00) Introduction
2. (00:13) List of MS Power Tools taught in video
3. (00:44) Preview of 7 examples
4. (01:48) Why use Worksheet as a data analysis solution?
5. (03:09) #1 Worksheet Formula solutions: XLOOKUP to do data modeling , PivotTables, dynamic spilled array formulas, and the GROUPBY and PIVOTBY functions
6. (22:41) #2: Power Query to import data (.txt & .xlsx), transform the data create a report.
7. (34:49) #3: Use Power Query to load a table to the PivotTable Cache.
8. (41:07) Fix broken On Premises File Path: Data Source Settings.
9. (43:02) #4: Json data, Power Pivot, Data Model, DAX formulas, Row Context and Filter Context for creating Data Model PivotTable Reports.
10. (01:09:12) #5 Xml data, Power BI Desktop, Data Model, DAX formulas and Interactive Visuals. Visualization tips too.
11. (01:33:58) #6 Power BI Online Services
12. (01:38:57) #7 Dataflow (Online Power Query)
13. (01:45:25) Summary
14. (01:45:55) Conclusion

DAME 01: Data Analysis Terms & the Awesome Microsoft Power Tools!

excelisfun 2024-04-08 | DAME 01: Data Analysis Terms & the Awesome Microsoft Power Tools!
Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/2024/Content/Week01a02/Video01-02Files.zip
Pdf notes to read online: https://people.highline.edu/mgirvin/AllClasses/348/348/2024/Content/Week01a02/01-DAMEMPT.pdf
Alternative link for zipped folder: excelisfun.net/files/Video01-02Files.zip

Free YouTube Data Analysis Class about Microsoft Power Tools in 2024 taught my Excel MVP and Highline College Professor, Mike “excelisfun” Girvin.
In this video learn about data analysis terms and all the amazing Microsoft Power Tools such as: Excel, Power Query, Power Pivot, Power BI, Dataflow, Worksheet formulas, Dynamic Spilled Array Formulas, M Code formulas, DAX formulas, and much more!
Topics:
1. (00:00) DAME Intro Song
2. (00:12) Introduction
3. (01:18) Table terminology
4. (04:52) What is Grain?
5. (06:41) SQL database as a data source
6. (07:55) Columnar Database in the Data Model in Power Pivot and Power BI
7. (08:57) The many types of source data
8. (09:37) On-premises or Online data source?
9. (10:44) Structure or Schema of tables, files and databases
10. (11:23) Csv File
11. (11:34) Txt File
12. (11:48) Xml File
13. (12:11) Json File
14. (12:36) Excel file
15. (13:03) Define Data Analysis and Business Intelligence
16. (13:49) Steps in data analysis process
17. (14:39) Examples of data analysis and end result.
18. (15:23) Data modeling defined
19. (15:43) Data cleaning and transforming
20. (16:03) Star Schema data model
21. (16:30) Steps to build data model
22. (16:51) Define reports, visuals, dashboards
23. (17:10) Dashboard terminology by Microsoft confusing?
24. (17:37) Excel and worksheet formulas
25. (18:33) Power Query and M Code formulas
26. (19:24) PivotTable as functional language
27. (19:59) Data model and DAX formulas
28. (20:48) Look at projects that we will complete in next video.
29. (23:09) Summary
30. (23:21) Conclusion

#data #dataanalysis #dataanalytics #freeclass #freeclasses #freecourse #microsoftexcel #microsoftmvp #microsoft #powerquery #worksheet #datamodel #datamodelling #powerbi #powerbidesktop #dataflow

Why Are So Many Decimal Showing In Excel? Answer Student’s Question About Number Formatting

excelisfun 2024-04-06 | Mike excelisfun answers students question about number formatting for the Highline College Busn 210 – Excel and Statistics Class.

Spring 2024 Data Analysis With Microsoft Power Tools - BI 348 at Highline College

excelisfun 2024-03-31 | Spring 2024 Data Analysis With Microsoft Power Tools - BI 348 at Highline College
Data Analysis free class YouTube playlist link: studio.youtube.com/playlist/PLrRPvpgDmw0lAIQ6DPvSe_hfAraNhTvS4/videos
First Video out on April 5.

Spring 2024 Business Excel Statistical Analysis - Busn 210 at Highline College

excelisfun 2024-03-31 | Spring 2024 Business Excel Statistical Analysis - Busn 210 at Highline College
Free YouTube Stats and Excel class playlist: youtube.com/playlist?list=PLrRPvpgDmw0m3oqpp1XcPuaxyM4Bpi0dN

PIVOTBY & GROUPBY to Create Year by Month Sales Report. Grouping Trick! Excel Magic Trick 1859

excelisfun 2024-03-25 | Download Excel File: excelisfun.net/files/EMT1857-1858.xlsx
Learn how to group in the GROUPBY and PIVOTBY functions to create Year by Month Sales Reports.
Topics:
1. (00:00) Introduction
2. (00:21) Shout Out to Excel Instructor: Radosław Poprawski at youtube.com/channel/UCVOcrn3Orn-aC0q1qGyQQmQ
3. (00:33) EOMONTH column, YEAR column inside HSTACK
4. (01:31) Custom Number Format for month
5. (01:42) GROUPBY
6. (02:17)PIVOTBY
7. (02:39) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp

Grouping in GROUPBY & PIVOTBY Functions!?!? Yes!!!! Frequency Distribution with GROUPBY EMT 1858

excelisfun 2024-03-18 | Download Excel File: excelisfun.net/files/EMT1857-1858.xlsx
Learn how to group in the GROUPBY and PIVOTBY functions to create Frequency Distributions.
Topics:
1. (00:00) Introduction to Frequency Distribution
2. (00:38) Start, Increment and Last Value for frequency distribution using MAX and CEILING.MATH functions
3. (01:12) Build Bins using SEQUENCE function
4. (01:42) Build GROUPBY Formula using LET and GROUPBY
5. (02:37) Look at PIVOTBY Formula
6. (02:43) Understanding Categories
7. (03:00) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #groupby #pivotby

Generate Random Data Sets with Dates & Sales Numbers: RANDARRAY Function. Excel Magic Trick 1857

excelisfun 2024-03-11 | Download Excel File: excelisfun.net/files/EMT1857-1858.xlsx
Learn how to create a data set with dates and sales numbers using an Excel Formula.
Topics:
1. (00:00) Create Inputs for Randon Data Set
2. (00:37) Create Random Dates using RANDARRAY function
3. (01:21) Create Random Sales amounts using RANDARRAY & ROUND function
4. (02:00) Paste Special Values Trick!
5. (03:00) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #randomdata

Power Query Table.Group Function Fifth Argument: Comparer Function: Group without Key. EMT 1856

excelisfun 2024-03-04 | Download Excel File: excelisfun.net/files/EMT1854-1856.xlsx
Learn how to group transactions with no key or invoice number using Power Query and the Table.Group fourth and fifth arguments: groupKind and comparer function. Group By when there is no key column and there are duplicates and empty cells. How to group transactions with no transaction number and repeating dates and empty cells.
Topics:
1. (00:00) Introduction
2. (00:28) Group By Date Column with two aggregate calculations: join descriptions and sum amount.
3. (01:00) Text.Combine with Column Lookup to join descriptions
4. (02:10) Table.Group fourth argument: groupKind using GroupKing.Local or zero, 0
5. (02:56) Table.Group fifth argument: comparer function
6. (05:29) Summary
7. (05:43) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp
Examples of Table.Group fourth argument, groupKind argument
Examples of Table.Group fifth argument, Comparer argument
Table.Group fourth argument
Table.Group fifth argument
Table.Group groupKind argument
Table.Group comparer argument

GROUPBY Function to Group Transactions with No Transaction Number. EMT 1855

excelisfun 2024-02-29 | Download Excel File: excelisfun.net/files/EMT1854-1856.xlsx
Learn how to group transactions with no key or invoice number using dynamic spilled array formulas. Group By when there is no key column and there are duplicates and empty cells. How to group transactions with no transaction number and repeating dates and empty cells.
Topics:
1. (00:00) Introduction
2. (00:27) Teammates!
3. (00:38) Create Unique Identifier with SCAN
4. (03:06) GROUPBY Function
5. (03:28) HSTACK, SUM and ARRAYTOTEXT
6. (04:44) DROP Function to finish report.
7. (05:29) Summary
8. (05:45) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #reporting #groupby #groupbyfunction #PivotBy #pivottable #pivot

Worksheet Formulas to Group Transactions with No Transaction Number. Magic Trick 1854

excelisfun 2024-02-26 | Download Excel File: excelisfun.net/files/EMT1854-1856.xlsx
Learn how to group transactions with no key or invoice number using worksheet formulas. Group By when there is no key column and there are duplicates and empty cells. How to group transactions with no transaction number and repeating dates and empty cells.
Topics:
1. (00:00) Introduction
2. (00:31) Key Column
3. (01:35) SEQUENCE function 1 to 5
4. (01:43) FILTER function to get dates
5. (01:59) FILTER and TEXTJOIN functions to get descriptions
6. (02:40) SUMIFS to add amounts based on key column
7. (03:03) Summary
8. (03:11) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp, #datatransformation

Formula for Sales Rep by Month Report: The Amazing GROUPBY Function! Excel Magic Trick 1853

excelisfun 2024-02-22 | Download Excel File: excelisfun.net/files/EMT1853.xlsx
Learn how easy it is to take daily dates and roll them up into a monthly sales rep report with GROUPBY and EOMONTH functions!
Topics:
1. (00:00) Introduction
2. (00:19) Sales Rep by Month Report with EOMONTH & GROUPBY & HSATCK Functions.
3. (01;52) Summary
4. (02:00) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp, #groupby #lambda #etalambda #eomonth #monthlyreport

Append Tables in Excel. Excel Magic Trick 1852

excelisfun 2024-02-19 | Download Excel File: excelisfun.net/files/EMT1852.xlsx
Learn the old way and the new way to append tables in Excel.
Topics:
1. (00:00) Introduction
2. (00:10) Old Method
3. (00:17) New Method with VSTACK
4. (00:37) Summary
5. (00:53) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #VSTACK

Excelisfun: 16 Years at YouTube: SQRT(256) Birthday Party!!!! Excel Magic Trick 1851

excelisfun 2024-02-12 | Download Excel File: excelisfun.net/files/EMT1851.xlsx
Celebrate Mike Girvin’s excelisfun 16 Years at YouTube Birthday Party!!! Learn Power Query M Code tricks too.
Topics:
1. (00:00) Introduction
2. (00:01) Thanks you for 16 years!!!!
3. (00:25) Alphanumeric Min and Max. List.Min on Text items, Words!?!?! Yes!
4. (01:37) Group By Can Insensitive!?!? Yes!!! With Table.GroupBy function
5. (02:32) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #powerquery, #mcode
Comparer.Ordinal, Comparer.OrdinalIgnoreCase

Don’t Use PivotTables. Use COUNTIFS or FREQUNCY Functions Instead. Excel Magic Trick 1849

excelisfun 2024-02-05 | Download Excel File: excelisfun.net/files/EMT1847-1849.xlsx
Learn about how PivotTables cannot include upper limit when grouping number data with decimals. COUNTIFS and FREQUNCY Functions do include upper limits when grouping numbers into upper and lower limit categories.
Topics:
1. (00:00) Introduction
2. (00:06) PivotTable
3. (01:45) COUNTIFS
4. (02:49) Text Labels for Formula Report
5. (03:25) Find Feature to create labels for PivotTable
6. (03:56) FREQUENCY Array Function
7. (05:15) Summary
8. (05:30) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #lookup #xlookup

Count Below Hurdle: PivotTable, COUNTIFS, GROUPBY or Power Query? Magic Trick 1848

excelisfun 2024-01-29 | Download Excel File: excelisfun.net/files/EMT1847-1849.xlsx
Learn about how to count assembly line station times that are under 10 seconds using PivotTable, COUNTIFS function, GROUPBY function and Power Query.
Topics:
1. (00:00) Introduction
2. (00:06) Counting Assembly Line Post Times Less Than 10 Seconds
3. (00:43) COUNTIFS
4. (01:54) PivotTable with Helper Column
5. (02:57) GROUPBY Array Function
6. (04:05) Power Query
7. (05:22) Summary
8. (05:35) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #countifs #groupby #pivottable #powerquery #powerquerytutorial

Check First Letter in Each Name Against a List of Letters, Then Count. Excel Magic Trick 1850

excelisfun 2024-01-23 | Download Excel File: excelisfun.net/files/EMT1850.xlsx
Learn about how to count how many names in a column start with the letters a, b or c. Then make formula dynamic so it can check any set of letters.
Topics:
1. (00:00) Introduction
2. (00:05) Formula that uses LEFT, an array constant {“a”,”b”,”c”} and the SUM function
3. (00:38) Why the two arrays must be in opposite directions
4. (02:22) Dynamic formula linked to a list in the cells.
5. (02:43) Sumamry, Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #count #left #excelfunctions #countifs

Filter Feature CANNOT run an OR Logical Test Across Two Columns: 2 Solutions. Excel Magic Trick 1847

excelisfun 2024-01-19 | Download Excel File: excelisfun.net/files/EMT1847-1849.xlsx
Learn that the Filter Feature CANNOT run an OR Logical Test Across Two Columns. Solution #1: Add a Helper Column with the OR function. Solution #2: Use the FILTER Array Function. Lean about logical tests in the FILTER function.
Topics:
1. (00:00) Introduction
2. (00:06) Filter Feature CANNOT run an OR Logical Test Across Two Columns
3. (01:03) Helper Column with OR Function to help the Filter Feature
4. (02:01) FILTER Array Function with OR Logical Test
5. (04:06) Summary
6. (04:20) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #filter #filterfunction

Excel LAMBDA, LAMBDA Helper Functions & Eta-LAMBDAs: the Complete Story – Updated 2024: 365 MECS 10

excelisfun 2024-01-03 | Download Excel Start file: excelisfun.net/files/10-M365ExcelClassStart2024.xlsx
Download pdf notes: excelisfun.net/files/10-M365ExcelClass2024.pdf
Download Excel Finished file: excelisfun.net/files/10-M365ExcelClassFinished2024.xlsx
This is a 1:45 hour video about everything that the Excel LAMBDA function, LAMBDA Helper Functions & Eta-LAMBDAs can do. This video is also a complete lesson in Defined Names, the LET function and Spilled Single Cell Formula Reports.
Course taught by Excel MVP and Highline College Professor, Mike Girvin. Course is Microsoft 365 Excel Complete Story.
Topics in video:
1. (00:00) Introduction
2. (01:18) Overview
3. (03:09) Defined Names
4. (13:00) First look at LAMBDA to create a re-usable function
5. (19:25) Advanced Formula Environment
6. (24:08) Summary of LAMBDA
7. (25:50) Rate Of Change Re-usable LAMBDA function
8. (28:33) COGS Re-usable LAMBDA function created in Advanced Formula Environment
9. (30:55) Save LAMBDA to default file
10. (32:44) Show Formula Re-usable LAMBDA function
11. (36:35) LAMBDA Helper Functions
12. (37:24) BYROW and BYCOL functions
13. (39:41) Eta-LAMBDAs
14. (42:16) MAP function
15. (50:46) SCAN function
16. (54:01) REDUCE function, initial look
17. (55:13) Recursion with LAMBDA
18. (01:04:24) REDUCE function, introduction and three examples
19. (01:14:48) MAKEARRAY function
20. (01:16:34) LET function
21. (01:17:46) GROUPBY and PIVOTBY functions
22. (01:22:29) Add calculation label to Single Cell Formula Report: 2 methods
23. (01:24:00) Single Cell Formula Report with two conditions in row area
24. (01:25:53) Conditional Formatting For Dynamic Reports
25. (01:28:25) Add two different calculations to a Single Cell Report
26. (01:31:10) PIVOTBY function
27. (01:33:05) PIVOTBY function to analyze increases and decreases in sales
28. (01:35:02) Goal Seek with the PIVOTBY function
29. (01:36:08) Fully Dynamic PIVOTBY report using formula inputs from cells using the functions: XLOOKUP, CHOOSE and XMATCH.
30. (01:40:15) CHOOSE and XMATCH functions to lookup a function
31. (01:41:53) Add dynamic calculation label to report using LET, ROWS, COLUMNS and SEQUENCE functions
32. (01:45:13) Finance and Statistics LAMBDA re-usable function examples
33. (01:45:52) Homework
34. (01:45:55) Summary and Conclusions
35. (01:46:41) Closing and Video Links

Song in video: Rock Intro 3 by Audionautix is licensed under a Creative Commons Attribution 4.0 license. creativecommons.org/licenses/by/4.0 . Artist: http://audionautix.com

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #groupby #excelformula #excelfunctions #excelfunctions #excelformulasandfunctions #lambda #excellambda #lambdahelper #etalambda

Excel PIVOTBY Function to Create Completely Dynamic PivotTable Formula Reports! EMT 1845

excelisfun 2023-11-20 | Download Excel File: excelisfun.net/files/EMT1845.xlsx
You will not believe what you see in this video. See the basics of the new PIVOTBY function to make PivotTable Formula Reports, then see how to create monthly sales reports (with a hack), then see a completely dynamic Formula PivotTable Report where you can use cell inputs to change, Row, Column and Filter Conditions, Change the Function for the report and create a dynamic label that describes the report. Simply Amazing!!!
Topics:
1. (00:00) Introduction.
2. (00:22) PIVOTBY function, complete description and examples.
3. (05:50) Monthly Sales Report with PIVOTBY function.
4. (08:11) Fully dynamic PivotTable report with cell inputs for the criteria and function in the report.
5. (18:57) Summary.
6. (19:23) Closing, Video Links.

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #excelformula #excelfunctions #excelfunctions #excelformulasandfunctions #lambda #pivot #pivotby #groupby #pivottable #pivotables

ETA-LAMBDAs for BYROW, BYCOL & SCAN to Make Spilling Row Totals & Running Totals Easy. EMT 1846

excelisfun 2023-11-19 | Download Excel File: excelisfun.net/files/EMT1846.xlsx
Learn about how to use ETA-LAMBDAs to replace LAMBDA to make easier formulas to spill row totals, spill column totals, spill running totals and even spilling an average in each row.
Topics:
1. (00:00) Introduction.
2. (00:18) Spill Row Totals with the BYROW function.
3. (00:50) Spill Column Totals with the BYCOL function.
4. (01:06) Spill a Running Total with the SACN function.
5. (01:32) Spill an Average for each row with the BYROW function.
6. (01:53) Summary.
7. (02:12) Closing, Video Links For Full Free LAMBDA Class.

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #lambda #excellambda #byrow #bycol #scan #spillingthemilk #array #arrayformula

2 Different Functions in GROUPBY & PIVOTBY Functions. Excel Magic Trick 1844 Part 2

excelisfun 2023-11-18 | Download Excel File: excelisfun.net/files/EMT1844-Part02.xlsx
Learn about how to use the GROUPBY and PIVOTBY functions to make single cell formula reports that have 2 functions in a single report.
Topics:
1. (00:00) Introduction to GROUPBY, PIVOTBY and the new Lambda Replacement Functions.
2. (00:15) GROUPBY Function.
3. (01:08) PIVOTBY Function.
4. (01:36) Confusing Labels in Report.
5. (01:49) Two Fields in the Row Area.
6. (02:01) Two Different Functions with Two Fields in Row Area: Labels are correct!
7. (02:32) Summary.
8. (02:55) Closing, Video Links.
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #groupby #excelformula #excelfunctions #excelfunctions #excelformulasandfunctions #lambda #pivotby

GROUPBY Excel Worksheet Function. Single Cell Reports Made Easy! 11 Examples. EMT 1844

excelisfun 2023-11-18 | Download Excel File: excelisfun.net/files/EMT1844.xlsx
Learn about how to use the GROUPBY function to make single cell formula reports that can update instantly when source data changes.
Topics:
1. (00:00) Introduction to GROUPBY, PIVOTBY and the new Lambda Replacement Functions
2. (00:58) Compare arguments in GROUPBY and PIVOTBY functions.
3. (01:58) GROUPBY and the 7 arguments.
4. (01:58) 16 New Lambda Replacement Functions.
5. (05:43) New Lambda Replacement Functions as replacement for LAMBDA.
6. (06:17) PERCENTAGE of in GROUPBY Function.
7. (07:04) Why use formulas rather than PivotTables.
8. (07:17) Two Fields in Row Area of Report.
9. (08:05) Problem: No Label for Calculation Column. Look at field_header argument.
10. (08:21) Two Columns of calculations in report.
11. (08:55) Add Custom Header Labels to Report with VSTCK Function and Array Constant.
12. (10:26) Subtotals
13. (11:00) Conditional Formatting for dynamic Report.
14. (13:25) F5 Go To Trick to find Conditional Formatting in Report.
15. (14:44) How to create multiple columns with different calculations with GROUPBY, LET, DROP and TAKE Functions.
16. (18:04) Filter GROUPBY Report with Contains Criteria.
17. (19:17) Filter GROUPBY Report with Criteria from a list.
18. (21:18) Using LAMBDA in the GROUPBY Function: two examples.
19. (23:25) Creating Array Calculation in values argument of GROUPBY Function.
20. (24:06) Create Fully Dynamic GROUPBY Report with Formulas Inputs from Worksheet Cells.
21. (26:55) Summary
22. (28:12) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #conditionalformat #conditionalformatting #subtotal #groupby #excelformula #excelfunctions #excelfunctions #excelformulasandfunctions #lambda

FILTER Function to Extract Records by Area Code. Excel Magic Trick 1843

excelisfun 2023-11-01 | Download Excel File: excelisfun.net/files/EMT1843.xlsx
Learn about how to use FILTER and SEARCH to filter a data set by area code.
Topics:
1. (00:00) Introduction
2. (00:05) FILTER and SEARCH functions to filter by Area Code.
3. (02:04) Summary
4. (02:13) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #lookup #xlookup #Filter #Search #filterfunction #filterfun

FILTER Function to Create Sub Table from Master Table. Data Validation Too! Excel Magic Trick 1842

excelisfun 2023-10-25 | Download Excel File: excelisfun.net/files/EMT1842.xlsx
Learn about how to use the FILTER function to create sub tables based on a master table. Also learn how to create a unique sorted list of city names for data validation. Learn how to create Conditional Formatting for Dynamic Spilled Arrays.
Topics:
1. (00:00) Introduction
2. (00:05) Filter data set by city name
3. (00:35) UNIQUE and SORT functions to extract a sorted unique list
4. (01:05) Data Validation
5. (01:42) FILTER Function
6. (02:29) Conditional Formatting for Dynamic Spilled Arrays
7. (03:13) Summary
8. (03:23) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #conditionalFormat #uniquefunction #sortfunction #filterfunction

FILTER Function or Advanced Filter to Extract Records with Complex Filter? Excel Magic Trick 1841

excelisfun 2023-10-18 | Learn about how to use FILTER function with a complex filter based on a BETWEEN, AND and OR Logical Tests. Chapter 13 in the book, The Only App That Matters. Learn about Advanced Filter also.
Download Excel File: excelisfun.net/files/EMT1841.xlsx
Topics:
1. (00:00) Introduction
2. (00:05) Complex Filter
3. (00:30) Logical Tests
4. (02:00) Filter Function
5. (06:02) Advanced Filter)
6. (07:54) Summary
7. (08:20) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #lookup #xlookup #filter #filterfunction #logical #advancedfilter

Power Query: Extract City Name from Description with List.Accumulate function Excel Magic Trick 1840

excelisfun 2023-10-11 | Download Excel File: excelisfun.net/files/EMT1840.xlsx
Learn about how to extract the city name from a description using Power Query with the List.Accumulate function and a Custom Function.
Topics:
1. (00:00) Introduction
2. (00:05) What we did last video
3. (00:38)) Excel Worksheet REDUCE function to help visualize what happens in Power Query
4. (02:54) Power Query Finished Formula List.Accumulate and Custom Function
5. (04:01) Build List.Accumulate and Custom Function formula
6. (06:14) Formula for more than one city in description
7. (07:11) Summary
8. (07:33) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #lookup #xlookup #powerquery #poowerbi #extraction #records #clean #datacleaning #datacleansing

Excel Formula: Extract City Name from Description. Excel Magic Trick 1839

excelisfun 2023-10-04 | Download Excel File: excelisfun.net/files/EMT1839.xlsx
Learn about how to extract the city name from a description using the SEARCH and LOOKUP functions. Then see how to spill the formula with BYROW and LAMBDA functions.
Buy excelisfun’s book: The Only App That Matters:
Amazon: amazon.com/Microsoft-365-Excel-Calculations-Analytics/dp/1615470700
Mr Excel Web Site: mrexcel.com/products/microsoft-365-excel-the-only-app-that-matters

Topics:
1. (00:00) Introduction
2. (00:05) SEARCH and LOOKUP functions
3. (02:39) BYROW and LAMBDA functions
4. (03:43) Summary
5. (03:52) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #lookup #BYROW #LAMBDA #dataanalysis