Introduction
The old 80/20 rule for software—that 80% of a program’s users use only 20% of a program’s features—doesn’t apply to Microsoft Excel. Instead, this program probably operates under what could be called the 95/5 rule: Ninety-five percent of Excel users use a mere 5% of the program’s power. On the other hand, most people know that they could be getting more out of Excel if they could only get a leg up on building formulas and using functions. Unfortunately, this side of Excel appears complex and intimidating to the uninitiated, shrouded as it is in the mysteries of mathematics, finance, and impenetrable spreadsheet jargon.
If this sounds like the situation you find yourself in, and if you’re a businessperson who needs to use Excel as an everyday part of your job, you’ve come to the right book. In Excel 2019 Formulas and Functions, I demystify the building of worksheet formulas and present the most useful of Excel’s many functions in an accessible, jargon-free way. This book not only takes you through Excel’s intermediate and advanced formula-building features but also tells you why these features are useful to you and shows you how to use them in everyday situations and real-world models. This book does all this with no-nonsense, step-by-step tutorials and lots of practical, useful examples aimed directly at business users.
Even if you’ve never been able to get Excel to do much beyond storing data and adding a couple of numbers, you’ll find this book to your liking. I show you how to build useful, powerful formulas from the ground up, so no experience with Excel formulas and functions is necessary.
What’s in the book
This book isn’t meant to be read from cover to cover, although you’re certainly free to do just that if the mood strikes you. Instead, most of the chapters are set up as self-contained units that you can dip into at will to extract whatever nuggets of information you need. However, if you’re a relatively new to Excel formulas and functions, I suggest starting with Chapter 1, “Building basic formulas,” and Chapter 4, “Understanding functions,” to ensure that you have a thorough grounding in the fundamentals.
The book is divided into four main parts. To give you the big picture before diving in, here’s a summary of what you’ll find in each part:
Part I, “Mastering Excel formulas”—The three chapters in Part I tell you just about everything you need to know about building formulas in Excel. This part discusses operators, expressions, advanced formula features, and formula-troubleshooting techniques.
Part II, “Harnessing the power of functions”—Functions take your formulas to the next level, and you learn all about them in Part II. After you see how to use functions in your formulas, you examine seven main function categories—text, logical, information, lookup, date, time, and math. In each case, I tell you how to use the functions and give you lots of practical examples that show you how you can use the functions in everyday business situations.
Part III, “Building business formulas”—This part is crammed with business goodies related to performing financial wizardry with Excel. You learn how to implement many standard business formulas in Excel, and you get in-depth looks at Excel’s descriptive and inferential statistical tools, powerful regression-analysis techniques to track trends and make forecasts, and techniques and functions for amortizing loans, analyzing investments, and using discounting for business case and cash-flow analysis.
Part IV, “Building business models”—The four chapters in Part IV are all business, as they examine various facets of building useful and robust business models. You learn how to analyze data with Excel tables and PivotTables, how to use what-if analysis and Excel’s Goal Seek and scenarios features, and how to use the amazing Solver feature to solve complex problems.
This book’s special features
Excel 2019 Formulas and Functions is designed to give you the information you need without making you wade through ponderous explanations and interminable technical background. To make your life easier, this book includes various features and conventions that help you get the most out of the book and Excel itself:
Steps: Throughout the book, each Excel task is summarized in step-by-step procedures.
Things you type: Whenever I suggest that you type something, what you type appears in a bold font.
Commands: I use the following style for Excel menu commands: File > Open. This means that you pull down the File menu and select the Open command.
Dialog box controls: The names of dialog box controls and other onscreen elements appear in bold text: Select the OK button.
Functions: Excel worksheet functions appear in capital letters and are followed by parentheses: SUM(). When I list the arguments you can use with a function, they appear in italic to indicate that they’re placeholders you replace with actual values; also, optional arguments appear surrounded by square brackets:
CELL(info_type [, reference]).
This book also uses the following boxes to draw your attention to important (or merely interesting) information.
Note
The Note box presents asides that offer more information about the topic under discussion. These tidbits provide extra insights that give you a better understanding of the task at hand.
Tip
The Tip box tells you about Excel methods that are easier, faster, or more efficient than the standard methods.
Caution
The all-important Caution box tells you about potential accidents waiting to happen. There are always ways to mess things up when you’re working with computers. These boxes help you avoid at least some of the pitfalls.
About the companion content
To make it easier for you to learn Excel formulas and functions, all the sample content used in the book is available online. To download the sample workbooks, look for the download link on the book’s page:
MicrosoftPressStore.com/Excel2019FormulasFunctions/downloads
Support and feedback
The following sections provide information on errata, book support, feedback, and contact information.
Stay in touch
Let’s keep the conversation going! We’re on Twitter:
http://twitter.com/MicrosoftPress
Errata, updates, and book support
We’ve made every effort to ensure the accuracy of this book and its companion content. Any errors that have been reported since this book was published are listed at MicrosoftPressStore.com/Excel2019FormulasFunctions/errata.
If you find an error that is not already listed, you can report it to us through the same page.
If you need additional support, email Microsoft Press Book Support at [email protected].
Please note that product support for Microsoft software and hardware is not offered through the previous addresses. For help with Microsoft software or hardware, go to http://support.microsoft.com.