Share this page
Excel - Charts (Graphs)
Why use a chart?
simplify the analysis of a mass of data.
This exercise consists in creating a "3D Column chart" on a new worksheet.
Enter the following data in the appropriate cells.
Select the data by using a block (continuous or non-continuous), for the exercise, from A1 to D4.
The data selection is very important. Avoid selecting empty rows or columns. They will be added to the chart and leave empty spots in your chart. Use the instructions in the Basic operations page to select only the blocks of cells that you need. Make sure that every range of cells selected represents at least a data series for the chart. Don't take single cells scattered everywhere on your worksheet.
Generally, the first row or row selected from the range of cells will be used by the chart for the description of the X axis. The content of the first column from the range of cells will be used as the description for the legend of the chart. But, Excel will be confused if the description that you need for the X axis are numbers.
the Insert menu, select the Chart option.
you have the right selection. For the exercise, select from the Column charts, the 3-D Column option.
For the second step to create a chart has two tabs: the
one to determine the range of data (Data range) and the other to look of the data series.
Click on the Series tab.
Make your selection and press the Next button.
For the third step, there are several tabs. Each describes a characteristic of the chart.
Click on the Titles tab.
Click on the Axes tab.
on the Gridlines tab.
on the Legend tab.
on the Data Labels tab.
this exercise, do not activate any options.
For this exercise, do not activate the Data table.
Press the Finish button.
Excel will show you the finished chart with the options you selected.
Even if you finished the chart, it's always possible to personalize it better to answer your needs. The next part consists in showing to you some of its options and how to apply them.
The first stage consists in widening the space that's assigned to the chart.
Click on the chart only to select the part that's reserved for the chart.
You can, by selecting an object, that it's the chart, the title, the legend or quite other object that meets itself in the area of chart, to move it or to change it.
The chart changed dimensions. But, the text that meets itself on axes is still too big for the rest of the chart. To change the size of the text, there are three ways.
the cursor on the X axis of the chart.
The window with the properties of the axis will appear
The result should look like the picture below.
This looks like a chart that you can put in a report or a document for a customer or even to your boss. But you can even better. Excel offers several other options to improve the presentation of a chart.
To change an option of one of the objects in the chart, you can click on it and choose from the Format menu the first option. You can also double-click on the object that you want to change. Another way is to click the object and to press Properties button on the Chart toolbar. Another option you can use is by placing the cursor over the object and pressing on the right mouse button. A list of the options most often used will appear. It is also called a context menu.
Here is the short list of what you can make to change in the chart: move objects, change their size, the color and the orientation of the text, change the color, the pattern and the order of the series, insert some free text, drawings, arrows, the square or any item from the Draw toolbar, etc. You can access all the options by using the mouse or the main menu.
To stop changing options of the chart, click outside of the frame of the chart. If you generated your chart on another worksheet, click the tab to another worksheet.
It's also possible for you to change the text that's in the legend. Here is the legend before the change.
the Chart menu, select the Source data option.
on the Series tab.
Here is the legend after the change.
There are three ways to change the place: by using an option of the format menu or the properties of the chart or manually by using the mouse.
on the legend.
on the Position tab.
You can also move the legend by using the mouse.
Click on the legend
You can later change the size of the chart to take advantage of the space freed by the legend.
Click on the chart.
A border with squares should appear around the chart. Otherwise, re-select.
the cursor on the square of the middle right border of the chart.
You can so in this way by using the other squares to change the size of the chart.
You may change these options for all the boxes of text including the one on the axes.
Click on the main title.
the Font tab.
The next example consists in changing the orientation of the text of one of axes...
Double-click on one of the axes.
on the Position tab.
To highlight a data series, you may change its color as well as its pattern. Furthermore, if you think of printing on a printer, you may need to change the pattern for each of the series. Otherwise, the bars of the chart are all going to look the same. For example, a data series of the red color will be printed with the same tone of grey as the one that's blue. Both will be printed grey on a piece of paper. It may be better to distinguishing different data series to have a different pattern for each.
on one of the data series.
Press the Fill Effects button.
There are many ways to change the pattern on the bars of a Data series. Excel regroups them under four tabs: Gradient, Texture, Pattern and Picture. The Gradient tab able you to place gradient progressive patterns on the bars. The Texture tabs able you to place "natural" pattern from marble, wood and fabrics. The Pattern tab offers different types of stripes, rows and other regular patterns. The Picture tab offers you the possibility to apply an picture of your choice inside the Data series bar. Look at all the options and select the one that you need.
Change the color and the pattern for the Data series.
Not only can you change the patterns on the bars of the chart, you can also change the forms. You can choose from regular boxes to pyramids, cylinders and cones. Please take note that the only fill effects available for cylinders and cones are patterns, no gradient, texture or pictures for them.
Select the Pyramid type.
From time to time, a data series is hidden by the others. Or you may wish to move a data series ahead of another. It's possible for you to change the order of the data series to avoid this situation.
on the series that you want to change the place in the chart.
the series of your choice from the left column.
The more the data series is up on the list, the more toward the front of the chart it will be. Here is the result according to the order of the previous picture.
Replace the series in its original location in the chart.
You can show the value of the bar or the description of the axis of the various data series.
Click on the first data series.
The values that represent bars will appear above these. You can then move them towards your choice. This option shows you the values of a series at the same moment. It's possible to show the labels of all the series.
the Chart menu, select the Chart Options option.
The values of the series appear above bars. You can move them afterward and change their format.
Remove all labels.
It's also possible to add pictures such as the company logo or another picture suited for the chart.
From the Insert menu, select the Image option.
Excel offers you several sources for the picture. It can be from Office's library, the Internet, a file that you have or a WordArt text picture.
the From a file option.
To move the picture.
the cursor inside the picture.
To change the size of the picture.
Click on the picture.
A border with squares will appear around the picture.
the cursor on one of the square of your choice.
To keep the picture size proportional, also keep a finger on the Shift key.
Apart from the main title and the axes titles, it's also possible to add text to the chart to add comments.
Click on the formula toolbar.
The text will appear in the chart. To move the text box.
the cursor inside the text box.
You can then change the format of the text such as its size, its color and its orientation. Simply select the text and to choose the first option from the Format menu or double-click on the text.
Not only can you add text but also any object from the Draw toolbar. The next exercise consists in adding an arrow to the chart to better explain a point. It's also possible to add square, circles and several other objects. Before, continuing the Draw toolbar must be activated.
the View menu, select the Toolbars.
To move the Drawing toolbar.
the cursor on the titles bar of the toolbar.
To insert an arrow.
Click on the button arrows.
Your are now asked to add a data series that includes the exports of the company.
Add this last data series of numbers to your model.
In fact, it would have been able to place these data wherever on the worksheet. It's only the most logical place to place them. There are two ways to insert them: by using the options of for chart or simply to "drag" the range over the chart. The next part consists in adding a data series by using the options for charts.
on the chart.
You'll have to enter some data in the Name and Values boxes so that the new Data series shows up on the chart.
in the Name box.
in the Values box.
Because labels for the X axis are the same than for the previous series, you don't need to change the data.
Press the OK button.
A new data series was added to the chart. This series is however in the back of the chart. It's hidden by all the others. You can change the order of presentation of the series that was explained earlier on this page.
The other way of adding a data series to the chart is to select the range of values and the title (A5 to D5) and to slide it over the chart. However, this option works only if the data and the chart are on the same worksheet. This is impossible for the exercise of this page. The procedure is very simple.
the area of data including the title.
Once over the chart, the cursor should change and show a "+" sign next to the cursor.
Release the mouse button.
The selected series will appear on the chart.
Excel makes possible the addition of a second Y axis on the right-hand side of the chart. This allows you to compare values of different proportions. For example, it would be very difficult to compare millions of units sold to the percentage of market shares. This option is only available for charts with two dimensions (2D). Before demonstrating you this option, it will so be necessary to change type of chart.
Remove the ''Spectacular increase'' text and the arrow.
From the Chart menu, select the Type of chart option.
the type of chart according to the picture above.
For this exercise, we will presume that the data series Exports needs to be on a second axis.
Select the series Exports by clicking one of the bars of the series.
the Format menu, select the option Selected Data Series.
Select the Selected Data Series tab.
Activate the Secondary Axis option.
Excel offers you a preview of the chart. You can change this option or the others concerning the data series.
Press the OK button.
The Export data series is now visible beside the other date series of the chart. It has its own Y axis on the right-hand side of the chart.
To be able to better compare the data series Exports of the others, it would be preferable to change the type of chart for it. It will be changed the type of Line chart.
Select the Exports series with one of the techniques mentioned above.
the type of chart for only this Data series by selecting a Line with markers for the type of chart.
Here is the chart under its final format. It's now easier to distinguish the series and to compare them. There was also another way of changing the type of chart for one or all the data series. But you can't mix 2D with 3D charts or some types of chart with others.
Select the data series or the chart in its entirety according to your needs.
From the Chart toolbar, select the type of chart of your choice.
There are some types of predefined charts having two axes.
the Chart menu, select the Type of chart option.
Of the list of the types of charts, there are two that have two axes: Line - Column on 2 axes and Lines on 2 Axes.
Press the Cancel button.
The "Pie" charts are mostly used to demonstrate proportions or shares. But you may wish to highlight a point of the pie to put it more interest on it. Before being able to "explode" a point, it's necessary to create a pie chart.
Enter the following data in a worksheet.
a block with the range of data.
The "3-D Pie chart" will appear in the frame that
you created. Otherwise, begin again the steps above.
It's as easy as pie to split a pie chart! Sorry! I couldn't resist the pun.
It's always possible to you to print the chart alone on a page even if it's next to numbers and to formulas.
Click on the chart you wish to print.
All the options of the menus will fit to give the possibilities of
the chart; including the printing. If you don't click the chart, the page setup
and printing will show you the chart and the numbers of the worksheet
instead of only the chart.
You can use the same page setup and printing options as you set for the printing of the worksheets. Make sure to always preview before the printing. If you want to print just the chart, trust the preview of the chart. Although the chart seems correct with your data, it's the print preview that gives the best representation on to paper.
Press the Setup button.
They are the options of pagination for the chart. They are almost identical to those for the pagination for the file with the exception of the last tab: chart.
Click on the Chart tab.
The chart size option allows you to determine the size that it will take on paper. The "Use full page" option ajust the chart to take advantage of the whole page. The option "Scale to fit page" takes just advantage of the width of the page. The "Custom" option keeps the chart the same size as on the worksheet.
The print quality options allow you some control over the chart's printing. The "Draft quality" option is faster and uses less ink or toner. The "Print in black and white" option is for dot matrix printers. This helps to make the distinction between each of the data series of the chart.
The Options... button will show you the available options of the printer. The contents of these tabs vary according to the type of printer that's available. These tabs help to :
the paper format as well as the page orientation.
Because the contents vary according to the printer, it's up to you to select from the options that are offered.