Total
10
9
8
7
6
5
4
3
2
1
0
f m f m f m
One Pt One To One Pt Three One Pt Two To One Pt Six One Pt Seven
Weight Sex Range Row Labels Count of Weight
1.1 m One Pt One To One Pt Three One Pt One To One Pt Three
1.2 f One Pt One To One Pt Three f 4
1.2 f One Pt One To One Pt Three m 2
1.3 m One Pt One To One Pt Three One Pt Two To One Pt Six
1.3 f One Pt One To One Pt Three f 9
1.3 f One Pt One To One Pt Three m 6
1.4 f One Pt Two To One Pt Six One Pt Seven
1.4 f One Pt Two To One Pt Six f 5
1.4 m One Pt Two To One Pt Six m 2
1.4 m One Pt Two To One Pt Six Grand Total 28
1.5 f One Pt Two To One Pt Six
1.5 f One Pt Two To One Pt Six
1.5 f One Pt Two To One Pt Six
1.5 f One Pt Two To One Pt Six
1.5 m One Pt Two To One Pt Six
1.6 f One Pt Two To One Pt Six
1.6 f One Pt Two To One Pt Six
1.6 f One Pt Two To One Pt Six
1.6 m One Pt Two To One Pt Six 1 I have an Excel 2007 spreadsheet with 2 columns for tracking 100 rows of fish stats.
1.6 m One Pt Two To One Pt Six
1.6 m One Pt Two To One Pt Six 2 I record the weights in column 1 (2lbs to 15lbs) and the sex in column 2 (M/F).
1.7 f One Pt Seven
1.7 f One Pt Seven 3 I want to create a chart that displays a count of how many male fish and how many
1.7 m One Pt Seven female fish were collected within 1lb weight ranges.
1.7 m One Pt Seven
1.7 f One Pt Seven 4 So I would be able to look at the chart and see how many of each sex
1.7 f One Pt Seven between 2-2.9lbs, 3-3.9lbs, 4-4.9 lbs, etc eas collected.
1.7 f One Pt Seven
11
12
13
14
15
16
17
18
19
20
1 Highlight the range A 1 to C 29 inclusive.
2 Insert / Tables group / PivotTable /
3 Click on Existing Worksheet Location: radio
button.
4 Click in cell E 1.
5 Pivot Table now loads starting at cell E 1.
6 Click in cell E 1.
7 The Pivot Table Field List should launch on the
right hand side of the screen.
8 Click in the Range box first (in the Pivot Table
Field List) then Sex then Weight.
9 I highlighted the whole Pivot Table at this point
and changed it to centred (cosmetic point
only).
10 Clicked in cell F 3 / right click then click on
Value Field Settings . . .
11 Value Field Settins window launches /
12 Summarize by tab - in here click on Count then
hit OK.
13 Lastly I changed the order of the Pivot Table
so that it matched the underlying data.
14 To do this I went into cell E 5 and typed:-
One Pt Two To One Pt Six
15 The Pivot Table was now arranged in the
same order as the underlying data.
16 To create a chart as well take the following
steps:-
17 Highlight the range E 1 to F 11 (the Pivot
Table).
18 Insert tab / click on Column icon / then click on
Clustered Column.
19 Click in the Chart somewhere / right click /
Move Chart . . . / select New sheet:
then hit OK.
20 Chart now gets moved to Chart1 Worksheet.