Table Of ContentExploring Microsoft Excel’s Hidden
Treasures
Turbocharge your Excel proficiency with expert
tips, automation techniques, and overlooked features
David Ringstrom, CPA
BIRMINGHAM—MUMBAI
Exploring Microsoft Excel’s Hidden Treasures
Copyright © 2022 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted
in any form or by any means, without the prior written permission of the publisher, except in the case
of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information
presented. However, the information contained in this book is sold without warranty, either express
or implied. Neither the author, nor Packt Publishing or its dealers and distributors, will be held liable
for any damages caused or alleged to have been caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and
products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot
guarantee the accuracy of this information.
Group Product Manager: Alok Dhuri
Publishing Product Manager: Shweta Bairoliya
Senior Editor: Nithya Sadanandan
Technical Editor: Pradeep Sahu
Copy Editor: Safis Editing
Project Coordinator: Manisha Singh
Proofreader: Safis Editing
Indexer: Hemangini Bari
Production Designer: Shyam Sundar Korumilli
Marketing Coordinator: Deepak Kumar and Rayyan Khan
Business Development Executive: Puneet Kaur
First published: August 2022
Production reference: 0290822
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham
B3 2PB, UK.
ISBN 978-1-80324-394-8
www.packt.com
– For Erin, Rachel, and Lucas—and Ringo too
Co n t r i b u t o r s
About the author
David Ringstrom exclaimed “Well, this is a stupid program, you can’t do anything with it” the first
time that he launched Lotus 1-2-3 in 1987, unaware that pressing the slash key displayed the menu.
That moment sealed his fate as he is now a nationally recognized spreadsheet expert. In 1991, David
started a spreadsheet consulting practice that he still runs today. David has taught over 2,000 webinars
and published hundreds of articles, all on Excel, and he imparts spreadsheet skills to thousands of
college students each year. He is the author or coauthor of five books and the technical editor of over
40 books. He is a certified public accountant and a graduate of Georgia State University and has served
in the United States Navy.
Thank you to my training and consulting clients for decades of Excel questions
that inspired this book. I’m grateful to my family for their unending support.
A special thank you goes to my editors Nithya Sadanandan and Manisha
Singh, and technical reviewers John Christovassilis and Alan Murray, for
bearing with my creative process and enhancing this book. Also special thanks
to all of the Packt team working behind the scenes to bring this book to life!
About the reviewers
John Christovassilis has a double major Bachelor of Science in computer science and physics (Queen
Mary, Univ. of London), a Master of Science in information security (Royal Holloway, Univ. of London),
and a Master of Science in airport planning and management (Loughborough Univ.). He is a Microsoft
Certified Trainer (MCT), Microsoft Certified Professional (MCP), Microsoft Office Specialist (MOS)
Master Instructor, and is ITIL Foundation certified. John is a data analysis expert, having produced
Excel reports with detailed metrics, macros, complex formulas, functions, charts, pivot tables, and
so on for 25+ years. He also has 15+ years’ experience consulting, managing, and delivering custom
learning and technical training solutions to clients worldwide.
Alan Murray is a Microsoft MVP, Excel trainer, and author. He has been helping people in Excel
for over 20 years. He loves training and the joy he gets from knowing he is making people’s working
lives easier. Alan runs his own blog, Computergaga (https://computergaga.com), and writes
for multiple other websites. His YouTube channel (Computergaga) has over 600 videos and over 40
million views. He organizes a free monthly Excel meetup in London where anyone can come learn
Excel, chat, and enjoy each other’s company.
Table of Contents
Preface xv
Part 1: Improving Accessibility
1
Implementing Accessibility 3
Technical requirements 3 Merge Cells feature 17
Making Excel more accessible 4 Minimizing the use of watermarks,
headers, and footers 19
Finding worksheet functions 4
Working carefully with color 23
Microsoft Search box 7
Using the Table feature 24
Help tab of Excel’s Ribbon 9
On-demand PivotTables and charts 10 Accessibility Checker feature 25
Accessibility Reminder add-in 26
Implementing accessibility
within spreadsheets 14 Examples of inaccessible spreadsheets 28
Assign worksheet names 14 Summary 30
2
Disaster Recovery and File-Related Prompts 33
Technical requirements 33 Accessing prior AutoSave versions 42
Undo and Redo 34 Resolving the disabled AutoSave 43
Permanently disabling AutoSave 44
The AutoRecover feature 35
Excel for Windows AutoRecover 36 The Always create backup setting 45
Excel for macOS AutoRecover 38 Repairing damaged workbooks 46
AutoSave with OneDrive 39 Removing excess formatting 48
Saving files to OneDrive 40
viii Table of Contents
Warning prompts when Trusted documents 51
opening workbooks 49 CSV prompt 53
Protected View 49
Summary 54
3
Quick Access Toolbar Treasures 55
Technical requirements 56 PDF shortcuts 67
Exploring the Quick Access Toolbar 56 Commands Not in the Ribbon 68
Customizing Excel’s Ribbon 59 Enhanced commands 68
Understanding the nuances of Quick Workbook-specific toolbars 70
Access Toolbar shortcuts 59
Creating shortcuts for Excel macros 71
Repositioning the Quick Access Toolbar
Transferring your Quick Access
commands 60
Toolbar between computers 74
Removing Quick Access Toolbar commands 61
Restoring legacy features 75
Resetting the Quick Access Toolbar 62
The Alt-Number Pad nuance 63 The Full screen feature 75
Full-screen mode 76
Adding Ribbon commands to
Restoring the Full Screen feature 78
the toolbar 64
The Share Workbook feature 78
Center text 65
Show Changes 79
Locking/unlocking worksheet cells 66
Summary 81
4
Conditional Formatting 83
Technical requirements 84 A Date Occurring 91
Formatting versus Conditional Duplicate Values 93
Formatting 84
Top and bottom rules 94
Highlight cell rules 86
Data Bars 95
Greater Than 87 Color Scales 97
Less Than 88
Icon Sets 98
Between 88
Custom rules 101
Equal To 89
IS functions 101
Text That Contains 90
CELL function 103
Table of Contents ix
Logical tests 105 Troubleshooting Conditional
Formatting 111
Managing rules 106
No formatting appears 111
Editing existing rules 106
Changing the order of the rules 113
Applies to ranges 107
Wingdings font 114
Creating a Conditional Formatting legend 108
Removing Conditional Formatting 109 Summary 115
Part 2: Spreadsheet Interactivity and Automation
5
Data Validation and Form Controls 119
Technical requirements 120 Custom rules 146
Introducing Data Validation 120 Protecting Data Validation cells 150
Settings tab 124 Auditing Data Validation cell inputs 151
Input Message tab 125
Enabling the Developer tab 151
Error Alert Message tab 125
Exploring Form Controls 152
Removing Data Validation 129
The INDEX function 154
Implementing Data Validation rules 131
Combo Box Form Control 154
Any value 131
Checkboxes and Option Buttons 156
Whole Numbers 131
Creating Checkboxes 156
Decimal 132
Creating Option Buttons 157
List 132
Managing Form Controls 158
Date 141
Time 143 Summary 159
Text length 145
6
What-If Analysis 161
Technical requirements 162 Setting the scene for a scenario 165
The PMT function 162 Creating scenarios 166
Showing scenarios 168
The CUMIPMT function 163
Scenario reports 169
Understanding the Scenario
Merging scenarios 173
Manager feature 164
