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
Excel® 2013 Formulas
Published by
John Wiley & Sons, Inc.
111 River Street
Hoboken, NJ 07030-5774
www.wiley.com
Copyright © 2013 by John Wiley & Sons, Inc., Hoboken, New Jersey
Published by John Wiley & Sons, Inc., Hoboken, New Jersey
Published simultaneously in Canada
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.
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 in the United States and/or other countries. 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.
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 Website 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. FULFILLMENT OF EACH COUPON OFFER IS THE SOLE RESPONSIBILITY OF THE OFFEROR.
For general information on our other products and services, 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.
For technical support, please visit www.wiley.com/techsupport.
Wiley also publishes its books in a variety of electronic formats. 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: 2013932115
ISBN: 978-1-118-49044-0 (pbk); ISBN 978-1-118-49045-7 (ebk); ISBN 978-1-118-49179-9 (ebk); ISBN 978-1-118-49189-8 (ebk)
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
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.
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