Dashboards for Excel

Dashboards for Excel

by
Released October 2015
Publisher(s): Apress
ISBN: 9781430249450

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

The book takes a hands-on approach to developing dashboards, from instructing users on advanced Excel techniques to addressing dashboard pitfalls common in the real world. Dashboards for Excel is your key to creating informative, actionable, and interactive dashboards and decision support systems. Throughout the book, the reader is challenged to think about Excel and data analytics differently—that is, to think outside the cell. This book shows you how to create dashboards in Excel quickly and effectively.

In this book, you learn how to:

  • Apply data visualization principles for more effective dashboards
  • Employ dynamic charts and tables to create dashboards that are constantly up-to-date and providing fresh information
  • Use understated yet powerful formulas for Excel development
  • Apply advanced Excel techniques mixing formulas and Visual Basic for Applications (VBA) to create interactive dashboards
  • Create dynamic systems for decision support in your organization
  • Avoid common problems in Excel development and dashboard creation
  • Get started with the Excel data model, PowerPivot, and Power Query
    •

    Table of contents

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

    Product information

    • Title: Dashboards for Excel
    • Author(s):
    • Release date: October 2015
    • Publisher(s): Apress
    • ISBN: 9781430249450