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:
If you find it useful, use the coupon to purchase a licensed copy at a discount.