Spreadsheet foundations
Work confidently with sheets, cells, and formulas
A practical guide to spreadsheet structure, references, formulas, and the checks that keep your results dependable.
Start with structure
Separate the work into clear layers
A spreadsheet is easier to maintain when each sheet has a job. Keep imported or raw values in one place, calculations in another, and reports or dashboards in a final view. This makes formulas easier to read and helps the assistant understand your intent.
Use the first row for useful headings. Avoid mixing notes, totals, and unrelated tables inside the same range when you want to sort, filter, or analyze the data.
Cells and references
Point formulas at the right data
Cell references tell a formula where to find its inputs. Relative references change when you copy a formula. Absolute references, such as $B$2, stay fixed. Mixed references hold one row or column steady.
When a formula gives an unexpected result, check the range first. A correct function pointed at the wrong rows is still the wrong answer.
Build and check
Use SheetAI as a second pair of eyes
Ask for a formula
Describe the columns, the rule, and the result you need. Include a sample row when the logic has an exception.
Explain a formula
Point to a cell and ask for a plain-language explanation of each part, including what happens when values are blank.
Test edge cases
Check blank cells, duplicate values, dates, zero values, and rows outside the expected range.
Verify the output
Compare a few results by hand before filling a formula down a large table.
For function names and examples, use the function reference. You can also try the Formula Generator for a focused answer.
Common patterns
Choose a formula that matches the question
Totals and averages
Use a clear range for totals, averages, counts, and subtotals. Decide whether filtered or hidden rows should be included.
Rules and exceptions
Use a conditional result when a row can be on track, late, missing, or outside a threshold. Write down the boundary so the result is consistent.
Lookups
Match a stable key such as an order number or product ID to a separate table. Check what should happen when there is no match.
If you see a blank result, an error, or an unexpected total, inspect the inputs before changing the formula. A short explanation from the Formula Explainer can help you find the first wrong assumption.
When a formula breaks
Check the inputs before replacing the formula
Start with the cell that shows the problem and trace the values it depends on. Look for text stored where a number should be, a date that is really text, a missing lookup key, or a range that stops one row too early.
If a formula works in one row but not another, compare the references. A copied formula may have moved a relative reference when you meant to keep a row or column fixed.
When a formula will be used by other people, prefer a readable structure over a clever shortcut. Add a short note when the result depends on a business rule that is not obvious from the headings.
A reliable check
Compare the formula with a known answer
Choose one row where you can calculate the expected result by hand. Put the expected value beside the formula result and compare them before you copy the formula through the table.
For totals, compare a small range and the full range. For lookups, test a key that should match and one that should not. For dates, test the first day, the last day, and a blank value.