Dashboards for Excel Deliver Critical Information and Insight at the Speed of a Click — Jordan Goldmeier Purnachandra Duggirala Dashboards for Excel Jordan Goldmeier Purnachandra Duggirala Dashboards for Excel Copyright © 2015 by Jordan Goldmeier and Purnachandra Duggirala This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Exempted from this legal reservation are brief excerpts in connection with reviews or scholarly analysis or material supplied specifically for the purpose of being entered and executed on a computer system, for exclusive use by the purchaser of the work. Duplication of this publication or parts thereof is permitted only under the provisions of the Copyright Law of the Publisher’s location, in its current version, and permission for use must always be obtained from Springer.
Permissions for use may be obtained through RightsLink at the Copyright Clearance Center. Violations are liable to prosecution under the respective Copyright Law. ISBN-13 (pbk): 978-1-4302-4944-3 ISBN-13 (electronic): 978-1-4302-4945-0 Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director: Welmoed Spahr Lead Editor: James DeWolf Development Editor: Chris Nelson Technical Reviewer: Fabio Claudio Ferracchiati Editorial Board: Steve Anglin, Mark Beckner, Gary Cornell, Louise Corrigan, Jim DeWolf, Jonathan Gennick, Jonathan Hassell, Robert Hutchinson, Michelle Lowman, James Markham, Susan McDermott, Matthew Moodie, Jeffrey Pepper, Douglas Pundick, Ben Renow-Clarke, Gwenan Spearing, Matt Wade, Steve Weiss Coordinating Editor: Melissa Maldonado Copy Editor: Kim Wimpsett Compositor: SPi Global Indexer: SPi Global Artist: SPi Global Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013.
Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders-ny@springer-sbm.com, or visit www. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation. For information on translations, please e-mail rights@apress.com, or visit www.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales–eBook Licensing web page at www.com/bulk-sales. Any source code or other supplementary materials referenced by the author in this text is available to readers at www.
For detailed information about how to locate your book’s source code, go to www.com/source-code/. —Jordan To Jyosthna, of course. —Purnachandra Contents at a Glance About the Authors���������������������������������������������������������������������������������������������������xxi About the Technical Reviewer������������������������������������������������������������������������������xxiii Acknowledgments�������������������������������������������������������������������������������������������������xxv Introduction���������������������������������������������������������������������������������������������������������xxvii ■ ■Part I: Dashboards and Data Visualization����������������������������������������� 1 ■ ■Chapter 1: Introduction to Dashboard and Decision Support Development��������� 3 ■ ■Chapter 2: A Critical View of Information Visualization�������������������������������������� 19 ■ ■Chapter 3: The Principles of Data Visualization in Microsoft Excel���������������������� 35 ■ ■Chapter 4: The Excel Data Presentation Library�������������������������������������������������� 55 ■ ■Part II: Excel Dashboard Design Tools and Concepts������������������������ 79 ■ ■Chapter 5: Getting Started: Thinking Outside the Cell����������������������������������������� 81 ■ ■Chapter 6: Visual Basic for Applications for Excel, a Refresher������������������������ 111 ■ ■Chapter 7: Avoiding Common Pitfalls in Development and Design������������������� 131 ■■Chapter 8: The Elements of Good Excel Dashboards and Decision Support Systems����������������������������������������������������������������������������������������������� 155 ■ ■Part III: Formulas, Controls, and Charts����������������������������������������� 177 ■ ■Chapter 9: Introducing Formula Concepts��������������������������������������������������������� 179 ■ ■Chapter 10: Advanced Formula Concepts��������������������������������������������������������� 199 ■ ■Chapter 11: Metrics: Performance and Context������������������������������������������������ 217 ■ ■Chapter 12: Charts with Heart (or, How to Avoid a Chart Attack)���������������������� 227 v ■ Contents at a Glance ■ ■Chapter 13: Creating an Interactive Gantt Chart Dashboard����������������������������� 253 ■ ■Chapter 14: An Interactive Gantt Chart Dashboard, Data Visualization������������� 273 ■ ■Chapter 15: An Interactive Gantt Chart Dashboard, Data Details on Demand���������295 ■ ■Part IV: From User Interface to Presentation���������������������������������� 317 ■ ■Chapter 16: Working with Form Controls���������������������������������������������������������� 319 ■ ■Chapter 17: Getting Input from Users ��������������������������������������������������������������� 349 ■ ■Chapter 18: Storage Patterns for User Input����������������������������������������������������� 369 ■ ■Chapter 19: Building for Sensitivity Analysis���������������������������������������������������� 393 ■ ■Chapter 20: Perfecting the Presentation����������������������������������������������������������� 423 ■ ■Part V: Data Models, PowerPivot, and Power Query����������������������� 449 ■ ■Chapter 21: Data Model Capabilities of Excel 2013������������������������������������������� 451 ■ ■Chapter 22: Advanced Modeling with Slicers, Filters, and Pivot Tables������������ 465 ■ ■Chapter 23: Introduction to Power Query���������������������������������������������������������� 483 ■ ■Chapter 24: Introduction to PowerPivot������������������������������������������������������������ 515 Index��������������������������������������������������������������������������������������������������������������������� 535 vi Contents About the Authors���������������������������������������������������������������������������������������������������xxi About the Technical Reviewer������������������������������������������������������������������������������xxiii Acknowledgments�������������������������������������������������������������������������������������������������xxv Introduction���������������������������������������������������������������������������������������������������������xxvii ■ ■Part I: Dashboards and Data Visualization����������������������������������������� 1 ■ ■Chapter 1: Introduction to Dashboard and Decision Support Development���������� 3 The Data Problem������������������������������������������������������������������������������������������������������������� 3 Enter Excel: The Most Dangerous Program in the World�������������������������������������������������� 4 Not Realizing How Far Spreadsheets Have Come���������������������������������������������������������������������������������� 4 Garbage In, Gospel Out��������������������������������������������������������������������������������������������������������������������������� 5 How Excel Fits In������������������������������������������������������������������������������������������������������������������������������������ 5 What Excel Is Good For����������������������������������������������������������������������������������������������������� 6 A Commercial-Off-the-Shelf Solution����������������������������������������������������������������������������������������������������� 6 Flexible and Customizable���������������������������������������������������������������������������������������������������������������������� 6 Familiarity and Ubiquity�������������������������������������������������������������������������������������������������������������������������� 6 Inexpensive-ish�������������������������������������������������������������������������������������������������������������������������������������� 7 Quick Turnaround Time��������������������������������������������������������������������������������������������������������������������������� 7 A Good Return on Investment����������������������������������������������������������������������������������������������������������������� 7 What Excel Isn’t Good For������������������������������������������������������������������������������������������������ 7 A Full-Fledged Database������������������������������������������������������������������������������������������������������������������������ 8 Enterprise-Level Reporting��������������������������������������������������������������������������������������������������������������������� 8 A Full Software Package������������������������������������������������������������������������������������������������������������������������� 8 Predicting the Future������������������������������������������������������������������������������������������������������������������������������ 8 vii ■ Contents Buzzword Bingo: Dashboards, Reports, Data Visualization, and Others��������������������������� 9 Dashboards��������������������������������������������������������������������������������������������������������������������������������������������� 9 Decision Support Systems�������������������������������������������������������������������������������������������������������������������� 10 The Excel Development Trifecta������������������������������������������������������������������������������������� 10 Good Visualization Practices����������������������������������������������������������������������������������������������������������������� 11 Good Development Practices���������������������������������������������������������������������������������������������������������������� 12 Thinking Outside the Cell���������������������������������������������������������������������������������������������������������������������� 15 Available Resources������������������������������������������������������������������������������������������������������� 16 The Last Word����������������������������������������������������������������������������������������������������������������� 17 ■ ■Chapter 2: A Critical View of Information Visualization�������������������������������������� 19 Understanding the Problem�������������������������������������������������������������������������������������������� 19 Of Pilots and Metaphors������������������������������������������������������������������������������������������������� 20 A Metaphor Too Far: Driving Down the Information Superhighway�������������������������������� 22 A Brief History of Dashboards and Information Visualization����������������������������������������� 23 A Quick Summary Before Taking a Critical Look������������������������������������������������������������ 24 Dashboards by Example: U. Patent and Trademark Office������������������������������������������ 24 Radial Gauges��������������������������������������������������������������������������������������������������������������������������������������� 26 So Many Metrics, So Little Working Memory���������������������������������������������������������������������������������������� 27 Is the Logo Necessary? ����������������������������������������������������������������������������������������������������������������������� 28 Visualizations That Look Cool but Just Don’t Work�������������������������������������������������������� 29 Data Journalism����������������������������������������������������������������������������������������������������������������������������������� 31 Why These Examples Are Important����������������������������������������������������������������������������������������������������� 33 The Last Word����������������������������������������������������������������������������������������������������������������� 33 ■ ■Chapter 3: The Principles of Data Visualization in Microsoft Excel���������������������� 35 What Is Visual Perception and How Does It Work?��������������������������������������������������������� 35 Perception and the Visual World����������������������������������������������������������������������������������������������������������� 36 Our Bias Toward Forms: Perception and Gestalt Psychology���������������������������������������������������������������� 36 viii ■ Contents The Preattentive Attributes of Perception���������������������������������������������������������������������� 46 Color Attributes������������������������������������������������������������������������������������������������������������������������������������� 47 High-Precision Judging������������������������������������������������������������������������������������������������������������������������ 49 Lower Precision, but Still Useful����������������������������������������������������������������������������������������������������������� 51 The Last Word����������������������������������������������������������������������������������������������������������������� 53 ■ ■Chapter 4: The Excel Data Presentation Library�������������������������������������������������� 55 Tables����������������������������������������������������������������������������������������������������������������������������� 55 Line and Bar Charts�������������������������������������������������������������������������������������������������������� 56 Scatter Charts���������������������������������������������������������������������������������������������������������������� 60 Scatter Charts vs.
Line Charts�������������������������������������������������������������������������������������������������������������� 61 Correlation Analysis������������������������������������������������������������������������������������������������������������������������������ 64 Correlation Fit and Coefficient�������������������������������������������������������������������������������������������������������������� 65 Linear Relationship and Using R2 Correctly������������������������������������������������������������������������������������������ 66 Bullet Graphs������������������������������������������������������������������������������������������������������������������ 67 Small Multiples��������������������������������������������������������������������������������������������������������������� 68 Charts Never to Use������������������������������������������������������������������������������������������������������� 69 Cylinders, Cones, and Pyramid Charts�������������������������������������������������������������������������������������������������� 69 Pie Charts��������������������������������������������������������������������������������������������������������������������������������������������� 71 Doughnut Charts����������������������������������������������������������������������������������������������������������������������������������� 72 Charts in the Third Dimension�������������������������������������������������������������������������������������������������������������� 72 Surface Charts�������������������������������������������������������������������������������������������������������������������������������������� 74 Stacked Columns and Area Charts������������������������������������������������������������������������������������������������������� 75 Radar Charts����������������������������������������������������������������������������������������������������������������������������������������� 77 The Last Word����������������������������������������������������������������������������������������������������������������� 78 ix ■ Contents ■ ■Part II: Excel Dashboard Design Tools and Concepts������������������������ 79 ■ ■Chapter 5: Getting Started: Thinking Outside the Cell����������������������������������������� 81 House Hunters: Excel Edition����������������������������������������������������������������������������������������� 82 The Purely VBA Method������������������������������������������������������������������������������������������������������������������������ 83 The Semi-code Method������������������������������������������������������������������������������������������������������������������������ 86 The No-Code Method���������������������������������������������������������������������������������������������������������������������������� 95 Sorting�������������������������������������������������������������������������������������������������������������������������� 101 The Rollover Method���������������������������������������������������������������������������������������������������� 105 Rollover Method Basics���������������������������������������������������������������������������������������������������������������������� 107 Implementing the Rollover Method���������������������������������������������������������������������������������������������������� 107 The Last Word��������������������������������������������������������������������������������������������������������������� 109 ■ ■Chapter 6: Visual Basic for Applications for Excel, a Refresher������������������������ 111 Making the Most of Your Coding Experience���������������������������������������������������������������� 111 Tell Excel: Stop Annoying Me!������������������������������������������������������������������������������������������������������������� 112 Make Loud Comments������������������������������������������������������������������������������������������������������������������������ 113 Pick a Readable Font�������������������������������������������������������������������������������������������������������������������������� 115 Start Using the Immediate Window, Immediately������������������������������������������������������������������������������� 115 Opt for Option Explicit������������������������������������������������������������������������������������������������������������������������� 116 Naming Conventions���������������������������������������������������������������������������������������������������� 117 Hungarian Notation����������������������������������������������������������������������������������������������������������������������������� 117 “Loose” CamelCase Notation������������������������������������������������������������������������������������������������������������� 118 Named Ranges����������������������������������������������������������������������������������������������������������������������������������� 119 Sheet Objects������������������������������������������������������������������������������������������������������������������������������������� 119 Referencing������������������������������������������������������������������������������������������������������������������ 121 Shorthand References������������������������������������������������������������������������������������������������������������������������ 121 Worksheet Object Names������������������������������������������������������������������������������������������������������������������� 122 Procedures and Macros���������������������������������������������������������������������������������������������������������������������� 122 Development Styles and Principles������������������������������������������������������������������������������ 123 Strive to Store Your Commonly Used Procedures in Relevant Worksheet Tabs���������������������������������� 123 x ■ Contents No More Using the ActiveSheet, ActiveCell, ActiveWorkbook, and Selection Objects������������������������� 127 Render Unto Excel the Things That Are Excel’s and Unto VBA the Things That Require VBA�������������� 128 Encapsulating Your Work�������������������������������������������������������������������������������������������������������������������� 129 The Last Word��������������������������������������������������������������������������������������������������������������� 129 ■ ■Chapter 7: Avoiding Common Pitfalls in Development and Design������������������� 131 Calculation Pitfalls�������������������������������������������������������������������������������������������������������� 132 Volatile Functions and Actions������������������������������������������������������������������������������������������������������������ 132 Understanding Different Formula Speeds������������������������������������������������������������������������������������������ 141 Spreadsheet Errors����������������������������������������������������������������������������������������������������������������������������� 144 Code Pitfalls����������������������������������������������������������������������������������������������������������������� 147 Copy/Paste Iterations�������������������������������������������������������������������������������������������������������������������������� 148 Testing Properties Before Setting Them��������������������������������������������������������������������������������������������� 148 Bad Names������������������������������������������������������������������������������������������������������������������� 151 The Last Word��������������������������������������������������������������������������������������������������������������� 153 ■■Chapter 8: The Elements of Good Excel Dashboards and Decision Support Systems����������������������������������������������������������������������������������������������� 155 Types of Dashboards���������������������������������������������������������������������������������������������������� 155 Strategic��������������������������������������������������������������������������������������������������������������������������������������������� 155 Operational����������������������������������������������������������������������������������������������������������������������������������������� 156 Analytical�������������������������������������������������������������������������������������������������������������������������������������������� 156 Decision Support Systems������������������������������������������������������������������������������������������� 156 Simplified Layout���������������������������������������������������������������������������������������������������������� 157 Information-Transformation-Presentation�������������������������������������������������������������������� 159 Common Dashboard Problems������������������������������������������������������������������������������������� 162 Too Much Formatting and Embellishment������������������������������������������������������������������������������������������ 162 Too Many Tabs������������������������������������������������������������������������������������������������������������������������������������ 165 Bad Layout������������������������������������������������������������������������������������������������������������������������������������������ 167 Needless Protection���������������������������������������������������������������������������������������������������������������������������� 171 Instructions and Documentation��������������������������������������������������������������������������������������������������������� 174 The Last Word��������������������������������������������������������������������������������������������������������������� 175 xi ■ Contents ■ ■Part III: Formulas, Controls, and Charts����������������������������������������� 177 ■ ■Chapter 9: Introducing Formula Concepts��������������������������������������������������������� 179 Formula Help���������������������������������������������������������������������������������������������������������������� 179 F2 to See the Formula of a Select Cell����������������������������������������������������������������������������������������������� 179 F9 for On-Demand and Piecewise Calculation����������������������������������������������������������������������������������� 179 Evaluate Formula Button�������������������������������������������������������������������������������������������������������������������� 180 Excel Formula Concepts����������������������������������������������������������������������������������������������� 181 Operators, in Depth����������������������������������������������������������������������������������������������������������������������������� 181 The Range Operator (:)������������������������������������������������������������������������������������������������������������������������ 182 The Union Operator (,)������������������������������������������������������������������������������������������������������������������������� 184 The Intersection Operator ( )��������������������������������������������������������������������������������������������������������������� 185 When to Use Conditional Expressions ������������������������������������������������������������������������� 187 Deceptively Simple Nested IF Statements������������������������������������������������������������������������������������������ 187 CHOOSE Wisely����������������������������������������������������������������������������������������������������������������������������������� 189 Why This Discussion Is Important������������������������������������������������������������������������������������������������������� 190 Introduction to Boolean Concepts�������������������������������������������������������������������������������� 190 Condensing Your Work������������������������������������������������������������������������������������������������������������������������ 193 The Legend of XOR( )-oh���������������������������������������������������������������������������������������������������������������������� 193 Do We Really Need IF?������������������������������������������������������������������������������������������������� 195 The Last Word��������������������������������������������������������������������������������������������������������������� 197 ■ ■Chapter 10: Advanced Formula Concepts��������������������������������������������������������� 199 Filtering and Highlighting��������������������������������������������������������������������������������������������� 199 Filtering with Formulas����������������������������������������������������������������������������������������������������������������������� 199 Conditional Highlighting Using Formulas�������������������������������������������������������������������������������������������� 203 Selecting���������������������������������������������������������������������������������������������������������������������� 205 Aggregating������������������������������������������������������������������������������������������������������������������ 211 Using SUMPRODUCT for Aggregation������������������������������������������������������������������������������������������������� 211 You’re About To Be FOILed!����������������������������������������������������������������������������������������������������������������� 214 Reusable Components������������������������������������������������������������������������������������������������� 215 The Last Word��������������������������������������������������������������������������������������������������������������� 216 xii ■ Contents ■ ■Chapter 11: Metrics: Performance and Context������������������������������������������������ 217 Telling the Whole Story Like a Reporter: An Introduction to Analytics�������������������������� 217 Who and Where���������������������������������������������������������������������������������������������������������������������������������� 218 When�������������������������������������������������������������������������������������������������������������������������������������������������� 220 Why, How, and What��������������������������������������������������������������������������������������������������������������������������� 220 What If?