Exploring Microsoft Excel's Hidden Treasures E-Book

David Ringstrom

• Herausgeber: Packt Publishing
• Kategorie: Fachliteratur
• Sprache: Englisch

Seitenzahl: 574

Veröffentlichungsjahr: 2022

Beschreibung

David Ringstrom coined the phrase “Either you work Excel, or it works you!” after observing how many users carry out tasks inefficiently.
In this book, you’ll learn how to get more done with less effort. This book will enable you to create resilient spreadsheets that are easy for others to use as well, while incorporating spreadsheet disaster preparedness techniques. The time-saving techniques covered in the book include creating custom shortcuts and icons to streamline repetitive tasks, as well as automating them with features such as Tables and Custom Views. You’ll see how Conditional Formatting enables you to apply colors, Cell icons, and other formatting on-demand as your data changes. You’ll be empowered to protect the integrity of spreadsheets and increase usability by implementing internal controls, and understand how to solve problems with What-If Analysis features. In addition, you’ll master new features and functions such as XLOOKUP, Dynamic Array functions, LET and LAMBDA, and Power Query, while learning how to leverage shortcuts and nuances in Excel.
By the end of this book, you’ll have a broader awareness of how to avoid pitfalls in Excel. You’ll be empowered to work more effectively in Excel, having gained a deeper understanding of the frustrating oddities that can arise daily in Excel.

Leseprobe

Exploring 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

Contributors

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

Part 1: Improving Accessibility

1

Implementing Accessibility

Technical requirements

Making Excel more accessible

Finding worksheet functions

Microsoft Search box

Help tab of Excel’s Ribbon

On-demand PivotTables and charts

Implementing accessibility within spreadsheets

Assign worksheet names

Merge Cells feature

Minimizing the use of watermarks, headers, and footers

Working carefully with color

Using the Table feature

Accessibility Checker feature

Accessibility Reminder add-in

Examples of inaccessible spreadsheets

Summary

2

Disaster Recovery and File-Related Prompts

Technical requirements

Undo and Redo

The AutoRecover feature

Excel for Windows AutoRecover

Excel for macOS AutoRecover

AutoSave with OneDrive

Saving files to OneDrive

Accessing prior AutoSave versions

Resolving the disabled AutoSave

Permanently disabling AutoSave

The Always create backup setting

Repairing damaged workbooks

Removing excess formatting

Warning prompts when opening workbooks

Protected View

Trusted documents

CSV prompt

Summary

3

Quick Access Toolbar Treasures

Technical requirements

Exploring the Quick Access Toolbar

Customizing Excel’s Ribbon

Understanding the nuances of Quick Access Toolbar shortcuts

Repositioning the Quick Access Toolbar commands

Removing Quick Access Toolbar commands

Resetting the Quick Access Toolbar

The Alt-Number Pad nuance

Adding Ribbon commands to the toolbar

Center text

Locking/unlocking worksheet cells

PDF shortcuts

Commands Not in the Ribbon

Enhanced commands

Workbook-specific toolbars

Creating shortcuts for Excel macros

Transferring your Quick Access Toolbar between computers

Restoring legacy features

The Full screen feature

Full-screen mode

Restoring the Full Screen feature

The Share Workbook feature

Show Changes

Summary

4

Conditional Formatting

Technical requirements

Formatting versus Conditional Formatting

Highlight cell rules

Greater Than

Less Than

Between

Equal To

Text That Contains

A Date Occurring

Duplicate Values

Top and bottom rules

Data Bars

Color Scales

Icon Sets

Custom rules

IS functions

CELL function

Logical tests

Managing rules

Editing existing rules

Applies to ranges

Creating a Conditional Formatting legend

Removing Conditional Formatting

Troubleshooting Conditional Formatting

No formatting appears

Changing the order of the rules

Wingdings font

Summary

Part 2: Spreadsheet Interactivity and Automation

5

Data Validation and Form Controls

Technical requirements

Introducing Data Validation

Settings tab

Input Message tab

Error Alert Message tab

Removing Data Validation

Implementing Data Validation rules

Any value

Whole Numbers

Decimal

List

Date

Time

Text length

Custom rules

Protecting Data Validation cells

Auditing Data Validation cell inputs

Enabling the Developer tab

Exploring Form Controls

The INDEX function

Combo Box Form Control

Checkboxes and Option Buttons

Creating Checkboxes

Creating Option Buttons

Managing Form Controls

Summary

6

What-If Analysis

Technical requirements

The PMT function

The CUMIPMT function

Understanding the Scenario Manager feature

Setting the scene for a scenario

Creating scenarios

Showing scenarios

Scenario reports

Merging scenarios

The Goal Seek feature

The Data Table feature

Creating a Data Table with one input

Creating a Data Table with two inputs

Creating a Data Table with three inputs

Improving calculation performance

Projecting amounts with the Forecast Sheet feature

Introducing the Solver feature

Summary

7

Automating Tasks with the Table Feature

Technical requirements

Excel’s unwritten rule

What is a Table?

The Format as Table command

The Insert | Table command

Table characteristics

Removing Tables

Table automation opportunities

Calculated Columns

Self-resizing formulas

Using structured references to write formulas

Filtering

Slicers

PivotTable integrity improvements

Self-resizing charts

Other Table techniques

Customizing Table Styles

Transferring Table Styles to other workbooks

Modifying or removing custom Table Styles

Copying and pasting Tables

Keyboard and mouse shortcuts

Troubleshooting Tables

The Include new rows and columns in Table option

Fill formulas in Tables to create Calculated Columns

Deleting rows prevents Table expansion

Summary

8

Custom Views

Technical requirements

Introducing Custom Views

Creating multipurpose worksheets

Creating a base view

Creating a Quarters Only view

Creating an Executive Summary view

Page Layout view conflict

Creating a Custom Views Quick Access Toolbar shortcut

Hiding and unhiding worksheets

Unhiding worksheets with a macro

Hiding and unhiding worksheets with Custom Views

Creating a Summary Only view

Automating filtering

Applying print settings on demand

Updating a Custom View

Removing all Custom Views from a workbook

Custom Views conflicts

Table feature conflicts

Worksheet protection conflicts

Workbook protection

Summary

9

Excel Quirks and Nuances

Technical requirements

Compatibility Checker feature

Compatibility Mode

Save As versus Convert command

Double-click trick for navigating within worksheets

Enter Mode Versus Edit Mode

Excluding weekend dates from charts

Sparklines

Circular references

Enable iterative calculation option

Inquire add-in

Summary

Part 3: Data Analysis

10

Lookup and Dynamic Array Functions

Technical requirements

The VLOOKUP function

The IFNA function

The MATCH function

The SUMIF function

The SUMIFS function

The XLOOKUP function

The if_not_found argument

The match_mode argument

Combining results into a single column

The search_mode argument and returning results to multiple columns

Matching on multiple column criteria

Returning results from multiple cells

The XMATCH function

The UNIQUE function

The SORT function

The FILTER function

The Spilled Range Operator

The dynamic amortization table

The SEQUENCE function

The EOMONTH function

The PPMT function

The SUMIF function

The #SPILL! errors

The RANDARRAY function

Summary

11

Names, LET, and LAMBDA

Technical requirements

Simple volume calculations in Excel

Multiplication

Decision-making functions

Naming worksheet cells

Name Box

Create from Selection

Define Name

Name Manager

Using Names within formulas

Introducing the LET function

Handling formula errors

The IFERROR function

The ISERROR function

Eliminating repetitive calculations

Variables restrictions

Introducing the LAMBDA function

Developing a LAMBDA formula

Naming Parameters and defining the calculation

Evaluating a LAMBDA function

Creating reusable LAMBDA functions

Moving LAMBDA functions between workbooks

Going deeper with LAMBDA functions

Optional LAMBDA Parameters

LAMDBA conflicts and errors

The Advanced Formula Environment add-in

The XBOXVOLUME function

Custom VBA worksheet functions

Summary

12

Power Query

Technical requirements

Introducing Power Query

Creating a list of worksheets

The HYPERLINK function

Making Power Query results into a clickable index

Refreshing Power Query Connections

Updating the worksheet index

Updating source data connections

Automatic report cleanup

Analytical obstacles

Transforming reports

Setting a locale or region

Adding supplemental formulas to Power Query results

Updating a Power Query connection with new data

Breaking Power Query Connections

Extracting data from PDF files

Unpivoting data

Appending and merging data from multiple sources

Connecting to databases and installing ODBC drivers

Establishing an Access database and SQL Server connections

Establishing ODBC connections

Installing ODBC drivers and creating data sources

Summary

Index

Other Books You May Enjoy

Preface

I coined the phrase Either you work Excel, or it works you! over a decade ago to describe how the average user gets pushed around when building and using spreadsheets. Even advanced Excel users can fall prey to what I call the death by a thousand cuts experience, where tasks are accomplished slightly inefficiently or in a repetitive fashion. The cumulative effect of both is that you accomplish less, maybe think Excel is too hard, or perhaps tell yourself you’re not smart enough to use spreadsheets. I’ve heard it all over the years, and so I have included numerous automation and productivity tips in this book to dispel all of those myths and issues.

I quickly exceeded the budgeted page count on every chapter. I am truly grateful to Packt for graciously allowing me to cover each topic in the depth that I felt was warranted. There’s always more that I could have written, but I aimed for a balance of being informative without being overwhelming.

You’ll see a couple mentions of programming code in this book, but almost all of the automation that I discuss involves code-free solutions. I want to empower you to create resilient spreadsheets that have better data integrity and require much less of your time.

Who this book is for

In this book David Ringstrom, CPA has distilled knowledge and questions asked from thousands of webinars and decades of spreadsheet consulting engagements into a concise guide aimed squarely at intermediate, advanced, and even Excel power users -- and those who aspire to such levels. David teaches and consults with accountants, business managers, CFOs, analysts, business owners, insurance specialists, human resource managers, grant managers, budget officers, controllers, the list goes on. If you spend a significant amount of time working in Excel, this book is assured to help boost your productivity. David coined the phrase “Either you work Excel, or it works you!” and has devoted his career to help anyone that uses Excel work smarter, faster, and agilely. The more time that you spend in Excel, the more time this book will save you. Don't let your data bog you down, this curated collection of code-free automation techniques, keyboard shortcuts, and overlooked features will empower you to become the Excel user you've always dreamed of becoming.

What this book covers

Chapter 1, Implementing Accessibility, will focus on accessibility from two perspectives. First, I’ll discuss features in Excel that make it easier to unearth features and worksheet functions, so that you can utilize more of the program, no matter what your abilities are. Second, I’ll discuss accessibility from the perspective of accommodating users that have disabilities, while showing that making spreadsheets more accessible to those that require assistive technologies actually makes spreadsheets easier for all users.

Chapter 2, Disaster Recovery and File-Related Prompts, focuses on bolstering your defenses against spreadsheet crashes and missteps. You’ll see how to build in layers of backups. You’ll understand various warning prompts that can appear when you open an Excel workbook, and choose which prompts you wish to suppress to minimize distractions.

Chapter 3, Quick Analysis Toolbar Treasures, discusses how to create shortcuts for virtually any Excel command. You’ll also see how to unearth legacy features you may have thought were no longer available. You’ll also be able to create custom toolbars that travel with specific workbooks, so that others can benefit as well.

Chapter 4, Conditional Formatting, gives you a deep dive on Excel’s Conditional Formatting feature, which enables you to apply color and graphics to your data based upon conditions that you specify. You’ll get the lowdown on all of the built-in rules, and also see how to create custom rules to suit your needs.

Chapter 5, Data Validation and Form Controls, empowers you to add ease-of-use and internal control features to your workbooks. Data Validation enables you to assign data entry rules to specific cells in your workbooks, as well as on-screen documentation. Data Validation is easy to implement, but enterprising users can easily circumvent the functionality if desired. Conversely, Form Controls enable you create data entry features that cannot be easily defeated.

Chapter 6, What-If Analysis, is one of several chapters focusing on automation. What-If Analysis features are problem solving tools that allow you to calculate a missing input, swap different sets of inputs into a spreadsheet, forecast date-based amounts into the future, and swap different sets of inputs simultaneously through a single formula.

Chapter 7, Automating Tasks with the Table Feature, focuses on one of the best features in Excel for eliminating repetitive tasks and improving data integrity. The Table feature streamlines filtering tasks, automates formula management, and makes Excel features and formulas self-updating when new data is added to a Table.

Chapter 8, Custom Views, brings more automation opportunities into the foreground. The Custom Views feature empowers you to create multipurpose worksheets by hiding and unhiding columns and rows in one fell swoop, applying filter settings, managing print settings, as well as hiding/unhiding worksheets.

Chapter 9, Excel Quirks and Nuances, focuses on certain rough edges in Excel, such as clarifying the difference between Enter mode and Edit mode so that you can avoid frustration when working in certain fields in Excel’s dialog boxes. Navigation nuances, compatibility issues, circular references, and a suite of auditing tools that are only available to certain Excel users are discussed as well.

Chapter 10, Lookup and Dynamic Array Functions, is all about worksheet functions, and mostly focusses on new additions in Microsoft 365 and Excel 2021. Lookup functions can transform how you write spreadsheets, and also vastly improve data integrity. Dynamic array functions are a new class of worksheet function that can automate manual tasks such as sorting, filtering, and removing duplicates. Unlike traditional functions that can only return results to a single cell, dynamic array functions are able to spill results into as many cells as needed. You’ll even see how traditional worksheet functions can take on dynamic array characteristics.

Chapter 11, Names, LET, and LAMBDA, begins with covering the concept of naming cells and ranges in worksheets as groundwork for the LET and LAMBDA functions. The LET function in Microsoft 365 and Excel 2021 allows you to assign names within a formula to inputs and calculations that you can reference elsewhere in the formula, so as to eliminate repetitive portions of calculations. The LAMBDA function in Microsoft 365 allows you to create custom worksheet functions that you can then transfer to other workbooks.

Chapter 12, Power Query, covers the code-free automation opportunities available to transform reports and data from Excel workbooks, database, PDF files, and other sources into self-updating data sets.

To get the most out of this book

You will be able to carry out just about every task in this book in Microsoft 365 for Windows. I do mention a couple of features that are in beta testing that may not be available on your computer just yet, but that should appear in the coming months. Throughout the book I note which features or techniques are not available in Excel for macOS, Excel for the Web, and Excel Mobile. It’s rare, but you will run across a couple of things that you can only do in Excel for macOS but not in Excel for Windows. Much of the book is relevant to users as far back as Excel 2013, but certain features and functions will require Microsoft 365. Some Ribbon tabs may have different names or slightly different configurations in older versions of Excel.

If you run across a feature or function that is missing from your version of Microsoft 365, choose File | Account | Check for Updates and install any updates that are available. The About section of this window will show if you are in the Current or Monthly Enterprise channels, which means new features and updates get pushed to your computer as often as monthly, or if you’re in the Semi-Annual Enterprise channel which means new updates and features will appear in January and July of each year.

Download the example workbooks

Every example that you see in this book is included in the example workbooks that you can download from GitHub at: https://github.com/PacktPublishing/Exploring-Microsoft-Excels-Hidden-Treasures. Any updates to the example workbooks will be uploaded to the GitHub repository. We also have code bundles from our rich catalog of books and videos available at: https://github.com/PacktPublishing/. Check them out!

Download the color images

We also provide a PDF file that has color images of the screenshots and diagrams used in this book. You can download it here: https://packt.link/k7VcU.

Conventions used

There are a number of text conventions used throughout this book.

Code in text: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: “Enter =COLUMN() in any worksheet cell to return the column position within a worksheet, or in this case =COLUMN(DS1) to return the position without physically scrolling to that column.”

A block of code is set as follows:

html, body, #map { height: 100%; margin: 0; padding: 0 }

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

[default] exten => s,1,Dial(Zap/1|30) exten => s,2,Voicemail(u100) exten => s,102,Voicemail(b100) exten => i,1,Voicemail(s0)

Bold: Indicates a new term, an important word, or words that you see onscreen. For instance, words in menus or dialog boxes appear in bold. Here is an example: “Find in Document is an alternative to the Find command located on the Find & Replace menu on the Home tab of Excel’s ribbon.”

Tips, Nuances, and Quirks Appear like this.

Get in touch

Feedback from our readers is always welcome.

General feedback: If you have questions about any aspect of this book, email us at [email protected] and mention the book title in the subject of your message.

Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/support/errata and fill in the form.

Piracy: If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.

If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.

Share Your Thoughts

Once you’ve read Exploring Microsoft Excel’s Hidden Treasures, we’d love to hear your thoughts! Please click here to go straight to the Amazon review page for this book and share your feedback.

Your review is important to us and the tech community and will help us make sure we’re delivering excellent quality content.

Part 1: Improving Accessibility

The Merriam-Webster dictionary offers five different definitions for the word “accessibility”. This part will focus on two of the five definitions, “being in reach” and “easily used or accessed by people with disabilities.” The first chapter addresses both definitions by showing you resources in Excel that can bring more features and functions within your reach, as well as illustrating how adapting spreadsheets for those that have disabilities actually makes things easier for everyone. There are not many things worse to an Excel user than having your work suddenly vanish from the screen, so the second chapter shares ways that you can defend against Excel crashes and better understand some of Excel’s messaging. The third chapter is all about streamlining access to Excel features by way of built-in as well as customizable shortcuts, including showing you the way to revive features you may have thought were lost to time. The fourth chapter is on making data more accessible by overlaying colors and/or shapes with conditional formatting.

The following chapters are included in this part:

Chapter 1, Implementing AccessibilityChapter 2, Disaster Recovery and File-Related PromptsChapter 3, Quick Analysis Toolbar TreasuresChapter 4, Conditional Formatting

1

Implementing Accessibility

There’s nothing quite like breaking your arm to energize your interest in accessibility. I should know because about a month and a half into drafting this book, I broke my right arm in a mountain biking accident. Fortunately, I was able to type even before the surgery that was needed to put my arm back together, so I didn’t have to do a deep dive into voice dictation and other measures. Regardless, even before my accident, I had planned to lead off this book with a discussion on accessibility because I’d realized that anything that makes a spreadsheet easier for people that are color-blind or require assistive technologies also makes the spreadsheet easier for all users. Further, it’s not just spreadsheets that can feel inaccessible. You may sometimes feel that Excel itself is impenetrable. Over the course of the entire book, my goal is to demystify as many aspects of Excel as will fit in the pages I have available.

In this chapter, I’ll discuss design strategies that will improve accessibility for all users, and point out certain Excel features that can improve accessibility within workbooks, but also within the program itself.

This chapter will delve into the following areas:

How to make Excel more accessible regardless of your abilitiesImplementing accessibility within spreadsheetsUsing Excel’s Accessibility Checker featureAccessing Excel’s Accessibility Reminder add-inA brief overview of spreadsheets that are inaccessible because of design strategies

Technical requirements

The example workbook that I used in this chapter is available for download from GitHub at https://github.com/PacktPublishing/Exploring-Microsoft-Excels-Hidden-Treasures/tree/main/Chapter01.

Making Excel more accessible

Although this entire book is centered on making Excel more accessible, I’d like to lead off with some features that can help make Excel feel more approachable. I’ll first show you how to determine whether Excel offers a worksheet function suitable for the calculation or data transformation that you’re considering. I’ll then show how you can transform staid lists of data into helpful reports and charts with just a couple of mouse clicks. After that, I’ll show you hidden ways to initiate Excel tasks with a plain English statement, and then offer a quick overview of Excel’s help resources. Let’s begin by looking at worksheet functions.

Finding worksheet functions

Depending upon your version, Excel has over 500 worksheet functions, which can feel overwhelming. Fortunately, Excel offers some tools you can use to decide whether a worksheet function that you need exists:

Insert Function: This command appears on Excel’s formula bar, the Formula tab of the Ribbon, or you can press Shift + F3 to display the dialog box shown in Figure 1.1:

Figure 1.1 – Insert Function dialog box

Let’s say that you want to compute the total interest on a loan. I explain how to build an amortization schedule in Chapter 10, Lookup Functions and Dynamic Arrays, but there’s a worksheet function you can use instead. Enter cumulative interest in the Search for a function field and then press Enter or click Go. The Select a function list will display CUMIPMT and CUMPRINC. Function descriptions appear beneath the Select a function list. For instance, CUMIPMT “returns the cumulative interest paid between two periods.” Click OK to accept this selection and display the Function Arguments dialog box shown in Figure 1.2:

Figure 1.2 – Function Arguments dialog box

Nuance

The Search for a function field is rather specific. For instance, typing total interest in that field won’t surface the CUMIPMT function, but cumulative interest does. Similarly, car payment won’t make the PMT function available for selection, but loan payment will. If you can’t find what you’re looking for, try an internet search such as Microsoft Excel total interest. Also, notice that the Or select a category list is set to Most Recently Used. This does not mean that functions you type into worksheet cells will appear on the recent version. This list only contains functions that you’ve searched for within the Insert Function dialog box.

I will explain the CUMIPMT function in Chapter 6, What-If Analysis, but I’m mentioning it here to point out two nuances in the Function Arguments dialog box. CUMIPMT has six arguments, but only five can be displayed in the Function Arguments dialog box at a time. You can use the scrollbar on the right to see the sixth argument, which is Type. The second nuance is related to the documentation in the Function Arguments dialog box. The valid choices for the Type field are 0 for payments made at the end of a loan period or 1 for payments made at the beginning. The explanation that appears when you scroll down to the Type field does not provide this information, which in this context at least makes the Function Arguments dialog box inaccessible. Conversely, when you type the CUMIPMT function out directly into a cell, Excel will display a drop-down list detailing the two options when you get to the sixth argument. In general, the Function Arguments dialog box is a useful tool, but as with many aspects of Excel, it does have its quirks and nuances.

Function ScreenTip: A Function ScreenTip appears any time you click inside the parentheses of an Excel formula, as shown in Figure 1.3:

Figure 1.3 – Function ScreenTip

There are some subtleties to be aware of with regard to Function ScreenTips:

Click on any argument name to select that part of the formula. In Figure 1.3, I chose rate within the Function ScreenTip.Click on the function name itself to display help documentation on the function. You can move the Function ScreenTip when it obscures column letters or other information that you wish to see. Grab any corner of the Function ScreenTip with your left mouse button and drag the tip to a new location. This is only a temporary change, as the Function ScreenTip will snap back to its normal location when you start editing the next formula.

Nuance

When working inside a formula, you can press F9 to convert a part of a formula to its calculated value. Once you’ve done so, either press Ctrl + Z to undo the change or press Esc to leave the formula and discard your change. You can generally undo up to your last 100 actions when working in Excel, but you can only undo one action within a worksheet cell or the formula bar. A safer approach is to choose Formulas | Evaluate Formula when verifying formula calculations, but keep in mind that you cannot make any edits within the Evaluate Formula dialog box.

Now let’s see ways that you can unearth Excel commands that are either new to you or whose location you’ve forgotten.

Microsoft Search box

The Microsoft Search box was known as the Tell Me feature in earlier versions of Excel and appears in Excel’s title bar. In Figure 1.4, I selected a cell within my chart data and then typed Create a chart in the Search field:

Figure 1.4 – Search field

Depending upon your request, the Microsoft Search box will offer you a variety of options, including commands for conducting actions:

Best Action: Based upon Excel’s interpretation of your request, this is the action that you’ll want to conduct. Actions: This section presents alternatives to the Best Action.Get Help: This section suggests help topics related to the keyword or phrase that you entered.Find in Document: Choose this option to search your document for the term or phrase that you entered. This is an alternative to choosing Home | Find & Select | Find or pressing Ctrl + F ( + F in Excel for macOS).Files: Recent workbooks you used that Excel determined may be relevant to the keyword or phrase that you entered.

The Microsoft Search box makes Excel more accessible, as it brings commands to you upon request. This turns the normal Excel experience on its head where users don’t remember where a command resides or whether a particular feature even exists.

Nuance

The Microsoft Search box is an effective means for finding commands in Excel, but it does a poor job with worksheet functions. The Insert Function command discussed earlier in this chapter is a more effective approach for unearthing functions. Further, not every command in the menu appears, even when you type it by name. For instance, typing Text to Columns shows alternatives but not the feature itself, which appears on the Data tab. Like many aspects of Excel, blind spots abound.

If you’re sensitive to changes in Excel’s user interface, you can collapse the Microsoft Search box down to an icon:

Choose File | Options | General.Click Collapse the Microsoft Search box by default in the User Interface options section and then click OK.

A magnifying glass icon stays in place in the title bar, which you can click any time you wish to use the Microsoft Search box, or you can type Alt + Q in Excel for Windows. As shown in Figure 1.5, you can also access another version of the Microsoft Search box by right-clicking on any cell in Excel for Windows:

Figure 1.5 – Context menu-based search option

You can enter search terms into the context menu in the same fashion as the Microsoft Search box at the top of the screen. Now, let’s see how you can get more help in Excel.

Help tab of Excel’s Ribbon

The Help tab first appeared in Excel 2019 and is designed to provide immediate access to several support resources.

Figure 1.6 – Help tab

As shown in Figure 1.6, the Help tab of the Ribbon has the following commands:

Help: Click this command or press F1 to display the Help task pane, which you can use to search for help on any aspect of Excel.

Nuance

Your device must be connected to the internet before you can use any command on the Help menu.

Contact Support: This section requires you to enter a search term and then click Get Help. Relevant articles will appear, below which a Contact Support button enables you to create an online chat session with a Microsoft support agent. If you cancel the chat session, Microsoft will follow up with you via email.Feedback: This command enables you to send a smile to Microsoft for something you like about Excel, a frown for something you don’t like, or to send a suggestion.

Nuance

You may be surprised to learn that the Excel development team at Microsoft takes user feedback seriously. The Suggestion option enables you to not only suggest changes in Excel but also vote on requests by others. For instance, as of this writing, 276 votes were enough to get Microsoft to commit to adding Center Across Selection to the Home | Merge & Center drop-down menu. I’ll explain how to access Center Across Selection later in the Using Center Across Selection instead of merged cells section. The bottom line is, given the hundreds of millions of Excel users around the globe, it truly takes a handful of voices to effect change in Excel. If something is frustrating you about Excel, it’s probably bothered others as well, so take a moment to vote on someone else’s suggestion or post your own.

Show Training: This command supplies instant access to a free video-based library of training materials that often includes downloadable templates so that you can follow along.What’s New: This command enables you to figure out whether any new features have been added to your version of Microsoft 365 recently.Community: This command links to a Microsoft-sanctioned online forum where you can ask and answer questions about Excel. Always be sure to search the forum before posting a new question because often you will find that your question has already been asked and answered.Excel Blog: This command opens a page with up-to-date news about Excel from the development team and is a straightforward way to keep up with new features that have been added recently or that are in development.

Let’s now look at ways to convert a list of data into an instant analysis.

On-demand PivotTables and charts

Excel offers three different approaches that allow any user to quickly transform a list of data into easy-to-understand reports or charts:

Recommended PivotTables: This feature can create an instant report out of a list of data:Select any cell within the list on the World’s Longest Metro Systems worksheet of this chapter’s example workbook.Choose Insert | Recommended PivotTables.Choose any report from the Recommended PivotTables task pane shown in Figure 1.7:

Figure 1.7 – Recommended PivotTables task pane

Nuance

Recommended PivotTables appears as a dialog box in Excel 2021 and earlier. Your version of Microsoft 365 may still have the dialog box as well. New features are pushed out to users in waves, so there can be a delay of 6 months or more before the latest changes to Excel make it to your device.

Any reports that you generate by way of Recommended PivotTables are merely a starting point. You can add or remove fields as needed by way of the PivotTable Fields task pane, which appears when you click within any PivotTable.

Recommended Charts: This artificial intelligence feature analyzes your data and makes suggestions as to which Excel charts are best suited to your needs: Select any cell within the list on the World’s Longest Metro Systems worksheet of this chapter’s example workbook.Click Insert | Recommended Charts.Choose a report from the Recommended Charts tab of the Insert Chart dialog box shown in Figure 1.8, and then click OK. In Excel for macOS, chart recommendations appear in a drop-down menu instead of a dialog box, and no rationale for why the chart is appropriate is offered.

Figure 1.8 – Recommended Charts dialog box

Analyze Data: This feature can be thought of as Recommended Charts on steroids. The feature debuted as Insights and was renamed Ideas before being dubbed Analyze Data. You can not only create reports but also find unusual aspects within a list:Select any cell within the list on the World’s Longest Metro Systems worksheet of this chapter’s example workbook.Click Home | Analyze Data.Choose a report or chart from the Analyze Data task pane, or as shown in Figure 1.9, enter a plain English question such as stations per mile to create a chart that will show the distribution of stations by system length in miles. Depending upon the question you ask, Analyze Data will either create a chart, PivotChart, or PivotTable.

Figure 1.9 – Analyze Data task pane

Nuance

Presently, Analyze Data only works with datasets that have 1.5 million cells or less. The feature works best when your list is formatted as a Table, which I discuss how to do in Chapter 7, Automating Tasks with Tables. Dates in the yyyy-mm-dd format, such as 2024-01-01 for January 1, 2024, will be treated as text, although you can convert these to dates by using the DATEVALUE or VALUE functions, or by using the Text to Columns feature. To use this feature, select the dates that you wish to convert, choose Data | Text to Columns, click Next twice, choose Date, and then specify YMD from the corresponding list, and then click OK. Generally, the Text to Columns feature is used to separate a column of data into two or more columns, but it also works as a handy data transformation tool, especially when dates or numbers are formatted or stored as text.

Now that we’ve discussed some ways to make Excel more accessible, let’s see how to improve accessibility within individual workbooks.

Implementing accessibility within spreadsheets

The good news about spreadsheet accessibility is that a few minor changes to how you work can have a significant impact on both users that require assistive technology and those that don’t. Keeping accessibility top of mind makes spreadsheets easier for everyone. Even better, the techniques are surprisingly simple. As you’ll see, techniques such as naming your worksheets, avoiding merged cells, limiting the use of watermarks, headers, and footers, using color conscientiously, and converting lists to Tables are huge boons to able and disabled users alike.

Assign worksheet names

Every new Excel workbook starts out with at least one worksheet, and the first sheet has a default name of Sheet1. Three ways that you can add more worksheets are as follows:

Click New Sheet, which appears as a + to the right of the worksheet tabs in modern versions of Excel, or as a miniature worksheet tab in older versions of ExcelChoose Home | Insert drop-down menu | Insert SheetPress Shift + F11

The second sheet in a workbook has a default name of Sheet2, the third Sheet3, and so on. Many times, users focus on the content within the sheets and don’t take the time to label the worksheets themselves. As you’ll see in the Check Accessibility feature section, Microsoft flags default sheet names as an accessibility issue, plus the default names make it harder for everyone to locate specific data in a workbook. Here’s how to rename a worksheet tab:

Use any of these three techniques:Double-click on the worksheet tabRight-click on a worksheet tab, and then choose RenameIn Excel for Windows, press F6 to select the current worksheet tab, press Shift + F10 to display the context menu, and then type R to choose RenameType up to 31 characters and then press Enter.

Nuance

The following characters cannot be used within a worksheet tab name:

\, /, *, [, ], and ?

Excel will ignore these characters if you try to type them, just as it ignores any characters beyond the first 31 that you try to type. Most other punctuation is allowed.

Worksheet names should be as specific as possible to make it easier for users to find the data they’re looking for. You can navigate between worksheets in several ways:

In Excel for Windows, press F6 to select the current worksheet tab and then use the left or right arrow keys to navigate to a new sheet, and then press Enter.Right-click on the navigation arrows at the bottom left-hand corner of the Excel window to display the Activate dialog box shown in Figure 1.10. Type the first letter of a sheet name to move purposefully through the list.

Figure 1.10 – Activate dialog box

Nuance

The Activate dialog box only shows visible worksheets in a workbook. Choose Home | Format | Hide & Unhide | Unhide Sheet to unhide any hidden worksheets, or right-click on any worksheet tab and choose Unhide Sheet. If Unhide Sheet is disabled, then most likely there are no hidden worksheets in the workbook. It is possible to use the Visual Basic Editor to set a worksheet to xlSheetVeryHidden, which means the worksheet cannot be unhidden through Excel’s user interface.

Choose Review | Navigation in Microsoft 365 to display the Navigation pane shown in Figure 1.11:

Figure 1.11 – Navigation pane in Microsoft 365

Notice that three ranges are listed on the World’s Longest Metro Systems worksheet:

B1: The first non-blank cell on the worksheetB3:G28: A cluster of text, values, and/or formulasZ500: A random cell that I typed a tip into

The Navigation pane lists every contiguous block of non-blank cells, as well as individual non-blank cells, so you can easily determine where data appears in each worksheet.

Nuance

The Navigation task pane is only functional when you have an internet connection. As of this writing, the Navigation task pane is still in beta testing, so it may or may not be available to you as you read this, but in the worst-case scenario, it will be available in the coming months.

In Excel for Windows, press Ctrl + PgUp to move one worksheet to the left at a time or Ctrl + PgDn to move one worksheet to the right at a time, or in Excel for macOS, press Fn + ⌃ + ↓ to move one worksheet to the right at a time or Fn + ⌃ + ↑ to move one worksheet to the left.

Quirk

You cannot assign the name History to an Excel worksheet. The Track Changes feature in Excel creates a History worksheet, and so that name is a reserved word that you cannot use in Excel. You can use the word History with a space at the beginning or end, but be mindful in doing so, as users may not realize that the tab name has an extra space and could end up frustrated when trying to write formulas by typing the sheet name directly.

Let’s now explore a divisive feature that I find Excel users either absolutely love or absolutely hate.

Merge Cells feature

If there’s ever a reality TV series where we get to vote features out of Excel, Merge Cells is first on my list. I realize that these are fighting words for some users who rely heavily on merged cells, but from an accessibility standpoint, merged cells should be avoided whenever possible. First, merged cells can wreak havoc with assistive technology such as screen readers. Second, merged cells also wreak havoc with ordinary tasks you may try to conduct in Excel. Few things in life set my teeth on edge quite like the prompt shown in Figure 1.12. This prompt can appear even when you’re making a change that is seemingly unrelated to merged cells because the action will affect rows or columns that intersect with the merged cells:

Figure 1.12 – Merged cells error prompt

In the vein of not that you would but you could, here’s an example of how to merge cells:

Select cells B4:G5 on the Merge Cells vs. Center Across worksheet of this chapter’s example workbook and then choose Home | Merge & Center.The prompt shown in Figure 1.13 appears because we’re trying to merge more than one row of data at a time. If you click OK, Excel will merge and center cells B4:G5 but will discard the data from row 5. You can click Undo or press Ctrl + Z (⌘ + Z) if you click through the prompt accidentally, or click Cancel to stop the merge process.

Figure 1.13 – Merged cells error prompt

Click the Merge & Center drop-down menu and then choose Merge Across to merge cells B4:G4 and B5:G5 separately and keep the data from each row, as shown in Figure 1.14:

Figure 1.14 – Merge Across

Optional: Choose Home | Center to center data within the merged cells, or press + E in Excel for macOS.

To unmerge cells, simply select a range that includes one or more sets of merged cells and then choose Home | Merge & Center or choose Home | the Merge & Center drop-down menu | Unmerge Cells. As you’ll see in the Using the Table feature section later in this chapter, converting a range of cells to a Table automatically unmerges any cells within the list as well.

Merged cells are often used to center headings across reports, which you can easily conduct in a different manner to make the spreadsheet more accessible to users of every stripe.

Using Center Across Selection instead of merged cells

A hidden but highly effective alternative to merging cells is named Center Across Selection. This helpful feature is buried in the Format Cells dialog box. Let’s say that you want to center the headings in cells B8:B9 of Figure 1.14 across columns B:G:

Select cells B8:G9.Click the Alignment Settings button on the Home tab of the Ribbon, press Ctrl + 1 (⌘ + 1), or choose Home | Format | Format Cells. Activate the Alignment tab if needed.Choose Center Across Selection from the Horizontal list as shown in Figure 1.15, and then click OK:

Figure 1.15 – Center Across Selection

The text is now centered across columns B:G. If you change your mind about centering the text, simply select cells B8:G9 and choose Home | Align Left. Center Across Selection eliminates all of the frustrations that can arise when you merge cells but provides the same effect.

Let’s now look at aspects of Excel that can make certain information inaccessible for viewing, or even editing.

Minimizing the use of watermarks, headers, and footers

Information placed within watermarks, headers, or footers can present a particular challenge for users using assistive technology because the information doesn’t appear within the worksheet itself. However, it’s easy for any user to overlook information stored in these locations because such information is only displayed in certain contexts in Excel. Further, because there isn’t a Watermark command in Excel, it can be tricky for others to know how to remove or edit an existing watermark. A watermark is an identifier, such as a company logo, or a message, such as the words DRAFT or CONFIDENTIAL, that can be overlaid over a worksheet. One approach involves the WordArt feature:

Choose Insert | WordArt, or Insert | Text | WordArt.Click on the worksheet to create a floating object and change the text as needed, as shown in Figure 1.16:

Figure 1.16 – WordArt

Optional: Use the button above the text that looks like an arrow pointing in a circle, as shown in Figure 1.17, to rotate the watermark:

Figure 1.17 – Format Shape task pane and rotation arrow

Optional: Change the transparency of the text by right-clicking on the image and choosing Format Shape | Text Options | Text Fill and then adjust the Transparency setting, as shown in Figure 1.17.

Nuance

Objects that float above the worksheet, such as WordArt and textboxes, can be tricky to format, as you have to pay attention to what is selected. If you see handles around the edge of the object, as shown in Figure 1.17, your formatting changes will affect the object as a whole. If you don’t see the handles, then most likely your formatting changes will affect some or all of the text within the object.

This sort of watermark will float above the worksheet, which means it can obscure text beneath it or confuse screen-reading technology. To remove the watermark, you can click once on the image and then press the Delete key on your keyboard.

A second approach involves placing the watermark in the header of a worksheet. The Header feature enables you to specify text that you wish to display at the top of a printed page or images that you wish to display within the body of the worksheet. The Footer feature enables you to specify text that appears at the bottom of a printed page. The challenge with headers and footers is that the user may be unaware of information stored in these sections unless they choose File | Print or View | Page Layout. Page Layout mode enables you to add a header or footer by clicking on the left, center, or right header and footer fields. A Header & Footer tab then appears in the Ribbon that contains commands that make it easy to craft headers and footers. Choose View | Normal when you’re ready to exit Page Layout mode.

Nuance

Page Layout mode is not compatible with the View | Freeze Panes feature. An alert message will appear if you try to enter Page Layout mode on a worksheet with frozen panes. If you click OK, you will enter Page Layout mode but your worksheet panes will no longer be frozen.

Alternatively, you can use these steps to add a watermark to a header without disrupting frozen worksheet panes, as well as add text to a header or footer:

Choose Page Layout | Print Titles.Click the Header/Footer tab in the Page Setup dialog box.Click Custom Header or Custom Footer.

Nuance

Images that you place in the Header section will appear near the top of your printout, while images that you place in the Footer section will appear near the bottom of the page. You cannot place an image in the center of the printed page in this fashion unless the image is large enough to span the entire printed page.

Select a section and then click the Insert Picture button, which is the second button from the right, as shown in Figure 1.18:

Figure 1.18 – Header dialog box

Make a choice from the Insert Pictures dialog box, which asks whether you want to choose a file from your local drive or an online resource.Once you select an image, a &[Picture] placeholder will appear in the section you chose, as shown in Figure 1.18.Optional: Click Format Picture, which is the last button on the right, to change the size of the picture. You may also wish to click on the Picture tab and change Color to Washout in the Image control section.Click OK as needed to close any open dialog boxes.Choose File | Print to display a print preview, because if you choose View | Page Layout to enter Page Layout mode, any frozen worksheet panes will become undone. You can return to the Page Setup dialog box and change the settings if needed, such as shrinking the dimensions to prevent an image from overrunning the printed page.

To remove headers or footers, choose Page Layout | Print Titles, activate the Header/Footer tab, and then choose (none) from the top of the corresponding drop-down list.

Nuance

Page Layout | Background offers a third approach for creating a watermark. The difference is that the image will repeat throughout Excel’s entire grid, and there is no way to edit the image. If you add an image in this fashion, the Background command toggles to Delete Background.

Any worksheet that has vital information in a watermark, header, or footer, such as CONFIDENTIAL or INTERNAL USE ONLY, should be considered inaccessible because assistive technology cannot access those areas of Excel. Any such information should also be repeated in cell A1, where it can be accessed by assistive technology but also be readily visible to all users of the worksheet.

Let’s now see how color can have an impact on the accessibility of your spreadsheets.

Working carefully with color

Accessibility standards call for colors to have sufficient contrast between background fill and fonts used within cells. One sure-fire way to ensure proper contrast is to use a black background with white text or light shades of gray. Black text on a white background is accessible as well. Conversely, let’s say blue text on a red background can be difficult for anyone to read, much less anyone with vision impairments or color-blindness.

Further, standards call for any indicators in a spreadsheet that are represented by color-only to also have supportive text, as shown in Figure 1.19. As much as 8% of the world’s male population and 0.5% of the female population is color-blind. That means if you work with say nine other people, there’s a good chance at least one person may be color-blind.

Figure 1.19 – An inaccessible list versus an accessible list

The list on the left only uses color to find the status of each project, such that anyone, no matter the level of vision they have, may find themselves struggling to make sense of the data, at least at first. Conversely, the list on the right pairs the color and text together, so that all users can decide the status of each project at once. I discuss how to automate color coding based upon cell contents in Chapter 4, Conditional Formatting, along with an approach where you can combine color coding with cell icons to provide an additional means for identifying types of data.

Let’s now see how the Table feature can improve accessibility within a worksheet.

Using the Table feature

I talk extensively about the Table feature in Chapter 7, Automating Tasks with Tables, so I won’t go into much detail here, but the Table feature is one of the best ways to improve the accessibility of a worksheet. First, you cannot use merged cells within a Table; any commands or options related to merged cells are disabled when your cursor is within a Table.

Nuance

When you convert a range of cells into a Table, any merged cells within the list will be automatically unmerged because merging cells is not compatible with the Table feature.

Enabling the Header Row and First Column options, as shown in Figure 1.20, can particularly improve accessibility for all:

Figure 1.20 – Table options

The Header Row allows you to place meaningful titles in the top row of a list. The titles move up into the worksheet frame when you scroll down past the first row of a Table. Filter arrows appear automatically in the Header Row to enable users to easily collapse the list down to just records of their choice. First Column makes the text in the first column bold but can also be used to help users of assistive technology know that they’re starting out in the first column rather than landing unexpectedly in the middle of a Table. Finally, assigning a meaningful name to the Table by way of Table Design | Table Name helps all users understand at once what type of data is contained within the Table. It is best to also supply a description of the data above the Header Row. As I discuss later in the book, Table Names supply an effortless way for all users of the spreadsheet to be able to jump directly to a list of data by selecting the Table Name from the Name box.

Leaving the default Table Names in place, such as Table1, Table2, Table3, and so on, quickly makes it difficult to know what data is where and cuts off the ability to move purposefully to a list of data anywhere in the workbook.

Let’s now see how you can easily find potential accessibility challenges in any workbook.

Accessibility Checker feature

The Accessibility Checker feature, available in Microsoft Excel and other Microsoft Office programs, can review your workbooks and supply feedback on changes you can make to improve the accessibility of your spreadsheets. You can launch the Accessibility Checker feature in one of three ways:

Choose Review | Check Accessibility.In Excel for Windows, choose File | Info | Check for Issues | Check Accessibility.Click the Accessibility button in Excel’s status bar, which displays the message Accessibility: Investigate, as shown in Figure 1.21, when one or more potential accessibility issues have been noted, or Accessibility: Good to Go when no issues have been found.

Any of these choices will display in the Accessibility task pane shown in Figure 1.21:

Figure 1.21 – Accessibility Checker

The Accessibility Checker feature has three levels of feedback and a Ribbon tab::

Errors are content that will be exceedingly difficult or impossible for disabled users to use. Situations can include negative numbers formatting in red and information rights restrictions.Warnings are triggered by content that will be difficult for disabled users to use. Situations can include worksheets with default names, and insufficient contrast between font color and cell fill color, such as dark gray letters on light gray cell fill. Tips are triggered by content that could be better organized to improve the ease of use for disabled users.

Nuance

Accessibility Checker is an imperfect feature and may flag issues that seem immaterial while blithely ignoring blatant accessibility issues that you can see in plain sight. Similarly Spell Check won't inform you if you've used the word principle when you should have used principal. With both spelling and accessibility issues, you must trust but verify that everything is in order. The Accessibility Checker does offer a Ribbon comprised of tools that can help adjust formatting, assign names, along with other accessibility features.

Now, let’s look at a hidden tool in Excel that you can use to annotate accessibility issues that you plan to clear up in your workbooks.

Accessibility Reminder add-in

The Accessibility Reminder add-in is a free tool that makes it easy to add comments to spreadsheets to call attention to accessibility issues. To install this add-in, follow these steps:

Choose Insert | Get Add-ins.Type Accessibility in the Search field and then press Enter.Click the Add button next to Accessibility Reminder and then click Continue.

A new Accessibility Reminder tab appears in the Ribbon, as shown in Figure 1.22.

Figure 1.22 – Accessibility Reminder Ribbon tab

The Accessibility Reminder tab offers three commands:

Add Accessibility Comment: Adds a generic comment that the file has accessibility issues.More Comments: Allows you to choose between adding three types of comments: Low Vision, Screen Reader, and Custom. The Custom comment defaults to the same text as Add Accessibility Comment, but you can create a message of your choosing on the Customize tab of the Accessibility Reminder task pane.Reminders: Displays an Accessibility Reminder task pane that has three sections, as shown in Figure 1.23:

Figure 1.23 – Accessibility Reminder task pane

Reminders: This section has four buttons that mirror the functionality of the Add Accessibility Comment and More Comments commands on the Accessibility Reminder tab of the Ribbon.Customize: This section allows you to create a custom comment that you add to worksheets by choosing the Custom Comment option from the Ribbon or the Reminders section of the task pane.Training: This section has three buttons:Launch Training: This command connects you to video-based training that discusses accessibility across the entire Microsoft 365 suiteWatch Video: This command links you to application-specific video-based training, which includes ExcelView Features: This command takes you to a comprehensive listing of accessibility discussions and resources

Tip

More accessibility training is available at www.section508.gov, which is a website kept by the US General Services Agency. Section 508 refers to the area of the United States Code that codifies the accessibility standards for documents that, by law, all federal government departments must follow.

Examples of inaccessible spreadsheets

The United States Supreme Court Justice William Rehnquist once noted, “I may not be able to define pornography, but I know it when I see it.” Much the same can be said about inaccessible spreadsheets; often you know them when you see them. Although I have laid out some guidelines in this chapter, the 17 billion cells available within every Excel worksheet supply lots of room for users to create all kinds of chaos. Spreadsheets are always more accessible when you orient your data vertically, going down columns whenever possible, and in as few sheets as possible. Doing so enables you to use a wide variety of features in Excel that can make quick work of tasks. Psychologically though, many users feel compelled to orient their data horizontally, meaning going across rows. The further to the right that your data extends, the less accessible it is for everyone that uses the spreadsheet. Granted, sometimes, such spreadsheets are generated by an accounting program, such as the report shown in Figure 1.24:

Figure 1.24 – An inaccessible accounting report

Three things make this report inaccessible:

Account numbers appear in columns D, E, and F, which can stymy users that wish to use lookup functions such as VLOOKUP, XLOOKUP, and SUMIF, which I discuss in Chapter 10, Lookup Functions and Dynamic Arrays.