A cross tabulation allows you to summarize the values in a column based on the values in two or more other columns and display the result as a matrix.

To perform a cross tabulation:

  1. In an open table or worksheet, click Analysis > Cross Tabulation...

    The Cross Tabulation dialog is presented.

  2. Enter a Title for the cross tabulation. (optional)
  3. For the rows in the resultant cross tabulation, specify all of the columns in the original table by which you would like to group the data.
    Note: All rows in the original table that have the same values for all of the columns specified here will be considered part of one group.

    For each column you want included in this group:

    1. From the Rows of Result drop-down list, select the desired column.
    2. If you want the data in this column sorted, select the order from the Sort drop-down.
    Note: To change the number of grouping columns that appear in the Cross Tabulation dialog, click View > Set Preferences and change the Number of Row Dimensions for Cross Tabulation setting under the User Interface section.
  4. For the columns in the resultant cross tabulation, specify all of the columns in the original table by which you would like to group the data.
    Note: All rows in the original table that have the same values for all of the columns specified here will be considered part of one group.

    For each column you want included in this group:

    1. From the Columns of Result drop-down list, select the desired column.
    2. If you want the data in this column sorted, select the order from the Sort drop-down.
    Note: To change the number of grouping columns that appear in the Cross Tabulation dialog, click View > Set Preferences and change the Number of Column Dimensions for Cross Tabulation setting under the User Interface section.
  5. For the column of data in the original table that you want to summarize:
    1. From the Column drop-down list, select the column you would like to summarize.
    2. From the Type of Summary drop-down list, select the type of summarization.
      Note: For the number of records per group (i.e., number of records), you don't have to specify a column.
    3. If the type of summarization you are performing requires a second column, select that column from the Reference Column drop-down list.

      See Types of Summarizations for Cross Tabulations for which calculations require a reference column.

    4. Select the Display Format from the drop-down list. (optional)
      The display format specifies how the values will be displayed in the resultant table.
      Note: If set to Default, the format is determined by the data.

      For example, dates are stored as integers in 1010data (in YYYYMMDD form). To display a date as MM/DD/YYYY, choose the appropriate display format from the drop-down.

    5. Select the Column Width from the drop-down list. (optional)
      This is the desired width (number of characters) of the columns in the resultant table.
      Note: If set to Default, the width is determined by the data.
    6. Select the Decimal Places from the drop-down list. (optional)
      This is the number of decimal places to show for numbers in the resultant table.
      Note: If set to Default, the number of decimal places is determined by the data, and may be different for each row.
  6. Select Use long column headings for results if you want the column headings in the resultant table to include the grouping columns' names in addition to the grouping columns' values.
    The default behavior is to display only the values of the columns you are grouping on in the column headers of the resultant table. When this option is selected, the column headings will also include the grouping column names.
    Note: This option only affects tables that are downloaded, for example to Microsoft Excel or as a comma-separated text file. This has no effect on the appearance of the resultant tables in the 1010data web interface.

    The following example is a cross tabulation showing the sum of sales where the rows are grouped by Transaction ID and the columns are grouped by both Store and Account.

    If you selected Use long column headings for results and downloaded the results to Excel, the column headings would contain both the column names and values (e.g., Store=1 Account=957):

    If this was not selected, the column headings would only contain the values (e.g., 1 957) :

  7. Click Submit.

The result of a cross tabulation is a dynamic table that may be manipulated just like any other table. You may download the results, select rows, sort rows, rearrange columns, create computed columns, link in other tables, and even apply another tabulation or cross tabulation.

Missing values in the data are handled automatically. For example, if you are computing the average value of a column that has some missing (N/A) values in a particular group of rows, those values are ignored when computing the average. See the descriptions of the individual types of summarizations to see how N/A values are handled for each.

You may reorder both the rows and the columns of a cross tabulation so that the row and column aggregates are in ascending or descending order. (The row and column aggregates are the numbers that appear in the gray cells.) To sort the columns in a cross tabulation, click or in the column header. To sort the rows in a cross tabulation, click or in the row header.

Note: In large cross tabulations, the meaningful data is often quite sparse. For example, suppose the original table was a phone book and we wanted to find the number of people with each last name in each ZIP code. In other words, we wanted a cross tabulation with one row for each last name and one column for each ZIP code, showing the total number of people in each category. Obviously, there may be no people with certain last names living in certain ZIP codes, so many cells will be zero. To focus in on those cells that do have meaningful data, we can sort both the row and column totals in descending order. This will have the effect of bringing the rows and columns with the largest numbers to the upper left.

Example

The following table is the result of a cross tabulation that calculates the sum of sales for each unique grouping of both Department and Transaction ID. The resultant table is a matrix with the Transaction ID as the Rows of Results and Department as the Columns of Results.

In this example, you can see that transaction ID 538 consisted of $5.30 spent in department 22 and $1.65 in department 29 for a grand total of $6.95, which can be found in the first column of that row (in gray). You can also see the total amount spent in department 22 from all transactions, which is $24.30, by looking at the first row in the column for that department (in gray).