Excel® 2016 Formulas

Published by
John Wiley & Sons, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com

ISBN: 978-1-119-06786-3

ISBN: 978-1-119-06792-4 (ebk)

ISBN: 978-1-119-06798-6 (ebk)

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or website may provide or recommendations it may make. Further, readers should be aware that Internet websites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services please contact our Customer Care Department within the United States at (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Library of Congress Control Number: 2015958259

Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Excel is a registered trademark of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

CONTENTS

Introduction

What You Need to Know
What You Need to Have
Conventions in This Book
How This Book Is Organized
About This Book’s Website
About the Power Utility Pak Offer

Part I: Understanding Formula Basics

Chapter 1: The Excel User Interface in a Nutshell
1. The Workings of Workbooks
2. The Excel User Interface
3. Protection Options
Chapter 2: Basic Facts About Formulas
1. Entering and Editing Formulas
2. Using Operators in Formulas
3. Calculating Formulas
4. Cell and Range References
5. Copying or Moving Formulas
6. Making an Exact Copy of a Formula
7. Converting Formulas to Values
8. Hiding Formulas
9. Errors in Formulas
10. Dealing with Circular References
11. Goal Seeking
Chapter 3: Working with Names
1. What’s in a Name?
2. A Name’s Scope
3. The Name Manager
4. Shortcuts for Creating Cell and Range Names
5. Creating Multisheet Names
6. Working with Range and Cell Names
7. How Excel Maintains Cell and Range Names
8. Potential Problems with Names
9. The Secret to Understanding Names
10. Advanced Techniques That Use Names

Part II: Leveraging Excel Functions

Chapter 4: Introducing Worksheet Functions
1. What Is a Function?
2. Function Argument Types
3. Ways to Enter a Function into a Formula
Chapter 5: Manipulating Text
1. A Few Words About Text
2. Text Functions
3. Advanced Text Formulas
Chapter 6: Working with Dates and Times
1. How Excel Handles Dates and Times
2. Date-Related Functions
3. Time-Related Functions
Chapter 7: Counting and Summing Techniques
1. Counting and Summing Worksheet Cells
2. Other Counting Methods
3. Basic Counting Formulas
4. Advanced Counting Formulas
5. Summing Formulas
6. Conditional Sums Using a Single Criterion
7. Conditional Sums Using Multiple Criteria
Chapter 8: Using Lookup Functions
1. What Is a Lookup Formula?
2. Functions Relevant to Lookups
3. Basic Lookup Formulas
4. Specialized Lookup Formulas
Chapter 9: Working with Tables and Lists
1. Tables and Terminology
2. Working with Tables
3. Using Advanced Filtering
4. Specifying Advanced Filter Criteria
5. Using Database Functions
6. Inserting Subtotals
Chapter 10: Miscellaneous Calculations
1. Unit Conversions
2. Rounding Numbers
3. Solving Right Triangles
4. Area, Surface, Circumference, and Volume Calculations
5. Solving Simultaneous Equations
6. Working with Normal Distributions

Part III: Financial Formulas

Chapter 11: Borrowing and Investing Formulas
1. The Time Value of Money
2. Loan Calculations
3. Investment Calculations
Chapter 12: Discounting and Depreciation Formulas
1. Using the NPV Function
2. Using the IRR Function
3. Irregular Cash Flows
4. Depreciation Calculations
Chapter 13: Financial Schedules
1. Creating Financial Schedules
2. Creating Amortization Schedules
3. Summarizing Loan Options Using a Data Table
4. Financial Statements and Ratios
5. Creating Indices

Part IV: Array Formulas

Chapter 14: Introducing Arrays
1. Introducing Array Formulas
2. Understanding the Dimensions of an Array
3. Naming Array Constants
4. Working with Array Formulas
5. Using Multicell Array Formulas
6. Using Single-Cell Array Formulas
Chapter 15: Performing Magic with Array Formulas
1. Working with Single-Cell Array Formulas
2. Working with Multicell Array Formulas

Part V: Miscellaneous Formula Techniques

Chapter 16: Importing and Cleaning Data
1. A Few Words About Data
2. Importing Data
3. Data Cleanup Techniques
4. A Data Cleaning Checklist
5. Exporting Data
Chapter 17: Charting Techniques
1. Understanding the SERIES Formula
2. Creating Links to Cells
3. Chart Examples
4. Creating a Timeline
5. Working with Trendlines
6. Creating Interactive Charts
Chapter 18: Pivot Tables
1. About Pivot Tables
2. A Pivot Table Example
3. Data Appropriate for a Pivot Table
4. Creating a Pivot Table Automatically
5. Creating a Pivot Table Manually
6. More Pivot Table Examples
7. Grouping Pivot Table Items
8. Creating a Frequency Distribution
9. Creating a Calculated Field or Calculated Item
10. Filtering Pivot Tables with Slicers
11. Filtering Pivot Tables with a Timeline
12. Referencing Cells Within a Pivot Table
13. Another Pivot Table Example
14. Using the Data Model
15. Creating Pivot Charts
Chapter 19: Conditional Formatting
1. About Conditional Formatting
2. Specifying Conditional Formatting
3. Conditional Formats That Use Graphics
4. Creating Formula-Based Rules
5. Working with Conditional Formats
Chapter 20: Using Data Validation
1. About Data Validation
2. Specifying Validation Criteria
3. Types of Validation Criteria You Can Apply
4. Creating a Drop-Down List
5. Using Formulas for Data Validation Rules
6. Understanding Cell References
7. Data Validation Formula Examples
Chapter 21: Creating Megaformulas
1. What Is a Megaformula?
2. Creating a Megaformula: A Simple Example
3. Megaformula Examples
4. The Pros and Cons of Megaformulas
Chapter 22: Tools and Methods for Debugging Formulas
1. Formula Debugging?
2. Formula Problems and Solutions
3. Excel’s Auditing Tools

Part VI: Developing Custom Worksheet Functions

Chapter 23: Introducing VBA
1. Fundamental Macro Concepts
2. Working in the Visual Basic Editor
Chapter 24: VBA Programming Concepts
1. A Brief Overview of the Excel Object Model
2. An Introductory Example Function Procedure
3. Using Built-In VBA Functions
4. Controlling Execution
5. Using Ranges
Chapter 25: Function Procedure Basics
1. Why Create Custom Functions?
2. An Introductory VBA Function Example
3. About Function Procedures
4. Using the Insert Function Dialog Box
5. Testing and Debugging Your Functions
6. Creating Add-Ins for Functions
Chapter 26: VBA Custom Function Examples
1. Simple Functions
2. Determining a Cell’s Data Type
3. A Multifunctional Function
4. Generating Random Numbers
5. Calculating Sales Commissions
6. Text Manipulation Functions
7. Counting Functions
8. Date Functions
9. Returning the Last Nonempty Cell in a Column or Row
10. Multisheet Functions
11. Advanced Function Techniques

Part VII: Appendixes

Appendix A: Excel Function Reference
Appendix B: Using Custom Number Formats
1. About Number Formatting
2. Creating a Custom Number Format
3. Custom Number Format Examples

Advert

EULA

List of Tables

Chapter 2

Table 2.1
Table 2.2
Table 2.3
Table 2.4

Chapter 6

Table 6.1
Table 6.2
Table 6.3
Table 6.4
Table 6.5

Chapter 7

Table 7.1
Table 7.2

Chapter 8

Table 8.1
Table 8.2

Chapter 9

Table 9.1
Table 9.2
Table 9.3
Table 9.4

Chapter 10

Table 10.1
Table 10.2
Table 10.3

Chapter 11

Table 11.1
Table 11.2
Table 11.3
Table 11.4

Chapter 12

Table 12.1

Chapter 17

Table 17.1
Table 17.2

Chapter 21

Table 21.1
Table 21.2
Table 21.3
Table 21.4
Table 21.5

Chapter 22

Table 22.1

Chapter 24

Table 24.1

Chapter 25

Table 25.1

Chapter 26

Table 26.1

Appendix A

Table A.1
Table A.2
Table A.3
Table A.4
Table A.5
Table A.6
Table A.7
Table A.8
Table A.9
Table A.10
Table A.11
Table A.12
Table A.13

Appendix B

Table B.1
Table B.2
Table B.3
Table B.4
Table B.5
Table B.6
Table B.7
Table B.8
Table B.9

List of Illustrations

Chapter 1

Figure 1.1 The File Sharing options are well hidden away in the Save As dialog box under General Options.
Figure 1.2 Type the password needed to modify the file.
Figure 1.3 A password is now needed to make changes to the file.
Figure 1.4 Type the password needed to modify the file.
Figure 1.5 Although this sheet is protected, users can enter data into the input cells provided.
Figure 1.6 To ensure that a cell remains unlocked when the worksheet is protected, deselect the Locked check box.
Figure 1.7 Select Protect Sheet in the Review tab.
Figure 1.8 Specify a password that removes worksheet protection.
Figure 1.9 The Unprotect Sheet icon removes worksheet protection.
Figure 1.10 The Protect Structure and Windows dialog box.

Chapter 2

Figure 2.1 Using Formula AutoComplete to enter a range name into a formula.
Figure 2.2 This formula contains spaces and line breaks.
Figure 2.3 This worksheet demonstrates the use of an absolute reference.
Figure 2.4 An example of using mixed references in a formula.
Figure 2.5 Choosing a paste option after pasting data.
Figure 2.6 Use the Format Cells dialog box to change the Hidden and Locked status of a cell or range.
Figure 2.7 Excel’s way of telling you that your formula contains a circular reference.
Figure 2.8 This worksheet presents a simple demonstration of goal seeking.
Figure 2.9 The Goal Seek dialog box.
Figure 2.10 The Goal Seek Status dialog box.

Chapter 3

Figure 3.1 The Name Manager dialog box.
Figure 3.2 The New Name dialog box.
Figure 3.3 The Name box provides a quick way to select a named cell or range.
Figure 3.4 Excel makes it easy to create names by using text in adjacent cells.
Figure 3.5 The Create Names from Selection dialog box.
Figure 3.6 This worksheet, which tracks daily sales, uses a named range that consists of an entire column.
Figure 3.7 Create a multisheet name.
Figure 3.8 Use the Paste Name dialog box to create a list of names.
Figure 3.9 The formula in cell B15 uses the intersection operator.
Figure 3.10 Range A2:A13 in this worksheet is named MonthNames. Cell G5 demonstrates an implicit intersection.
Figure 3.11 The Apply Names dialog box.
Figure 3.12 Excel displays range names when you zoom a sheet to 39 percent or less.
Figure 3.13 Defining a name that refers to a constant.
Figure 3.14 Defining a named formula that uses worksheet functions.
Figure 3.15 Defining a named formula that uses a cell reference.
Figure 3.16 Using the INDIRECT function with a named range.
Figure 3.17 You can use a dynamic named formula to represent the sales data in column B.
Figure 3.18 Using an XLM macro in a named formula can generate a list of file names in a worksheet.

Chapter 4

Figure 4.1 When you begin to type a function, Excel lists available functions that begin with the typed letters.
Figure 4.2 Excel displays a list of the function’s arguments.
Figure 4.3 The icons in the Function Library group on the Formulas tab.
Figure 4.4 The Insert Function dialog box.
Figure 4.5 The Function Arguments dialog box.
Figure 4.6 Don't forget about Excel's Help system. It's the most comprehensive function reference source available.

Chapter 5

Figure 5.1 Excel’s background error checking flags numbers that are formatted as text.
Figure 5.2 The ANSI character set (for the Calibri font).
Figure 5.3 The formula in cell D2 doesn’t display the formatted number.
Figure 5.4 Using the REPT function to create a histogram in a worksheet range.
Figure 5.5 Using a formula to pad a number with asterisks.
Figure 5.6 Using a formula to express a number as an ordinal.
Figure 5.7 This worksheet uses formulas to extract the first name, middle name (or initial), and last name from a list of names in column A.

Chapter 6

Figure 6.1 Use the Number Format drop-down list to change the appearance of dates and times.
Figure 6.2 Use the Number tab of the Format Cells dialog box to change the appearance of dates and times.
Figure 6.3 Using Excel’s AutoFill feature to create a series of dates.
Figure 6.4 Using the NETWORKDAYS function to calculate the number of working days between two dates.
Figure 6.5 Calculating the number of each weekday in each month of a year.
Figure 6.6 Using formulas to determine the date for various holidays.
Figure 6.7 Calculating the number of hours worked returns an error if the shift spans midnight.
Figure 6.8 Incorrect cell formatting makes the total appear incorrectly.
Figure 6.9 An employee timesheet workbook.
Figure 6.10 Using a formula to create a series of incremental times.
Figure 6.11 This worksheet converts dates and times between time zones.
Figure 6.12 This worksheet uses times not associated with a time of day.

Chapter 7

Figure 7.1 Formulas provide various counts of the data in A1:B10.
Figure 7.2 This worksheet demonstrates various counting techniques that use multiple criteria.
Figure 7.3 The MODE function returns the most frequently occurring value in a range.
Figure 7.4 This worksheet demonstrates various ways to count characters in a range.
Figure 7.5 Using an array formula to count the number of unique values in a range.
Figure 7.6 Creating a frequency distribution for the data in A1:E25.
Figure 7.7 Frequency distributions created using the FREQUENCY function.
Figure 7.8 Creating a frequency distribution of test scores.
Figure 7.9 The Analysis ToolPak’s Histogram dialog box.
Figure 7.10 A frequency distribution and chart generated by the Analysis ToolPak’s Histogram option.
Figure 7.11 Summarizing grades with a pivot table and pivot chart.
Figure 7.12 The chart displays a histogram; the contents of cell E1 determine the number of categories.
Figure 7.13 Simple formulas in column C display a cumulative sum of the values in column B.
Figure 7.14 Using an IF function to hide cumulative sums for missing data.
Figure 7.15 Using an array formula to calculate the sum of the 30 largest values in a range.
Figure 7.16 A negative value in column F indicates a past-due payment.
Figure 7.17 This worksheet demonstrates summing based on multiple criteria.

Chapter 8

Figure 8.1 Lookup formulas in row 2 look up the information for the last name in cell C2.
Figure 8.2 Using VLOOKUP to look up a tax rate.
Figure 8.3 Using HLOOKUP to look up a tax rate.
Figure 8.4 Using LOOKUP to look up a tax rate.
Figure 8.5 Using the INDEX and MATCH functions to perform a lookup.
Figure 8.6 This lookup table requires an exact match.
Figure 8.7 The VLOOKUP function can’t look up a value in column B based on a value in column C.
Figure 8.8 Using an array formula to perform a case-sensitive lookup.
Figure 8.9 This worksheet demonstrates the use of multiple lookup tables.
Figure 8.10 Looking up letter grades for test scores.
Figure 8.11 Using multiple formulas to calculate a GPA.
Figure 8.12 This table demonstrates a two-way lookup.
Figure 8.13 This workbook performs a lookup by using information in two columns (D and E).
Figure 8.14 The formula in cell B2 returns the address in the Data range for the value in cell B1.
Figure 8.15 This workbook demonstrates how to perform a lookup by using the closest match.
Figure 8.16 This worksheet demonstrates a table lookup using linear interpolation.
Figure 8.17 Column B contains formulas that perform a lookup using linear interpolation.
Figure 8.18 This worksheet uses a formula that uses the LOOKUP function and the TREND function.

Chapter 9

Figure 9.1 A typical list.
Figure 9.2 A list, converted to a table.
Figure 9.3 When you select a cell in a table, you can use the commands on the Table Tools contextual tab.
Figure 9.4 Excel offers many different table styles.
Figure 9.5 Removing duplicate rows from a table is easy.
Figure 9.6 Each column in a table contains sorting and filtering options.
Figure 9.7 A table, after performing a three-column sort.
Figure 9.8 Using the Sort dialog box to specify a three-column sort.
Figure 9.9 Filtering a table to show only the information for N. County.
Figure 9.10 The table is filtered by two Slicers.
Figure 9.11 Several types of summary functions are available for the Total row.
Figure 9.12 Adding a calculated column to this table is easy.
Figure 9.13 The Difference column contains a formula.
Figure 9.14 This table shows sales by month and by region.
Figure 9.15 The Formula AutoComplete feature is useful when creating a formula that refers to data in a table.
Figure 9.16 This real estate listing table is used to demonstrate advanced filtering.
Figure 9.17 A criteria range for advanced filtering.
Figure 9.18 The Advanced Filter dialog box.
Figure 9.19 The result of applying an advanced filter.
Figure 9.20 This criteria range uses multiple columns that select records using a logical AND operation.
Figure 9.21 This criteria range selects records that describe properties that were listed in the month of August.
Figure 9.22 This criteria range has two sets of criteria, each of which is in a separate row.
Figure 9.23 Using computed criteria with advanced filtering.
Figure 9.24 Using the DSUM function to sum a table using a criteria range.
Figure 9.25 This list is a good candidate for subtotals, which are inserted at each change of the month.
Figure 9.26 The Subtotal dialog box automatically inserts subtotal formulas into a sorted table.
Figure 9.27 Excel adds the subtotal formulas automatically and creates an outline.
Figure 9.28 Use the outline controls to hide the detail and display only the summary rows.
Figure 9.29 Use a pivot table to summarize data. Formulas are not required.

Chapter 10

Figure 10.1 A table that lists all the area units supported by the CONVERT function.
Figure 10.2 A table that lists all the units supported by the CONVERT function.
Figure 10.3 A right triangle’s components.
Figure 10.4 This workbook is useful for working with right triangles.
Figure 10.5 Using formulas to solve simultaneous equations.
Figure 10.6 Using formulas to calculate a normal distribution and a cumulative normal distribution.
Figure 10.7 Comparing a set of data with a normal distribution.

Chapter 11

Figure 11.1 Using the PMT function to calculate a periodic loan payment amount.
Figure 11.2 This chart shows how the interest and principal amounts vary during the payment periods of a loan.
Figure 11.3 This worksheet calculates the number of payments required to pay off a credit card balance by paying the minimum payment amount each month.
Figure 11.4 The second column shows the payment required to pay off the credit card balance for various payoff periods.
Figure 11.5 A loan amortization schedule.
Figure 11.6 This worksheet tracks loan payments that are made on an irregular basis.
Figure 11.7 This worksheet calculates simple interest payments.
Figure 11.8 Using a series of formulas to calculate compound interest.
Figure 11.9 Using a single formula to calculate compound interest.
Figure 11.10 Calculating interest by using daily compounding.
Figure 11.11 This worksheet contains formulas to calculate annuities.

Chapter 12

Figure 12.1 Three methods of computing NPV.
Figure 12.2 An initial investment returns positive future cash flows.
Figure 12.3 The NPV function can be used to determine the initial investment required.
Figure 12.4 Some NPV calculations include an initial cash inflow.
Figure 12.5 The initial investment may still have value at the end of the cash flows.
Figure 12.6 The NPV function can include an initial value and a terminal value.
Figure 12.7 The NPV function can accept multiple positive and negative cash flows.
Figure 12.8 The IRR returns the rate based on the cash flow frequency and should be converted into an annual rate.
Figure 12.9 Using the IRR function to calculate geometric average growth.
Figure 12.10 Checking IRR and NPV using the sum of PV approach.
Figure 12.11 The XNPV function works with irregular cash flows.
Figure 12.12 The XIRR function works with irregular cash flows.
Figure 12.13 A comparison of four depreciation functions.
Figure 12.14 This chart shows an asset’s value over time, using four depreciation functions.
Figure 12.15 Using the VDB function to calculate accumulated depreciation.

Chapter 13

Figure 13.1 A simple amortization schedule.
Figure 13.2 A dynamic amortization schedule.
Figure 13.3 Calculating a credit card payment schedule.
Figure 13.4 The layout for a one-way data table.
Figure 13.5 Using a one-way data table to display three loan calculations for various interest rates.
Figure 13.6 The Data Table dialog box
Figure 13.7 The structure for a two-way data table.
Figure 13.8 Using a two-way data table to display payment amounts for various loan amounts and interest rates.
Figure 13.9 A trial balance lists all accounts and balances.
Figure 13.10 A balance sheet summarizes certain accounts.
Figure 13.11 The income statement can include a statement of retained earnings.
Figure 13.12 Entries on a common size income statement are shown relative to revenue.
Figure 13.13 Various financial ratio calculations.
Figure 13.14 Creating an index from growth data.

Chapter 14

Figure 14.1 Column D contains formulas to calculate the total sales for each product.
Figure 14.2 An array formula to calculate the total sales.
Figure 14.3 A 3 × 4 array entered into a range of cells.
Figure 14.4 A 3 × 4 array entered into a 10 × 5 cell range.
Figure 14.5 Creating a named array constant.
Figure 14.6 Using a named array constant in an array formula.
Figure 14.7 Excel’s warning message reminds you that you can’t edit just one cell of a multicell array formula.
Figure 14.8 Creating an array from a range.
Figure 14.9 After you press F9, the cell references are converted to an array constant.
Figure 14.10 Performing a mathematical operation on an array.
Figure 14.11 Multiplying each array element by itself.
Figure 14.12 Using the TRANSPOSE function to transpose a rectangular array.
Figure 14.13 Using an array formula to generate consecutive integers.
Figure 14.14 The goal is to count the number of characters in a range of text.
Figure 14.15 An array formula returns the sum of the three smallest values in A1:A10.
Figure 14.16 An array formula returns the number of text cells in the range.
Figure 14.17 Without an array formula, calculating the average change requires intermediate formulas in column D.
Figure 14.18 You can replace the lookup table in D1:E10 with an array constant.

Chapter 15

Figure 15.1 An array formula can sum a range of values, even if the range contains errors.
Figure 15.2 The calculated average includes cells that contain a 0.
Figure 15.3 Using an array formula to determine whether a range contains a particular value.
Figure 15.4 Using an array formula to count the number of differences in two ranges.
Figure 15.5 Using an array formula to return the longest text in a range.
Figure 15.6 Using an array formula to count and identify items that aren’t in a list.
Figure 15.7 Two versions of an array formula that calculates the sum of the digits in an integer.
Figure 15.8 Using an array formula to correct rounding errors.
Figure 15.9 An array formula returns the sum of every nth value in the range.
Figure 15.10 An array formula returns the closest match.
Figure 15.11 Using array formulas to return the last nonempty cell in a column or row.
Figure 15.12 Using an array formula to return only the positive values in a range.
Figure 15.13 A multicell array formula displays the entries in A4:A13 in reverse order.
Figure 15.14 A multicell array formula displays the values in column A, sorted.
Figure 15.15 Using an array formula to return unique items from a list.
Figure 15.16 Displaying a calendar by using a single array formula.
Figure 15.17 An annual calendar made from array formulas.

Chapter 16

Figure 16.1 Filtering by file extension in the Open dialog box.
Figure 16.2 This CSV file will be imported into a range.
Figure 16.3 Using the Import Data dialog box to import a CSV file at a particular location.
Figure 16.4 This range contains data imported directly from a CSV file.
Figure 16.5 Use the Remove Duplicates dialog box to delete duplicate rows.
Figure 16.6 Using formulas to identify duplicate rows.
Figure 16.7 The imported data was put in one column rather than multiple columns.
Figure 16.8 The first dialog box in the Convert Text to Columns Wizard.
Figure 16.9 The goal is to extract the numbers in column A.
Figure 16.10 Using manually entered examples in B1 and B2, Excel makes some incorrect guesses.
Figure 16.11 After entering an example of a decimal number, Excel gets them all correct.
Figure 16.12 Using Flash Fill to split names.
Figure 16.13 Excel suggests a Flash Fill as you type.
Figure 16.14 Using Flash Fill to extract domains from URLs.
Figure 16.15 Using a lookup table to classify ages into age ranges.
Figure 16.16 Using a lookup table to assign a region for a state.
Figure 16.17 The goal is to identify member numbers that are in the resigned members list.
Figure 16.18 Vertical data that needs to be converted to three columns.
Figure 16.19 Use formulas to convert column data to row data.
Figure 16.20 Sort the data on the Mod column to group the data.
Figure 16.21 Each record of data is on its own row.
Figure 16.22 This report contains gaps in the Sales Rep column.
Figure 16.23 The gaps are gone, and this list can now be sorted.
Figure 16.24 Misspelled words can be ignored or changed.
Figure 16.25 To replace only the second hyphen in these cells, Find and Replace is not an option.
Figure 16.26 The Trailing Minus for Negative Numbers option makes it easy to fix trailing minus signs in a range of data.

Chapter 17

Figure 17.1 The Formula bar displays the SERIES formula for the selected data series in a chart.
Figure 17.2 A chart after being converted to a picture (and then edited).
Figure 17.3 The chart title is linked to cell A1.
Figure 17.4 Five single data point charts.
Figure 17.5 This single data point chart is a line chart, with a shape used as the marker.
Figure 17.6 This chart resembles a speedometer gauge and displays a value between 0 and 100 percent.
Figure 17.7 The color of the column varies with the value.
Figure 17.8 A comparative histogram.
Figure 17.9 You can create a simple Gantt chart from a bar chart.
Figure 17.10 This box plot summarizes the data in columns A through D.
Figure 17.11 This chart plots every nth data point (specified in A1) by ignoring data in the rows hidden by filtering.
Figure 17.12 This chart uses two XY series to highlight the maximum and minimum data points in the line series.
Figure 17.13 A scatter chart disguised as a timeline.
Figure 17.14 This chart plots the SIN(x).
Figure 17.15 A general-purpose, single-variable plotting workbook.
Figure 17.16 Using a surface chart to plot a function with two variables.
Figure 17.17 A general-purpose, two-variable plotting workbook.
Figure 17.18 Creating a circle using an XY chart.
Figure 17.19 A general circle plotting application.
Figure 17.20 This fully functional clock is actually an XY chart in disguise.
Figure 17.21 Displaying a digital clock in a worksheet is much easier but not as much fun to create.
Figure 17.22 A hypocycloid curve.
Figure 17.23 Use the Format Trendline task pane to fine-tune trendlines.
Figure 17.24 Adding a linear trendline to an existing chart.
Figure 17.25 Using the LINEST function to calculate slope and y-intercept.
Figure 17.26 Column E contains formulas that calculate the predicted values for y.
Figure 17.27 Using a trendline to forecast values for two additional periods of time.
Figure 17.28 Using Excel 2016’s new forecasting functions.
Figure 17.29 Charts with various trendline options.
Figure 17.30 Selecting data to plot using a drop-down list.
Figure 17.31 This chart displays the most recent data points.
Figure 17.32 This chart displays data based on values specified by the user.
Figure 17.33 Population by age group, for two years.
Figure 17.34 This application uses a variety of techniques (but no VBA code) to plot monthly climate data for two selected U.S. cities.

Chapter 18

Figure 18.1 This table is used to create a pivot table.
Figure 18.2 A simple pivot table.
Figure 18.3 A pivot table that uses a report filter.
Figure 18.4 This range is not appropriate for a pivot table.
Figure 18.5 This range contains normalized data and is appropriate for a pivot table.
Figure 18.6 A pivot table created from normalized data.
Figure 18.7 Selecting a recommended pivot table.
Figure 18.8 In the Create PivotTable dialog box, you tell Excel where the data is and then specify a location for the pivot table.
Figure 18.9 Use the PivotTable Fields task pane to build the pivot table.
Figure 18.10 Right-click anywhere in the data to summarize using a different aggregate.
Figure 18.11 After a few simple steps, the pivot table shows a summary of the data.
Figure 18.12 The PivotTable Options dialog box.
Figure 18.13 Two fields are used for row labels.
Figure 18.14 The pivot table is filtered by date.
Figure 18.15 This pivot table shows daily totals for each branch.
Figure 18.16 This pivot table shows totals by day of the week.
Figure 18.17 This pivot table uses the Count function to summarize the data.
Figure 18.18 This pivot table counts the number of accounts that fall into each value range.
Figure 18.19 This pivot table uses a report filter to show only the teller data.
Figure 18.20 This pivot table (and pivot chart) compares the Central branch with the other two branches combined.
Figure 18.21 This pivot table uses three report filters.
Figure 18.22 A pivot table before creating groups of states.
Figure 18.23 A pivot table with two groups and subtotals for the groups.
Figure 18.24 Pivot tables with options for subtotals and grand totals.
Figure 18.25 You can use a pivot table to summarize the sales data by month.
Figure 18.26 The pivot table, before grouping by month.
Figure 18.27 Use the Grouping dialog box to group pivot table items by dates.
Figure 18.28 The pivot table, after grouping by years and months.
Figure 18.29 This pivot table shows sales by quarter and by year.
Figure 18.30 This pivot table is grouped by hours.
Figure 18.31 Creating a frequency distribution for these test scores is simple.
Figure 18.32 The pivot table and pivot chart show the frequency distribution for the test scores.
Figure 18.33 This data demonstrates calculated fields and calculated items.
Figure 18.34 This pivot table was created from the sales data.
Figure 18.35 The Insert Calculated Field dialog box.
Figure 18.36 This pivot table uses a calculated field.
Figure 18.37 The Insert Calculated Item dialog box.
Figure 18.38 This pivot table uses calculated items for quarterly totals.
Figure 18.39 The pivot table, after creating two groups and adding subtotals.
Figure 18.40 Using slicers to filter the data displayed in a pivot table.
Figure 18.41 Using a slicer to filter a pivot table by state.
Figure 18.42 Using a timeline to filter a pivot table by date.
Figure 18.43 The formulas in column F reference cells in the pivot table.
Figure 18.44 After expanding the pivot table, formulas that used the GETPIVOTDATA function continue to display the correct result.
Figure 18.45 This table contains data for each county in the United States.
Figure 18.46 This pivot table was created from the county data.
Figure 18.47 This worksheet lists calculated fields and items for the pivot table.
Figure 18.48 This custom list ensures that the Region names are sorted correctly.
Figure 18.49 These three tables will be used for a pivot table, using the Data Model.
Figure 18.50 The PivotTable Fields task pane, with three active tables.
Figure 18.51 Creating a relationship between two tables.
Figure 18.52 The pivot table, after adding two slicers.
Figure 18.53 This data will be used to create a pivot chart.
Figure 18.54 This pivot table summarizes sales by region and by month.
Figure 18.55 The pivot chart uses the data displayed in the pivot table.
Figure 18.56 If you modify the pivot table, the pivot chart is also changed.

Chapter 19

Figure 19.1 This worksheet demonstrates a few conditional formatting rules.
Figure 19.2 One of several different conditional formatting dialog boxes.
Figure 19.3 Use the New Formatting Rule dialog box to create your own conditional formatting rules.
Figure 19.4 The length of the data bars is proportional to the track length in the cell in column D.
Figure 19.5 Comparing data bars conditional formatting (top) with a bar chart.
Figure 19.6 Two examples of color scale conditional formatting.
Figure 19.7 Use the Edit Formatting Rule dialog box to customize a color scale.
Figure 19.8 This worksheet uses color scale conditional formatting to display daily temperatures.
Figure 19.9 Using an icon set to indicate the status of projects.
Figure 19.10 Changing the icon assignment rule.
Figure 19.11 Using a modified rule and eliminating an icon makes the table more readable.
Figure 19.12 The arrows depict the trend from Test 1 to Test 2.
Figure 19.13 Hiding one of the icons makes the table less cluttered.
Figure 19.14 Creating a conditional formatting rule based on a formula.
Figure 19.15 Highlighting a row, based on a matching name.
Figure 19.16 Using conditional formatting to apply formatting to alternate rows.
Figure 19.17 Conditional formatting produces these groups of alternate shaded rows.
Figure 19.18 The sum is displayed only when all four values have been entered.
Figure 19.19 A missing value causes the sum to be hidden.
Figure 19.20 Using a custom VBA function to apply conditional formatting to cells that contain a formula.
Figure 19.21 Using conditional formatting to highlight cells with invalid entries.
Figure 19.22 This range uses data bars, color scales, and icon sets.

Chapter 20

Figure 20.1 Displaying an input message and a message when the user makes an invalid entry.
Figure 20.2 The three tabs of the Data Validation dialog box.
Figure 20.3 Excel can draw circles around invalid entries (in this case, cells that contain values less than 1 or greater than 100).
Figure 20.4 This drop-down list (with an Input Message) was created using data validation.
Figure 20.5 Entering a data validation formula.
Figure 20.6 Using data validation to prevent duplicate entries in a range.
Figure 20.7 Using data validation to ensure that the sum of a range does not exceed a certain value.
Figure 20.8 The items displayed in the list in cell F2 depend on the list item selected in cell E2.
Figure 20.9 Structured references grow or shrink with the table.
Figure 20.10 Named ranges can refer to tables using structured references.
Figure 20.11 A defined name can be used in data validation to avoid the structured reference limitation.

Chapter 21

Figure 21.1 This spreadsheet uses multiple formulas to calculate mortgage loan information.
Figure 21.2 The goal is to remove the middle name or middle initial from each name.
Figure 21.3 Removing the middle names and initials requires six intermediate formulas.
Figure 21.4 These intermediate formulas will eventually be converted to a single megaformula.
Figure 21.5 Column B contains a megaformula that returns the character position of the last space of the name in column A.
Figure 21.6 The formulas in this worksheet determine the validity of a credit card number.
Figure 21.7 Using a megaformula to determine the validity of credit card numbers.
Figure 21.8 This workbook uses a megaformula to generate realistic random names.

Chapter 22

Figure 22.1 Using a formula to identify cells that contain extra space characters.
Figure 22.2 #DIV/0! errors occur when the data in column B is missing.
Figure 22.3 Formulas in the range C4:C7 use an absolute reference to cell C1.
Figure 22.4 A simple demonstration of numbers that appear to add up incorrectly.
Figure 22.5 Excel’s way of asking whether you want to update links in a workbook.
Figure 22.6 This worksheet demonstrates an inconsistency when summing logical values.
Figure 22.7 The Go to Special dialog box.
Figure 22.8 Displaying formulas (bottom window) and their results (top window).
Figure 22.9 This worksheet displays lines that indicate cell precedents for the formula in cell C13.
Figure 22.10 Excel can check your formulas for potential errors.
Figure 22.11 Clicking an error’s icon gives you a list of options.
Figure 22.12 Using the Error Checking dialog box to cycle through potential errors that Excel identifies.
Figure 22.13 Excel’s Formula Evaluator shows a formula being calculated one step at a time.

Chapter 23

Figure 23.1 The Record Macro dialog box.
Figure 23.2 The Trusted Locations window allows you to add directories that are considered trusted.
Figure 23.3 You can find the form controls in the Developer tab.
Figure 23.4 Assign a macro to the newly added button.
Figure 23.5 Adding a macro to the Quick Access toolbar.
Figure 23.6 The VBE with significant elements identified.
Figure 23.7 This Project window lists two projects. They are expanded to show their objects.
Figure 23.8 Code modules are visible in the Project window in a folder called Modules.
Figure 23.9 The Editor tab in the Options dialog box.
Figure 23.10 Change the VBE’s looks with the Editor Format tab.
Figure 23.11 The General tab of the Options dialog box.
Figure 23.12 The Docking tab of the Options dialog box.

Chapter 24

Figure 24.1 Displaying a list of VBA functions in the VB Editor.
Figure 24.2 The intersection of two ranges.

Chapter 25

Figure 25.1 A simple VBA function displayed in a code window.
Figure 25.2 Use the References dialog box to create a reference to a project that contains a custom VBA function.
Figure 25.3 Excel’s Insert Function dialog box displays a brief description of the selected function.
Figure 25.4 Provide a function description in the Macro Options dialog box.
Figure 25.5 Executing a VBA statement that assigns a function to a particular function category.
Figure 25.6 Use a MsgBox statement to monitor the value of a variable as a Function procedure executes.
Figure 25.7 Using the VB Editor Immediate window to display results while a function is running.
Figure 25.8 A runtime error identified by VBA.
Figure 25.9 The highlighted statement generated a runtime error.
Figure 25.10 The highlighted statement contains a breakpoint.

Chapter 26

Figure 26.1 The CELLTYPE function returns a string that describes the contents of a cell.
Figure 26.2 Selecting an operation from the list displays the result in cell C14.
Figure 26.3 Calculating sales commissions based on sales amount and years employed.
Figure 26.4 A VBA function that determines whether a particular word is contained in a string.
Figure 26.5 Examples of the SPELLDOLLARS function.
Figure 26.6 Examples of the extended date function.
Figure 26.7 The MONTHNAMES function entered as an array formula.
Figure 26.8 An array formula generates nonduplicated consecutive integers, arranged randomly.
Figure 26.9 The RANGERANDOMIZE function returns the contents of a range, but in a randomized order.
Figure 26.10 Comparing Excel’s SUM function with a custom function.

Appendix B

Figure B.1 The Number tab of the Format Cells dialog box.

About the Authors

Michael Alexander is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Microsoft Excel. He has more than 15 years of experience consulting and developing Microsoft Office solutions. Mike has been named a Microsoft MVP for his ongoing contributions to the Excel community. In his spare time, he runs a free tutorial site, www.datapigtechnologies.com, where he shares Excel and Access tips.

Dick Kusleika has been awarded as a Microsoft MVP for 12 consecutive years and has been working with Microsoft Office for more than 20. Dick develops Access- and Excel-based solutions for his clients and has conducted training seminars on Office products in the United States and Australia. Dick also writes a popular Excel-related blog at www.dailydoseofexcel.com.

Credits

Acquisitions Editor

Stephanie McComb

Project Editor

Kelly Talbot

Technical Editor

Niek Otten

Production Editor

Rebecca Anderson

Copy Editor

Karen Gill

Manager of Content Development and Assembly

Mary Beth Wakefield

Marketing Director

David Mayhew

Marketing Manager

Carrie Sherrill

Professional Technology & Strategy Director

Barry Pruett

Business Manager

Amy Knies

Executive Editor

Jody Lefevere

Project Coordinator, Cover

Patrick Redmond

Proofreader

Rebecca Rider

Indexer

Johnna VanHoose Dinse

Cover Designer

Wiley

Excel^® 2016 Formulas

About the Authors

About the Technical Editor

Credits