Exploring Microsoft Excel's Hidden Treasures: Turbocharge your Excel proficiency with expert tips, automation techniques, and overlooked features

Cover
Title Page
Copyright and Credits
Contributors
About the reviewers
Table of Contents
Preface
Part 1: Improving Accessibility
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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