Excel Cookbook

Excel Cookbook

by Dawn Griffiths
Released May 2024
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781098143329

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.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

Filled with tips, tricks, and techniques, this easy-to-use book is the perfect resource for intermediate to advanced users of Excel. You'll find complete recipes for more than 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, or financial analyst, 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.

This cookbook shows you how to:

  • 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

Publisher resources

View/Submit Errata

Table of contents

  1. Preface
    1. Conventions Used in This Book
    2. Using Code Examples
    3. O’Reilly Online Learning
    4. How to Contact Us
    5. Acknowledgments
  2. 1. Workbooks, Worksheets, and Cells
    1. 1.1. Using Themes
    2. 1.2. Using Cell Styles
    3. 1.3. Formatting Cells
    4. 1.4. Formatting a Cell’s Value
    5. 1.5. Defining a Custom Number Format
    6. 1.6. Merging Cells
    7. 1.7. Creating Templates
    8. 1.8. Protecting Excel Files, Workbooks, Worksheets, and Cells
    9. 1.9. Using Conditional Formatting
    10. 1.10. Using the Format Painter
    11. 1.11. Using Paste Special
    12. 1.12. Using Auto Fill
    13. 1.13. Using Custom Lists
    14. 1.14. Using Flash Fill
    15. 1.15. Customizing AutoCorrect
    16. 1.16. Using Notes and Comments
    17. 1.17. Finding and Selecting Cells and Navigation
    18. 1.18. Creating a Custom View
    19. 1.19. Customizing the Ribbon and Ribbon Tabs
    20. 1.20. Using the Quick Access Toolbar
    21. 1.21. Using the Accessibility Checker
  3. 2. References and Structured Data
    1. 2.1. Using Relative and Absolute References
    2. 2.2. Using Relative and Absolute References in Conditional Formatting
    3. 2.3. Using R1C1-Style Cell References
    4. 2.4. Referencing Another Worksheet or Workbook
    5. 2.5. Using 3-D References
    6. 2.6. Naming Cells, Ranges, Constants, and Formulas
    7. 2.7. Creating Dynamic Named Ranges
    8. 2.8. Using Data Validation
    9. 2.9. Creating a Custom Data Validation Rule
    10. 2.10. Entering Data with a Drop-Down List
    11. 2.11. Defining Dependent or Cascading Drop-Down Lists
    12. 2.12. Using a Data-Entry Form
    13. 2.13. Sorting Data by Value, Format, or Custom List
    14. 2.14. Filtering Data
    15. 2.15. Freezing Panes
    16. 2.16. Using AutoSum
    17. 2.17. Using Outlines to Add Subtotals and Groups
    18. 2.18. Using Tables
    19. 2.19. Using Structured References
  4. 3. Using Formulas
    1. 3.1. Using Operators and Order of Precedence
    2. 3.2. Using Excel in Different Regions and Languages
    3. 3.3. Using Array Constants
    4. 3.4. Using Dynamic and Legacy Array Formulas
    5. 3.5. Using Spill Range References
    6. 3.6. Preventing Dynamic Array Behavior
    7. 3.7. Using the Insert Function or Function Builder Tool
    8. 3.8. Adding Notes to Numeric Formulas
    9. 3.9. Showing Formulas
    10. 3.10. Using the Watch Window
    11. 3.11. Showing Cell Interdependencies
    12. 3.12. Performing Background Error Checks
    13. 3.13. Using Error Checking
    14. 3.14. Tracing Errors
    15. 3.15. Correcting Error Values
    16. 3.16. Evaluating Formulas
    17. 3.17. Changing the Calculation Mode
    18. 3.18. Setting Rounding Precision
    19. 3.19. Resolving Circular References
  5. 4. Math and Engineering
    1. 4.1. Generating Numbers
    2. 4.2. Converting Text or a Boolean to a Number
    3. 4.3. Getting a Number’s Sign and Absolute Value
    4. 4.4. Counting, Summing, and Averaging Cell Values
    5. 4.5. Using Criteria to Count, Sum, and Average
    6. 4.6. Adding and Subtracting Squares of Values
    7. 4.7. Using Multiplication and Multiples
    8. 4.8. Finding Quotients, Remainders, and Divisors
    9. 4.9. Rounding to Decimal Places and Integers
    10. 4.10. Rounding to Significant Figures and Multiples
    11. 4.11. Using Powers, Exponents, Square Roots, and Logarithms
    12. 4.12. Summing a Power Series
    13. 4.13. Using Factorials, Permutations, and Combinations
    14. 4.14. Using Trigonometry
    15. 4.15. Working with Matrices
    16. 4.16. Converting Between Number Systems
    17. 4.17. Performing Bitwise Operations
    18. 4.18. Working with Complex Numbers
  6. 5. Text Manipulation
    1. 5.1. Concatenating Text
    2. 5.2. Using Character Codes
    3. 5.3. Generating a Sequence of Characters
    4. 5.4. Generating Random Letters
    5. 5.5. Finding the Length of a Text String
    6. 5.6. Finding Text Position in a Text String
    7. 5.7. Getting Fixed-Width Text from a Text String
    8. 5.8. Getting Text from a Text String by Delimiter
    9. 5.9. Getting Text from a Text String by Digit to Nondigit
    10. 5.10. Replacing, Inserting, and Deleting Text
    11. 5.11. Removing Extra Characters
    12. 5.12. Counting Words or Specific Characters
    13. 5.13. Changing Text Case
    14. 5.14. Repeating Characters
    15. 5.15. Converting an Array to Text
    16. 5.16. Formatting Text as Currency
    17. 5.17. Including Numeric Values in a Text String
    18. 5.18. Including Date/Time Values in a Text String
  7. 6. Dates and Times
    1. 6.1. Returning the Current Date and Time
    2. 6.2. Getting Part of a Date/Time Value
    3. 6.3. Getting the Day of the Week and Week of the Year
    4. 6.4. Getting the Calendar or Fiscal Quarter
    5. 6.5. Constructing Dates Using Day, Month, and Year
    6. 6.6. Constructing Times Using Hours, Minutes, and Seconds
    7. 6.7. Converting a Text Value to a Date/Time Serial Number
    8. 6.8. Extracting the Date and Time from a Serial Number
    9. 6.9. Adding Days, Months, and Years to a Date
    10. 6.10. Adding Hours, Minutes, and Seconds to a Time
    11. 6.11. Getting the Last Day of the Month
    12. 6.12. Calculating the Year Fraction
    13. 6.13. Calculating the Difference Between Dates and Times
    14. 6.14. Using Working Days
    15. 6.15. Getting a Sequence of Dates
  8. 7. Array, Logic, and Lookup Functions
    1. 7.1. Getting Unique Values
    2. 7.2. Sorting an Array
    3. 7.3. Filtering an Array
    4. 7.4. Manipulating Arrays
    5. 7.5. Using Logical True/False Criteria
    6. 7.6. Evaluating AND and OR Conditions in Array Formulas
    7. 7.7. Working with Types and Error Values
    8. 7.8. Choosing Values to Return
    9. 7.9. Looking Up Exact and Nearest Values
    10. 7.10. Finding a Matching Value’s Index
    11. 7.11. Using an Index to Return a Value
    12. 7.12. Creating Indirect References to Cells and Ranges
    13. 7.13. Getting a Cell’s Address
    14. 7.14. Using Offset References
  9. 8. Statistical Analysis
    1. 8.1. Creating a Frequency Table
    2. 8.2. Showing Cumulative and Percentage Frequencies
    3. 8.3. Using a Histogram or Pareto Chart
    4. 8.4. Calculating Averages
    5. 8.5. Ranking Numeric Data
    6. 8.6. Finding the kth Largest or Smallest Value
    7. 8.7. Dividing Data into Quartiles and Percentiles
    8. 8.8. Calculating Ranges and Variances
    9. 8.9. Finding Outliers
    10. 8.10. Using a Box and Whisker Chart
    11. 8.11. Calculating Skewness
    12. 8.12. Calculating Probabilities Using a Probability Table
    13. 8.13. Calculating Expectation and Variance
    14. 8.14. Using the Binomial Distribution
    15. 8.15. Using the Negative Binomial Distribution
    16. 8.16. Using the Hypergeometric Distribution
    17. 8.17. Using the Poisson Distribution
    18. 8.18. Using the Exponential Distribution
    19. 8.19. Using the Normal Distribution
    20. 8.20. Using Z-Scores
    21. 8.21. Calculating a Confidence Interval for the Population Mean
    22. 8.22. Performing a Chi-Squared (χ2) Test for Independence
    23. 8.23. Finding the Line of Best Fit
    24. 8.24. Getting the Line of Best Fit’s Equation
  10. 9. The Analysis ToolPak
    1. 9.1. Installing the Analysis ToolPak
    2. 9.2. Generating Descriptive Statistics
    3. 9.3. Generating Ordinal and Percentage Rank Statistics
    4. 9.4. Generating a Frequency Distribution
    5. 9.5. Generating Moving Averages
    6. 9.6. Using Exponential Smoothing
    7. 9.7. Generating a Random Sample
    8. 9.8. Generating a Periodic Sample
    9. 9.9. Drawing Random Numbers from a Distribution
    10. 9.10. Generating a Correlation Matrix
    11. 9.11. Generating a Covariance Matrix
    12. 9.12. Performing a Linear Regression Analysis
    13. 9.13. Performing a Two-Sample t-Test
    14. 9.14. Performing a Two-Sample z-Test
    15. 9.15. Performing a Paired Two-Sample t-Test
    16. 9.16. Performing a Two-Sample F-Test for Variances
    17. 9.17. Performing a One-Way ANOVA Test
    18. 9.18. Performing a Two-Way ANOVA Test
    19. 9.19. Running a Fourier Analysis
  11. 10. Financial Analysis
    1. 10.1. Calculating Fixed-Rate Loan Payments
    2. 10.2. Calculating Interest and Principal Loan Payments
    3. 10.3. Building a Variable Rate Loan Amortization Schedule
    4. 10.4. Calculating the Term for a Fixed-Rate Loan
    5. 10.5. Calculating the Principal or Present Value
    6. 10.6. Converting Between Nominal and Effective Rates
    7. 10.7. Calculating the Future Value of a Fixed-Rate Lump-Sum Investment
    8. 10.8. Calculating the Future Value of a Variable-Rate Lump-Sum Investment
    9. 10.9. Calculating the Future Value of an Investment with Regular Deposits
    10. 10.10. Meeting Investment Goals
    11. 10.11. Calculating Net Present Value
    12. 10.12. Calculating the Internal Rate of Return
    13. 10.13. Calculating Depreciation
    14. 10.14. Getting Stock and Currency Data
    15. 10.15. Getting Historic Stock and Currency Data
    16. 10.16. Using Stock Charts
    17. 10.17. Calculating a Stock’s Beta
    18. 10.18. Forecasting Linear and Exponential Growth
    19. 10.19. Forecasting Seasonal Growth
  12. 11. PivotTables
    1. 11.1. Organizing Data for PivotTables
    2. 11.2. Inserting a PivotTable
    3. 11.3. Adding Rows, Columns, and Values
    4. 11.4. Using Secondary Rows
    5. 11.5. Refreshing a PivotTable’s Data
    6. 11.6. Moving a PivotTable
    7. 11.7. Changing a PivotTable’s Appearance
    8. 11.8. Changing the Default Layout
    9. 11.9. Changing Value Aggregations
    10. 11.10. Showing Different Value Calculations
    11. 11.11. Creating Custom Subtotals
    12. 11.12. Sorting Data
    13. 11.13. Moving Items Manually
    14. 11.14. Filtering Data
    15. 11.15. Using a Filter to Create Multiple PivotTables
    16. 11.16. Grouping by Date/Time
    17. 11.17. Grouping by Number
    18. 11.18. Manually Grouping by Text Values
    19. 11.19. Including Groups with Missing Data
    20. 11.20. Changing the Format of Empty Cells
    21. 11.21. Using Calculated Fields
    22. 11.22. Using Calculated Fields to Count Items
    23. 11.23. Using Calculated Items
    24. 11.24. Referring to Position in a Calculated Item Formula
    25. 11.25. Changing the Calculated Item Solve Order
    26. 11.26. Generating a List of Custom Formulas
    27. 11.27. Changing a PivotTable’s Data Source
    28. 11.28. Using the PivotTable Cache
    29. 11.29. Filtering Multiple PivotTables That Share a Cache
    30. 11.30. Reducing the Workbook File Size
    31. 11.31. Reinstating a PivotTable’s Source Data
    32. 11.32. Referring to PivotTable Values
  13. 12. Charts
    1. 12.1. Using Different Chart Types
    2. 12.2. Inserting a Chart
    3. 12.3. Filtering a Chart
    4. 12.4. Tweaking a Chart’s Appearance
    5. 12.5. Adding and Removing Chart Elements
    6. 12.6. Formatting Chart Elements
    7. 12.7. Creating Dynamic Titles and Labels
    8. 12.8. Customizing Data Label Text
    9. 12.9. Controlling Chart Axes and Gridlines
    10. 12.10. Displaying Negative Values
    11. 12.11. Using Pictures in Column Charts
    12. 12.12. Formatting Pie of Pie and Bar of Pie Charts
    13. 12.13. Formatting a Histogram Chart
    14. 12.14. Specifying a Combination Chart’s Chart Types
    15. 12.15. Handling Empty Cells
    16. 12.16. Basing a Chart on Noncontiguous Data
    17. 12.17. Changing a Data Series Name and Legend Entry
    18. 12.18. Adding a Series or Changing the Data Source
    19. 12.19. Basing a Chart on a Dynamic Named Range
    20. 12.20. Inserting a PivotChart
    21. 12.21. Creating a Gantt Chart
    22. 12.22. Creating and Using Chart Templates
  14. 13. Graphics, Sparklines, and 3D Maps
    1. 13.1. Inserting Symbols
    2. 13.2. Inserting Equations
    3. 13.3. Inserting Shapes
    4. 13.4. Using the Draw Tool
    5. 13.5. Using SmartArt
    6. 13.6. Inserting Pictures
    7. 13.7. Grouping Objects
    8. 13.8. Moving and Sizing Objects with Cells
    9. 13.9. Inserting a Linked Picture
    10. 13.10. Using Sparklines
    11. 13.11. Using Sparkline Groups
    12. 13.12. Using 3D Maps
    13. 13.13. Creating Videos with 3D Maps
  15. 14. What-If Analysis
    1. 14.1. Creating a One-Variable Data Table
    2. 14.2. Creating a Row-Oriented One-Variable Data Table
    3. 14.3. Creating a Two-Variable Data Table
    4. 14.4. Editing Data Tables
    5. 14.5. Using Scenario Manager
    6. 14.6. Merging Scenarios
    7. 14.7. Generating Scenario Summaries
    8. 14.8. Using Goal Seek
    9. 14.9. Finding Multiple Solutions with Goal Seek
    10. 14.10. Handling Discontinuous Formulas with Goal Seek
    11. 14.11. Enabling Solver
    12. 14.12. Solving an Optimization Problem with Solver
    13. 14.13. Using Integer-Only Constraints with Solver
    14. 14.14. Using Binary-Only Constraints with Solver
    15. 14.15. Making Changing Cells All Different with Solver
    16. 14.16. Handling Discontinuities with Solver
    17. 14.17. Finding Multiple Solutions with Solver
    18. 14.18. Finding a Formula’s Global Minimum or Maximum with Solver
    19. 14.19. Adjusting Solver’s Options
    20. 14.20. Saving and Loading Solver Parameters
    21. 14.21. Saving Solver-Generated Scenarios
    22. 14.22. Displaying Solver Reports
  16. 15. Power Query
    1. 15.1. Getting and Loading Data
    2. 15.2. Getting and Loading Data from Files in a Folder
    3. 15.3. Specifying Where to Load Data To
    4. 15.4. Editing Data Source Settings and Security
    5. 15.5. Refreshing a Query’s Data
    6. 15.6. Managing Queries
    7. 15.7. Editing a Query
    8. 15.8. Managing a Query’s Steps
    9. 15.9. Managing Columns
    10. 15.10. Using Data Types
    11. 15.11. Sorting and Filtering Data
    12. 15.12. Filtering Files When Loading Data from a Folder
    13. 15.13. Removing Duplicates, Blank Rows, and Errors
    14. 15.14. Transforming Data in Columns
    15. 15.15. Splitting and Merging Columns
    16. 15.16. Pivoting Columns
    17. 15.17. Unpivoting Columns
    18. 15.18. Transforming Structured Columns
    19. 15.19. Returning a Value or List
    20. 15.20. Adding New Columns
    21. 15.21. Adding a Column Based on Examples
    22. 15.22. Adding a Conditional Column
    23. 15.23. Adding a Custom Column
    24. 15.24. Using Parameters
    25. 15.25. Creating a Custom Function
    26. 15.26. Adding a Column by Invoking a Custom Function
    27. 15.27. Duplicating a Query
    28. 15.28. Referencing a Query
    29. 15.29. Appending Data from Multiple Queries
    30. 15.30. Merging Data from Multiple Queries
    31. 15.31. Editing a Query’s M Code
  17. 16. Power Pivot and the Data Model
    1. 16.1. Installing Power Pivot
    2. 16.2. Adding Data to the Data Model
    3. 16.3. Managing Power Pivot Data Connections
    4. 16.4. Viewing and Managing the Data Model’s Tables
    5. 16.5. Refreshing the Data Model’s Data
    6. 16.6. Working with Table Columns
    7. 16.7. Creating and Editing Relationships
    8. 16.8. Adding a Calculated Column
    9. 16.9. Basing a PivotTable or PivotChart on Data Model Tables
    10. 16.10. Inserting Measures
    11. 16.11. Using KPIs
    12. 16.12. Creating Hierarchies
    13. 16.13. Creating a Date Table
    14. 16.14. Using Named Sets
    15. 16.15. Converting a PivotTable to Formulas
    16. 16.16. Using Cube Formulas
    17. 16.17. Filtering Cube Formulas with Slicers and Timelines
  18. 17. LET, LAMBDA, and LAMBDA Helper Functions
    1. 17.1. Improving Formula Efficiency
    2. 17.2. Writing and Testing a LAMBDA Formula
    3. 17.3. Making LAMBDA Arguments Optional
    4. 17.4. Defining a Custom LAMBDA Function
    5. 17.5. Writing Recursive LAMBDA Formulas
    6. 17.6. Copying a Custom LAMBDA Function to Another Workbook
    7. 17.7. Applying a LAMBDA Formula to Each Column
    8. 17.8. Applying a LAMBDA Formula to Each Row
    9. 17.9. Creating an Array of Calculated Values
    10. 17.10. Transforming the Values in Arrays
    11. 17.11. Calculating Cumulative Values
    12. 17.12. Returning the Final Value of a Cumulative Calculation
  19. 18. Developer Tools: Macros, VBA, Controls, and XML
    1. 18.1. Showing the Developer Tab
    2. 18.2. Recording a Macro
    3. 18.3. Using a Personal Macro Workbook
    4. 18.4. Editing a Macro’s Options
    5. 18.5. Running a Macro
    6. 18.6. Viewing or Editing a Macro’s VBA Code
    7. 18.7. Using Absolute and Relative References
    8. 18.8. Creating a Macro by Writing VBA
    9. 18.9. Creating a Custom VBA Function
    10. 18.10. Using Worksheet and Workbook Events
    11. 18.11. Overriding Keystrokes with OnKey
    12. 18.12. Scheduling Code with OnTime
    13. 18.13. Deleting a Macro or Function
    14. 18.14. Copying Code to Another VBA Project
    15. 18.15. Debugging VBA Code
    16. 18.16. Using Built-in Dialog Boxes
    17. 18.17. Using Form Controls
    18. 18.18. Using ActiveX Controls
    19. 18.19. Creating a UserForm
    20. 18.20. Creating a Custom Excel Add-in
    21. 18.21. Setting Security and Privacy Options
    22. 18.22. Importing and Exporting XML
  20. Index
  21. About the Author

Product information

  • Title: Excel Cookbook
  • Author(s): Dawn Griffiths
  • Release date: May 2024
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781098143329