MrExcel 2024 Igniting Excel


February 2024

MrExcel 2024 Igniting Excel

The 7th Edition of MrExcel XL, updated with new functions released for Microsoft 365.

Bill Jelen, MrExcel
Add to Cart: $14.95 »

category: Spreadsheets
covers: Microsoft 365

Product Details
  • 326 pages
  • Publisher: Holy Macro! Books
  • PDF ISBN: 978-1-61547-171-3

This is the 7th edition of MrExcel XL. Updates for 2024 include: Remove Filter Items Using Search Box, The Consolas Font Makes it Easy to Tell Zero from the Letter O, Display Online Pictures In a Cell Using a Formula, Place Pictures In Cell From Local Computer, Using Artificial Intelligence with Copilot for Excel, Ask OpenAI Questions from Excel Using Excel Labs, Pivot Table with Sum and Average Total Rows (MDX), Sort into a Random Sequence, Create a Summary Table With the GROUPBY Function, Counting with GROUPBY, Moving from GROUPBY to PIVOTBY, Calculate Percent of Total with PERCENTOF Function, Number the Visible Rows in a Filtered Data Set, Using Python in Excel, Excel Pre-Loads Many Python Libraries, Python for K-Means Clustering of Excel Data, Combining Subformulas into a Single Lambda, Data From Picture is Not Power Query, Checkboxes, Prevent Default Conversions, Check Performance in Excel, See Intermediate Calculations, Stale Value Formatting, Sort by Font Width?!, Get the Countries from a Column of Phone Numbers, Temporarily Make Formula Bar Font Size Larger, Oddity 1: The Year 2025 is the Square of 45, Circle Invalid Data Automatically, The Advanced Filter Treats Criteria as Begins With"", Generate All Combinations Using BASE Function, Why a Dark Rectangle Around the Formula Bar?, Unhide One Specific Column Without Unhiding Others

Updates for 2022 include: Show Changes, Version History, Collaboration, Data Validation Partial Matching, Cartoon People, Stacking arrays with VSTACK, Array shaping with WRAP, TOCOL, and TOROW, Advanced Formula Editor, LAMBDA helper functions, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, Paste between computers with the cloud clipboard, smooth scrolling, full screen mode, and more.

Updates for 2021 included: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.

Updates in 2020 included Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.

  • #1 Double-Click the Fill Handle to Copy a Formula
  • #2 Break Apart Data
  • #3 Convert Text Numbers to Numbers Quickly
  • #4 Filter by Selection
    • Bonus Tip: Filter by Selection for Numbers Over
    • Bonus Tip: Remove Filter Items Using Search Box
  • #5 Total the Visible Rows
  • #6 The Fill Handle Does Know 1, 2, 3...
    • Bonus Tip: Fill Jan, Feb, ..., Dec, Total
    • Bonus Tip: Fill 1 to 100,000 in a Flash
  • #7 Fast Worksheet Copy
    • Bonus Tip: Put the Worksheet Name in a Cell
    • Bonus Tip: Add a Total Row and a Total Column with One AutoSum
    • Bonus Tip: Power Up the Status Bar Statistics
    • Bonus Tip: Change All Sheets with Group Mode
    • Bonus Tip: Create a SUM That Spears Through All Worksheets
    • Bonus Tip: Use INDIRECT for a Different Summary Report
  • #8 Use Default Settings for All Future Workbooks
    • Bonus Tip: Changes to Book Template are Cumulative
    • Bonus Tip: Replace the Comma Style in Book.xltx
    • Bonus Tip: The Consolas Font Makes it Easy to Tell Zero from the Letter O
  • #9 Recover Unsaved Workbooks
  • #10 Search While Using File Open
    • Bonus Tip: Pin an Item to the Top of the Recent Files
  • #11 Show Changes From Last 60 Days
    • Bonus Tip: Roll Back to a Previous Version of the Workbook
  • #12 Simultaneously Edit a Workbook in Microsoft 365
    • Bonus Tip: Avoiding the Veto
    • Bonus Tip: AutoSave is Necessary, But Turn it Off When Not Co-Authoring
    • Bonus Tip: Undo an AutoSave
  • #13 Save Filter & Sorting in Sheet View
  • #14 New Threaded Comments Allow Conversations
    • Bonus Tip: Old Style Comments Are Available as Notes
    • Bonus Tip: Add a Tooltip to a Cell with Validation
    • Bonus Tip: Data Validation Partial Matching Added in 2022
  • #15 Create Perfect One-Click Charts
  • #16 Paste New Data on a Chart
  • #17 Create Interactive Charts
  • #18 Show Two Different Orders of Magnitude on a Chart
  • #19 Create Waterfall Charts
  • #20 Create Funnel Charts
  • #21 Create Filled Map Charts
  • #22 Create a Bell Curve
  • #23 Plotting Employees on a Bell Curve
  • #24 Add Meaning to Reports Using Data Visualizations
  • #25 Use People to Add Interest to Your Worksheet
    • Bonus Tip: Add Text to the Sign Held by a Person
    • Make an Image Semi-Transparent
  • #26 Save Any Object as an Image
  • #27 Display Online Pictures In a Cell Using a Formula
  • #28 Place Pictures In Cell From Local Computer
  • #29 Set Up Your Data for Data Analysis
    • Bonus Tip: Use Accounting Underline to Avoid Tiny Blank Columns
    • Bonus Tip: Use Alt+Enter to Control Word Wrap
    • Bonus Tip: Someone went crazy and used Alt+Enter Too Much
  • #30 Sort East, Central, and West Using a Custom List
  • #31 Sort Left to Right
  • #32 Sort Subtotals
    • Bonus Tip: Fill in a Text Field on the Subtotal Rows
    • Bonus Tip: An Easier Way to Fill in a Text Field on Subtotal Rows
    • Bonus Tip: Format the Subtotal Rows
    • Bonus Tip: Copy the Subtotal Rows
  • #33 Sort and Filter by Color or Icon
  • #34 Consolidate Quarterly Worksheets
  • #35 Create Your First Pivot Table
    • Bonus Tip: Rearrange fields in a pivot table
    • Bonus Tip: Format a Pivot Table
    • Bonus Tip: Format One Cell in a Pivot Table
    • Bonus Tip: Fill in the Blanks in the Annoying Outline View
    • Bonus Tip: Replace Blank Values Cells With Zero
    • Bonus Tip: Rearrange Fields Pane
  • #36 Create a Year-over-Year Report in a Pivot Table
    • Bonus Tip: Another Way to Calculate Year-Over-Year
  • #37 Change the Calculation in a Pivot Table
    • Bonus Tip: Why Do Pivot Tables Count Instead of Sum?
  • #38 Find the True Top Five in a Pivot Table
  • #39 Specify Defaults for All Future Pivot Tables
    • Bonus Tip: Change What Drives You Crazy About Excel
  • #40 Make Pivot Tables Expandable Using Ctrl+T
    • Bonus Tip: Use Ctrl+T with VLOOKUP and Charts
  • #41 Replicate a Pivot Table for Each Rep
  • #42 Use a Pivot Table to Compare Lists
    • Bonus Tip: Show Up
    • Bonus Tip: Compare Two Lists by Using Go To Special
  • #43 Build Dashboards with Sparklines and Slicers
    • Bonus Tip: Line Up Dashboard Sections with Different Column Widths
    • Bonus Tip: Report Slicer Selections in a Title
  • #44 See Why GETPIVOTDATA Might Not Be Entirely Evil
  • #45 Ask Excel's A.I. a Question About Your Data
  • #46 Using Artificial Intelligence with Copilot for Excel
  • #47 Ask OpenAI Questions from Excel Using Excel Labs
  • #48 Eliminate VLOOKUP or XLOOKUP with the Data Model
    • Bonus Tip: Count Distinct
  • #49 Pivot Table with Sum and Average Total Rows (MDX)
  • #50 Compare Budget Versus Actual via Power Pivot
    • Bonus Tip: Portable Formulas
    • Bonus Tip: Text in the Values of a Pivot Table
  • #51 Slicers for Pivot Tables From Two Data Sets
  • #52 Use F4 for Absolute Reference or Repeating Commands
    • Bonus Tip: Use a Named Range Instead of Absolute References
  • #53 Quickly Convert Formulas to Values
    • Bonus Tip: Skip Blanks While Pasting
  • #54 See All Formulas at Once
    • Bonus Tip: Highlight All Formula Cells
    • Bonus Tip: Trace Precedents to See What Cells Flow into a Formula
    • Bonus Tip: See Which Cells Depend on the Current Cell
  • #55 Audit a Worksheet With Spreadsheet Inquire
  • #56 Discover New Functions by Using fx
  • #57 Use Function Arguments for Nested Functions
  • #58 Calculate Nonstandard Work Weeks
    • Bonus Tip: Use WORKDAY.INTL for a Work Calendar
  • #59 Turn Data Sideways with a Formula
    • Bonus Tip: Protect Rows with an Old-Style Array Formula
  • #60 Handle Multiple Conditions in IF
    • Bonus Tip: Use Boolean Logic
  • #61 Troubleshoot VLOOKUP
  • #62 Use a Wildcard in VLOOKUP or XLOOKUP
    • Bonus Tip: VLOOKUP to Two Tables
  • #63 Twelve Benefits of XLOOKUP
  • #64 Preview What Remove Duplicates Will Remove
  • #65 Replace Nested IFs with a Lookup Table
    • Bonus Tip: Match the Parentheses
  • #66 Suppress Errors with IFERROR
  • #67 Handle Plural Conditions with SUMIFS
  • #68 Geography, Exchange Rate & Stock Data Types in Excel
    • Bonus Tip: Use Data, Refresh All to Update Stock Data
  • #69 Get Historical Stock History from STOCKHISTORY
  • #70 Create Your Own Data Types
  • #71 IF Based on Installed Language in a Bilingual Worksheet
  • #72 Dynamic Arrays Can Spill
  • #73 Sorting with a Formula
    • Bonus Tip: Sort into a Random Sequence
  • #74 Filter with a Formula
    • Bonus Tip: Understanding Array Constants
  • #75 Formula for Unique or Distinct
    • Bonus Tip: Use # "The Spiller" to Refer to All Array Results
  • #76 Other Functions Can Now Accept Arrays as Arguments
  • #77 One Hit Wonders with UNIQUE
  • #78 SEQUENCE inside of other Functions such as IPMT
  • #79 Generating Random Numbers in Excel
  • #80 Replace a Pivot Table with 3 Dynamic Arrays
  • #81 Create a Summary Table With the GROUPBY Function
    • Bonus Tip: Counting with GROUPBY
  • #82 Moving from GROUPBY to PIVOTBY
  • #83 Calculate Percent of Total with PERCENTOF Function
    • Bonus Tip: Replace Ctrl+Shift+Enter with Dynamic Arrays.
  • #84 Dependent Validation using Dynamic Arrays
  • #85 Complex Validation Using a Formula
  • #86 Stack Multiple Arrays
  • #87 Dropping, Taking, or Choosing from an Array
  • #88 Reshaping an Array to a Vector and Back
  • #89 Getting the UNIQUE of a Rectangular Range
  • #90 Shuffling and Dealing a Deck of Cards
  • #91 Use A2:INDEX() as a Non-Volatile OFFSET
  • #92 Number the Visible Rows in a Filtered Data Set
  • #93 Unhide Multiple Worksheets
  • #94 Write Your Data with the Action Pen
  • #95 Many Task Panes Now Collapse into a Tab Strip
  • #96 How to Provide Usable Feedback to the Excel Team
  • #97 Date Tricks in Excel
  • #98 Using Python in Excel
    • Bonus Tip: Excel Pre-Loads Many Python Libraries
  • #99 Python for K-Means Clustering of Excel Data
  • #100 Use the LET Function to Re-Use Variables in a Formula
  • #101 Store Complex Formula Logic in LAMBDA function
    • Bonus: Importing LAMBDAs from GitHub
  • #102 Combining Subformulas into a Single Lambda
    • Bonus Tip: Perform a Loop with a Recursive LAMBDA
    • Bonus Tip: Branching in a LAMBDA
  • #103 New LAMBDA Helper Functions
  • #104 Find Largest Value That Meets One or More Criteria
    • Bonus Tip: Concatenate a Range by Using TEXTJOIN
  • #105 Text Before or After a Specific Delimiter
  • #106 Split Text into Words Using TEXTSPLIT
  • #107 Less CSV Nagging and Better AutoComplete
  • #108 Protect All Formula Cells
  • #109 Back into an Answer by Using Goal Seek
  • #110 Do 60 What-If Analyses with a Sensitivity Analysis
    • Bonus Tip: Create a Data Table from a Blank Cell
  • #111 Find Optimal Solutions with Solver
  • #112 Interpolate between a starting and ending number
  • #113 Improve Your Macro Recording
    • Bonus Tip: Use TypeScript to Write Macros for Excel Online
  • #114 Clean Data with Power Query
    • Bonus Tip: Tame the Sequence of Refresh All
    • Bonus Tip: Data Profiling in Power Query
  • #115 Use Fuzzy Match in Power Query
  • #116 Data From Picture is Not Power Query
  • #117 Build a Pivot Table on a Map Using 3D Maps
  • #118 The Forecast Sheet Can Handle Some Seasonality
  • #119 Perform Sentiment Analysis in Excel
  • #120 Build Org Charts with the Visio Data Visualizer in Excel
  • #121 Fill in a Flash
  • #122 Format as a Façade
  • #123 Show All Open Workbooks in the Windows Taskbar
  • #124 Paste to Another Computer Using Cloud Clipboard
  • #125 Surveys & Forms in Excel
  • #126 Use the Windows Magnifier
  • #127 New Scrolling Tricks
  • #128 Checkboxes
  • #129 Prevent Default Conversions
  • #130 Check Performance in Excel
  • #131 See Intermediate Calculations
  • #132 Stale Value Formatting
  • #133 Sort by Font Width?!
  • #134 Get the Countries from a Column of Phone Numbers
  • #135 Word for Excellers
    • Bonus Tip: Merge Shapes
    • Bonus Tip: Use the Eye Dropper
  • #136 Avoid Whiplash with Speak Cells
    • Bonus Tip: Provide Feedback with Sound
    • Bonus Tip: A Great April Fool’s Day Trick
  • #137 Temporarily Make Formula Bar Font Size Larger
  • #138 Oddity 1: The Year 2025 is the Square of 45
  • #139 Circle Invalid Data Automatically
  • #140 The Advanced Filter Treats Criteria as "Begins With"
  • #141 Generate All Combinations Using BASE Function
  • #142 Why a Dark Rectangle Around the Formula Bar?
  • #143 Unhide One Specific Column Without Unhiding Others
  • #144 Customize the Quick Access Toolbar
    • Bonus Tip: Sometimes, You Don't Want the Gallery
    • Bonus Tip: Show QAT Below the Ribbon
  • #145 Create Your Own QAT Routines Using VBA Macros
    • Bonus Tip: Settings in the Excel Options Menu
  • #146 Favorite Keyboard Shortcuts
  • #147 Ctrl+Click to Unselect Cells
  • #148 Collapse the Search Box
  • #149 More Excel Tips
  • #150 Excel Stories
  • #151 Excel Function Quick Reference
  • #152 Dedication
    • About the Author
    • About the Illustrators
    • Foreword (Even though it is in the back)
    • Sample File Downloads
    • Edge Index
  • #153 Index

Where to Buy

MrExcel Bookstore

eBook from MrExcel.


Other Editions


MrExcel 2022 Boosting Excel

April 2022

The 6th Edition of MrExcel XL, updated with new functions released for Microsoft 365.


MrExcel 2021 - Unmasking Excel

February 2021

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.


MrExcel 2020 - Seeing Excel Clearly

January 2020

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.


MrExcel LX – The Holy Grail of Excel Tips

February 2019

A book for people who use Excel 40+ hours per week. Illustrated in full color.


MrExcel LIVe

April 2018

A book for people who use Excel 40+ hours per week. Illustrated in full color.


Related Products


Power Excel With MrExcel - 2019 Edition

December 2019

This is the print book edition of "Power Excel with MrExcel - 2019 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.