Business Productivity Tools

Building Charts Using Excel

© Mike Splane 2006
(10 points possible)

 

Office 2007 instructions are in Red. Instructions specific to older versions are in Blue.

This project will use data from: www.cob.sjsu.edu/splane_m/ChartData.xls to create four charts. First save the ChartData file to the desktop and then open it.  View samples. 

Remember the ABC’s: Accent the labels and data, Build the chart, and then Change its appearance. Labels include the field names (i.e. months) and the series names (column A in the data rows.) Accent the months’ row first and include the blank cell in column A. By accenting the blank cell, you will be able to include the series names found in column A when you select your data rows.

2007. You select only the data, not the labels. After you build the chart, you add labels.

Chart One:  A Column Chart showing monthly juice sales for each of the 6 months. Chart Type: Stacked Column with 3-D Visual Effect

2007. Select cells B14 to G14 with the mouse.

Look on the Insert Tab, Chart Group, Column Category. Select the second chart in row 2.

  1. The chart should have a title (Juice Sales), with the X and Z axes labeled as Months and Sales. Don’t label the Y axis.

2007. From the Design Tab, Data Group, Select Data category.

      Edit the Legend – Type in Juice Sales, or click the icon in the text box and select cell A14.

Edit the Axis labels – Click the icon in the text box. Highlight cells B3 to G3.

  1. Place the chart in a chart sheet with the sheet tab labeled Chart A.

2007. Click on the Insert Workbook tab, at the bottom of the spread sheet. Cut and paste the       chart into the new Worksheet. Right click on the tab of the new worksheet to rename it.

  1. Add your student's name and section number on a second line in the title.

2007. Right click on the title and choose “edit text.”

  1. Format all of the text and numbers in the chart using a 12-point font size.

2007. Click on the chart background. Choose 12 in the font size box above the worksheet.

  1. Fill in the walls area background using a 1-color gradient.

2007. Right click on the walls. Select Format Walls. Select Fill. Select Gradient Fill

Choose Type: Rectangular

Choose Direction: From Center

Choose Stops: There should be two stops. Remove any extra stops.

Select Stop 1. Pick a color.

Select stop 2. Pick a second color.

  1. Fill in the floor area with a texture.
  2. Fill the columns of your graph with a pattern.

2007. Patterns are not an option. Use a gradient.

  1. Add light yellow shading and a shadow to the legend.
  2. Click and drag to enlarge the legend box and then move it to the lower left of the chart.
  3. Add a texture to the chart area background.
  4. Add a graphic element from the drawing toolbar (WordArt, ClipArt, Shape, Callout. Etc). You do not have to use the same clip art as the ones shown in the samples.
  5. View samples.

Chart Two: Line Graph with 3 lines: Total Food Sales, Total Beverage Sales, and Net Revenue, by month. Chart type: Line with markers (not stacked)

2007. Select cells B8 to G8 with the mouse.

Hold down CTRL Select cells B16 to G16 and B22 to G22 with the mouse.

Release CTRL

Look on the Insert Tab, Chart Group, Line Category. Select the first chart in row 2

  1. The chart should have a title: Sales and Profits.
  2. Label the axes as Months and Sales Forecast.

2007. From the Design Tab, Data Group, Select Data category.

Edit the Legend – Click the icon in the text box. Highlight cells A8, A16, and A22.

Edit the Axis labels – Click the icon in the text box. Highlight cells B3 to G3.

  1. The chart should have a legend at the top as shown in the sample.
  2. The chart should contain major horizontal gridlines.
  3. Place the chart in a chart sheet with the sheet tab labeled Chart B.

2007. Click on the Insert Workbook tab, at the bottom of the spread sheet. At the bottom of the worksheet you should see two tabs on the left labeled Sales and Sample Chart. The Insert Worksheet tab should be just to the right of the Sample Chart tab. If it is missing, you can also right click on a tab and select Insert. Cut and paste the chart into the new Worksheet. Right click on the tab of the new worksheet to rename it.

  1. Format all of the text and numbers in the chart using a 12-point font size.
  2. Change the color of the Net Sales line to blue and increase its weight by one level.
  3. Add a shadow to the Net Sales marker and increase its size to 11 points. Give it a blue foreground and a white background. Change the marker to a square.
  4. Change the color of the Beverage line to red and increase its weight by one level.
  5. Add a shadow to the Beverage marker and increase its size to 11 points. Give it a red foreground and background. Change the marker to a circle.
  6. Change the color of the Food line to green and increase its weight by one level.
  7. Increase the size of the Food marker to 13 points. Give it a black foreground and a yellow background. Change the marker to a triangle.
  8. Fill in the plot area background with a gold preset gradient angled diagonally up.

2007 - You can create your own gradient effect

  1. Add a label to each line, using text boxes. Use a bold, size 14 font.
  2. View samples.

Chart Three: Pie Chart showing Food Ingredient Costs by month.

 Chart type: Pie with a 3-D visual effect.

2007. Select cells B19 to G19 with the mouse.

Look on the Insert Tab, Chart Group, Pie Category. Select the first chart in row 3.

  1. The chart should have a title: Food Costs

2007. From the Design Tab, Data Group, Select Data category.

Edit the Legend – Type in Food Costs.

  1. Do not show the legend.
  2. Label the pie wedges with both category and percentage. Use a “new line” separator.
  3. Place the chart in a chart sheet with the sheet tab labeled Chart C.

2007. Click on the Insert Workbook tab, at the bottom of the spread sheet. Cut and paste the       chart into the new Worksheet. Right click on the tab of the new worksheet to rename it.

  1. Add your name and section number on the second line of the title.
  2. Format all of the text and numbers in the chart using a bold, italics, 12-point font size.
  3. Format the percentages to show 2 decimal places.
  4. Drag the June wedge away from the rest of the pie. (Hint, this requires three left clicks on the wedge. Drag it after the third click)
  5. Change the color of the wedges to different varieties of blue. 
  6. Fill the chart area background with a texture. 
  7. Add a graphic element from the drawing toolbar (WordArt, ClipArt, Shape, Callout. Etc). You do not have to use the same clip art as the ones shown in the samples.
  8. View samples.

Chart Four: Column Chart showing monthly Beverage Sales by type. Include all five rows of data. Chart type: Stacked Column

2007. Select cells B11 to G15 with the mouse.

Look on the Insert Tab, Chart Group, Column Category. Select the second chart in row 1.

  1. The chart should have a title: Monthly Beverage Sales.
  2. Label the x-axis as Months and the y-axis as Sales.

2007. From the Design Tab, Data Group, Select Data category.

Edit the Legend – Click the icon in the text box. Highlight cells A11 to A15

Edit the Axis labels – Click the icon in the text box. Highlight cells B3 to G3.

  1. Keep the legend on the right.
  2. Include the chart in your sales data worksheet, not on a separate chart/work sheet.
  3. Drag the chart down the page so it is not covering the worksheet data.
  4. Change all of the font sizes to 12 points and make them all bold.
  5. Fill in the plot area background with a texture.
  6. Fill in the sections of the columns using a 1-color “from center” gradient.

2007 - You may need to choose two colors for the gradient.

  1. Add a color and a shadow to the legend.
  2. Fill in the chart area background with a 1-color gradient.

2007 - You may need to choose two colors for the gradient.

  1.  Add a shadow and rounded corners to the chart. Note: the rounded corners option is only available in worksheets, not in chart sheets.

2007 - Right click on the chart background.

Select the last option "Format Chart Area."

Select "Border Styles." Put a check next to "Rounded Corners."

Select "Shadow" Change the "Size" option to 105%

  1. Add a graphic element from the drawing toolbar (WordArt, ClipArt, Shape, Callout. Etc). You do not have to use the same clip art as the ones shown in the samples.
  2.  View samples.

Be sure to put the sheet tabs in order (Sales, Chart A, Chart B, Chart C).

 

How to Make Corrections In Office 2003:

If you need to make a correction to the chart type, change the data source, or change the chart options, right-click on the background of the chart to reenter the chart wizard.

For example, if you don't highlight the cells in column A when you highlighted your labels and data, the legends in the charts will say series 1, series 2, etc. You can fix this later by going back into the wizard.

Right click on the chart background. Choose Source Data. A dialog box opens. Choose the Series Tab. To the right of the box that shows series 1, series 2, etc, you'll see an empty box next to the word Name.

Click on series 1. Click in the Name box. Now click on the tab of the worksheet that contains your data. Find the cell with the name that matches the row of data and click on it.

Go back to the wizard dialog box. Click on Series 2. Click in the Name box. Now click on the tab of the worksheet that contains your data. Find the cell with the name that matches the row of data and click on it.  Keep going until you have given each series a name.