Exploring Microsoft Excel's Hidden Treasures

Exploring Microsoft Excel's Hidden Treasures

by David Ringstrom
Released September 2022
Publisher(s): Packt Publishing
ISBN: 9781803243948

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Discover timesaving features, accessibility and internal control approaches, data integrity improvements, and spreadsheet automation techniques by exploring Excel shortcuts and nuances in Microsoft 365, Excel versions till 2021

Key Features

  • Get hands-on experience by carrying out techniques in detailed example workbooks
  • Reclaim portions of your day by immediately implementing data integrity and automation features
  • Incorporate spreadsheet disaster recovery techniques into your daily work

Book Description

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.

What you will learn

  • Explore hidden and overlooked features that will save your time
  • Implement disaster prevention and recovery techniques
  • Improve spreadsheet accessibility for all users
  • Bolster data integrity and spreadsheet resilience
  • Craft code-free custom worksheet functions with LAMBDA
  • Create code-free report automation with Power Query
  • Integrate spreadsheet automation techniques with ease

Who this book is for

This book is for intermediate to advanced excel users working in diverse roles such as business users, accountants, project managers and business analysts among others. The more time that you spend in excel the more time this book will save you. You will be able to maximize your productivity by learning spreadsheet interactivity, accessibility and automation. This clear step-by-step explanation and detailed example workbook will help you to try out new techniques firsthand and leverage them for your business's advantage in no time.

Publisher resources

Download Example Code

Table of contents

  1. Exploring Microsoft Excel’s Hidden Treasures
  2. Contributors
  3. About the author
  4. About the reviewers
  5. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
    4. Download the example workbooks
    5. Download the color images
    6. Conventions used
    7. Get in touch
    8. Share Your Thoughts
  6. Part 1: Improving Accessibility
  7. Chapter 1: Implementing Accessibility
    1. Technical requirements
    2. Making Excel more accessible
      1. Finding worksheet functions
      2. Microsoft Search box
      3. Help tab of Excel’s Ribbon
      4. On-demand PivotTables and charts
    3. Implementing accessibility within spreadsheets
      1. Assign worksheet names
      2. Merge Cells feature
      3. Minimizing the use of watermarks, headers, and footers
      4. Working carefully with color
      5. Using the Table feature
    4. Accessibility Checker feature
    5. Accessibility Reminder add-in
    6. Examples of inaccessible spreadsheets
    7. Summary
  8. Chapter 2: Disaster Recovery and File-Related Prompts
    1. Technical requirements
    2. Undo and Redo
    3. The AutoRecover feature
      1. Excel for Windows AutoRecover
      2. Excel for macOS AutoRecover
    4. AutoSave with OneDrive
      1. Saving files to OneDrive
      2. Accessing prior AutoSave versions
      3. Resolving the disabled AutoSave
      4. Permanently disabling AutoSave
    5. The Always create backup setting
    6. Repairing damaged workbooks
      1. Removing excess formatting
    7. Warning prompts when opening workbooks
      1. Protected View
      2. Trusted documents
      3. CSV prompt
    8. Summary
  9. Chapter 3: Quick Access Toolbar Treasures
    1. Technical requirements
    2. Exploring the Quick Access Toolbar
      1. Customizing Excel’s Ribbon
    3. Understanding the nuances of Quick Access Toolbar shortcuts
      1. Repositioning the Quick Access Toolbar commands
      2. Removing Quick Access Toolbar commands
      3. Resetting the Quick Access Toolbar
      4. The Alt-Number Pad nuance
    4. Adding Ribbon commands to the toolbar
      1. Center text
      2. Locking/unlocking worksheet cells
      3. PDF shortcuts
    5. Commands Not in the Ribbon
      1. Enhanced commands
    6. Workbook-specific toolbars
    7. Creating shortcuts for Excel macros
    8. Transferring your Quick Access Toolbar between computers
    9. Restoring legacy features
      1. The Full screen feature
      2. Full-screen mode
      3. Restoring the Full Screen feature
      4. The Share Workbook feature
      5. Show Changes
    10. Summary
  10. Chapter 4: Conditional Formatting
    1. Technical requirements
    2. Formatting versus Conditional Formatting
    3. Highlight cell rules
      1. Greater Than
      2. Less Than
      3. Between
      4. Equal To
      5. Text That Contains
      6. A Date Occurring
      7. Duplicate Values
    4. Top and bottom rules
    5. Data Bars
    6. Color Scales
    7. Icon Sets
    8. Custom rules
      1. IS functions
      2. CELL function
      3. Logical tests
    9. Managing rules
      1. Editing existing rules
      2. Applies to ranges
      3. Creating a Conditional Formatting legend
      4. Removing Conditional Formatting
    10. Troubleshooting Conditional Formatting
      1. No formatting appears
      2. Changing the order of the rules
      3. Wingdings font
    11. Summary
  11. Part 2:Spreadsheet Interactivity and Automation
  12. Chapter 5: Data Validation and Form Controls
    1. Technical requirements
    2. Introducing Data Validation
      1. Settings tab
      2. Input Message tab
      3. Error Alert Message tab
      4. Removing Data Validation
    3. Implementing Data Validation rules
      1. Any value
      2. Whole Numbers
      3. Decimal
      4. List
      5. Date
      6. Time
      7. Text length
      8. Custom rules
    4. Protecting Data Validation cells
    5. Auditing Data Validation cell inputs
    6. Enabling the Developer tab
    7. Exploring Form Controls
      1. The INDEX function
      2. Combo Box Form Control
    8. Checkboxes and Option Buttons
      1. Creating Checkboxes
      2. Creating Option Buttons
      3. Managing Form Controls
    9. Summary
  13. Chapter 6: What-If Analysis
    1. Technical requirements
    2. The PMT function
    3. The CUMIPMT function
    4. Understanding the Scenario Manager feature
      1. Setting the scene for a scenario
      2. Creating scenarios
      3. Showing scenarios
      4. Scenario reports
      5. Merging scenarios
    5. The Goal Seek feature
    6. The Data Table feature
      1. Creating a Data Table with one input
      2. Creating a Data Table with two inputs
      3. Creating a Data Table with three inputs
      4. Improving calculation performance
    7. Projecting amounts with the Forecast Sheet feature
    8. Introducing the Solver feature
    9. Summary
  14. Chapter 7: Automating Tasks with the Table Feature
    1. Technical requirements
    2. Excel’s unwritten rule
    3. What is a Table?
      1. The Format as Table command
      2. The Insert | Table command
      3. Table characteristics
    4. Removing Tables
    5. Table automation opportunities
      1. Calculated Columns
      2. Self-resizing formulas
      3. Using structured references to write formulas
      4. Filtering
      5. Slicers
      6. PivotTable integrity improvements
      7. Self-resizing charts
    6. Other Table techniques
      1. Customizing Table Styles
      2. Transferring Table Styles to other workbooks
      3. Modifying or removing custom Table Styles
      4. Copying and pasting Tables
      5. Keyboard and mouse shortcuts
    7. Troubleshooting Tables
      1. The Include new rows and columns in Table option
      2. Fill formulas in Tables to create Calculated Columns
      3. Deleting rows prevents Table expansion
    8. Summary
  15. Chapter 8: Custom Views
    1. Technical requirements
    2. Introducing Custom Views
    3. Creating multipurpose worksheets
      1. Creating a base view
      2. Creating a Quarters Only view
      3. Creating an Executive Summary view
      4. Page Layout view conflict
    4. Creating a Custom Views Quick Access Toolbar shortcut
    5. Hiding and unhiding worksheets
      1. Unhiding worksheets with a macro
      2. Hiding and unhiding worksheets with Custom Views
      3. Creating a Summary Only view
    6. Automating filtering
    7. Applying print settings on demand
    8. Updating a Custom View
      1. Removing all Custom Views from a workbook
    9. Custom Views conflicts
      1. Table feature conflicts
      2. Worksheet protection conflicts
      3. Workbook protection
    10. Summary
  16. Chapter 9: Excel Quirks and Nuances
    1. Technical requirements
    2. Compatibility Checker feature
      1. Compatibility Mode
      2. Save As versus Convert command
    3. Double-click trick for navigating within worksheets
    4. Enter Mode Versus Edit Mode
    5. Excluding weekend dates from charts
    6. Sparklines
    7. Circular references
      1. Enable iterative calculation option
    8. Inquire add-in
    9. Summary
  17. Part 3: Data Analysis
  18. Chapter 10: Lookup and Dynamic Array Functions
    1. Technical requirements
    2. The VLOOKUP function
    3. The IFNA function
    4. The MATCH function
    5. The SUMIF function
    6. The SUMIFS function
    7. The XLOOKUP function
      1. The if_not_found argument
      2. The match_mode argument
      3. Combining results into a single column
      4. The search_mode argument and returning results to multiple columns
      5. Matching on multiple column criteria
      6. Returning results from multiple cells
    8. The XMATCH function
    9. The UNIQUE function
    10. The SORT function
    11. The FILTER function
    12. The Spilled Range Operator
    13. The dynamic amortization table
      1. The SEQUENCE function
      2. The EOMONTH function
      3. The PPMT function
      4. The SUMIF function
    14. The #SPILL! errors
    15. The RANDARRAY function
    16. Summary
  19. Chapter 11: Names, LET, and LAMBDA
    1. Technical requirements
    2. Simple volume calculations in Excel
      1. Multiplication
      2. Decision-making functions
    3. Naming worksheet cells
      1. Name Box
      2. Create from Selection
      3. Define Name
      4. Name Manager
      5. Using Names within formulas
    4. Introducing the LET function
      1. Handling formula errors
      2. The IFERROR function
      3. The ISERROR function
      4. Eliminating repetitive calculations
      5. Variables restrictions
    5. Introducing the LAMBDA function
      1. Developing a LAMBDA formula
      2. Naming Parameters and defining the calculation
      3. Evaluating a LAMBDA function
      4. Creating reusable LAMBDA functions
      5. Moving LAMBDA functions between workbooks
    6. Going deeper with LAMBDA functions
      1. Optional LAMBDA Parameters
      2. LAMDBA conflicts and errors
      3. The Advanced Formula Environment add-in
      4. The XBOXVOLUME function
    7. Custom VBA worksheet functions
    8. Summary
  20. Chapter 12: Power Query
    1. Technical requirements
    2. Introducing Power Query
    3. Creating a list of worksheets
      1. The HYPERLINK function
      2. Making Power Query results into a clickable index
      3. Refreshing Power Query Connections
      4. Updating the worksheet index
      5. Updating source data connections
    4. Automatic report cleanup
      1. Analytical obstacles
      2. Transforming reports
      3. Setting a locale or region
      4. Adding supplemental formulas to Power Query results
      5. Updating a Power Query connection with new data
      6. Breaking Power Query Connections
    5. Extracting data from PDF files
    6. Unpivoting data
    7. Appending and merging data from multiple sources
    8. Connecting to databases and installing ODBC drivers
      1. Establishing an Access database and SQL Server connections
      2. Establishing ODBC connections
      3. Installing ODBC drivers and creating data sources
    9. Summary
  21. Index
    1. Why subscribe?
  22. Other Books You May Enjoy
    1. Packt is searching for authors like you
    2. Share Your Thoughts

Product information

  • Title: Exploring Microsoft Excel's Hidden Treasures
  • Author(s): David Ringstrom
  • Release date: September 2022
  • Publisher(s): Packt Publishing
  • ISBN: 9781803243948