Chapter 5 – Charts

Chapter 5 – Charts. 1

The ABC! Method to Creating Charts. 1

Accentuate the Labels and Data. 1

Build the Chart 1

Change its Appearance. 3

Exercise 1 - Bar Chart 4

Exercise 2 - Building Charts. 5

 

The ABC! Method to Creating Charts

Accentuate the Labels and Data. Build the Chart. Change its Appearance.

Accentuate the Labels and Data

You can use the mouse to highlight (Accentuate) the cells in the worksheet that include the information needed to build a chart. The selected rows or columns (including the labels) must contain the same number of cells.

 

The first step is always to accentuate the row or column of labels for the fields of data. If your data will have a name for each row, called a Series Name, your row of labels should start with a blank cell. Highlight that blank cell when you highlight the row.

 

Next, highlight your data (numbers.) If the rows or columns are not next to one another, hold down the Control key while highlighting different rows.

 

Each row of data usually starts with a Series Name for the data row. If your data has series names, select these cells when you accentuate the data.

For example: highlight cells C3 to I3 for the labels, hold down Control, highlight cells C6 to I9 for four rows of data, keep holding down Control, and highlight cells: C12 to I12 for a fifth row of data.

 

Be sure to highlight the labels first. The labels row (or column) must start with a blank cell if you want to include a series name for the rows of data. If your finger slips and you don’t completely highlight all the desired cells in a row or column, start over.

Build the Chart

Click the Chart Wizard icon on the Standard toolbar. The wizard builds the chart. The chart wizard is limited in what it can do, so you will change the appearance of your chart after you build it. For example, the wizard only allows a single line of text for the chart title, but you can change this after the chart is built. You can move both forward and backward through the wizard.

 

  1. Choose one of the available chart types.
  2. Look at a preview of the charts and select one. Move to the next dialog box.
  3. Confirm your data is correct.  Move to the next dialog box.
  4. Move from tab to tab, left to right, as you move through the third dialog box. Enter the title. Specify additional options. Look at every tab to be sure you have not missed any crucial options. Move to the fourth dialog box.
  5. Choose whether the chart is to be created as an embedded chart (an object) within a specific worksheet, or whether it is to be created in its own chart sheet. 

 

Change its Appearance

To change the appearance of any object in the chart: Right-click on it. Choose format. You can change the font, size, color, and style of existing text anywhere in the chart. Graphics can be re-colored and re-sized. You can add shadows to chart elements. Also notice that a Chart option appears on the menu bar when a chart is selected.

To work with individual data points: (For Example - Pie Wedges) Left click on the data series. All points are selected. Click again on the individual data point. The single point is selected.  Left click to drag, right click to format.

To edit the title: Click twice on it. You can insert extra lines (The wizard allows just 1).

To move or resize the legend: Click and drag

To name the chart sheet:  Rename the tab by right-clicking on it.

To move the chart: You can resize, move, copy, or delete a chart or any of the objects within the chart. If you resize the chart you may need to resize the fonts inside the chart.

To change the chart after it has been created. You can return to any dialog box in the wizard.  Right click in the chart background. Choose from the four “wizard” options, or use the Chart menu.

To remove a set of data points:  Right click within an element of the series. Select clear.

To refresh the chart’s data: A chart is linked to the worksheet on which it is based. If you change any of the worksheet’s data, the chart automatically adjusts to the new data.

To add additional rows of data to the chart:

·         Mouse method: Highlight a row of data. Left click again and move the cursor to get an arrow. Click and drag the arrow into the chart area. Release the mouse.

·        Wizard method: From the Source Data, Series tab, click on Add Row. Click the icon in the name box. Use the mouse to highlight the worksheet cell containing the name of your data. Press Enter. Click the icon in the Values box. Highlight the cells with the data and press Enter. Click OK

To add colorful background effects: Right click on any graphic element of the chart. Select format. Click on “Fill Effects.”  If you are formatting a graphic from the Drawing toolbar, you may have to use the “Color” pull-down box to find the “Fill Effects’ button.

To combine graphics with a chart: Use the Drawing toolbar to add text boxes, arrows and lines. Adding clip art WordArt and pictures can make a plain chart look spectacular.

To attach the graphics to your chart:  Select the objects by holding down the Control key and left clicking on the chart and on the graphics. Right click and select group.


Exercise 1 - Bar Chart

From the Tools menu, Customize submenu, Options tab, select all options except “large icons.”

We will build a bar chart showing monthly food sales by category for 6 months.

Data used for the charts www.cob.sjsu.edu/BUS91L/Textbook/chartdata.xls

Do not open the file from the web. Save the file on your desktop first, and then open it.

For sample pictures of the charts go to www.cob.sjsu.edu/BUS91L/Textbook/Ch5pics1.htm

 

 

A

B

C

D

E

F

G

3

 

Jan-00

Feb-00

Mar-00

Apr-00

May-00

Jun-00

4

Food Sales

 

 

 

 

 

 

5

   Sandwiches

$17,500

$18,000

$18,500

$19,000

$19,500

$20,000

6

  Soups/Salads

8,000

8,720

9,505

10,360

11,293

12,309

7

   Desserts

9,000

9,450

9,923

10,419

10,940

11,487

8

Total Food Sales

$34,500

$36,170

$37,928

$39,779

$41,733

$43,796

 

  1. Accent (Highlight with the mouse) the labels in cells A3 to G3. Cells in Row 3 contain your X Axis Labels. Highlight them first. Include the blank cell in A3.
  2. Cells in Rows 5, 6, and 7 contain data. Hold down Control. This will keep the cells in Row 3 highlighted. Use the mouse to highlight the rows of data. Cells A5, A6, and A7 contain the “series names” for the rows of data. Include these cells with the data.
  3. Build the chart – click the Chart Wizard icon.
  4. Select the Chart Type: Stacked Bar
  5. The chart’s title is Food Sales, with the axes labeled as Months and Revenues.
  6. Place the chart in a new sheet. Call the sheet “FoodSales.” (Don’t include any spaces.)
  7. Click twice in the title. Add your name and section number on a second line.
  8. Format all of the text elements as 12-point font.
  9. Format the Revenues axis so that the numbers are aligned to 45 degrees.
  10. Format the Revenues axis scale: maximum value is $45,000 and the minimum is $0. The Auto option next to maximum and minimum should be unchecked.
  11. Add light green shading and a shadow to the legend.
  12. Drag the legend to align its bottom line with the chart’s bottom line. Make the box taller.
  13. Fill the Sandwiches set of data points with a Bouquet texture. Add a shadow.
  14. Fill the Soups/Salads data points with Pink Tissue Paper texture. Add a shadow.
  15. Fill the Desserts set of data points with a Stationery texture. Add a shadow.
  16. Fill in the plot area background using the preset gradient Daybreak.
  17. Fill the chart’s background with an 80% pattern. Use a light yellow foreground.
  18. Add graphic elements from the drawing toolbar (WordArt, ClipArt, Shape, Etc).

 

Alter some data. Notice how the chart changes.

Exercise 2 - Building Charts

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

For sample pictures of the charts go to www.cob.sjsu.edu/BUS91L/Textbook/Ch5pics2.htm

Remember the ABC’s: Accentuate 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.) Accentuate the months’ row first and include the blank cell in column A. By including the blank cell, you will be able to include the series names found in column A when you select your data rows.

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

  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.
  2. Place the chart in a chart sheet with the sheet tab labeled ChartA.
  3. Add your name and section number on a second line in the title.
  4. Format all of the text and numbers in the chart using a 12-point font size.
  5. Fill in the walls area background using a 1-color gradient.
  6. Fill in the floor area with a texture.
  7. Fill the columns of your graph with a pattern.
  8. Add light yellow shading and a shadow to the legend.
  9. Click and drag to enlarge the legend box and then move it to the lower left of the chart.
  10. Add a texture to the chart area background.
  11. Add a graphic element from the drawing toolbar (WordArt, ClipArt, Shape, Call Out, Etc.) You do not have to use the same clip art as the ones shown in the 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.)

  1. The chart should have a title: Sales and Profits.
  2. Label the axes as Months and Sales Forecast.
  3. The chart should have a legend at the top as shown in the sample.
  4. The chart should contain major horizontal gridlines.
  5. Place the chart in a chart sheet with the sheet tab labeled ChartB.
  6. Format all of the text and numbers in the chart using a 12-point font size.
  7. Change the color of the Net Sales line to blue and increase its weight by one level.
  8. 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.
  9. Change the color of the Beverage line to red and increase its weight by one level.
  10. 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.
  11. Change the color of the Food line to green and increase its weight by one level.
  12. 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.
  13. Fill in the plot area background with a gold preset gradient angled diagonally up.
  14. Add a label to each line, using text boxes. Use a bold, size 14 font.

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

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

  1. The chart should have a title: Food Costs
  2. Do not show the legend.
  3. Label the pie wedges with both category and percentage. Use a “new line” separator.
  4. Place the chart in a chart sheet with the sheet tab labeled ChartC.
  5. Add your name and section number on the second line of the title.
  6. Format all of the text and numbers in the chart using a bold, italics, 12-point font size.
  7. Format the percentages to show 2 decimal places.
  8. 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.)
  9. Change the color of the wedges to different varieties of blue. 
  10. Fill the chart area background with a texture. 
  11. Add a graphic element from the drawing toolbar (WordArt, ClipArt, Shape, Call Out, Etc.) You do not have to use the same clip art as the ones shown in the samples.

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

  1. The chart should have a title: Monthly Beverage Sales.
  2. Label the x-axis as Months and the y-axis as Sales.
  3. Keep the legend on the right.
  4. Build the chart in your sales data worksheet, not on a separate chart/work sheet.
  5. Drag the chart down the page so it is not covering the worksheet data.
  6. Change all of the font sizes to 12 points and make them all bold. Hint, you can click in the chart background to change the format of all of the fonts.
  7. Fill in the plot area background with a texture.
  8. Fill in the sections of the columns using a 1-color “from center” gradient.
  9. Add a color and a shadow to the legend.
  10. Fill in the chart area background with a 1-color gradient.
  11.  Add a shadow and rounded corners to the chart. Note: the rounded corners option is only available in worksheets, not in chart sheets.
  12. Add a graphic element from the drawing toolbar (WordArt, ClipArt, Shape, Call Out, Etc.) You do not have to use the same clip art as the ones shown in the samples.
  13. Be sure to put the sheet tabs in order (Sales, ChartA, ChartB, ChartC.)

 

How to Make Corrections:

  1. 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, or use the Chart menu, to reenter the chart wizard.
  2. For example, if you didn'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.

Home                     Table of Contents             Next