Chapter 1 · 5 levels
Formulas and references
Write formulas that point at cells, add up ranges with SUM and AVERAGE, lock a reference with $ when you fill down, and make decisions with IF.
StartHalf of the questions a data analyst gets still end in a spreadsheet. A manager wants the numbers in a sheet they can open, filter and forward. Before pivot tables and lookups, everything rests on one idea: a cell can hold a formula that points at other cells, so the answer updates by itself when the data changes.
In this chapter you work in a real spreadsheet, right in the browser, on slices of the Harbor &
Pine store data: order lines, the lighting catalog, kitchen prices, recent orders. You will write
arithmetic with cell references, summarise a range with SUM, AVERAGE, MIN and MAX, learn
why $B$1 behaves differently from B1 when you fill a formula down, and make a decision per row
with IF. The boss level is a complete order-total sheet built in three steps.
Everything you type follows the same rules as Microsoft Excel and Google Sheets, so what you practise here works at your desk.
Levels
- Line totals with cell references
from Brian Mitchell, Finance Analyst
formulas, cell references, arithmetic, operator precedence
- Summarise the lighting range
from Jessica Turner, Merchandising Manager
functions, ranges, sum, average, min max
- Prices with tax, one rate for every row
from Laura Bennett, Head of Finance
absolute references, relative references, fill down
- Shipping fees with IF
from Rachel Adams, Customer Support Lead
if, comparison operators, absolute references
- Boss: build the order-total sheet
from Angela Rivera, Head of Customer Support
formulas, sum, if, absolute references, fill down