Condensing the visual display of comparisons: Data Dashboards

by Stephanie Chasteen on September 13, 2017

I’ve been learning more about effective data visualization lately, and recently was in a wonderful webinar on Data Dashboards (with Ann Emery — whose blog has great posts about data viz, such as using color, and telling stories with data).  It was a wonderfully information-packed session, and I’d recommend it to anybody!  I have a lot to learn in this area, but find it fascinating — particularly since I learn a lot about human psychology and information processing along the way.

What is a Data Dashboard?

It’s a single visual aggregation of all the important information for a project or objective, in one compact display.  For example, you might want to compare all the responses on a series of questions in one display, or show how this year compared to last year on a series of measures, or show progress towards project goals, or show a series of line graphs.  Or, more likely, you may want to do a combination of these things.  First, consider your audience — what information do they need to compare?  What will tell the story that they need to think about?  Dashboards are just one communication tool among many; most useful for pulling together comparisons — across sites, individuals, or time.

In this post I’ll briefly go over some of the types of dashboards I learned about, and how to make some of them.  You really have to just go through it step by step to become familiar with it.  Once you start playing with these, you can get some great ideas.

Types of Dashboards

For more ideas about different types of data displays, see Ann Emery’s “Choose the right chart” posts.  Some other types of visuals she showed us were tracking progress towards goals, and combinations of the chart types below.  All of these are basically add-ons to an existing (nicely organized) table of data values.

1. Comparisons with mini bar charts

This could be used for giving a snapshot of how different sites are performing on a set of measures, for example.  Using Conditional Formatting in Excel, (the “data bars” option), and then being clever with formatting to make it look nice, you can make some nice little bar charts to accompany your data table.  In some cases, you may find it is easier to visually parse this information by using a “heat map” — where the percentage is shown by shading the bar to different degrees.

2.  Comparisons with deviation bar charts

If you want to compare two points in time, or compare one instance to the previous average, deviation bar charts can be really useful.  You could compare Year 1 to Year 4, or Starting to Ending.  This is also done using Conditional Formatting (“data bars”):  The “deviations” are shown as bars that show increases/decreases (e.g., comparing Year 1 to 4)

3. Two or more points in time:  Mini lines

This is a pretty common style, according to Ann, and is pretty easy, allowing you to quickly visualize trends over time.  This is done in Excel using “Sparklines.”

So, how do you do all this?

In the webinar, Ann walked us through doing this in Excel, which was very helpful.  Here are the basic steps.

  1. Create your data table in Excel, and declutter it.  For example, put all your variables in columns (e.g., “Question 1, Question 2) and your groups to compare (e.g., “Workshop 1, Workshop 2”) in the rows.  Give them short names, and organize it into any categories (e.g., “Attitude questions”, “Content questions”) that you want to compare.  Separate those categories with white space.  This isn’t just the data; this will become your dashboard, so make it look nice.
  2. Add your visual.  Detail on creating the visuals above is outlined.  Once you make one, you can copy to your additional rows of data.
    1. To create sparklines (to track data over time):  Highlight the row of data indicating trends over time.  Choose “sparklines.”  You may want to choose to use “Markers” First/last, to make the first and last data point particularly visible.
    2. To make mini bar-charts (to compare groups on similar questions):  Add a blank column between your columns of data.  Using the “=” function in this new column, create a “twin” column of data (i.e., this “twin” column is a copy of your original data).  Use Conditional Formatting on this twin column, choosing “data bars.”  To make it look nice, as in the example, edit the “rule” for that data bar to show the bar only, and choose minimum 0 and maximum 1 (thus the bar will stretch to the max).  To create a grey shading in the background (to make it clearer how far you are from 100%), use the Shading command.  Then outline each bar in white to make it stand out, by using internal and external borders (“more borders”) at the thickest setting.  You can separate the bars from one another by inserting a small blank column.
    3. To make heat maps (to compare groups on similar questions using saturation to indicate higher levels instead of the length of the bar).  As above, but choose Conditional Formatting, and color scales.  It’s best to outline them in white using borders, and try the green/white palette to start.
    4. To make deviation bar charts.  As above (Data Bars), but go into Edit Rule and choose Maximum (Number: 1) and Minimum (Number: 1), filling positive values in blue and negative in red.
  3. Write text to orient your user.  At the top of the dashboard, describe the dashboard. Define any variables. Put the year to the top right.
  4. Use clear text hierarchy.  You want the title to be large, and subtitles to be bold.  What do you want your user to look at first?  What is the most important?  Guide their eye.
  5. Brand the dashboard.  Use fonts and colors which reflect the client.  You can use colors to highlight categories nicely (e.g. the “Listserv” category may use red font, and then red sparklines). Use to avoid colors that are difficult to distinguish for color blind users (such as red and green:  Green and orange or blue and red are fine).  Add a logo.
  6. Print it.  Select the portion of the dashboard you want to print (you may have some notes or other elements off-screen), and choose Print to PDF.  You may wish to play with the size of rows and columns to make it fit nicely to a page. You may wish to change it to Landscape mode too.   Note that if you select all columns and then adjust the size of them, it will adjust the width of all columns in tandem: this is a good trick for ensuring equally sized columns (or rows).

I ended up using these to create a combination dashboard, which used bar charts to show the average question rating, sparklines to show the rating over time, and deviation bar charts to show how this year’s workshop compared to the historical average.  I really like it!  Below is the dashboard that I created for one of my clients.







I’m pleased to announce the launch of our new Physics Consultants Directory on   Here you can list yourself as a consultant, or find consultants to help with a variety of projects.  We are trying to populate the directory intensively by August 17th, so please try to list yourself by then (though the site will be available for ongoing submissions).  And yes, you can list yourself if your expertise isn’t strictly physics, as long as you can work in the physics realm.

Many thanks to my collaborator Sam McKagan (director of PhysPort), my husband Terry Ollila (who did the custom design for the consultant form), and Lyle Barbato (who worked web magic to integrate this into PhysPort).  This project is supported by a mini grant from the Physics Education Research Topical Group of the American Association of Physics Teachers.

Should you consult?

You might be wondering, is consulting for me?  Is this something I can do?  Should do?  Would be good at? How do I figure out who I would work for?  Many of these things are difficult to figure out in advance, which is why it can be great to jump in and do a few small consulting projects (often as a subcontractor, or working for someone you know), to get a taste for the work and if you like this style of working.  That’s what I did, creating a podcast for the NSDL as my first project, then doing external evaluation, which I originally hated.  My mission statement and sense of purpose has changed over time, and now I make most of my living as a consultant, conducting external evaluation for projects aimed at spreading educational reform.  It was not a linear path and yours may not be either.  Many people consult on an occasional basis, and that’s just fine too.

One of my go-to resources which is highly valuable for researchers like us trying to figure this out is Consulting Start-Up and Management: A Guide for Evaluators and Applied Researchers. This nuts-and-bolts guide talks new consultants through figuring out their value, whether they’re up for throwing themselves into this business, and discusses practical matters such as finding clients, managing time, and setting rates.  Many of the following questions are taken from that book.

Are you the right personality?

Consulting requires time management, organization skills, and an entrepreneurial spirit.  If you are none of those, this might not be right for you.  You may need to finish a project on time and in budget.  You may need to go out and seek clients.  You will need to be easy to work with and honest, so clients will come back.  And since we are academics most working in an academic environment, you want to cater to that culture; many academics are leery of marketing or business strategies that would work well in the private sector.

My story: I was told by one of my clients that the projects I work on are the ones she knows will get done.  I think another reason people hire me is that I strive for efficiency, and think carefully about whether what I’m giving to a client will really help them do their project better.  This takes a lot of time — I spend many unbillable hours on professional development, and making sure to organize my many projects and timelines. Some questions to ask yourself:

  • Am I good at multitasking and dealing with competing demands on my time?
  • Am I resilient?
  • Am I self-motivated?
  • Can I work efficiently and meet deadlines?
  • Do people like working with me?
  • Do I like being my own boss?

Do you have something to sell?

This is usually the hardest question for people.  What services would you sell? What are you good at?  This requires looking at your experience from the point of view of a client.  What value-added do you offer?  Why would someone hire you?  A common resume-writing mistake is to write a resume in terms of your experience, rather than your skills, thus making your employer “work” to figure out how your experience can help them. The same is true of consulting.  Spend some deep time thinking about what kinds of projects you might work on, and how your expertise supports that.  You can get some help by looking at the categories already pre-listed on our consultant directory.You can also talk to potential users of your services in mini-interviews to help target your work.

My story: For me, I thought I would mostly provide services in writing for teachers and conducting workshops, due to my journalism and professional development background, but found that the market wasn’t strong enough for this — and so turned to external evaluation.  I still write (and do research) in this job, but for a very distinct audience. You can also talk to potential users of your services in mini-interviews to help target your work.   Some questions to ask yourself:

  • What skills am I selling?  Is there a market for them?
  • What am I good at?
  • Are there ways in which I offer unique combinations of skills?  (For example, I have education research, and public communication expertise)
  • Who is my target market?  What are their problems and how can I help them solve them?  (Your market may be departments, faculty, project leaders, or even other consultants, like me, looking to subcontract work)
  • How can I bolster my own skills by joining forces with others or expanding my skillset?
  • Can I prepare a one-page description of my proposed services and the types of clients I might serve?

Where can I find more information?

Some other books that have been recommended for getting started:

  1. Consulting And Evaluation With Nonprofit And Community-Based Organizations
  2. Million Dollar Consulting: The Professional’s Guide to Growing a Practice, Fifth Edition (Business Books)
  3. Escape From Cubicle Nation: From Corporate Prisoner to Thriving Entrepreneur
  4. Book Yourself Solid: The Fastest, Easiest, and Most Reliable System for Getting More Clients Than You Can Handle Even if You Hate Marketing and Selling
  5. Working for Yourself: Law & Taxes for Independent Contractors, Freelancers & Gig Workers of All Types

Useful resources to explore:

And of course, don’t forget to list yourself on the  Physics Consultants Directory!

Please add questions and additional ideas in the comments, and we can use this to help share our experiences as consultants.

{ Comments on this entry are closed }

My learning goal and clicker workshops all online

August 3, 2017

Giving workshops on the use of clickers / peer instruction, or learning goals?  I wanted to let you all know that my workshop materials for both topics are all compiled and archived online on our SEI Workshop Page.  There are also videos of several of my workshops (though a few years old, they still show […]

Read the full article →

Defining excellence in physics teacher preparation programs: The PTEPA (#AAPTSm17)

July 26, 2017

A big challenge in physics is preparing adequate numbers of well-prepared future physics teachers.  There is a huge dearth of qualified physics teachers at the high school level, and some physics departments have taken it upon themselves to try to address this gap.  Some are very successful.  How do they do it? I’ve been working […]

Read the full article →

Phys21: Preparing students for diverse careers (#AAPTSM17)

July 26, 2017

I just gave an invited talk at AAPT about my work for the Phys21 Report:  Preparing Students for 21st Century Careers.  I was commissioned by the JTUPP committee to create case studies of how institutions achieved success for diverse students.  This was my favorite project last year, it was completely inspiring to talk about what […]

Read the full article →

Improving the bottom quartile with a metacognitive exercise (#AAPTSM17)

July 25, 2017

I’m in an inspiring session by Charles Atwood (University of Utah) about how they improved the performance of at-risk students in introductory chemistry at the University of Utah. Abstract: To improve success rates in large general chemistry sections at the University of Utah, we realized we must improve the bottom two student quartiles performance. We […]

Read the full article →

Mutual mentoring (liveblogging from #AAPTsm17)

July 25, 2017

I’m now attending a session on Mutual Mentoring for physics faculty, presented by Anne Cox (Eckerd College). Abstract: We were part of an NSF ADVANCE grant mutual mentoring project for senior women faculty in chemistry and physics that began in 2007. We have continued our bi-monthly mentoring meetings for the past 10 years (well beyond the […]

Read the full article →

Promoting innovation and entrepreneurship in physics (liveblogging from #AAPTSm17)

July 24, 2017

I’m at the American Association of Physics Teachers meeting this week, and will blog about a few sessions while I’m here. In a talk by Crystal Bailey (American Physical Society), she argued that we need to more explicitly teach Physics Innovation and Entrepreneurship (PIE) to our students.  I find this a really valuable message; having […]

Read the full article →

Educational change: How systemic thinking helps to push social progress

July 5, 2017

In today’s post I want to share some ponderous thoughts about how educational reforms happen, and how systemic thinking helps to support those reforms.  I am fortunate to be a working group leader in the Accelerating Systemic Change Network (ASCN;, and one of the working groups focuses on how theories and models of change can […]

Read the full article →

Changing how universities teach science: The SEI Model

June 21, 2017

We know a lot about how to improve STEM teaching and learning at the college level, and yet these improvements have yet to take hold in a widespread manner.  This is the perennial problem which many of us in STEM education are wrestling with.  The study of institutional change is expanding ever more, including lessons […]

Read the full article →

Data visualization tips

June 14, 2017

Are you trying to tell a story with your data?  This is a big part of my job (as an external evaluator), and I recently attended an excellent webinar on data visualization.  Now, I hate webinars that are trying to sell me a book, but this one was so packed full of great ideas that […]

Read the full article →

You can now embed PhET into Powerpoint!

June 2, 2017

If you’re a PhET user, you’ll be interested in this one.  PhET has a new application that allows you to directly embed the simulations into your Powerpoint.  No more switching back and forth between Powerpoint and the simulation, or awkward pauses while you drag the simulation to your projection screen. Just install their free PhET […]

Read the full article →

A great new book! Teaching and Learning STEM by Felder and Brent

June 2, 2017

I was pleased to be invited to write a review for Physics Today on a new book, Teaching and Learning STEM by Felder and Brent.  I loved this book!  I found it utterly charming, useful, kind, and knowledgable.  I highly recommend it, and am going to be purchasing several copies to be able to give to […]

Read the full article →

Outdoor activities for kids: Big Book of Nature Activities

January 15, 2017

If you’re feeling a little stuck indoors with your kids, here is a resource to get your kids (or students) learning from the outdoors even through the colder months.  Last year I picked up a copy of The Big Book of Nature Activities: A Year-Round Guide to Outdoor Learning.  I found it to be a great […]

Read the full article →

Writing a proposal on institutional change? Here are some great resources!

December 21, 2016

This is a repost of my blog post at the ASCN (Accelerating Systemic Change Network) blog.  Check out and subscribe to their blog for more posts like this! For many of us, it’s proposal writing season. If you are submitting an NSF-IUSE proposal, there are increasing expectations that the proposal will include a theory of […]

Read the full article →

Need to train postdocs or instructors to support teaching improvements?

November 15, 2016

This summer, Carl Wieman will host a workshop at Stanford to train STEM disciplinary experts to become educational specialists working in departments, in a short, intensive version of the postdoc training developed at CU Boulder’s Science Education Initiative and the sister CWSEI at University of British Columbia.  If you need to get someone up to […]

Read the full article →

Theories of Change

November 4, 2016

I have been wanting to blog more, but have been having trouble finding as many “transportable” ideas to share publicly, now that my work is more focused on broader, institutional change.  But one thing that I have been working a lot with, which is becoming more broadly recognized as useful in educational projects, are Theories of […]

Read the full article →

I am such a good physicist….

October 28, 2016

Check it out and you’ll see why I’m posting this video… You have to be patient for about 3 minutes, and you’ll see why I’m posting this.  And no, I had never heard of this guy before!  What a kick!

Read the full article →

“Teacher primers” on physics PhET sims published!

August 5, 2016

Over the past several years, I’ve been working with the PhET Interactive Simulations to determine how best to do video “walk-throughs” of their simulations for teachers, showing the main features and how they can be used in a teaching setting.  We call these short videos “teacher primers,” and I’ve got a set of them that […]

Read the full article →

Faculty perceptions around research based assessment (#AAPTsm16)

July 19, 2016

(liveblogging from the AAPT).  Adrian Madsen shared some work to identify faculty ideas and beliefs around research based assessments in physics, such as concept inventories (think FCI) or non-content instruments (e.g., CLASS).  This work is part of a project by to collect research-based assessments on a website, to provide a more coherent portal to […]

Read the full article →