Excel Cookbook: Recipes for Mastering Microsoft Excel

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