Excel Cookbook Recipes for Mastering Microsoft Excel (Dawn Griffiths) (Z-Library)

Dawn Grif f iths Excel Cookbook Recipes for Mastering Microsoft Excel

BUSINESS Excel Cookbook linkedin.com/company/oreilly-media youtube.com/oreillymedia Filled with tips, tricks, and techniques, this easy-to-use book is the perfect Excel resource. You’ll find more than 350 recipes for over a dozen topics covering formulas, PivotTables, charts, Power Query, and more. Each recipe poses a particular problem and outlines a solution that you can put to use right away—without having to comb through tutorial pages. Whether you’re a data analyst, project manager, financial analyst, or regular Excel user, author Dawn Griffiths directs you straight to the answers you need. Ideal as a quick reference, Excel Cookbook is also perfect for learning how to work in a more efficient way, leading to greater productivity on the job. With this book, you’ll jump in and get answers to your questions—fast. • Build compelling charts and use Sparklines, 3D Maps, and other visualizations • Use PivotTables to slice, dice, and summarize datasets • Perform statistical and financial analyses using formulas, Forecast Sheets, the Analysis ToolPak, and more • Master dynamic array functions such as SEQUENCE, TEXTSPLIT, and FILTER • Use Power Query to import, shape, and combine datasets • Create custom functions using LAMBDA formulas • Use developer options to write VBA code and create custom UserForms Dawn Griffiths is an author and trainer who has taught Excel to thousands of students worldwide. Her previous books include Head First Statistics, Head First Android Development, Head First Kotlin, Head First C, and React Cookbook. 9 7 8 1 0 9 8 1 4 3 3 2 9 5 6 5 9 9 US $65.99 CAN $82.99 ISBN: 978-1-098-14332-9 “The ultimate desk reference, offering a thorough blend of traditional and modern Excel solutions. Perfect for daily users seeking to solve a wide range of problems.” —George Mount Founder, Stringfest Analytics “Dawn’s knowledge of Excel is phenomenal. She explains everything in a way that is clear and easy to follow. I had a good knowledge of Excel already but learned a lot from this book.” —Ann Brumwell Financial controller, Denfield Advertising & Marketing

Dawn Griffiths Excel Cookbook Recipes for Mastering Microsoft Excel

978-1-098-14332-9 [LSI] Excel Cookbook by Dawn Griffiths Copyright © 2024 Dawn Griffiths. All rights reserved. Printed in the United States of America. Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472. O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (http://oreilly.com). For more information, contact our corporate/institutional sales department: 800-998-9938 or corporate@oreilly.com. Acquisitions Editor: David Michelson Development Editor: Corbin Collins Production Editor: Beth Kelly Copyeditor: Stephanie English Proofreader: Sharon Wilkey Indexer: Ellen Troutman-Zaig Interior Designer: David Futato Cover Designer: Karen Montgomery Illustrator: Kate Dullea May 2024: First Edition Revision History for the First Edition 2024-05-14: First Release See http://oreilly.com/catalog/errata.csp?isbn=9781098143329 for release details. The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. Excel Cookbook, the cover image, and related trade dress are trademarks of O’Reilly Media, Inc. The views expressed in this work are those of the author and do not represent the publisher’s views. While the publisher and the author have used good faith efforts to ensure that the information and instructions contained in this work are accurate, the publisher and the author disclaim all responsibility for errors or omissions, including without limitation responsibility for damages resulting from the use of or reliance on this work. Use of the information and instructions contained in this work is at your own risk. If any code samples or other technology this work contains or describes is subject to open source licenses or the intellectual property rights of others, it is your responsibility to ensure that your use thereof complies with such licenses and/or rights.

Table of Contents Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii 1. Workbooks, Worksheets, and Cells. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.1 Using Themes 1 1.2 Using Cell Styles 2 1.3 Formatting Cells 3 1.4 Formatting a Cell’s Value 4 1.5 Defining a Custom Number Format 6 1.6 Merging Cells 10 1.7 Creating Templates 11 1.8 Protecting Excel Files, Workbooks, Worksheets, and Cells 12 1.9 Using Conditional Formatting 13 1.10 Using the Format Painter 16 1.11 Using Paste Special 16 1.12 Using Auto Fill 18 1.13 Using Custom Lists 20 1.14 Using Flash Fill 21 1.15 Customizing AutoCorrect 23 1.16 Using Notes and Comments 24 1.17 Finding and Selecting Cells and Navigation 26 1.18 Creating a Custom View 28 1.19 Customizing the Ribbon and Ribbon Tabs 29 1.20 Using the Quick Access Toolbar 31 1.21 Using the Accessibility Checker 32 2. References and Structured Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 2.1 Using Relative and Absolute References 33 2.2 Using Relative and Absolute References in Conditional Formatting 34 iii

2.3 Using R1C1-Style Cell References 36 2.4 Referencing Another Worksheet or Workbook 38 2.5 Using 3-D References 39 2.6 Naming Cells, Ranges, Constants, and Formulas 39 2.7 Creating Dynamic Named Ranges 42 2.8 Using Data Validation 44 2.9 Creating a Custom Data Validation Rule 46 2.10 Entering Data with a Drop-Down List 47 2.11 Defining Dependent or Cascading Drop-Down Lists 48 2.12 Using a Data-Entry Form 49 2.13 Sorting Data by Value, Format, or Custom List 51 2.14 Filtering Data 52 2.15 Freezing Panes 54 2.16 Using AutoSum 55 2.17 Using Outlines to Add Subtotals and Groups 56 2.18 Using Tables 57 2.19 Using Structured References 60 3. Using Formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 3.1 Using Operators and Order of Precedence 63 3.2 Using Excel in Different Regions and Languages 66 3.3 Using Array Constants 67 3.4 Using Dynamic and Legacy Array Formulas 68 3.5 Using Spill Range References 70 3.6 Preventing Dynamic Array Behavior 71 3.7 Using the Insert Function or Function Builder Tool 72 3.8 Adding Notes to Numeric Formulas 73 3.9 Showing Formulas 74 3.10 Using the Watch Window 75 3.11 Showing Cell Interdependencies 76 3.12 Performing Background Error Checks 77 3.13 Using Error Checking 78 3.14 Tracing Errors 79 3.15 Correcting Error Values 80 3.16 Evaluating Formulas 82 3.17 Changing the Calculation Mode 84 3.18 Setting Rounding Precision 86 3.19 Resolving Circular References 87 4. Math and Engineering. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 4.1 Generating Numbers 89 4.2 Converting Text or a Boolean to a Number 90 iv | Table of Contents

4.3 Getting a Number’s Sign and Absolute Value 91 4.4 Counting, Summing, and Averaging Cell Values 92 4.5 Using Criteria to Count, Sum, and Average 93 4.6 Adding and Subtracting Squares of Values 95 4.7 Using Multiplication and Multiples 96 4.8 Finding Quotients, Remainders, and Divisors 97 4.9 Rounding to Decimal Places and Integers 98 4.10 Rounding to Significant Figures and Multiples 100 4.11 Using Powers, Exponents, Square Roots, and Logarithms 101 4.12 Summing a Power Series 103 4.13 Using Factorials, Permutations, and Combinations 103 4.14 Using Trigonometry 104 4.15 Working with Matrices 105 4.16 Converting Between Number Systems 107 4.17 Performing Bitwise Operations 108 4.18 Working with Complex Numbers 109 5. Text Manipulation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 5.1 Concatenating Text 111 5.2 Using Character Codes 112 5.3 Generating a Sequence of Characters 113 5.4 Generating Random Letters 114 5.5 Finding the Length of a Text String 114 5.6 Finding Text Position in a Text String 115 5.7 Getting Fixed-Width Text from a Text String 116 5.8 Getting Text from a Text String by Delimiter 117 5.9 Getting Text from a Text String by Digit to Nondigit 119 5.10 Replacing, Inserting, and Deleting Text 120 5.11 Removing Extra Characters 121 5.12 Counting Words or Specific Characters 122 5.13 Changing Text Case 123 5.14 Repeating Characters 124 5.15 Converting an Array to Text 124 5.16 Formatting Text as Currency 125 5.17 Including Numeric Values in a Text String 126 5.18 Including Date/Time Values in a Text String 127 6. Dates and Times. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 6.1 Returning the Current Date and Time 129 6.2 Getting Part of a Date/Time Value 130 6.3 Getting the Day of the Week and Week of the Year 130 6.4 Getting the Calendar or Fiscal Quarter 131 Table of Contents | v

6.5 Constructing Dates Using Day, Month, and Year 133 6.6 Constructing Times Using Hours, Minutes, and Seconds 134 6.7 Converting a Text Value to a Date/Time Serial Number 135 6.8 Extracting the Date and Time from a Serial Number 136 6.9 Adding Days, Months, and Years to a Date 137 6.10 Adding Hours, Minutes, and Seconds to a Time 138 6.11 Getting the Last Day of the Month 139 6.12 Calculating the Year Fraction 140 6.13 Calculating the Difference Between Dates and Times 141 6.14 Using Working Days 142 6.15 Getting a Sequence of Dates 143 7. Array, Logic, and Lookup Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145 7.1 Getting Unique Values 145 7.2 Sorting an Array 146 7.3 Filtering an Array 148 7.4 Manipulating Arrays 149 7.5 Using Logical True/False Criteria 151 7.6 Evaluating AND and OR Conditions in Array Formulas 152 7.7 Working with Types and Error Values 153 7.8 Choosing Values to Return 154 7.9 Looking Up Exact and Nearest Values 156 7.10 Finding a Matching Value’s Index 158 7.11 Using an Index to Return a Value 159 7.12 Creating Indirect References to Cells and Ranges 161 7.13 Getting a Cell’s Address 162 7.14 Using Offset References 163 8. Statistical Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 8.1 Creating a Frequency Table 165 8.2 Showing Cumulative and Percentage Frequencies 168 8.3 Using a Histogram or Pareto Chart 169 8.4 Calculating Averages 171 8.5 Ranking Numeric Data 173 8.6 Finding the kth Largest or Smallest Value 174 8.7 Dividing Data into Quartiles and Percentiles 175 8.8 Calculating Ranges and Variances 176 8.9 Finding Outliers 178 8.10 Using a Box and Whisker Chart 178 8.11 Calculating Skewness 180 8.12 Calculating Probabilities Using a Probability Table 181 8.13 Calculating Expectation and Variance 182 vi | Table of Contents

8.14 Using the Binomial Distribution 183 8.15 Using the Negative Binomial Distribution 184 8.16 Using the Hypergeometric Distribution 185 8.17 Using the Poisson Distribution 186 8.18 Using the Exponential Distribution 187 8.19 Using the Normal Distribution 187 8.20 Using Z-Scores 189 8.21 Calculating a Confidence Interval for the Population Mean 190 8.22 Performing a Chi-Squared (χ2) Test for Independence 192 8.23 Finding the Line of Best Fit 193 8.24 Getting the Line of Best Fit’s Equation 195 9. The Analysis ToolPak. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197 9.1 Installing the Analysis ToolPak 197 9.2 Generating Descriptive Statistics 198 9.3 Generating Ordinal and Percentage Rank Statistics 200 9.4 Generating a Frequency Distribution 202 9.5 Generating Moving Averages 205 9.6 Using Exponential Smoothing 207 9.7 Generating a Random Sample 209 9.8 Generating a Periodic Sample 211 9.9 Drawing Random Numbers from a Distribution 211 9.10 Generating a Correlation Matrix 213 9.11 Generating a Covariance Matrix 215 9.12 Performing a Linear Regression Analysis 216 9.13 Performing a Two-Sample t-Test 219 9.14 Performing a Two-Sample z-Test 222 9.15 Performing a Paired Two-Sample t-Test 224 9.16 Performing a Two-Sample F-Test for Variances 226 9.17 Performing a One-Way ANOVA Test 227 9.18 Performing a Two-Way ANOVA Test 229 9.19 Running a Fourier Analysis 232 10. Financial Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235 10.1 Calculating Fixed-Rate Loan Payments 235 10.2 Calculating Interest and Principal Loan Payments 236 10.3 Building a Variable Rate Loan Amortization Schedule 238 10.4 Calculating the Term for a Fixed-Rate Loan 240 10.5 Calculating the Principal or Present Value 241 10.6 Converting Between Nominal and Effective Rates 242 10.7 Calculating the Future Value of a Fixed-Rate Lump-Sum Investment 243 10.8 Calculating the Future Value of a Variable-Rate Lump-Sum Investment 244 Table of Contents | vii

10.9 Calculating the Future Value of an Investment with Regular Deposits 245 10.10 Meeting Investment Goals 246 10.11 Calculating Net Present Value 248 10.12 Calculating the Internal Rate of Return 249 10.13 Calculating Depreciation 251 10.14 Getting Stock and Currency Data 252 10.15 Getting Historic Stock and Currency Data 254 10.16 Using Stock Charts 254 10.17 Calculating a Stock’s Beta 256 10.18 Forecasting Linear and Exponential Growth 257 10.19 Forecasting Seasonal Growth 259 11. PivotTables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263 11.1 Organizing Data for PivotTables 263 11.2 Inserting a PivotTable 264 11.3 Adding Rows, Columns, and Values 266 11.4 Using Secondary Rows 268 11.5 Refreshing a PivotTable’s Data 270 11.6 Moving a PivotTable 270 11.7 Changing a PivotTable’s Appearance 271 11.8 Changing the Default Layout 272 11.9 Changing Value Aggregations 273 11.10 Showing Different Value Calculations 274 11.11 Creating Custom Subtotals 276 11.12 Sorting Data 277 11.13 Moving Items Manually 278 11.14 Filtering Data 279 11.15 Using a Filter to Create Multiple PivotTables 281 11.16 Grouping by Date/Time 282 11.17 Grouping by Number 283 11.18 Manually Grouping by Text Values 284 11.19 Including Groups with Missing Data 285 11.20 Changing the Format of Empty Cells 286 11.21 Using Calculated Fields 287 11.22 Using Calculated Fields to Count Items 290 11.23 Using Calculated Items 291 11.24 Referring to Position in a Calculated Item Formula 294 11.25 Changing the Calculated Item Solve Order 295 11.26 Generating a List of Custom Formulas 297 11.27 Changing a PivotTable’s Data Source 298 11.28 Using the PivotTable Cache 298 11.29 Filtering Multiple PivotTables That Share a Cache 300 viii | Table of Contents

11.30 Reducing the Workbook File Size 301 11.31 Reinstating a PivotTable’s Source Data 302 11.32 Referring to PivotTable Values 303 12. Charts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305 12.1 Using Different Chart Types 305 12.2 Inserting a Chart 311 12.3 Filtering a Chart 312 12.4 Tweaking a Chart’s Appearance 313 12.5 Adding and Removing Chart Elements 314 12.6 Formatting Chart Elements 314 12.7 Creating Dynamic Titles and Labels 320 12.8 Customizing Data Label Text 320 12.9 Controlling Chart Axes and Gridlines 321 12.10 Displaying Negative Values 323 12.11 Using Pictures in Column Charts 324 12.12 Formatting Pie of Pie and Bar of Pie Charts 325 12.13 Formatting a Histogram Chart 326 12.14 Specifying a Combination Chart’s Chart Types 327 12.15 Handling Empty Cells 329 12.16 Basing a Chart on Noncontiguous Data 329 12.17 Changing a Data Series Name and Legend Entry 330 12.18 Adding a Series or Changing the Data Source 331 12.19 Basing a Chart on a Dynamic Named Range 332 12.20 Inserting a PivotChart 334 12.21 Creating a Gantt Chart 335 12.22 Creating and Using Chart Templates 336 13. Graphics, Sparklines, and 3D Maps. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339 13.1 Inserting Symbols 339 13.2 Inserting Equations 341 13.3 Inserting Shapes 341 13.4 Using the Draw Tool 342 13.5 Using SmartArt 343 13.6 Inserting Pictures 344 13.7 Grouping Objects 346 13.8 Moving and Sizing Objects with Cells 346 13.9 Inserting a Linked Picture 347 13.10 Using Sparklines 348 13.11 Using Sparkline Groups 351 13.12 Using 3D Maps 352 13.13 Creating Videos with 3D Maps 355 Table of Contents | ix

14. What-If Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357 14.1 Creating a One-Variable Data Table 357 14.2 Creating a Row-Oriented One-Variable Data Table 359 14.3 Creating a Two-Variable Data Table 360 14.4 Editing Data Tables 362 14.5 Using Scenario Manager 363 14.6 Merging Scenarios 365 14.7 Generating Scenario Summaries 366 14.8 Using Goal Seek 368 14.9 Finding Multiple Solutions with Goal Seek 370 14.10 Handling Discontinuous Formulas with Goal Seek 371 14.11 Enabling Solver 372 14.12 Solving an Optimization Problem with Solver 373 14.13 Using Integer-Only Constraints with Solver 378 14.14 Using Binary-Only Constraints with Solver 381 14.15 Making Changing Cells All Different with Solver 384 14.16 Handling Discontinuities with Solver 387 14.17 Finding Multiple Solutions with Solver 389 14.18 Finding a Formula’s Global Minimum or Maximum with Solver 391 14.19 Adjusting Solver’s Options 392 14.20 Saving and Loading Solver Parameters 395 14.21 Saving Solver-Generated Scenarios 396 14.22 Displaying Solver Reports 396 15. Power Query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397 15.1 Getting and Loading Data 397 15.2 Getting and Loading Data from Files in a Folder 399 15.3 Specifying Where to Load Data To 400 15.4 Editing Data Source Settings and Security 402 15.5 Refreshing a Query’s Data 404 15.6 Managing Queries 405 15.7 Editing a Query 407 15.8 Managing a Query’s Steps 408 15.9 Managing Columns 409 15.10 Using Data Types 410 15.11 Sorting and Filtering Data 411 15.12 Filtering Files When Loading Data from a Folder 412 15.13 Removing Duplicates, Blank Rows, and Errors 413 15.14 Transforming Data in Columns 414 15.15 Splitting and Merging Columns 418 15.16 Pivoting Columns 419 15.17 Unpivoting Columns 420 x | Table of Contents

15.18 Transforming Structured Columns 421 15.19 Returning a Value or List 423 15.20 Adding New Columns 424 15.21 Adding a Column Based on Examples 425 15.22 Adding a Conditional Column 427 15.23 Adding a Custom Column 428 15.24 Using Parameters 431 15.25 Creating a Custom Function 433 15.26 Adding a Column by Invoking a Custom Function 435 15.27 Duplicating a Query 436 15.28 Referencing a Query 437 15.29 Appending Data from Multiple Queries 438 15.30 Merging Data from Multiple Queries 440 15.31 Editing a Query’s M Code 442 16. Power Pivot and the Data Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443 16.1 Installing Power Pivot 443 16.2 Adding Data to the Data Model 444 16.3 Managing Power Pivot Data Connections 446 16.4 Viewing and Managing the Data Model’s Tables 447 16.5 Refreshing the Data Model’s Data 449 16.6 Working with Table Columns 450 16.7 Creating and Editing Relationships 452 16.8 Adding a Calculated Column 454 16.9 Basing a PivotTable or PivotChart on Data Model Tables 457 16.10 Inserting Measures 459 16.11 Using KPIs 462 16.12 Creating Hierarchies 465 16.13 Creating a Date Table 468 16.14 Using Named Sets 470 16.15 Converting a PivotTable to Formulas 473 16.16 Using Cube Formulas 475 16.17 Filtering Cube Formulas with Slicers and Timelines 478 17. LET, LAMBDA, and LAMBDA Helper Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481 17.1 Improving Formula Efficiency 481 17.2 Writing and Testing a LAMBDA Formula 482 17.3 Making LAMBDA Arguments Optional 484 17.4 Defining a Custom LAMBDA Function 485 17.5 Writing Recursive LAMBDA Formulas 487 17.6 Copying a Custom LAMBDA Function to Another Workbook 489 17.7 Applying a LAMBDA Formula to Each Column 490 Table of Contents | xi

17.8 Applying a LAMBDA Formula to Each Row 492 17.9 Creating an Array of Calculated Values 493 17.10 Transforming the Values in Arrays 495 17.11 Calculating Cumulative Values 497 17.12 Returning the Final Value of a Cumulative Calculation 499 18. Developer Tools: Macros, VBA, Controls, and XML. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 501 18.1 Showing the Developer Tab 501 18.2 Recording a Macro 502 18.3 Using a Personal Macro Workbook 504 18.4 Editing a Macro’s Options 505 18.5 Running a Macro 506 18.6 Viewing or Editing a Macro’s VBA Code 507 18.7 Using Absolute and Relative References 510 18.8 Creating a Macro by Writing VBA 511 18.9 Creating a Custom VBA Function 512 18.10 Using Worksheet and Workbook Events 515 18.11 Overriding Keystrokes with OnKey 517 18.12 Scheduling Code with OnTime 519 18.13 Deleting a Macro or Function 520 18.14 Copying Code to Another VBA Project 521 18.15 Debugging VBA Code 523 18.16 Using Built-in Dialog Boxes 528 18.17 Using Form Controls 529 18.18 Using ActiveX Controls 532 18.19 Creating a UserForm 537 18.20 Creating a Custom Excel Add-in 540 18.21 Setting Security and Privacy Options 542 18.22 Importing and Exporting XML 543 Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547 xii | Table of Contents

Preface This book contains more than 350 recipes designed to help you get the most out of Microsoft Excel, whether you’re writing formulas, importing data, wrangling with PivotTables, or solving complex optimization problems. The recipes result from my over 20 years of experience using Excel and teaching it to thousands of students worldwide. Each recipe solves a specific problem using one or more techniques, and nearly every recipe uses examples to illustrate the teaching points. You can test the examples for yourself and then put them into practice by applying them to your situation. You don’t have to read the chapters or their recipes in any particular order, so you can read the book from cover to cover or start reading anywhere. Many recipes reference others, so you can quickly jump from one to another. The book includes the latest features of Excel 365 (at the time of writing), such as LAMBDA functions. However, it’s also backward compatible with earlier versions. For example, it covers the XMATCH function available in Excel 2021 and Excel 365 and the MATCH function available in earlier versions. This book also includes differences in using Excel for Microsoft Windows and Excel for Mac, such as how to change the default settings. Conventions Used in This Book The following typographical conventions are used in this book: Italic Indicates new terms, URLs, email addresses, filenames, and file extensions. Constant width Used for program listings, as well as within paragraphs to refer to program ele‐ ments such as variable or function names, databases, data types, environment variables, statements, and keywords. xiii

Constant width bold Shows commands or other text that should be typed literally by the user. Constant width italic Shows text that should be replaced with user-supplied values or by values deter‐ mined by context. This element signifies a tip or suggestion. This element signifies a general note. This element indicates a warning or caution. Using Code Examples Supplemental material (code examples, exercises, etc.) is available for download at https://github.com/dogriffiths/excelcookbook. If you have a technical question or a problem using the code examples, please send email to support@oreilly.com. This book is here to help you get your job done. In general, if example code is offered with this book, you may use it in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission. We appreciate, but generally do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “Excel Cookbook by Dawn Griffiths (O’Reilly). Copyright 2024 Dawn Griffiths, 978-1-098-14332-9.” xiv | Preface

If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at permissions@oreilly.com. O’Reilly Online Learning For more than 40 years, O’Reilly Media has provided technol‐ ogy and business training, knowledge, and insight to help companies succeed. Our unique network of experts and innovators share their knowledge and expertise through books, articles, and our online learning platform. O’Reilly’s online learning platform gives you on-demand access to live training courses, in-depth learning paths, interactive coding environments, and a vast collection of text and video from O’Reilly and 200+ other publishers. For more information, visit https://oreilly.com. How to Contact Us Please address comments and questions concerning this book to the publisher: O’Reilly Media, Inc. 1005 Gravenstein Highway North Sebastopol, CA 95472 800-889-8969 (in the United States or Canada) 707-827-7019 (international or local) 707-829-0104 (fax) support@oreilly.com https://www.oreilly.com/about/contact.html We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at https://oreil.ly/excel-cookbook. For news and information about our books and courses, visit https://oreilly.com. Find us on LinkedIn: https://linkedin.com/company/oreilly-media Watch us on YouTube: https://youtube.com/oreillymedia Acknowledgments My heartfelt thanks go to my fantastic editor, Corbin Collins, for telling me when my sentences contained all the right words but not necessarily in the right order. He’s been an absolute dream to work with and made me feel valued. I can’t thank him enough. I also want to thank David Michelson for commissioning this book, Theresa Jones for helping me with my AsciiDoc queries, Chris Faucher for herding the book Preface | xv

through early release, Beth Kelly for guiding it through production, and Stephanie English for copyediting. Thanks also to Melissa Duffield for approaching me about running Excel courses on the O’Reilly platform. This book benefited from an incredible team of tech reviewers: Ann Brumwell, Jacqui Cope, and George Mount. Each brought a different perspective, and I thank them for sharing their insights, expertise, and feedback. Thanks also to those who have attended my Excel courses. Their real-world questions and feedback inspired many of the recipes in this book and helped me improve it. A special thank you to my husband, David, and to Mum and Dad for their love, sup‐ port, encouragement, and being there every step of the way. Thanks also to Andy, Chris, Ian, and Jess. xvi | Preface

CHAPTER 1 Workbooks, Worksheets, and Cells Excel has many features and tools to help you save time and produce slick, polished spreadsheets. This first chapter focuses on features generally useful when working with Excel files, such as themes and cell styles, defining templates, creating custom number formats, and using conditional formatting. It also covers time-saving tools such as Auto Fill, Flash Fill, and the Quick Access Toolbar and includes hidden gems such as custom lists and the Go To Special tool. 1.1 Using Themes Problem You have a workbook and want to apply a consistent look and feel to it. Solution When you create a new workbook, Excel applies a theme to it: a set of colors, fonts, and effects designed to give your workbook a consistent appearance. Excel includes several predefined themes, and you can switch to a different one by choosing Page Layout ⇒ Themes ⇒ Themes and selecting a theme from the Theme gallery. You can also create a custom theme as follows: 1. Select a set of theme colors by choosing Page Layout ⇒ Themes ⇒ Colors and selecting a predefined color set. Alternatively, create a new custom color set by choosing Page Layout ⇒ Themes ⇒ Colors and selecting the Customize Colors option. 1

2. Select a set of fonts—a heading font and a body font—by choosing Page Layout ⇒ Themes ⇒ Fonts and selecting a predefined font set. Alternatively, create a new custom font set by choosing Page Layout ⇒ Themes ⇒ Fonts and selecting the Customize Fonts option. 3. Select a set of effects to change the appearance of any drawing objects by choos‐ ing Page Layout ⇒ Themes ⇒ Effects and selecting an effect; note that you can use only predefined effects and can’t create your own. 4. Save the colors, fonts, and effects as a theme by choosing Page Layout ⇒ Themes ⇒ Themes ⇒ Save Current Theme, and selecting a save location. Once you’ve created a custom theme, apply it to a workbook by choosing Page Layout ⇒ Themes ⇒ Themes ⇒ Browse for Themes. Discussion A theme lets you apply a consistent look and feel to your workbook. You can choose one of Excel’s predefined themes or create a custom one to apply a corporate or per‐ sonal style. See Also To change the default theme Excel applies to new workbooks, see Recipe 1.7. 1.2 Using Cell Styles Problem You’ve applied a theme to a workbook and want to use it to style selected cells. Solution You can apply a theme’s fonts and colors to cells using cell styles. A cell style is a defined set of formatting characteristics—such as font, font size, and shading—that help you consistently style cells. Each style uses the fonts and colors associated with the workbook’s theme (see Recipe 1.1), so if you switch to a different theme, the cell styles update automatically. To apply a cell style, select the cells you want to format, choose Home ⇒ Styles ⇒ Cell Styles, and select a style. The styles are grouped according to purpose as follows: Good, Bad, and Neutral These styles let you format cells containing particularly good or bad results. 2 | Chapter 1: Workbooks, Worksheets, and Cells