Contents
Part I: Dashboards and Data Visualization
Chapter 1: Introduction to Dashboard and Decision Support Development
Enter Excel: The Most Dangerous Program in the World
Not Realizing How Far Spreadsheets Have Come
A Commercial-Off-the-Shelf Solution
Buzzword Bingo: Dashboards, Reports, Data Visualization, and Others
The Excel Development Trifecta
Chapter 2: A Critical View of Information Visualization
A Metaphor Too Far: Driving Down the Information Superhighway
A Brief History of Dashboards and Information Visualization
A Quick Summary Before Taking a Critical Look
Dashboards by Example: U.S. Patent and Trademark Office
So Many Metrics, So Little Working Memory
Visualizations That Look Cool but Just Don’t Work
Why These Examples Are Important
Chapter 3: The Principles of Data Visualization in Microsoft Excel
What Is Visual Perception and How Does It Work?
Perception and the Visual World
Our Bias Toward Forms: Perception and Gestalt Psychology
The Preattentive Attributes of Perception
Lower Precision, but Still Useful
Chapter 4: The Excel Data Presentation Library
Scatter Charts vs. Line Charts
Correlation Fit and Coefficient
Linear Relationship and Using R2Correctly
Cylinders, Cones, and Pyramid Charts
Stacked Columns and Area Charts
Part II: Excel Dashboard Design Tools and Concepts
Chapter 5: Getting Started: Thinking Outside the Cell
Implementing the Rollover Method
Chapter 6: Visual Basic for Applications for Excel, a Refresher
Making the Most of Your Coding Experience
Start Using the Immediate Window, Immediately
Development Styles and Principles
Strive to Store Your Commonly Used Procedures in Relevant Worksheet Tabs
No More Using the ActiveSheet, ActiveCell, ActiveWorkbook, and Selection Objects
Render Unto Excel the Things That Are Excel’s and Unto VBA the Things That Require VBA
Chapter 7: Avoiding Common Pitfalls in Development and Design
Volatile Functions and Actions
Understanding Different Formula Speeds
Testing Properties Before Setting Them
Chapter 8: The Elements of Good Excel Dashboards and Decision Support Systems
Information-Transformation-Presentation
Too Much Formatting and Embellishment
Instructions and Documentation
Part III: Formulas, Controls, and Charts
Chapter 9: Introducing Formula Concepts
F2 to See the Formula of a Select Cell
F9 for On-Demand and Piecewise Calculation
When to Use Conditional Expressions
Deceptively Simple Nested IF Statements
Why This Discussion Is Important
Introduction to Boolean Concepts
Chapter 10: Advanced Formula Concepts
Conditional Highlighting Using Formulas
Using SUMPRODUCT for Aggregation
Chapter 11: Metrics: Performance and Context
Telling the Whole Story Like a Reporter: An Introduction to Analytics
Working Criteria for Choosing Metrics
Chapter 12: Charts with Heart (or, How to Avoid a Chart Attack)
Charts: More Art Than Science, in Some Ways
Chart Formats for Simple Comparison
Chapter 13: Creating an Interactive Gantt Chart Dashboard
Features of the Gantt Chart Dashboard
Chapter 14: An Interactive Gantt Chart Dashboard, Data Visualization
Placing the Chart onto the Dashboard
Creating the Banded Chart Legend
Creating the Endpoints of the Legend
Interpolating Between the Endpoints
Chapter 15: An Interactive Gantt Chart Dashboard, Data Details on Demand
A Quick Review of the Rollover Method
A Conceptual Model for Rollover Method Implementation
Implementing Conditional Formatting
Getting the Information to Create the Details-on-Demand Pop-up
Making the Pop-up Follow Your Mouse
Part IV: From User Interface to Presentation
Chapter 16: Working with Form Controls
The Least Favorites: Button, Label, Option Button, and GroupBox Controls
Highlighting Data Points on Charts
Chapter 17: Getting Input from Users
Custom Formats for Input Validation
Creating a Spreadsheet-Based Wizard
Layout Patterns for the Spreadsheet-Based Wizard
Views That Require Additional Instruction
Components That Provide Information
Chapter 18: Storage Patterns for User Input
The World Health Organization: An Applied Example
Design of Your Spreadsheet File
Setting Focus to the First Input Cell
Linking the Column of Country Names to the Form Control ListBox
Chapter 19: Building for Sensitivity Analysis
Sensitivity Analysis on a Weighted Average Model
Creating a Linked Values Table
Getting to the Backend, the Intermediate Table
Formula-based Sorting Data for Analysis
The Sort Column, Your New Best Friend
The Match Index Column, the Sort Column’s Buddy
Chapter 20: Perfecting the Presentation
Implementation and Design of the Weight Adjustment System
Displaying Data from the Intermediate Table
The Current Rank of Each Country
In-cell Bar Charts for All Metrics
“Sort By” Dropdown and Sort Labels
Using Boolean Formulas to Define Which Metric Has Been Selected
Connecting Everything with Conditional Format Highlighting
The Presentation Display Buttons
Weighted vs. Not-Weighted Metrics
Part V: Data Models, PowerPivot, and Power Query
Chapter 21: Data Model Capabilities of Excel 2013
Preparing Data for Relationships
Working with Relationships in the Excel Data Model
Exposing Relationships with Pivot Tables
Slicing and Dicing with Relationships
Chapter 22: Advanced Modeling with Slicers, Filters, and Pivot Tables
Creating an Interactive Excel Dashboard
Steps for Creating the Dashboard
Tips for Using Pivot Tables, Slicers, and the Data Model Effectively in Your Dashboards
Chapter 23: Introduction to Power Query
What Is Power Query, and How Can It Help You?
Loading Data to Where You Want It
Five Data Problems and How Power Query Can Solve Them
Loading the Data in to Power Query
Removing Extra Spaces from the Name Column
Bringing the Data Back to Excel
Loading the Text File Data into Power Query
Merging Customer and Phone Number Data
Giving a Proper Name to the Phone Number Column
Loading the Merged Data in to Excel
Problem 4: Duplicates in Your Data
Problem 5: Inconsistent Data Formats
Chapter 24: Introduction to PowerPivot
A Note About How to Get PowerPivot
What to Expect from This Chapter
Getting Started with PowerPivot
Loading Sample Data into PowerPivot
What Is the PowerPivot Data Model?
Let’s Enter the World of PowerPivot
Creating Your First PowerPivot Table
The Real Power of PowerPivot: DAX Formulas
Let’s Create Your First-Ever DAX Formula Measure in PowerPivot
Let’s Create a Few More DAX Measures
Example PowerPivot Report: Top Five Products Based on Average Quantity per Customer