|
07 June 2010
I’m a geek! I like numbers a lot! All types of numbers! Simple data, values, stats, binary, hex…bring it on! I also think formulas and functions are fascinating. I’m also a realist! I know that not everyone feels the same. In fact, some people find numbers boring or befuddling. Enter graphs! Graphs are a palatable way to feed various stats to people who would otherwise scan over your data with a blank stare or yawn their way through your report.
Excel offers a myriad of graph options for all types of data and situations. In this quick tutorial, we will cover the 3 basic graph types: Column or Bar (histograms), Pie Chart and Line Chart.
Knowing which type of graph to use with your data is half the battle. Column or Bar graphs work well when comparing 2 or more values side by side. This applies whether you are presenting a single sample or several. For example, consider the following graph which compares various Internet Browser speeds (from lifehacker.com). The graph demonstrates how each browser compares, or stacks up, to others.
A Pie Chart, on the other hand, doesn’t compare individual values but rather presents parts of a whole. If we follow our Internet Browser example, rather than show individual browser performance measurements and how they compare, if we want to show what percentage of the entire browser market each browser commands, we would use a Pie Chart! (from w3counters.com)
Line graphs are used to compare values AND present trending patterns over time. By trending, I simply mean whether a particular item is moving up or down. If you think of stock values or earnings reports, these are generally presented with Line graphs. This example from the 2007 Dow Jones Index kind of reminds me of my last stress test! (from stockcharts.com)
Ok! So now that we know when to use which type of graph, let’s have a look at HOW to create them in Microsoft Excel. As stated above, Excel offers tons of graph types and options from 2D/3D, Stacked or Clustered, various shapes and colors, and more.
If you want to create or use your own sample data, feel free. If you’d rather use the worksheets I’ve prepared for this tutorial, you can download the Excel file using the link at the end of this article.
If you are using my Excel workbook, you’ll note that I have 3 worksheets prepared for the 3 graph types we are discussing here.
The Columns worksheet has a simple table that shows 4 teams (North, South, East and West) and their scores over 4 games (Game 1 through 4). Although the scores have been totaled by team and by game, we won’t be using totals in our column graph.
To create your column graph, select the entire table (excluding the totals column and row)…or cells A1:E5.
From the Ribbon menu, select “Insert”. From the Insert menu, select “Column” from the “Charts” section. A fly out appears from which you can choose the type of Column graph you wish to use.
Your graph appears. You can resize it by using the sizing handles in the corners and sides. You can also modify any element of your graph by right-clicking it. Modifications can include font size, style, labels, colors, legend and more.
Moving onto Pie Charts (select the Pie Chart worksheet by clicking it’s tab), in this case ALL we want are the item titles (or labels) and totals…none of the data in between. Here’s our table:
To select non-contiguous cell ranges we simply hold down the Ctrl key while making our selections with the mouse. In this case, we want to select the Expenses and Total columns (A1:A8 and N1:N8).
Again from the Ribbon menu, select “Insert”. From the Insert menu, click the “Pie” button and select the chart type you wish to use from the fly out menu.
Again, you can modify any element of this Pie Chart by right-clicking on it. Also, you can add segment labels (percentage or actual value), again by right clicking.
Finally, we move to the Line worksheet. Here’s our table:
For Line charts as with Column charts, we want all the data excluding totals. In this case, you can select the entire table (A1:M4).
From the menu, “Insert>Line” and select the Line chart type you want.
As you can see, this type of graph shows our investment earnings (or loss) trends over a 12 month period.
That about covers it! We just wanted to present you with the basics. If you’d like to learn more about graphs and charts in Excel, leave us a comment.
Thanks for reading!
Brought to you by zeke43 – www.dodisdodat.com!
Brought to you by dodisdodat.com! All Rights Reserved © 2010-2011!
Share This Article! Share This Article!
Are you a blogger, site owner or writer with a tip or review to share?
