Using Charts in Excel 2010
 
 
 

Charts and diagrams are tools you can use to visually represent the data in a worksheet. You can use them to show trends, averages, high points, low points, and more. Not only do they make your worksheets more visually appealing, they also make it easier for you and your intended audience to sort out and understand the information you are presenting to them. This is especially true when dealing with a great deal of data.

If you used Excel 2003, you might remember the Chart Wizard. It was a four-step window designed to help you create and customize a chart as painlessly as possible. For Excel 2007 and 2010, Microsoft removed the Wizard and simply stuck all of the chart buttons in the ribbon. But don't let that intimidate you. It's actually easier to create and customize charts in 2010 than it was in 2003.

So let's navigate to the Insert tab and take a look at the Chart tools.
Types of Charts Here you can see the different types of charts. There are column charts, line charts, pie charts, bar charts, and more. Each button bears a graphic representation of the chart it creates, making it easy to identify. You can also click the tiny arrow in the lower right-hand corner of the Charts group to launch the Chart window. The chart window offers all of the same options as the buttons, except you are able to scroll through the different types of chart templates.
Clicking any of the Chart buttons unfurls a dropdown menu that contains a list of chart templates.
In this example, you can see all of the chart templates that are associated with the Column button. The button at the bottom labeled "All Chart Types" is another way to launch the Chart window. Let's take a look at it while we're there.

Like we said earlier, this window just gives you another way to browse chart templates. You can either click any item in the list on the left to see the templates associated with it, or simply use the scroll bar on the right.

Clicking a template automatically inserts it into the document . Unless a data range has already been selected, your chart will show up in your spreadsheet as an empty box. So to select a data range, select the data in your worksheet that you want the chart to represent.

In the following example, the information in the graph has been taken from the section called "Decorations." As you can see, the chart simply compares the estimated costs of decorations with their actual costs. Before we created this chart, we selected a cell in the Decorations group to select that data range.
If you neglected to select a data range, or selected the wrong data range, don't worry--Excel 2010 gives you many tools to change the data range of a chart that has already been inserted. We'll tell you more about this in a minute.
Chart Tools
Whenever you insert or select a chart in Excel, the Chart Tools tabs automatically activate. The Chart Tools consist of three tabs: Design, Layout, and Format. The tools on these tabs will help you customize your charts.
Let's take a look at the Design tab and discuss the options available to you.   Below is a zoom of the Design tab of Chart Tools, left and right.
Want to learn more? Take an online course in Excel 2010.






In this example, you can see that the Chart Tools are available because of the green highlight above the ribbon.

  • Change Chart Type -- The first button on the far left of the Design tab is the Change Chart Type button. Use it to launch the Change Chart Type window which is identical in every respect to the Insert Chart window. From here you can select a new template for your chart.
  • Save As Template – If you've created a custom chart that you want to use later as a template, you can click the Save As Template Button.
  • Switch Row/Column--This tool allows you to change the representation of your chart. For instance, take another look at the chart we made earlier. It lists flowers, candles, lighting, balloons, paper supplies, and a total along the bottom, while the estimated and actual costs were represented by the colors. If we click the Switch Row/Column button, these items are swapped.
  • Select Data--As we mentioned earlier, if you didn't choose a data range, or didn't select the correct data range, we could easily change this after the chart was created. The Select Data button lets us accomplish that. Clicking it launches the Select a Data Range window.

The data range refers to the number of cells you'd like to use. For instance, in the above example "=Expenses! $B$12:$D$18" refers to cells B12 through D18 on the worksheet titles "Expenses."  For now, it may be easier to select the data range by dragging your mouse over it. To do that, click the Data Range button  next to the text box.

The Select Data Source window minimize and you'll see another box that looks like this:  

But don't worry, you will not have to relaunch the Select Data Source window. This box is simply asking you to define the data range, and you can do it easily by clicking on a cell, holding the mouse button, and dragging it over all of the cells you'd like to add. MS Excel automatically enters the selected cell coordinates into the data range window. When you are finished, you can either click the button on the right  or push Enter.

Once you've created your chart and chosen data, you can begin to customize the look at feel. The Chart Layout group gives you a couple of quick layout styles you can easily apply to any chart.

To apply them, simply select a chart, then click on a chart. The icons representing each chart give you a good idea what the resulting chart will look like. For instance, the first button in the example above puts a title at the top of the chart and a legend on the left. The next button puts a title at the top, a legend directly under it, and labels at the top of each graph bar.

Below is an example of what our chart would look like if we selected the second button.

You can see additional quick layout styles by clicking the arrow at on the right.

You can rename the chart by clicking "Chart Title" and typing your own name. There's another way to change the chart name, too, which we'll talk about in just a minute.

To delete any element in a chart, simply select it, and hit the backspace or delete button.
Modifying and Moving a Chart Now let's take a look at the Layout tab under Chart Tools. Many of the options will be familiar to you, since we've already discussed them, so let's focus on the tools that pertain specifically to charts.

 Here is a zoom of the ribbon Layout Ribbon above, left and right




Remember a minute ago when we said there was another way to change a chart name? Well, if you look to the far right of the Layout tab, you'll see the Chart Name box. The selected chart is named Chart 3. We can change it by clicking the box and typing a new name.

You can also use the buttons on the ribbon to choose a chart title, change the title of each axis, modify the legend or data labels, or even plot an axis or a trendline.

Charts can be moved just like any other object in Excel. Simply select the object, move the mouse over the edge of it until it turns into a 4-headed arrow, then click and hold the left mouse button to drag.

Organizational Charts

While an ordinary chart represents data, diagrams and organizational charts explain the causal relationship between elements. The following organizational chart, for instance, explains the relationship between managers and subordinates.

 

Organizational charts are called SmartArt in Excel 2010. You can find the SmartArt button on the Insert tab. It looks like this: . Clicking it launches the SmartArt graphic gallery.

 

A basic organizational chart will open in the current worksheet. It will look like this.

The SmartArt tools will also open in the ribbon. From it you can customize your SmartArt to suit your needs, or try another. You can even change the chart color.   Below this ribbon is a zoom of the left and right sides.

 

 



Just like any other object entered into Excel, you can resize it by dragging the outer edges to the chosen dimensions.   Move an Organizational chart by dragging it to any location within a worksheet.

Changing an Organizational Chart

You will notice there are two panes in our organizational part. The one on the left is basically to make it easier to enter text. You can click the placeholder text in either the pane on the left or in the organizational chart itself and start typing to change it.

Other Changes to Charts

To add a picture to an organizational chart, just click the picture placeholders, which can be seen in the above example in the center of each circle. This will open a window from which you can select a picture from any location on your computer.
Sparklines Sparklines are an exciting new feature that allows you to create a quick visual representation of the information in a range of cells in a single cell. This visual representation can come in the form of a line graph, a bar graph, or win/loss graph.

Below is an example of a Sparkline. The cells highlighted in yellow represent the data range, while the selected cell (outlined in purple) represents the Sparkline graph.

 
The Sparkline group of buttons is located on the Insert tab of the ribbon.

When you click one of these buttons to create a Sparkline graph, you will first be asked to select a data range on which the graph is to be based.

Enter a data range, then select the cell where you want the sparkline graph to appear. Click OK when finished.

Whenever you create a Sparkline graph, or whenever a cell containing one is selected, the Sparkline Tools/Design tab will be available in the ribbon.    Below this ribbon is a zoom of the left and right sides of it.





From here, you can change the graph type, the visual style, colors, axis, etc.