Exercise Two: Column, Line, and Pie Charts
Chart One: Stacked Column with 3-D Visual Effect
Chart Two: Line Graph with Markers (Not Stacked)
Chart Three: Pie Chart with a 3-D Visual Effect
Building a chart is a process with several steps.
Select
the cells containing information needed to build the chart. Include cells
containing data and cells with text for making labels.
Select
a chart type from the Insert tab.
Create
the chart.
Reposition
the chart in the datasheet or move it to a separate worksheet.
Add
labels to the chart.
Select
chart options.
Customize
the chart.
To select
data before building the chart:
Mouse over the cells that contain the information before inserting the chart.
If the cells are in separate areas of the worksheet, mouse over one area, then
hold down the Ctrl key while mousing over the other
areas. Only one of the cells may appear to be selected, but that’s ok. It’s
just a flaw in the new software. Release Ctrl.
To
change the chart after it has been created. Right-click in the
chart background.
Choose from the options.
To change
the appearance of any object in the chart: Right-click on it. Choose the format option at the bottom of the menu. To
add colorful background effects click on “Fill Effects.”
To work
with individual data points: Click
on the data series. All points are selected. Click again on the individual data
point. The single point is selected.
To edit the
title: You can replace text in titles by clicking on
the text box, then entering new text in the formula bar, then pressing Enter.
To add additional lines to a title, click twice in the text. Move the cursor to
the end of the line and press Enter. When finished adding text, click in the
gray area outside the chart.
To move or
resize the legend: Click in the
upper left corner and drag.
To rename
the chart sheet: Rename the tab by right-clicking on it.
To move or
resize the chart: Click and drag. If
you resize the chart you may need to change the size of the fonts used in the
chart.
To combine
graphics with a chart: Use icons on
the Insert tab to add graphic elements. Text boxes, arrows and lines, clip art,
WordArt, and pictures can make a plain chart look spectacular.
Exercise One: Bar Chart
Download and save the file www.cob.sjsu.edu/bus91L/textbook2/BPTDATA.xls.
Open the file.
Select the ChartData Worksheet.
|
|
A |
B |
C |
D |
E |
F |
G |
|
2 |
Java Juice – Monthly Food Sales in Dollars |
||||||
|
3 |
|
Jan-00 |
Feb-00 |
Mar-00 |
Apr-00 |
May-00 |
Jun-00 |
|
4 |
Food Sales |
|
|
|
|
|
|
|
5 |
Sandwiches |
42,000 |
42,500 |
43,000 |
43,500 |
44,000 |
44,500 |
|
6 |
Soups/Salads |
19,000 |
20,710 |
22,574 |
24,606 |
26,820 |
29,234 |
|
7 |
Desserts |
22,000 |
23,100 |
24,255 |
25,468 |
26,741 |
28,078 |
|
8 |
Total
Sales |
83,000 |
86,310 |
89,829 |
93,573 |
97,561 |
101,812 |
We
will build a bar chart showing monthly food sales by category for six months.
View sample charts. www.cob.sjsu.edu/bus91L/Textbook2/samplecharts.htm
1.
Use the mouse to
select the cells in the range A5 to G7. Note that you can select the names for each row of data along with the
numbers.
2.
From the Insert
tab, Charts group, Bar icon, select the 2D Type called Stacked Bar.
3.
Right-click on
the chart. Choose Move Chart. Place the chart in a new sheet. Name the sheet “FoodSales.” (Don’t include any spaces.)
4.
On the Design
tab, Data group, click on Select Data to open a dialog box. Click on the Edit
option underneath the words “Horizontal Category Axis Labels. Click on the icon in the textbox. Click on
the ChartData tab. Accent (highlight with the mouse)
the labels in cells B3 to G3. Press Enter. Click OK twice.
5.
From the Layout
tab, Labels group, choose Chart Title.
Select the “Above Chart” option. Click in the formula bar and type Food
Sales. Press Enter. Click in the title textbox. Add the words “Java Juice” to a
second line in the title. Click outside the text box to finish editing.
6.
From the Layout
tab, Labels group, choose Axis Titles. Insert a horizontal title for the
horizontal axis. Do the same for the vertical axis.
7.
Click on each axis
title and edit it in the formula bar. Label the horizontal axis as Revenues and
the vertical axis as Months. Press Enter after entering each label.
8.
Right-click in
the Revenues numbers. Choose Font. Change the font size to 14.
9.
Right-click in
the chart background. Choose Font. Select a bold style with a 14-point font
size.
10. Right-click on the Revenues numbers. Choose Format
Axis.
·
Under Axis
Options, Change the Minimum setting to Fixed and the value to zero.
·
Change the
Maximum setting to Fixed and the value to 105,000.
·
Change the Major
Units to fixed and the value to 15,000.
·
Click Close to
apply the changes.
11. Right-click on the Revenues numbers. Choose Format
Axis. Choose Alignment. Enter a custom angle of 45 degrees. Click Close to
apply the change.
12. Select the legend.
From the Format tab, Shape Styles group, Shape Fill icon, select a light green shading.
13. Click near the upper left corner of the legend and
drag the legend to the lower right corner of the chart. Click and drag to make
the legend larger.
14. Right-click in the plot area background. Select
Format Plot Area, Fill, and Gradient Fill. Click on the pull-down arrow for
preset colors. Select the fourth style in the first row, called Daybreak. Click
Close to apply the change.
15. Click in the chart background. From the Format tab, Shape Styles group, Shape Fill icon, select More Fill Colors. Select a
light yellow color.
16. Add graphic elements from the Insert tab, Illustrations group or Text group.
17. Save your file – Press F12.
Download and save the file www.cob.sjsu.edu/bus91L/textbook2/BPTDATA.xls.
Open the file.
Select the ChartData worksheet.
View sample charts. www.cob.sjsu.edu/bus91L/Textbook2/samplecharts.htm
Show monthly Juice Sales for each of the months.
1.
Use the mouse to
select cells A14 to G14 on the ChartData worksheet.
2.
From the Insert
tab, Chart group, Column icon, select the second chart in row 2.
3.
Right click on
the chart. Choose Move Chart. Select New Sheet and name it ChartA.
Click OK.
4.
From the Design
tab, Data group, click Select Data. Click on the Edit option underneath the
words Horizontal Category Axis Labels. Click the icon in the text box. Click on
the Chart Data worksheet tab. Use the mouse to highlight cells B3 to G3. Press
Enter. Click OK twice.
5.
Right-click on
the title and choose “edit text.” Add a second line that says Java Juice.
6.
Click on the
chart background. Choose 14 in the font size box above the worksheet.
7.
Fill in the
walls area background using a two-color gradient.
a.
Right-click on
the walls. Select Format Walls. Select Fill. Select Gradient Fill.
b.
Choose Type:
Rectangular.
c.
Choose
Direction: From Center.
d.
Choose the
pull-down menu under Gradient Stops: You will be using two stops. Remove any
extra stops.
e.
Select Stop 1.
Pick a yellow color.
f.
Select Stop 2.
Pick a green color.
g.
Click Close.
8.
Right-click
on the floor. Select Format Floor. Select Fill. Select Picture or Texture Fill.
Select a Green Marble texture. Click
Close.
9.
Fill the columns
of your graph with a preset-color gradient. Choose the Moss option.
10. Click and drag to enlarge the legend box and then
move it to the lower right corner of the chart. Add a solid-fill blue color to
the legend.
11. Add a denim texture to the background area outside
the chart.
12. Add a text box to the upper right corner of the
chart. Enter your name and section number. Use a size 14 font.
Show Total Food Sales, Total Beverage Sales, and Net
Revenue, by month.
1.
Use the mouse to
select cells A8 to G8 on the ChartData worksheet.
2.
Hold down the
Ctrl key. Mouse over cells A16 to G16 and A22 to G22. Only one of the cells may
appear to be selected, but that’s ok. It’s just a flaw in the new software.
Release Ctrl.
3.
From the Insert
tab, Chart group, Line icon, select the first chart in row 2.
4.
Right-click on
the chart. Choose Move Chart. Select New Sheet and name it ChartB.
Click OK.
5.
From the Layout
tab, Labels group, Chart Title icon, select Above
Chart.
6.
Change the title.
Click in the Formula bar. Type Sales and Profits and press Enter.
7.
From the Design
tab, Data group, click Select Data. Click on the Edit option underneath the
words Horizontal Category Axis Labels. Click the icon in the text box. Click on
the Chart Data worksheet tab. Use the mouse to highlight cells B3 to G3. Press
Enter. Click OK twice.
8.
From the Layout
tab, Labels group, click the Legend icon and move the legend to the top.
9.
From the Layout
tab, Axes group, use the Gridline icon to turn on major horizontal gridlines.
10. Click on the chart background. Choose 14 in the font
size box above the worksheet.
11. Click on the marker on the Total Food Sales line.
Right-click and select Format Data Series. Choose Line Style. Change the width
to 3. Choose Marker Options. Click Built-in. Increase the marker size to 11. Click
Close to apply the changes.
12. Click on the marker on the Total Beverage Sales line.
Right-click and select Format Data Series. Choose Line Style. Change the width
to 3. Choose Marker Options. Click Built-in. Increase the marker size to 11.
Click Close to apply the changes.
13. Click on the marker on the Net revenue line.
Right-click and select Format Data Series. Choose Line Style. Change the width
to 3. Choose Marker Options. Click Built-in. Increase the marker size to 11.
Click Close to apply the changes.
14. Fill in the plot area background with a canvas
texture.
15. Add a name to each line, using text boxes. Use a
bold, size 14 font.
16. Add a text box to the upper right corner of the
chart. Enter your name and section number. Use a size 14 font.
Show Food Ingredient Costs by month.
1.
Use the mouse to
select cells B19 to G19 on the ChartData worksheet.
2.
From the Insert
tab, Chart group, Pie icon, select the first chart in row 3.
3.
Right-click on
the chart. Choose Move Chart. Select New Sheet and name it Chart C. Click OK.
4.
From the Layout
tab, Labels group, Chart Title icon, select Above
Chart.
5.
Change the
title. Click in the Formula bar. Type Food Costs and press Enter.
6.
Format the title
in italics.
7.
From the Design
tab, Data group, click Select Data.
8.
Click on the
Edit option underneath the word Legend. Click the icon in the text box. Type in
Food Costs. Press Enter. Click OK.
9.
Click on the
Edit option underneath the words Horizontal Category Axis Labels. Click the
icon in the text box. Highlight cells B3 to G3 in the ChartData
worksheet to add the names of the months as category names. Press Enter. Click
OK twice.
10. Right-click on the legend and choose delete.
11. Right-click on a pie wedge. Choose Add Data Labels.
12. Right-click on a pie wedge. Choose Format Data
Labels. Change the labels option. Only category names and percentages should be
selected. Click Close to apply the changes.
13. In the lower right corner, insert a banner. You’ll find
banners on the Insert tab, Illustrations group, Shapes icon’s pull-down menu.
Look for Stars and Banners. Add your
name and section number to the banner.
14. Click on the chart background. Choose 14 in the font
size box above the worksheet.
15. Right-click on one of the percent numbers. Choose
Format Data Labels. Select Number. Change the category to Percentage. Format
the percentages to show 2 decimal places. Click Close to apply the change.
16. 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.)
17. Fill the chart area background with a dark-blue solid
fill color.