Pinterest Stumbleupon Whatsapp
Ads by Google

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 How To Create A Graph From Raw Data Files In Any Office Product How To Create A Graph From Raw Data Files In Any Office Product Wouldn't it be nice to have the ability to just open up an Excel spreadsheet or a Word document, and without having to do anything at all, data gets read directly out of a text... Read More , 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.

data-analysis1

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.

Ads by Google

data-analysis2

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 Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Sometimes, a simple spreadsheet format isn't engaging enough to make your data accessible. A dashboard allows you to present your most important data in an easy-to-digest format. Read More .

Column Charts

One of the most common charts used in presentations 10 Powerpoint Tips for Preparing a Professional Presentation 10 Powerpoint Tips for Preparing a Professional Presentation Read More and dashboards Leftronic: Create Dashboard To Monitor Stats & Trends From Various Web Services In One Place Leftronic: Create Dashboard To Monitor Stats & Trends From Various Web Services In One Place Read More , 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.

chart-types1

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.

chart-types2

Personally, I prefer the look of 3-D charts PowerPoint Art: How To Create A 3-D Circle To Show A Cyclical Process PowerPoint Art: How To Create A 3-D Circle To Show A Cyclical Process With each improved edition of PowerPoint, presenters have got better tools to play around with. So much so, that you don’t need a full-blown graphics editor to work magic on your slides. You can do... Read More . Here’s what the data above looks like using 3-D bar charts.

chart-types4

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.

chart-types5

Now you’ll see a vertical label that you can edit.

chart-types6

As you can see, using a column chart to visualize Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Sometimes, a simple spreadsheet format isn't engaging enough to make your data accessible. A dashboard allows you to present your most important data in an easy-to-digest format. Read More 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.

chart-types7

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.

chart-types8

Voilà! You now have a bar chart comparison of reported problems by renters for geographic regions across the U.S.

chart-types9

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.

Pie Charts

As the name implies, pie charts PieColor: Online Pie Chart Maker PieColor: Online Pie Chart Maker Read More 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.

chart-types10

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.

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

chart-types12

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.

chart-types13

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.

chart-types14

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

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.

chart-types15

You could also represent the same data using a stacked area chart (also available in the charts section under the Insert menu).

chart-types16

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 Online Dating - Men Don't Get It And Women Don't Understand Online Dating - Men Don't Get It And Women Don't Understand Do online dating websites work? It's time for a frank discussion! What I learned from interviews was that online dating is equally painful for men and for women, but for very different reasons. Read More 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.

chart-types18

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.

Bubble Chart

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.

chart-types22

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.

Surface Charts

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.

chart-types20

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

Doughnut charts are another complex visualization 4 Data Visualization Tools For Captivating Data Journalism 4 Data Visualization Tools For Captivating Data Journalism Words and numbers are not enough. In the end, visuals will capture and convince your audience. Here are the tools that will help you turn your data into something great. Read More 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.

chart-types21

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 5 Powerpoint Tips to Improve Your Presentation Skills Overnight 5 Powerpoint Tips to Improve Your Presentation Skills Overnight You step up to the podium. Your palms are sweaty, your heart is racing, and just as you're about to open your mouth and speak, your mind goes blank. Need help? Read More . 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.

  1. Kathleen Wilson-Goode
    July 7, 2016 at 1:05 am

    Thank you!

  2. Anchit
    June 15, 2016 at 5:48 am

    for relation?

  3. Hitesh Gondalia
    October 15, 2015 at 9:51 am

    awesome..

  4. Matthew Drury
    August 12, 2015 at 2:03 am

    Basically every visualization expert condemns the use of 3D charts to visualize non 3D data. The advice to use them is horribly misleading, and greatly favors style over content. Know that as soon as I see a 3D bar/pie/polygon chart in a presentation, it makes me very hard to take the presenter seriously.

  5. Andy Cotgreave
    August 11, 2015 at 3:41 pm

    Hi Ryan
    Congrats for writing an in-depth post promoting data visualisation: we need more people to do this and to make seeing data a completely normal experience for everyone.

    I really recommend you go take a look at a couple of books about the science behind data visualisation. Why? The charts you promote in this post do not aid understanding and actually confuse the eye, rather than help it. For example, 3d pie charts distort rather than display data.

    I'd recommend any of the books by Stephen Few as a start. Now You See It or Show Me The Numbers are ideal. http://www.perceptualedge.com/library.php

    Then read Alberto Cairo's The Functional Art which is packed full of ways to make pretty charts which are also functional. http://www.thefunctionalart.com/

    Good luck!
    Andy

    • Mihir Patkar
      August 17, 2015 at 2:39 pm

      What a lovely reply, thanks for commenting Andy!

Leave a Reply

Your email address will not be published. Required fields are marked *