EXCEL EXERCISE #4: Using ChartWizard
Creating the Worksheet:
1. Enter the information in the worksheet below.
A B C D E F G H I J K
1 Distributions of Selected General Social Survey Questions
2
3 QUESTION #1 QUESTION #2 QUESTION #3
4 Marital Church
5 Gender Freq. Percent Status Freq. Percent Attendance Freq. Percent
6 Male 641 Married 802 Never 173
7 Female 825 Widowed 167 Less than once a year 97
8 Total Divorced 170 Once or twice a year 205
9 Separated 51 Several times a year 232
10 Never Marr 276 About once a month 117
11 Total 2-3 times a month 148
12 Nearly every week 76
13 Every week 298
14 Several times a week 108
15 Don't know; no answer 12
16 Total
2. Enter the questions below into the appropriate cells as indicated and copy into the
cell range that is listed.
B8: =b6+b7 (copy into cell C8)
C6: =b6/$b$8 (copy into cell C7)
F11: =sum(f6:f10) (copy into cell G11)
G6: =f6/$f$11 (copy into cells G7:G10)
J16: =sum(j6:j15) (copy into cell K16)
K6: =j6/$j$16 (copy into cells K7:K15)
3. Format values in percent columns using the percentage format 0.00%
4. Change the column widths as you like.
Your worksheet should look similar to the one below. Save it using the
filename A:GSS.XLS
1
, A B C D E F G H I J K
1 Distributions of Selected General Social Survey Questions
2
3 QUESTION #1: QUESTION #2: QUESTION #3:
4 Marital Church
5 Gender Freq. Percent Status Freq. Percent Attendance Freq. Percent
6 Male 641 43.72% Married 802 54.71% Never 173 11.80%
7 Female 825 56.28% Widowed 167 11.39% Less than once a year 97 6.62%
8 Total 1466 100.00% Divorced 170 11.60% Once or twice a year 205 13.98%
9 Separated 51 3.48% Several times a year 232 15.83%
10 Never Marr 276 18.83% About once a month 117 7.98%
11 Total 1466 100.00% 2-3 times a month 148 10.10%
12 Nearly every week 76 5.18%
13 Every week 298 20.33%
14 Several times a week 108 7.37%
15 Don't know; no answer 12 0.82%
16 Total 1466 100.00%
Using ChartWizard to Create a Chart for the Gender Question:
1. Click on cell A6 to make it the active cell, and drag the mouse so that the cell
range A6:B7 is highlighted.
2. Open the INSERT menu. Select the CHART option. Select the AS NEW SHEET
command.
3. Respond as follows to the steps in ChartWizard as prompted.
Step 1: Data Range
This will show you the range of cells that you have highlighted for the
chart’s contents. If the cell range is what you want, click on NEXT.
Step 2: Chart Type
Select COLUMN and click on NEXT.
Step 3: Chart Format
Select column format “1” and click on NEXT.
Step 4: Data Options
This shows where the ChartWizard is looking for your data and labels
to construct the chart. Check to see that the following options have
been selected.
Data series in: Column.
Use first (1) columns for X axis.
Use first (0) rows for legend text.
Click on NEXT if this information is correct.
Step 5: Chart Labels
This allows you to dress up your chart as you like. Add the
following information in the spaces provided.
Add legend: No.
Chart title: General Social Survey: Gender
Axis title--Category (X): Gender
Axis title--Value (Y): Frequency
2
Creating the Worksheet:
1. Enter the information in the worksheet below.
A B C D E F G H I J K
1 Distributions of Selected General Social Survey Questions
2
3 QUESTION #1 QUESTION #2 QUESTION #3
4 Marital Church
5 Gender Freq. Percent Status Freq. Percent Attendance Freq. Percent
6 Male 641 Married 802 Never 173
7 Female 825 Widowed 167 Less than once a year 97
8 Total Divorced 170 Once or twice a year 205
9 Separated 51 Several times a year 232
10 Never Marr 276 About once a month 117
11 Total 2-3 times a month 148
12 Nearly every week 76
13 Every week 298
14 Several times a week 108
15 Don't know; no answer 12
16 Total
2. Enter the questions below into the appropriate cells as indicated and copy into the
cell range that is listed.
B8: =b6+b7 (copy into cell C8)
C6: =b6/$b$8 (copy into cell C7)
F11: =sum(f6:f10) (copy into cell G11)
G6: =f6/$f$11 (copy into cells G7:G10)
J16: =sum(j6:j15) (copy into cell K16)
K6: =j6/$j$16 (copy into cells K7:K15)
3. Format values in percent columns using the percentage format 0.00%
4. Change the column widths as you like.
Your worksheet should look similar to the one below. Save it using the
filename A:GSS.XLS
1
, A B C D E F G H I J K
1 Distributions of Selected General Social Survey Questions
2
3 QUESTION #1: QUESTION #2: QUESTION #3:
4 Marital Church
5 Gender Freq. Percent Status Freq. Percent Attendance Freq. Percent
6 Male 641 43.72% Married 802 54.71% Never 173 11.80%
7 Female 825 56.28% Widowed 167 11.39% Less than once a year 97 6.62%
8 Total 1466 100.00% Divorced 170 11.60% Once or twice a year 205 13.98%
9 Separated 51 3.48% Several times a year 232 15.83%
10 Never Marr 276 18.83% About once a month 117 7.98%
11 Total 1466 100.00% 2-3 times a month 148 10.10%
12 Nearly every week 76 5.18%
13 Every week 298 20.33%
14 Several times a week 108 7.37%
15 Don't know; no answer 12 0.82%
16 Total 1466 100.00%
Using ChartWizard to Create a Chart for the Gender Question:
1. Click on cell A6 to make it the active cell, and drag the mouse so that the cell
range A6:B7 is highlighted.
2. Open the INSERT menu. Select the CHART option. Select the AS NEW SHEET
command.
3. Respond as follows to the steps in ChartWizard as prompted.
Step 1: Data Range
This will show you the range of cells that you have highlighted for the
chart’s contents. If the cell range is what you want, click on NEXT.
Step 2: Chart Type
Select COLUMN and click on NEXT.
Step 3: Chart Format
Select column format “1” and click on NEXT.
Step 4: Data Options
This shows where the ChartWizard is looking for your data and labels
to construct the chart. Check to see that the following options have
been selected.
Data series in: Column.
Use first (1) columns for X axis.
Use first (0) rows for legend text.
Click on NEXT if this information is correct.
Step 5: Chart Labels
This allows you to dress up your chart as you like. Add the
following information in the spaces provided.
Add legend: No.
Chart title: General Social Survey: Gender
Axis title--Category (X): Gender
Axis title--Value (Y): Frequency
2
