You’ve got a presentation due in a few days and you really want to impress the boss. You want to show your data in a way that’s easy to understand, but also visual and impressive. The only way to do that is to choose the right chart for your data.
You can draw on many different tools for creating charts and graphs, but Microsoft Excel remains one of the most powerful and functional of all of them. It allows you to visualize data in whatever format and style you want, as you’ll see below.
In this article, you’ll learn about the many types of charts available to you in Microsoft Excel using examples from publicly available data provided by data.gov. The data set is drawn from the 2010 U.S. Census, and will be used to show you how impressive it is when you choose the right chart for your data.
Creating the Right Charts
The U.S. Government provides volumes of data to the public for absolutely free. One of the most valuable pieces of information for digging through cultural and social facts is the Census.
For the first few examples below, I’ve pulled problems that renters have had with their dwellings, split up by the four regions of the United States.
This is a nice, small subset of data that allows for simple charting.
In order to decide what chart to use, you need to take a close look at what the data represents, and how you want to visualize that data.
One of the most common charts used in presentations and dashboards, column charts are meant to compare values to each other. Usually these are values that have been categorized in some way. The most common subset for a column chart is one set of data broken up into categories.
In this case, I’ve chosen to break down the number of problems renters have in the Northeast United States. Once you’ve selected your data, just click Insert, and you’ll see a choice of chart types in the menu. In this case, you can go with 2-D or 3-D column.
Personally, I prefer the look of 3-D charts. Here’s what the data above looks like using 3-D bar charts.
Unfortunately, charts default without any vertical or horizontal labels, so you’ll need to add those so people know what the numbers mean. Doing that is easy enough. Just click the “+” button to the upper right corner of the chart, make sure Axis Titles is selected, and then only select Primary Vertical.
Now you’ll see a vertical label that you can edit.
As you can see, using a column chart to visualize categorized data makes the “problems” pop right out at you. Obviously people in the Northeast have major plumbing and heating problems!
But how bad is that compared to the rest of the country? To show this, let’s add more categorized data, but use bar charts instead. Bar charts are the same as column charts, but the categories appear horizontally rather than vertically.
This time, highlight all the data.
Now click Insert in the menu just like you did before, and this time click on the dropdown for bar charts, just to the right of the column charts dropdown.
Voilà! You now have a bar chart comparison of reported problems by renters for geographic regions across the U.S.
Pro tip: I had to edit the title as shown. You do this simply by clicking on the title itself and typing a new title, at least in Office 365 – in other versions you may need to click Advanced Edit.
As you can see, column charts and bar charts are a really cool way to either compare categorized data for one data set, or compare categorized data across multiple data sets. For example, the regional chart above shows quite clearly that plumbing issues are much worse in the South and Northeast, while the South struggles a lot more than anyone else with property upkeep.
As the name implies, pie charts are shaped like a pie, and are best used when you need to show the amount of a much larger category that’s taken up by smaller sub-categories.
For example, a University may use a pie chart to show a breakdown of the racial demographics of its student population. Using our data above, you might use a pie chart to breakdown Northeast renter problems by repair issue.
Again, this is another good chart to use when you are starting out with already categorized data. Pie charts are best used for one data set that’s broken down into categories. If you want to compare multiple data sets, it’s best to stick with bar or column charts.
Moving on to line charts requires a new set of data because line charts and other similar chart types tend to be time-dependent. This means, you are usually (but not always) charting a data point over the progression of time.
This isn’t always the case. Ultimately, for a line chart you only need an X and a Y value. In the example below, X will be time and Y will be population, but you could just as easily chart the productivity of your company (Y) as the number of bonuses paid goes up (X).
The census data set we’ll use in this example is the change in population of the United States from 2010 through 2015.
Highlighting the year and the total population columns, and then clicking on Insert from the menu and choosing a line chart graph results in a line chart showing up in your spreadsheet. Right click on the chart and choose Select Data Source. Make sure the Year is unselected.
Now that it’s not trying to use the Year column as Y, it’ll use it as X and correctly label your horizontal axis.
At just a glance, you can see that the population of the U.S. has been steadily increasing at a rate of about 0.76% per year. This is the kind of thing line charts are made to show.
Area charts are identical to line charts, but the area under the line is filled in. While the focus of the line chart is still change in values over time, the focus of an area chart is to highlight the magnitude of values over time. The difference is subtle, but when you need to show things like how many trees have been clear cut from 1990 to 1995 versus 2000 to 2005, the area chart really shines.
Using the population data above, you can compare the male versus female population growth from 2010 through 2015.
You could also represent the same data using a stacked area chart (also available in the charts section under the Insert menu).
This chart can be thought of as a line graph and a pie chart combined into one. You can see the percentage breakdown of the categorized data as that data changes over time.
Since the change is so subtle over the 5 years plotted above, you can’t quite make out the fact that while the female population is about 3% larger than the male population, the male population is actually increasing approximately 3% per year faster than the population of women in the U.S. As though dating wasn’t hard enough for guys as it is!
Scatter (XY) Charts
A favorite among scientists and statisticians, scatter charts are plotted data points (usually a cluster of similarly measured data points), that are intended to show correlations or patterns in the data that aren’t obvious when you’re just looking at individual data points.
For example, plotting Cancer recovery success rates vs time spent in the hospital may show a correlation between how long someone is treated for Cancer and how successful the treatment is likely to be.
To show the power of this chart type, I’ve plotted the number of asthma hospitalizations in Chicago from 2000 through 2011, organized by zip code.
This X-Y scatter chart (also called a “cluster” chart) reveals that zip code 60628 had more asthma hospitalizations per year than just about every other region, and zip codes 60655 and over make up the best areas to live if you want the least possible odds of having an asthma hospitalization.
This can also be represented (even better) in the form of a bubble chart. This is a fun visualization to create when you have three dimensions of data you can plot.
This is a typical XY plotted data point of two values (the point being a “bubble”), with a combined additional value that determines the size of that bubble. For example, with this kind of chart, you could plot how many teens go to the movies, but the size of each data point (bubble) may represent how many females make up that data set.
It’s a really interesting way to represent two sets of data with one chart. In the example above, if you included the name of the movies as the X-axis labels, you could identify that of all of the movies that teens went to, which specific movies attracted a larger teen female audience.
Just when you thought there couldn’t possibly be more charts available, here come surface charts. This three dimensional chart lets you plot two series of data points across several dimensions. It can be complex to use, but with the right data points (two series with a clear relationship), the visualization can be quite impressive.
The 3-D version of the chart looks like a topographic map, which makes sense because such a map is a plot of altitude and distance from any given point. Similarly, you can use this map to do things like chart the magnitude of a disease outbreak against age and time the person has been sick.
By plotting in this way, interesting patterns might arise that you’d never notice, if you only plotted two points along a simple X-Y axis.
Doughnut charts are another complex visualization that lets you graph one data series in a sort of pie chart format, but also additional data series in “layers”, forming a multicolored “doughnut”. This is best used when the two data series are subcategories of a larger category of data.
A good example of this is the data sets used earlier in this article, plotting male and female population per year.
As you can see, every series (the years 2010 through 2015) contains nearly identical-sized segments, showing just how slowly both populations change from year to year. The value of this graph really comes from having the right data and the right goal that you want to achieve visually with that data.
Choosing the Right Graph
That is really the point of all of this. There may be a nice selection of graphs to use, but what you should choose really boils down to the type of data sets you have available, and exactly what it is that you’re trying to show (or prove) with that data. Some charts will make your point much more clearly than others.
Knowing what chart will accomplish that is half the battle. The other half is figuring out exactly how to arrange your data and format the chart how you like.
The truth is that graphs make a huge impact in any presentation. Now that you know everything that’s available to you in Microsoft Excel, choose the right graph for your needs, and create a presentation with it that’ll really knock their socks off.