Often, we think of data visualization as being all about charts and graphs. In Excel, data visualization starts with tools to help us explore the data tables themselves!
For example, let’s take a look at a table of average gas mileage (mpg) for different types of vehicles over time (from the Bureau of Transportation Statistics).
What if we wanted to see at a glance whether gas mileage was improving over time, or which years met a certain threshold for efficiency? We’ll look at two techniques: color scales and cell rules.
Color scales
In many cases, we want to see which rows and columns of a table have larger or smaller values. For example, we might want to see if vehicles have tended to get more efficient over time or identify the most and least efficient vehicles in the MPG dataset. A way to solve this using data visualization is by creating a heatmap, which colors each cell in the data table depending on its value.
In the heatmap below, for example, the smallest values are colored dark red, the highest values are dark green, and the values in-between are shaded based on where they fall in that spectrum (with yellow in the middle).
The car column is all shades of green, showing that they are more efficient than the other columns (yellow for car SUVs and red for trucks). Within the car column, the green is getting darker over time, indicating that cars have continually improved in average MPG!
We’ve placed a slideshow in the Learning Environment illustrating how to apply a heatmap in Excel using conditional formatting!
Cell rules
Sometimes, we have more specific questions about the values in our data. For example, we might want to see if every value in a table meets a particular threshold or not.
In our dataset, let’s assume that a minimum of 26 mpg is considered “good” gas mileage for a vehicle. We can visualize this by coloring cells that meet this criteria green and all other cells red.
The result would look something like this:
In Excel, we can do this by defining custom rules to conditionally format the cells. The full process is illustrated in the slideshow in the learning environment.
Instructions
When you’re ready to practice what you’ve learned, download and work through our exercise spreadsheet. A couple of important points to keep in mind:
- Unlike formulas and pivot tables, we can’t automatically assess your visualizations. We have placed solutions to each exercise within the same spreadsheet. Feel free to compare your work to ours if you get stuck or to check if your solution is correct. Your visualization may not look identical to ours and that’s okay!
- There are lots of options we haven’t covered — feel free to play around and see how different options work!
- You can always re-download the spreadsheet if you want to start fresh.
Once you’ve finished, think about the next question.
Question: What do you notice about the first “good” years for each vehicle type?