ULearnOffice.com

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 2013/2010/2007 - Chart options

Introduction
Excel 2010: The double click is back!
Change the chart

Design tab

Add chart element
Quick layout
Change colors
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

A chart is used to help interpret a mass of data, to determine trends or changes in proportions. 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 double click is back!

An option that was very handy in Excel 2003 is now back in Excel 2010 and 2013.The possibility to double-click on any of item of the chart in order to change its format was removed in Excel 2007. In that version, 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 see the options offered by Microsoft Excel.

Excel 2013 - Chart - Initial data

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


Excel 2013 - Chart - Insert chart

*Select the Insert tab followed by the chart category you want.

*If you're not seeing the chart category you want, you can press the dialog launch button at he bottom right of the group to view more options.

Excel 2013 - Chart - recommended charts

A new option with Excel 2013 a the Recommended Charts button. It will recommend some chart types according to your data.

*Select All chart tab at the top of the window.

Excel offers 11 chart categories: Column, Line, Pie (with Doughnut), Bar, Area, X Y (Scatter) (with Bubble), Stock, Surface, Radar. Each chart categories offers many chart types. Excel 2013 has made it easier to combine many chart types into a signle chart with the Combo option.

 

 

Excel 2013 - Chart - Insert chart - All charts - Combo

Before going any further, here is a description for some chart type that you may not have seen before.

There is also the 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.

Excel 2007-2010: type anneau

The "Doughnut" charts looks a bit like a Pie chart. But someone ate the middle! 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.

Also, 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.

Excel 2007-2010 Type Bulle

The « scatter » chart or the "X Y" charts are used to represent two values (x and y) for the same data point (perceived value vs price point for example).

The Bubble chart is a evolved version of the scatter chart, or "X Y" chart, 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.

Excel 2007-2010 Type Radar

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 it's center outward.

 


Excel 2007-2010 Type Histogramme 3D Excel 2007-2010 Type Courbe Excel 2007-2010 Type Barres 3D

The three charts above representents 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.

Now, lets get back to our example...

*For this example, select Column and 3-D column.
*Press the OK button.

Here is the first version of the chart:

Excel 2013 - chart - first chart

Change the chart

With Office 2007, Microsoft changed completly the menu structure; including the way to manage charts. All the options are now under three tabs : Design, Layout and Format. Excel 2013 takes it one step further. The options that were under the layout tab have been moved to the right side of the chart. Some other fun options were also added.

Excel 2013 - Chart - Design tab

Excel 2013 - Chart - Format tab

 

This next section will explain the options found under the chart 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. The order of the options were changed in Excel 2013. But, most options are also available in Excel 2007 and 2010.

Add Chart element

Excel 2013 - Chart - Add chart element

Add chart element is new to Excel 2013. It replaces the options that were available under the Layout tab in Excel 2007 and 2010. It allows you to activate/deactivate chart elements that you can add to your chart. These same options are also available by pressing the green plus sign that is to the right of your chart. If you don't see it, just click inside your chart. The options will then appear to the right of the selected chart.

Try to activate each option to see how it will change your chart. You may decide to keep some elements and remove others. Most of these elements offer more options. Even with these options, some elements can still be moved around your chart. Make sure that your presentation is clear so that people can understand quickly the reason for the chart.

Quick layout

Excel 2013 - Chart - Quick layout

Excel offers you the option to select from a list of predetermied chart formatting styles. It can make t easier for you to create your chart and to personalize it afterward.

Change colors

Excel 2013 - Chart - Change colors

The change colors options offers you a quick way to change the colors of every data series. You can select from the list. You can always change a data series color with the options under the chart's Format tab.

Chart Styles

Excel 2013 - chart - chart styles

The chart styles gallery helps you change how the data is presented. You can choose from styles in the list or personnalize even more with the options under the chart's Format tab.

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.

Excel 2007-2010 Graphique Histogramme 3D avec the data intervertiesExcel 2007-2010 Graphique Histogramme 3D

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 by year. The second chart shows the years and the values by type of product.

Select data

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

Excel 2007-2010 Fenbe Sélectionner la source des data

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.

Excel 2007-2010 Ajouter Série de data

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.

Excel 2007-2010 Modifier Étiquettes des axes du graphique

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.

Change chart type

Excel 2007-2010 Bouton Modifier le type de graphique

This option allows you to change the chart type at anytime. 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.

Chart layouts

Excel 2007-2010 Disposition du graphique

This window you offers one list of prepared presentation formats. 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.

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.

Excel 2007-2010 Déplacer le graphique

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

Layout tab

Excel 2007-2010 Sélection de l'élément du graphique

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.

Excel 2007-2010 Insérer image dans graphique

*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

Office 2007-2010 Formes

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

Excel 2007-2010 Titre du graphique

A chart should have a main title that represents its content or the idas 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

Excel 2007-2010 Titre des axes du graphique

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

Legend

Excel 2007-2010 Légende du graphique

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

Excel 2007 Légende du graphique

Data labels

Excel 2007-2010 Étiquette des data du graphique

The data labels shows the values beside each data point. You can shows these labels for each data point of the chart, on ly 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

Excel 2007-2010 Axes du graphique

Showing the chart's axes allows you to show a descriptions for each data series and also the values qu’ils representnt. On voit for the picture of droite the années for the axe of abscisses (X), the description of data series for the axe of profondeur and the values for the axe vertical (also appelé the axe of ordata or Y).

Gridlines

Excel 2007-2010 Quadrillage du graphique

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

Excel 2007-2010 Zone de traçage

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

Excel 2007-2010 Zone de traçage du graphique

Chart wall

Excel 2007-2010 Paroi du graphique

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.

Excel 2007-2010 Paroi du graphique

Chart floor

Excel 2007-2010 Plancher du graphique

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

Excel 2007-2010 Plancher du graphique

3-D Rotation

Excel 2007-2010 Rotation 3D

The 3-D rotation 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.

Excel 2007-2010 Rotation 3D

Here is the same chart with a different orientation.

Trendline

Excel 2007-2010 Courbe de tendanceExcel 2007-2010 Courbe de tendance

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

Excel 2007-2010 Lignes d'un graphiqueExcel 2007-2010 Lignes

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

Excel 2007-2010 Graphique Barres Haut/BasExcel 2007-2010 Graphique Barres Haut/Bas

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

Excel 2007-2010 Graphique Barres d'erreurExcel 2007-2010 Barres d'erreur

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

Excel 2007-2010 Graphique Menu Mise en forme

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

Excel 2007-2010 Graphique Style de mise en forme

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

Excel 2007-2010 Graphique Remplissage de formes

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.

Excel 2007-2010 Graphique Remplissage uni   Excel 2007-2010 Graphique Remplissage transparence

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.

Excel 2007-2010 Graphique Remplissage dégradé   Excel 2007-2010 Graphique remplissage dégradé

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 jouter/Retirer dégradé buttons to add or remove color transition points.

Move the bars Excel 2010 Graphique Barre dégradé to determine where the change in color will start and the Excel 2010 Graphique Dégradé couleur button to determine the color of your choice.

Excel 2007-2010 Graphique remplissage avec une image   Excel 2007-2010 Graphique Remplissage avec une image

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

Office 2010 Contour de forme

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

Excel 2007-2010 Graphique Effet sur the formes   Excel 2007-2010 Graphique Effet sur the formes

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 to applied to a few elements such as the entire chart or to shapes.

WordArt Styles

Excel 2007-2010 Style WordArt

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

Excel 2007-2010 Organiser

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

Excel 2007-2010 Graphique Taille

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.

 

You like what you read? Share it with your friends.





This site is hosted by 1&1.com