excelisfun
excelisfunVideo
- Format:
- YouTube channel
- New videos:
- Irregular
- Typical length:
- about 15 minutes, not counting Shorts
- Latest video:
About this show
Mike Girvin: 18 Year Excel & Power BI YouTuber, 23 years as professor at Highline College, 13 years as an Excel and Power BI author (5 books), and 10 years running an award-winning boomerang manufacturing company (Gel Boomerangs) with sports boomerang sales all over the world. Here at my excelisfun YouTube Channel, I provide free education for the world, with over 3,800 videos, over 100 playlists free classes, over 10,000 downloadable Excel example files, and over 2,000 free pdf notes files. Data analysis, accounting, finance and statistics are my specialty. Hopefully these free Excel & Power BI resources will help you to become better with Excel, Power BI and data analysis so you can save time at work, help coworkers become better, and save so much time that you can take an extra vacation! This is all free, so pass the word along to all who you know: excelisfun is here to help! Go Team! P.S. if you want to Donate, use PayPal link below, or join my YouTube channel : )
Videos come from the channel's public YouTube feed. The artwork is the newest video thumbnail. Last updated . Names, artwork and text belong to their publishers.
Cosmos
Similar by topic and creator
- Neso Academy
Shares the topics Education and Technology. - Gate Smashers
Shares the topics Education and Technology. - Simplilearn
Shares the topics Education and Technology.
Videos
Power Query Nested Functions in Custom Column: Convert Multiple Csv Files into One Table. EMT 1918
· 15 min
Download: 1) Excel File: https://excelisfun.net/files/EMT1917-1919.xlsx 2) Zipped Data Folder: https://excelisfun.net/files/SeattleAirportData.zip Buy excelisfun’s Power Query M Code book: https://www.mrexcel.com/products/the-transformative-magic-of-power-query-m-code-in-excel-and-power-bi/ Buy excelisfun’s other Excel Books: https://www.amazon.com/stores/Mike-excelisfun-Girvin/author/B009Q5T4P2?ref=ap_rdr&ccs_id=6f8ce877-462f-447e-b825-7ede1f72a9d3 Free Excel Class from excelisfun: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo Learn how to import multiple Csv files that each have a cross tabulated data structure and build a Custom Column formula with Nested Functions to convert each table to a proper table and then append them all into a single table. Learn a lot about M Code in this video. Topics: 1. (00:00) Introduction. 2. (00:23) Preview of videos 1917, 1918, and 1919! 3. (00:47) excelisfun’s Power Query M Code Book. 4. (01:15) The problem with storing data in a cross tabulated table. 5. (01:59) Goal of video and look at files we are working with. 6. (02:34) From Folder feature to import multiple csv files. 7. (03:08) Add Custom Column and build formula step by step. 8. (03:52) Csv.Document Power Query M Code function. 9. (05:08) Table.Transpose Power Query M Code function. 10. (05:33) Tip about IntelliSense and accepting function names from the dropdown list: type all lowercase with no spaces and no periods. 11. (06:03) Table.PromoteHeaders Power Query M Code function. 12. (06:30) Table.RenameColumns Power Query M Code function. 13. (07:59) Table.UnpivotOtherColumns Power Query M Code function. 14. (09:14) Table.SelectRows Power Query M Code function. 15. (10:31) Table.TransformColumnTypes Power Query M Code function. 16. (10:35) Tip to help remember M Code: add step, copy M Code, delete step, use M Code in a different step. 17. (11:43) Finish step that uses the Table.TransformColumnTypes Power Query M Code function. 18. (12:46) Lookup a column from a table and return as a list. Syntax = Table[ColumnName] = content of column as a list. 19. (13:17) Table.Combine Power Query M Code function. 20. (13:32) Summary 21. (13:45) Load finished table. 22. (13:57) Test formula by adding new files to the folder. 23. (14:57) Fun with Mega-Nested M Code Formulas 24. (15:04) Closing #excel #microsoft #excelisfun #excelcourse #mikegirvin #excel365 #powerquery #customfunction #powerqueryfunction #powerquerycustomfunction #importmultiplefiles #letexpression #letstatement #mcode #powerquerymcode #importdata #exceldataanalysis #powerbidataanalysis This video has subtitles or audio translations in these languages: Afrikaans, Amharic, Arabic (experimental), Bangla (experimental), Bosnian, Bulgarian, Cantonese, Chinese, Croatian, Czech, Danish, Dutch, Dutch (Netherlands), Estonian, Filipino, Finnish, French, French (France), Georgian, German, German (Germany), Greek, Hebrew (experimental), Hindi, Icelandic, Indonesian, Irish, Italian, Japanese, Khmer, Korean (experimental), Latvian, Lithuanian, Luxembourgish, Macedonian, Malagasy, Malay, Malayalam, Mongolian, Nepali, Norwegian, Pashto, Persian, Persian (Afghanistan), Persian (Iran), Polish, Portuguese, Portuguese (Brazil), Punjabi (experimental), Russian (experimental), Sanskrit, Scottish Gaelic, Serbian, Sicilian, Slovak, Slovenian, Somali, Spanish, Spanish (Latin America), Spanish (Mexico), Spanish (Spain), Spanish (United States), Swahili, Swedish, Tajik, Tamil (experimental), Tatar, Telugu (experimental), Thai, Tibetan, Turkish, Turkmen, Ukrainian, Urdu, Uzbek, Vietnamese, Welsh, Zulu. Click gear icon below video to set subtitles, audio and language.
Not rated yetBuild Power Query Custom Function: Convert Multiple Csv Cross Tab Tables into Single Table EMT 1917.
· 18 min
Download: 1) Excel File: https://excelisfun.net/files/EMT1917-1919.xlsx 2) Zipped Data Folder: https://excelisfun.net/files/SeattleAirportData.zip Buy excelisfun’s Power Query M Code book: https://www.mrexcel.com/products/the-transformative-magic-of-power-query-m-code-in-excel-and-power-bi/ Buy excelisfun’s other Excel Books: https://www.amazon.com/stores/Mike-excelisfun-Girvin/author/B009Q5T4P2?ref=ap_rdr&ccs_id=6f8ce877-462f-447e-b825-7ede1f72a9d3 Free Excel Class from excelisfun: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo Learn how to import multiple Csv files that each have a cross tabulated data structure and build a Custom Power Query Function to convert each table to a proper table and then append them all into a single table. Learn a lot about M Code to build Custom Functions. Topics: 1. (00:00) Introduction. 2. (00:28) Preview of videos 1917, 1918, and 1919! 3. (01:03) excelisfun’s Power Query M Code Book. 4. (01:26) Goal of Video. 5. (01:38) Preview files used in video. Structure of data files. 6. (02:26) The ways to import, transform and append cross tabulated cdv files. The three methods will be taught in videos 1917, 1918, 1919. 7. (03:00) Open Queries and Connections. 8. (03:15) From Folder feature to import multiple Csv files from Folder. 9. (04:11) On-Premise Folder Path. 10. (04:55) Preview of “Hack” method, or “Manual Method” of building code for the Custom Function in the query that uses the Custom Function. 11. (05:09) Extract single Csv file and create code for Custom Function. 12. (06:10) Keyboard shortcut to name columns in a Power Query Editor Table from dmclean3324 at YouTube. 13. (07:10) Notice that in last video Power Query interpreted empty cells in an Excel Table as null values, but Power Query interpreted empty cells in a Csv file as blank (zero length text string) values. 14. (08:01) Advanced Editor. 15. (08:17) What is a let expression? 16. (09:45) Create a note in the Advanced Editor. 17. (10:34) Cut M Code from first Query and paste it into a new query. 18. (11:42) Create a Custom Function. What is a Custom Function? 19. (14:13) Invoke Custom Function in table with Csv files. 20. (15:10) Append or Combine tables into single table. 21. (16:34) Summary of import, build Custom Function, Invoke Function and Append. 22. (17:00) Load new table to Worksheet. 23. (17:11) Add new Csv files to folder and refresh: AND everything updates with new data!! 24. (17:57) Summary 25. (18:05) Closing #excel #microsoft #excelisfun #excelcourse #mikegirvin #excel365 #powerquery #customfunction #powerqueryfunction #powerquerycustomfunction #importmultiplefiles #letexpression #letstatement #mcode #powerquerymcode #importdata #exceldataanalysis #powerbidataanalysis This video has subtitles or audio translations in these languages: Abkhazian, Afar, Afrikaans, Akan, Albanian, Amharic, Arabic (experimental), Armenian, Assamese, Aymara, Azerbaijani, Bambara, Bangla (experimental), Bangla (India), Bashkir, Basque, Belarusian, Bhojpuri, Bosnian, Breton, Bulgarian, Burmese, Cantonese, Cantonese (Hong Kong), Catalan, Chinese, Chinese (China), Chinese (Hong Kong), Chinese (Simplified), Chinese (Singapore), Chinese (Taiwan), Chinese (Traditional), Corsican, Croatian, Czech, Danish, Dogri, Dutch, Dutch (Belgium), Dutch (Netherlands), Dzongkha, English, English (Australia), English (Canada), English (India), English (Ireland), English (United Kingdom), Esperanto, Estonian, Ewe, Faroese, Fijian, Filipino, Finnish, French, French (Belgium), French (Canada), French (France), French (Switzerland), Fula, Galician, Ganda, Georgian, German, German (Austria), German (Germany), German (Switzerland), Greek, Guarani, Gujarati, Haitian Creole, Hausa, Hawaiian, Hebrew (experimental), Hindi, Hungarian, Icelandic, Igbo, Indonesian, Inuktitut, Irish, Italian, Japanese, Javanese, Kalaallisut, Kannada, Kazakh, Khmer, Kinyarwanda, Konkani, Korean (experimental), Kurdish, Kyrgyz, Lao, Latin, Latvian, Lingala,…
Not rated yetPart 2: Power Query Unpivot Cross Tab Table with 2 Column Variables. An Easier Way! EMT 1916 Part 02
· 9 min
Download Excel File: https://excelisfun.net/files/EMT1916Part02.xlsx Buy excelisfun’s Power Query M Code book: https://www.mrexcel.com/products/the-transformative-magic-of-power-query-m-code-in-excel-and-power-bi/ Buy excelisfun’s other Excel Books: https://www.amazon.com/stores/Mike-excelisfun-Girvin/author/B009Q5T4P2?ref=ap_rdr&ccs_id=6f8ce877-462f-447e-b825-7ede1f72a9d3 Free Excel Class from excelisfun: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo Learn how to convert bad data (cross tabulated table with two column variables and one row variable) into a proper data set for easier analysis using Power Query. This solution comes from our Online Excel Teammate at YouTube: Roger Stocker. Thanks : ) Go Team!!! Topics: 1. (00:00) Introduction. 2. (00:05) Fundamental problem of a Cross Tab Table. 3. (00:39) Amazing Online Excel Team!! Roger and Geert help our Team!!!! 4. (01:16) New Power Query book by Mike excelisfun Girvin 5. (01:37) Convert worksheet cross tabulated table into an Excel Table BEFORE bringing into the Power Query Editor. 6. (01:51) Use keyboard to convert data to an Excel Table. 7. (02:16) Use keyboard shortcut to bring Excel Table into the Power Query Editor. 8. (02:49) Name Query. 9. (03:14) Transpose table for first time. 10. (03:28) Rename Steps in Query to make M Code easier to read. 11. (04:21) Promote Headers 12. (04:47) Rename Columns 13. (05:07) Unpivot from Cargo Type and Year Columns, then rename two new columns: Year and Volume. 14. (05:45) Investigate Volume column to see that there are numbers that represent volumes and text entries to indicate a cyber attack in the same column. 15. (06:20) Reference base query and create new query named Cyber Attack Records. 16. (06:45) Filter to show Cyber Attack Records. 17. (07:08) Add Data Types 18. (07:18) Duplicate Cyber Attack Records query and create new query to get a table with the Cargo Records, including editing the existing filtering and changing the comparative operator “equals to” to “not equal to”. 19. (08:10) Change Data Type for Volume Column. 20. (08:33) Load base query as a connection only and the two proper table queries as new queries on new worksheets. 21. (09:18) Summary 22. (09:24) Closing #excel #microsoft #excelisfun #excelcourse #mikegirvin #excel365 #powerquery #mcode #powerbi #datacleaning #dataanalysis #exceldataanalysis #powerbidataanalysis #unpivot #baddata #splitcolumns #mergecolumns #transposematrix This video has subtitles or audio translations in these languages: Afrikaans, Amharic, Arabic (experimental), Bangla (experimental), Bosnian, Cantonese, Chinese, Croatian, Czech, Danish, Dutch, Dutch (Netherlands), Estonian, Fijian, Filipino, French (France), German, German (Germany), Hebrew (experimental), Hindi, Hungarian, Indonesian, Irish, Italian, Japanese, Khmer, Korean (experimental), Lao, Latvian, Lithuanian, Macedonian, Malagasy, Malay, Malayalam, Mongolian, Nepali, Norwegian, Pashto, Persian, Polish, Portuguese, Portuguese (Brazil), Punjabi (experimental), Romanian, Russian (experimental), Scottish Gaelic, Serbian, Sicilian, Slovak, Slovenian, Somali, Spanish, Spanish (United States), Swahili, Tajik, Tamil (experimental), Tatar, Telugu (experimental), Thai, Tibetan, Turkish, Turkmen, Ukrainian, Urdu, Vietnamese, Welsh, Western Frisian, Zulu. Click gear icon below video to set subtitles, audio and language.
Not rated yetPower Query Unpivot with 2 Column Variables? Bad Data Into Good Data. Excel Magic Trick 1916
· 10 min
Download Excel File: https://excelisfun.net/files/EMT1916.xlsx Buy excelisfun’s Power Query M Code book: https://www.mrexcel.com/products/the-transformative-magic-of-power-query-m-code-in-excel-and-power-bi/ Buy excelisfun’s other Excel Books: https://www.amazon.com/stores/Mike-excelisfun-Girvin/author/B009Q5T4P2?ref=ap_rdr&ccs_id=6f8ce877-462f-447e-b825-7ede1f72a9d3 Free Excel Class from excelisfun: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo In this video learn how to convert bad data (cross tabulated table with two column variables and one row variable) into a proper data set for easier analysis using Power Query. Topics: 1. (00:00) Introduction. 2. (00:31) New Power Query book by Mike excelisfun Girvin 3. (00:52) How a cross tabulated table translates into a proper table. 4. (01:37) Convert worksheet cross tabulated table into an Excel Table BEFORE bringing into the Power Query Editor. 5. (02:55) Use keyboard shortcut to bring Excel Table into the Power Query Editor. 6. (03:20) Power Query Editor. 7. (03:46) Transpose table for first time. 8. (04:00) Rename Steps in Query to make M Code easier to read. 9. (04:47) Merge fields. 10. (05:31) Transpose table for second time. 11. (05:40) Promote Headers 12. (05:53) Name Week Column 13. (05:59) Unpivot from Week column, then rename Volume column. 14. (06:40) Split merged columns and name two new columns. 15. (07:12) Investigate Volume column to see that there are numbers that represent volumes and text entries to indicate a cyber attack in the same column. 16. (07:45) Reference base query and create new query named Cargo Records. 17. (08:10) Filter out Cyber Attack Records. 18. (08:18) Add Data Types 19. (08:33) Reference base query and create new query named Cyber Attack Records, including filtering to show only Cyber Attack Records and adding Data Types. 20. (08:55) Load base query as a connection only and the two proper table queries as new queries on new worksheets. 21. (09:37) Summary 22. (09:48) Closing #excel #microsoft #excelisfun #excelcourse #mikegirvin #excel365 #powerquery #mcode #powerbi #datacleaning #dataanalysis #exceldataanalysis #powerbidataanalysis #unpivot #baddata #splitcolumns #mergecolumns #transposematrix This video has subtitles or audio translations in these languages: Afrikaans, Albanian, Amharic, Arabic (experimental), Armenian, Bangla (experimental), Bosnian, Burmese, Cantonese, Catalan, Chinese, Croatian, Czech, Danish, Dutch, Dutch (Netherlands), Estonian, Faroese, Filipino, Finnish, French, French (France), Georgian, German, German (Austria), German (Germany), German (Switzerland), Greek, Hebrew (experimental), Hindi, Icelandic, Indonesian, Irish, Italian, Japanese, Khmer, Korean (experimental), Kurdish, Lao, Latvian, Lithuanian, Luxembourgish, Macedonian, Malagasy, Malay, Malayalam, Mongolian, Nepali, Norwegian, Pashto, Persian, Persian (Afghanistan), Persian (Iran), Polish, Portuguese, Portuguese (Brazil), Punjabi (experimental), Romanian, Russian (experimental), Scottish Gaelic, Serbian, Sicilian, Slovak, Slovenian, Somali, Spanish, Spanish (Latin America), Spanish (Mexico), Spanish (Spain), Spanish (United States), Tajik, Tamil (experimental), Tatar, Telugu (experimental), Thai, Tibetan, Tsonga, Turkish, Turkmen, Ukrainian, Urdu, Uzbek, Vietnamese, Welsh, Western Frisian, Zulu. Click gear icon below video to set subtitles, audio and language.
Not rated yet2 Excel Formulas to Add Sequential Numbers Each Time a New Row Is Added To a Table. EMT 1915
· 5 min
Download Excel File: https://excelisfun.net/files/EMT1914-1915.xlsx Buy excelisfun’s Excel Books: https://www.amazon.com/stores/Mike-excelisfun-Girvin/author/B009Q5T4P2?ref=ap_rdr&ccs_id=6f8ce877-462f-447e-b825-7ede1f72a9d3 Free Excel Class from excelisfun: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo Learn how to increment numbers (1, 2, 3, 4…) to create an auto numbering column in a table that adds a new sequential number each time a new record is added. See a formula to do it in a worksheet table and a different formula in an Excel Table. Topics: 1. (00:00) Introduction. 2. (00:17) Goal of Formula 3. (00:33) Formula #1: SEQUENCE and COUNT function to increment numbers in a table in the worksheet. This is a Dynamic Spilled Array Formula. 4. (01:32) Characteristics of a Dynamic Spilled Array Formula. 5. (02:04) Add new records and formula updates! 6. (02:06) Keyboard shortcut to hard code a historical date into a cell = Ctrl + ; 7. (02:33) Dynamic Spilled Array Formula DO NOT WORK in Excel Tables. 8. (02:58) Formula #2: In an Excel Table Column, use ROWS function, Locked Cell Reference, and Table Formula Nomenclature with the Implicit Intersection Operator. This is a Single-Input-Single-Output Formula. 9. (04:25) Add a new record to the Excel Table and the Table feature automatically copies the formula to the new record (row). 10. (04:41) Summary 11. (04:57) Closing #excel #microsoft #excelisfun #excelcourse #mikegirvin #excel365 #rows #rowsfunction #sequence #sequencefunction # DynamicSpilledArrayFormula #arrayformulas #exceltables #sequence #autonumber #incrementnumbers #count #countfunction #countafunction # ImplicitIntersectionOperator #cellreference This video has subtitles or audio translations in these 145 different languages: Abkhazian, Afar, Afrikaans, Albanian, Amharic, Arabic (experimental), Armenian, Assamese, Aymara, Azerbaijani, Bambara, Bangla (experimental), Bangla (India), Bashkir, Basque, Belarusian, Bosnian, Breton, Bulgarian, Burmese, Cantonese, Cantonese (Hong Kong), Catalan, Chinese, Chinese (China), Chinese (Hong Kong), Chinese (Singapore), Chinese (Taiwan), Corsican, Croatian, Czech, Danish, Dutch, Dutch (Belgium), Dutch (Netherlands), English (United Kingdom), Esperanto, Estonian, Faroese, Fijian, Filipino, Finnish, French, French (Belgium), French (Canada), French (France), French (Switzerland), Georgian, German, German (Austria), German (Germany), German (Switzerland), Greek, Gujarati, Haitian Creole, Hausa, Hawaiian, Hebrew (experimental), Hindi, Hungarian, Icelandic, Indonesian, Irish, Italian, Japanese, Kannada, Kazakh, Khmer, Korean (experimental), Kurdish, Lao, Latin, Latvian, Lingala, Lithuanian, Luxembourgish, Macedonian, Maithili, Malagasy, Malay, Malay (Singapore), Malayalam, Maltese, Mizo, Mongolian, Nepali, Norwegian, Occitan, Odia, Oromo, Papiamento, Pashto, Persian, Persian (Afghanistan), Persian (Iran), Polish, Portuguese, Portuguese (Brazil), Portuguese (Portugal), Punjabi (experimental), Romanian, Rundi, Russian (experimental), Samoan, Sango, Santali, Scottish Gaelic, Serbian, Serbian (Cyrillic), Serbian (Latin), Sicilian, Sindhi, Slovak, Slovenian, Somali, Spanish, Spanish (Latin America), Spanish (Mexico), Spanish (Spain), Spanish (United States), Swahili, Swedish, Tajik, Tamil (experimental), Tatar, Telugu (experimental), Thai, Tibetan, Tigrinya, Tongan, Tsonga, Tswana, Turkish, Turkmen, Ukrainian, Urdu, Uyghur, Uzbek, Venda, Vietnamese, Welsh, Western Frisian, Wolof, Yoruba, Zulu. Click gear icon below video to set subtitles, audio and language.
Not rated yetPivotTable New Features (Spill Error & Auto Refresh) and PivotTable Basics Revealed. EMT 1914
· 7 min
Download Excel File: https://excelisfun.net/files/EMT1914-1915.xlsx Buy excelisfun’s Excel Books: https://www.amazon.com/stores/Mike-excelisfun-Girvin/author/B009Q5T4P2?ref=ap_rdr&ccs_id=6f8ce877-462f-447e-b825-7ede1f72a9d3 Free Excel Class from excelisfun: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo Learn about two new features in a PivotTable: Helpful #SPILL! Error and Auto Refresh. Also learn the powerful basics of how PivotTables work and how they are created. In specific, I take a BMX bike racing data set from all the races I have participated in this year and create a report that counts how many races for each track, % of races for each track, total points for each track and the % of point total for each track. In the process we the important basics of PivotTables. Topics: 1. (00:00) Introduction. 2. (00:29) Table with BMX Race Data that needs to be analyzed. 3. (00:46) Reporting goal of video. 4. (01:02) Create PivotTable report using Ribbon or Keyboard. 5. (01:29) #SPILL! Error: New in a PivotTable. 6. (02:06) PivotTable Brilliance: Drag Field to Row area and you get a Unique List. 7. (02:17) Drag Text Field to Values area and you get the default COUNT aggregate calculation. 8. (02:28) Summarize Values By calculation option. 9. (02:46) Show Values As option to calculate the % of Column Total for the COUNT aggregate calculation. 10. (03:00) Create informative labels for report. 11. (03:06) Number Formatting for a PivotTable. 12. (03:59) Drag Number Field to Values area and you get the default SUM aggregate calculation. 13. (04:19) Show Values As option to calculate the % of Column Total for SUM aggregate calculation. 14. (04:44) One reason PivotTables are the easiest option when creating reports. 15. (05:00) Sort report by aggregate total column. 16. (05:43) Auto Refresh (when new data is added to source table): New in a PivotTable. 17. (06:25) Set default on your computer so that ALL PivotTables will Auto Refresh when new data is added to the Source Data Table. 18. (06:57) Bonus Tip! 19. (07:40) Summary 20. (07:44) Closing #excel #microsoft #excelcourse #mikegirvin #excel365 #excelisfun #mikegirvin #freeexcellessons #pivottables #pivottable #excelpivottable #excelpivottables #excelpivot #spillerror #spill #dynamicspilledarrayformulas #autorefresh #summarize #summarizevaluesby #showvaluesas #excelreporting #excel2025 #excel2024 #excel2026 This video has subtitles or audio translations in these languages: Afrikaans, Amharic, Arabic (experimental), Armenian, Bangla (experimental), Bulgarian, Burmese, Cantonese, Cantonese (Hong Kong), Chinese, Chinese (China), Croatian, Czech, Danish, Dutch, Dutch (Belgium), Dutch (Netherlands), Estonian, Filipino, Finnish, French, French (Belgium), French (Canada), French (France), French (Switzerland), Georgian, German, German (Austria), German (Germany), German (Switzerland), Greek, Hebrew (experimental), Hindi, Hungarian, Icelandic, Indonesian, Irish, Italian, Japanese, Khmer, Korean (experimental), Kurdish, Latvian, Lithuanian, Maithili, Malagasy, Malay, Malay (Singapore), Malayalam, Maltese, Mongolian, Nepali, Norwegian, Pashto, Persian, Persian (Afghanistan), Persian (Iran), Polish, Portuguese, Portuguese (Brazil), Portuguese (Portugal), Punjabi (experimental), Romanian, Russian (experimental), Sanskrit, Scottish Gaelic, Serbian, Serbian (Cyrillic), Serbian (Latin), Sicilian, Sindhi, Slovak, Slovenian, Somali, Spanish, Spanish (Latin America), Spanish (Mexico), Spanish (Spain), Spanish (United States), Swahili, Swati, Swedish, Tajik, Tamil (experimental), Tatar, Telugu (experimental), Thai, Tibetan, Tigrinya, Tongan, Tsonga, Tswana, Turkish, Turkmen, Ukrainian, Urdu, Uzbek, Vietnamese, Welsh, Western Frisian, Zulu. Click gear icon below video to set subtitles, audio and language.
Not rated yetFILTER function AND Logical Test made Easy with BYROW & AND functions! Excel Magic Trick 1912
· 12 min
Download Excel File: https://excelisfun.net/files/EMT1912-1913.xlsx Buy excelisfun’s Excel Books: https://www.amazon.com/stores/Mike-excelisfun-Girvin/author/B009Q5T4P2?ref=ap_rdr&ccs_id=6f8ce877-462f-447e-b825-7ede1f72a9d3 Free Excel Class from excelisfun: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo Learn how to use the FILTER function to filter a table using an AND Logical Test. See three methods: AND Function helper column, Direct AND Logical Test Dynamic Spilled Array Formula using the multiplication math operator, and the BYROW & AND functions. Learn the basics of an AND and an OR Logical Test. Learn why the AND function does not work in the FILTER function. Topics: 1. (00:00) Introduction. 2. (00:48) Goal of video: Use FILTER function to dynamically extract records from a data set based on an AND Logical Test. 3. (01:10) What is an AND Logical Test? 4. (02:14) Solution #1: AND Function in Helper Column. Why the AND function can’t be used in the FILTER function. 5. (04:36) Solution #2: Direct AND Logical Test Dynamic Spilled Array Formula using the multiplication math operator inside FILTER function. 6. (07:43) Using results from AND function helper column in the FILTER function. 7. (08:07) Solution #3: BYROW and AND function to shorten the formula element that is required in the FILTER function. 8. (10:01) Change conditions to check if formulas work. 9. (10:16) Bonus: Conditionally Formatting the dynamic result with Conditional Formatting. 10. (11:42) Summary 11. (11:56) Closing #excel #microsoft #excelisfun #excelcourse #mikegirvin #excel365 #mikegirvin #freeexcellessons #excelformulas #excelfunctions #filter #filterfunction #byrow #and #logicaltest #andlogicaltest #exceldataanalysis #excellogical #lambda #excellambda This video has subtitles or audio translations in these languages:: Afrikaans, Amharic, Arabic (experimental), Armenian, Bangla (experimental), Bosnian, Bulgarian, Burmese, Cantonese, Chinese, Czech, Danish, Dutch, Dutch (Belgium), Dutch (Netherlands) (experimental), Estonian, Faroese, Fijian, Filipino, Finnish, French, French (Belgium), French (Canada), French (France), French (Switzerland), Georgian, German, German (Austria), German (Germany), German (Switzerland), Greek, Hebrew (experimental), Hindi, Hungarian, Icelandic, Indonesian, Irish, Italian, Japanese, Khmer, Korean (experimental), Kurdish, Macedonian, Maithili, Malayalam, Maltese, Mongolian, Nepali, Norwegian, Pashto, Persian, Persian (Afghanistan), Persian (Iran), Polish, Portuguese, Portuguese (Brazil), Portuguese (Portugal), Punjabi (experimental), Romanian, Russian (experimental), Sanskrit, Scottish Gaelic, Serbian, Serbian (Cyrillic), Serbian (Latin), Sicilian, Slovak, Slovenian, Somali, Spanish, Spanish (Latin America), Spanish (Mexico), Spanish (Spain), Spanish (United States), Swahili, Swedish, Tajik, Tamil (experimental), Tatar, Telugu (experimental), Thai, Tibetan, Tsonga, Tswana, Turkish, Turkmen, Ukrainian, Urdu, Uzbek, Vietnamese, Welsh, Western Frisian, Zulu. Click gear icon below video to set subtitles, audio and language.
Not rated yetAmazing BYROW Function in FILTER Function Make Dynamic Filtering by OR Logical Test Easy! EMT 1913
· 10 min
Download Excel File: https://excelisfun.net/files/EMT1912-1913.xlsx Buy excelisfun’s Excel Books: https://www.amazon.com/stores/Mike-excelisfun-Girvin/author/B009Q5T4P2?ref=ap_rdr&ccs_id=6f8ce877-462f-447e-b825-7ede1f72a9d3 Free Excel Class from excelisfun: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo Learn how to use the FILTER function to filter a table using an OR Logical Test. See three methods: OR Function helper column, Direct OR Logical Test Dynamic Spilled Array Formula using the math operator addition (plus), and the BYROW & AND functions. Learn the basics of an AND and an OR Logical Test. Learn why the OR function does not work in the FILTER function. Topics: 1. (00:00) Introduction. 2. (00:41) Goal of video: Use FILTER function to dynamically extract records from a data set based on an OR Logical Test. 3. (00:58) What is an OR Logical Test? 4. (02:08) Solution #1: OR Function in Helper Column. Why the OR function can’t be used in the FILTER function. 5. (04:31) Solution #2: Direct OR Logical Test Dynamic Spilled Array Formula using the addition math operator inside FILTER function. 6. (04:53) Why Addition Operator is used for an OR Logical Test. 7. (07:41) Solution #3: BYROW and OR function to shorten the formula element that is required in the FILTER function. 8. (09:27) Summary 9. (09:52) Closing #excel #microsoft #excelisfun #excelcourse #mikegirvin #excel365 #freeexcellessons #excelformulas #excelfunctions #filter #filterfunction #byrow #lambda #exceltips #exceltutorial #exceldataanalysis #excellambda #excellogical #orfunction #andfunction #orlogicaltest #andlogicaltest #filter #dynamicspilledarrayformulas This video has subtitles or audio translations in these languages:: Afrikaans, Amharic, Arabic (experimental), Armenian, Bangla (experimental), Bosnian, Bulgarian, Burmese, Cantonese, Chinese, Croatian, Danish, Dutch, Dutch (Belgium), Dutch (Netherlands) (experimental), Estonian, Faroese, Fijian, Filipino, Finnish, French, French (Belgium), French (Canada), French (France), French (Switzerland), Georgian, German, German (Austria), German (Germany), German (Switzerland), Greek, Hebrew (experimental), Hindi, Hungarian, Icelandic, Indonesian, Irish, Italian, Japanese, Khmer, Korean (experimental), Latvian, Lithuanian, Luxembourgish, Macedonian, Maithili, Malagasy, Malay, Malayalam, Maltese, Mongolian, Nepali, Norwegian, Pashto, Persian, Persian (Afghanistan), Persian (Iran), Polish, Portuguese, Portuguese (Brazil), Punjabi (experimental), Romanian, Russian (experimental), Sanskrit, Scottish Gaelic, Serbian, Sicilian, Slovak, Slovenian, Somali, Spanish, Spanish (Latin America), Spanish (Mexico), Spanish (Spain), Spanish (United States), Swahili, Swedish, Tajik, Tamil (experimental), Tatar, Telugu (experimental), Thai, Tibetan, Turkish, Turkmen, Ukrainian, Urdu, Uzbek, Vietnamese, Welsh, Western Frisian, Zulu. Click gear icon below video to set subtitles, audio and language.
Not rated yetMind Blowing GROUPBY Function Reporting Trick with BYROW & LAMBDA eta AND. Excel Magic Trick 1911
· 8 min
Download Excel File: https://excelisfun.net/files/EMT1909-1911.xlsx Buy excelisfun’s Excel Books: https://www.amazon.com/stores/Mike-excelisfun-Girvin/author/B009Q5T4P2?ref=ap_rdr&ccs_id=6f8ce877-462f-447e-b825-7ede1f72a9d3 Free Excel Class from excelisfun: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo Learn how to create a single cell worksheet formula to produce a summary report with a complex AND Logical Test, OR Logical Test and ALL Criteria. This complex report uses multiple formula inputs and allows the user to change inputs and have the report instantly update. But the complex nature of the logical tests is made easy by using the BYROW & AND functions in the filter_array argument of the GROUPBY function. Learn about AND Logical Test, OR Logical Test, and how math operators convert Boolean TRUE & FALSE values to ones and zeroes. Topics: 1. (00:00) Introduction. 2. (00:51) Review of next three videos: 1909, 1910, 1911. 3. (01:24) Look at GROUPBY Function. 4. (02:56) Compare GROUPBY with full logical test and the BYROW option. 5. (03:46) Build Logical Test that we use in array argument of the BYROW function. 6. (06:25) BYROW Array Function. 7. (07:01) GROUPBY Array Function. 8. (07:36) Test. 9. (07:59) Summary 10. (08:32) Closing #excel #microsoft #excelisfun #excelcourse #mikegirvin #excel365 #lambda #excellambda #byrow #etaLamda #AND #Excellogical #logicaltest #Groupby #groupbyfunction, groupbyarrayfunction #Excelgroupby #dynamicspilledarrayformulas #singlecell reports #excel #excel365 #excelisfun #highlinecollege #mikegirvin #freeexcellessons #excelformulas #excelfunctions This video has subtitles or audio translations in these languages:: Afrikaans, Amharic, Arabic (experimental), Bangla (experimental), Chinese, Danish, Dutch, Dutch (Netherlands) (experimental), Filipino, Finnish, French, French (France), German, German (Germany), Hebrew (experimental), Hindi, Icelandic, Indonesian, Irish, Italian, Japanese, Khmer, Korean (experimental), Macedonian, Maithili, Malagasy, Malay, Malayalam, Mongolian, Nepali, Norwegian, Pashto, Persian, Persian (Afghanistan), Persian (Iran), Polish, Portuguese, Portuguese (Brazil), Punjabi (experimental), Romanian, Russian (experimental), Sanskrit, Scottish Gaelic, Serbian, Sicilian, Slovak, Spanish, Spanish (United States), Swahili, Tajik, Tamil (experimental), Tatar, Telugu (experimental), Thai, Tibetan, Turkish, Turkmen, Ukrainian, Urdu, Vietnamese, Welsh, Zulu. Click gear icon below video to set subtitles, audio and language.
Not rated yetMagic Data Validation List using Dynamic Range Reference or Spilled Range Operator!! EMT 1910
· 7 min
Download Excel File: https://excelisfun.net/files/EMT1909-1911.xlsx Buy excelisfun’s Excel Books: https://www.amazon.com/stores/Mike-excelisfun-Girvin/author/B009Q5T4P2?ref=ap_rdr&ccs_id=6f8ce877-462f-447e-b825-7ede1f72a9d3 Free Excel Class from excelisfun: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo Learn how to add a Data Validation Dropdown List to a single cell so that you can copy the cell and the validation AND so that when new values are added, the Data Validation Dropdown List updates perfectly. Learn how to use the Spilled Range Operator and the Dynamic Range Reference Operator. Also learn how to use Relative Cell References in the Data Validation dialog box. Topics: 1. (00:00) Introduction. 2. (00:24) Review of next three videos: 1909, 1910, 1911. 3. (01:01) What we did in last video. 4. (01:16) Goal of this video. 5. (01:58) Problem with Data Validation List. 6. (02:54) Spilled Range Operator solution for a Dynamic Spilled Array. 7. (04:48) Dynamic Range Reference Operator solution. 8. (06:56) Summary 9. (07:13) Closing #excel #microsoft #excelisfun #excelcourse #mikegirvin #excel365 #DynamicRangeReferenceOperator # SpilledRangeOperator # DynamicSpilledArray #datavalidation #datavalidationlist #exceldatavalidation #excel #excel365 #excelisfun #highlinecollege #mikegirvin #freeexcellessons #excelformulas #excelfunctions This video has subtitles or audio translations in these languages:: Afrikaans, Amharic, Arabic (experimental), Bangla (experimental), Chinese, Danish, Dutch, Dutch (Netherlands) (experimental), Filipino, Finnish, French, French (France), German, German (Germany), Hebrew (experimental), Hindi, Indonesian, Irish, Italian, Japanese, Khmer, Korean (experimental), Maithili, Malagasy, Malay, Malayalam, Nepali, Norwegian, Pashto, Persian, Persian (Afghanistan), Persian (Iran), Polish, Portuguese, Portuguese (Brazil), Punjabi (experimental), Russian (experimental), Sanskrit, Scottish Gaelic, Serbian, Sicilian, Slovenian, Somali, Spanish, Spanish (United States), Swahili, Tajik, Tamil (experimental), Tatar, Telugu (experimental), Thai, Tibetan, Turkish, Ukrainian, Urdu, Vietnamese, Welsh. Click gear icon below video to set subtitles, audio and language.
Not rated yet