Michael Alexander - 101 ready-to-use excel formulas

Formulas are the true engines of Excel. Employing various Excel functions, formulas enable Excel analysts to create aggregated reporting, complex calculation engines, clever dashboard models, and much more. Indeed, Excel analysts become more productive as their proficiency with Excel functions and formulas improves.

But building proficiency with Excel functions and formulas takes time. Given that Excel contains more than 400 functions, you could spend months, even years, learning which functions are best for certain tasks and which functions can be combined with others functions.

Unfortunately, many analysts dont have the luxury of taking a few weeks time-out to learn all they need to know about Excel functions and formulas. The scenarios and issues they face require solutions now.

This is where 101 Ready-to-Use Excel Formulas comes in. This book approaches Excel formulas with the assumption that learning comes with accomplishing core tasks. Instead of offering the usual general overview of Excel formula writing, this book provides 101 of the most commonly used, real-world Excel formulas.

For each formula covered, we outline a common problem that needs to be solved and provide the actual Excel formula to solve the problem, along with detailed explanations of how the formula works. This approach lets you use this book as a handy reference for finding a formula that solves a common problem.

After reading about a given formula, you should be able to

• Immediately implement the needed Excel formula
• Understand how the formula works
• Reuse the formula in other workbooks

To get the most out of this book, you need to have established certain skills before diving in. The ideal candidate for this book has experience working with data in Excel along with familiarity with the basic concepts of data analysis such as working with tables, aggregating data, performing calculations, and creating charts.

You need the following to be able to download and use the examples highlighted in this book:

• A licensed copy of Excel 2010 or Excel 2013
• An Internet connection in order to download the sample files

Weve grouped this book into nine chapters that are chock-full of tips, techniques, and formulas dedicated to a particular topic.

Chapter serves as an introduction to Excel formulas, giving you an understanding of how Excel formulas work and some of the ground rules for working with formulas.

In Chapter , you gain insight into some of the fundamental mathematical operations every Excel analyst should know. The formulas found here serve as the foundation for all kinds of advanced data analysis.

Chapter focuses on the transformation and shaping of text strings. In this chapter, you explore some of the common text transformation exercises an Excel analyst performs, and in the process, you get a feel for many of the text-based functions Excel has to offer.

Chapter gives you a solid understanding of how Excel handles time-based data. Through the prism of the most commonly used date formulas, you discover how to more effectively utilize the dates and times within your data sets.

In Chapter , you take a look at a wide array of conditional analysis formulas that add flexibility to your analytical processes. With the formulas found here, youll be able to save time, organize your analytical processes, and enhance your data-crunching power.

Chapter focuses on Excels powerful, sometimes intimidating, Lookup formula. The formulas demonstrated in this chapter provide the foundation you need to add depth and power to your Excel data models.