book

Excel Cookbook

Name: Excel Cookbook
Author: Dawn Griffiths
ISBN: 9781098143329

by Dawn Griffiths

May 2024

Intermediate to advanced

592 pages

13h 44m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Preface
Conventions Used in This BookUsing Code ExamplesO’Reilly Online LearningHow to Contact UsAcknowledgments
1. Workbooks, Worksheets, and Cells
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. References and Structured Data
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. Using Formulas
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. Math and Engineering
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. Text Manipulation
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. Dates and Times
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. Array, Logic, and Lookup Functions
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. Statistical Analysis
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. The Analysis ToolPak
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. Financial 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. PivotTables
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. Charts
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. Graphics, Sparklines, and 3D Maps
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. What-If Analysis
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. Power Query
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. Power Pivot and the Data Model
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. LET, LAMBDA, and LAMBDA Helper Functions
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. Developer Tools: Macros, VBA, Controls, and XML
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
Index
About the Author

Content preview from Excel Cookbook

Chapter 5. Text Manipulation

Excel isn’t a word processor, but in many situations you’ll need to manipulate text strings and transform data to fit a particular model.

This chapter shows how to use formulas to perform common text manipulation exercises, including joining text, extracting numeric codes, removing extra spaces and characters, and applying text formats to currency, number, and date/time values.

Note

You can also perform many of the operations in this chapter using Excel’s Power Query tool. See Chapter 15 to find out more.

5.1 Concatenating Text

Problem

You have two or more text strings and want to join them.

Solution

Suppose cell A1 contains the text John, B1 contains Doe, and you want to combine the two text strings.

One method uses the & operator, which concatenates two text strings. Typing the formula =A1&B1 in cell C1, for example, adds the text in B1 to the end of that in A1 and returns the text JohnDoe. If you want to insert a space between the two text strings and return John Doe instead, you type the formula =A1&" "&B1; this adds a space to the end of the text in A1 before adding the text in B1.

Tip

You must surround any static text, such as spaces, with double quotes and omit them for anything you want Excel to evaluate, such as cell references. The formula =A1&B1, for example, joins the contents of cells A1 and B1, while the formula ="A1"&"B1" returns the text A1B1.

An alternative approach is to use the CONCAT function. Generally, you use =CONCAT(

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781098143312Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills