Data at Work: Best practices for creating effective charts and information graphics in Microsoft® Excel®

Data at Work: Best practices for creating effective charts and information graphics in Microsoft® Excel®

by Jorge Camões
Released April 2016
Publisher(s): New Riders
ISBN: 9780134268798

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

Information visualization is a language. Like any language, it can be used for multiple purposes. A poem, a novel, and an essay all share the same language, but each one has its own set of rules. The same is true with information visualization: a product manager, statistician, and graphic designer each approach visualization from different perspectives.


Data at Work was written with you, the spreadsheet user, in mind. This book will teach you how to think about and organize data in ways that directly relate to your work, using the skills you already have. In other words, you don’t need to be a graphic designer to create functional, elegant charts: this book will show you how.


Although all of the examples in this book were created in Microsoft Excel, this is not a book about how to use Excel. Data at Work will help you to know which type of chart to use and how to format it, regardless of which spreadsheet application you use and whether or not you have any design experience. In this book, you’ll learn how to extract, clean, and transform data; sort data points to identify patterns and detect outliers; and understand how and when to use a variety of data visualizations including bar charts, slope charts, strip charts, scatter plots, bubble charts, boxplots, and more.


Because this book is not a manual, it never specifies the steps required to make a chart, but the relevant charts will be available online for you to download, with brief explanations of how they were created.

Table of contents

  1. Title Page
  2. Copyright Page
  3. Dedication Page
  4. Acknowledgments
  5. About the Author
  6. Contents
  7. Introduction
    1. A Quantitative Change
    2. A Language for Multiple Users
    3. A Wrong Model
    4. A Better Model
    5. Data Visualization for the Masses
    6. The Labor Market
    7. My View of Data Visualization
    8. Organization of the Book
    9. The Limits of This Book
    10. Break the Rules!
    11. Companion Website
  8. 1. The Building Blocks of Data Visualization
    1. Spatial Organization of Stimuli
    2. Seeing Abstract Concepts
      1. Charts
      2. Networks
      3. Maps
      4. Volume: Figurative Visualizations
      5. Visualization in Excel
    3. Retinal Variables
    4. From Concepts to Charts
    5. The Proto-Chart
    6. Chart Effectiveness
    7. Takeaways
  9. 2. Visual Perception
    1. Perception and Cognition
      1. Cognitive Offloading
      2. A False Dichotomy
      3. Charts and Tables
    2. Eye Physiology
      1. The Retina
      2. Cones
      3. The Arc of Visual Acuity
      4. Saccades
      5. Impact of Eye Physiology on Visualization
    3. Pre-Attentive Processing
      1. Salience
      2. Impact of Pre-Attentive Processing and Salience on Visualization
    4. Working Memory
      1. Impact of Working Memory on Visualization
    5. Gestalt Laws
      1. Law of Proximity
      2. Law of Similarity
      3. Law of Segregation
      4. Law of Connectivity
      5. Law of Common Fate
      6. Law of Closure
      7. Law of Figure/Ground
      8. Law of Continuity
      9. Impact of Gestalt Laws on Visualization
    6. The Limits of Perception
      1. Why We Need Grid Lines and Reference Lines: Weber’s Law
      2. Being Aware of Distortions: Stevens’ Power Law
      3. Context and Optical Illusions
      4. Impact of the Limits of Perception on Visualization
    7. Takeaways
  10. 3. Beyond Visual Perception
    1. Social Prägnanz
    2. Breaking the Rules
      1. The Tragedy of the Commons
      2. Color Symbolism
      3. Representing Time
      4. Axis Folding
      5. Don’t Make Me Think!
    3. Literacy and Experience
      1. Graphic Literacy
      2. Familiarity with the Subject
      3. Information Asymmetry
    4. Organizational Contexts
      1. Wrong Messages from the Top
      2. Impression Management
    5. Takeaways
  11. 4. Data Preparation
    1. Problems with the Data
      1. Structure without Content
      2. Content without Structure
    2. What Does “Well-Structured Data” Mean, Anyway?
      1. A Helping Hand: Pivot Tables
    3. Extracting the Data
      1. The PDF Plague
      2. “Can It Export to Excel?”
    4. Cleansing Data
    5. Transforming Data
    6. Loading the Data Table
    7. Data Management in Excel
      1. Organizing the Workbook
      2. Links Outside of Excel
      3. Formulas
      4. Cycles of Production and Analysis
    8. Takeaways
  12. 5. Data Visualization
    1. From Patterns to Points
      1. Shape Visualization
      2. Point Visualization
      3. Outlier Visualization
      4. Data Visualization Tasks
    2. The Construction of Knowledge
      1. Data
      2. Information
      3. Knowledge
      4. Wisdom
    3. Defining Data Visualization
    4. Languages, Stories, and Landscapes
    5. Graphical Literacy
    6. Graphical Landscapes
      1. Profiling
      2. Dashboards
      3. Infographics
    7. A Crossroad of Knowledge
      1. Statistics
      2. Design
      3. Applications
      4. Content and Context
    8. Data Visualization in Excel
      1. The Good
      2. The Bad
      3. The Ugly
      4. Beyond the Excel Chart Library
      5. Don’t Make Excel Charts
    9. Takeaways
  13. 6. Data Discovery, Analysis, and Communication
    1. Where to Start?
      1. The Visual Information-Seeking Mantra
      2. Focus plus Context
    2. Asking Questions
      1. A Classification of Questions
    3. Selecting and Collecting the Data
    4. Searching for Patterns
    5. Setting Priorities
    6. Reporting Results
      1. Clarification
      2. The Human Dimension
      3. The Design
    7. Project: Monthly Births
      1. Defining the Problem
      2. Collecting the Data
      3. Assessing Data Availability
      4. Assessing Data Quality
      5. Adjusting the Data
      6. Exploring the Data
      7. Embracing Seasonality
      8. Communicating Our Findings
    8. Takeaways
  14. 7. How to Choose a Chart
    1. Task-Based Chart Classification
    2. Audience Profile
    3. Sharing Visualizations
      1. Screens and Projectors
      2. Smartphones and Vertical Displays
      3. PDF Files
      4. Excel Files
      5. Sharing Online
    4. Takeaways
  15. 8. A Sense of Order
    1. The Bar Chart
      1. Vertical and Horizontal Bars
      2. Color Coding
      3. Ordering
      4. Chart Size
      5. Breaks in the Scale
      6. Changing Metrics to Avoid Breaks in the Scale
      7. Evolution and Change
      8. A Special Bar Chart: The Population Pyramid
    2. Dot Plots
    3. Slope Charts
    4. Strip Plots
    5. Speedometers
    6. Bullet Charts
    7. Alerts
    8. Takeaways
  16. 9. Parts of a Whole: Composition Charts
    1. What Is Composition?
      1. Composition or Comparison?
    2. Pie Charts
      1. Critique
      2. Damage Control
    3. Donut Charts
      1. Donuts as Multi-Level Pies
    4. Actual Hierarchical Charts: Sunburst Charts and Treemaps
    5. Stacked Bar Chart
    6. Pareto Chart
    7. Takeaways
  17. 10. Scattered Data
    1. The Data
    2. Distribution
      1. Showing Everything: Transparencies and Jittering
      2. Quantifying Impressions
      3. Mean and Standard Deviation
      4. The Median and the Interquartile Range
      5. Outliers
    3. Box-and-Whisker Plots
      1. Z-Scores
    4. The Pareto Chart Revisited
    5. Excel Maps
    6. Histograms
      1. Bin Number and Width
      2. Histograms and Bar Charts
    7. Cumulative Frequency Distribution
    8. Takeaways
  18. 11. Change Over Time
    1. Focus on the Flow: The Line Chart
      1. Scales and Aspect Ratios
    2. Focus on the Relationships: Connected Scatter Plots
    3. Sudden Changes: The Step Chart
    4. Seasonality: The Cycle Plot
    5. Sparklines
    6. Animation
    7. Takeaways
  19. 12. Relationships
    1. Understanding Relationships
      1. Curve Fitting
    2. The Scatter Plot
      1. Scatter Plot Design
      2. Clusters and Groupings
      3. Multiple Series and Subsets
      4. Profiles
    3. Bubble Charts
    4. Takeaways
  20. 13. Profiling
    1. The Need to Solve
    2. Panel Charts
    3. Bar Charts with Multiple Series
    4. Horizon Chart
    5. Reorderable Matrix
    6. Small Multiples
    7. Profiling in Excel
    8. Takeaways
  21. 14. Designing for Effectiveness
    1. The Aesthetic Dimension
      1. A Wrong Model
      2. The Design Continuum
    2. Tools Are Not Neutral: Defaults
    3. Reason and Emotion
      1. A.I.D.A.
      2. Does Reason Follow Emotion?
      3. Emotion and Effectiveness
      4. Occam’s Razor
    4. Designing Chart Components
      1. Pseudo-3D
      2. Textures
      3. Titles
      4. Fonts
      5. Annotations
      6. Grid Lines
      7. Clip Art
      8. The Secondary Axis
      9. Legends
      10. Backgrounds
    5. Ordering the Data
    6. Number of Series
      1. Chart Type
      2. Grouping
      3. Residual Category
      4. Context
      5. Small Multiples
    7. Lying and Deceiving with Charts
      1. Data, Perception, and Cognition
      2. Exaggerating Differences
      3. Distorting Time Series
      4. Aspect Ratio
      5. Omitting Points
      6. Mistaking Variation for Evolution
      7. Double Axes
      8. Pseudo 3D
      9. Context
      10. When Everything Goes
    8. Takeaways
  22. 15. Color: Beyond Aesthetics
    1. Quantifying Color
      1. The RGB Model
      2. The HSL Model
    2. Stimuli Intensity
    3. The Functional Tasks of Color
      1. Categorize
      2. Group
      3. Emphasize
      4. Sequence
      5. Diverge
      6. Alert
    4. Color Symbolism
    5. The Role of Gray
    6. Color Staging
    7. Color Harmony
      1. General Principles
      2. The Classical Rules
      3. Complementary Colors
      4. Split Complementary Colors
      5. Triadic Harmony
      6. Analogous Colors
      7. Rectangle
      8. Warm Colors and Cool Colors
    8. Sources for Color Palettes
      1. Excel
      2. Beyond Excel
      3. Color Blindness
    9. Takeaways
  23. 16. Conclusion
    1. It’s All About Pragmatism, Not Aesthetics
    2. Say Goodbye to the Old Ways
    3. Find Your Own Data Visualization Model
    4. In Business Visualization, Hard Work Is Not Always the Best Work
    5. Organizational Literacy
    6. Reason and Emotion
    7. Play with Constraints
    8. The Tools
  24. Index

Product information

  • Title: Data at Work: Best practices for creating effective charts and information graphics in Microsoft® Excel®
  • Author(s): Jorge Camões
  • Release date: April 2016
  • Publisher(s): New Riders
  • ISBN: 9780134268798