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.

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:

1. Choose File | Options | General.
2. 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:
  1. Select any cell within the list on the World’s Longest Metro Systems worksheet of this chapter’s example workbook.
  2. Choose Insert | Recommended PivotTables.
  3. 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:
  1. Select any cell within the list on the World’s Longest Metro Systems worksheet of this chapter’s example workbook.
  2. Click Insert | Recommended Charts.
  3. 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:
  1. Select any cell within the list on the World’s Longest Metro Systems worksheet of this chapter’s example workbook.
  2. Click Home | Analyze Data.
  3. 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.

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 Excel
• Choose Home | Insert drop-down menu | Insert Sheet
• Press 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:

1. Use any of these three techniques:
   • Double-click on the worksheet tab
   • Right-click on a worksheet tab, and then choose Rename
   • In 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 Rename
2. Type 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 worksheet
• B3:G28: A cluster of text, values, and/or formulas
• Z500: 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:

1. Select cells B4:G5 on the Merge Cells vs. Center Across worksheet of this chapter’s example workbook and then choose Home | Merge & Center.
2. 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

3. 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

4. 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:

1. Select cells B8:G9.
2. Click the Alignment Settings button on the Home tab of the Ribbon, press Ctrl + 1 (⌘ + 1), or choose Home | Format | Format Cells.
3. Activate the Alignment tab if needed.
4. 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:

1. Choose Insert | WordArt, or Insert | Text | WordArt.
2. 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

3. 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

4. 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:

1. Choose Page Layout | Print Titles.
2. Click the Header/Footer tab in the Page Setup dialog box.
3. 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.

4. 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

5. 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.
6. Once you select an image, a &[Picture] placeholder will appear in the section you chose, as shown in Figure 1.18.
7. 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.
8. Click OK as needed to close any open dialog boxes.
9. 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.

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.

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:

1. Choose Insert | Get Add-ins.
2. Type Accessibility in the Search field and then press Enter.
3. 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 suite
• Watch Video: This command links you to application-specific video-based training, which includes Excel
• View 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.

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.
• The data in the spreadsheet starts in column A and ends in column DS, which means it spans 123 columns. In Chapter 12, Power Query, I show how to unpivot this report, meaning transposing the data from going horizontally across rows to instead running vertically down columns.

Nuance

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.

• Cell DS6 on the Inaccessible Worksheet tab contains the formula =ROUND(J6+L6+N6+P6+R6+T6+W6+Y6+AA6+AC6+AE6+AH6+AJ6+AL6+AN6+AP6+AS6+AU6+AW6 +AY6+BA6+BC6+BE6+BG6+BJ6+BL6+BN6+BQ6+BS6+BU6+BX6+BZ6+CB6 +CD6+CF6+CH6+CJ6+CL6+CN6+CP6+CR6+CT6+CV6+CX6+CZ6+DB6+DD6 +DF6+DH6+DK6+DM6+DO6+SUM(DQ6:DR6),5), which is completely inaccessible for most Excel users. Conversely, cell DT6 contains the formula =SUMIF(G2:DQ2,"Total*",G6:DQ6)+DR6. The SUMIF function has three arguments:
  • Range – This argument specifies the range of cells Excel should search, in this case, G2:DQ2.
  • Criteria – This argument specifies the criteria that Excel should match on. In this case, "Total*" enables SUMIF to perform a partial match and add up the values from every column where the values in row 2 begin with the word Total. The asterisk is known as a wildcard character for performing partial matches such as this.
  • Sum_range – The range of cells that should be summed when matching criteria is found, in this case, cells G6:DQ6.

Notice that the formula includes +DR6 because cell DR2 contains the word Overhead, and so it would be excluded based upon the criteria specified in the SUMIF function.

Inaccessible spreadsheets are a fact for many Excel users, but throughout this book, you’ll discover ways to turn the tide and improve their usability. I’ll leave you with one last rule of thumb, which is to use as few worksheets in a workbook as possible. For instance, stick with a single worksheet that has a month or period column that you fill in on each row, instead of creating 12 monthly worksheets to house data by period. In general, resist the urge to recreate the same sheet over and over, such as separate worksheets for each vehicle, department, project, or what have you, and instead, make minor modifications to keep the data to a single worksheet. Doing so treats Excel more like a database and unlocks many ways to use your data more effectively.

Choose Review | Workbook Statistics to determine of worksheets in a workbook, as shown in Figure 1.25. A double-digit number of worksheets doesn’t automatically make a workbook inaccessible, but inaccessible workbooks typically have double-digit worksheet counts or sometimes more.

Figure 1.25 – Workbook Statistics dialog box

Nuance

The Workbook Statistics dialog box includes both hidden and visible sheets, along with the number of filled cells, the number of Tables, formulas, and objects. Objects are anything that floats above the worksheet, such as the WordArt that we created earlier.

Let’s now look at what you’ve learned in this chapter.

It’s easy to dismiss accessibility as something akin to eating your vegetables, or exercising; you know you should do both, but you’ll get to it tomorrow. Accessibility improves everyone’s experience, not just those that use assistive technology. If your boss asks you to perform an analytical task in Excel, and you don’t know where to start, suddenly, Excel is inaccessible. Fortunately, the Insert Function command lets you easily lay your hands on any of the hundreds of worksheet functions available within Excel. Similarly, the Microsoft Search box puts even the most hidden Excel commands at your fingertips. When you need to go further, the Help tab of the Ribbon connects you to an extensive array of resources that include online documentation, training videos, online chat support, and user-to-user support by way of an Excel community forum.

You also learned how artificial intelligence is making Excel more accessible by way of features such as Recommended PivotTables, Recommended Charts, and Analyze Data. All these features can help you get past that I don’t know where to start phase of analyzing a dataset.

Accessibility extends far beyond goodwill for disabled users. Any time you implement even simple accessibility techniques, such as naming Tables and worksheets, avoiding the use of merged cells, limiting the use of critical information within watermarks, headers, and footers, ensuring proper color contrasts, and using the Table feature within your spreadsheets, you improve the ease of use of the workbook for every single user that touches it.

It can feel overwhelming to try to find all the accessibility issues in a workbook, especially if you have a legal mandate to do so. Fortunately, the Check Accessibility feature supplies instant feedback on issues that can cause a workbook to be considered inaccessible. The free Accessibility Reminder add-in makes it easy to document accessibility issues you wish to clean up and supplies links to more training materials.

Accessibility is subjective, particularly in large workbooks, but just remember, if you’re struggling with a workbook that you authored yourself, it’s highly unlikely that others will be able to make any sense of it. The good news is that accessibility runs through this book as an unspoken theme, and as you progress through the book, you’ll become ever more empowered to work with Excel, as opposed to Excel pushing you around or, worse, stopping you in your tracks.

In the next chapter, I’ll be discussing how to implement disaster recovery techniques, including what to do when terrible things happen to good spreadsheets.