Survey processing: breakdowns – the tables by groups

1. Loading the data

Paste columns directly from the sheet (including the header row) – select them in Excel, copy, and paste here:

When to use?

After the tables report of survey processing has answered "how many?", the breakdown answers "among whom?": does satisfaction differ between men and women, between age groups, between regions of residence? In research institutes this is the "breakdown report" – the same questions, the same rows, but instead of one Total column there is a column for each subgroup (the "banner", the column headers). In the old scripts this was /TABLE= q + $t000001 BY kk: kk is the column-header variable, cpct – column percentage, count 'N' – the base row. The page produces exactly this table, and adds what the scripts did not do: a chi-square test for each question × background variable, Cramér's V and marking of the cells that deviate from the expected.

The page suits any closed question that has groups to compare: single response (the regular table), multiple response (percentage of respondents in each group), an item battery (mean per item in each group) and a continuous variable (mean, SD and median in each group). What it does not do – explain why the groups differ; for that a model is needed (see Common mistakes).

The workflow – from survey processing to breakdowns

1. Start on the survey processing page. There, build the dictionary, check the data, recode (age groups – a column header cannot be built from continuous age), filter and weight – and download "the file with the updated dictionary". Its "settings" sheet (settings) saves everything.
2. Load this file here. The dictionary, the recoding, the filtering, the weighting and the percentage base load automatically (the message "Settings saved in the file"). You can also load a regular file with a legend and write the preparation in section 3, or use "Load settings from a file" (settings.json from the research package).
3. Choose column headers (section 4) – by default the background variables, including derived ones such as age group.
4. Produce. Table 1 is the size of each group (to see small columns in advance); after it a table for each question; at the end – the summary table of the tests, the report sentences and the reading of the result.
5. Download Word or HTML, and the syntax (SPSS) or the script (R) that reproduce the numbers. The settings of this page (the column headers and the options) are saved in the file downloaded from "Download the file with the dictionary and settings" – load it again and the report is restored.

The column headers – what makes a variable good columns?

Few categories, many respondents in each. The page offers categorical or ordinal variables with 2-12 categories. Continuous age, income or years of residence are not offered – group them first by recoding (Section 3; Pallant, 2020, Chapter 8 – Recode into Different Variables), and the derived variable joins the list automatically. The practical rule of research institutes: a column with fewer than 30 respondents is reported with caution – a percentage based on 20 people moves by 5 points with each respondent. Table 1 shows the size of each group before the tables are read, and every table note mentions small columns.

Who is not in the columns. A respondent who did not answer the background question (blank), or answered "don't know/refuse" (a code marked in the dictionary as a non-substantive response), does not belong to any group – they are included in the Total column and not in the columns. A value that is not in the code list (e.g., 3 in a sex variable coded 1/2) is almost always an entry error: by default it too stays in the Total only and is marked in red in Table 1 and in the reading of the result; those who want to see it as a column (as SPSS does) check "A column also for a value not in the dictionary". This also changes the test: a column of one respondent adds a row of degrees of freedom and near-zero expected cells.

Background × background. By default the background variables are also broken down by each other (sex by age group, education by region) – these tables often explain a finding "by age" that is really "by education". A variable is not broken down by itself.

How to read the table?

Each cell is a column percentage: of the respondents in the column, how many chose the answer in the row. The columns add up to 100 (the "Total" row); the comparison is across – between the columns, and against the Total column. Those who also want to know how many people stand behind the percentage choose "Percentage and n in parentheses"; in any case the n row at the bottom gives the base of each column – the number of respondents with a substantive answer (in the default base) in the group. In a weighted report a "weighted base" row is added.

The Total column is the table from the survey processing page – all respondents, including those who are not in any column (blank or "refuse" in the background question). Therefore the sum of the columns' n may be smaller than the Total's n; the table note says by how much. (In SPSS, CROSSTABS shows Total only for those with a value on both variables – see below.)

The base is the same base as in survey processing – "respondents with a substantive answer" by default: "don't know" and blanks are outside the percentages and reported in the note; "everyone who answered" brings "don't know" in as a row; "all respondents" adds a "no answer" row. A question asked of only some of the respondents ("asked if") is computed on the eligible in each column. Three things to look at before going on: rows with 0 in all columns (a category nobody chose – shown by default, can be hidden), a column without a label (a value not in the dictionary), and a small base.

The test: chi-square test of independence and Cramér's V

For each question × background variable the page runs Pearson's chi-square test of independence (Field, 2024, Section 19.3; Pallant, 2020, Chapter 16): does the distribution of the answers differ between the columns, or are the differences seen in the table what is expected in a random sample when there is no relationship? The expected frequency in each cell derives from the table margins alone, and the statistic sums the discrepancies between observed and expected. The test runs on the counts of the substantive answers only – without "don't know", without blanks – and an empty row or column is dropped from it (as in SPSS, where a category without cases is not in the table). The test line in each table note: χ²(df, N) = …, p = …, Cramér's V = …, and the summary table at the end of the report gathers them all.

The condition. The test is approximate, and the approximation is good when the expected frequencies are not too small: in a 2×2 table no expected cell below 5; in a larger table – all cells above 1 and no more than 20% below 5 (Field, 2024, Section 19.5.2). When the condition is violated the page says so in the note ("the test is approximate") and in the summary table; the usual remedy in a breakdown is to collapse categories (rows or columns) by recoding. In a small 2×2 table report Fisher's exact test – on the chi-square page.

Strength, not only significance. In a large sample even a difference of a few percentage points comes out significant; significance says there is a relationship, not how strong it is (Field, 2024, Section 19.5.3). Cramér's V – √(χ²/(N·df*)), df* = min(r − 1, c − 1) (Gravetter & Wallnau, 2017, Equation 17.9) – ranges from 0 to 1 and does not depend on sample size. Cohen's cutoffs for effect size: .10 small, .30 medium, .50 large (Cohen, 1988, as cited in Pallant, 2020, and in Gravetter & Wallnau, 2017) – and in a larger table the cutoffs for V are lower, according to df* (Gravetter & Wallnau, Table 17.10); the page translates: "small/medium/large effect" in the note and in the report sentence according to w = V·√df*, as on the chi-square page.

Many tests. A typical breakdown report runs dozens of tests (questions × background variables), and in every 20 tests one is expected to come out significant by chance even when there is no relationship at all. Therefore the page reports how many tests were run and how many came out significant, and mentions this in the reading of the result. The right interpretation is a pattern: a relationship that recurs in several related questions, with a V that is not negligible – and not a single comparison that crossed .05. Those who must use a corrected threshold divide .05 by the number of tests (Bonferroni) and mark in the summary table only what passes it.

▲ ▼ – which cell makes the difference?

A significant test says there is a relationship, not where. For each cell the page computes an adjusted standardized residual (SPSS's Adjusted Residual): the discrepancy between observed and expected, standardized also for the size of the row and column, so that it is approximately distributed as z. A cell above 1.96 – more than expected under independence (▲); below −1.96 – fewer (▼) (Field, 2024, section 19.8 – the standardized residual is a z-score, and the interval ±1.96; Pallant, 2020, chapter 16 – the cutoff of 2 for the adjusted residual, "a kind of post hoc test"). The marks appear only when there is a test, and also when it is not significant – then they point to a cell that deviates in a table that overall does not differ from the expected; read them as a direction, not as an independent finding. The residuals themselves, the expected value for each cell and the rest of the detail are available for a single table on chi-square.

Multiple response, batteries and continuous variables by group

Multiple response. A row for each option, cell = the percentage of respondents in the column who checked it (Percent of Cases in MULT RESPONSE); the column total exceeds 100. The base row – respondents who answered (checked at least one option) in each group, among the eligible. There is no single test for such a table; each option is a 0/1 variable, and those who want a test run chi-square for option × group on the chi-square page.

Batteries. A row for each item, cell = the mean of the codes (e.g., 1-5) in the group and the item's N in parentheses – "don't know" outside the mean, as in survey processing. Site convention: for a Likert scale the mean is a summary measure only; a difference between groups is tested with analysis of variance (or Kruskal-Wallis), and for two groups with a t-test.

Continuous variables. Mean, standard deviation (with denominator n − 1), median, minimum-maximum and N for each group – the "description by group" table of the method chapter. Here too the test is on the inference pages.

Weighting in the breakdown

When the file was weighted in survey processing (the cell components load automatically), the percentages in each column are weighted and a "weighted base" row joins the n row. The workflow rule applies here too: show the actual n without weight, and the weighted base next to it – "the sum of the weights should not be called a number of respondents".

The test on the actual counts. Chi-square assumes that each observation is one respondent; running it on weighted counts (as SPSS does after WEIGHT BY) treats a weight of 1.7 as if it were 1.7 people, and in a survey where the weights inflate a small group – it inflates the significance. Therefore the page computes χ², V and the residuals on the unweighted counts, and notes this in every note; the syntax it downloads produces the weighted percentages and the tests in two blocks (WEIGHT BY / WEIGHT OFF). A test that properly accounts for the weights (Rao-Scott, design effect correction) is not on this page – see What the page does not do.

Reporting

The tables are built according to APA: table number and title above, a spanner heading for each group of columns (the background variable) and subheadings for the categories, a stub column for the answers, a Total row and an n row, and a general note below explaining the base and the test (APA, 2020, Sections 7.14 and 7.21). In Word the table wraps into rows when it is wide – a report for delivery chooses 2-3 column headers per table, or produces the same question several times with different column headers. Numbers with two decimals and without a leading zero for V and p (APA, 2020, Section 6.36); in the text – χ²(df, N = n) = x.xx, p = .xxx, V = .xx (ibid., section 6.43). The report sentences that the page composes (in English) say what the breakdown was by, how many tests were run, which crosstabulations are significant – each with the statistic, the effect size and the salient gap between two groups – and what was not found. In the findings chapter cite the table ("Table 4") and the sentence; for the rest – the reporting rules and the findings chapter generator.

What the page does not do (yet)

• A test for multiple response by group – there is no single statistic for such a table; for each option – a separate chi-square.
• Significance letters between columns (column proportions test – A, B, C next to the percentage, as in CTABLES; Field, 2024, section 19.8) – no; there are ▲/▼ by residuals against the expected, not a pairwise comparison between every two columns.
• Breakdown by two variables at once (subcells: young men, older men…) – no; create a combined variable by recoding or in response coding, and it becomes a column header.
• Weighted tests (Rao-Scott, standard errors with a design effect) and confidence intervals for percentages – no; the test is on the actual counts.
• Correction for multiple tests (Bonferroni, FDR) – not automatic; the summary table gives all the p values, and the researcher decides.
• Charts for the breakdown – not in this version.
• Merging files – on the survey processing page; merge there, download the file, and load it here.

And how do you do it in SPSS?

Analyze › Descriptive Statistics › Crosstabs: the question in Row(s), the background variable in Column(s); under Cells… check Column under percentages and Adjusted standardized under residuals; under Statistics… – Chi-square and Phi and Cramer's V (Pallant, 2020, Chapter 16; Field, 2024, Chapter 19). In the output: % within [background] is the page's column percentage; Pearson Chi-Square, df and Asymptotic Significance – the test line; Cramer's V – in Symmetric Measures. The syntax the page downloads does all this: CROSSTABS /TABLES=questions BY column-headers /CELLS=COUNT COLUMN /STATISTICS=CHISQ PHI – one command for all the regular questions, plus TEMPORARY + SELECT IF for a conditional question; before it the dictionary commands (MISSING VALUES for "don't know" – so that in the background variable too they are not a column), the recoding, the filtering and the weighting, and after it MULT RESPONSE … /TABLES=$group BY background /CELLS=COLUMN for multiple response and MEANS for batteries and continuous variables.

Three differences to know: (1) the Total column in CROSSTABS includes only respondents with a value on both variables, whereas the Total column on the page is all the respondents to the question – when there are blanks in the background variable SPSS's total is smaller. (2) A value not in the dictionary is a column in SPSS; when the option is off on the page, the syntax sets it to SYSMIS in RECODE. (3) After WEIGHT BY SPSS's test is weighted too – the syntax runs the tests after WEIGHT OFF, as on the page. And the old scripts? TABLES … BY kk with cpct produced this table without a test; TABLES no longer exists, and CROSSTABS (or CTABLES) is its replacement.

And how do you do it in R?

Without special packages: xtabs(w ~ x + g) for counts (weighted when there is a weight), prop.table(tab, 2) for column percentages, chisq.test(tab, correct = FALSE) – without Yates's correction, as SPSS's default for Pearson – and $stdres for the adjusted residuals; Cramér's V is computed manually from the statistic. The script the page downloads ("Download R script") reads the file with readxl, applies recoding, filtering and weighting, defines a function xt() that excludes NA, "don't know" codes and values not in the dictionary, and prints for each question × column header the column percentages, n, the test and the residuals – plus a line for each multiple response, battery and continuous variable. The site's rule for Hebrew in R: a UTF-8 file and a UTF-8 locale (readxl will not find a sheet with a Hebrew name in the "C" locale), every comment line starts with an English word. The script was run in R 4.3 on the weighted sample file and gave the page's numbers (the verification README).

Common mistakes

Reading row percentages as column percentages. "60% of the satisfied are women" is not "60% of women are satisfied". The table here is column percentages – of the group, how many answered this way – and this is the right comparison between groups of different sizes.

Getting excited about a difference in a column of 15 people. 40% vs. 27% can be 6 vs. 4 respondents. Table 1 and the n row are there for this; collapse categories or report the numbers.

Counting significant results without counting tests. 3 significant crosstabulations out of 60 are about what is expected by chance. Report "out of 60", and interpret a pattern.

Skipping the condition. A small p in a table where most expected cells are below 5 is not worth much; the table marks "condition" – collapse and run again.

Significant = large. With 1,000 respondents V = .06 comes out significant; this is a negligible relationship. V before p.

Interpreting a breakdown as a cause. "Older people are less satisfied with the digital services" – or they use them less. A background × background table and logistic regression check who explains whom.

Forgetting who is not in the columns. 20 "refusers" on the income question are not in any income column but are in the Total – and if they are different, the Total is not the average of the columns. The table note says how many they are.

References

American Psychological Association. (2020). Publication manual of the American Psychological Association (7th ed.). https://doi.org/10.1037/0000165-000

Field, A. (2024). Discovering statistics using IBM SPSS statistics (6th ed.). Sage.

Gravetter, F. J., & Wallnau, L. B. (2017). Statistics for the behavioral sciences (10th ed.). Cengage Learning.

Pallant, J. (2020). SPSS survival manual: A step by step guide to data analysis using IBM SPSS (7th ed.). Routledge.

According to the site's rule, the list of sources includes only sources that were read in the original, and in the sections already verified on the chi-square page: Field – chapter 19 (sections 19.3, 19.5.2, 19.5.3, 19.8), Pallant – chapters 8 and 16, Gravetter and Wallnau – section 17.4, equation 17.9 and Table 17.10, APA – sections 6.36, 6.43, 7.14 and 7.21. Cohen (1988) is cited as cited in Pallant and in Gravetter and Wallnau. The workflow and the TABLES scripts are the site's working documents and are not published sources. The computation on the page is verified against Python (pandas, scipy) on the sample file – every percentage, every base, every χ², p, V and residual flag, in all three bases, without decimals, with and without columns for values not in the dictionary, in multiple response, in a battery, in a continuous variable and in the weighted script (recoding, filtering, weighting) – and against PSPP (CROSSTABS) and R (chisq.test, prop.table, stdres); see the verification README.