Home Page Icon
Home Page
Table of Contents for
Dedication
Close
Dedication
by Purnachandra Duggirala, Jordan Goldmeier
Dashboards for Excel
Cover
Title
Copyright
Dedication
Contents at a glance
Contents
About the Authors
About the Technical Reviewer
Acknowledgments
Introduction
Part I: Dashboards and Data Visualization
Chapter 1: Introduction to Dashboard and Decision Support Development
The Data Problem
Enter Excel: The Most Dangerous Program in the World
Not Realizing How Far Spreadsheets Have Come
Garbage In, Gospel Out
How Excel Fits In
What Excel Is Good For
A Commercial-Off-the-Shelf Solution
Flexible and Customizable
Familiarity and Ubiquity
Inexpensive-ish
Quick Turnaround Time
A Good Return on Investment
What Excel Isn’t Good For
A Full-Fledged Database
Enterprise-Level Reporting
A Full Software Package
Predicting the Future
Buzzword Bingo: Dashboards, Reports, Data Visualization, and Others
Dashboards
Decision Support Systems
The Excel Development Trifecta
Good Visualization Practices
Good Development Practices
Thinking Outside the Cell
Available Resources
The Last Word
Chapter 2: A Critical View of Information Visualization
Understanding the Problem
Of Pilots and Metaphors
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
Radial Gauges
So Many Metrics, So Little Working Memory
Is the Logo Necessary?
Visualizations That Look Cool but Just Don’t Work
Data Journalism
Why These Examples Are Important
The Last Word
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
Color Attributes
High-Precision Judging
Lower Precision, but Still Useful
The Last Word
Chapter 4: The Excel Data Presentation Library
Tables
Line and Bar Charts
Scatter Charts
Scatter Charts vs. Line Charts
Correlation Analysis
Correlation Fit and Coefficient
Linear Relationship and Using R2 Correctly
Bullet Graphs
Small Multiples
Charts Never to Use
Cylinders, Cones, and Pyramid Charts
Pie Charts
Doughnut Charts
Charts in the Third Dimension
Surface Charts
Stacked Columns and Area Charts
Radar Charts
The Last Word
Part II: Excel Dashboard Design Tools and Concepts
Chapter 5: Getting Started: Thinking Outside the Cell
House Hunters: Excel Edition
The Purely VBA Method
The Semi-code Method
The No-Code Method
Sorting
The Rollover Method
Rollover Method Basics
Implementing the Rollover Method
The Last Word
Chapter 6: Visual Basic for Applications for Excel, a Refresher
Making the Most of Your Coding Experience
Tell Excel: Stop Annoying Me!
Make Loud Comments
Pick a Readable Font
Start Using the Immediate Window, Immediately
Opt for Option Explicit
Naming Conventions
Hungarian Notation
“Loose” CamelCase Notation
Named Ranges
Sheet Objects
Referencing
Shorthand References
Worksheet Object Names
Procedures and Macros
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
Encapsulating Your Work
The Last Word
Chapter 7: Avoiding Common Pitfalls in Development and Design
Calculation Pitfalls
Volatile Functions and Actions
Understanding Different Formula Speeds
Spreadsheet Errors
Code Pitfalls
Copy/Paste Iterations
Testing Properties Before Setting Them
Bad Names
The Last Word
Chapter 8: The Elements of Good Excel Dashboards and Decision Support Systems
Types of Dashboards
Strategic
Operational
Analytical
Decision Support Systems
Simplified Layout
Information-Transformation-Presentation
Common Dashboard Problems
Too Much Formatting and Embellishment
Too Many Tabs
Bad Layout
Needless Protection
Instructions and Documentation
The Last Word
Part III: Formulas, Controls, and Charts
Chapter 9: Introducing Formula Concepts
Formula Help
F2 to See the Formula of a Select Cell
F9 for On-Demand and Piecewise Calculation
Evaluate Formula Button
Excel Formula Concepts
Operators, in Depth
The Range Operator (:)
The Union Operator (,)
The Intersection Operator ( )
When to Use Conditional Expressions
Deceptively Simple Nested IF Statements
CHOOSE Wisely
Why This Discussion Is Important
Introduction to Boolean Concepts
Condensing Your Work
The Legend of XOR( )-oh
Do We Really Need IF?
The Last Word
Chapter 10: Advanced Formula Concepts
Filtering and Highlighting
Filtering with Formulas
Conditional Highlighting Using Formulas
Selecting
Aggregating
Using SUMPRODUCT for Aggregation
You’re About To Be FOILed!
Reusable Components
The Last Word
Chapter 11: Metrics: Performance and Context
Telling the Whole Story Like a Reporter: An Introduction to Analytics
Who and Where
When
Why, How, and What
What If?
Metrics, Metrics, Metrics
Working Criteria for Choosing Metrics
The Last Word
Chapter 12: Charts with Heart (or, How to Avoid a Chart Attack)
Challenging the Defaults
Data-Ink Density
Charts: More Art Than Science, in Some Ways
Chart Formats for Simple Comparison
Column and Bar Charts
Layout Constraints
Banding
Bullet Charts
The Last Word
Chapter 13: Creating an Interactive Gantt Chart Dashboard
Features of the Gantt Chart Dashboard
Pop-up Information Box
Banded Region Chart
Dynamic Chart Legend
Behind the Scenes
The Data Worksheet Tab
The Calculation Worksheet Tab
The Dashboard Worksheet Tab
The Last Word
Chapter 14: An Interactive Gantt Chart Dashboard, Data Visualization
The Banded Region Chart
Creating the Chart
Placing the Chart onto the Dashboard
Creating the Banded Chart Legend
The Dynamic Legend
Creating the Endpoints of the Legend
Interpolating Between the Endpoints
Applying the Visual Effects
The Last Word
Chapter 15: An Interactive Gantt Chart Dashboard, Data Details on Demand
The Rollover Method
A Quick Review of the Rollover Method
A Conceptual Model for Rollover Method Implementation
Implementing a Hover Table
Implementing Conditional Formatting
Gantt Chart Rollovers
Rollover Implementation
Getting the Information to Create the Details-on-Demand Pop-up
Designing the Pop-up
Making the Pop-up Follow Your Mouse
The Last Word
Part IV: From User Interface to Presentation
Chapter 16: Working with Form Controls
Welcome to the Control Room
Form Control Fundamentals
The ComboBox Control
The ListBox Control
The Scroll Bar Control
The Spinner Control
The CheckBox Control
The Least Favorites: Button, Label, Option Button, and GroupBox Controls
Creating Scrollable Tables
Highlighting Data Points on Charts
The Dynamic Legend
The Last Word
Chapter 17: Getting Input from Users
Of Input Forms and Excel
A Simple Input Form
Custom Formats for Input Validation
Creating a Spreadsheet-Based Wizard
Layout Patterns for the Spreadsheet-Based Wizard
The Helper Tab
Moving Between Views
Views That Require Additional Instruction
Components That Provide Information
The Last Word
Chapter 18: Storage Patterns for User Input
The World Health Organization: An Applied Example
Design of Your Spreadsheet File
The Input Wizard
Setting Focus to the First Input Cell
The Database
Input Entry Table
Database Information Table
The Backend Database Table
Menu Screen Functionality
Inserting a New Record
Editing an Existing Record
Deleting a Selected Record
Linking the Column of Country Names to the Form Control ListBox
Wizard Summary Buttons
The Last Word
Chapter 19: Building for Sensitivity Analysis
Weighted Average Models
Sensitivity Analysis on a Weighted Average Model
One-Way Sensitivity Analysis
Creating a Linked Values Table
Linking to the Database
Building the Tool
Getting to the Backend, the Intermediate Table
Scrolling Capability
Adjusting the Scroll Bar
Formula-based Sorting Data for Analysis
The Sort Column, Your New Best Friend
The Match Index Column, the Sort Column’s Buddy
You Have a “Unique” Problem
Seeing It Work Altogether
The Last Word
Chapter 20: Perfecting the Presentation
Implementation and Design of the Weight Adjustment System
Displaying Data from the Intermediate Table
Results Information Label
The Current Rank of Each Country
Country Name
Total Scores for Each Country
In-cell Bar Charts for All Metrics
Best Possible Comparisons
Weight Box Progress Meters
“Sort By” Dropdown and Sort Labels
Dropdown Metric Selection
Using Boolean Formulas to Define Which Metric Has Been Selected
Connecting Everything with Conditional Format Highlighting
The Presentation Display Buttons
Going Back to the Menu
Resetting the Weights
Data Display and Aesthetics
Weighted vs. Not-Weighted Metrics
Color Choices
Data Spacing
The Last Word
Part V: Data Models, PowerPivot, and Power Query
Chapter 21: Data Model Capabilities of Excel 2013
Relationship Advice
Preparing Data for Relationships
Setting Up the Relationships
Working with Relationships in the Excel Data Model
Exposing Relationships with Pivot Tables
Your First Multitable Pivot
Slicing and Dicing with Relationships
Rules for Happy Relationships
The Last Word
Chapter 22: Advanced Modeling with Slicers, Filters, and Pivot Tables
Creating an Interactive Excel Dashboard
Let’s Meet the Data
Steps for Creating the Dashboard
Problems with Pivot Tables
Tips for Using Pivot Tables, Slicers, and the Data Model Effectively in Your Dashboards
The Last Word
Chapter 23: Introduction to Power Query
What Is Power Query, and How Can It Help You?
Bringing Data to Excel
Transforming Data
Loading Data to Where You Want It
Five Data Problems and How Power Query Can Solve Them
Problem 1: Unclean Data
Loading the Data in to Power Query
Removing Extra Spaces from the Name Column
Removing the Extra Periods
Fixing the E-mail Addresses
Bringing the Data Back to Excel
Problem 2: Partial Data Sets
Loading the Text File Data into Power Query
Saving and Loading the Query
Merging Customer and Phone Number Data
Giving a Proper Name to the Phone Number Column
Loading the Merged Data in to Excel
Problem 3: Data Consolidation
Taking a Look at the Old Data
Setting Up a New Connection
Appending Data
Problem 4: Duplicates in Your Data
Problem 5: Inconsistent Data Formats
The Last Word
Chapter 24: Introduction to PowerPivot
What Is 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
PowerPivot and Excel Dashboards
The Last Word
Index
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Copyright
Next
Next Chapter
Contents at a glance
For Katherine.
—Jordan
To Jyosthna, of course.
—Purnachandra
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset