MrExcel 2024 E-Book

MrExcel's Holy Macro! Books

• Herausgeber: Packt Publishing
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 429

Veröffentlichungsjahr: 2024

Beschreibung

Unlock Excel 2024's potential with this essential guide, featuring 150 advanced techniques designed to elevate your skills. Whether you're automating tasks, creating dynamic dashboards, or utilizing Excel's new AI tools, this guide provides clear, practical instructions. Each chapter is crafted to help you navigate Excel's latest features, from data manipulation to complex formulas and Python integration.
This guide offers a hands-on approach, with real-world examples that demonstrate how to apply Excel’s capabilities in practical scenarios. You'll learn to optimize your workflow, enhance data visualization, and make informed decisions based on your analysis. The guide is perfect for intermediate to advanced users looking to stay ahead of the curve and maximize their productivity.
By focusing on both the "how" and the "why" of each feature, this guide ensures you not only understand the technical steps but also grasp the underlying principles that make these tools powerful. Whether you're a data analyst, financial professional, or business manager, this guide equips you with the skills to transform your Excel experience, making your work more efficient, accurate, and insightful.

Leseprobe

What's new as of this 2024 edition:

"Bonus Tip: Remove Filter Items Using Search Box" on page 11

"Bonus Tip: The Consolas Font Makes it Easy to Tell Zero from the Letter O" on page 31

"#27 Display Online Pictures In a Cell Using a Formula" on page 72

"#28 Place Pictures In Cell From Local Computer" on page 76

"#46 Using Artificial Intelligence with Copilot for Excel" on page 135

"#47 Ask OpenAI Questions from Excel Using Excel Labs" on page 141

"#49 Pivot Table with Sum and Average Total Rows (MDX)" on page 147

"Bonus Tip: Sort into a Random Sequence" on page 208

"#81 Create a Summary Table With the GROUPBY Function" on page 215

"Bonus Tip: Counting with GROUPBY" on page 217

"#82 Moving from GROUPBY to PIVOTBY" on page 218

"#83 Calculate Percent of Total with PERCENTOF Function" on page 218

"#92 Number the Visible Rows in a Filtered Data Set" on page 228

"#98 Using Python in Excel" on page 231

"Bonus Tip: Excel Pre-Loads Many Python Libraries" on page 233

"#99 Python for K-Means Clustering of Excel Data" on page 234

"#102 Combining Subformulas into a Single Lambda" on page 243

"#116 Data From Picture is Not Power Query" on page 285

"#128 Checkboxes" on page 301

"#129 Prevent Default Conversions" on page 302

"#130 Check Performance in Excel" on page 303

"#131 See Intermediate Calculations" on page 304

"#132 Stale Value Formatting" on page 305

"#133 Sort by Font Width?!" on page 306

"#134 Get the Countries from a Column of Phone Numbers" on page 307

"#137 Temporarily Make Formula Bar Font Size Larger" on page 314

"#138 Oddity 1: The Year 2025 is the Square of 45" on page 314

"#139 Circle Invalid Data Automatically" on page 315

"#140 The Advanced Filter Treats Criteria as "Begins With"" on page 316

"#141 Generate All Combinations Using BASE Function" on page 317

"#142 Why a Dark Rectangle Around the Formula Bar?" on page 318

"#143 Unhide One Specific Column Without Unhiding Others" on page 319

"Bonus Tip: Force Excel to Compile VBA on Each Open of the Workbook" on page 331

What's was new in 2022:

"#10 Search While Using File Open" on page 33

"#11 Show Changes From Last 60 Days" on page 35

"Bonus Tip: Data Validation Partial Matching Added in 2022" on page 47

"Bonus Tip: Replace Blank Values Cells With Zero" on page 100

"XLOOKUP Benefit 9: Return All 12 Months in a Single Formula!" on page 187

"#79 Generating Random Numbers in Excel" on page 214

"#86 Stack Multiple Arrays" on page 222

"#87 Dropping, Taking, or Choosing from an Array" on page 223

"#88 Reshaping an Array to a Vector and Back" on page 225

"#89 Getting the UNIQUE of a Rectangular Range" on page 226

"#90 Shuffling and Dealing a Deck of Cards" on page 226

"#103 New LAMBDA Helper Functions" on page 253

"#105 Text Before or After a Specific Delimiter" on page 259

"#106 Split Text into Words Using TEXTSPLIT" on page 259

"#112 Interpolate between a starting and ending number" on page 271

"#124 Paste to Another Computer Using Cloud Clipboard" on page 297

"#127 New Scrolling Tricks" on page 300

"25. Ctrl+Shift+F1 for Full Screen Mode" on page 334

Holy Macro! BooksPO Box 541731, Merritt Island FL 32954

MrExcel 2024 — Igniting Excel

© 2024 by Tickling Keys, Inc.

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without permission from the publisher.

All terms known in this book known to be trademarks have been appropriately capitalized. Trademarks are the property of their respective owners and are not affiliated with Holy Macro! Books

Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

First Publication: January 19, 2024

Authors: Bill Jelen

Copy Editor: Kitty Wilson

Tech Editors: Roger Govier in 2020, Bob Umlas in 2021, 2022, and 2024

Formula Guru: Richard Simpson.

Indexer: Nellie Jay

Compositor: Jill Cabot

Illustrations: Cartoon Bob D‘Amico, George Berlin, Walter Moore, Bobby Rosenstock, Chad Thomas

Photography: Sean Carruthers, Mary Ellen Jelen, Bill Jelen

Published by: Holy Macro! Books, PO Box 541731, Merritt Island FL 32954

Distributed by Independent Publishers Group, Chicago, IL

ISBN 978-1-61547-079-2 (print) 978-1-61547-171-3 (digital)

Library of Congress Control Number: 2024931569

Version 20240718a

Table of Contents

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

Dedication

Dedicated to the ABSN workout groups "Rocking the 80's", "Dojo at Dawn", "Saturday Supersense", and "Fittin' in Some Fitness". I began a fitness journey in 2023 and found the Supernatural VR app in the Meta Quest headset. Thanks to Roxana and Johnny for starting the Facebook group, ABSN - Adventures by Supernatural Ninjas and to my regular workout crew of Angela, Cris, Jenni, Jim, Johnny, Justine, Karyn, Katie, Kelly, Kikas, Lauren, Leeann, Luke, Mallory, Mariah, Micah, Michelle, Morgan, Nellie, Roxana, Sherry, Stacie, Susan, and Wendy. If you are already using Supernatural VR, join me in the app. If you want to try Supernatural VR, reach out to [email protected] and I will send you a 30-day free trial!

About the Author

Bill Jelen is the host of MrExcel.com and the author of 68 books about Microsoft Excel including Excel Gurus Gone Wild andExcel Inside Out for Microsoft Press. He has made over 80 guest appearances on TV’s The Lab with Leo / Call for Help with Leo Laporte and was voted guest of the year on the Computer America radio show. He writes the Excel column for Strategic Finance magazine. He has produced over 2,600 episodes of his daily video podcast Learn Excel from MrExcel.

About the Illustrators

Cartoonist Bob D’Amico creates custom cartoons for business and more. See www.cartoonbob.com for more about his work.

George Berlin is all about delight and wonder! He puts a smile on the world's faces with illustration, animation, and interactive projection art. See more at www.georgeberlin.com.

Walter Moore is famous for his ape cartoons. If you need an illustration of the monkey business at your work, search Bing for Walter Moore Apes.

Chad Thomas is an illustrator who showcases his artwork on his website, www.whiterabbitart.com. His colorful and detailed artwork ranges from pet and people portraits to illustrations for children's books.

Foreword

After 17 years on the road, performing 35 Power Excel seminars a year, I had decided during 2018 to retire from the travel circuit. I still do a few online-only webinars. Check the MrExcel.com homepage for upcoming events.

The book you are reading was the book that I used in those seminars. I would update the book for a new edition of Excel, print 5000 copies and hit the road. This 2024 update is current through January 18, 2024.See what is new in this edition on the next page.

You will see a number of shortlinks in this book in the format mrx.cl/short. The idea is that it will be easier for you to type mrx.cl than a long URL.

Sample File Downloads

The files used in this book are available for download from mrx.cl/2024bookfiles.

What's new as of this 2024 edition:

"Bonus Tip: Remove Filter Items Using Search Box" on page 11

"Bonus Tip: The Consolas Font Makes it Easy to Tell Zero from the Letter O" on page 31

"#27 Display Online Pictures In a Cell Using a Formula" on page 72

"#28 Place Pictures In Cell From Local Computer" on page 76

"#46 Using Artificial Intelligence with Copilot for Excel" on page 135

"#47 Ask OpenAI Questions from Excel Using Excel Labs" on page 141

"#49 Pivot Table with Sum and Average Total Rows (MDX)" on page 147

"Bonus Tip: Sort into a Random Sequence" on page 208

"#81 Create a Summary Table With the GROUPBY Function" on page 215

"Bonus Tip: Counting with GROUPBY" on page 217

"#82 Moving from GROUPBY to PIVOTBY" on page 218

"#83 Calculate Percent of Total with PERCENTOF Function" on page 218

"#92 Number the Visible Rows in a Filtered Data Set" on page 228

"#98 Using Python in Excel" on page 231

"Bonus Tip: Excel Pre-Loads Many Python Libraries" on page 233

"#99 Python for K-Means Clustering of Excel Data" on page 234

"#102 Combining Subformulas into a Single Lambda" on page 243

"#116 Data From Picture is Not Power Query" on page 285

"#128 Checkboxes" on page 301

"#129 Prevent Default Conversions" on page 302

"#130 Check Performance in Excel" on page 303

"#131 See Intermediate Calculations" on page 304

"#132 Stale Value Formatting" on page 305

"#133 Sort by Font Width?!" on page 306

"#134 Get the Countries from a Column of Phone Numbers" on page 307

"#137 Temporarily Make Formula Bar Font Size Larger" on page 314

"#138 Oddity 1: The Year 2025 is the Square of 45" on page 314

"#139 Circle Invalid Data Automatically" on page 315

"#140 The Advanced Filter Treats Criteria as "Begins With"" on page 316

"#141 Generate All Combinations Using BASE Function" on page 317

"#142 Why a Dark Rectangle Around the Formula Bar?" on page 318

"#143 Unhide One Specific Column Without Unhiding Others" on page 319

"Bonus Tip: Force Excel to Compile VBA on Each Open of the Workbook" on page 331

What's was new in 2022:

"#10 Search While Using File Open" on page 33

"#11 Show Changes From Last 60 Days" on page 35

"Bonus Tip: Data Validation Partial Matching Added in 2022" on page 47

"Bonus Tip: Replace Blank Values Cells With Zero" on page 100

"XLOOKUP Benefit 9: Return All 12 Months in a Single Formula!" on page 187

"#79 Generating Random Numbers in Excel" on page 214

"#86 Stack Multiple Arrays" on page 222

"#87 Dropping, Taking, or Choosing from an Array" on page 223

"#88 Reshaping an Array to a Vector and Back" on page 225

"#89 Getting the UNIQUE of a Rectangular Range" on page 226

"#90 Shuffling and Dealing a Deck of Cards" on page 226

"#103 New LAMBDA Helper Functions" on page 253

"#105 Text Before or After a Specific Delimiter" on page 259

"#106 Split Text into Words Using TEXTSPLIT" on page 259

"#112 Interpolate between a starting and ending number" on page 271

"#124 Paste to Another Computer Using Cloud Clipboard" on page 297

"#127 New Scrolling Tricks" on page 300

"25. Ctrl+Shift+F1 for Full Screen Mode" on page 334

#1 Double-Click the Fill Handle to Copy a Formula

You have thousands of rows of data. You’ve added a new formula in the top row of your data set, something like =PROPER(A2&" "&B2), or =TEXTJOIN(" ",A2:B2) as shown here. You need to copy the formula down to all of the rows of your data set.

Many people will grab the Fill Handle and start to drag down. But as you drag down, Excel starts going faster and faster. There is a 200-microsecond pause at the last row of data. 200 microseconds is long enough for you to notice the pause but not long enough for you to react and let go of the mouse button. Before you know it, you’ve dragged the Fill Handle way too far.

The solution is to double-click the Fill Handle! Go to exactly the same spot where you start to drag the Fill Handle. The mouse pointer changes to a black plus sign. Double-click. Excel looks at the surrounding data, finds the last row with data today, and copies the formula down to the last row of the data set.

In the past, empty cells in the column to the left would cause the “double-click the Fill Handle” trick to stop working just before the empty cell. But as you can see below, names like Madonna, Cher, or Pele will not cause problems. Provided that there is at least a diagonal path (for example, via B76-A77-B78), Excel will find the true bottom of the data set.

In my live Power Excel seminars, this trick always elicits a gasp from half the people in the room. It is my number-one time-saving trick.

Alternatives to Double-Clicking the Fill Handle

This trick is an awesome trick if all you've done to this point is drag the Fill Handle to the bottom of the data set. But there are even faster ways to solve this problem:

Use Tables. If you select one cell in A1:B112 and press Ctrl+T, Excel formats the range as a table. Once you have a table, simply enter the formula in C2. When you press Enter, it is copied to the bottom.Use a complex but effective keyboard shortcut. This shortcut requires the adjacent column to have no empty cells. While it seems complicated to explain, the people who tell me about this shortcut can do the entire thing in the blink of an eye.

Here are the steps:

1. From your newly entered formula in C2, press the Left Arrow key to move to cell B2.

2. Press Ctrl+Down Arrow to move to the last row with data—in this case, B112.

3. Press the Right Arrow key to return to the bottom of the mostly empty column C.

4. From cell C112, press Ctrl+Shift+Up Arrow. This selects all of the blank cells next to your data, plus the formula in C2.

5. Press Ctrl+D to fill the formula in C2 to all of the blanks in the selection. Ctrl+D is fill Down.

Note: Ctrl+R fills right, which might be useful in other situations.

As an alternative, you can get the same results by pressing Ctrl+C before step 1 and replacing step 5 with pressing Ctrl+V.

Be careful when you are joining text with a date or with currency. Even though your cells are formatted to show a currency symbol and two decimal places, the formula can't see the number formatting. You can explicitly add formatting using the TEXT function as shown here.

Thanks to the following people who suggested this tip: D. Carmichael, Shelley Fishel, Dawn Gilbert, @Knutsford_admi, Francis Logan, Michael Ortenberg, Jon Paterson, Mike Sullivan and Greg Lambert Lane suggested Ctrl+D. Bill Hazlett, author of Excel for the Math Classroom, pointed out Ctrl+R.

#2 Break Apart Data

You have just seen how to join data, but people often ask about the opposite problem: how to parse data that is all in a single column. Say you wanted to sort the data in the figure below by zip code:

Tip: After March 2022, you could easily isolate the Zip code with =TEXTAFTER(A2," ",-1). See "#105 Text Before or After a Specific Delimiter" on page 259.

Select the data in A2:A99 and choose Data, Text to Columns. Because some city names, such as Sioux Falls, are two words, you cannot break the data at each occurrence of a space. Instead, you need to use a comma to get the city in column A and the state and zip code in column B, so choose Delimited in step 1 of the wizard and click Next.

In step 2 of the wizard, deselect Tab and select Comma. The preview at the bottom of the dialog shows what your data will look like. Click Next.

Aha: For the rest of the day after you use Text to Columns, Excel will remember the choices you've chosen in step 2 of the Convert Text to Columns Wizard. If you copy data from Notepad and paste to Excel, it will be split at the comma. This is often maddening because most days, the data is not parsed at the comma, but for the rest of today, it will be. To fix it, close and re-open Excel.

Step 3 of the wizard asks you to declare each column as General, Text, or Date. It is fine to leave the columns set as General.

After you‘ve split the state and zip code to column B, select B2:B99 and again choose Data, Text to Columns. This time, since each state is two characters, you can use Fixed Width in step 1 of the wizard. To preserve leading zeros in the zip code, select the second column and choose Text as the data type in step 3 of the wizard.

New in 2023: Go to File, Options, Data. Turn off Remove Leading Digits and Convert to a Number.

Tip: A lot of data will work well with Fixed Width, even it doesn‘t look like it lines up. In the next figure, the first three rows are in Calibri font and don‘t appear to be lined up. But if you change the font to Courier New, as in rows 4:7, you can see that the columns are perfectly lined up.

Sometimes, you will find a data set where someone used Alt+Enter to put data on a new line within a cell. You can break out each line to a new column by typing Ctrl+j in the Other box in step 2 of the wizard, as shown below. Why Ctrl+j? Back in the 1980's IBM declared Ctrl+j to be a linefeed. Ctrl+j also can be typed in the Find & Replace dialog box.

There are three special situations that Text to Columns handles easily:

Dates in YYYYMMDD format can be changed to real dates. In step 3 of the wizard, click the column heading in the dialog, choose Date, then choose YMD from the dropdown.If you have negative numbers where the minus sign shows up after the number, go to step 3 of the wizard, click the Advanced Button, and choose Trailing Minus for Negative Numbers.Data copied from a Table of Contents will often have dot leaders that extend from the text to the page number as shown below. In step 2 of the wizard, choose Other, type a period, and then select the checkbox for Treat Consecutive Delimiters as One.

#3 Convert Text Numbers to Numbers Quickly

It sometimes happen that you end up with a long column of numbers stored as text and you need to convert those to real numbers.

During 2020, the logic behind Convert to Number was rewritten. In the past, using this feature could take minutes, as Excel would recalculate the worksheet after each cell was converted to a number. Today, however, it is super-fast. Simply select the range of cells where the first cell is a number stored as text. An on-grid drop-down will appear to the left of the top text number. Open the drop-down menu and choose Convert to Number.

Note that this option only appears if you have File, Options, Formula set to these:

Before Convert to Number was rewritten, my favorite method of converting Text Numbers would be to select the column of text numbers and press Alt+D E F. This would run the column through the defaults of Text to Columns.

For completeness, there is a third method. Select a blank cell and enter the number 1. Copy the 1 to the clipboard. Then select the cells with text numbers. From the Paste drop-down menu, choose Paste Special. In the Paste Special dialog, choose both Values and Multipy. When you multiply the 1 times text numbers, they will convert to real numbers. You can also copy any blank cell and then Paste Special Add. This adds a zero to the text numbers.

#4 Filter by Selection

The filter dropdowns have been in Excel for decades, but there are two faster ways to filter. Most people select a cell in the data, choose Data, Filter, open the dropdown menu on a column heading, uncheck Select All, and scroll through a long list of values, trying to find the desired item.

One faster way is to click in the Search box and type enough characters to uniquely identify your selection. Once the only visible items are (Select All Search Results), Add Current Selection to Filter, and the one desired customer, press Enter.

But the fastest way to Filter came from Microsoft Access. Microsoft Access invented a concept called Filter by Selection. It is simple: find a cell that contains the value you want and click Filter by Selection. The filter dropdowns are turned on, and the data is filtered to the selected value. Nothing could be simpler.

Starting in Excel 2007, you can right-click the desired value in the worksheet grid, choose Filter, and then choose By Selected Cells Value.

Guess what? The Filter by Selection trick is also built into Excel, but it is hidden and mislabeled.

Here is how you can add this feature to your Quick Access Toolbar: Right-click anywhere on the Ribbon and choose Customize Quick Access Toolbar.

There are two large listboxes in the dialog. Above the left listbox, open the dropdown and change from Popular Commands to Commands Not In The Ribbon.

In the left listbox, scroll to the command AutoFilter and choose it.

In the center of the dialog, click the Add>> button. The AutoFilter icon moves to the right listbox, as shown below. Click OK to close the dialog.

That’s right: The icon that does Filter by Selection is mislabeled AutoFilter.

Here is how to use the command: Say that you want to see all West region sales of widgets. First, choose any cell in column B that contains West. Click the AutoFilter icon in the Quick Access Toolbar.

Excel turns on the filter dropdowns and automatically chooses only West from column B.

Next, choose any cell in column E that contains Widget. Click the AutoFilter icon again.

You could continue this process. For example, you could choose a Utilities cell in the Sector column and click AutoFilter.

Caution: It would be great if you could multi-select cells before clicking the AutoFilter icon, but this does not work. If you need to see sales of widgets and gadgets, you could use Filter by Selection to get widgets, but then you have to use the Filter dropdown to add gadgets. Also. Filter by Selection does not work if you are in a Ctrl+T table.

How can it be that this feature has been in Excel since Excel 2003, but Microsoft does not document it? It was never really an official feature. The story is that one of the developers added the feature for internal use. Back in Excel 2003, there was already an AutoFilter icon on the Standard toolbar, so no one would bother to add the apparently redundant AutoFilter icon.

This feature was added to Excel’s right-click menu—but three clicks deep: Right-click a value, choose Filter, then choose Filter by Selected Cell’s Value.

Bonus Tip: Filter by Selection for Numbers Over/Under

What if you wanted to see all revenue greater than $20,000? Go to the blank row immediately below your revenue column and type >19999. Select that cell and click the AutoFilter icon.

Excel will show only the rows of $20,000 or above.

Bonus Tip: Remove Filter Items Using Search Box

What if you wanted to hide all items that contain certain text? Use the Filter Search box to find all matches. Unselect the box for Select All Search Results. This turns off all of the items that contain "C" in this case.

Then, choose Add Current Selection to Filter.

Apparently, since everything in the Current Selection is unchecked, you are adding the unchecked state to the current filter.

It is a cool (but unintuitive) trick.

Fold the corner of this page down, because it is a difficult trick to remember.

#5 Total the Visible Rows

After you’ve applied a filter, say that you want to see the total of the visible cells.

Select the blank cell below each of your numeric columns. Click AutoSum or type Alt+=.

Instead of inserting SUM formulas, Excel inserts =SUBTOTAL(9,…) formulas. The formula below shows the total of only the visible cells.

Insert a few blank rows above your data. Cut the formulas from below the data and paste to row 1 with the label Total Visible.

Now, as you change the filters, even if the data fills up more than one full screen, you will see the totals at the top of your worksheet.

Thanks to Sam Radakovitz on the Excel team for Filter by Selection – not for suggesting Filter by Selection, but for formalizing Filter by Selection! Thanks to Taylor & Chris in Albuquerque for the Over/under technique.

#6 The Fill Handle Does Know 1, 2, 3…

Why does the Excel Fill Handle pretend it does not know how to count 1, 2, 3? The Fill Handle is great for filling months, weekdays, quarters, and dates. Why doesn’t it know that 2 comes after 1?

In case you’ve never used the Fill Handle, try this: Type a month name in a cell. Select that cell. There is a square dot in the lower right corner of the cell. This dot is called the Fill Handle. Hover over the Fill Handle. The mouse cursor changes from a white cross to a black plus. Click the handle and drag right or drag down. The tooltip increments to show the last month in the range.

Note: If it is not working, select File, Options, Advanced. The third checkbox, Enable Fill Handle and Cell Drag and Drop, toggles the Fill Handle.

When you let go of the mouse button, the months will fill in. An icon appears, giving you additional options.

The Fill Handle works great with months or weekdays.

The Fill Handle also works with quarters in many formats. There is even a secret custom list that will sort JFM, AMJ, JAS, OND into sequence (JFM is Jan, Feb, Mar and so on.)

To do both quarters and years, you have to type a number, then Q, then any punctuation (period, space, apostrophe, dash) before the year.

When you type 1 and grab the Fill Handle, Excel gives you 1, 1, 1, … Many people say to enter the 1 and the 2, select them both, then drag the Fill Handle. Here is a faster way.

The secret trick is to hold down Ctrl while dragging. Hold down Ctrl and hover over the fill handle. Instead of the normal icon of a plus sign, you will see a plus sign with a superscript plug sign. When you see the ++, click and drag. Excel fills in 1, 2, 3, ….

Note: Andrew Spain of Spain Enterprise in Huntsville, Alabama taught me a cool variation on this trick. If you start dragging without Ctrl, you can press Ctrl during the drag. A + icon appears at the bottom of the drag rectangle to indicate that you are going to fill instead of copy. If you need a great Excel Consultant, find Andrew at spainenterprise.com

How were we supposed to figure out that Ctrl makes the Fill Handle count instead of copy? I have no idea. I picked up the tip from row 6 at the IMA Meonske seminar in Kent, Ohio. It turns out that Ctrl seems to make the Fill Handle behave in the opposite way: If you Ctrl+drag a date, Excel copies instead of fills.

I‘ve heard another trick: Type 1 in A1. Select A1 and the blank B1. Drag. Excel fills instead of copies.

Right-Click the Fill Handle for More Options

If you right-click and drag the Fill Handle, a menu appears with more options, like Weekdays, Months, and Years. This menu is great for dates.

What if your payroll happens on the 15th and on the last day of the month? Put in both dates. Select them both. Right-click and drag the Fill Handle. When you finish dragging, choose Fill Months.

Teach the Fill Handle a New List

The Fill Handle is a really handy tool. What if you could use it on all sorts of lists? You can teach Excel a new list, provided that you have anywhere from 2 to 255 items. Here is the easy way:

1. Type the list in a column in Excel.

2. Select the list.

3. Select File, Options, Advanced. Scroll almost to the bottom and click Edit Custom Lists.

In the Custom Lists dialog, click Import.

Excel will now understand your list as well as it understands Sunday, Monday, Tuesday. Type any item from the list It does not have to be the first item.

Grab the Fill Handle and drag. Excel fills from your list.

I use this trick for lists that should be in Excel, such as a list of the U.S. states and a list of the letters of the alphabet.

Bonus Tip: Fill Jan, Feb, ..., Dec, Total

A person in one of my seminars wanted to have Jan fill into 13 values: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Total.

While you can edit any custom list that you create, you cannot edit the first four lists in the Custom Lists dialog.

However, if you use the preceding tip to add a new custom list with the 13 values, that list wins. If two custom lists have the value Jan, the lowest one in the dialog box is the one that is used.

If you fiscal year ends March 31, you could set up a list with Apr, May, Jun, ..., Jan, Feb, Mar, Total.

Bonus Tip: Fill 1 to 100,000 in a Flash

What if you have so many items that you can't drag the Fill Handle? Follow these steps:

1. Type the number 1 in a cell.

2. Select that cell.

3. On the Home tab, toward the right, in the Editing group, open the Fill dropdown and choose Series.

4. Select Columns.

5. Enter a Stop Value of 100000.

6. Click OK.

What if you have to fill 100,000 cells of bagel flavors?

1. Type the first bagel flavor in A1.

2. Select A1.

3. Type A100000 in the Name box and press Shift+Enter to select from the current cell to A100000.

4. Home, Fill, Series… and click AutoFill in the Type box. Click OK to fill from the custom list.

Thanks to the person in row 6 at the Meonske Conference in Kent, Ohio, for suggesting this feature.

#7 Fast Worksheet Copy

Yes, you can right-click any sheet tab and choose Move or Copy to make a copy of a worksheet. But that is the very slow way to copy a worksheet. The fast way: Hold down the Ctrl key and drag the worksheet tab to the right.

The downside of this trick is that the new sheet is called January (2) instead of February – but that is the case with the Move or Copy method as well. In either case, double-click the sheet name and type a new name.

Ctrl+drag February to the right to create a sheet for March. Rename February (2) to March.

Select January. Shift+select March to select all worksheets. Hold down Ctrl and drag January to the right to create three more worksheets. Rename the three new sheets.

Select January. Shift+select June. Ctrl+drag January to the right, and you’ve added the final six worksheets for the year. Rename those sheets.

Using this technique, you can quickly come up with 12 copies of the original worksheet quickly.

Illustration: Walter Moore

Bonus Tip: Put the Worksheet Name in a Cell

If you want each report to have the name of the worksheet as a title, use either of these

=TEXTAFTER(CELL("filename",A1),"]")

=TRIM(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,20)) &" Report"

The CELL() function in this case returns the full path\[File Name]SheetName. By looking for the closing square bracket, you can figure out where the sheet name occurs.

If you plan on using this formula frequently, set up a book.xltx as described in "#8 Use Default Settings for All Future Workbooks" on page 24. In book.xltx, go to Formulas, Define Name. Use a name such as SheetName with a formula of =TEXTAFTER(CELL("filename",book.xltx!$A$1),"]"). Then, in any new workbook =SheetName&" Report" will work.

Bonus Tip: Add a Total Row and a Total Column with One AutoSum

Say that you want to add a total row and a total column to a data set. Select all the numbers plus one extra row and one extra column. Click the AutoSum icon or press Alt+=.

Excel adds SUM functions to the total row and the total column as shown in the figure below.

Bonus Tip: Power Up the Status Bar Statistics

When you select two or more numeric cells, the total appears in the status bar in the lower right of the Excel window. When you see a total, right-click and choose Average, Count, Numerical Count, Minimum, Maximum, and Sum. You can now see the largest, smallest, and average just by selecting a range of cells.

Aha!: Left-click any number in the status bar to copy that number to the clipboard.

Caution: Here is a fun fact: The numbers in the status bar are shown in the number format of the active cell. This is generally very useful and allows the status bar to show the Min and Max date as a date for example. But in one very confusing trick, someone had applied a crazy number format to hide the negative sign from a number to the top cell in a range. If you selected the range from the top, you had one number as the Sum in the status bar. If you selected from bottom to top, you had a different number in the status bar. It through a lot of really smart Excel people for a loop. For details, see Episode 2566 at the MrExcel YouTube channel.

Bonus Tip: Change All Sheets with Group Mode

Any time your manager asks you for something, he or she comes back 15 minutes later and asks for an odd twist that wasn't specified the first time. Now that you can create worksheet copies really quickly, there is more of a chance that you will have to make changes to all 12 sheets instead of just 1 sheet when your manager comes back with a new request.

I will show you an amazingly powerful but incredibly dangerous tool called Group mode.

Say that you have 12 worksheets that are mostly identical. You need to add totals to all 12 worksheets. To enter Group mode, right-click on any worksheet tab and choose Select All Sheets.

The name of the workbook in the title bar now indicates that you are in Group mode.

Anything you do to the January worksheet will now happen to all the sheets in the workbook.

Why is this dangerous? If you get distracted and forget that you are in Group mode, you might start entering January data and overwriting data on the 11 other worksheets!

When you are done adding totals, don’t forget to right-click a sheet tab and choose Ungroup Sheets.

Bonus Tip: Create a SUM That Spears Through All Worksheets

So far, you have a workbook with 12 worksheets, 1 for each month. All of the worksheets have the same number of rows and columns. You want a summary worksheet in order to total January through December.

To create it, use the formula =SUM(January:December!B4).

Copy the formula to all cells and you will have a summary of the other 12 worksheets.

Caution: I make sure to never put spaces in my worksheet names. If you do use spaces or punctuation, the formula would have to include apostrophes, like this: =SUM('Jan 2025:Mar 2025'!B4).

Tip: If you use 3D spearing formulas frequently, insert two new sheets, one called First and one called Last. Drag the sheet names so they create a sandwich with the desired sheets in the middle.Then, the formula is always =SUM(First:Last!B4).

Here is an easy way to build a 3D spearing formula without having to type the reference: On the summary sheet in cell B4, type =SUM(. Using the mouse, click on the January worksheet tab. Using the mouse, Shift+click on the December worksheet tab. Using the mouse, click on cell B4 on the December worksheet. Type the closing parenthesis and press Enter.

Bonus Tip: Use INDIRECT for a Different Summary Report

Say that you want to build the following report, with months going down column A. In each row, you want to pull the grand total data from each sheet. Each sheet has the same number of rows, so the total is always in row 12.

The first formula would be =January!B12. You could easily copy this formula to columns C:F, but there is not an easy way to copy the formula down to rows 5:15.

The INDIRECT function evaluates text that looks like a cell reference. INDIRECT returns the value at the address stored in the text. In the next figure, a combination of the ADDRESS and COLUMN functions returns a series of text values that tell Excel where to get the total.

Wrap the previous formula in =INDIRECT() to have Excel pull the totals from each worksheet.

Caution: INDIRECT will not work for pulling data from other workbooks. Search the Internet for Harlan Grove PULL for a VBA method of doing this.

Thanks to Othneil Denis for the 3D formula tip, Olga Kryuchkova for the Group mode tip, and Al Momrik for status bar.

#8 Use Default Settings for All Future Workbooks

Do you have favorite worksheet settings in Excel? I do. There are things I do to every new workbook I create.

In a few minutes, you can teach Excel your favorite settings. Then, every time you create a new workbook with Ctrl+N or insert a new worksheet, the worksheet will inherit all of your favorite settings.

The key step is to save the workbook as a template into a specific folder with two specific names.

Start with a blank workbook with a single worksheet.

Apply all your favorite settings. There are dozens of possibilities. Here are a few that I use:

On the Page Layout tab, change the Scale to Fit so the Width is 1 page. Leave Height set to Automatic and Width set to 1 Page.

Create a custom header or footer. Use the dialog launcher in the bottom right of the Page Setup group. Go to the Header/Footer tab. Choose Custom Footer. Type whatever is your company standard in the footer.

Create custom margins. I like narrow margins – even more narrow than the built-in Narrow margin settings. I’ve been using 0.25-inch margins since the 1990s, and they‘re automatically set for me because I‘ve added that to my template.

Choose a theme. I like the colors from Slipstream, but I prefer the Effects from Office 2007–2010.

When you set a pivot table default theme, it only applies to the current workbook. Excel never saves your preference. Create a tiny two-cell data set. Create a pivot table. Change the default formatting. Delete the pivot table and the data set. The template will remember the setting.

Would you use cell styles more often if they weren’t so ugly? Do you hate that input cells are orange? Go to Cell Styles, right-click Input, and choose Modify as shown below. Click the Format button and choose a different input color.

If you develop some favorite functions stored as LAMBDA functions in the Name Manager, you can store those functions in your Book.XLTX workbook. Read more in "#101 Store Complex Formula Logic in LAMBDA function" on page 238.

I‘ve just shown you some of my favorite settings. I'm sure you have your own favorites. Maybe you always set up a name to define the tax rate. Add it to your template, and you will never have to set it up again. Turn off gridlines. Do whatever you always do.

Once you‘ve finished customizing your workbook, you need to figure out which file type you use most often. For people who never use macros, this is often XLSX. But I always use macros, so my default file type is XLSM. Maybe you want workbooks to open faster, and you use XLSB. There is a template format related to each of these file types, and you can just change the extension as needed. So, for me, I save the workbook as XLTM. You might save it as XLTX or XLTB.

As soon as you choose one of these file types, the Save As dialog box moves to a templates folder. You need to save the workbook in a different folder.

In the folder bar, type %AppData% and press Enter to get to the AppData\Roaming\ folder on your computer. From there, navigate to Microsoft\Excel\XLSTART.