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.