Creating and Modifying Charts Tutorial
Excel 2010 -
Free Online Microsoft Excel Tutorial
* Select and Insert a Chart
Edit Data Ranges for a Chart
* Change Chart Type and Layout
Formatting a Chart
Charts are a graphical representation of your data. How to create a bar or pie chart. Knowing how to format charts using the Design, Layout and Format tabs to change colors, labels and scale makes you an Excel Chart Wizard!
Test your Excel skills with the corresponding FREE Online Multiple Choice
Excel Creating and Modifying Charts Test
* Select and Insert a Chart
Using charts to present your data visually does not only look professional, it will also aid in making reams of data spread over columns and rows in a worksheet, easier to interpret. Although charts may look complex to create, they are surprisingly easy to insert, edit and format using Excel 2010.
There are hundreds of different chart types available and the choice of which type to use will depend on the type of data you are using and what the purpose of your chart is. For example, a bar chart could be used to compare different data sets over a period of time with one each other, whilst a pie chart is a quick way to display numbers as fractions of a whole.
There are two basic ways of creating charts, inserting the chart first and adding data secondarily or creating and selecting data before inserting a chart. In this tutorial we will focus on creating charts from data that has already been created. In general, this is an easier method for inserting charts.
To insert a bar chart:
1. Open the worksheet containing the data you wish to use to create a chart. As an example, we are going to create a bar chart using the worksheet pictured below:
2. Select the data range in the worksheet that you wish to chart. For the example above, if we wished to create a bar chart displaying the different grade averages for each grade per semester, the range to select would be cells A2:E6.
3. After making the selection as detailed in point 2 above, click on the Insert tab on the Ribbon and in the Charts group click on the Bar button (circled in yellow in the screenshot below):
4. From the Bar Chart gallery select the chart style that you prefer from the available options by clicking on it. The chart will be inserted into your current worksheet. Don't panic if it is obscuring your data or not in the correct position. Click the outline of the chart to move it to a new position on the sheet or simply cut and paste it to a new worksheet. The chart remains linked to the data that was selected to create it, so if the original data is modified, the chart will also change.
Below is a screenshot of the chart created based on the data above. For this example, we picked the first option also known as a Clustered Bar:
We hope that you will agree that inserting a simple chart such as the one above, is not a complex process at all.
Let's look at a further example, by examining how to insert a Pie Chart:
1. Open the worksheet that contains the data set for the Pie Chart. We are going to use the data in the screenshot example below:
2. Select the data you wish to include in the Chart.
3. Click on the Insert tab on the Ribbon and in the Charts group click on the Pie Chart button.
4. Select the Pie Chart style you wish to use from the Pie Chart gallery and click to insert it into your worksheet:
The chart will be inserted into your current worksheet. As noted for the the Bar Chart above, do not panic if the chart is obscuring your data or not in the correct position. Simply click the outline of the chart box area to move it to a new position on the sheet or cut and paste it to a new worksheet.
Practice inserting charts by selecting logical sets of data that can be charted and inserting charts to represent the selected number ranges.
*Edit Data Ranges for a Chart
Charts are fed by numbers. Without numbers, any chart will cease to exist, with illogical selections of data, the chart will display nonsensical readings. It is therefore essential to plan what the purpose of the chart will be, what is the best chart type to represent your data and which part of the data needs to be included in the selection when creating the chart.
Once you have created a basic chart, as detailed in the tutorial section above called Select and Insert a Chart, you can manipulate the data even further or redefine which data to include.
To edit a data range in an existing chart:
1. Open the worksheet containing the chart and select the chart you wish to edit the data range for by clicking the box around the chart.
2. Under the Chart Tools contextual tab, click on the Design tab.
3. In the Data group, click on the Select Data button (circled in yellow in the screenshot below):
4. This will launch the Select Data Source dialogue box:
5. Click in the Chart Data Range box (circled in yellow in the screenshot above) and delete the current data references in the box.
6. Select the data you wish to chart by clicking and dragging through the relevant cells in your worksheet. Hold down the Ctrl key on your keyboard whilst selecting ranges that are not next to each other.
7. Press OK to accept the new data range to base your chart on.
TIP: Look out for the Switch Row/Columns button in the Select Data Source dialogue box. Clicking this box will swop your data around and may be just what your chart needs to make it easier to understand.
Don't be afraid to experiment. You can change chart data continuously by editing the data selection. If the actual number values in your data range(s) are changed, the chart will update automatically to match the latest values.
* Change Chart Type and Layout
Data is often dynamic, changing with seasons, sales or other variables. The chart type and layout applied to one data set, may no longer be applicable a short while later or you may find that the layout of a chart is making data difficult to read or understand.
There are a myriad of options available to change both the chart type or chart layout after it has been inserted. Chart layout refers to how the different elements of the chart is organized and displayed, for example, the chart title, labels or legend.
To change the chart type of an existing chart:
1. Select the chart by clicking on its outside border.
2. On the Chart Tools contextual tab on the Ribbon, click on the Design tab.
3. In the Type group, click on Change Chart Type. This will launch the Change Chart Type dialogue box with the current chart type highlighted (see screenshot below):
4. Select a new chart type to represent your data and press OK.
You can experiment with changing your chart as many times as you wish until you find the right chart that fits your needs.
Once you have found the right chart for your data, the next logical step would be to manipulate and tweak the chart layout.
To change the layout of a chart by adding a Chart Title:
1. Select the chart you wish to add the title to.
2. Click on the Chart Tools contextual tab on the Ribbon and then on the Layout tab.
3. In the Labels group click on Chart Title and select whether you wish to insert a title that is in the center slightly over the chart or a title that is above the data. Further chart title options can be accessed by selecting More Title Options from the Chart Title list.
4. The title will be inserted as a box containing the words Chart Title. Click in this box and type the desired heading for your chart as per the example circled in yellow in the screenshot below:
To add a title to either the horizontal or vertical axis of the chart, click on the Axis Titles button located next to the Chart Title button. This will insert a title box that can be renamed in the same way the Chart Title was edited.
The Legend of a chart usually comprises row or column labels that act as a guide for the data contained in the chart. For example, in the screenshot below, the Legend (circled in yellow) tells us that the orange bars represent Semester 1, the purple bars Semester 2 and so forth:
To reposition the Legend of a chart:
1. Select the chart by clicking on the outside borders.
2. Under the Chart Tools contextual tab on the Ribbon, click on the Layout tab.
3. In the Labels group, click on the Legend button and select one of the Legend position options from the list. For more in-depth options for the Chart Legend, click on More Legend Options.
Two final label layout options to add to a chart that may aid in making a chart easier to read, especially if there are percentages involved, is using the Data Labels or Data Table options. These are also located in the Labels group under the Layout tab on the Chart Tools contextual tab.
Data Labels add the actual data values to a chart whilst a Data Table inserts the data the chart is based on.
To make a chart easier to read, add Gridlines by:
1. Selecting the chart and clicking on the Layout tab under the Chart Tools contextual tab.
2. In the Axes group, click on the Gridlines button and select which gridlines you wish to add to your chart.
Pre-set layout options are available for all chart types and could save you a considerable amount of time. To apply a preset Chart Layout:
1. Select the chart to apply the layout to.
2. Under the Chart Tools contextual tab click the Design tab.
3. In the Chart Layouts group, select one of the preset chart layout types from the available gallery. Experiment with applying the different preset layouts until you find one that is close to the layout you need for your chart and click on it to apply. Use the layout out options listed above to tweak the pre-set Chart Layout further.
* Formatting a Chart
As the purpose of a chart is to represent data visually, it will come as no surprise to you that there are millions of ways to format charts by changing font type and size, colors, graphical elements and more.
To change the color of a chart:
1. Select the part of the chart to apply a new color to,. for example, click on one of the slices in a pie chart to select it.
2. Under the Chart Tools contextual tab, click on the Format tab.
3. In the Shape Styles group, click on the Shape Fill button and select a different color. You can also apply an outline to the chart slice that you selected by clicking on Shape Outline and selecting a color and line type.
To quickly apply a shape style to selected parts of a chart, review the available styles in the Shape Style gallery located to the left of the Shape Fill and Outlines buttons in the Shape Styles group.
To change the font type and size of Chart Titles and Labels:
1. Click on the label (titles, data or legend labels) you wish to change.
2. Under the Chart Tools contextual tab click on the Format tab.
3. In the WordArt Style group select one of the preformatted WordArt Styles from the gallery or use the Text Fill, Outline and Effects buttons to customize the formatting for a data label.
4. Click on the Home tab and apply font formatting such as font type and size from the Font group.
TIP: To quickly apply a preset chart format to the entire chart, click on the Design tab under the Chart Tools contextual tab. In the Chart Styles gallery, select one of the preformatted chart styles to apply to your chart.
There are literally millions of combinations of formatting, layout and chart type options available. Go ahead and experiment with creating charts, changing chart layout and applying formatting to it.