Visualizing Data

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](https://www.bts.gov/content/productions-production-shares-and-production-weighted-fuel-economies-new-domestic-and)).

![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.](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/visualizing-data/ex2_slides/assets/ex2_a.png)

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.](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/visualizing-data/ex2_slides/assets/ex2_d.png)

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](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/visualizing-data/ex2_slides/assets/ex2_i.png)

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.



Conditional Formatting

What if you aren't sure which chart type is best for your data? After all, different types of graphs can bring out different aspects of the data. Conveniently, Excel provides a quick and easy way to preview various types of charts with its Recommended Charts functionality! 

**Note**: Just because Excel recommends or doesn't recommend a chart doesn't mean that chart should or shouldn't be used. Make sure to use your own discretion to see what works well for your specific case!

We've placed a slideshow in the learning environment illustrating how to view recommended charts and demonstrating that some recommended charts are not very useful!

Recommended Charts

In this lesson, you've learned how to use

- conditional formatting for visualizing tabular data
- column charts and pie charts for visualizing categorical data
- histograms and scatterplots for visualizing numeric data
- line charts and sparklines for identify trends over time

You've also learned how to

- select the right type of chart for your data
- interpret the results of each type of chart
- customize the charts with titles and axis labels

Congratulations!

An Excel interface surrounded by illustrative tables of data, charts, and formulas.

Let's Review

The last two visualizations we'll look at are charts that help us see the change in a numeric column over time.

For this exercise, we'll look at a dataset containing data on the total production (thousands of vehicles) for different types of vehicles over time (from the [Bureau of Transportation Statistics](https://www.bts.gov/content/productions-production-shares-and-production-weighted-fuel-economies-new-domestic-and)). Here are the first few columns of this dataset:

![A table in Excel. There are columns corresponding to the years 2010 through 2013, and rows corresponding to the different vehicle types.](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/visualizing-data/lines/assets/ex5_a.png)

#### Line charts

Line charts plot points in the data connected with a line to visualize the trend. Multiple lines can be plotted on the same graph to compare trends between groups. For example, here is a line chart of the production numbers above:

![A chart in Excel titled Total productions by vehicle type. The horizontal axis lists the years 2010 through 2013. The vertical axis lists numbers in thousands from 0 to 9 thousand. Each vehicle type is associated with a different color line, and a line is drawn for each vehicle type with the height of the line indicating the size of production in that year.](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/visualizing-data/ex5_slides/assets/ex5_p1.png)

It looks like most vehicle types increased production over these four years, but Vans stayed fairly constant in comparison to the rest.

#### Sparklines

Sparklines are a feature in Excel that places a trend line (like a mini line chart) next to each row of a table. For example, here is what the sparklines would look like for our vehicle production data:

![The original vehicle production table. Next to the 2013 column is a column of trendlines. The lines go up when the numbers in that row increase, and down when the numbers in that row decrease.](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/visualizing-data/ex5_slides/assets/ex5_o2.png)

Sparklines can be helpful when we want to see general trends for individual categories. In the line chart, the fluctuations in Van production weren't visible, since they were so small compared to the fluctuations for cars. In the sparkline, we can see them much more clearly.

We've placed a slideshow illustrating both methods in the learning environment. When you're ready to practice this yourself, move on to the next set of instructions!

Line Charts and Sparklines

One of the most effective ways to identify and communicate trends in data is through **data visualization**. In Excel, we can create a wide variety of visualizations in the same sheets we've been using for data storage and analysis. In this lesson, you'll learn to visualize data by

- using **conditional formatting** to visually explore the values in tabular data
- creating **column charts** and **pie charts** to compare the sizes of categories
- creating **histograms** and **scatterplots** to visualize numeric data
- creating **line charts** and **sparklines** to identify trends in data
- using Excel's **recommended chart type** feature to easily explore different possible visualizations 

Each exercise will come with an Excel spreadsheet for you to download. The spreadsheet will give you instructions to practice what you've learned, explore customization options, and check your answers.


Introduction to Visualizing Data

A common task in data visualization is to compare the sizes of categories. For example, in our vehicle data we might want to compare how many cars are being produced as opposed to SUVs or trucks. **Column charts** and **pie charts** are simple ways to visualize categorical data and make comparisons.

For this exercise, we'll look at a dataset containing data on the production shares (%) for different types of vehicles in 2020 (from the [Bureau of Transportation Statistics](https://www.bts.gov/content/productions-production-shares-and-production-weighted-fuel-economies-new-domestic-and)).

![A table in Excel. There are two columns: vehicle type and percent. The vehicle types are: Car, 30.9%; Car SUV, 13%; Pickup truck, 14.4%; Van, 2.9%; Truck SUV, 38.7%](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/visualizing-data/ex3_slides/assets/ex3_a.png)

#### Column charts

In column charts, each category is represented by a column in the plot and we can compare them by assessing the length of the columns or bars. Note that these are commonly also called bar charts, but the tool in Excel calls them column charts.

![A chart titled Production share by vehicle type. Horizontally, there are columns labeled car, car suv, pickup truck, van, truck SUV. The heights of the columns are measured by a vertical axis going from 0 to 45 in steps of 5. The column heights are in order truck SUV, car, car SUV, pickup truck, van, truck SUV](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/visualizing-data/ex3_slides/assets/ex3_h.png)

### Pie Charts

In pie charts, each category is viewed as a "slice"  or `sector` of a pie, with the size (or area) of the slice corresponding to the relative size of that category. Because the categories as slices make up the whole pie, pie charts should *only* be used to visualize categories that are pieces of some whole. In other words, the area of the sectors (slices) should add up to 100%. 

In our dataset, the different vehicle categories are all part of the same whole: the collection of all vehicles in 2020. 

![A pie chart that pictures each category as a slice of pie, with the size of the slice relative to how big that category is.](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/visualizing-data/ex3_slides/assets/ex3_k.png)

Let's look at a related dataset that might not be very suitable for pie charts. This table shows the production share of cars from 2015 to 2020:

![A table in Excel with Year and Percent columns. The Year column goes from 2015 to 2020. The percent values are: 47, 43, 41, 36, 32, 30.](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/visualizing-data/ex3_slides/assets/ex3_p.png)

While we can certainly plot a pie chart, it would be misleading, because these percents are not pieces of the same whole. They are each "slices" of their own year, and shouldn't be placed together in a pie. Make sure to evaluate your data to see if a pie chart is a right choice for visualization! 

We've placed a slideshow illustrating how to create both types of charts in the learning environment. When you're ready to practice this yourself, move on to the next set of instructions!



Column and Pie Charts

Column charts and pie charts are excellent for comparing the sizes of categories. But they won't help us understand more general numeric columns. For example, suppose we wanted to use the following table of [housing price data](https://www.kaggle.com/datasets/camnugent/california-housing-prices) (in thousands of US dollars) to analyze the impact of wealth inequality on housing.

![A table in Excel with Income and Housing Price columns. The Income column has values roughly 40, 20, 30, 28, 15, 50, 44, 11, 31. The Housing Price column has values 2489,975,1399,875,518,2320,1907,561,3667.](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/visualizing-data/ex4_slides/assets/ex4_a.png)

We'll look at two methods for visualizing numeric data in this exercise: histograms and scatterplots. We'll use histograms to visualize one column at a time, and scatterplots to understand the connection between the two columns.

#### Histogram

Histograms are used to understand the "shape" of a single column of numeric data. A histogram breaks the data-points into **bins** and then counts the number of data points in each bin.

To better understand what a histogram shows, let's visualize the median household income data for just the first 9 rows of data shown above:

![A chart in Excel. The horizontal axis has broken the range from 11.8 to 50.1 into four equal pieces, and above each piece there is a column representing the number of datapoints in that piece of the range.](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/visualizing-data/ex4_slides/assets/ex4_j.png)

The smallest and largest household incomes are 11.8 and 50.1. To create this chart, Excel broke that range into five equal pieces. The range of values for each piece is labelled along the x-axis. The column above each piece represents the number of data points that fall within that range. For example, the largest column has a height of 3, indicating that there are 3 data points in the table between 27.1 and 34.7 (not including 27.1). Check the table above to confirm that Excel got that right!

#### Scatterplots

Scatterplots are used for visualizing **correlation** between two columns. As one column increases, does the other also increase? Decrease? Fluctuate? For example, in our dataset, we might want to know if higher-income households own more expensive houses: as income increases, does housing price also increase? It's important to remember that the existence of a correlation like this does not mean the one column is directly influencing the other.

Here is what the scatterplot looks like using the first 9 rows of data shown above, with household income on the x-axis and housing price on the y-axis:

![](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/visualizing-data/ex4_slides/assets/narrative-scatter.png)

To create this chart, Excel has placed a dot for each row of the table. The dot is positioned horizontally by the income value in that row and vertically by the housing price for that row. For example, the first dot in the bottom left corresponds to the ninth row with an income of 11.8 and housing price of 561. 

We can see a general trend from this plot: as incomes increase (moving to the right in the plot), so do housing prices (moving up in the plot.) We refer to this as a positive correlation when the variables trend in the same direction. If one variable increases while another decreases, this is referred to as a negative correlation.

We've placed a slideshow illustrating how to create these plots in Excel! When you're ready to try it yourself, move on to the next set of instructions.



