Excel® 2013 Formulas

Table of Contents

Introduction

What You Need to Know

What You Need to Have

Conventions in This Book

Keyboard conventions

Mouse conventions

What the icons mean

How This Book Is Organized

Part I: Basic Information

Part II: Using Functions in Your Formulas

Part III: Financial Formulas

Part IV: Array Formulas

Part V: Miscellaneous Formula Techniques

Part VI: Developing Custom Worksheet Functions

Part VII: Appendixes

How to Use This Book

About This Book's Website

About the Power Utility Pak Offer

Part I: Basic Information

Chapter 1: Excel in a Nutshell

Excel: What Is It Good For?

What's New in Excel 2013?

The Object Model Concept

The Workings of Workbooks

Worksheets

Chart sheets

Macro sheets and dialog sheets

The Excel User Interface

The Ribbon

Backstage View

Shortcut menus and the mini toolbar

Dialog boxes

Customizing the UI

Task panes

Keyboard shortcuts

Customized onscreen display

Object and cell selecting

The Excel Help System

Cell Formatting

Numeric formatting

Stylistic formatting

Tables

Worksheet Formulas and Functions

Objects on the Drawing Layer

Shapes and illustrations

Controls

Charts

Sparkline graphics

Customizing Excel

Macros

Add-in programs

Internet Features

Analysis Tools

Database access

Scenario management

Pivot tables

Auditing capabilities

Solver add-in

Protection Options

Protecting formulas from being overwritten

Protecting a workbook's structure

Password-protecting a workbook

Chapter 2: Basic Facts about Formulas

Entering and Editing Formulas

Formula elements

Entering a formula

Pasting names

Spaces and line breaks

Formula limits

Sample formulas

Editing formulas

Using Operators in Formulas

Reference operators

Operator precedence

Calculating Formulas

Cell and Range References

Creating an absolute or a mixed reference

Referencing other sheets or workbooks

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

A goal seeking example

More about goal seeking

Chapter 3: Working with Names

What's in a Name?

A Name's Scope

Referencing names

Referencing names from another workbook

Conflicting names

The Name Manager

Creating names

Editing names

Deleting names

Shortcuts for Creating Cell and Range Names

The New Name dialog box

Creating names using the Name box

Creating names from text in cells

Naming entire rows and columns

Names created by Excel

Creating Multisheet Names

Working with Range and Cell Names

Creating a list of names

Using names in formulas

Using the intersection operators with names

Using the range operator with names

Referencing a single cell in a multicell named range

Applying names to existing formulas

Applying names automatically when creating a formula

Unapplying names

Names with errors

Viewing named ranges

Using names in charts

How Excel Maintains Cell and Range Names

Inserting a row or column

Deleting a row or column

Cutting and pasting

Potential Problems with Names

Name problems when copying sheets

Name problems when deleting sheets

The Secret to Understanding Names

Naming constants

Naming text constants

Using worksheet functions in named formulas

Using cell and range references in named formulas

Using named formulas with relative references

Advanced Techniques That Use Names

Using the INDIRECT function with a named range

Using arrays in named formulas

Creating a dynamic named formula

Using an XLM macro in a named formula

Part II: Using Functions in Your Formulas

Chapter 4: Introducing Worksheet Functions

What Is a Function?

Simplify your formulas

Perform otherwise impossible calculations

Speed up editing tasks

Provide decision-making capability

More about functions

Function Argument Types

Names as arguments

Full-column or full-row as arguments

Literal values as arguments

Expressions as arguments

Other functions as arguments

Arrays as arguments

Ways to Enter a Function into a Formula

Entering a function manually

Using the Function Library commands

Using the Insert Function dialog box

More tips for entering functions

Function Categories

Financial functions

Date and time functions

Math and trig functions

Statistical functions

Lookup and reference functions

Database functions

Text functions

Logical functions

Information functions

User-defined functions

Engineering functions

Cube functions

Compatibility functions

Web functions

Other function categories

Chapter 5: Manipulating Text

A Few Words about Text

How many characters in a cell?

Numbers as text

Text Functions

Determining whether a cell contains text

Working with character codes

Determining whether two strings are identical

Joining two or more cells

Displaying formatted values as text

Displaying formatted currency values as text

Removing excess spaces and nonprinting characters

Counting characters in a string

Repeating a character or string

Creating a text histogram

Padding a number

Changing the case of text

Extracting characters from a string

Replacing text with other text

Finding and searching within a string

Searching and replacing within a string

Advanced Text Formulas

Counting specific characters in a cell

Counting the occurrences of a substring in a cell

Removing trailing minus signs

Expressing a number as an ordinal

Determining a column letter for a column number

Extracting a filename from a path specification

Extracting the first word of a string

Extracting the last word of a string

Extracting all but the first word of a string

Extracting first names, middle names, and last names

Removing titles from names

Counting the number of words in a cell

Chapter 6: Working with Dates and Times

How Excel Handles Dates and Times

Understanding date serial numbers

Entering dates

Understanding time serial numbers

Entering times

Formatting dates and times

Problems with dates

Date-Related Functions

Displaying the current date

Displaying any date with a function

Generating a series of dates

Converting a non-date string to a date

Calculating the number of days between two dates

Calculating the number of work days between two dates

Offsetting a date using only work days

Calculating the number of years between two dates

Calculating a person's age

Determining the day of the year

Determining the day of the week

Determining the week of the year

Determining the date of the most recent Sunday

Determining the first day of the week after a date

Determining the nth occurrence of a day of the week in a month

Counting the occurrences of a day of the week

Expressing a date as an ordinal number

Calculating dates of holidays

Determining the last day of a month

Determining whether a year is a leap year

Determining a date's quarter

Converting a year to roman numerals

Time-Related Functions

Displaying the current time

Displaying any time using a function

Calculating the difference between two times

Summing times that exceed 24 hours

Converting from military time

Converting decimal hours, minutes, or seconds to a time

Adding hours, minutes, or seconds to a time

Converting between time zones

Rounding time values

Working with non–time-of-day values

Chapter 7: Counting and Summing Techniques

Counting and Summing Worksheet Cells

Other Counting Methods

Basic Counting Formulas

Counting the total number of cells

Counting blank cells

Counting nonblank cells

Counting numeric cells

Counting text cells

Counting nontext cells

Counting logical values

Counting error values in a range

Advanced Counting Formulas

Counting cells with the COUNTIF function

Counting cells that meet multiple criteria

Counting the most frequently occurring entry

Counting the occurrences of specific text

Counting the number of unique values

Creating a frequency distribution

Summing Formulas

Summing all cells in a range

Summing a range that contains errors

Computing a cumulative sum

Summing the “top n” values

Conditional Sums Using a Single Criterion

Summing only negative values

Summing values based on a different range

Summing values based on a text comparison

Summing values based on a date comparison

Conditional Sums Using Multiple Criteria

Using And criteria

Using Or criteria

Using And and Or criteria

Chapter 8: Using Lookup Functions

What Is a Lookup Formula?

Functions Relevant to Lookups

Basic Lookup Formulas

The VLOOKUP function

The HLOOKUP function

The LOOKUP function

Combining the MATCH and INDEX functions

Specialized Lookup Formulas

Looking up an exact value

Looking up a value to the left

Performing a case-sensitive lookup

Choosing among multiple lookup tables

Determining letter grades for test scores

Calculating a grade point average

Performing a two-way lookup

Performing a two-column lookup

Determining the address of a value within a range

Looking up a value by using the closest match

Looking up a value using linear interpolation

Chapter 9: Working with Tables and Lists

Tables and Terminology

A list example

A table example

Working with Tables

Creating a table

Changing the look of a table

Navigating and selecting in a table

Adding new rows or columns

Deleting rows or columns

Moving a table

Removing duplicate rows from a table

Sorting and filtering a table

Working with the Total row

Using formulas within a table

Referencing data in a table

Converting a table to a list

Using Advanced Filtering

Setting up a criteria range

Applying an advanced filter

Clearing an advanced filter

Specifying Advanced Filter Criteria

Specifying a single criterion

Specifying multiple criteria

Specifying computed criteria

Using Database Functions

Inserting Subtotals

Chapter 10: Miscellaneous Calculations

Unit Conversions

Rounding Numbers

Basic rounding formulas

Rounding to the nearest multiple

Rounding currency values

Working with fractional dollars

Using the INT and TRUNC functions

Rounding to an even or odd integer

Rounding to n significant digits

Solving Right Triangles

Area, Surface, Circumference, and Volume Calculations

Calculating the area and perimeter of a square

Calculating the area and perimeter of a rectangle

Calculating the area and perimeter of a circle

Calculating the area of a trapezoid

Calculating the area of a triangle

Calculating the surface and volume of a sphere

Calculating the surface and volume of a cube

Calculating the surface and volume of a rectangular solid

Calculating the surface and volume of a cone

Calculating the volume of a cylinder

Calculating the volume of a pyramid

Solving Simultaneous Equations

Working with Normal Distributions

Part III: Financial Formulas

Chapter 11: Borrowing and Investing Formulas

The Time Value of Money

Loan Calculations

Worksheet functions for calculating loan information

A loan calculation example

Credit card payments

Creating a loan amortization schedule

Calculating a loan with irregular payments

Investment Calculations

Future value of a single deposit

Present value of a series of payments

Future value of a series of deposits

Chapter 12: Discounting and Depreciation Formulas

Using the NPV Function

Definition of NPV

NPV function examples

Using the IRR Function

Rate of return

Geometric growth rates

Checking results

Irregular Cash Flows

Net present value

Internal rate of return

Depreciation Calculations

Chapter 13: Financial Schedules

Creating Financial Schedules

Creating Amortization Schedules

A simple amortization schedule

A dynamic amortization schedule

Credit card calculations

Summarizing Loan Options Using a Data Table

Creating a one-way data table

Creating a two-way data table

Financial Statements and Ratios

Basic financial statements

Ratio analysis

Creating Indices

Part IV: Array Formulas

Chapter 14: Introducing Arrays

Introducing Array Formulas

A multicell array formula

A single-cell array formula

Creating an array constant

Array constant elements

Understanding the Dimensions of an Array

One-dimensional horizontal arrays

One-dimensional vertical arrays

Two-dimensional arrays

Naming Array Constants

Working with Array Formulas

Entering an array formula

Selecting an array formula range

Editing an array formula

Expanding or contracting a multicell array formula

Using Multicell Array Formulas

Creating an array from values in a range

Creating an array constant from values in a range

Performing operations on an array

Using functions with an array

Transposing an array

Generating an array of consecutive integers

Using Single-Cell Array Formulas

Counting characters in a range

Summing the three smallest values in a range

Counting text cells in a range

Eliminating intermediate formulas

Using an array in lieu of a range reference

Chapter 15: Performing Magic with Array Formulas

Working with Single-Cell Array Formulas

Summing a range that contains errors

Counting the number of error values in a range

Summing the n largest values in a range

Computing an average that excludes zeros

Determining whether a particular value appears in a range

Counting the number of differences in two ranges

Returning the location of the maximum value in a range

Finding the row of a value's nth occurrence in a range

Returning the longest text in a range

Determining whether a range contains valid values

Summing the digits of an integer

Summing rounded values

Summing every nth value in a range

Removing nonnumeric characters from a string

Determining the closest value in a range

Returning the last value in a column

Returning the last value in a row

Working with Multicell Array Formulas

Returning only positive values from a range

Returning nonblank cells from a range

Reversing the order of cells in a range

Sorting a range of values dynamically

Returning a list of unique items in a range

Displaying a calendar in a range

Part V: Miscellaneous Formula Techniques

Chapter 16: Importing and Cleaning Data

A Few Words about Data

Importing Data

Importing from a file

Importing a text file into a specified range

Copying and pasting data

Data Clean-up Techniques

Removing duplicate rows

Identifying duplicate rows

Splitting text

Changing the case of text

Removing extra spaces

Removing strange characters

Converting values

Classifying values

Joining columns

Rearranging columns

Randomizing the rows

Matching text in a list

Change vertical data to horizontal data

Filling gaps in an imported report

Spelling checking

Replacing or removing text in cells

Adding text to cells

Fixing trailing minus signs

A Data Cleaning Checklist

Exporting Data

Exporting to a text file

Exporting to other file formats

Chapter 17: Charting Techniques

Understanding the SERIES Formula

Using names in a SERIES formula

Unlinking a chart series from its data range

Creating Links to Cells

Adding a chart title link

Adding axis title links

Adding text links

Adding a linked picture to a chart

Chart Examples

Single data point charts

Displaying conditional colors in a column chart

Creating a comparative histogram

Creating a Gantt chart

Creating a box plot

Plotting every nth data point

Identifying maximum and minimum values in a chart

Creating a Timeline

Plotting mathematical functions

Plotting a circle

Creating a clock chart

Creating awesome designs

Working with Trendlines

Linear trendlines

Working with nonlinear trendlines

Summary of trendline equations

Creating Interactive Charts

Selecting a series from a drop-down list

Plotting the last n data points

Choosing a start date and number of points

Displaying population data

Displaying weather data

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

Specifying the data

Specifying the location for the pivot table

Laying out the pivot table

Formatting the pivot table

Modifying the pivot table

More Pivot Table Examples

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Grouping Pivot Table Items

A manual grouping example

Viewing grouped data

Automatic grouping examples

Creating a Frequency Distribution

Creating a Calculated Field or Calculated Item

Creating a calculated field

Inserting a 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

A pivot chart example

More about pivot charts

Chapter 19: Conditional Formatting

About Conditional Formatting

Specifying Conditional Formatting

Formatting types you can apply

Making your own rules

Conditional Formats That Use Graphics

Using data bars

Using color scales

Using icon sets

Creating Formula-Based Rules

Understanding relative and absolute references

Conditional formatting formula examples

Working with Conditional Formats

Managing rules

Copying cells that contain conditional formatting

Deleting conditional formatting

Locating cells that contain conditional formatting

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

Accepting text only

Accepting a larger value than the previous cell

Accepting nonduplicate entries only

Accepting text that begins with a specific character

Accepting dates by the day of the week

Accepting only values that don't exceed a total

Creating a dependent list

Chapter 21: Creating Megaformulas

What Is a Megaformula?

Creating a Megaformula: A Simple Example

Megaformula Examples

Using a megaformula to remove middle names

Using a megaformula to return a string's last space character position

Using a megaformula to determine the validity of a credit card number

Generating random names

The Pros and Cons of Megaformulas

Chapter 22: Tools and Methods for Debugging Formulas

Formula Debugging?

Formula Problems and Solutions

Mismatched parentheses

Cells are filled with hash marks

Blank cells are not blank

Extra space characters

Formulas returning an error

Absolute/relative reference problems

Operator precedence problems

Formulas are not calculated

Actual versus displayed values

Floating-point number errors

Phantom link errors

Logical value errors

Circular reference errors

Excel's Auditing Tools

Identifying cells of a particular type

Viewing formulas

Tracing cell relationships

Tracing error values

Fixing circular reference errors

Using background error checking

Using Excel's Formula Evaluator

Part VI: Developing Custom Worksheet Functions

Chapter 23: Introducing VBA

About VBA

Displaying the Developer Tab

About Macro Security

Saving Workbooks That Contain Macros

Introducing the Visual Basic Editor

Activating the VB Editor

The VB Editor components

Using the Project window

Using code windows

Entering VBA code

Saving your project

Chapter 24: Function Procedure Basics

Why Create Custom Functions?

An Introductory VBA Function Example

About Function Procedures

Declaring a function

Choosing a name for your function

Using functions in formulas

Using function arguments

Using the Insert Function Dialog Box

Adding a function description

Specifying a function category

Adding argument descriptions

Testing and Debugging Your Functions

Using the VBA MsgBox statement

Using Debug.Print statements in your code

Calling the function from a Sub procedure

Setting a breakpoint in the function

Creating Add-Ins for Functions

Chapter 25: VBA Programming Concepts

An Introductory Example Function Procedure

Using Comments in Your Code

Using Variables, Data Types, and Constants

Defining data types

Declaring variables

Using constants

Using strings

Using dates

Using Assignment Statements

Using Arrays

Declaring an array

Declaring multidimensional arrays

Using Built-In VBA Functions

Controlling Execution

The If-Then construct

The Select Case construct

Looping blocks of instructions

The On Error statement

Using Ranges

The For Each-Next construct

Referencing a range

Some useful properties of ranges

The Set keyword

The Intersect function

The Union function

The UsedRange property

Chapter 26: VBA Custom Function Examples

Simple Functions

Does a cell contain a formula?

Returning a cell's formula

Is the cell hidden?

Returning a worksheet name

Returning a workbook name

Returning the application's name

Returning Excel's version number

Returning cell formatting information

Determining a Cell's Data Type

A Multifunctional Function

Generating Random Numbers

Generating random numbers that don't change

Selecting a cell at random

Calculating Sales Commissions

A function for a simple commission structure

A function for a more complex commission structure

Text Manipulation Functions

Reversing a string

Scrambling text

Returning an acronym

Does the text match a pattern?

Does a cell contain a particular word?

Does a cell contain text?

Extracting the nth element from a string

Spelling out a number

Counting Functions

Counting pattern-matched cells

Counting sheets in a workbook

Counting words in a range

Date Functions

Calculating the next monday

Calculating the next day of the week

Which week of the month?

Working with dates before 1900

Returning the Last Nonempty Cell in a Column or Row

The LASTINCOLUMN function

The LASTINROW function

Multisheet Functions

Returning the maximum value across all worksheets

The SHEETOFFSET function

Advanced Function Techniques

Returning an error value

Returning an array from a function

Returning an array of nonduplicated random integers

Randomizing a range

Using optional arguments

Using an indefinite number of arguments

Part VII: Appendixes

Appendix 1: Excel Function Reference

Appendix 2: Using Custom Number Formats

Automatic number formatting

Formatting numbers by using the Ribbon

Using shortcut keys to format numbers

Using the format cells dialog box to format numbers

Parts of a number format string

Custom number format codes

Scaling values

Hiding zeros

Displaying leading zeros

Displaying fractions

Displaying N/A for text

Displaying text in quotes

Repeating a cell entry

Displaying a negative sign on the right

Conditional number formatting

Coloring values

Formatting dates and times

Displaying text with numbers

Displaying a zero with dashes

Using special symbols

Suppressing certain types of entries

Filling a cell with a repeating character

Displaying leading dots

End User License Agreement

About the Author

John Walkenbach is a bestselling Excel author who has published more than 50 books on spreadsheets. He lives amid the saguaros, javelinas, rattlesnakes, bobcats, and gila monsters in southern Arizona — but the critters are mostly scared away by his clawhammer banjo playing. For more information, Google him.

Publisher's Acknowledgments

We're proud of this book; please send us your comments at http://dummies.custhelp.com. For other comments, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002.

Some of the people who helped bring this book to market include the following:

Acquisitions, Editorial, and Vertical Websites

Sr. Project Editor: Christopher Morris

Acquisitions Editor: Katie Mohr

Sr. Copy Editor: Teresa Artman

Technical Editor: Niek Otten

Editorial Manager: Kevin Kirschner

Vertical Websites Project Manager: Laura Moss-Hollister

Editorial Assistant: Annie Sullivan

Sr. Editorial Assistant: Cherie Case

Composition Services

Project Coordinator: Kristie Rees

Layout and Graphics: Jennifer Henry

Proofreader: ConText Editorial Services, Inc.

Indexer: BIM Indexing & Proofreading Services

Publishing and Editorial for Technology Dummies

Richard Swadley, Vice President and Executive Group Publisher

Andy Cummings, Vice President and Publisher

Mary Bednarek, Executive Acquisitions Director

Mary C. Corder, Editorial Director

Publishing for Consumer Dummies

Kathleen Nebenhaus, Vice President and Executive Publisher

Composition Services

Debbie Stailey, Director of Composition Services

Introduction

Welcome to Excel 2013 Formulas. I approached this project with one goal in mind: to write the ultimate book about Excel formulas that would appeal to a broad base of users. That's a fairly ambitious goal, but based on the feedback I received from the earlier editions, I think I accomplished it.

Excel is the spreadsheet market leader by a long shot not only because of Microsoft's enormous marketing clout but also because it is truly the best spreadsheet available. One area in which Excel's superiority is most apparent is formulas. Excel has some special tricks up its sleeve in the formulas department. As you'll see, Excel lets you do things with formulas that are impossible with other spreadsheets.

It's a safe bet that only about 10 percent of Excel users really understand how to get the most out of worksheet formulas. In this book, I attempt to nudge you into that elite group. Are you up to it?

What You Need to Know

This is not a book for beginning Excel users. If you have absolutely no experience with Excel, this is probably not the best book for you unless you're one of a rare breed who can learn a new software product almost instantaneously.

To get the most out of this book, you should have some background using Excel. Specifically, I assume that you know how to

• Create workbooks, insert sheets, save files, and complete other basic tasks.

• Navigate a workbook.

• Use the Excel Ribbon and dialog boxes.

• Use basic Windows features, such as file management and copy-and-paste techniques.

What You Need to Have

I wrote this book with Excel 2013 as a base, but most of the material also applies to Excel 2010 and Excel 2007. If you're using a version prior to Excel 2007, I suggest that you put down this book immediately and pick up a previous edition. The changes introduced in Excel 2007 are so extensive that you might be hopelessly confused if you try to follow along using an earlier version of Excel.

To download the examples for this book, you need to access the Internet. The examples are discussed further in the “About This Book's Website” section, later in this Introduction.

I use Excel for Windows exclusively, and I do not own a Mac. Therefore, I can't guarantee that all examples will work with Excel for Mac. Excel's cross-platform compatibility is pretty good, but it's definitely not perfect.

As far as hardware goes, the faster the better. And, of course, the more memory in your system, the happier you'll be. And, I strongly recommend using a high-resolution video mode. Better yet, try a dual-monitor system.

Conventions in This Book

Take a minute to skim this section and learn some of the typographic conventions used throughout this book.

Keyboard conventions

You use the keyboard to enter formulas. In addition, you can work with menus and dialog boxes directly from the keyboard — a method you may find easier if your hands are already positioned over the keys.

Formula listings

Formulas usually appear on a separate line in monospace font. For example, I may list the following formula:

=VLOOKUP(StockNumber,PriceList,2,False)

Excel supports a special type of formula known as an array formula. When you enter an array formula, press Ctrl+Shift+Enter (not just Enter). Excel encloses an array formula in brackets in order to remind you that it's an array formula. When I list an array formula, I include the brackets to make it clear that it is, in fact, an array formula. For example:

{=SUM(LEN(A1:A10))}

Do not type the brackets for an array formula. Excel will put them in automatically.

VBA code listings

This book also contains examples of VBA code. Each listing appears in a monospace font; each line of code occupies a separate line. To make the code easier to read, I usually use one or more tabs to create indentations. Indentation is optional, but it does help to delineate statements that go together.

If a line of code doesn't fit on a single line in this book, I use the standard VBA line continuation sequence: a space followed by an underscore character. This indicates that the line of code extends to the next line. For example, the following two lines comprise a single VBA statement:

If Right(cell.Value, 1) = “!” Then cell.Value _

   = Left(cell.Value, Len(cell.Value) - 1)

You can enter this code either exactly as shown on two lines or on a single line without the trailing underscore character.

Key names

Names of keys on the keyboard appear in normal type: for example, Alt, Home, PgDn, and Ctrl. When you should press two keys simultaneously, the keys are connected with a plus sign: “Press Ctrl+G to display the Go To dialog box.”

Functions, procedures, and named ranges

Excel's worksheet functions appear in all uppercase, like so: “Use the SUM function to add the values in column A.”

Macro and procedure names appear in normal type: “Execute the InsertTotals procedure.” I often use mixed upper- and lowercase to make these names easier to read. Named ranges appear in italic: “Select the InputArea range.”

Unless you're dealing with text inside of quotation marks, Excel is not sensitive to case. In other words, both of the following formulas produce the same result:

=SUM(A1:A50)

=sum(a1:a50)

Excel, however, will convert the characters in the second formula to uppercase.

Mouse conventions

The mouse terminology in this book is all standard fare: pointing, clicking, right-clicking, dragging, and so on. You know the drill.

What the icons mean

Throughout the book, icons appear to call your attention to points that are particularly important.

This icon indicates a feature new to Excel 2013.

I use Note icons to tell you that something is important — perhaps a concept that may help you master the task at hand or something fundamental for understanding subsequent material.

Tip icons indicate a more efficient way of doing something or a technique that may not be obvious. These will often impress your officemates.

These icons indicate that an example file is on this book's website. (See the upcoming “About This Book's Website” section.)

I use Caution icons when the operation that I'm describing can cause problems if you're not careful.

I use the Cross Reference icon to refer you to other chapters that have more to say on a particular topic.

How This Book Is Organized

There are dozens of ways to organize this material, but I settled on a scheme that divides the book into six main parts. In addition, I included a few appendixes that provide supplemental information that you may find helpful.

Part I: Basic Information

This part is introductory in nature; it consists of Chapters 1–3. Chapter 1 sets the stage with a quick and dirty overview of Excel. This chapter is designed for readers who are new to Excel but have used other spreadsheet products. In Chapter 2, I cover the basics of formulas. This chapter is absolutely essential reading to get the most out of this book. Chapter 3 deals with names. If you thought names were just for cells and ranges, you'll see that you're missing out on quite a bit.

Part II: Using Functions in Your Formulas

This part consists of Chapters 4–10. Chapter 4 covers the basics of using worksheet functions in your formulas. I get more specific in subsequent chapters. Chapter 5 deals with manipulating text, Chapter 6 covers dates and times, and Chapter 7 explores various counting techniques. In Chapter 8, I discuss various types of lookup formulas. Chapter 9 deals with tables and worksheet databases; and Chapter 10 covers a variety of miscellaneous calculations, such as unit conversions and rounding.

Part III: Financial Formulas

Part III consists of three chapters (Chapters 11–13) that deal with creating financial formulas. You'll find lots of useful formulas that you can adapt to your needs.

Part IV: Array Formulas

This part consists of Chapters 14 and 15. The majority of Excel users know little or nothing about array formulas — a topic that happens to be dear to me. Therefore, I devote an entire part to this little-used yet extremely powerful feature.

Part V: Miscellaneous Formula Techniques

This part consists of Chapters 16–22. They cover a variety of topics — some of which, on the surface, may appear to have nothing to do with formulas. Chapter 16 provides lots of useful information about cleaning up data. In Chapter 17, you'll see why formulas can be important when you work with charts, and Chapter 18 covers formulas as they relate to pivot tables. Chapter 19 contains some very interesting (and useful) formulas that you can use in conjunction with Excel's conditional formatting feature. Chapter 20 covers the data validation feature. Chapter 21 covers a topic that I call “megaformulas,” which are huge formulas that takes the place of several intermediary formulas. And what do you do when your formulas don't work correctly? Consult Chapter 22 for some debugging techniques.

Part VI: Developing Custom Worksheet Functions

This part consists of Chapters 23–26. This is the part that explores Visual Basic for Applications (VBA), the key to creating custom worksheet functions. Chapter 23 introduces VBA and the VB Editor, and Chapter 24 provides some necessary background on custom worksheet functions. Chapter 25 covers programming concepts, and Chapter 26 provides a slew of custom worksheet function examples that you can use as-is or customize for your own needs.

Part VII: Appendixes

What's a computer book without appendixes? This book has two appendixes: Appendix A is a quick reference guide to Excel worksheet functions, and Appendix B contains tips on using custom number formats.

How to Use This Book

You can use this book any way you please. If you choose to read it cover to cover while lounging on a sunny beach in Kauai, that's fine with me. More likely, you'll want to keep it within arm's reach while you toil away in your dimly lit cubicle.

Due to the nature of the subject matter, the chapter order is often immaterial. Most readers will probably skip around, picking up useful tidbits here and there. The material contains many examples, designed to help you identify a relevant formula quickly. If you're faced with a challenging task, you may want to check the index first to see whether the book specifically addresses your problem.

About This Book's Website

This book contains many examples, and the workbooks for those examples are available at this URL:

www.wiley.com/go/excel2013formulas

Files that have an *.xlsm extension contain VBA macros. To use the macros, you must enable the macros when you open the file (or put the files in a trusted location).

About the Power Utility Pak Offer

Toward the back of the book, you'll find a coupon that you can redeem for a discounted copy of my award-winning Power Utility Pak, which comprises a collection of useful Excel utilities, plus many new worksheet functions. I developed this package using VBA exclusively.

You can also use this coupon to purchase the complete VBA source code for a nominal fee. Studying the code is an excellent way to pick up some useful programming techniques.

You can download a 30-day trial version of the most recent version of the Power Utility Pak from my website:

http://spreadsheetpage.com

If you find it useful, use the coupon to purchase a licensed copy at a discount.

WILEY END USER LICENSE AGREEMENT

Go to www.wiley.com/go/eula to access Wiley’s ebook EULA.

Part I: Basic Information

Chapter 1

Excel in a Nutshell

Chapter 2

Basic Facts about Formulas

Chapter 3

Working with Names