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