Useful Excel resources to
consider
(Updated
2024-April-12th)
Quotes:
- "This new [advanced] formula editor is the best thing to happen to Excel formulas in such a long time." -- Bill Jelen (video #2466), Feb 2022.
- "The beauty of Pivot Tables is you don't break anything ... you're just slicing and dicing it by row and column..." -- Wyn Hopkins, July 2021.
- "...Power Query only allows you to make GET and POST requests [when accessing API's]." -- Chris Webb, Feb 2021.
- "With LAMBDA, Excel has become Turing-complete. You can now, in principle, write any computation [as Excel formula]." -- Andy Gordon, Jan 2021.
- "The greatest thing to ever happen to Excel is Power Query ... it is the world's best washing machine for dirty data." -- Wyn Hopkins, May 2020.
- "Facts get aggregated ... Dimensions slice Facts ..." -- Ken Puls, June 2019.
- "No column from a Fact Table should EVER be used in a Dimension Field!" -- Ken Puls, June 2019.
- "I don't care where your data lives ... [or] how nasty it is. We can use Power Query to clean it up and automate it for next time." -- Ken Puls, Oct 2018.
- "Power Query remembers the steps [used to transform and clean data]. It is an amazing addition to Excel." -- Bill Jelen (video #1956), Sep 2015.
- "Power Pivot does amazing things: hundred million records, join two tables together without VLOOKUP." -- Bill Jelen (video #1390), April 2011.
- "A PivotTable ... allows you to take ... 500,000 rows of data and summarize it in about six clicks." -- Bill Jelen (video #846), Jan 2009.
Free Interactive Training:
Books:
- P. McFedries, MS Excel Formulas and Functions (Office 2021 & MS 365), Microsoft Press, 2022.
- K. Puls, M. Escobar, Master Your Data with Excel and Power BI, 2nd Ed., Holy Macro! Books, 2021 / 2015.
- R. Rad, L. Etaati, Getting Started with Power Query in Power BI and Excel, RADACAD, 2021.
- R. Rad, L. Etaati, Mastering Power Query in Power BI and Excel, RADACAD, 2021.
- J. Lambert, C. Frye, MS Excel Step by Step (Office 2021 & MS 365), Microsoft Press, 2021.
- B. Jelen, MS Excel Inside Out (Office 2021 & MS 365), Microsoft Press, 2021.
- B. Jelen, MS Excel Pivot Table Data Crunching (Office 2021 & MS 365), Microsoft Press, 2021.
- M. Russo, A. Ferrari, The Definitive Guide to DAX, 2nd Ed., Microsoft Press, 2019 / 2015.
- G. Raviv, Collect, Combine, and Transform Data Using Power Query in Excel and Power BI, Microsoft Press, 2018.
- A. Ferrari, M. Russo, Analyzing Data with Power BI and Power Pivot for Excel, Microsoft Press, 2017.
- K. R. Baker, Optimization Modeling with Spreadsheets, 3rd Ed., Wiley, 2015 / 2005.
- H. Guerrero, Excel Data Analysis: Modeling and Simulation, Springer, 2010.
- P. Cornell, Beginning Excel What-If Data Analysis Tools, Apress, 2005.
Websites/Videos/Podcasts:
- Bill Jelen, "Checkboxes Arrive in Excel", YouTube video, Oct 2023.
- Wyn Hopkins, "How to Use Pivot in Power Query", YouTube video, June 2023.
- Wyn Hopkins, "Excel Self-Updating Drop Down List for Data Validation", YouTube video, April 2023.
- Dave Bruns, "How to Pick Names Out of a Hat with Excel", ExcelJet Article & Video, March 2023.
- Wyn Hopkins, "5 Magic Pivot Table Techniques", YouTube video, March 2023.
- Wyn Hopkins, "Excel vs. Power BI Compared", YouTube video, March 2023.
- Jeff Lenning, "Excel PivotTables for Beginners", YouTube video, Jan 2023.
- Jeff Lenning, "7 Excel Time Hacks Everyone Should Know", YouTube video, Aug 2022.
- David Langer, "Hands-on Data Literacy for Excel Users", YouTube video, Feb 2022.
- Bill Jelen, "PivotTables with MrExcel", Excel Virtually Global talk, Oct 2021.
- Ann K. Emery, "The Inside Scoop on Excel with Bill Jelen (Mr. Excel)", YouTube video, July 2021.
- Leila Gharani, "How to Create Random Numbers in Excel (including no repeats)", YouTube Video, June 2021.
- Wyn Hopkins, "Three Essential Excel Skills for the Data Analyst", YouTube video, June 2021.
- Bill Jelen, "Pivot Table Tips and Tricks", MS Excel Toronto Meetup talk, April 2021.
- Ken Puls, "Quickly Solving the [Slowly Changing Dimension] SCD Challenge", Perth Power BI Usergroup Meetup talk, April 2021.
- Ken Puls, "Excel & Power BI: Better Together", Calgary Power Platform User Group talk, Nov 2020.
- Wyn Hopkins, "Twenty Years of Excel Tips Jammed into One Hour!", YouTube video, May 2020.
- Joe McDaid, "Getting Started with Dynamic Arrays in Excel", MS Session, June 2019.
- Joe McDaid, "Advanced Spreadsheet Modeling Using Dynamic Arrays", MS Session, June 2019.
- Ken Puls, "Excel: Best Practices for Spreadsheet Modeling, Data Shaping, and Data", MS Business Applications Summit talk, June 2019.
- K. Puls, D. Monroy, "Best Practices for Spreadsheet Modeling, Data Shaping and Analysis", MS Session, June 2019.
- Alan Murray, "Compare Two Lists in Excel using Power Query", YouTube video, May 2019.
- A. Sharma, G. Hunkin, "Whirlwind Tour of the Modern Excel", MS Session, June 2017.
- Joe McDaid, "New Aggregation Functions: GROUPBY and PIVOTBY", MS Blog Article, Nov 2023.
- Joe McDaid, "Announcing New Text and Array Functions", MS Blog Article, March 2022.
- TEXTBEFORE(), TEXTAFTER(), TEXTSPLIT(), HSTACK(), VSTACK()
- TOROW(), TOCOL(), WRAPROWS(), WRAPCOLS()
- TAKE(), DROP(), CHOOSEROWS(), CHOOSECOLS(), EXPAND()
- Wyn Hopkins, "What are Tables [in Excel]?", YouTube video, Sep 2021.
- Svetlana Cheusheva, "Structured Reference in Excel Tables",
Ablebits Blog Article, Feb 2019.
- Svetlana Cheusheva, "Excel Table: End-to-End Tutorial with Examples",
Ablebits Blog Article, July 2016.
- Dave Bruns, "Pivot Table Tips", ExcelJet Article, Aug 2020.
- John MacDougall, "Summarizing Text Data with Pivot Tables", Blog Article, January 2019.
- Svetlana Cheusheva, "Excel Slicers for [Tables,] Pivot Tables and Charts",
Ablebits Blog Article, June 2018.
- Bill Jelen, "Median In Pivot Table", MrExcel video, May 2018.
- Dave Bruns, "Ten Pivot Table Problems and Easy Fixes", Exceljet video, April 2015.
- Dave Bruns, "What can you do with Pivot Tables?", Exceljet video, April 2015.
- Dave Bruns, "Why Pivot Tables?", Exceljet video, April 2015.
- Bill Jelen, "[Statistical Range] (Max - Min) in a Pivot Table", MrExcel video, April 2011.
- Svetlana Cheusheva, "How to Use Pivot Tables in Excel - Tutorial for Beginners",
Ablebits Blog Article, Nov 2014.
- Frederique Klitgaard, "Excel Table or PivotTable?", MS 365 Blog Article, Jan 2011.
- Bill Jelen, "Non-Pivotable Data", MrExcel video, April 2009.
- Wyn Hopkins, "Power Query Merge Challenge Solution - Duplicates Alert", YouTube video, Oct 2022.
- Wyn Hopkins, "How and Why to Unpivot Data with Power Query", YouTube video, Dec 2021.
- Wyn Hopkins, "What is Power Query?", YouTube video, Sep 2021.
- Mynda Treacy, "Power Query vs. Power Pivot", YouTube video, April 2021.
- Wyn Hopkins, "Power Query Formula Help", YouTube video, April 2021.
- Wyn Hopkins, "Ten Million Rows of Data Analyzed using Excel's Data Model", YouTube video, Mar 2021.
- Wyn Hopkins, "Power Query Documentation Tips", YouTube video, Feb 2021.
- Leila Gharani, "Excel Unpivot Data with Multiple Headers", YouTube video, Jul 2020.
- Leila Gharani, "How Power Query Will Change the Way You Use Excel", YouTube video, Jun 2020.
- Ken Puls, "Advanced Data Prep with Power Query [and Power Pivot]", Modern Excel Webinar, Jun 2020.
- Jon Acampora, "Power Query Overview - Automate Tasks in Excel & Power BI", YouTube video, May 2020.
- Mike Girvin, "Bring Excel Sheet Variable into Power Query", YouTube video, Nov 2019.
- Leila Gharani, "Covert Columns to Rows in Excel [using Unpivot in Power Query]", YouTube video, Sep 2019.
- John MacDougall, "Create a Parameter Table for Your Power Queries", YouTube video, Nov 2018.
- Mike Girvin, "Power Query to Import from SQL Server Database in Excel or Power BI Desktop", YouTube video, Sep 2018.
- Craig Hatmaker, "Parameterized Power Query", YouTube video, April 2018.
- Bill Jelen, "Unpivot with Power Query", MrExcel video, Aug 2014.
- Victor Sánchez Jáuregui, "Power Query or SQL Server? Which, When and Why", SQLServerCentral Blog entry, Jan 2021.
- Jonathan Kahati, "Announcing Power Query Data Types",
MS Blog Article, Oct 2020.
- John MacDougall, "Fuzzy Matching in Power Query for Microsoft Excel",
MS Blog Article, Oct 2019.
- Teo Lachev, "T-SQL, Power Query, or DAX?", Prologika Blog Entry, Jan 2019.
- Chris Webb, "Comparing the Performance of CSV and Excel Data Sources in Power Query", Blog Entry, August 2018.
- Paul Turley, "SQL, M or DAX?", Blog Entry, May 2018.
- Rob Collie, "I Know SQL Queries, So Why Do I Need Power Pivot?", P3 Adaptive Blog entry, Sep 2014.
- Svetlana Cheusheva, "Excel XMATCH Function with Formula Examples",
Ablebits Blog Article, Sep 2020.
- Wyn Hopkins, "What is XLOOKUP and How to Use it", YouTube video, August 2020.
- Wyn Hopkins, "Which is Faster: XLOOKUP or INDEX MATCH?, YouTube video, March 2020.
- Svetlana Cheusheva, "XLOOKUP Function in Excel for Vertical and Horizontal Lookup",
Ablebits Blog Article, Dec 2019.
- Bill Jelen, "XLOOKUP-XLOOKUP or INDEX-MATCH-MATCH: Head-to-Head", MrExcel Podcast Episode 2287, August 2019.
- Joe McDaid, "Announcing XLOOKUP (and XMATCH)",
MS Blog Article, Aug 2019.
- Chris Gross, "Announcing LAMBDA's to Production and Advanced Formula Environment", MS Blog Article, Feb 2022.
- Svetlana Cheusheva, "How to Write Recursive LAMBDA Function in Excel with Examples",
Ablebits Blog Article, Jun 2021.
- Svetlana Cheusheva, "Excel LAMBDA Function: How to Write and Use",
Ablebits Blog Article, Jun 2021.
- Andy Gordon, Simon Peyton Jones, "Advancing Excel as a Programming Language
", MS Research Podcast, May 2021.
- Andy Gordon, Simon Peyton Jones, "LAMBDA: The Ultimate Excel Worksheet Function",
MS Blog Article, Jan 2021.
- Bill Jelen, "Excel LAMBDA Functions Change Everything!", MrExcel Podcast Episode 2381, December 2020.
- Brian Jones, "Announcing LAMBDA: Turn Excel Formulas into Custom Functions",
MS Blog Article, Dec 2020.
- Svetlana Cheusheva, "Using LET Function in Excel with Formula Examples",
Ablebits Blog Article, Feb 2021.
- Chris Gross, "LET - Names in Formulas - Generally Available",
MS Blog Article, Nov 2020.
- Chris Gross, "Announcing LET",
MS Blog Article, March 2020.
- Joe McDaid, "Excel Dynamic Array Improvements",
MS Blog Article, Feb 2019.
- Joe McDaid, "Preview of Dynamic Arrays in Excel",
MS Blog Article, Sep 2018.
- Svetlana Cheusheva, "Spilling in Excel: What It Means and How to Use It",
Ablebits Blog Article, April 2021.
- Svetlana Cheusheva, "Excel Spill Range Explained",
Ablebits Blog Article, April 2021.
- Svetlana Cheusheva, "Implicit Intersection and @ Operator in Excel 365 and Older Versions",
Ablebits Blog Article, March 2021.
- Svetlana Cheusheva, "Excel #SPILL! Error - Causes and Fixes",
Ablebits Blog Article, March 2021.
- Svetlana Cheusheva, "CONCAT Function in Excel to Concatenate Text Strings",
Ablebits Blog Article, Sep 2021.
- Svetlana Cheusheva, "5 Ways to do VLOOKUP in Excel - Which is Fastest?",
Ablebits Blog Article, Dec 2020.
- Svetlana Cheusheva, "Excel Wildcard: Find and Replace Wildcards, Filter, and Use in Formulas",
Ablebits Blog Article, Oct 2020.
- Shashank Gandhi, "Create Data-Driven Visio Diagrams Directly in Excel",
MS Blog Article, Nov 2019.
- Liam Bastick, "Conditional Number Formatting",
MS Blog Article, Sep 2019.
- Svetlana Cheusheva, "Linear Regression Analysis in Excel",
Ablebits Blog Article, Aug 2018.
- Svetlana Cheusheva, "How to Use LINEST Function in Excel - Formula Examples",
Ablebits Blog Article, July 2018.
- Svetlana Cheusheva, "Basic Excel Formulas & Functions with Examples",
Ablebits Blog Article, June 2017.
- Svetlana Cheusheva, "How to Use Solver in Excel with Examples",
Ablebits Blog Article, June 2016.
- Svetlana Cheusheva, "Circular Reference in Excel - How to Check, Find, Enable or Remove",
Ablebits Blog Article, Dec 2015.
Best viewed at
1280x720 (720p) screen resolution or higher.