Unit 9 - Making Excel Charts

Excel can make charts to graphically represent your data. There are several steps involved in making a chart such as selecting the data series, giving the chart a title, naming the axis, and working with the legend. After the chart is complete, you can change the formatting of individual parts. The table below will be used to create graphs and charts.


Column Chart

To start making the chart, highlight the data area (but not the Total column) of the table along with any row or column headings that are immediately adjacent to the data (A3:E11).

NOTE: Blank rows or columns in the selected area will be blank areas in the chart.

Click 'Insert Tab'->'Column' from the Ribbon's 'Chart' tools. Then select the '2-D Column' chart style and then the 'Clustered' column type. The Chart will automatically appear. Also the 'Chart Tools' menu appears, it's the green tab at the top of the window.

Under the 'Chart Tools' menu are three sub menus 'Design', 'Layout', and 'Format'. Click the 'Design' tab. With this type of data Excel automatically uses the rows on the x axis of the chart. This chart compares which grades bought how many balloons of a specific color. We can switch the axis of the chart by clicking the 'Switch Row/Column' tool button. Notice how the data switches. Now the chart describes how many balloons of a specific color each grade purchased. Switch back and forth between Rows and Columns several times and note how the sample chart changes as the rows/columns are shifted. If a chart does not look right at first, try switching the rows & columns. In Excel it is very important to double check your work!! Go back and make sure the data looks right. Even though you think all the buttons and data have been correctly selected and included go back and compare the chart with the table.

To give the chart a title:
o Click the 'Layout' tab under the 'Chart Tools' tab
o Click 'Chart Title'
o Choose 'Above Chart'
o Click in the title box and type in the name of the title (a good title describes the data in the chart)
o Click outside of the title box or press 'esc' when finished

This is what it should look like.


Charts are inserted in worksheets as objects and can be moved around and resized just as clip art was in Word.

To move a chart:
o Move the cursor over the edge of the chart until a black four headed arrow appears
o Left click and hold it while you move the mouse. The chart should follow

Pie Chart

Pie charts are for a single group of numbers. Trying to put all the colors and all the grades on a Pie chart would not work. The data for the 1st grade is all that would be graphed. Only a single row or a single column of numbers may be displayed in an Excel Pie chart. In Excel you can highlight noncontiguous cells by holding down the Ctrl key while clicking in the first cell of the series & holding then drag the mouse to the end of the data. The areas don't have to touch each other. We will try this below. If you need to get the 'Chart Tools' menu back click in the chart.

To make a Pie chart that shows the total color preferences of all grades:
o Highlight the column with the color names A4:A11. Now hold down the 'Ctrl' key while highlighting from cell F4:F11. It should look like the figure below (the cell with 'Total' in it is NOT selected)
o Click the 'Insert Tab' then 'Pie' drop down menu
o Select the '2-D' pie chart



Formatting Pie Charts:
o Click on the chart to get the 'Chart Tools' tab
o Click the 'Layout Tab'
o Click 'Chart Title'
o Click 'Above Chart'
o Enter the chart title something like Total Balloons Sold. Note that you can enter the chart title in the 'Formula Bar' or in the chart title box itself
o Click enter

To label the slices in the pie chart:
o Click the pie chart itself
o Click the 'Layout Tab' under the 'Chart Tools' tab
o Click the 'Data Labels' drop down menu
o Click 'Inside End'
The chart should like the one below


Changing Excel Charts

Excel charts, like formulas and functions, are automatically updated when the underlying values change. Changing even one of the chart values causes the chart to change. If a cell value is linked to multiple charts, all charts change.

A chart is made up of many components: the title, the chart area, the plot area, the axes, the legend and other elements. Many of these elements can be moved to a different location on the chart and all can be reformatted. When you right click on a chart element, the 'Context Menu' gives you the option of reformatting that element.

To modify the chart title:
o Click on the current chart title
o It should be highlighted meaning it should have a rectangle around it with circles on the corners
o Click in the box to get a cursor
o Edit the title of the chart
o Hit enter


To move the chart title to any location on the chart:
o Click the title in the chart so it is selected. A box around the title with blue circles in the corners indicates a selected title
o Move the mouse over the lines of the title box until a black four headed arrow appears
o Left click the mouse and hold it
o Move the mouse and the title box should move as well
o Drag the title box to the desired location and release the left mouse button

It is important for the readability of the charts to label the horizontal and vertical axis.

To label the vertical and horizontal axis:
o Highlight the chart.Remember the chart is highlighted when two light grey lines frame the entire chart
o On the 'Chart Tools Tab' that should come up when you highlight the chart select the 'Layout Tab'
o Click 'Axis Titles'
o Select either the 'Primary Horizontal Axis Title' or the 'Primary Vertical Axis Title' and then choose the desired style from the drop down menu
o Type in the name of the axis

The format of the numbers on the axis can be changed as well as the scale. Scale refers to the interval between the numbers. Depending on the size of your chart, the scale varies. If it is 4, the numbers along the axis are 0, 4, 8, 12, and 16 and so on. If it is 5, the numbers are 0, 5, 10, 15, and 20 and so on. Excel will automatically adjust the scale as the size of the chart changes; unless you have entered a specific scale to be used.

To change the scale and remove tick marks:
o Select the vertical axis by clicking on it. When highlighted a rectangular box with light blue circles in the corners surrounds the data on the vertical axis
o Select 'Layout Tab' from the 'Chart Tools Tab'
o Click 'Axis'
o Click 'Primary Vertical Axis'
o Click 'More Primary Vertical Axis Options...' to launch the 'Format Axis' menu
o Select 'Axis Options' and choose 'Major unit' to 'Fixed' and enter 5 next to it
o In the 'Major tick mark type' and 'Minor tick mark type' select 'None'
o Click 'Close'

A chart legend can have a border around it.

To add or remove a border around the legend:
o Open the 'Format Legend Entry' dialog box by double clicking on the legend
o Click 'Fill' in the left pane then click the 'Solid fill' radial button
o Choose a color.
o Click 'Close'

Individual column colors can be changed.

To modify column colors:
o Click the column to be modified. It should become highlighted
o Right click the highlighted column and select 'Format Data Series' menu
o Click 'Fill' in the left pane
o Click 'Solid Fill'
o Choose a color under 'Fill Color'
o Click 'Close'

The plot area is the rectangular area behind the columns. Initially it is clear and looks no different from the background of the entire chart. The figure below demonstrates the 'Plot Area' it is the area inside the rectangle with the light blue circles in the corners and the light blue squares on the lines connecting the circles. Here a border can be placed around the 'Plot Area' or a background can be applied.


To add a background color in the plot area:
o Highlight the plot area by clicking in the plot area
o Right click
o Choose 'Format Plot Area' from the context menu to bring up the 'Format Plot Area" menu
o Select 'Fill' if it is not already selected in the left pane
o Click the 'Solid Fill' radial button
o Choose a color
o Click 'Close'


To remove the grid lines in the plot area:
o Click on the grid lines until they are highlighted. The ends of the lines will have a blue circle on them
o Under the 'Chart Tools' tab select the 'Layout Tab'
o Click the 'Gridlines' drop down menu
o Click 'Primary Horizontal Gridlines'
o Select 'None'

Most of the parts of the pie chart can be reformatted just as they were with the column chart.

To change a pie slice's color:
o Click on the pie; the whole pie is selected
o Next click on the slice you wish to change. Now just the slice is highlighted
o Right click to bring up the context menu and select 'Format Data Point'
o Click 'Fill' on the 'Format Data Point' menu
o Click the 'Solid Fill' radical button
o Choose your color
o Click 'Close'

Copying from Excel to Word

All Windows-based applications share the same Windows Clipboard for copying and pasting information. When the paste command is given, whatever is in the Clipboard is copied into the active application. Note: the application that the information was copied from needs to remain open when the information is pasted. Windows looks to the copied application for formatting and will guess at how the information is to be formatted. Windows can guesses wrong on the best formatting style so always check what you have pasted.

Assume you have an Excel worksheet and a Word document open. Minimize the Word document and activate the Excel worksheet. Highlight a range of cells in the worksheet to be copied. (right click to copy or use the faster method Ctrl + c) Minimize the Excel worksheet and bring to the front the Word document. Now right click on the location for the paste operation, this bring up the 'Context Menu'. On this menu are the 'Paste Options:'. See the image to the left. Hover over each one and notice how the paste action can change the preview of the data being pasted. Click on one and the Excel table is pasted into the Word document. It becomes a Word table and the Word operations are used to reformat it. NOTE it is best to format your table in Excel before copying it to Word.