Introduction

This book will challenge you to think differently about Excel. It will challenge you to think outside the cell.

Most of us are not used to thinking differently about Excel. We’re used to viewing Excel as a one-dimensional platform with a fixed set of capabilities. This myopic view has caused us to build dashboards and models, a not-so-small-number of which are perceived as being slow and clunky. Some have even accepted “slow and clunky” as the compromise to building dashboards in Excel. But it doesn’t have to be this way. You can create intuitive, interactive data and analytics applications in Excel without sacrificing speed and utility, if you know how.

This book will teach you how. But it won’t happen through a series of clicks or rote memorization. Indeed, memorizing everything in this book will likely not help you. To master the techniques in this book, you must learn to think creatively and differently. You must be willing and perhaps even courageous to challenge Excel’s perceived limitations. You must be able to extend focused examples into your own work, adapting them to new scenarios and different problems. Luckily, as an analyst and professional, you likely already have the creative gene to get the job done. This book will show you how to apply it to Excel dashboards and development.

What to Expect

Here’s what you should know. This is an intermediate to advanced-level book. We assume you are not a novice to Excel formulas and have some degree of experience with Visual Basic for Applications programming, even if it’s just the macro recorder. If you are open to learning new things, this book will teach you. Remember, the most important skill is being able to think about problems differently. Chances are you already do.

This book will not help you pick the correct metrics for your dashboards, but it will help you present them in the best way possible. This is not a book dedicated to pivot tables, VBA, or formulas but rather to how you can use them appropriately, and sometimes counterintuitively, to create engaging and meaningful analytical tools.

Much of this book will focus on reverse engineering complete designs. There are many reasons we chose to present examples in this way. For one, we want to establish the use of reusable components. We’ll go into reusable components later in the book, but for now, you can understand them as collections of formulas, charts, form controls, and other Excel features that allow you to repeat and implement similar design patterns across different scenarios and applications. By reverse engineering completed work, you’ll see that most applications in Excel share very similar constructions that are easy to build and ready to be reused in different products.

More important, however, is that real life rarely allows you to start from scratch. More often than not, you look to the work of others for both guidance and inspiration. Perhaps you are taking over a dashboard designed by your predecessor. Or, maybe you’re adapting the solution you found on an Excel forum after an Internet search. Whatever the reasons, being a good developer requires that you know how to read, understand, apply, and adapt the work of others. How to take a small concept and apply its lessons across a range of other scenarios is a skill worth developing in its own right, and to the extent possible, this book will challenge you to do just that. It’s not enough to be good at Excel development; you must learn to think like a developer. This is how Excel can transcend its unfounded reputation.

How This Book Is Laid Out

This book is split into five parts.

• Part I, “Dashboards and Data Visualization”
• Part II, “Excel Dashboard Design Tools and Concepts”
• Part III, “Formulas, Controls, and Charts”
• Part IV, “From User Interface to Presentation”
• Part V, “Data Models, PowerPivot, and Power Query”

Part I introduces what it takes to make an awesome Excel application: good development practices, good data visualization principles, and thinking outside the cell. “Thinking outside the cell” refers to when and how you can use Excel differently. We also review dashboards, explaining how some are successful and why many are not. Finally, we cover data visualization principles so you can understand the best ways to present your data on your dashboards and decision support systems.

Part II explains what’s required for to build and design a dashboard in Excel. Specifically, we’ll focus on what to do and what not to do, while applying the data visualization principles outlined in Part I. Moreover, we’ll walk you through how this book uses VBA and formulas differently than you might be used to. Part II is filled with new ways to use Excel features you might already be familiar with, such as form controls and shapes.

Part III will investigate a Gantt chart dashboard used for workforce planning. This part will apply much of what was presented in Part II to implement a fully functional dashboard system with visual and interactive elements.

Part IV will investigate how to build a decision support system used to investigate different healthcare systems across different countries. The chapters in this part will go through taking in user input, storing the input in a back-end database, and displaying the final results to the user. This part also introduces some easily implemented analytics techniques from the field of management science.

Part V was written by Purnachandra, a.k.a. Chandoo, and he shows how many of Excel’s new features can help build dashboards similar to those presented in the first four parts. These features include data models, slicers, and PowerPivot and Power Query. These new features make up the new Microsoft Power Business Intelligence platform.

Above all, this book will focus on thinking creatively. It will focus on showing you how to hone your skills as an analyst to use Excel to create work that will astound your boss and colleagues. Most of all, this book is about helping you take your Excel skills to the next level. This book is a journey, and you’re just at the beginning. The road ahead is long, and the challenges are formidable but well within your grasp if you keep at it.

Good luck on your journey. I know it will be a good one.

—Jordan Goldmeier