Master Excel like a pro with over 256 laser-focused tips in MrExcel 2025 Navigating Excel

From basic tricks to advanced AI-powered workflows, Bill Jelen shows you how to work smarter, faster, and with way more confidence. Whether you’re wrangling data, building dashboards, or exploring Python and Copilot, this book is your shortcut to everything new and powerful in Excel.

Whether you're just starting out or you're the office Excel wizard, MrExcel 2025 Navigating Excel is your all-in-one field guide to mastering Microsoft Excel’s newest and most powerful features.

With 172 bite-sized tips, each one focused, practical, and battle-tested, this book will change the way you use Excel. Learn faster ways to clean data, build smarter dashboards, analyze with Python, create stunning visualizations, and even collaborate with AI using Microsoft Copilot.

From simple tricks like double-clicking the fill handle to power moves like using GROUPBY and LAMBDA, Bill Jelen walks you through real-world solutions in a way that’s fun, fast, and crystal clear. And yes, there are dozens of bonus tips tucked in along the way!

Highlights include:

How to build perfect pivot tables (and fix what drives you crazy about them)
Clean, analyze, and visualize your data with Python—right inside Excel
Use Copilot to write formulas, summarize YouTube videos, and search the web
Discover dynamic arrays, regex formulas, and new validation tricks
Unlock over 40 tips for working with charts, dashboards, and slicers
Automate tasks with VBA, Office Scripts, and new macro recording improvements

Updated for the latest Excel innovations through June 2025, including everything Copilot, AI, and Python-related.

Whether you're an accountant, analyst, student, or manager, this is your roadmap to Excel mastery. Stop clicking around. Start navigating.

Product Details

by Bill Jelen

category: Excel, Spreadsheets

covers: Microsoft 365

317 pages

Publisher: Holy Macro! Books

ISBN: 978-1-61547-088-4

PDF ISBN: 978-1-61547-175-1

Table of Contents

#1 Double-Click the Fill Handle to Copy a Formula
#2 Break Apart Data
#3 Convert Text Numbers to Numbers Quickly
#4 Highlight the Active Cell with Focus Cell
#5 Filter by Selection
Bonus Tip: Filter by Selection for Numbers Over/Under
Bonus Tip: Remove Filter Items Using Search Box
#6 Total the Visible Rows
#7 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
#8 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
#9 Use Default Settings for All Future Workbooks
Bonus Tip: Replace the Comma Style in Book.xltx
Bonus Tip: The Consolas Font Makes it Easy to Tell Zero from the Letter O
#10 Recover Unsaved Workbooks
#11 Search While Using File Open
Bonus Tip: Pin an Item to the Top of the Recent Files
#12 Show Changes From Last One Year
Bonus Tip: Roll Back to a Previous Version of the Workbook
#13 Add Interactive Checkboxes in Excel
#14 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
#15 Save Filter & Sorting in Sheet View
#16 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
#17 Create Perfect One-Click Charts
#18 Paste New Data on a Chart
#19 Create Interactive Charts
#20 Show Two Different Orders of Magnitude on a Chart
#21 Create Waterfall Charts
#22 Create Filled Map Charts
#23 Create a Bell Curve
#24 Plotting Employees on a Bell Curve
#25 Add Meaning to Reports Using Data Visualizations
#26 Use People to Add Interest to Your Worksheet
Bonus Tip: Add Text to the Sign Held by a Person
Bonus Tip: Make an Image Semi-Transparent
#27 Save Any Object as an Image
#28 Display Online Pictures In a Cell Using a Formula
#29 Place Pictures In Cell From Local Computer
#30 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
#31 Sort East, Central, and West Using a Custom List
#32 Sort Left to Right
#33 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
#34 Sort and Filter by Color or Icon
#35 Consolidate Quarterly Worksheets
#36 Create Your First Pivot Table
Bonus Tip: Rearrange fields in a pivot table
Bonus Tip: Pivot Table Inherits Number Formatting from the Grid!
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
#37 Show Details Behind Any Value Cell
#38 Create a Year-over-Year Report in a Pivot Table
Bonus Tip: Grouping one Pivot Table groups them all
Bonus Tip: Another Way to Calculate Year-Over-Year
#39 Change the Calculation in a Pivot Table
#40 Find the True Top Five in a Pivot Table
#41 Specify Defaults for All Future Pivot Tables
Bonus Tip: Change What Drives You Crazy About Excel
#42 Make Pivot Tables Expandable Using Ctrl+T
Bonus Tip: Use Ctrl+T with VLOOKUP and Charts
#43 Replicate a Pivot Table for Each Rep
#44 Use a Pivot Table to Compare Lists
Bonus Tip: Compare Two Lists by Using Go To Special
#45 Build Dashboards with Sparklines and Slicers
Bonus Tip: Replace a Long Slicer with a Filter Drop-Down
Bonus Tip: Line Up Dashboard Sections with Different Column Widths
Bonus Tip: Report Slicer Selections in a Title
#46 See Why GETPIVOTDATA Might Not Be Entirely Evil
#47 Eliminate VLOOKUP or XLOOKUP with the Data Model
Bonus Tip: Count Distinct
#48 Pivot Table with Sum and Average Total Rows (MDX)
#49 Compare Budget Versus Actual via Power Pivot
Bonus Tip: Portable Formulas
Bonus Tip: Text in the Values of a Pivot Table
#50 Slicers for Pivot Tables From Two Data Sets
#51 Using Python in Excel
Bonus Tip: Excel Pre-Loads Many Python Libraries
#52 Python for K-Means Clustering of Excel Data
#53 Ask OpenAI Questions from Excel Using Excel Labs
#54 Ask Excel's A.I. a Question About Your Data
#55 Introduction to Copilot in June 2025
Bonus Tip: There Are Many Entry Points to Copilot
Bonus Tip: The Copilot Task Pane offers Four Different Experiences
Bonus Tip: Much of Copilot Requires AutoSave to be On
Bonus Tip: Artificial Intelligence is Unpredictable, On Purpose
#56 Hide the Floating Copilot Icon and “Start With Copilot” Prompt
#57 Explore “Start With Copilot” – Excel's New Way to Launch
#58 Copilot Can Search the Web and Download Data
Bonus Tip: Use Your Voice with Copilot
#59 Use Copilot to Extract Data From an Image
#60 Generate Fake Data with Python in Excel
#61 Clean Data with Copilot
#62 Regular Copilot in Excel
#63 Let Copilot Write Python Code for You in Excel
Bonus Tip: Create a Word Cloud with One Prompt
#64 Regular Copilot Tasks Do Not Work in Advanced Analysis
#65 Create 10 Visualizations with “Think Deeper”
#66 View all Python Code in the Python Editor
#67 Your Chat History is Barely Available, and Only to You
#68 Using Copilot for Text Sentiment Analysis
#69 Create a Custom Agent For Use in Excel
#70 Use Copilot to Instantly Summarize any YouTube Video
#71 Copilot in OneDrive to Analyze Financial Statements
#72 Fixing Excel Functions Without Breaking the Past
#73 TRIMRANGE or Dot Notation for Dynamic Ranges
#74 Use F4 for Absolute Reference or Repeating Commands
Bonus Tip: Use a Named Range Instead of Absolute References
#75 Quickly Convert Formulas to Values
Bonus Tip: Skip Blanks While Pasting
#76 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
#77 Audit a Worksheet With Spreadsheet Inquire
#78 Discover New Functions by Using fx
#79 Use Function Arguments for Nested Functions
#80 Calculate Nonstandard Work Weeks
Bonus Tip: Use WORKDAY.INTL for a Work Calendar
#81 Turn Data Sideways with a Formula
Bonus Tip: Protect Rows with an Old-Style Array Formula
#82 Handle Multiple Conditions in IF
Bonus Tip: Use Boolean Logic
#83 Pattern Matching with RegEx
Bonus Tip: Learn the Basics of RegEx Patterns
Bonus Tip: A Real-World Fix: Replace Non-breaking Spaces
Bonus Tip: RegEx Now Works in XLOOKUP and XMATCH!
#84 Troubleshoot VLOOKUP
#85 Use a Wildcard in VLOOKUP or XLOOKUP
Bonus Tip: VLOOKUP to Two Tables
#86 Twelve Benefits of XLOOKUP
#87 Preview What Remove Duplicates Will Remove
#88 Replace Nested IFs with a Lookup Table
Bonus Tip: Match the Parentheses
#89 Suppress Errors with IFERROR
#90 Handle Plural Conditions with SUMIFS
#91 Geography, Exchange Rate & Stock Data Types in Excel
Bonus Tip: Use Data, Refresh All to Update Stock Data
#92 Get Historical Stock History from STOCKHISTORY
#93 Create Your Own Data Types
#94 TRANSLATE and DETECTLANGUAGE
#95 Dynamic Arrays Can Spill
#96 Sorting with a Formula
Bonus Tip: Sort into a Random Sequence
#97 Filter with a Formula
Bonus Tip: Understanding Array Constants
#98 Formula for Unique or Distinct
Bonus Tip: Use # "The Spiller" to Refer to All Array Results
#99 Other Functions Can Now Accept Arrays as Arguments
#100 One Hit Wonders with UNIQUE
#101 SEQUENCE inside of other Functions such as IPMT
#102 Generating Random Numbers in Excel
#103 Create a Summary Table With the GROUPBY Function
Bonus Tip: Counting with GROUPBY
#104 Moving from GROUPBY to PIVOTBY
#105 Calculate Percent of Total with PERCENTOF Function
Bonus Tip: Replace Ctrl+Shift+Enter with Dynamic Arrays.
#106 Dependent Validation using Dynamic Arrays
#107 Complex Validation Using a Formula
#108 Stack Multiple Arrays
#109 Dropping, Taking, or Choosing from an Array
#110 Reshaping an Array to a Vector and Back
#111 Getting the UNIQUE of a Rectangular Range
#112 Shuffling and Dealing a Deck of Cards
#113 Use A2:INDEX() as a Non-Volatile OFFSET
#114 Number the Visible Rows in a Filtered Data Set
#115 Unhide Multiple Worksheets
#116 Write Your Data with the Action Pen
#117 Draw Perfect Shapes Using the New Draw-and-Hold Gesture
#118 Many Task Panes Now Collapse into a Tab Strip
#119 How to Provide Usable Feedback to the Excel Team
#120 Date Tricks in Excel
#121 Use the LET Function to Re-Use Variables in a Formula
#122 Store Complex Formula Logic in LAMBDA function
Bonus: Importing LAMBDAs from GitHub
#123 Combining Subformulas into a Single Lambda
Bonus Tip: Perform a Loop with a Recursive LAMBDA
Bonus Tip: Branching in a LAMBDA
#124 New LAMBDA Helper Functions
#125 Find Largest Value That Meets One or More Criteria
Bonus Tip: Concatenate a Range by Using TEXTJOIN
#126 Text Before or After a Specific Delimiter
#127 Split Text into Words Using TEXTSPLIT
#128 Less CSV Nagging
#129 Better AutoComplete
#130 Protect All Formula Cells
#131 Back into an Answer by Using Goal Seek
#132 Do 60 What-If Analyses with a Sensitivity Analysis
Bonus Tip: Create a Data Table from a Blank Cell
#133 Find Optimal Solutions with Solver
#134 Interpolate between a starting and ending number
#135 Improve Your Macro Recording
Bonus Tip: Use TypeScript to Write Macros for Excel Online
#136 Clean Data with Power Query
Bonus Tip: Tame the Sequence of Refresh All
Bonus Tip: Data Profiling in Power Query
#137 Use Fuzzy Match in Power Query
#138 Data From Picture is Not Power Query
#139 Build a Pivot Table on a Map Using 3D Maps
#140 The Forecast Sheet Can Handle Some Seasonality
#141 Fill in a Flash
#142 Format as a Façade
#143 Show All Open Workbooks in the Windows Taskbar
#144 Paste to Another Computer Using Cloud Clipboard
#145 Surveys & Forms in Excel
#146 Use the Windows Magnifier
#147 New Scrolling Tricks
#148 Prevent Default Conversions
#149 Check Performance in Excel
#150 See Intermediate Calculations
#151 Stale Value Formatting
#152 Sort by Font Width?!
#153 Get the Countries from a Column of Phone Numbers
#154 Word for Excellers
Bonus Tip: Merge Shapes
Bonus Tip: Use the Eye Dropper
#155 Avoid Whiplash with Speak Cells
Bonus Tip: Provide Feedback with Sound
Bonus Tip: A Great April Fool’s Day Trick
#156 Temporarily Make Formula Bar Font Size Larger
#157 Circle Invalid Data Automatically
#158 The Advanced Filter Treats Criteria as "Begins With"
#159 Generate All Combinations Using BASE Function
#160 Why a Dark Rectangle Around the Formula Bar?
#161 High Contrast Color Picker Avoids Accessibility Issues
#162 Unhide One Specific Column Without Unhiding Others
#163 How to Enable ActiveX Controls
#164 Using the Date Picker in Excel Online
#165 Export a PDF from Excel (Even Just One Page!)
Bonus: Export Only One Page to PDF
Bonus: Include Gridlines and Headers in the PDF
#166 Customize the Quick Access Toolbar
Bonus Tip: Sometimes, You Don't Want the Gallery
Bonus Tip: Show QAT Below the Ribbon
#167 Create Your Own QAT Routines Using VBA Macros
Bonus Tip: Force Excel to Compile VBA on Each Open of the Workbook
Bonus Tip: Settings in the Excel Options Menu
#168 Favorite Keyboard Shortcuts
#169 Ctrl+Click to Unselect Cells
#170 Collapse the Search Box
#171 More Excel Tips
#172 The MrExcel Message Board

You may also like

Customer Reviews

Average Ratings

Write a review