Dashboard Spy readers know how much I am convinced that Microsoft Excel 2007 will be leading a new wave of dashboards across the corporate world. The new version of MS Excel is built from the ground up with tools for dashboarding. Remember this UI designer sketch I featured in a past post (How Microsoft Designed Excel 2007) that a Microsoftie Dashboard Spy sent me? Here’s a “before and after” set of images:
Already we’ve seen some great Excel 2007 dashboards from business-side power users. Keep them coming and I’ll show the rest of the Dashboard Spy readers.
Great news on the “books about Excel 2007 for Dashboards” front. Take a look at this book cover. Yep, a Dummies book on Excel 2007 Dashboarding! It’s officially called Microsoft Office Excel 2007 Dashboards & Reports. Coming soon to a book store near you, but you can pre-order now at a great price.
(click link for Amazon.com page)
Excel 2007 Dashboards & Reports For Dummies (Low Amazon Price of $16.49 plus Additional 5% Discount for Pre-Orders)
The best thing about this book is that it’s written by Dashboard Spy reader and data analysis guru Mike Alexander. He has authored 7 great books on advanced business analysis with Microsoft Excel and Access. Over the years, his company, DataPig Technologies has contributed greatly to the Excel community.
Check out the listing of Excel video tutorials and be sure to keep tabs on the new DataPig blog.
Mike has been kind enough to provide us with a sneek peek at his Excel 2007 Dashboarding book table of contents.
Excel 2007 Dashboards & Reports For Dummies
By Mike Alexander
The goal of this book is to show you how to leverage Excel functionality to build and manage better reporting mechanisms. Each chapter in this book provides a comprehensive review of the technical and analytical concepts that help you create better reporting components – components that can be used for both dashboards and reports. It’s important to note that this book isn’t a guide to visualizations or dashboarding best practices. While those are subjects worthy of their own book, this book focuses on understanding the technical aspects of using Excel’s various tools and functionality and applying them to reporting.
Part I: Making the Move to Dashboards
Chapter 1: Getting in the Dashboard State of Mind
In Excel, the differences between building a dashboard and creating standard table-driven analyses is as great as the differences between California and New York. To approach a dashboarding project, you truly have to get into the dashboard state of mind. Dashboarding requires far more preparation than standard Excel analyses. It calls for closer communication with business leaders, stricter data modeling techniques, and the following of certain best practices. It’s beneficial to have a base familiarity with fundamental dashboarding concepts before venturing off into the mechanics of building a dashboard. In chapter 1, you get a solid understanding of these basic dashboard concepts and design principles as well as what it takes to prepare for a dashboarding project.
Chapter 2: Building a Super Model
Although Excel is like the cool gym teacher that lets you do anything you want, a lack of structure in your data models can lead to some serious headaches in the long run. Creating a poorly-designed data model can mean hours of manual labor maintaining and refreshing your reporting mechanisms. On the other hand, creating an effective model allows you to easily repeat monthly reporting processes without damaging your reports or your sanity. The goal of this chapter 2 is to show you the concepts and techniques that help you build effective data models. In this chapter, you discover that creating a successful reporting mechanism requires more than slapping data onto a spreadsheet. Although you’ll discover how to build cool dashboard components in later chapters, they won’t do you any good if you can’t effectively manage your data models. On that note, let’s get started.
Part II: Building Basic Dashboard Components
Chapter 3: The Pivotal Pivot Table
With pivot tables, you can build reporting models that can not only be easy to set up, but can be refreshed with a simple press of a button. This allows you to spend less time maintaining your dashboards and reports and more time doing other useful things. No utility is in the whole of Excel that allows you to achieve this efficient data model better than a pivot table. In Chapter 3, you’ll not only get a concise introduction to pivot tables, but you’ll find some time-saving techniques to help create some useful pivot-driven views for your dashboards and reports.
Chapter 4: Excel Charts for the Uninitiated
Few mechanisms allow you to absorb data faster than a chart. Charts offer instant gratification, allowing users to immediately see relationships, point out differences, and observe trends. For those of you who have been uninitiated to the world of Excel 2007 charting, chapter 4 provides the basics of creating and customizing charts in Excel.
Chapter 5: The New World of Conditional Formatting
Microsoft has dramatically enhanced this functionality in Excel 2007. In Excel 2007, conditional formatting includes a more robust set of visualizations and predefined formatting rules. These enhancements allow you to quickly and easily build dashboard-style reporting that goes far beyond the traditional red, yellow, and green designations. In chapter 5, you’re introduced to the new world of conditional formatting in Excel 2007, discovering how to leverage this functionality to enhance your dashboards and reports.
Chapter 6: The Art of Dynamic Labeling
Dynamic labeling is less a function in Excel than it is a concept. Dynamic labels are labels that change to correspond to the data you’re viewing. With dynamic labeling, you can interactively change the labeling of data, consolidate many pieces of information into one location, and easily add layers of analysis. In chapter 6, you explore the various techniques that can be used to create dynamic labels.
Part III: Building Advanced Dashboard Components
Chapter 7: Components that Show Trending
One of the most common concepts used in dashboards and reports is the concept of trending, or measuring of variance over some defined interval. Trending provides a rational expectation of what might happen in the future. In chapter 7, you explore basic trending concepts and some of the advanced techniques you can use to take your trending components beyond simple line charts.
Chapter 8: Components that Group and Bucket Data
The benefit of grouping data is that it allows you to more easily pick out groups that fall outside the norm for your business. In this chapter, I explore some of the techniques you can use to create components that group and bucket data.
Chapter 9: Components that Display Performance against a Target
The business world is full of targets and goals. Your job is to find effective ways to represent performance against those targets. In this chapter 9, I explore some new and interesting ways to create components that show performance against a target.
Part IV: Advanced Reporting Techniques
Chapter 10: Macro Charged Reporting
In chapter 10, I explain why you should use macros and how macros can help automate your recurring reporting processes to simplify your life.
Chapter 11: Giving Users an Interactive Interface
Today, managers increasingly want to be empowered to switch from one view of data to another with a simple selection from a menu of choices. Fortunately, Excel offers tools that enable you to add interactivity into your reports. With these tools and a bit of creative data modeling, you can give your managers the choices they crave with relative ease. In chapter 11, I show you how to incorporate menus, options, and selectors into your reporting mechanisms and offer a few useful examples you can implement into your processes.
Part V: Working with the Outside World
Chapter 12: Using External Data in your Dashboards and Reports
When dealing with small datasets that are developed and maintained in Excel, you have to make a conscious effort to make that separation. However, in complex models where large volumes of data come from Access or SQL, the effort on your part is eliminated. The worry in these situations, however, is how to efficiently move that data from over there to over here. Chapter 12 explores the most efficient ways to get external data into Excel.
Chapter 13: Sharing Your Work with the Outside World
The focus of chapter 13 is on preparing your dashboards for life outside your PC. In this chapter, you explore the various methods of protecting your work from accidental and purposeful meddling and discover how you can distribute your dashboards via PowerPoint and PDF.
Part VI: Parts of Ten
Chapter 14: Ten Chart Design Principles
Excel makes charting so simple, it’s often tempting to accept the charts it creates no matter how bad the default colors or settings are. But I’m here to implore you to turn away from the glitzy lure of the default settings. You can easily avoid charting fiascos by following a few basic design principles. In chapter 14, I share with you a few of these principles and help you avoid some of the more common charting design mistakes.
Ten Questions to Ask Before Distributing Your Dashboard
You started this book with two chapters that discuss a few design and data modeling principles that, together, make up what could be considered dashboarding’s best practices. Before you send out your finished product, it’s valuable to check your reporting mechanism against some of the principles covered in this book. You can use the ten questions in this chapter as a kind of checklist to ensure your dashboard follows the best practices covered in this book.
This book includes a full color gallery of some of the components you will find within the black and white pages of this book, plus a few sample dashboards you may be able to use as inspiration for your next reporting project.
Tags: Excel 2007 Dashboard Book, Microsoft Office Excel 2007 for Dashboards & Reports