How to use Microsoft Excel data tables to analyze information in a database

View products that this article applies to.

This article was previously published under Q282851

SUMMARY

This article describes how to use Microsoft Excel tables to analyze information contained in a database.

Back to the top

You can use database functions in one-input (http://support.microsoft.com/kb/q282855/) and two-input (http://support.microsoft.com/kb/q282856/) tables to analyze values obtained from a database, by using both comparison and computed criteria.

Back to the top

Comparison Criteria

Comparison criteria are the most commonly used criteria to extract or analyze information from a Microsoft Excel database. The value that you place under the column heading in your criteria range is compared against the records in your database. If a record matches that value, the record is extracted or included in the group of records to be analyzed by the database functions.

To create a sample database and a sample criteria range, follow these steps:

Open a new workbook.

Type the following information in cells A1:C25 of a new worksheet:

      |       A      |   B     |   C
   ---|--------------|---------|--------
    1 | Type of Soda |Month    |Consumed
    2 | Pepup        |January  |     946
    3 | Diet Pepup   |January  |     762
    4 | Colo         |January  |     224
    5 | Diet Colo    |January  |       1
    6 | Splash       |January  |     715
    7 | Diet Splash  |January  |     506
    8 | Lime-Up      |January  |     354
    9 | Diet Lime-Up |January  |     542
    10| Pepup        |February |     910
    11| Diet Pepup   |February |     894
    12| Colo         |February |     926
    13| Diet Colo    |February |     471
    14| Splash       |February |     493
    15| Diet Splash  |February |     276
    16| Lime-Up      |February |      45
    17| Diet Lime-Up |February |     301
    18| Pepup        |March    |     840
    19| Diet Pepup   |March    |     442
    20| Colo         |March    |     409
    21| Diet Colo    |March    |     205
    22| Splash       |March    |     109
    23| Diet Splash  |March    |     263
    24| Lime-Up      |March    |     603
    25| Diet Lime-Up |March    |     555

Select cells A1:C25.
On the Insert menu, point to Name, and then click Define.
Type Database, and then click OK.

For the sample criteria range, type the following data in cells E1:G1 of the worksheet:

      |       E      |  F   |   G
   ---|--------------|------|--------
    1 | Type of Soda |Month |Consumed
    2 |              |      |

Select cells E1:G2.
On the Insert menu, point to Name, and then click Define.
Type Criteria, and then click OK.

In a One-Input Table

To find the cost of soda consumed per type the entire period, create a one-input table that uses the data from the database:

In cells E5:E12, type the different kinds of soda. (Because you enter this variable data in a column, this is a column input table).

NOTE: You can copy the types from the database and paste them into the cells.
In cell F4, type the formula:
=DSUM(Database,"Consumed",Criteria)*0.45
NOTE: This formula adds all the consumed sodas in the database that match the specified criteria, and multiplies the result by 45 cents (the cost per can).
Select cells E4:F12.
On the Data menu, click Table.
In the Column Input Cell box, type E2.

NOTE: E2 is the cell in the criteria range where you would type the name of a specific type of soda. Because you want to substitute different types of soda to calculate the expense for each type, leave cell E2 blank in the actual criteria. The table automatically (internally) substitutes each soda type that is listed in the table (E4:E12) into cell E2, and calculates the formula based on that criteria.

One-Input table with data from database (with formulas displayed)
=================================================================

      |       E       |                    F
   ---|---------------|----------------------------------------
    4 | First Quarter |=DSUM(Database,"Consumed",Criteria)*0.45
    5 | Pepup         |=TABLE(,E2)
    6 | Diet Pepup    |=TABLE(,E2)
    7 | Colo          |=TABLE(,E2)
    8 | Diet Colo     |=TABLE(,E2)
    9 | Splash        |=TABLE(,E2)
    10| Diet Splash   |=TABLE(,E2)
    11| Lime-Up       |=TABLE(,E2)
    12| Diet Lime-Up  |=TABLE(,E2)


One-Input table with data from database (with values displayed)
===============================================================

      |       E       |           F
   ---|---------------|------------------------
    4 | First Quarter |Money Spent on Beverages
    5 | Pepup         |               $1,213.20
    6 | Diet Pepup    |                 $944.10
    7 | Colo          |                 $701.55
    8 | Diet Colo     |                 $304.65
    9 | Splash        |                 $592.65
    10| Diet Splash   |                 $470.25
    11| Lime-Up       |                 $450.90
    12| Diet Lime-Up  |                 $629.10

The value displayed in cell F4 is a number format. To duplicate this value, follow these steps:

Select cell F4.
On the Format menu, click Cells.
Click the Number tab.
In the Category list, click Custom.
In the Type box, type "Money Spent on Beverages" (with the quotation marks).
Click OK.

In a Two-Input Table

For the following example, use the sample database and criteria that you created earlier. To create a two-input table and use it to find the cost of soda consumed per type per month, follow these steps:

In cells E15:E22, type the different types of soda. (This represents the column input.)

NOTE: You can copy the types from the database and paste them into the cells.
Type January in cell F14, type February in cell G14, and type March in cell H14.
In cell E14, type the formula:
=DSUM(Database,"Consumed",Criteria)*0.45
NOTE: This formula adds all the consumed sodas in the database based on the criteria, and multiplies the total by 45 cents (cost per can).
Select cells E14:H22.
On the Data menu, click Table.
In the Row Input Cell box, type F2. In the Column Input Cell box, type E2.

NOTE: Because you want to calculate the expenses for each type of soda for each month, and do not want to limit your expense analysis to one particular month, leave F2 blank in the defined criteria range. F2 is the cell in the criteria range where you would type the name of a specific month. The table automatically (internally) substitutes each month that is listed in the table (F14:H14) into cell F2, and calculates the formula based on that month.

Because you want to calculate the expense for each type of soda, leave E2 blank in the actual criteria. E2 is the cell in the criteria range where you would type the name of a specific type of soda. For example, if you wanted to calculate the expense for your diet sodas, you would place the word "diet" in cell E2. The table automatically (internally) substitutes each soda type that is listed in the table (E15:E22) into cell E2 and calculates the formula based on that type.

Two-input table with data from database (with formulas displayed)
=================================================================

NOTE: Due to screen display limitations, the following four-column table is 
shown in two parts.

(Left column of a four-column table.)

      |                    E
   ---|-----------------------------------------
    14| =DSUM(Database,"Consumed",Criteria)*0.45
    15| Pepup
    16| Diet Pepup
    17| Colo
    18| Diet Colo
    19| Splash
    20| Diet Splash
    21| Lime-Up
    22| Diet Lime-Up

(Right three columns of a four-column table.)

      |       F       |      G       |      H
   ---|---------------|--------------|-------------
    14| January       |February      |March
    15| =TABLE(F2,E2) |=TABLE(F2,E2) |=TABLE(F2,E2)
    16| =TABLE(F2,E2) |=TABLE(F2,E2) |=TABLE(F2,E2)
    17| =TABLE(F2,E2) |=TABLE(F2,E2) |=TABLE(F2,E2)
    18| =TABLE(F2,E2) |=TABLE(F2,E2) |=TABLE(F2,E2)
    19| =TABLE(F2,E2) |=TABLE(F2,E2) |=TABLE(F2,E2)
    20| =TABLE(F2,E2) |=TABLE(F2,E2) |=TABLE(F2,E2)
    21| =TABLE(F2,E2) |=TABLE(F2,E2) |=TABLE(F2,E2)
    22| =TABLE(F2,E2) |=TABLE(F2,E2) |=TABLE(F2,E2)


Two-input table with data from database (with values displayed)
===============================================================

      |       E        |    F   |    G    |   H
   ---|----------------|--------|---------|--------
    14| Cost per Month |January |February |March
    15| Pepup          |$425.70 | $409.50 | $378.00
    16| Diet Pepup     |$342.90 | $402.30 | $198.90
    17| Colo           |$100.80 | $416.70 | $184.05
    18| Diet Colo      |  $0.45 | $211.95 |  $92.25
    19| Splash         |$321.75 | $221.85 |  $49.05
    20| Diet Splash    |$227.70 | $124.20 | $118.35
    21| Lime-Up        |$159.30 |  $20.25 | $271.35
    22| Diet Lime-Up   |$243.90 | $135.45 | $249.75

The value displayed in cell E14 is a number format. To duplicate this value, follow these steps:

Select cell E14.
On the Format menu, click Cells.
Click the Number tab.
In the Category list, click Custom.
In the Type box, type "Cost per Month" (with the quotation marks).
Click OK.

Back to the top

Computed Criteria

You can also use computed criteria in one-input and two-input tables to obtain and analyze values from a database. Computed criteria use a formula to extract or obtain values for analysis.

When you use computed criteria, be aware of the following:

The field name of the computed criteria must be a label other than a field name in the database (or you can leave it blank). In the example, cell H1 is blank; it could contain the word "month" or "formula" or any other text, as long as it is not the name of a field in your database.
In the formula that uses the computed criteria, you must use a relative reference to the first record in the field of the database that you want to reference. In the following example, the formula contains a relative reference to cell B2 in the formula =MONTH(B2)=MONTH($H$3).
In most cases, any other references in the computed criteria must be absolute. In the following example, the formula contains an absolute reference to cell H3 in the formula =MONTH(B2)=MONTH($H$3).

For the following examples, you must create a sample database and a sample criteria range. To create a sample database, type the following information in cells A1:C15 of a new worksheet:

      |    A      |    B    |     C
   ---|-----------|---------|------------
    1 | Product # |Date     |Amount Sold
    2 |       9865|   1/2/90|          91
    3 |       9870|  1/12/90|          94
    4 |       9875|  1/22/90|          76
    5 |       9880|   2/1/90|          22
    6 |       9865|  2/11/90|          82
    7 |       9870|  2/21/90|          71
    8 |       9870|   3/3/90|          50
    9 |       9865|  3/13/90|          35
    10|       9880|  3/23/90|          54
    11|       9875|   4/2/90|          80
    12|       9865|  4/12/90|          33
    13|       9880|  4/22/90|          83
    14|       9875|   5/2/90|          62
    15|       9870|  5/12/90|          15

Follow these steps to define the database name and set a criteria:

Select cells A1:C15.
On the Insert menu, point to Name, and then click Define.
Type Database, and then click OK.
Select cells E1:H2.
On the Insert menu, point to Name, and then click Define.
Type Criteria, and then click OK.

      |     E     |  F  |      G      |          H
   ---|-----------|-----|-------------|---------------------
    1 | Product # |Date |Amount Sold  |
    2 |           |     |             |=MONTH(B2)=MONTH($H$3)

The formula =MONTH(B2)=MONTH($H$3) returns a value of either TRUE or
FALSE, which is displayed in H2:

      |     E     |  F  |      G      |    H
   ---|-----------|-----|-------------|---------
    1 |Product #  |Date |Amount Sold  |
    2 |                                  TRUE

In a One-Input Table

If you want to find out how many items were sold each month, how many days a sale was made, and the maximum number of items sold on one day in each month, create a one-input table from this data, as follows:

Type the following data in cells E6:E10:
```
      |     E     
   ---|-----------
    6 |     1/1/90
    7 |     2/1/90
    8 |     3/1/90
    9 |     4/1/90
   10 |     5/1/90
						
```
NOTE: If you only want the name of the month to be displayed in the table (as in the following example), change the number format of cells E6:E10. To do this, click Cells on the Format menu, click Custom in the Category list, and then type mmmm in the Type box. With this format, E6 is displayed as January, E7 is displayed as February, and so on.
In cell F5, type the formula:
=DSUM(Database,"Amount Sold",Criteria)
In cell G5, type the formula:
=DCOUNT(Database,,Criteria)
In cell H5, type the formula:
=DMAX(Database,"Amount Sold",Criteria)
Select cells E5:H10.
On the Data menu, click Table.
In the Column Input Cell box, type H3.

NOTE: Cell H2 contains the formula =MONTH(B2)=MONTH($H$3). This formula checks to see if the month in the first record of the Date field (B2) equals the month of cell H3 (cell H3 is the column input cell). The table automatically (internally) substitutes each month listed in the table (E5:E10) into cell H3, and calculates the formulas based on that month.


One-Input table with computed criteria (with formulas displayed)
================================================================

NOTE: Due to screen display limitations, the following four-column table is 
shown in two parts.

(Left two columns of a four-column table.)

      |   E    |                  F
   ---|------- |--------------------------------------
    5 |        |=DSUM(Database,"Amount Sold",Criteria)
    6 | 1/1/90 |=TABLE(,H3)
    7 | 2/1/90 |=TABLE(,H3)
    8 | 3/1/90 |=TABLE(,H3)
    9 | 4/1/90 |=TABLE(,H3)
    10| 5/1/90 |=TABLE(,H3)

(Right two columns of a four-column table.)

      |               G             |           H
   ---|-----------------------------|-------------------------------------
    5 | =DCOUNT(Database,,Criteria) |=DMAX(Database,"Amount Sold",Criteria)
    6 | =TABLE(,H3)                 |=TABLE(,H3)
    7 | =TABLE(,H3)                 |=TABLE(,H3)
    8 | =TABLE(,H3)                 |=TABLE(,H3)
    9 | =TABLE(,H3)                 |=TABLE(,H3)
    10| =TABLE(,H3)                 |=TABLE(,H3)



One-Input table with computed criteria (with values displayed)
==============================================================

      |    E    |      F      |      G      |     H
   ---|---------|-------------|-------------|----------
    5 |         | Total Amount| # of Entries| Max Entry
    6 | January |          261|            3|        94
    7 | February|          175|            3|        82
    8 | March   |          139|            3|        54
    9 | April   |          196|            3|        83
    10| May     |           77|            2|        62

The values displayed in cells F5:H5 are number formats. To duplicate these values, follow these steps:

Select cell F5.
On the Format menu, click Cells.
Click the Number tab.
In the Category list, click Custom.
In the Type box, type "Total Amount" (with the quotation marks).
Click OK.
Repeat steps 1-5 with cells G5 and H5. In step 5, type the formats as "# of Entries" and "Max Entry", respectively (with the quotation marks).

In a Two-Input Table

If you want to find how many items were sold each month for each product number, you can create a two-input table from this data, as follows:

Type the following data in cells E13:E17 of column E:
```
      |     E     
   ---|-----------
   13 |     1/1/90
   14 |     2/1/90
   15 |     3/1/90
   16 |     4/1/90
   17 |     5/1/90
						
```
NOTE: If you want only the name of the month to be displayed in the table (as in the following example), change the number format of cells E13:E17 to mmmm. To do this, click Cells on the Format menu, click Custom, and then type mmmm in the Type box. With this format, E13 is displayed as January, E14 is displayed as February, and so on.

Type the following product numbers in cells F12:I12 of row 12:

      |  F  |  G  |  H  |  I
   ---|-----|-----|-----|-----
   12 | 9865| 9870| 9875| 9880
   13 |

In cell E12, type the following formula:
=DSUM(Database,"Amount Sold",Criteria)
Select cells E12:I17.
On the Data menu, click Table.
In the Row Input Cell box, type E2, and in the Column Input Cell box, type H3.

NOTE: Because you want the total number of each product sold broken down by each month, leave E2 blank in the defined criteria range. E2 is the cell in the criteria range where you would type a specific product number. The table automatically (internally) substitutes each product number in the table (F12:I12) into cell E2, and calculates the formula based on that product.

Cell H2 contains the formula =MONTH(B2)=MONTH($H$3). This formula checks to see if the month in the first record of the Date field (B2) equals the month of cell H3, which is the column input cell. Remember, the table automatically (internally) substitutes each month in the table (E13:E17) into cell H3, and calculates the formulas based on that month.

Two-Input table with computed criteria (with formulas displayed)
================================================================

Due to screen display limitations, the following five-column table is shown 
in two parts.

(Left two columns of a five-column table.)

      |                    E                   |      F
   ---|----------------------------------------|-------------
    12| =DSUM(Database,"Amount Sold",Criteria) |9865
    13| 1/1/90                                 |=TABLE(E2,H3)
    14| 2/1/90                                 |=TABLE(E2,H3)
    15| 3/1/90                                 |=TABLE(E2,H3)
    16| 4/1/90                                 |=TABLE(E2,H3)
    17| 5/1/90                                 |=TABLE(E2,H3)

(Right three columns of a five-column table.)

      |       G       |      H       |      I
   ---|---------------|--------------|-------------
    12|           9870|          9875|         9880
    13| =TABLE(E2,H3) |=TABLE(E2,H3) |=TABLE(E2,H3)
    14| =TABLE(E2,H3) |=TABLE(E2,H3) |=TABLE(E2,H3)
    15| =TABLE(E2,H3) |=TABLE(E2,H3) |=TABLE(E2,H3)
    16| =TABLE(E2,H3) |=TABLE(E2,H3) |=TABLE(E2,H3)
    17| =TABLE(E2,H3) |=TABLE(E2,H3) |=TABLE(E2,H3)


Two-Input table with computed criteria (with values displayed)
==============================================================

      |     E    |  F  |  G  |  H  |  I
   ---|----------|-----|-----|-----|----
    12|          | 9865| 9870| 9875|9880
    13| January  |   91|   94|   76|   0
    14| February |   82|   71|    0|  22
    15| March    |   35|   50|    0|  54
    16| April    |   33|    0|   80|  83
    17| May      |    0|   15|   62|   0

Back to the top