Business Intelligence

Use Excel to create simple graphs and bring your point home

Sometimes we take for granted what users know. While Microsoft Excel may be familiar to you, if you are on a help desk you will have to explain how the application works to users that fall within a vast range of skill level and knowledge. This series of Excel tutorials can make that training much simpler. This installment of the series shows you how to create and use Excel's built-in graphing functions.

You know the saying: "A picture is worth a thousand words." In most cases, an appropriate pictorial representationâ€"aka graphsâ€"of data can be even more valuable than the actual data since it can help a manager spot trends that might otherwise go unnoticed. As such, graphs can go a long way toward helping a business make better decisions about the future.

In this article, I'll use the budget spreadsheet example I've been using for this Excel data analysis series, but only for a specific example. In order to provide a wide overview of the different kinds of graphs available, I'll also be using another data set that lends itself well to a specific type of graph.

In part two of the graphing discussion, I will show you how to create a PivotChart, a graph-based PivotTable and how to create 3-D graphs.



This data analysis series consists of these articles:



The reason

As you've seen in the previous parts in this series, Excel gives you a lot of ways to organize and present your data. However, when it comes to simply presenting data, nothing can send a more powerful message than a well-designed graph. For example, when a manager looks at the figures $10,000 and $100,000, he knows that they are very different, but when faced with a bar chart that has one line ten times longer than the other, the message simply hits home a little harder.

Take the information shown below in Figures A and B. In Figure A, I've provided a budget breakdown by expenditure category. Suppose, for this example that upper management wants to see this information for the upcoming budget review cycle.

Figure A

A list of budget information in a raw form. Notice the use of subtotals.

Along with a little prose, this information would easily convey what you want it to convey. But, look at Figure B.

Figure B

The same information, but in graph form.

The first thing that this chart screams at you is that payroll costs are a huge percentage of the budget. Beyond that, it gives you an at-a-glance look at exactly where your money is going.

Graphs aren't always useful

When used correctly, a graph can be a very useful tool. When used incorrectly, a graph could work very much against you. I can offer two tips to help you with this:

  1. Always use the right kind of graph (bar, pie, line, etc)
  2. Take the time to format the graph so that it has the greatest impact on whatever you're trying to present.

For example, in Figure B, I intentionally rotated the graph so that the Payroll information would be front and center. If I was presenting a case to senior management for reducing the salary line, this would have the best impact as opposed to having it less of the focus.

Take a look at Figure C. This is a great example of how not to create a graph. This graph is a pie chart breakdown of the Administration department's total expenditures for each of three months. This information would be best left in a table!

Figure C

This graph tells you very little, and actually confuses the issue.

Graphing

Whenever possible, I like to show you how to do things with reasonable examples rather than with boring "now do this, now do this" kind of stuff. To that end, for this first example, we'll use a small personal expense spreadsheet to create a couple of different kinds of graphs. A look at the data behind the upcoming graphs is shown in Figure D.

Of course, for such a small data set, a graph isn't going to provide mind-blowing insight. However, this small data set with provide fodder for examples that can be easily applied to larger sets of information.

Figure D

This is a snapshot of a personal expense budget. What you learn using this small sample can be easily applied later to a larger workbook.

A look at trends


With a graph, trend analysis becomes a simple at-a-glance task. For example, suppose you want to get a look at a specific trend for a single item on your list to see what kinds of ups and downs you've had. As an example, let's look at the total gas expenditures for each month and plot them on a line graph. Why a line graph? A line graph is the best kind to use when you are interested in getting a look at trends. Figure E shows you a sample graph based on the sample data shown in Figure D.

Figure E

This chart shows you very quickly that gas prices skyrocketed and are coming back to earth.

You might be asking yourself why you would care about a graph like this. Consider this: Suppose you work for a shipping company. Gas prices factor very heavily into how you do business. By analyzing a trend like this, you can attempt to nail down the root cause of an increase and, if possible, take steps to correct it. In this case, the higher gas prices would be totally out of your company's control. Based on this kind of information, you could reasonably make a sound business decision to increase your delivery fees to cover your increasing costs.

Now, I'm going to provide the very quick steps you take to create a graph:

  1. Select the data, including row and column headings, that you want to appear on the graph. Omit what you don't want. You can select multiple areas by holding down the Control key on the keyboard while you make your selection. Alternatively, you can just start the chart wizard and provide this information as a part of that process, but I find that more cumbersome, personally.
  2. Click the chart button on the Standard Toolbar (Figure F).
  3. Select the type of graph you would like to create.
  4. Follow the instructions in the rest of the graphing wizard to complete your graph.
  5. Manually format your graph to provide the most impact.

Figure F

Use the chart button after you've selected the information you want to graph.

Step-by-step


I walk step-by-step through the procedure I used to create the graph you saw in Figure E.

First, in order to get just the headings and the data I wanted, I selected cells A1 and A3, and the ranges C1:H1 and C3:H3, as shown below in Figure G. I selected the row and column headings as a part of this process since I want them to show up in the graph. Notice that I skipped selecting the category field. I don't need it for this chart.

Figure G

I selected the cells highlighted in blue.

Once I selected the cells I want to chart, I clicked the Chart Wizard button I showed you in Figure F. This button opens the Chart Wizard, which consists of four steps. Step 1 is shown below in Figure H. On this step, pick the kind of chart you want to create. I opted for a line chart.

Figure H

The first step asks you to choose the kind of chart you want to create.

The second step of the chart wizard asks you to select your source data. Since I did that before I started the chart wizard, all of the information is already filled in. I find it much easier to select my data before I start the chart wizard than I do to use this screen, shown to you in Figure I.

Figure I

We'll take a closer look at some options on this screen later on.

Step 3 of the wizard asks you for a whole lot of information, including axis titles, gridline display information, whether or not you want to see the legend, what you want to display on each data point, and whether or not you want to display the source data with the chart. In the example I showed you in Figure E, the only change I made at this step was on the Data Labels tab, where I enabled the display of the dollar value of each data point. See Figure J for a look.

Figure J

In the Label Contains box, by enabling the check box next to Values, the actual source value for each data point is shown on the chart. You can see the result in the mini-sample chart in this window.

Your final decision to make is an easy one. Do you want the chart to get its own sheet in your workbook, or do you want to add the chart to an existing sheet in your workbook. For this example, I've opted to create a new sheet for the chart as you can see in Figure K.

Figure K

A new chart can be added to an existing worksheet, or you can segregate it off onto its own sheet.

The result of these choices is not exactly what you see in Figure E, though. In fact, take a look at Figure L to see the direct results of the chart wizard.

Figure L

The end of the chart wizard is just the beginning of your charting adventure.

I don't particularly like the base result from the wizard, actually. The data line is not pronounced enough, and the Y-axis scale places the results very high on the chart. It doesn't provide as much of an impact as it could with a more refined scale. So, I'll change it!

First, I'll change the Y-axis scale so that the hills and valleys in the graph are more pronounced. To do this, right-click any value on the Y axis (up and down axis), and choose Format Axis from the shortcut menu. This opens up a dialog box that provides you with the ability to completely customize the look and feel of the Y axis (See Figure M). You can change the scale, the font, how the tick marks are displayed and much more. For this example, I will just change the scale. I will make the minimum value 100 and the maximum value 200.

Figure M

By changing these values, the graph will stretch to incorporate more of the window.

The result is shown in Figure N.

Figure N

Note that the graph is now stretched out more, but the line is still a little ugly.

In Figure E, you'll notice that the data line is rounded and a little thicker. To change the look of the data line itself, right-click the line and choose Format Data Series from the shortcut menu. If you get the option "Format Data Labels" instead, move your mouse away from a data point and then right-click the line.

For the look you saw in Figure E, I changed the weight of the line, and turned on the "Smoothed line" checkbox on the Patterns tab in the Format Data Series dialog box, as you can see in Figure O.

Figure O

Change the values on this screen to modify the look of the data line itself.

The end result: The chart that you saw in Figure E.

If the point of a line chart is to provide trending information, why not add that information right to the chart. In Figure P below, I've added what's called a "moving average" trend line based on an average of the previous two months of data. This kind of trending is much more useful with more data, but the example in Figure P at least shows you how it works.

To add your own trend line to chart, right-click the data line and choose Add Trendline. You will also notice that I made some other cosmetic changes to the graph, including changing the background color. To change a chart's background color, right-click somewhere on your chart away from your data point and data lines, and choose the Format Plot Area option from the resulting shortcut menu.

Figure P

A view with a trendline and some other changes.

Definitions of charting terms

You might think this section is out of place, but it's not. I wanted to wait until you saw an example before I sprung a bunch of terms on you. Rather than just define terms, I will provide you with a definition and use the previous example to help you get a better feel for the term.

  • Category Name: A label on the X axis, also known a tick-mark label. In the previous example, the months are the category names.
  • Data Label: Refers to a label identifying each individual data point on a chart. In this example, the dollar values for each point on the data line are Data Labels.
  • Data Points: A Data Point is a value from a worksheet cell that is displayed on the chart. There are six data points on the chart in this example.
  • Data Series: A related group of Data Points, such as the yellow line shown in Figure P.
  • Explode: You can see exploding in action in Figure B. In this pie chart, the pieces of the pie do not join together to create a circle, but appear to be exploded away from the center of the pie chart. Exploding a pie chart can help to make it more readable.
  • Gridline: The lines across the charting plot area which make it easy for the eyes to follow back to the Y axis.
  • Legend: A box on the chart, such as the one to the right of the chart in Figure E, which explains what each line on the chart refers to.
  • Nonadjacent Selections (also called discontiguous selections): A nonadjacent selection happens when you select data from a worksheet that is not immediately next to other data you select. For this example, I used a nonadjacent selection, as you saw in Figure G.
  • Plot Area: The big green space in Figure P! The area in which your chart with be created.
  • Series Lines: The lines between stacked columns that connect the top of each series within the stack. Can be used on stacked bar charts. To set, click the appropriate chart type group on the Format menu, and then click to select the check box on the Options tab.
  • Tick-mark: Indicators on each axis that mark off the scale or category, or separate series groups on the X axis. In Figure P, notice the small black horizontal lines between each number on the Y axis. These are tick marks.
  • Trend line: An optional addition to a chart that may help you to determine trends based on your data. Figure P shows you a trend line in action.

What's next

In the next, and final, part of this series, you will learn how to create and manipulate 3-D graphs and PivotCharts.

0 comments

Editor's Picks