Theory
Raw data here, summary there
Priya's survey has 200 messy response rows. She does not want that clutter on the same tab as her clean summary for the teacher.
The answer, familiar from Excel: multiple tabs in one Sheets file. Keep raw responses on a 'Data' tab, build a tidy 'Summary' tab, and let formulas on the Summary pull from Data.
The key new skill is the cross-sheet reference: how a formula on one tab reaches a cell on another. It is one small piece of syntax that unlocks organising a whole workbook into purposeful sheets. Let us see it.
Theory
Rooms in one house
One Sheets file is a house; each tab is a room with its own purpose, one for raw storage, one for working out, one for the presentable summary. And just as you can carry something from the storeroom to the living room, a formula in the Summary room can fetch a value from the Data room. Separate rooms keep things tidy; cross-references let them still work together. Organisation without isolation.
Theory
Tabs and cross-sheet references
- Add a tab: the + at the bottom-left. Rename (double-click), colour, reorder by drag, delete. Give tabs clear names: 'Data', 'Summary'.
- Reference another tab in a formula with SheetName!Cell:
=Data!B2 pulls cell B2 from the Data tab.
=SUM(Data!B2:B100) totals a range on the Data tab.
If the tab name has spaces, wrap it in single quotes: ='Raw Data'!B2.
Everything else, =, SUM, IF, VLOOKUP, works exactly like Excel (BCA105). So Priya's Summary tab can compute totals and averages that live-read from the Data tab. Edit the data, the summary updates.
Quiz
On her 'Summary' tab, Priya wants to total cells B2 to B100 that live on the 'Data' tab. What formula does she write?
- =SUM(Data!B2:B100), the Sheet!Cell reference reaches the other tab
- =SUM(B2:B100), which reads the Summary tab
- =SUM(Data:B2:B100)
- You cannot reference another tab
Show the answer
=SUM(Data!B2:B100), the Sheet!Cell reference reaches the other tab
A cross-sheet reference uses SheetName!Cell, so =SUM(Data!B2:B100) totals that range on the Data tab from the Summary tab. Plain =SUM(B2:B100) would read the current (Summary) tab, the wrong data. The ! separates the sheet name from the cell reference. Cross-tab references are the key mechanism for organising data across tabs while still calculating over it.
Think first
Why split into tabs at all?
Priya could keep everything, raw responses and summary, on one tab. Why is splitting into a 'Data' tab and a 'Summary' tab better practice?
Show the answer
Separation of concerns: the Data tab holds messy raw responses (which she should not disturb), while the Summary tab presents clean results to the teacher, no clutter, no risk of accidentally editing raw data while formatting the summary. Cross-sheet formulas keep them linked, so the summary stays accurate. It is the same 'keep raw data separate from the report' discipline from BCA105, and it makes a workbook far easier to maintain and share.
Watch out
Where marks leak
Not knowing the SheetName!Cell syntax for cross-tab references (and that names with spaces need single quotes: 'Raw Data'!B2). Forgetting a formula with no sheet name reads the current tab. And missing that one Sheets file holds many tabs (add/rename/reorder), just like an Excel workbook holds worksheets (BCA105). The cross-sheet reference is the exam-worthy new skill.
Theory
Organise like a pro
Splitting a workbook into raw-data, calculation and summary tabs, tied by cross-sheet formulas, is exactly how professionals structure spreadsheets. It scales from a college survey to a company's financial model. Next, the team turns the survey numbers into pictures: charts in Google Sheets, which, as you saw, can even embed live into the Docs report. Next: charts in Sheets.
Summary
Key takeaways
- One Google Sheets file holds many tabs (add with +, rename, colour, reorder, delete).
- Reference another tab in a formula with SheetName!Cell (e.g. =Data!B2, =SUM(Data!B2:B100)).
- Wrap sheet names containing spaces in single quotes: ='Raw Data'!B2.
- A formula with no sheet name reads the current tab.
- Split a workbook into purposeful tabs (raw data, summary) linked by cross-sheet formulas.
- Memory hook: rooms in one house, carry values from the storeroom to the summary.