book

Excel Cookbook

by Dawn Griffiths

May 2024

Intermediate to advanced

592 pages

13h 44m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Conventions Used in This BookUsing Code ExamplesO’Reilly Online LearningHow to Contact UsAcknowledgments
1.1. Using Themes1.2. Using Cell Styles1.3. Formatting Cells1.4. Formatting a Cell’s Value1.5. Defining a Custom Number Format1.6. Merging Cells1.7. Creating Templates1.8. Protecting Excel Files, Workbooks, Worksheets, and Cells1.9. Using Conditional Formatting1.10. Using the Format Painter1.11. Using Paste Special1.12. Using Auto Fill1.13. Using Custom Lists1.14. Using Flash Fill1.15. Customizing AutoCorrect1.16. Using Notes and Comments1.17. Finding and Selecting Cells and Navigation1.18. Creating a Custom View1.19. Customizing the Ribbon and Ribbon Tabs1.20. Using the Quick Access Toolbar1.21. Using the Accessibility Checker
2.1. Using Relative and Absolute References2.2. Using Relative and Absolute References in Conditional Formatting2.3. Using R1C1-Style Cell References2.4. Referencing Another Worksheet or Workbook2.5. Using 3-D References2.6. Naming Cells, Ranges, Constants, and Formulas2.7. Creating Dynamic Named Ranges2.8. Using Data Validation2.9. Creating a Custom Data Validation Rule2.10. Entering Data with a Drop-Down List2.11. Defining Dependent or Cascading Drop-Down Lists2.12. Using a Data-Entry Form2.13. Sorting Data by Value, Format, or Custom List2.14. Filtering Data2.15. Freezing Panes2.16. Using AutoSum2.17. Using Outlines to Add Subtotals and Groups2.18. Using Tables2.19. Using Structured References
3.1. Using Operators and Order of Precedence3.2. Using Excel in Different Regions and Languages3.3. Using Array Constants3.4. Using Dynamic and Legacy Array Formulas3.5. Using Spill Range References3.6. Preventing Dynamic Array Behavior3.7. Using the Insert Function or Function Builder Tool3.8. Adding Notes to Numeric Formulas3.9. Showing Formulas3.10. Using the Watch Window3.11. Showing Cell Interdependencies3.12. Performing Background Error Checks3.13. Using Error Checking3.14. Tracing Errors3.15. Correcting Error Values3.16. Evaluating Formulas3.17. Changing the Calculation Mode3.18. Setting Rounding Precision3.19. Resolving Circular References
4.1. Generating Numbers4.2. Converting Text or a Boolean to a Number4.3. Getting a Number’s Sign and Absolute Value4.4. Counting, Summing, and Averaging Cell Values4.5. Using Criteria to Count, Sum, and Average4.6. Adding and Subtracting Squares of Values4.7. Using Multiplication and Multiples4.8. Finding Quotients, Remainders, and Divisors4.9. Rounding to Decimal Places and Integers4.10. Rounding to Significant Figures and Multiples4.11. Using Powers, Exponents, Square Roots, and Logarithms4.12. Summing a Power Series4.13. Using Factorials, Permutations, and Combinations4.14. Using Trigonometry4.15. Working with Matrices4.16. Converting Between Number Systems4.17. Performing Bitwise Operations4.18. Working with Complex Numbers
5.1. Concatenating Text5.2. Using Character Codes5.3. Generating a Sequence of Characters5.4. Generating Random Letters5.5. Finding the Length of a Text String5.6. Finding Text Position in a Text String5.7. Getting Fixed-Width Text from a Text String5.8. Getting Text from a Text String by Delimiter5.9. Getting Text from a Text String by Digit to Nondigit5.10. Replacing, Inserting, and Deleting Text5.11. Removing Extra Characters5.12. Counting Words or Specific Characters5.13. Changing Text Case5.14. Repeating Characters5.15. Converting an Array to Text5.16. Formatting Text as Currency5.17. Including Numeric Values in a Text String5.18. Including Date/Time Values in a Text String
6.1. Returning the Current Date and Time6.2. Getting Part of a Date/Time Value6.3. Getting the Day of the Week and Week of the Year6.4. Getting the Calendar or Fiscal Quarter6.5. Constructing Dates Using Day, Month, and Year6.6. Constructing Times Using Hours, Minutes, and Seconds6.7. Converting a Text Value to a Date/Time Serial Number6.8. Extracting the Date and Time from a Serial Number6.9. Adding Days, Months, and Years to a Date6.10. Adding Hours, Minutes, and Seconds to a Time6.11. Getting the Last Day of the Month6.12. Calculating the Year Fraction6.13. Calculating the Difference Between Dates and Times6.14. Using Working Days6.15. Getting a Sequence of Dates
7.1. Getting Unique Values7.2. Sorting an Array7.3. Filtering an Array7.4. Manipulating Arrays7.5. Using Logical True/False Criteria7.6. Evaluating AND and OR Conditions in Array Formulas7.7. Working with Types and Error Values7.8. Choosing Values to Return7.9. Looking Up Exact and Nearest Values7.10. Finding a Matching Value’s Index7.11. Using an Index to Return a Value7.12. Creating Indirect References to Cells and Ranges7.13. Getting a Cell’s Address7.14. Using Offset References
8.1. Creating a Frequency Table8.2. Showing Cumulative and Percentage Frequencies8.3. Using a Histogram or Pareto Chart8.4. Calculating Averages8.5. Ranking Numeric Data8.6. Finding the kth Largest or Smallest Value8.7. Dividing Data into Quartiles and Percentiles8.8. Calculating Ranges and Variances8.9. Finding Outliers8.10. Using a Box and Whisker Chart8.11. Calculating Skewness8.12. Calculating Probabilities Using a Probability Table8.13. Calculating Expectation and Variance8.14. Using the Binomial Distribution8.15. Using the Negative Binomial Distribution8.16. Using the Hypergeometric Distribution8.17. Using the Poisson Distribution8.18. Using the Exponential Distribution8.19. Using the Normal Distribution8.20. Using Z-Scores8.21. Calculating a Confidence Interval for the Population Mean8.22. Performing a Chi-Squared (χ2) Test for Independence8.23. Finding the Line of Best Fit8.24. Getting the Line of Best Fit’s Equation
9.1. Installing the Analysis ToolPak9.2. Generating Descriptive Statistics9.3. Generating Ordinal and Percentage Rank Statistics9.4. Generating a Frequency Distribution9.5. Generating Moving Averages9.6. Using Exponential Smoothing9.7. Generating a Random Sample9.8. Generating a Periodic Sample9.9. Drawing Random Numbers from a Distribution9.10. Generating a Correlation Matrix9.11. Generating a Covariance Matrix9.12. Performing a Linear Regression Analysis9.13. Performing a Two-Sample t-Test9.14. Performing a Two-Sample z-Test9.15. Performing a Paired Two-Sample t-Test9.16. Performing a Two-Sample F-Test for Variances9.17. Performing a One-Way ANOVA Test9.18. Performing a Two-Way ANOVA Test9.19. Running a Fourier Analysis

10.1. Calculating Fixed-Rate Loan Payments10.2. Calculating Interest and Principal Loan Payments10.3. Building a Variable Rate Loan Amortization Schedule10.4. Calculating the Term for a Fixed-Rate Loan10.5. Calculating the Principal or Present Value10.6. Converting Between Nominal and Effective Rates10.7. Calculating the Future Value of a Fixed-Rate Lump-Sum Investment10.8. Calculating the Future Value of a Variable-Rate Lump-Sum Investment10.9. Calculating the Future Value of an Investment with Regular Deposits10.10. Meeting Investment Goals10.11. Calculating Net Present Value10.12. Calculating the Internal Rate of Return10.13. Calculating Depreciation10.14. Getting Stock and Currency Data10.15. Getting Historic Stock and Currency Data10.16. Using Stock Charts10.17. Calculating a Stock’s Beta10.18. Forecasting Linear and Exponential Growth10.19. Forecasting Seasonal Growth
11.1. Organizing Data for PivotTables11.2. Inserting a PivotTable11.3. Adding Rows, Columns, and Values11.4. Using Secondary Rows11.5. Refreshing a PivotTable’s Data11.6. Moving a PivotTable11.7. Changing a PivotTable’s Appearance11.8. Changing the Default Layout11.9. Changing Value Aggregations11.10. Showing Different Value Calculations11.11. Creating Custom Subtotals11.12. Sorting Data11.13. Moving Items Manually11.14. Filtering Data11.15. Using a Filter to Create Multiple PivotTables11.16. Grouping by Date/Time11.17. Grouping by Number11.18. Manually Grouping by Text Values11.19. Including Groups with Missing Data11.20. Changing the Format of Empty Cells11.21. Using Calculated Fields11.22. Using Calculated Fields to Count Items11.23. Using Calculated Items11.24. Referring to Position in a Calculated Item Formula11.25. Changing the Calculated Item Solve Order11.26. Generating a List of Custom Formulas11.27. Changing a PivotTable’s Data Source11.28. Using the PivotTable Cache11.29. Filtering Multiple PivotTables That Share a Cache11.30. Reducing the Workbook File Size11.31. Reinstating a PivotTable’s Source Data11.32. Referring to PivotTable Values
12.1. Using Different Chart Types12.2. Inserting a Chart12.3. Filtering a Chart12.4. Tweaking a Chart’s Appearance12.5. Adding and Removing Chart Elements12.6. Formatting Chart Elements12.7. Creating Dynamic Titles and Labels12.8. Customizing Data Label Text12.9. Controlling Chart Axes and Gridlines12.10. Displaying Negative Values12.11. Using Pictures in Column Charts12.12. Formatting Pie of Pie and Bar of Pie Charts12.13. Formatting a Histogram Chart12.14. Specifying a Combination Chart’s Chart Types12.15. Handling Empty Cells12.16. Basing a Chart on Noncontiguous Data12.17. Changing a Data Series Name and Legend Entry12.18. Adding a Series or Changing the Data Source12.19. Basing a Chart on a Dynamic Named Range12.20. Inserting a PivotChart12.21. Creating a Gantt Chart12.22. Creating and Using Chart Templates
13.1. Inserting Symbols13.2. Inserting Equations13.3. Inserting Shapes13.4. Using the Draw Tool13.5. Using SmartArt13.6. Inserting Pictures13.7. Grouping Objects13.8. Moving and Sizing Objects with Cells13.9. Inserting a Linked Picture13.10. Using Sparklines13.11. Using Sparkline Groups13.12. Using 3D Maps13.13. Creating Videos with 3D Maps
14.1. Creating a One-Variable Data Table14.2. Creating a Row-Oriented One-Variable Data Table14.3. Creating a Two-Variable Data Table14.4. Editing Data Tables14.5. Using Scenario Manager14.6. Merging Scenarios14.7. Generating Scenario Summaries14.8. Using Goal Seek14.9. Finding Multiple Solutions with Goal Seek14.10. Handling Discontinuous Formulas with Goal Seek14.11. Enabling Solver14.12. Solving an Optimization Problem with Solver14.13. Using Integer-Only Constraints with Solver14.14. Using Binary-Only Constraints with Solver14.15. Making Changing Cells All Different with Solver14.16. Handling Discontinuities with Solver14.17. Finding Multiple Solutions with Solver14.18. Finding a Formula’s Global Minimum or Maximum with Solver14.19. Adjusting Solver’s Options14.20. Saving and Loading Solver Parameters14.21. Saving Solver-Generated Scenarios14.22. Displaying Solver Reports
15.1. Getting and Loading Data15.2. Getting and Loading Data from Files in a Folder15.3. Specifying Where to Load Data To15.4. Editing Data Source Settings and Security15.5. Refreshing a Query’s Data15.6. Managing Queries15.7. Editing a Query15.8. Managing a Query’s Steps15.9. Managing Columns15.10. Using Data Types15.11. Sorting and Filtering Data15.12. Filtering Files When Loading Data from a Folder15.13. Removing Duplicates, Blank Rows, and Errors15.14. Transforming Data in Columns15.15. Splitting and Merging Columns15.16. Pivoting Columns15.17. Unpivoting Columns15.18. Transforming Structured Columns15.19. Returning a Value or List15.20. Adding New Columns15.21. Adding a Column Based on Examples15.22. Adding a Conditional Column15.23. Adding a Custom Column15.24. Using Parameters15.25. Creating a Custom Function15.26. Adding a Column by Invoking a Custom Function15.27. Duplicating a Query15.28. Referencing a Query15.29. Appending Data from Multiple Queries15.30. Merging Data from Multiple Queries15.31. Editing a Query’s M Code
16.1. Installing Power Pivot16.2. Adding Data to the Data Model16.3. Managing Power Pivot Data Connections16.4. Viewing and Managing the Data Model’s Tables16.5. Refreshing the Data Model’s Data16.6. Working with Table Columns16.7. Creating and Editing Relationships16.8. Adding a Calculated Column16.9. Basing a PivotTable or PivotChart on Data Model Tables16.10. Inserting Measures16.11. Using KPIs16.12. Creating Hierarchies16.13. Creating a Date Table16.14. Using Named Sets16.15. Converting a PivotTable to Formulas16.16. Using Cube Formulas16.17. Filtering Cube Formulas with Slicers and Timelines
17.1. Improving Formula Efficiency17.2. Writing and Testing a LAMBDA Formula17.3. Making LAMBDA Arguments Optional17.4. Defining a Custom LAMBDA Function17.5. Writing Recursive LAMBDA Formulas17.6. Copying a Custom LAMBDA Function to Another Workbook17.7. Applying a LAMBDA Formula to Each Column17.8. Applying a LAMBDA Formula to Each Row17.9. Creating an Array of Calculated Values17.10. Transforming the Values in Arrays17.11. Calculating Cumulative Values17.12. Returning the Final Value of a Cumulative Calculation
18.1. Showing the Developer Tab18.2. Recording a Macro18.3. Using a Personal Macro Workbook18.4. Editing a Macro’s Options18.5. Running a Macro18.6. Viewing or Editing a Macro’s VBA Code18.7. Using Absolute and Relative References18.8. Creating a Macro by Writing VBA18.9. Creating a Custom VBA Function18.10. Using Worksheet and Workbook Events18.11. Overriding Keystrokes with OnKey18.12. Scheduling Code with OnTime18.13. Deleting a Macro or Function18.14. Copying Code to Another VBA Project18.15. Debugging VBA Code18.16. Using Built-in Dialog Boxes18.17. Using Form Controls18.18. Using ActiveX Controls18.19. Creating a UserForm18.20. Creating a Custom Excel Add-in18.21. Setting Security and Privacy Options18.22. Importing and Exporting XML

Content preview from Excel Cookbook

Chapter 14. What-If Analysis

What-if analysis involves trying different values to examine their effect. For example, you may want to determine how changing an interest rate affects loan repayments or find the most efficient way to schedule your workforce.

The recipes in this chapter cover four kinds of what-if analysis tools available in Excel: Data Tables, Scenarios, Goal Seek, and Solver. The areas covered include:

Producing results tables showing the impact of one or two variables assuming different values
Saving sets of values as scenarios and switching between them
Finding the cell values needed to meet goals and return specific results
Solving optimization problems, such as maximizing profit given constraints, workforce scheduling, task allocation, and route planning

14.1 Creating a One-Variable Data Table

Problem

You want to create a table showing how substituting different values in a single cell changes the results of one or more formulas.

Solution

Suppose you have a worksheet that calculates the amount you would need to pay each month if you took out a fixed-rate loan, where cell B1 contains the loan amount, B2 contains the loan term in months, B3 contains the annual rate, and cell B4 uses the PMT function (see Recipe 10.1) to calculate the monthly payment. You want to see how changing the loan term affects the monthly payment and total amount to pay.

You can solve this problem by creating a one-variable data table: a range of cells that show how substituting ...