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

source link 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