Learn

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).

A table in Excel with four columns: year, car , car SUV, and pickup truck. The rows are labeled with years 2010 through 2015. The values are average miles per gallon.

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!

The same MPG table. The car column is shaded green, starting light in 2010 and getting dark by 2015. The Car SUV column is shaded yellow, getting slightly lighter over time, and the pickup truck is shaded red, getting slightly lighter over time.

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:

The same MPG dataset. Now, every cell is green if it is larger than 26 and red if it is smaller than 26

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?

Our Answer We can see that cars are most efficient, with almost all years having "good" mpg since 1990. The first "good" year for car SUVs is in 2010, followed by truck SUVs in 2018, and vans in 2020. Pickup trucks have yet to reach a "good" mpg as of 2020!

Take this course for free

Mini Info Outline Icon
By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.

Or sign up using:

Already have an account?