Lesson 2: Laying Out a Sheet That Works

Why layout comes first

Most spreadsheet problems start with layout, not formulas. A SUMIFS, a filter or a pivot table reads your sheet as a grid of records. If the grid is broken by merged titles, blank spacer rows or totals in the middle of the data, those tools give wrong answers or fail. Gemini does the same: it works far better on a clean table than on a page laid out for printing.

Think of a data tab as a ledger, not a poster. A poster is arranged so a person can read it. A ledger is arranged so every entry has the same parts in the same order.

The four layout rules

Use these rules on every tab that holds data:

1. One table per tab. Do not stack a second table underneath the first or put one beside it.
2. One header row. Row 1 holds one short, unique name for each column, such as Date, Client, Item, Qty or Amount. Do not use two-row headers or leave a header cell blank.
3. One record per row. Each row is one sale, one invoice line or one task. Do not leave blank rows between groups, and do not use subtotal rows.
4. No merged cells in the data. Every cell holds its own value. If the value repeats down a column, type it in each row.

Example. A sales log has a merged title "Q3 Sales" across A1:E1, a two-row header, a blank row between each month and a "July total" row. Sorting it by Amount would mix the totals in with the sales, and filtering it would cut off at the first blank row. In the fixed version, row 1 holds the headers, each sale has its own row and the totals move to a separate tab.

Task 1: Restructure a messy sheet

Work on a copy first: right-click the tab and choose Duplicate.

1. Unmerge cells. Select the whole data area (click the square in the top-left corner of the grid to select everything). Click Format > Merge cells > Unmerge. Unmerging does not bring back values that were lost when the cells were merged.
2. Fill the gaps that unmerging leaves. A merged "July" label leaves only one cell filled. Type or copy the value into every row it belongs to.
3. Reduce the headers to one row. Combine two-row headers into one name, for example Q3 Amount. Delete title rows above the headers: right-click the row number and choose Delete row.
4. Delete blank rows and subtotal rows inside the data. You will rebuild totals with formulas in a later step.
5. Check every header for a short, unique name with no blank cells.

A sort or filter on a range that contains merged cells usually fails with an error about merged cells. Unmerging removes that problem.

Task 2: Separate raw data, calculations and summaries

Use separate tabs, each with one job:

• Data: raw records only. You add new rows here and nothing else.
• Calc (optional): working columns and lookups that read from Data.
• Summary: totals, key figures and later on charts and pivot tables. These are formulas that read from Data and never contain typed-in numbers.

To set this up:

1. Click + (Add sheet) at the bottom left to create a tab.
2. Double-click a tab name to rename it, for example to Data or Summary.
3. Drag the tabs into the order Data, Calc, Summary.
4. Rebuild the subtotals you deleted as formulas on the Summary tab, for example =SUM(Data!E:E). Formulas that total by month and category are covered later in the course.

Why do this? When a new record arrives, you add one row to Data and the summary updates by itself. Nobody types over a raw number by mistake.

Task 3: Freeze the header row

Freezing keeps the headers visible while you scroll down. It changes only what you see, not the data.

Web (any desktop browser: Chrome, Edge, Safari and others)

1. Click any cell in row 1.
2. Click View > Freeze > 1 row.
3. To undo it, click View > Freeze > No rows. You can freeze columns from the same menu, but only rows counted from the top and columns counted from the left.

Android app

1. Touch and hold the row number 1 (the grey number, not a cell).
2. In the menu, tap Freeze.

iPhone and iPad app

1. Tap the row number 1.
2. Tap Freeze. If you can't see it, tap the arrow in the menu to show more options.

A thick grey line under row 1 shows that the freeze worked.

Where Gemini fits

If your plan includes Gemini in Sheets, you can open the Gemini side panel and ask it to review your layout. In Google's April 2026 announcement, the eligible plans were Business and Enterprise Standard and Plus, Google AI Pro and Ultra, and some add-ons. Workspace smart features must be switched on, and your IT team may have switched Gemini off. Plans change often, so check your own account as Lesson 1 showed. Gemini's suggestions are a draft. Compare them with the four rules above, and check the row count before and after any change so that no records go missing.

Task 4: Audit your own sheet

Open a sheet you use for real work, such as a budget, an invoice log or a tracker. Duplicate it, then go through this checklist:

• Each tab holds only one table
• Row 1 is the only header row, and every column has a unique name
• Each row is one record
• There are no blank rows or subtotal rows inside the data
• There are no merged cells in the data area
• Raw data, calculations and summaries are on separate tabs
• The header row is frozen

Fix each item that fails, using Tasks 1–3. Write down how many rows of data you had before and after, and make sure the numbers match.

Key takeaways

• A data tab is a ledger: one table, one header row, one record per row.
• Merged cells, blank rows and subtotal rows break sorting, filtering and formulas.
• Keep raw data, calculations and summaries on separate tabs, and build summaries with formulas.
• Freeze row 1 with View > Freeze > 1 row on the web. In the mobile apps, use the row number menu.
• Gemini can suggest layout fixes on eligible plans, but check its changes, including the row count.