Description
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 2016 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 Excel user, I suggest starting with Chapters 1, “Getting the Most Out of Ranges,” 2, “Using Range Names,” 3, “Building Basic Formulas,” and 6, “Understanding Functions,” to ensure that you have a thorough grounding in the fundamentals of Excel ranges, formulas, and functions.
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 Ranges and Formulas”—The five chapters in Part I tell you just about everything you need to know about building formulas in Excel. Starting with a thorough look at ranges (crucial for mastering formulas), this part also 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’ll learn all about them in Part II. After you see how to use functions in your formulas, you’ll examine the eight main function categories text, logical, information, lookup, date, time, math, and statistical. 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 Models” The five chapters in Part III are all business, as they examine various facets of building useful and robust business models. You’ll learn how to analyze data with Excel tables and PivotTables, how to use what if analysis and Excel’s Goal Seek and scenarios features, how to use powerful regression-analysis techniques to track trends and make forecasts, and how to use the amazing Solver feature to solve complex problems.
Part IV, “Building Financial Formulas” The book finishes with more business goodies related to performing financial wizardry with Excel. You’ll learn techniques and functions for amortizing loans, analyzing investments, and using discounting for business case and cash-flow analysis.