Excel 2016 Formulas E-Book

Michael Alexander

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Serie: Mr. Spreadsheet's Bookshelf
• Sprache: Englisch
• Veröffentlichungsjahr: 2016

Seitenzahl: 996

Beschreibung

Leverage the full power of Excel formulas Excel 2016 Formulas is fully updated to cover all of the tips, tricks, and techniques you need to maximize the power of Excel 2016 through the use of formulas. This comprehensive book explains how to create financial formulas, release the power of array formulas, develop custom worksheet functions with VBA, debug formulas, and much more. Whether you're a beginner, a power user, or somewhere in between this is your essential go-to for the latest on Excel formulas. When conducting simple math or building highly complicated spreadsheets that require formulas up to the task, leveraging the right formula can heighten the accuracy and efficiency of your work, and can improve the speed with which you compile and analyze data. Understanding which formulas to use and knowing how to create a formula when you need to are essential. * Access tips, tricks, and techniques that have been fully updated to reflect the latest capabilities of Microsoft Excel * Create and use formulas that have the power to transform your Excel experience * Leverage supplemental material online, including sample files, templates, and worksheets from the book

Leseprobe

Excel® 2016 Formulas

Michael Alexander

Dick Kusleika

Excel® 2016 Formulas

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

Copyright © 2016 by John Wiley & Sons, Inc., Indianapolis, Indiana

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, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], 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 [phone], outside the United States at [phone] or fax [phone].

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.

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.

About the Technical Editor

Niek Otten lives in the Netherlands. He started in data processing in 1967 in an insurance company. He ran into Visicalc in 1980 and has been addicted to spreadsheets ever since. His first encounter with Excel (version 1!) was in 1985 on a Macintosh. Since 2005, Niek has been self-employed—reviewing books, writing articles, and developing Excel-related software, such as a high-performance actuarial add-in.

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

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

The Workings of Workbooks

The Excel User Interface

Protection Options

Chapter 2: Basic Facts About Formulas

Entering and Editing Formulas

Using Operators in Formulas

Calculating Formulas

Cell and Range References

Copying or Moving Formulas

Making an Exact Copy of a Formula

Converting Formulas to Values

Hiding Formulas

Errors in Formulas

Dealing with Circular References

Goal Seeking

Chapter 3: Working with Names

What’s in a Name?

A Name’s Scope

The Name Manager

Shortcuts for Creating Cell and Range Names

Creating Multisheet Names

Working with Range and Cell Names

How Excel Maintains Cell and Range Names

Potential Problems with Names

The Secret to Understanding Names

Advanced Techniques That Use Names

Part II: Leveraging Excel Functions

Chapter 4: Introducing Worksheet Functions

What Is a Function?

Function Argument Types

Ways to Enter a Function into a Formula

Chapter 5: Manipulating Text

A Few Words About Text

Text Functions

Advanced Text Formulas

Chapter 6: Working with Dates and Times

How Excel Handles Dates and Times

Date-Related Functions

Time-Related Functions

Chapter 7: Counting and Summing Techniques

Counting and Summing Worksheet Cells

Other Counting Methods

Basic Counting Formulas

Advanced Counting Formulas

Summing Formulas

Conditional Sums Using a Single Criterion

Conditional Sums Using Multiple Criteria

Chapter 8: Using Lookup Functions

What Is a Lookup Formula?

Functions Relevant to Lookups

Basic Lookup Formulas

Specialized Lookup Formulas

Chapter 9: Working with Tables and Lists

Tables and Terminology

Working with Tables

Using Advanced Filtering

Specifying Advanced Filter Criteria

Using Database Functions

Inserting Subtotals

Chapter 10: Miscellaneous Calculations

Unit Conversions

Rounding Numbers

Solving Right Triangles

Area, Surface, Circumference, and Volume Calculations

Solving Simultaneous Equations

Working with Normal Distributions

Part III: Financial Formulas

Chapter 11: Borrowing and Investing Formulas

The Time Value of Money

Loan Calculations

Investment Calculations

Chapter 12: Discounting and Depreciation Formulas

Using the NPV Function

Using the IRR Function

Irregular Cash Flows

Depreciation Calculations

Chapter 13: Financial Schedules

Creating Financial Schedules

Creating Amortization Schedules

Summarizing Loan Options Using a Data Table

Financial Statements and Ratios

Creating Indices

Part IV: Array Formulas

Chapter 14: Introducing Arrays

Introducing Array Formulas

Understanding the Dimensions of an Array

Naming Array Constants

Working with Array Formulas

Using Multicell Array Formulas

Using Single-Cell Array Formulas

Chapter 15: Performing Magic with Array Formulas

Working with Single-Cell Array Formulas

Working with Multicell Array Formulas

Part V: Miscellaneous Formula Techniques

Chapter 16: Importing and Cleaning Data

A Few Words About Data

Importing Data

Data Cleanup Techniques

A Data Cleaning Checklist

Exporting Data

Chapter 17: Charting Techniques

Understanding the SERIES Formula

Creating Links to Cells

Chart Examples

Creating a Timeline

Working with Trendlines

Creating Interactive Charts

Chapter 18: Pivot Tables

About Pivot Tables

A Pivot Table Example

Data Appropriate for a Pivot Table

Creating a Pivot Table Automatically

Creating a Pivot Table Manually

More Pivot Table Examples

Grouping Pivot Table Items

Creating a Frequency Distribution

Creating a Calculated Field or Calculated Item

Filtering Pivot Tables with Slicers

Filtering Pivot Tables with a Timeline

Referencing Cells Within a Pivot Table

Another Pivot Table Example

Using the Data Model

Creating Pivot Charts

Chapter 19: Conditional Formatting

About Conditional Formatting

Specifying Conditional Formatting

Conditional Formats That Use Graphics

Creating Formula-Based Rules

Working with Conditional Formats

Chapter 20: Using Data Validation

About Data Validation

Specifying Validation Criteria

Types of Validation Criteria You Can Apply

Creating a Drop-Down List

Using Formulas for Data Validation Rules

Understanding Cell References

Data Validation Formula Examples

Chapter 21: Creating Megaformulas

What Is a Megaformula?

Creating a Megaformula: A Simple Example

Megaformula Examples

The Pros and Cons of Megaformulas

Chapter 22: Tools and Methods for Debugging Formulas

Formula Debugging?

Formula Problems and Solutions

Excel’s Auditing Tools

Part VI: Developing Custom Worksheet Functions

Chapter 23: Introducing VBA

Fundamental Macro Concepts

Working in the Visual Basic Editor

Chapter 24: VBA Programming Concepts

A Brief Overview of the Excel Object Model

An Introductory Example Function Procedure

Using Built-In VBA Functions

Controlling Execution

Using Ranges

Chapter 25: Function Procedure Basics

Why Create Custom Functions?

An Introductory VBA Function Example

About Function Procedures

Using the Insert Function Dialog Box

Testing and Debugging Your Functions

Creating Add-Ins for Functions

Chapter 26: VBA Custom Function Examples

Simple Functions

Determining a Cell’s Data Type

A Multifunctional Function

Generating Random Numbers

Calculating Sales Commissions

Text Manipulation Functions

Counting Functions

Date Functions

Returning the Last Nonempty Cell in a Column or Row

Multisheet Functions

Advanced Function Techniques

Part VII: Appendixes

Appendix A: Excel Function Reference

Appendix B: Using Custom Number Formats

About Number Formatting

Creating a Custom Number Format

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

n

th 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

n

th 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.

Guide

Cover

Table of Contents

Part I

Pages

v

vii

xxvii

xxviii

xxix

xxx

xxxi

xxxii

1

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

83

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

180

181

182

183

184

185

186

187

188

189

190

191

192

193

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

441

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

391

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

417

418

419

420

421

422

423

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

469

470

471

472

473

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

512

513

514

515

516

517

518

519

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

547

548

549

550

551

552

553

554

555

556

557

558

559

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

605

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

714

715

716

717

718

719

720

721

722

723

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761