Navigation

Previous
Next
Homepage
FAQ
What's new
Sitemap

Topics

Excel 2007/2010/2013
File tab
Home tab
Insert tab
Page Layout tab
Formulas tab
Data tab
Review tab
View tab
Developer tab

Excercises
Autosum
Chart - Insert a second axis
Create a half-moon chart
Create a demographics chart
Conditional formatting
Data table
Pivot table
Scenario Manager
Solver

Tutorials

Word
Excel 2003
Excel 2007/2010/2013
PowerPoint
Access

Others

Demonstration files
Texte en français

Contact

By e-mail
Join our Newsletter

Excel 2007/2010 - Chart options

Introduction
Excel 2010: The return the double-click
Change the chart

Design tab

Change the chart style
Save the model
Switch Row/Column
Select data
Chart layouts
Chart Styles
Move chart

Layout tab

Picture
Shapes
Text Box
Chart Title
Axis title
Legend
Data labels
Data table
Axes
Gridlines
Plot Area
Chart wall
Chart floor
3-D Rotation
Trendline
Rows
Up/Down Bars
Error Bars

Format tab

List of elements
Format selection
Reset to match style
Shape styles
Shape fill
Shape Outline
Shape effects
WordArt Styles
Arrange
Size

Introduction

This document was created using Excel 2010. You can use this manual to create charts. A chart is used to help interpret a mass of data, to determine trends or changes in proportion. You can add a multitude of elements (text, pictures, images, etc.) to your chart. However, remember to add only items that will help people understand the main point of the chart. Never add anything that might distract from it.

The return of double clicking

An option that was very handy in Excel 2003 is now back in Excel 2010.The possibility to double-click on any of element of the charts in order to change its format. With Excel 2007, you must place the cursor on the item, press on the right mouse button and choose "Format ...". However, it may be difficult to select an item when several are close to each other. That is why the first option in the Layout and Format tabs is the list of elements that make up the chart.

The next step is to create a chart in order to visualised the options offered by Microsoft Excel.

Enter the text and values in the appropriate cells. Select the range of cells from A1 to D4.

Select the Insert tab followed by the chart category you want. If you're not seeing the chart category you want, you can select the Other Charts button.

Select the chart type of your choice. Excel 2010 offer 11 chart categories: Column, Line, Pie, Bar, Area, X Y (Scatter), Stock, Surface, Doughnut, Bubble and Radar. Each chart categories offers many chart types. For example, the Column chart offers 19 chart types. The first chart type shows each data values side by side. The second type places the each value on top of each other to view the total and see how each value compares to each other. The third chart type is a chart where the element are of the same height but the proportions of each individual value changes from one to the other. The first three types are in 2-D. The next four are in 3-D. The last type places each data series one behind the other to create a "real" 3-D chart. The other types are variations of cylinders, cones and pyramids the represent the data instead of bars. Select All chart types at the bottom of the window.

The picture to the left shows the chart categories and its possible types. For example, The "pie" charts shouldn't have more than five or six values to be readable. However, you can also regroup some values into a sub-chart. The « scatter » or "X Y" charts are used to represent two values (x and y) for the same data point (perceived value vs price point for example).

There is also of stock charts that will show the highest, lowest and close values for a particular stock that you follow. The surface charts will show peaks and valleys from all the data.

The "Doughnut" charts looks a bit like a Pie chart. However, this type of chart can represent many data series at the same time. Each "ring" is a data series. You can compare the proportion of each compared to the others.

The Bubble charts of type pushes the scatter, or X Y charts, a bit further by adding a third dimension; the size of the point. The X and Y coordinates determine the center of the point. The third value determines the size of that point.

A Radar chart will allow you to compare many variables at the same time. That if why this type of chart must be read from its center outward. After you have seen all the possible chart categories, which one should you use for your data? The one that best represents the data in the chart. Do you want to show trends or proportions? Does the chart has many data series to show?

The three charts above represents the same values. Which one best represent the values? Would another chart type be more appropriate? The only person that can answer this question is you. Try out different types of charts before selecting the one that best suit your needs. Remember, a chart is used to make it easier for the reader to interpret the data; not harder. Any item you place on your chart must meet that goal.

For this exercise, from the Column category, select 3-D Column.

Change the chart

With Office 2007, Microsoft changed the menu structure; including the way to manage charts. All the options are now under three tabs : Design, Layout and Format.

This next section will explain the options found under these three tabs.

The Design tab

The Design tab allows you to determine the chart type, the data source for these charts, the chart styles and its location in the workbook.

Change chart type

This option allows you to change the chart type at any time. You can select from the complete list of charts. You can experiment and change the chart type to view chart represent best all your data.

Save as template

You can prepare your own chart models and the save so that they can be used for the next charts.

Switch Row/Column

Excel you offers an easy way to reverse a chart. The values that where on X axis are now on the Y axis and vice versa. You can decide which version best represent your data.

Look at the two charts above. Both are 3-D Column charts. However, the data for the X and Y axes have been reversed. The first chart represents the types of products per year. The second chart shows the years and the values per type of product.

Select data

This option allows you to add, edit or remove the data series from the chart.

The Switch Row/Column option is the same as mentioned above.

The other buttons allows you to add, change or remove of data series. The up and down arrow buttons allow you to change the presentation order of the data series. This is very useful when a data series masks in part the other data series.

For this exercise, use the up and down buttons to change the presentation order for the data series to Other, Carpets and Furniture. The picture to the left shows how to add a data series.  Enter the title of the data series, or the cell address where the title is located,  in the first box. Enter in the second box the range of cells where the data you want to add to the chart is located.

Press the OK button.

Another way add of data series to your chart is to copy it and paste it directly onto the chart.

Select the data series that you want add to an existing chart.
Press on the keys CTRL and C.
Click anywhere inside the chart.
Press on the keys CTRL and V.

The data series will be added to the chart.

The section to the right of the window allows you to change the text on that axis. Under the Label section for the horizontal axis, press the Change button. Enter the text for the label or the cell addresses with the description for the axis.

Chart layouts

This window you offers one list of presentation styles. You can choose among these to quickly present your chart. You will always be able to change any aspect of the chart to better suit your needs.

Chart Styles

The chart styles gallery shows you ways to formatting styles for your chart. You can choose among the list or personnalize even more with the options under the Chart's Format tab.

Move the chart

You can change at any time the location of a chart. You can the place it on a worksheet besides some values of your rapports or place it on a worksheet just for your chart.

Press the Move the chart button. Select the new location to the chart. Press the OK button.

Layout tab

The chart element list allows you to choose an item that composes the chart. You can then change it's formatting on the chart. This option is also the first one you will find under the Chart Tool's Format tab. There is also an option to reset to match style button to return to the original presentation. This allows you to experiment and try out thing while still being able to return to a standard chart presentation. You can also click on the chart elements that compose the chart. However, when the elements are very close to each other, it can be more useful to use the chart element list instead of trying to pick out the right element. To change an element of the chart, select it from the list before pressing the Format button.

Picture

You can also insert a picture such as the company's logo or something that represents the content of the chart. It can also be used as the background for the chart or it can also be used to represent your data! There will be more explications on that last option with the fill option under the Format tab.

Select the Picture button.
Select the folder where the picture you want is located.
Select the picture.
Press the Insert button.
Move the picture to the location of your choice on the chart.

Shapes

You can also insert of shapes to create your own pictures or frame an important element of your chart. You can combine these shapes to create the composition that you want.  You will find under the Format tab the Arrange group of commands. These commands will allow you to change the shapes' order and also to regroup them.

Text Box

You can also add text boxes to your chart to add some short explications to it.

Press the Text Box.
Place the pointer to the location where you want to place the text in your chart.
Enter the text of your choice.

Afterward, you can change the shape text box and move it to the location of your choice.

Chart title

A chart should have a main title that represents its content or the ideas you wish to pass to the readers (increase in sales…) Excel offers you three options to position the main title on the chart. You can also adjust the location of the title by dragging it around the chart.

Axis titles

A chart can have up to three axis: horizontal, vertical and depth. This option lets you determine if you should show the title and what style it should take.

Legend

The legend regroups the names of each data series.  The option allows you to decide the location where you want it on the chart. You can always change its location by dragging it with the mouse.

Data labels

The data labels shows the values beside each data point. You can shows these labels for each data point of the chart, only for a data series or even for only one important point. You must select it before applying a data label. Once you've activated it, you can change its location and its presentation (size, color…)

Data table

A data table shows the values represented in the chart in the form of ... a table that is located below the chart. This option in sonly useful is there are only a few numbers. It's useless if there are too much.

Axes

Showing the chart's axes allows you to show a descriptions for each data series and also the values they represent.

Gridlines

The gridlines are very important. The vertical grid allows you to regroup the values for the same period. The horizontal grid allows you to compare data points from different series.

Plot area

The plot area is the background for a 2-D chart. You can change the presentation to a gradient fill or a picture.

Chart wall

The chart wall is used to describe a 3-D chart's background. Like the plot area, you can insert of the color, a gradient fill of colors or a picture.

Chart floor

The chart floor can also be decorated with colors or a picture.

3-D Rotation

The 3-D rotate allows you to change the chart's orientation on all three axes. You may find a better view for your data with a different angle than the standard view.

Here is the same chart with a different orientation.

Trendline

It's often hard to determine a trend from a mass of data. That is why a trendline helps bring out the results. Excel 2010 offers many types of trendlines. It could be linear, exponential, logarithmic, polynomial, power or a moving average. The chart above shows linear trend with the green line, an exponential trend with the red line, and a future linear trend with the blue line. Be aware that trendlines are only available for 2-D charts.

Rows

There are two types of rows: projection and Up/Down. They allow you to project a trend or to view the highest and lowest values. You can then view the range between the minimum and the maximum for each period. The chart above shows this type of line. This type of line is only available to line and stock charts.

Up/Down Bar

The up/down bars are another representation of the up/down rows mentioned previously. They allow you to view the lowest and highest values for every period.

Error Bar

The error bars shows the margin of error according to some criteria.

The Format tab

This tab allows you to change the presentation styles of each and every element that marks the chart. It's under this tab that you can improve your chart's presentation. With Excel 2010, you can double-click on an element to have a complete list of options to change the style. You can also select an element and press for the right mouse button and choose the option Format the selected item.

List of elements

As from Layout tab, this first option allows you to choose an element from a list of all the items that compose the chart. This is very useful when there are many elements close the each other.

Format Selection

This option opens a window with a list of all the available presentation options for the element you selected. A double click on an item or a right mouse click will bring you to the same menu.

Reset to match style

You can experiment as much as you like knowing that you can always reset your chart to its original options.

Styles of format

You can choose among the predetermined presentation styles. The gallery allows you to choose among many colors and color gradients. There are even more options with the shape fill option.

Shape fill

This option allows you to determine the background of the chart or any element that composes a data series, the color, the pattern, a gradient fill or a picture of your choice.

Select a data series.
Press the right mouse button.
Select the option Format a data series.
Select the Fill category.

There are fill options for colors, gradient fill, picture or texture fill and pattern fill. You can select even more options once you have chosen a type of fill.

Select the element of your choice.
From the Layout or Format tabs, select the Format selection option.

Select the Fill category.

The transparency option allows you to  remove the emphasis from some data series. Also, it allows you to view data that would be hidden otherwise.

The gradient fill can help place emphasis on a data series instead of the others. There are predetermined gradient fills or you can create you own. Use the buttons to add or remove color transition points.

Move the bars  to determine where the change in color will start and the button to determine the color of your choice.

You can also insert a pictures to represent the data. It can be a clipart of the librairie of picture of Microsoft Office or another picture that répond aux needs.

Select a data series of data.
Right click to select the option Format Data series.
Select the Fill category.
Select the Picture or texture fill option.
For this example, press the ClipArt button.
Search for an appropriate clipart of your choice and select a picture.
Press the OK button.

The chosen picture will be streched to cover the entire data point. That could be enough for your needs. But the stack options are also very interesting. They allow you to stack pictures one on top of the other as seen in the example above. In this case, a complete picture represents each value of 5 units. The first column has three pictures to represent 15. The 4 planes in the second column represents 20 units and  5 planes for 25 units. To have the best effect, you may have to remove the data series outline.

Shape outline

You can place more emphasis on a data series by changing the outline of the shape. You can change the color to contrast with the fill color. You can also change the thickness of outline to make a data series stand out against the others.

Shape effect

The shape effects places a style around a data series such as a shadow, a glow or a bevel on a shape. However, the most popular effect that Excel 2007 added is the bevel effect. The result is stunning. The chart above shows many effects with different types of bevel on every bar. Find the one that best represents your data.

The 3-D rotation effect can only be applied to a few elements such as the entire chart or to shapes.

WordArt Styles

You can apply a WordArt style on any text included in the chart. Select the text of your choice. Press the button at the end of the WordArt window to open the WordArt gallery. Select among the styles available.

Arrange

Among the elements available, you can include shapes to compose your own pictures. The Arrange group of commands allows you to change the shapes' order and to align them.

Size

Determines the size of the chart if it's located on a worksheet. The chart takes the entire screen if the chart is located on its own worksheet.