Column Charts

Modules >>
Excel II >>
Column Charts

For your assignment, you will need to create a column chart based on extracted data for British Columbian males and females. To do this,

Create a copy of the Extract spreadsheet
Rename this new spreadsheet "Chart"
Under the PYLL column, choose "All" to view all values
Under the Sex column, choose "All" to view all records
Leave the Geography column filtered to British Columbia
There are two ways to create a chart:
- Go to the Insert menu and click on Chart, or
- Click on the Chart Wizard button on the Standard toolbar. This button will look like

You will have to work through each step in the wizard as follows:

Step 1 - simply choose the chart type.

In this case, select Column and click Next
(choose a column subtype, if you have this option)

Step 2 - choosing the data to include.

Click on the Series tab. We're going to have series for male and female rates.
On the Series Window, click Add
In the Name field, enter Male
In the Values field, click on the button to the right of the field (shown circled below).

You will be brought back to the spreadsheet to select the values (cells) you wish to include for the males. The following small "source data" window will also appear to record the values from the cells you select:

To select more than one cell, hold down the "Ctrl" button on your keyboard as you click on each value in column E that corresponds to a Male. In my sheet, these would be cells shown with dashed borders around them (bottom right) in the image below:

Their references will be recorded in the source data window.
Once your selection is made, click on the button to the right of the Source Data text field (shown circled below) to return to the chart wizard. This button is called the "collapse dialogue" button.

Repeat the above "step 2" to add a series for females. Name the field "Female" and select the cells in column E that correspond to females.
Last, we have to choose the labels for the X-axis (horizontal). The Y-axis (vertical) measure the Potential Years of Life Lost, but we would like the column chart to show us the values for each cause of death for males and females. To select the Category X-axis labels, click on the button (shown circled below).

Select the data cells in Column C for "Selected causes of death" as shown below by the dashed box

Next, click on the "collapse dialogue" button in the Source Data window to return to the chart wizard.
Your window should look similar to the following:

Step 3 - Chart Options

The third window will allow you to set your chart options. This is similar to the chart option window you found in the PowerPoint I module.
Set your options as follows: Chart title is "PYLL for B.C. men and women," Category X-axis is "Cause of death," and Value Y-axis is "PYLL" (for Potential Years of Life Lost). Click Next to continue.

Step 4 - Chart Location

You can choose whether you wish to include your chart as an object in the same spreadsheet you are working in or as a new spreadsheet. For your assignment, choose "As object in" and select the spreadsheet you want from the drop-down menu. In this case, select the "Chart" spreadsheet.
Click Finish
Your chart will appear. Click and drag the chart to position it so that it does not overlap your data. You can also click and drag the chart handles to resize the chart. You may wish to enlarge it so it is easier to read. If not all categories are appearing on your X-axis, you can edit it by doing the following:
- right-click on the X-axis labels
- choose "Format Axis"
- go to the "Scale" tab
- under "Number of categories between tick-mark labels," change the value to "1"
- there are a variety of other ways to format the X-axis, so explore the other tabs. You can always to the Edit menu and click "Undo."
That's it! Your chart is complete. Good work!

Please continue to the next section on Workbook Templates.