Super Graphing with Microsoft Excel

Available Graph Types

  • Data from an Excel spreadsheet can be displayed graphically using the following chart types:

Page 1

Super Graphing with Microsoft Excel

Line

Bar

Stacked-Bar

Pie

XY (scatter)

Area

Doughnut

Radar

Surface

Bubble

Page 1

Super Graphing with Microsoft Excel

  • Each type has two or more subtypes from which to choose.

When to Use Each Type of Graph

Pie Chart

Answers the question: What portion of the total does this part make up?

  • A pie graph allows us to compare parts of the whole with each other, or the fraction of the whole each part takes up.

Bar Graph

Answers the question: How different are these variables to each other?

  • Bar graphs are great for looking at differences between similar things and for giving a comparison of absolute numbers.

Line Graph

Answers the question: How does this one variable change over time?

  • A line graph is a good way to look at how something changes over time or sometimes over space.
  • Selected spreadsheet columns are charted in Excel column-by-column. The first column becomes the X-axis labels, and the subsequent columns become the data series.

Using Excel's Chart Wizard

2003/04

  • To create a graph using Excel's Chart Wizard:
  1. Open Excel.
  2. Type the information to be graphed into and Excel spreadsheet.

Example:

A / B / C
1 / Fruit / Likes / Dislikes
2 / Bananas / 6 / 2
3 / Oranges / 5 / 3
4 / Apples / 4 / 4
5 / Pears / 1 / 7
  1. Click and drag to select the data to be graphed. In the above example, this would mean selecting cells A1 through C5.
  2. Click on the Chart Wizard button (3-D Bar Chart).
  3. Select a chart type at the left and a chart sub-type at the right. Click and hold down the Press and hold to view sample button to see how the selected data will be displayed on the chosen chart.
  4. Click Next.
  5. If the preview chart is satisfactory, the data range is correct.
  6. Click Next.
  7. Click each of the tab options for the type of chart selected and make changes.
  8. Click Next.
  9. Place the chart either:

As new sheetCreates a workbook containing only the chart

As object inPlaces the chart on the current worksheet

  1. Click Finish.
  2. Resize the chart as needed.
  3. Double-click the different parts of the chart (title, legend, background, scale) to customize each (see Formatting Charts on page 4).
  • Chart options shown in Step 3 of the Chart Wizard may also be accessed by selecting Chart Options from the Chart menu.

2007

  1. Click and drag to select the data to be graphed.
  2. Click the Insert tab.
  3. In the Charts section, click the desired chart type.
  4. From the drop down list, click the desired chart style for that type.
  5. The chart will be inserted as an object in the current worksheet.

Chart Parts

Formatting Charts

  • Each area of the chart has a number of format options so the look of the chart can be customized to meet the user’s needs.

2003/04

  • To format a chart area using the menu:

a. Click on the chart area to select it.

b. From the Format menu select the first option, for example, Selected Chart Title.

NOTE: This option’s name changes according to which chart area has been selected.

c. Click on the availabletabs to see options.

d. Make changes and observe the sample.

e. Click on OK.

  • Chart options may also be accessed by double-clicking on a chart component.

2007

  • To format a chart area:
  • Right click on the chart area.
  1. From the pop-up menu, select Format Data Area.

NOTE:This option’s name changes according to which chart area has been select.

  1. Click on the options at the left.
  2. Make choices for each option at the right.
  3. Click on Close.

OR

  1. Click once the select the chart.
  2. Click the Layout tab on the ribbon.
  3. Click the drop down arrow of the chart area you wish to format.
  4. Select display options from the list

OR

  1. Click More Options… from the list.


The Chart Toolbar (2003/04)

  • The Chart toolbar has many of the same options available as the Chart menu.
  • The Chart toolbar appears when a chart is selected.
  • If the Chart toolbar does not appear when a chart is selected:

a. From the View menu, point to Toolbars.

b. Click on Chart.

  • To change chart types through the menu:

a. Click on a chart to select it.

b. From the Chart menu, select Chart Type.

c. Select a new Chart type.

d. Select a new Chart sub-type.

e. Click on OK.

  • To change chart types using the Chart toolbar:

a. Click on a chart to select it.

b. Click on the Chart Typebutton on the Chart toolbar.

c. Select a new chart type from the list.

  • To format a chart area using the Chart toolbar:

a. Select a chart area from the drop down list.

b. Click on the Format button (immediately to the right of the drop down list).

c. Click on the available tabs to see options.

d. Make changes.

e. Click on OK.

Chart Tools (2007)

Design Tab

TypeChange the chart’s type; save the specified chart as a template

DataSelect where the data comes from

Chart LayoutPositioning of titles, legends, and labels

Chart StylesColor schemes and formats

Move ChartGives to option to place the chart on a separate worksheet

Layout

Current SelectionFormat options for the currently selection chart area

InsertInsert pictures, shapes, and/or text boxes into the chart

LabelsLabel formatting options

AxesX and Y axis and gridlines formatting options

BackgroundPlot area formatting options

AnalysisInsert analysis options

Making Pictographs

2003

  1. Create a bar chart using the chart wizard.
  2. Click on the bars of the graph to select them. A black square in the center means they are all selected.
  3. If you wish each bar to be a different picture, click once on the desired bar.
  4. From the Format menu, select Selected data point.
  5. Under the color palette, click the Fill Effects button.
  6. Click the Picture tab.
  7. Click the Select Picture button.
  8. Select a picture file.
  9. Click OK.
  10. Under Format, select from these options:

Stretch

Stack

Stack and scale to (specified amount).

  1. Click OK twice.

2007

  1. Create a bar chart using the graph wizard.
  2. Click on the bars of the chart to select them. Circles at each vertex means they are all selected.
  3. If you wish each bar to be a different picture, click once on the desired bar to select only that bar.
  4. Right click on the bar, and from the drop down list click Format data point.
  5. In the left column, click Fill.
  6. In the right column, click the circle next to Picture or texture fill.
  7. Under Insert From, click either File, Clipboard, or ClipArt and select a picture.
  8. Click the circle next to Stack and Scale with.
  9. Type a number in the white box next to Stack and Scale with.
  10. Click Close.

Exploding Pie Chart Pieces

  • One or more pie chart pieces may be separated from the rest of the pie.
  • To “explode” all the pie pieces, click once on the pie chart, then click and drag on the pie and drag away from the center.
  • To “explode” one pie piece:

a. Click once on the pie chart.

b. Click a second time on a specific pie piece to select it.

c. Click and drag the pie piece away from the center of the pie.

Graphing Ordered Pairs

  • To chart ordered pairs in Excel:
  1. Type the X and Y coordinates of the points to be graphed into an Excel spreadsheet. (See sample below).

A / B / C
1 / X / Y / Quadrant
2 / 4 / 2
3 / 3 / -1
4 / 0 / 5
5 / 5 / 0
6 / -4 / -2
7 / -1 / 4
  1. Click and drag to select the points (A2 through B7 in the example above).

2003

  1. Click the Chart Wizard button.
  2. Under Charttype, select the XY (Scatter) graph.
  3. Select the top Chart sub-type.
  4. Complete the remaining Wizard instructions.
  5. After the chart is created, double-click on the chart's individual components to format them.

2007

  1. Click the Insert tab.
  2. In the Charts section, click the Scatter button and select the first Scatter type.
  3. Format other portions of the chart as desired.
  • In column C and beside each ordered pair, have students type the number of the quadrant in which each ordered pair is found. What is the pattern?

Graphing Equations

A / B
1 / Equation:
2 / X / Y
3 / -2
4 / -1
5 / 0
6 / 1
7 / 2
8 / 3
  • To calculate the value of Y:
  1. Create a spreadsheet as shown above.
  2. In cell A1 after the word equation, type the equation to be graphed.

Examples:

2x^2...... Two times x to the second power

4x + 3x^3....Four times x plus three times x to the third power

  1. In cell B3, type an Excel formula which will calculate the value of Y.

Examples:

=2*A3^2

=4*A3+3*A3^3

  1. Use Fill Down to copy the formula into at least 6 more cells in row B.
  1. Select cell B3.
  2. Click and drag the cell's lower right corner to copy the formula into the cells below.
  1. In column A, enter values for X.
  2. By hand, calculate at least one of the answers to double-check the formula entered.
  • To create the chart:
  1. Select the data.

NOTE: In the sample above, selected data would include cells A3 through B8.

  1. Click the Chart Wizard button (2007 click the Insert tab).
  2. Under Chart type, select the XY (Scatter) graph.
  3. Select one of the bottom four chart types (those that show connected points).
  4. Format other options as desired.

Page 1