Docstoc

table

Document Sample
table Powered By Docstoc
					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
  Sales in euros




                   16000
                   15000                                                                        Carl
                   14000
                                                                                                Lisa
                   13000
                   12000                                                                        Henry
                   11000
                   10000
                                                   Ferbruary




                                                                           April




                                                                                    May
                                                                 March
                                  January




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.

				
DOCUMENT INFO
Categories:
Tags:
Stats:
views:3
posted:8/1/2012
language:English
pages:12