Exploring Microsoft Excel’s Hidden Treasures: Turbocharge your Excel proficiency with expert tips, automation techniques, and overlooked features
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
- 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
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.
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
How to download source code?
1. Go to:
2. In the Find a repository… box, search the book title:
Exploring Microsoft Excel’s Hidden Treasures: Turbocharge your Excel proficiency with expert tips, automation techniques, and overlooked features, sometime you may not get the results, please search the main title.
3. Click the book title in the search results.
3. Click Code to download.
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.