Building an Analysis Workbook
One sheet holds the data, one holds a sorted copy, and eight hold a question each. It is a good structure and it survives inspection nearly everywhere — the exceptions being one chart, and one absence no structure can compensate for.
The shape of the workbook
This workbook belongs to one examined master's survey project and it is the library's first. It is 103 KB, ten sheets, eight pivot tables, eight charts and zero formulas in any cell of any sheet. Its own file properties record it as created on 27 December 2021 and last modified two days later.
THE TEN SHEETS, IN ORDER
| # | Sheet | What it holds | Pivot | Chart |
|---|---|---|---|---|
| 1 | Survey Data | A1:H51 — the complete dataset, 8 columns by 50 rows | — | — |
| 2 | Demographic Questions | A1:D204 — questions 1-4 only, the same 50 rows re-sorted, plus five unexplained rows at 200-204 | — | — |
| 3 | Age Demographics | Question 1 counts, A3:B7 | From sheet 2, A1:D51 | 3-D clustered bar |
| 4 | Gender Demographics | Question 2 counts, A3:B6 | From sheet 2, A1:D51 | 3-D clustered bar |
| 5 | Experience | Question 3 counts, A3:B8 | From sheet 2, A1:D51 | 3-D clustered bar |
| 6 | Size of Enterprise | Question 4 counts, A3:B8 | From sheet 2, A1:D51 | 3-D clustered bar |
| 7 | Importance of Change Management | Question 5 counts, twice | From sheet 1, E1:E51 | 3-D bar with value labels, reading the typed block |
| 8 | Change Management Model | Question 6 counts, A3:B8 | From sheet 1, A1:H51 | 3-D pie with percentage labels |
| 9 | Change Management Process | Question 7 counts, A3:B8 | From sheet 1, A1:H51 | 3-D pie with percentage labels |
| 10 | Communication Preference | Question 8 counts, A3:B8 | From sheet 1, A1:H51 | 3-D pie with percentage labels |
Sheet 2's five extra rows are examined separately at data screening: a worked decision. Every pivot source range stops at row 51.
- Exported responses
- Dataset sheet
- One sheet per question
- Pivot table
- Chart
- Figure in the thesis
That is the whole architecture, and the thesis describes it accurately. Its analysis section says the work used "data visualisation with pivot tables, and various charts including bar, column and pie charts". Eight pivot tables: confirmed. Bar, column and pie: confirmed, in those three kinds. It is worth saying plainly, because method chapters that describe an analysis you can then verify are not common — this one can be checked against the file and it holds.
Why one question per sheet is a good structure
The design is simple enough to describe in a sentence and it does four useful things at once. If you are building your first analysis workbook, this is a structure worth starting from.
What the structure buys
One figure, one sheet
Each published figure has exactly one home. A reader checking a number opens one sheet and sees the counts and the chart that came from them, without navigating anything else.
The range is declared
Every pivot table names the sheet and cells it reads. That makes the base of every tabulation checkable, and it is what allowed the count in this project to be verified independently.
The dataset is never edited
Sheet 1 is entered once and read by everything downstream. Analysis happens on other sheets, so a mistake in a tabulation cannot damage the data.
Eight questions, eight sheets
The workbook's structure mirrors the instrument's. Anyone holding both can move between a question, its counts and its figure without a key or a map.
THE EIGHT TABULATIONS, EVERY COUNT VERIFIED AGAINST THE RAW DATA
| Question | Counts | Base |
|---|---|---|
| 1 — Age | 18-30: 19 · 31-40: 17 · 41-50: 8 · >50: 6 | 50 |
| 2 — Gender | Female 22 · Male 23 · Other 5 | 50 |
| 3 — Experience | 0-5: 17 · 5-10: 12 · 10-20: 11 · 20-40: 5 · >40: 5 | 50 |
| 4 — Enterprise size | <10: 10 · (10-50): 7 · 50-100: 9 · 100-250: 16 · >250: 8 | 50 |
| 5 — Importance | Fairly Important 7 · Important 5 · Must be implemented 21 · Not Important 3 · Very Important 14 | 50, and the only sheet printing its total |
| 6 — Preferred model | Seven-component 18 · individual-adoption 11 · eight-step 11 · three-step 7 · curve 3 | 50 |
| 7 — Process priority | Stakeholder engagement 15 · Leadership Alignment 13 · Employee communication and training 11 · Change readiness 6 · Organizational structure & design 5 | 50 |
| 8 — Communication | Need and process 12 · Feedback 12 · Action plans 11 · Influencing role 8 · Benefit 7 | 50 |
Every figure is this study's own result, verified against its raw data; none is a benchmark or a norm. The workbook's own pivot tables list most of these categories alphabetically. Question 6's five options are named published models, identified here by structure under this library's naming rule.
Three things the workbook does not document
None of these three changes a number. All three would cost a reader time, and two of them would cost a future maintainer more than that.
- The demographics exist twice, in two orders. Sheet 1 holds all eight questions in the export's order; sheet 2 holds questions 1 to 4 for the same fifty respondents, sorted by age and then gender. Four pivot tables read the sorted copy and four read the original. Both give identical counts — verified — so nothing turns on it, but no document explains why the duplicate exists.
- Seven of the eight pivot tables have grand totals switched off. Only the question 5 sheet prints "Grand Total 50". The setting is explicit in each pivot table's definition. A reader of any other sheet has to add the column up to learn the base, which is exactly the number a reader most wants.
- Every chart is a 3-D chart, and the four demographic charts carry no labels at all. Three pies show percentages only; the four demographic bars show neither values nor percentages, so their counts can be read only against a gridline. This observation is this library's; the source makes no comment on chart type.
The defect: a live chart on a dead source
One sheet in this workbook is built differently from the other seven, and it is the only structural fault in the file. The question 5 sheet carries its counts twice.
THE SAME FIVE COUNTS, IN TWO PLACES ON ONE SHEET
| Location | Form | Order | Total shown |
|---|---|---|---|
| A3:B9 | Pivot table, declared source Survey Data!E1:E51, 50 records | Alphabetical by category label | Grand Total 50 |
| A33:B37 | Typed cells, no formula, no reference | Descending by count | None |
Both blocks hold the same five counts: 21, 14, 7, 5, 3.
The chart on that sheet reads the typed block, not the pivot table. Its two series references point at $A$33:$A$37 and $B$33:$B$37. Every other chart in the workbook points at its own sheet's pivot table. This one chart — the one published as the study's importance figure — is wired to a transcription of the data rather than to the data.
When you want a chart in a different order
The absence no structure can fix
The workbook is the only complete copy of this study's data. The exported raw file holds only the four demographic questions; the four substantive questions — the ones every finding rests on — are not in it. They exist in one place: the first sheet of this file.
Building one that can be audited
The supplied teaching material names spreadsheet software and a statistics package and shows neither in use, so there is no source procedure to follow. What follows is this library's, built from what this workbook did well and the two places it did not.
Nine rules for an auditable analysis workbook
- Export the platform's full raw file, every question, and store it untouched. Then copy it into the workbook.
- Keep one dataset sheet, entered once, and never analyse on it.
- One question per sheet: its tabulation and its chart together.
- Every tabulation declares the range it reads, and you check that range against the number of cases you intend to publish.
- Turn grand totals on. The base is the number a reader wants, and the number an error hides behind — see presenting data in tables.
- Wire every chart to a table. If a chart's series points at loose cells, either repair it or write down why.
- Turn on data labels for any chart whose values your prose will quote.
- If a sorted or duplicated copy of the data must exist, put a note on the sheet saying what it is and what reads it.
- Before submission, open each chart in turn and read its series references. Three minutes for eight charts.
Two limits on all of this. Every defect above is a defect in the file, not in a researcher whose thesis passed examination — and one of them, the retyped block, is an ordinary consequence of how the tool sorts. And this is one workbook from one project: it is visible in this detail only because the raw file, the workbook internals and the finished thesis all survived together, which is not usual. The full apparatus is walked through at a complete research project, end to end. Treat it as a worked example, not as evidence about how research workbooks are generally built.
What to carry forward
- One question per sheet, with its tabulation and chart together, is a structure that makes every published figure checkable in one place.
- A chart is only as live as the cells it points at. Retyping a table to change its sort order breaks the link silently and permanently.
- Print the base. Seven of the eight tabulations here hide the number a reader most needs.
- Label the values on any chart whose numbers your prose quotes; unlabelled charts cannot correct a prose error.
- Keep a raw export of every question outside the workbook. Half of this study's responses have no copy anywhere else.
Frequently asked questions
Is a spreadsheet an acceptable tool for analysing survey data?
For descriptive tabulation of categorical responses it is entirely adequate, and this workbook demonstrates it: eight pivot tables, eight charts and eight counts that are all exactly right. The tool sets the ceiling elsewhere — no test, no measure of association and no reliability check appears anywhere in this project, and nothing in a spreadsheet prevents any of them.
Why does it matter that a chart reads typed cells instead of a pivot table?
Because it breaks the link between the figure and the data without leaving any sign. Refresh the workbook and the pivot table updates while the chart does not, so a reader sees a current table beside a superseded chart. Here the typed numbers happened to be correct, which is why the defect is structural rather than arithmetical.
Should I duplicate my data onto a second sheet if I need it sorted?
Prefer sorting the tabulation itself, or the pivot table's field. If a second copy is unavoidable, build it from references rather than typed values, and put a note on the sheet saying what the copy is and what reads it. This workbook's second demographic copy gives identical counts, so nothing turned on it, but no document explains why it exists.
What should I keep as raw data?
The platform's complete export, every question, stored unedited and separately from the workbook. This project kept an export of the four demographic questions only, so two hundred of its four hundred responses exist in exactly one file, and any error introduced in that file cannot be detected by comparison.
Why turn grand totals on if the base never changes?
Because a reader cannot know that it never changes. A printed total states the base of the tabulation and makes a missing or duplicated category visible immediately. In this workbook one sheet prints its total and seven do not, and the one that does is the easiest of the eight to check.
Does this mean the study's numbers are unreliable?
No. Every count in the workbook was recomputed from the raw rows and every one matches. The workbook is the sound part of this project. The failures in it are documentation failures, and the numeric errors in this study happen later, when figures were transcribed into sentences.
References and source attribution
- A complete research dataset supplied as student work: one examined master's thesis with its ten-sheet analysis workbook (eight pivot tables, eight charts, no formulas), its raw response file, its survey instrument and its participant information sheet. Researcher, supervisor, institution and network user names scrubbed; software product names written generically. Used as observed practice, not as a model answer.
- The supplied teaching source: weekly study notes and slide decks for a master's-level research methods subject in project management, which names spreadsheet and statistics software as analysis tools without showing either in use. Author, institution and year not stated in the supplied files.
Suggested questions for Ask KEVOS
- How should I structure a spreadsheet workbook so my thesis figures can be traced back to the data?
- How do I check which cells a chart in my workbook is actually reading?
- What raw data files should I keep, and where should I keep them?
- Should tabulations show grand totals, and why?
- When should a chart carry data labels?
- How do I document a sorted or duplicated copy of my dataset?