159 Chapters

What's new as of this 2024 edition:

Dedication

About the Author

About the Illustrators

Foreword

Sample File Downloads

What's new as of this 2024 edition:

#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/Under

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

#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

Bonus Tip: 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/Down Markers

Bonus Tip: Compare Two Lists by Using Go To Special

#43 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

#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: Force Excel to Compile VBA on Each Open of the Workbook

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

Index

MrExcel 2024: Igniting Excel Mastery with the Latest Tips & Tricks

What do you get with eBook?

Key benefits

Description

Who is this book for?

What you will learn

Product Details

What do you get with eBook?

Product Details

Frequently bought together

Table of Contents

Recommendations for you

About the authors

FAQs

MrExcel 2024: Igniting Excel Mastery with the Latest Tips & Tricks

What do you get with eBook?

Key benefits

Description

Who is this book for?

What you will learn

Product Details

What do you get with eBook?

Product Details

Packt Subscriptions

Frequently bought together

Table of Contents

Recommendations for you

About the authors

FAQs