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

Exploring Microsoft Excel’s Hidden Treasures
Contributors
About the author
About the reviewers
Preface
    Who this book is for
    What this book covers
    To get the most out of this book
    Download the example workbooks
    Download the color images
    Conventions used
    Get in touch
    Share Your Thoughts
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
    Why subscribe?
Other Books You May Enjoy
    Packt is searching for authors like you
    Share Your Thoughts