SPREADSHEET CHARTING WITH EXCEL

Excel a beginning! 2000 The Garlic Press
SPREADSHEET BASICS EXCEL BASICS EXCEL CHARTS EXCEL DATA BASE
BACK
Contents
 
Charting Basics (In Graphics)
Creating a Chart
Modifying your chart
Excel Steps in Creating a Chart
(1) On your spreadsheet, select the data for your chart-including column and row labels as well as the data series. Note: This data compares prisoner and student cost in 1995 as reported in the Los Angeles Times January 1995.
 
Note: You can select data areas of you spreadsheet that are not directly connected by pressing the <Ctrl> key as you select by dragging the mouse

Note: Click the data table to load this spreadsheet file into Excel on your computer.


(2) Select the Chart Wizard icon from the toolbar 
 
(3) The Chart Wizard dialog box, Step 1 of 4, will appear. We could select a Chart type and sub-type but for this date we will keep the Column default selection. Don't be concerned with making the perfect decision --you can always change your choice later.
 
(4) Click Next and the Chart Wizard step 2 box, a miniature display of your chart and choices for Row or Column Series, will be displayed. Click Next for the next step. You could make changes in the Data range: and Series in: boxes if desired. Click Next when finished.
 
(5) The step 3 dialog box will appear.  (a) Type in a short, clear Chart title, Category (X) axis, and Value (Y) axis labels. Then click Next when you have completed your choices.
 
 
(a) Click the Axis tap to make changes in the axis selection. We will leave the default for this data.
(b) Click the Grid lines tab to make changes in the grid lines on your chart.  We will try the default for this data.
(c) Click the Legend tab to choose if you want to Show legend and then choose a the legend's Placement.  We will again go with the default for this data.
(d) Click the Data table tab  to choose to  Show data table.  The default, not showing the data table is our choice to keep our chart simple. 
(6) Click next to go to step 4

(7) The Step 4 dialog box will appear.  Choose whether to display your chart As new sheet or As object in your spreadsheet page. Then click the Finish button. I usually choose to display my chart on the same page as my spreadsheet.

(8) My first draft chart will appear on my spreadsheet along with the chart editing tool bar.

(9) I can now change data if necessary on my spreadsheet and the chart will be automatically updated, click and drag the chart to a new position if needed to get to your spreadsheet or copy the chart for placing in another document.

(10) You have completed your first chart. Your next step is selective refinement, customizing or modifying your chart.



Modifying your chart: Sample Spreadsheet and a default chart. We are going to use a different spreadsheet for this section of the tutorial thus allowing you to make the changes on the Prisoner vs Student Cost 1995 chart if you want to practice this section.
Note: Click the data table on the left below to load this spreadsheet file into Excel on your computer.
 
Customizing/Modifying an Excel Embedded Chart
After having created an embedded chart, editing requires clicking on a blank space on  the chart to make it active (be sure and do not have a specific chart element selected unless you want to make changes to that element) and to display the chart edit tool bar. A thick border will appear around the chart and the chart menu bar will appear floating on top of your spreadsheet window (see the graphic with step 8 in of Creating a Chart). You can drag the chart and the tool bar to any place on the spreadsheet you like.
 
Some but not all Ways to edit your chart:
(1) Double click the chart element and  a dialog box will open allowing you to make changes to the element, Note: clicking once then again after a few seconds on a label element, (the title, axes labels) will let you edit the wording of the label.
(2) Select the  chart by  clicking on a blank space of the chart, then choose an edit option from the Chart  menu at the top of your spreadsheet. You can choose  page Chart Type  which opens a dialog box that allows you to change the type of chart but maintain all labels. Chart Options opens a dialog box, see step 3 of creating a chart, that allows you to change title, axes, grid lines, legend, data labels and data tables choices.
(3) Choose a chart element from the Chart Edit menu bar. Note: if the chart menu bar is not floating on the top of your spreadsheet you can get it to appear by selection the chart (click once on a blank space on the chart) then choosing from the View menu Toolbars then Chart
(4) You can change the size of your chart (and along with size have more of the labels appear) by selecting the chart and dragging the corners of the chart box with the mouse.
(5) You can change the data being graphed by selecting the chart and then dragging the blue outline that appears around the data in your spreadsheet

This tutorial will treat only some of the editing techniques  you can use to customize your chart. Experiment almost everything you do can be reversed.
 
Changing chart type
Changing chart size
Changing a labels content:
Changing a labels format
Changing an Axis Format
Changes with the Chart Edit menu Bar
Adding a Text Note
Pulling slices from a pie
Formatting and Scaling Axes
More Chart Formatting possibilities
Copying your Chart to a Word Processing document
Sample Charts from Tutorial
Exercises: Not Completed
Exercises

  • Changing chart type: Select the  chart by  clicking on a blank space of the chart, then choose from the Chart  menu at the top of your spreadsheet Chart Type. A dialog box will open that allows you to change the type of chart but maintains all your labels.

  •  

     
     
     

    Our chart might be more informative as a pie since it expresses each category as a percentage of the whole so we choose a pie chart as an alternative type
     

  • Changing chart size: place the cursor at a corner of the chart. The cursor will change to a double pointed arrow. Drag the cursor to change the chart size.
    Our column chart looks a little flat and we would like to make it taller to focus on the differences in the column heights so we drag up to increase the height.
     
  • Changing a labels content: click once then again after a few seconds on a label element, (the title, axes labels) will let you edit the wording of the label.
  • Changing a labels format: double click any label (Title, or either Axis Title) to format the labels pattern font or alignment.  Just click the appropriate tab to make a change. Our titles looked OK so we did not change them.
  • Our titles looked OK so we did not change them.
     
  • Changing an Axis Format: double click any axis label either Category Axis or Value Axis  to format the labels pattern font or alignment.  Just click the appropriate tab to make a changes for patterns, scale, font, number or alignment.
Our column chart category axis does not show all the categories so we clicked alignment and placed the category labels on a down 45 degree down alignment.

  • Changes with the Chart Edit menu Bar: you could also use the Chart Edit Bar to make the changes above as well as a few additional possibilities:
(a) Click the down arrow by the Chart Area box to choose a chart area to edit then double click the selected element to get the change dialog box for that element
(b) Click to choose patterns, fonts and properties for your chart
(c) Click the down arrow by the Chart icon box to choose a different chart type. Note Chart types in the Chart menu has more choices available
(d) Click to have the legend appear or not appear with your chart
(e) Click to display the data table on your chart
(f) Click to change the chart orientation to rows for the legend
(g) Click to change the chart orientation to column for the legend
    Our column chart legend does not define categories appropriately so we clicked the chart orientation icon for row on the chart menu bar to obtain a proper legend.

    (h) Click to angle your Y axis label down
    (i) Click to angle your Y axis label up
     

  • Adding a Text Note: You can add a note, unattached text,  to your chart to make a comment or as we have done to site the source for the chart.
    • Select the chart by clicking once on a blank space on the chart, make sure no chart object is selected
    • Type in your text in the formula box at the top of your spreadsheet (press <Ctrl> <Enter> if you want more then one line of text
    • Click the green accept arrow beside the formula box
    • The text box with your text will appear on your chart
    • Select and drag  the text box to the location you desire
     
  • Pulling slices from a pie
    • Activate the chart by double clicking it
    • Select the pie slice you want to accentuate (by exploding) by double clicking and drag it out of the main pie
    • Note: Training has been reported as an important variable in recidivism

    •  
  • Formatting and Scaling Axes
    • Select the chart by clicking on a blank space on the chart
    • Select the scale you want to modify
    • Choose Selected Axes from the Format menu
    • Click the tab for the change you want to make: Patterns, Scale,  Font,  Number, Alignment  for your choice of dialog boxes
    • Make the modifications you want
    • Click OK
  • More Chart Formatting possibilities.  You can do much more to a chart then is covered in this tutorial.  You can make a chart like those that appear in USA Today by adding a picture or graphic to your chart (check Help in Excel to find how to do this. You can also copy and paste your chart to Photo Shop are another graphic editing program and really get creative.  Experiment!

  •  
  • Copying your Chart to a Word Processing document
    • This works pretty much like copying in other programs:
    • Select the chart by clicking on a blank space on the chart
    • Choose Copy from the Edit menu or use the shortcut keys <Ctrl><c>
    • Open the Word-processing document and place your cursor at the place you want your chart to appear
    • Choose Paste from the Edit menu or use the shortcut keys <Ctrl><v>
    • Resize the chart to an appropriate size for the document In the word processing document, select the chart by clicking it once to.  (usually you want to keep it proportional so drag from the bottom right corner handle on the chart graph to change the size proportionally)
  • Sample Charts from Tutorial, using the formatting procedures discusses.

  •  
  • Exercises: Not Completed


1] Many of the ideas presented in this document are from "Mac Charting Tools" by Michael Alexander as reported in Macworld January 1988.
[2] For some pretty good quality informative charts check out The Los Angeles Times
[3] For quality data to create your own charts try the Whitehouse web site Social Statistics Briefing Room at http://www.whitehouse.gov/fsbr/ssbr.html