Table
If you have a list of data, you should convert it to a Table:
● Select one cell inside data and select Insert → Table.
● If there are titles on the first row of data, select My table has headers.
Whenever you have a cell selected inside a Table, you find additional tools on the ribbon Table Tools - Design.
A Table has several useful properties:
● Table is easy to read due to nice looking formatting.
● There is a drop-down menu for sorting and filtering each column of the Table.
● If you enter new data below or beside the Table then the Table grows automatically.
● If you enter a formula beside the Table then the formula is automatically copied to each row of the Table.
● You can insert new rows and columns to the Table by dragging a small triangle at the lower right corner of the Table.
● You can add a summary: Table Tools - Design → Total Row. You can select calculation method from the drop-down menu.
● If you create a chart based on the Table data then the chart automatically expands when adding new rows to the Table.
To get rid of the Table, just select Table Tools - Design → Convert to Range.
Aki Taanila 8.2.2011
Sorting, filtering, adding new data
Use header row's drop down menu to sort and filter. Try!
You can expand the Table by adding new data below the Table. Try adding new products!
Product id Product name Category Price
402 Aerobic suit Clothes 39.00
403 College suit Clothes 59.00
203 Diet supplement Supplement 10.00
101 Wonder diet Book 25.00
305 Kettlebell 5 kg Fittness equipment 15.00
301 Excercise bike 4
Fittness equipment 99.00
104 Bodybuilding for beginners Book 39.90
304 Dumbbell set Fittness equipment 99.00
201 Mass builder Supplement 62.00
202 MultiVitaminPro Supplement 8.00
302 Rowing machine 5
Fittness equipment 99.00
401 T-shirt Clothes 9.00
103 Hair back in 20 days Book 49.00
105 Stretching guide Book 15.90
102 Get slim in a week Book 30.00
303 Multi gym 2
Fittness equipment 99.00
Total row, expanding Table
20000
19000
18000
17000
Myynti euroina
16000
15000
Carl
14000
13000 Lisa
12000 Henry
11000
10000
April
May
Ferbruary
January
March
Month Carl Lisa Henry Total Total Row has been added:
January 15800 12900 14500 43200 Table Tools - Design → Total Row.
Ferbruary 17460 13660 13980 45100 Calculation method Average has been selected
March 17130 13900 18500 49530 from the Total Row drop down menu.
April 16500 12200 14200 42900 You can add new months by dragging the triangle
May 18900 15200 19300 53400 at the lower right corner. Try!
Average 17158 13572 16096 46826 New months are automatically added to the chart.
Descriptive statistics
Excel gives each Table a name. You can find the name by selecting Table Tools - Design.
Excel gives each Table column a name on the basis of the column title.
You can use names in formulas (for example Table3[age]).
If you use mouse to show references then Excel uses names (like Table3[age]).
When using names, formulas are automatically update after adding new rows to the Table.
Note, that filtering has no effect to the calculated descriptive statistics.
Mean 37.95 12.18 2563.97
Standard deviation 9.77 8.81 849.38
Min 20.00 0.00 1521.00
Lower quartile 31.00 3.75 2026.80
Median 37.50 12.50 2320.20
Upper quartile 44.00 18.25 2808.00
Max 61.00 36.00 6278.40
Count 82 80 82
id sex age years in duty salary
1 1 38 22 3587
2 1 29 10 2963
3 1 30 7 1989
4 1 36 14 2144
5 1 24 4 2183
6 2 31 14 1910
7 1 49 16 2066
8 1 55 0 2066
9 1 40 23 2768
10 1 33 16 2106
11 1 39 22 2651
12 1 40 21 2846
13 1 35 15 2808
14 1 58 21 3587
15 1 53 12 3393
16 2 42 23 2691
17 1 26 2 5225
18 2 38 17 2729
19 1 42 20 2925
20 2 40 13 2457
21 2 40 20 2691
22 1 47 17 4874
23 1 44 27 3510
24 1 36 7 4446
25 1 43 1 2925
26 1 26 3 1521
27 1 26 2 1989
28 2 56 15 2223
29 1 47 23 2808
30 1 21 1949
31 1 21 1 2340
32 1 45 24 2925
33 1 59 15 6278
34 1 37 14 2183
35 1 28 5 1989
36 1 31 0 1559
37 2 56 17 2729
38 1 50 1 2027
39 1 30 10 2300
40 1 32 3 2106
41 1 33 9 2846
42 1 29 6 2534
43 2 40 12 2144
44 1 30 7 2223
45 1 55 35 2651
46 2 51 28 1989
47 2 22 21 1872
48 1 34 18 2183
49 1 27 7 2729
50 1 29 7 2340
51 2 39 10 2106
52 1 41 18 2261
53 1 44 3 1989
54 1 25 1 1559
55 2 45 17 2417
56 2 31 6 1949
57 1 61 36 3119
58 1 38 2574
59 1 20 1 2261
60 1 31 10 2144
61 1 44 19 2183
62 1 40 0 1872
63 2 51 10 1872
64 2 44 1 1715
65 2 35 17 2691
66 2 37 16 2027
67 1 37 8 5069
68 1 33 7 2417
69 1 28 1 3510
70 1 52 22 3119
71 1 34 1 2495
72 1 46 23 3470
73 2 40 2 2027
74 1 45 20 2846
75 1 40 1 1949
76 1 37 15 1598
77 1 39 22 2183
78 1 22 0 1598
79 1 33 2 1638
80 1 27 7 2612
81 1 35 16 2808
82 2 35 15 2183
Descriptive statistics, sensitive to filtering
The Subtotal function ignores any rows that are not included in the result of a filter
For example, if you hide males (sex=1) by filtering the Table then Subtotal calculates descriptives for females only (sex=2).
The first argument specifies which function to use. For example, 1 specifies Average.
See Help for Subtotal function to find relevant number for each function.
Unfortunately median, lower quartile and upper quartile cannot be calculated by using Subtotal.
Beginning Excel 2010 you can use more versatile Aggregate-function.
Mean 37.95 12.18 12.18
Standard deviation 9.77 8.81 8.81
Min 20.00 0.00 0.00
Max 61.00 36.00 36.00
Count 82 80 80
id sex age years in duty salary
1 1 38 22 3587
2 1 29 10 2963
3 1 30 7 1989
4 1 36 14 2144
5 1 24 4 2183
6 2 31 14 1910
7 1 49 16 2066
8 1 55 0 2066
9 1 40 23 2768
10 1 33 16 2106
11 1 39 22 2651
12 1 40 21 2846
13 1 35 15 2808
14 1 58 21 3587
15 1 53 12 3393
16 2 42 23 2691
17 1 26 2 5225
18 2 38 17 2729
19 1 42 20 2925
20 2 40 13 2457
21 2 40 20 2691
22 1 47 17 4874
23 1 44 27 3510
24 1 36 7 4446
25 1 43 1 2925
26 1 26 3 1521
27 1 26 2 1989
28 2 56 15 2223
29 1 47 23 2808
30 1 21 1949
31 1 21 1 2340
32 1 45 24 2925
33 1 59 15 6278
34 1 37 14 2183
35 1 28 5 1989
36 1 31 0 1559
37 2 56 17 2729
38 1 50 1 2027
39 1 30 10 2300
40 1 32 3 2106
41 1 33 9 2846
42 1 29 6 2534
43 2 40 12 2144
44 1 30 7 2223
45 1 55 35 2651
46 2 51 28 1989
47 2 22 21 1872
48 1 34 18 2183
49 1 27 7 2729
50 1 29 7 2340
51 2 39 10 2106
52 1 41 18 2261
53 1 44 3 1989
54 1 25 1 1559
55 2 45 17 2417
56 2 31 6 1949
57 1 61 36 3119
58 1 38 2574
59 1 20 1 2261
60 1 31 10 2144
61 1 44 19 2183
62 1 40 0 1872
63 2 51 10 1872
64 2 44 1 1715
65 2 35 17 2691
66 2 37 16 2027
67 1 37 8 5069
68 1 33 7 2417
69 1 28 1 3510
70 1 52 22 3119
71 1 34 1 2495
72 1 46 23 3470
73 2 40 2 2027
74 1 45 20 2846
75 1 40 1 1949
76 1 37 15 1598
77 1 39 22 2183
78 1 22 0 1598
79 1 33 2 1638
80 1 27 7 2612
81 1 35 16 2808
82 2 35 15 2183
Aggregate
Aggregate is new function in Excel 2010.
Aggregate can ignore hidden rows.
For example, if you hide males (sex=1) by filtering the Table then Aggregate calculates descriptives for females only (sex=2).
The first argument specifies which function to use. For example, 1 specifies Average.
See Help for Aggregate function to find relevant number for each function.
The second argument determines which values to ignore (5= ignore hidden rows).
The third argument is reference to the data values.
The fourth argument is needed for some functions like Percentile.exc.
Mean 37.95 12.18 2563.97
Standard deviation 9.77 8.81 849.38
Min 20.00 0.00 1521.00
Lower quartile 30.75 3.25 2026.80
Median 37.50 12.50 2320.20
Upper quartile 44.00 18.75 2817.45
Max 61.00 36.00 6278.40
Count 82 80 82
id sex age years in duty salary
1 1 38 22 3587
2 1 29 10 2963
3 1 30 7 1989
4 1 36 14 2144
5 1 24 4 2183
6 2 31 14 1910
7 1 49 16 2066
8 1 55 0 2066
9 1 40 23 2768
10 1 33 16 2106
11 1 39 22 2651
12 1 40 21 2846
13 1 35 15 2808
14 1 58 21 3587
15 1 53 12 3393
16 2 42 23 2691
17 1 26 2 5225
18 2 38 17 2729
19 1 42 20 2925
20 2 40 13 2457
21 2 40 20 2691
22 1 47 17 4874
23 1 44 27 3510
24 1 36 7 4446
25 1 43 1 2925
26 1 26 3 1521
27 1 26 2 1989
28 2 56 15 2223
29 1 47 23 2808
30 1 21 1949
31 1 21 1 2340
32 1 45 24 2925
33 1 59 15 6278
34 1 37 14 2183
35 1 28 5 1989
36 1 31 0 1559
37 2 56 17 2729
38 1 50 1 2027
39 1 30 10 2300
40 1 32 3 2106
41 1 33 9 2846
42 1 29 6 2534
43 2 40 12 2144
44 1 30 7 2223
45 1 55 35 2651
46 2 51 28 1989
47 2 22 21 1872
48 1 34 18 2183
49 1 27 7 2729
50 1 29 7 2340
51 2 39 10 2106
52 1 41 18 2261
53 1 44 3 1989
54 1 25 1 1559
55 2 45 17 2417
56 2 31 6 1949
57 1 61 36 3119
58 1 38 2574
59 1 20 1 2261
60 1 31 10 2144
61 1 44 19 2183
62 1 40 0 1872
63 2 51 10 1872
64 2 44 1 1715
65 2 35 17 2691
66 2 37 16 2027
67 1 37 8 5069
68 1 33 7 2417
69 1 28 1 3510
70 1 52 22 3119
71 1 34 1 2495
72 1 46 23 3470
73 2 40 2 2027
74 1 45 20 2846
75 1 40 1 1949
76 1 37 15 1598
77 1 39 22 2183
78 1 22 0 1598
79 1 33 2 1638
80 1 27 7 2612
81 1 35 16 2808
82 2 35 15 2183
Grouping
In the table below age has been grouped to age groups (20-29, 30-39,…):
● Copy age (in this example cells C11:C93) to the column just beside the table
● Sort the table on the basis of age
● Select the age values belonging to the first group in the copied age column
● Enter the label for the age group (e.g. '20-29)
● Click ctrl-enter (this takes the group label to the selected cells)
● Repeat the previous three steps for all age groups
id sex age years in duty salary age2
59 1 20 1 2261 20-29
30 1 21 1949 20-29
31 1 21 1 2340 20-29
47 2 22 21 1872 20-29
78 1 22 0 1598 20-29
5 1 24 4 2183 20-29
54 1 25 1 1559 20-29
17 1 26 2 5225 20-29
26 1 26 3 1521 20-29
27 1 26 2 1989 20-29
49 1 27 7 2729 20-29
80 1 27 7 2612 20-29
35 1 28 5 1989 20-29
69 1 28 1 3510 20-29
2 1 29 10 2963 20-29
42 1 29 6 2534 20-29
50 1 29 7 2340 20-29
3 1 30 7 1989 30-39
39 1 30 10 2300 30-39
44 1 30 7 2223 30-39
6 2 31 14 1910 30-39
36 1 31 0 1559 30-39
56 2 31 6 1949 30-39
60 1 31 10 2144 30-39
40 1 32 3 2106 30-39
10 1 33 16 2106 30-39
41 1 33 9 2846 30-39
68 1 33 7 2417 30-39
79 1 33 2 1638 30-39
48 1 34 18 2183 30-39
71 1 34 1 2495 30-39
13 1 35 15 2808 30-39
65 2 35 17 2691 30-39
81 1 35 16 2808 30-39
82 2 35 15 2183 30-39
4 1 36 14 2144 30-39
24 1 36 7 4446 30-39
34 1 37 14 2183 30-39
66 2 37 16 2027 30-39
67 1 37 8 5069 30-39
76 1 37 15 1598 30-39
1 1 38 22 3587 30-39
18 2 38 17 2729 30-39
58 1 38 2574 30-39
11 1 39 22 2651 30-39
51 2 39 10 2106 30-39
77 1 39 22 2183 30-39
9 1 40 23 2768 40-49
12 1 40 21 2846 40-49
20 2 40 13 2457 40-49
21 2 40 20 2691 40-49
43 2 40 12 2144 40-49
62 1 40 0 1872 40-49
73 2 40 2 2027 40-49
75 1 40 1 1949 40-49
52 1 41 18 2261 40-49
16 2 42 23 2691 40-49
19 1 42 20 2925 40-49
25 1 43 1 2925 40-49
23 1 44 27 3510 40-49
53 1 44 3 1989 40-49
61 1 44 19 2183 40-49
64 2 44 1 1715 40-49
32 1 45 24 2925 40-49
55 2 45 17 2417 40-49
74 1 45 20 2846 40-49
72 1 46 23 3470 40-49
22 1 47 17 4874 40-49
29 1 47 23 2808 40-49
7 1 49 16 2066 40-49
38 1 50 1 2027 50-
46 2 51 28 1989 50-
63 2 51 10 1872 50-
70 1 52 22 3119 50-
15 1 53 12 3393 50-
8 1 55 0 2066 50-
45 1 55 35 2651 50-
28 2 56 15 2223 50-
37 2 56 17 2729 50-
14 1 58 21 3587 50-
33 1 59 15 6278 50-
57 1 61 36 3119 50-
Notes
If you select a filtered Table and copy it (Copy-Paste) elsewhere then only
visible rows are copied. This way you can easily select subsets from the original data set.