<<Back to Lessons

Microsoft Excel Chapter 4

Chapter Overview

In the business world, it is important to relay information so that it is visually appealing and easy to comprehend.  For this reason, Excel Chapter 4 teaches students to create charts and graphs to illustrate all types of data in various forms.  This section will focus specifically on how to use the Chart Wizard for 5 different types of charts:

 Pie Charts Bar Graphs Column Charts Line Graphs Combination Charts

It is crucial to understand each different type of chart because each of these charts or graphs can be used for different types of data in order to most clearly depict the necessary information.

## Projects

### Excel Chapter Four Project 1

Objective:  Create a simple pie chart.

1.  Open file called “Restaurant Stats”, which contains a company’s statistics on four of their restaurants.  They want to analyze the data in the form of a pie chart to display the breakdown of sales between each store and different types of foods.  The first task is to make a pie chart showing the difference in total amounts of food sold at each of the four restaurants.

2.  Highlight the last column of the series- the 5 numbers under the “Total” category.  Then click the Chart Wizard from the standard tool bar.  Select Pie for the Chart Type.  Click Next.

3.  The Data Range currently includes the total from the bottom of the category, which does not need to be included in the pie chart.  Change the range to go from G:6 to G:9.  Click Next.

4.  Title the graph- Total Sales.  Select the Legend tab and choose to show the legend on the Bottom.  Select the Data Labels tab and choose to show percent.  Click Finish.

5.  Select the chart and move it so it is not on top of the data set.  Notice that this graph does not contain labels and therefore does not show which color represents which store.  Right click the mouse over the chart and select Source Data.  Select the Series tab and under Category Labels type “North, South, East, West”.  Be sure commas separate the names.  Click Ok.

6.  Save file.

### Excel Chapter Four Project 2

Objective: Change Values to Currency and Create an Embedded Bar Chart, Change to 3D Chart

1.      Open file named Exc4Proj1Data.xls.

2.      Click and highlight cells B4 to E7.  Change numbers to currency by then clicking on Format, and then Cells.  Under Numbers, click on Currency.

3.      Resize the columns appropriately by clicking on the right edge of the column header and moving the mouse to the right.

4.      Create a chart comparing player’s salaries.  Highlight cells A3 to B7.  Go to Insert and then Chart.  Under Standard Types, Click on Bar Chart

5.      Uncheck Show Legend under Chart Options

6.      Click on Finish to Create the Embedded Chart.

7.      Resize chart by clicking on chart and resizing chart handles.

8.      Change the bars to 3D by clicking on the chart, then clicking the right mouse button the pull up a shortcut menu. Click on Chart Type, then select Clustered Bar with a 3D Visual Effect.

### Excel Chapter Four Project 3

Objectives: Create a column chart that shows the total departure totals by city.

1.      Open a new Excel Wordbook.

2.     Open the Mass Transportation chart.

3.      Select cells B4 through E4, then hold down the ctrl key and select B8 through E8 then pull down the Insert menu and select Chart.

4.      Select the column chart and the first sub-chart then click next.

5.      Under step 2, click next.

6.      Enter under the chart title “Total Mass Transit Departures by City for March

7.      Under the x-axis, enter “Cities

8.      Under the y-axis, enter “Total Number of Departures

9.      Go to the legends tab and deselect “Show Legend” and click next.

10.   Select the option “as option in Sheet 1”

### Excel Chapter Four Project 4

Objective: Create a combination chart using two types of data.

1.      Open data file Ms. Smiths Class

2.      Highlight B:4 to F:4 and B:5 to F:5 numerical entries.

3.      Click on Chart Wizard icon on the toolbar.

4.      Click Custom Types tab at the top of the menu box.

5.      Select "Line-Column" chart type. Click Next.

6.      Click Series tab at the top of the menu box.

7.      Click Category (X) Axis labels box.

8.      Highlight B:2 to F:2.  Click Next.

9.      Type "Ms. Smith’s Average Math and Spelling Scores" in Chart Title Box.

10.  Type "Year" in Category (X) axis box.

11.  Type "Score" in Value (Y) axis box.  Click Next.

12.  Then Click Finish.

13.  Click Chart tab on the toolbar.

14.  Click Source Data on the chart menu.

15.  Click Series tab.

16.  Highlight Series 2 and type "Math" in the Name box.

17.  Highlight Series 1 and type "Spelling in the Name box.

18.  Click Finish.

19.  Save as a part of Sheet 1.

### Excel Chapter Four Project 5

Objective:  Create a line graph with two sets of data.

2.      Highlight cells A:3 thru E:3 and then holding down move the mouse down to A:7 thru E:7.  A large block should be highlighted.

3.      Go to Insert and select Charts.  The Chart Wizard will open up.

4.      Under Chart type, select line.  A series of line graphs will appear.  Select lines with markers displayed at each data value.

5.      Select Next at the bottom of the window.

6.      The window for chart source data will appear.  Click the Series tab and select rows.  Select Next on the bottom of the window.  The Chart Options window will appear.  Under Chart Title type in ‘Student Average Scores.’

7.      Under x-axis, type in ‘Exam Number.’

8.      Under Y-axis, type in “Exam Score.”

9.      Select Next on the bottom of the window.  The Chart Location window will appear.

10.  Select place object in sheet 1 and select finish

11.  The graph will appear on the spread sheet along with the data chart.  Chick on the graph and move it below the first line graph created.

### Excel Chapter Four Project 6

Objective:  Understand how to change the location and size for a pie chart.

1.  Open the document containing the restaurant stats and the Pie Chart already created in an earlier activity.

2.  Select the Pie Chart and right click over the chart.  Select Location.  The Chart Location dialog box will come to the screen.  Choose As new sheet.  Click OK.

3.  Click on one of the data labels (represented by the different percents).  Once the sizing handles appear on all four labels, right click and select Format Data Labels…

4.  Select the Font tab and change the font to Comic Sans, Bold, Size 12.

5.  Select the purple wedge, which represents the North store by clicking twice in the middle of the wedge.  Drag on the middle and pull it slightly out from the rest of the wedges.

6.  Create a boarder by clicking the mouse on the white area surrounding the chart.  Click the right mouse button and select Format Chart Area…  Click Custom for the boarder and then scroll down and select any choice of style, color, and weight.  Select OK.

7.  Select the legend.  Grab the sizing handles and pull the legend to increase the size.

# Objective: Change Values to Currency by Using Mouse, Create Multiple 3-D Bar Charts in Separate Sheets, Format Axes

1.      Open file Exc4Proj1Data.xls.

2.      Click and highlight cells B4 to E7.  Change numbers to currency by clicking the right mouse button to display a shortcut menu, then click on Format Cells.  In the Number tab, select Currency in the category menu.  Resize chart columns appropriately.

3.      Make a salary comparison chart in it’s own sheet. Highlight cells A3 to B7.  Go to Insert and then Chart.  Choose Bar Chart and under sub-types, choose Clustered Bar with a 3-d visual effect.  Click Next.

4.      Under Legend, uncheck Show Legend.  Click Next.

5.      Under Chart Location in Step 4 of 4, place chart in As New Sheet.  Click Finish.

6.      Make an endorsements comparison chart in it’s own sheet.  Highlight cells A4 to A7, hold the Control Key on the keyboard, and then highlight cells C4 to C7.  On the Standard Toolbar, click on Chart Wizard

7.      Select Bar Chart along with a 3-d visual effect.  Click Next to get to Step 3 of 4. Uncheck Show Legend. Under the Titles Tab, enter the title Endorsements.  Click Next.

8.      Click on Place Chart as New Sheet.  Click Finish

9.      Bold the axes values on both charts by clicking the right mouse button on the axis of the chart and selecting Format Axis.  Under the Font Tab, choose Bold.

10.  Save as Exc4Proj3Data.xls.

Excel Chapter Four Project 8

Objective:      Create a stacked column chart showing the number of departures for each type of transportation, organized by city.

1.      Open a new Excel Workbook.

2.      Open Mass Transportation file.

3.      Select cells A5 through E8 then go to the Insert menu and select Chart.

4.      Select Column Chart, and under sub-chart, select Stacked Column with a 3-D visual effect then click next.

5.      On Step Two, click next.

6.      Under Chart title, type ”Transportation Departures By Type and City for March”.

7.      Under the x-axis, type “City” and under the z-axis, type “Number of Departures” then click next.

8.      Select the option “as option in Sheet 1.

Excel Chapter Four Project 9

Objective: Create a chart with a secondary "y- axis".

1.      Open data file “Maryland Shirt Company 1”.

2.      Click on the Chart Wizard icon on toolbar.

3.      Click on Custom Types tab.

4.      Select the "Line-Column" chart type.  Click Next.

5.      Highlight the data containing the year, profit numbers, and #of shirts sold.

6.      Click Series tab.

7.      Select Series 2 in the Series box and click the remove button.

8.      Then click the "Category (X) axis labels" box.

9.      Highlight the row B:2 to F:2 on the spreadsheet.  Click Next.

10.  Title the Chart "Maryland Shirt Company."

11.  Type in "Year" the Category (X) axis box.

12.  Type "Profit" in the Value (Y) axis box.

13.  Click the Axes tab and check box named Value (Y) axis in the secondary axis section.

14.  Go back to Titles tab and type "# of Shirts Sold" in the Second Value (Y) axis box.

15.  Click legend tab and remove check from "show legend" box.

16.  Click Next and then click Finished.

17.  Save as on Sheet 1.

Excel Chapter Four Project 10

Learning Objectives: Create a line graph with more than one data variables.

1.      Open the data file titled ‘Student Exam Scores.

2.      Highlight cells A:3 thru E:3 and then holding down move the mouse down to A:7 thru E:7.  A large block should be highlighted.

3.      Go to Insert and select Charts.  The Chart Wizard will open up.

4.      Under Chart type, select line.  A series of line graphs will appear.  Select lines with markers displayed at each data value.

5.      Select Next at the bottom of the window.

6.      The window for chart source data will appear.  Click the Series tab and select rows.  Select Next on the bottom of the window.  The Chart Options window will appear.  Under Chart Title type in ‘Student Average Scores.’

7.      Under x-axis, type in ‘Exam Number.’

8.      Under Y-axis, type in “Exam Score.”

9.      Select Next on the bottom of the window.  The Chart Location window will appear.

10.  Select place object in sheet 1 and select finish

11.  The graph will appear on the spread sheet along with the data chart.  Chick on the graph and move it below the first line graph created.

Excel Chapter Four Project 11

Objectives: Formatting and aligning Pie Charts.

1.  Open file labeled “Restaurant Stats”.  The company wants to analyze the number hamburgers sold at the different restaurants.  To do so, highlight the names of the restaurants then hold down the control key and select the column containing the hamburger statistics.

2.  Press the Chart Wizard from the toolbar and select Pie Chart.  Choose the second option under pie chart, which is 3-D Visual Effects.  Select Next.

3.  Check the Data Range and be sure it spans from B6:B9 and D6:D9.   Click Next.

4.  Under the Titles tab, label the chart “Hamburger Sales”.  Select the Data Labels tab and choose to show both label and percent.  Select Next.

5.  Create as New Sheet and select Finish.

6.  Select on the outside circle of the pie chart until the Moving Handles appear.  Pull on the outside of the circle slightly to separate the pieces of the pie from each other.  Only pull them out slightly to make sure the pieces are all easy to identify.

7.  Right Click the mouse on each of the labels for each slice until the Moving Handles appear and drag it with the mouse onto the slice it coincides with.  Be sure to place it on the top part of the slice to make sure it is still easy to read.

8.  Double Click on the title so the Format Chart Title box appears.  Create a Boarder and change the color of the Area as desired.  Click the Font tab and change the font size to 18 points.  Choose the Alignment tab and change the Orientation of the text to 45 degrees.

9.  Save file.

Excel Chapter Four Project 12

Objective: Edit an Existing Chart by Renaming Tabs, Editing Data Chart and Adding Value Labels to Bar Charts

1.      Open Exc4Proj3Data.xls.

2.      In Sheet1, Highlight cells A1 to C1.  Go to the Standard Toolbar, and change the font type to Impact and Font Size to 20.

3.      Shade the headers in the data chart by highlighting cells B3 to E3, holding down the Control Key, and then highlighting cells A4 to A7.  Click the right mouse button to display the shortcut menu and select Format Cells.  Under the Patterns tab, select the light blue shading for the cells.

4.      Rename the chart tabs by clicking the right mouse button on the tabs and then clicking Rename.  Rename Sheet 1 to Data Chart.  Rename Chart 1 to Salary Chart.  Rename Chart 2 to Endorsements Chart

5.      Emphasize the comparisons on the bar charts by showing data values.  On the salary chart, click on the chart and then click the right mouse button to display a shortcut menu.  Click on Chart Options and then go to the Data Labels Tab.  Click on Show Value and then OK.  Do the same for the Endorsements Chart.

Excel Chapter Four Project 13

Objective:   Create a chart showing  the amount of departures per city organized by the type of transportation.

1.     Open a new Excel Workbook.

2.    Open file labeled Mass Transportation.

3.     Select cells B4 through E7 then go to the Insert menu and select Chart.

4.      Select Column chart, and under Sub-chart, select Clustered Column with the 3-D visual effect then click Next.

5.   Select Next on the following screen.

5.     Under Chart title, type “Departures by Transportation Type” and click Next

6.     Select the option “as option in Sheet 1”

7.     The legend should be there but we need to name it.  In order to do this, go under the Chart menu and bring up Source Data

8.     Select Series 1 and under the Name text box, type “Trains

9.     Select Series 2 and under the Name text box, type “Buses

10.  Select Series 3 and under the Name text box, type “Planes

Excel Chapter Four Project 14

Objective:  Create a combination chart specifying exact values of units sold.

1.      Open data file "Maryland Shirt Company 2".

2.      Highlight all numerical entries in spreadsheet.

3.      Click Chart Wizard icon in toolbar.

4.      Click Custom Types Tab.

5.      Select "Line-Column on 2 axes" chart type. Click Next.

6.      Click Series tab.

7.      Select Category (X) axis labels box.

8.      Highlights rows with the years entered. Click Next.

9.      Click Titles Tab.

10.  Type "Maryland Shirt Company" in Chart Title Box.

11.  Type "Year" in Category (X) Axis Box.

12.  Type "Revenue" in Value (Y) Axis Box.

13.  Type "Units Sold" in Second Value (Y) Axis.

14.  Click Gridlines Tab.

15.  Click Major Gridlines box in the Value (Y) Axis section.

16.  Click Legend tab.

17.  Select top placement box. Click Next.

18.  Click Finish.

19.  Save as a part of Sheet 1.

Excel Chapter Four Project 15

Learning Objective: Customize graph using font, color, and borders.

1.      Open the data file titled ‘Student Exam Scores.”

2.      Highlight cells A:3 thru E:3 and then holding down move the mouse down to A:7 thru E:7.  A large block should be highlighted.

3.      Go to Insert and select Charts.  The Chart Wizard will open up.

4.      Under Chart type, select line.  A series of line graphs will appear.  Select lines with markers displayed at each data value.

5.      Select Next at the bottom of the window.

6.      The window for chart source data will appear.  Click the Series tab and select rows.  Select Next on the bottom of the window.  The Chart Options window will appear.  Under Chart Title type in ‘Student Average Scores.’

7.      Under x-axis, type in ‘Exam Number.’

8.      Under Y-axis, type in “Exam Score.”

9.      Select Next on the bottom of the window.  The Chart Location window will appear.

10.  To change the font of the lettering on the graph left click on the chart until a “Format Chart Area” Box appears.  Select the font tab.  Under Font, scroll down to Comic Sans MS.  Select OK.

11.  To change the coloring of the lettering on the graph double left click on the chart until the “Format Chart Area” box appears.  Select the font tab.  Under color, select red and press ok.

12.  To add a boarder on the graph left click on the chart until “Format Chart Area” box appears.  Select the Patterns tab.  Under Border section select custom.

13.  Under Color select Red (this is the color of the border).  Under weight select a thick line (this is the thickness of the boarder).  Select OK.  The boarder is now in place around the chart

14.  To fill in the chart area left click on the chart until the “Format Chart Area” box appears.  Select the Patterns tab.  Under Area, select automatic and select a light yellow color.  Select OK.  The chart is now filled in with a color.

15.  Save the completed formatted chart

Excel Chapter Four Project 16

Objectives:  Understand how to use different Pie Chart Wizards.

1.  Open file labeled “Restaurant Stats”.  The goal is to make a custom type pie chart appropriate for a formal presentation of the restaurant’s information to a potential new owner.

2.  Drag the mouse over the four total values at the bottom of the chart to highlight the total number of burgers, etc sold at all of the stores.

3.  Press the Chart Wizard from the toolbar and select Pie Chart.  Select the top Tab labeled Custom Types. Choose Blue Pie.  Click Next.

4.  Click the Series tab.  In the name box, type “Sales”.  Under Values, check to make sure the series range is from C:10 to F:10.  In the Category Label box, type the names of the categories in the order they appear on the spreadsheet (hotdog, hamburger, etc.).  Click Next.

5.  Under Chart Title, change the title from “Sales” to “Total Company Sales”.  Select the Data Labels tab and choose Show Label and Percent.  Click Next.

6.  Select As object in and scroll down to choose Sheet 2.  Click Finish.

7.  Notice that the data labels are too large for the chart and words are divided into more than one line.  But, the company wants the percents to remain large, so it is clear what percentage of each type of food was sold.  To solve this problem, Highlight each name of the food individually.  Once it is highlighted, Right Click and select Format Data Labels.  Change the Font Style to Regular and the Font Size to 10.  Select OK.  Repeat this for all four of the data labels.

8.  Click on the blue background of the graph so the Sizing Handles appear.  Drag the mouse on these handles and increase the size of the graph until the labels are clearly spread out and easy to read.

9.  Save file.

Excel Chapter Four Project 17

Objective: Create a Stacked Bar Chart, Use Autosum and Merge Cells

1.      Open Exc4Proj4Data.xls.

2.      Merge and center Cells A1 to E1 by highlighting and clicking the Merge and Center button on the Standard Toolbar.

3.      Find totals for each column and row by highlighting B4 to B8, then clicking the Autosum command on the Standard Toolbar.  Do this for all columns and rows containing numeric values.

4.      Create a stacked bar chart by highlighting cells A3 to E7, then clicking on the Chart Wizard command on the Standard Toolbar.  Under Chart sub-type, choose Stacked Bar with a 3-d visual effect.  Under the Chart Title Tab, enter ComputerLand Sales

5.      Show the data table in the chart by clicking on the bar chart and then clicking the right mouse button.  Click on Chart Options, then under the Data Table Tab check the Show Table option.

Excel Chapter Four Project 18

Objectives:      Create a graph with  the amount of departures by type of transportation organized by the city.

1.     Open a new Excel Workbook.

2.     Open file called Mass Transportation.

3.     Select cells B4 through E7 then go to the Insert menu and select Chart.

4.         Select Column chart, and under Sub-chart, select Clustered Column with the 3D visual effect then click Next.

5.      In step 2, change the series in to columns and click Next.

6.    Under Chart title, type “Total Departures for March by Transportation Type” and click Next.

7.    Select the “as object in sheet 1” and click Finish.

8.    The legend should be there but we need to name it.  In order to do this, go under the Chart menu and bring up Source Data

9.    Select Series 1 and under the Name text box, type “Houston”

10.     Select Series 2 and under the Name text box, type “Atlanta

11.      Select Series 3 and under the Name text box, type “Baltimore”

12.      Select Series 4 and under the Name text box, type “Chicago”

13.      Select Series 5 and under the Name text box, type “Total

Excel Chapter Four Project 19

Objective:  Create combination chart showing the raw data used to create the chart.

1.      Open data file "Pierre’s Shirt Shop."

2.      Highlight rows B:3 to F:3 and B:4 to F:4.

3.      Click on the Chart Wizard icon in the toolbar.

4.      Click the Custom Types tab and select Line – Column 2 Axes.

5.      Click Next.

6.      Select the Series tab.

7.      Highlight Series 2 and type "Profit" in the Name box.

8.      Highlight Series 1 and type "Shirts" in the Name box.

9.      Click in the Category (X) axis labels box and Highlight B:2 to F:2.

10.  Click Next.

11.  Type "Pierre’s Shirt Shop" in the Chart Title Box.

12.  Type "Profit" in the Category (Y) axis box.

13.  Type "Year" in the Category (X) axis box.

14.  Type "Shirts" in the Second value (Y) axis box.

15.  Click the Gridlines tab.

16.  Check the Major Gridlines box in the Category (X) axis section.

17.  Click Legend tab and select Corner in the Placement section.

18.  Click the Data Table tab and check the Show data table box.

19.  Click Next.

20.  Place Chart as Chart 1 by selecting the "As new sheet" box.

Excel Chapter Four Project 20

Objective: Create a line graph with many different sources.

1.      Open data file “Rainy Days.xls”.

2.      Highlight B:3 to F:8.

3.      Click the Chart Wizard icon on the toolbar.

4.      Select Line Chart.

5.      Select the series in rows option.

6.      Click Next.

7.      Type “Number of Times it Rains per Week” in chart title box.

8.      Type, “Week number” in Category (X) axis box.

9.      Ttype, “Number of days” in Category (Y) axis box.

10.  Click on Gridlines tab.  Check major gridlines box in the y- axis section.

11.  Click Legend tab and check the left box in the placement section.

12.  Save as object in Sheet 1.

13.  Click on title so that a box surrounds the title.

14.  Click the Bold and Underline icon on the toolbar.