Interface, Navigation & Entering Data Right

Phase EX: Excel for Analysts · White Belt · ~3–4 focused hours

Every model you will ever build, every ratio you will ever compute, every board deck that ever carried your name, sits on top of a grid of cells that somebody had to fill correctly first. Before DuPont, before DCFs, before a single XLOOKUP, there is a more primitive skill that separates analysts whose work is trusted from analysts whose work is quietly re-checked by someone senior: the ability to get data into a spreadsheet in a clean, addressable, formula-ready state, and to move around that spreadsheet at the speed of thought rather than the speed of a mouse.

That is not a "beginner topic" you graduate from. It is the foundation the entire tower stands on. The most expensive errors in finance (the mis-totalled model, the number that was really text, the merged cell that broke a sort the night before a deadline) are almost always failures of this material, not of anything advanced.

Think of Excel not as a piece of paper you type on but as an addressable machine: a vast lattice of memory locations, each with a name (A1, C5, AZ1200), each capable of holding a raw value or a formula that computes one. The whole power of the tool comes from the fact that a formula in one cell can point at another cell by its address and recompute automatically when that address changes.

Master the address system, the ways to select and move across it, the discipline of Tables that keeps data honest, and the distinction between what a cell shows you and what it actually stores, and everything downstream becomes possible. Skip it, and you will spend the rest of the curriculum fighting your own spreadsheets.

What follows treats the grid as an analyst's weapon: Microsoft 365 assumed throughout, every action given as both a menu path (Home ▸ Number ▸ …) and its keyboard alternative, because the keyboard is where the speed lives.

One promise for the hour ahead: by the end you will paste a filthy sales export (text where numbers should be, leading zeros gone, formats fighting you) and turn it into a clean, totalled, trustworthy Table in about ninety seconds without touching the mouse more than twice. That ninety-second reflex is the white belt.

Learning objectives

By the end, you can:

1. Read and write any A1-style cell and range reference, explain what the grid actually is (an addressable lattice of 16,384 columns × 1,048,576 rows), and use the Name Box both to jump to a location and to name a range.
2. Navigate and select a data region entirely from the keyboard (Ctrl+arrow to jump a region's edge, Ctrl+Shift+arrow to select to it, Ctrl+Home/Ctrl+End for the corners) at a speed that makes the mouse feel slow.
3. Convert any rectangular block of data into an Excel Table with Ctrl+T, and explain the four concrete disciplines a Table enforces (structured references, auto-expansion, filters, a total row) that raw cells do not.
4. State the single most important truth about a cell — that its number format is a display mask, not the stored value — and predict exactly when a calculation will surprise a user because the screen and the storage disagree.
5. Enter and fill data efficiently: static dates and times (Ctrl+;, Ctrl+Shift+:), fill series versus copy, Ctrl+D/Ctrl+R, the fill-handle double-click, and Flash Fill (Ctrl+E) to reconstruct a pattern the software infers.
6. Use Paste Special deliberately (values only, formats only, transpose, and the multiply-by-1 coercion trick) and say why pasting values over a formula is one of the most common ways analysts silently destroy a model.
7. Freeze and split panes to keep headers visible while navigating a large sheet, and know which tool suits which situation.
8. Apply basic file hygiene (.xlsx versus .csv, AutoSave and its OneDrive/SharePoint requirement, Version History, and sane naming) so that your work is recoverable and singular.
9. Reproduce, from memory, the ~25 core keyboard moves that constitute an analyst's muscle memory, and diagnose the five classic data-entry failures (merged cells, formatting-versus-changing, numbers-as-text, no-Table, overwrite-instead-of-paste-values).

Prerequisites & connections

Builds on. Nothing but the ability to open Excel: this is the first node of the Excel track, the true white belt. If you have used spreadsheets casually, expect this node to rewire habits rather than add to them: most self-taught users navigate with the mouse and format numbers they think they are changing, and both habits must go.

Feeds forward. Everything. EX0.02 (formulas, operators, the order of evaluation, and the absolute/relative reference model behind F4) assumes you can reference and select cells fluently, that you already trust the difference between a stored value and a shown one, and that the reference model introduced here is second nature. The lookup and aggregation nodes (SUMIFS, XLOOKUP, INDEX/MATCH) all assume your data lives in clean Tables with real (not text) numbers: the single most common reason a lookup "returns nothing" is a type mismatch born in this node. And when the financial-analysis phases ask you to spread a P&L or build a DuPont pass (M2.01 and beyond), the raw material arrives as a messy export exactly like the ones you will clean here; the analyst who cannot get it into the grid cleanly never gets to the analysis at all. This node is the floor of the whole building.

The grid and the A1 reference

Intuition first. Open a blank workbook and you are looking at a coordinate system. Columns run across the top, labelled with letters: A, B, … Z, then AA, AB, … all the way to XFD. Rows run down the side, numbered 1 to 1,048,576. That is 16,384 columns and 1,048,576 rows, or just over 17 billion cells in a single sheet, you will never fill them, but knowing the ceiling stops a certain class of panic ("did my data get cut off?"). Every cell has an address formed by its column letter followed by its row number, with the letter always first: A1 is the top-left corner, C5 is column C row 5, AZ1200 is column AZ row 1200. This is called A1 notation, and it is the language every formula speaks.

Why does the order matter, and why letters-then-numbers? Because a formula refers to cells by these addresses, and the whole spreadsheet paradigm is that a cell can hold a formula that points at other cells. Type =C5*C6 into D5 and you have not stored a number; you have stored an instruction: "whatever is in C5, multiply by whatever is in C6, and show the result here." Change C5 and D5 updates itself. That live pointer, multiplied across thousands of cells, is the entire reason spreadsheets conquered finance. The address is the pointer; learn to read and write addresses cold.

The active cell and the Name Box. Exactly one cell is active at any moment: outlined in green, its address shown in the Name Box, the small white box at the far left of the formula bar (just left of the fx). The Name Box is not decoration. Type any address into it and press Enter and Excel jumps there instantly: Z500, Enter, and you are at Z500 no matter where you were. This is the fastest way to reach a distant cell, and it is the first mouse-avoidance habit to build.

A range is a rectangle. A contiguous block of cells is written as top-left:bottom-right: A1:C10 means the ten-row, three-column rectangle from A1 to C10 (30 cells). A whole column is C:C; a whole row is 5:5. The colon is the range operator; you will read it as "through." SUM(A1:A20) means "sum everything from A1 through A20." Non-adjacent cells are joined with a comma (the union operator): SUM(A1:A10, C1:C10). Get comfortable reading a range aloud as a shape, because half of debugging a formula is realising it points at the wrong rectangle.

One aside on the other dialect. Excel can also use R1C1 notation, where both axes are numbered and R5C3 means row 5 column 3. You will almost never turn it on (File ▸ Options ▸ Formulas ▸ "R1C1 reference style"), but you should recognise the name, because macros and some error messages speak it. A1 is the analyst's default; leave it there.

Ranges, the Name Box, and selection moves

Selecting the right cells quickly is 80% of spreadsheet speed, and it is almost entirely a keyboard skill. The mouse is fine for a single cell; for a region it is a liability, because dragging is imprecise and slow and you cannot see where a drag will stop.

The jump: Ctrl+arrow. Put the cursor anywhere in a block of data and press Ctrl+→. The active cell leaps to the last filled cell before a blank in that direction: the right edge of your data region. Ctrl+↓ jumps to the bottom edge, Ctrl+↑ to the top, Ctrl+← to the left. On an empty stretch it flies to the very edge of the sheet (XFD or row 1,048,576). This is how you check "how far does my data actually go?" in a fifth of a second, and how you land on the bottom of a 40,000-row export without scrolling.

The select: Ctrl+Shift+arrow. Add Shift and the jump selects everything it passes over. Stand in the top-left of a column of numbers, press Ctrl+Shift+↓, and the entire column of data is selected: ready to sum, format, or copy. Ctrl+Shift+→ then Ctrl+Shift+↓ selects a whole rectangular block corner to corner. This pair (jump and jump-select) is the single most valuable motor skill you will build here. Drill it until it is unconscious.

The corners: Ctrl+Home and Ctrl+End. Ctrl+Home returns you to A1 (or to the first unfrozen cell, if you have frozen panes) from anywhere. Ctrl+End jumps to the bottom-right corner of the used range: the intersection of the last row and last column that has ever held content. Beware: Ctrl+End reveals a common gremlin: if it lands far below or right of your real data, the sheet has "ghost" formatting or deleted content bloating the used range, which inflates file size and scrollbars. The fix is to delete the empty rows/columns and save.

Select all: Ctrl+A. Pressed once inside a data region, Ctrl+A selects the whole region; pressed again, the entire sheet. Inside a Table, the first Ctrl+A selects the Table's data, the second adds the header, the third takes the sheet.

Extend and shrink with Shift. Shift+arrow extends the selection one cell at a time; Shift+click extends to the clicked cell; Shift+Space selects the whole row, Ctrl+Space the whole column. These compose: Ctrl+Space then Ctrl+Shift+↓ is one way to grab a column's data below the header.

Naming a range. Select a range, click into the Name Box, type a friendly name like Tax_Rate or SalesData, and press Enter. Now that name is the range everywhere: =B7*Tax_Rate reads like English and never breaks when rows shift. Manage names at Formulas ▸ Name Manager (Ctrl+F3). Named ranges are a readability and safety tool you will lean on hard once models get large; introduce them early so the habit is there.

Tables: the unit of clean data

Whenever you have a rectangular block of data with headers, make it an Excel Table. That is the single most important discipline here, and the one most beginners have never heard of. Select any cell in the block and press Ctrl+T (menu: Insert ▸ Table). Confirm "My table has headers," press Enter, and the raw range becomes a first-class object with a name (Table1 by default; rename it in Table Design ▸ Table Name to something meaningful like Sales).

Why does this matter so much that it earns the word "discipline"? Because a Table enforces four things that raw cells leave to your fragile human diligence:

1. Structured references. Inside and outside a Table you can refer to columns by name instead of by coordinate. =SUM(Sales[Revenue]) sums the Revenue column no matter how many rows it has today; =[@Units]*[@Price] in a Table cell means "this row's Units times this row's Price." These references are self-documenting and they do not break when you insert or sort rows. Compare =SUM(D2:D5000) (a coordinate you must maintain by hand) with =SUM(Sales[Revenue]), which is simply correct.
2. Auto-expansion. Type a new row directly beneath a Table and the Table swallows it: formatting, formulas, and structured references extend automatically. Add a column to the right and it joins too. Every downstream formula, chart, and PivotTable pointed at the Table updates without you touching its range. This one behaviour eliminates the most common "my total forgot the new rows" error in all of finance.
3. Automatic filters and sorting. Every header gets a filter dropdown the moment the Table exists (toggle with Ctrl+Shift+L). Sorting is safe because the Table knows its own boundaries: no risk of sorting one column while the others stay put and scrambling every row (the single most destructive beginner accident, covered under Common Mistakes).
4. A total row on demand. Table Design ▸ Total Row (or Ctrl+Shift+T in current builds) adds a footer where each column offers a dropdown of aggregate functions (Sum, Average, Count, Max) computed with the SUBTOTAL function so filtered-out rows are excluded correctly.

There is also plain readability: Tables come with banded rows and a styled header (restyle at Table Design ▸ Table Styles), and the header row stays visible as you scroll down within the Table, a freeze-panes effect you get for free. Convert a Table back to a plain range if you ever must with Table Design ▸ Convert to Range, but the default posture of a professional analyst is: data in, Table on. When we clean the messy export in the worked examples, Ctrl+T is the very first move, before we fix a single number.

Number formats are a mask, not the value

**A cell's number format changes only how the value is displayed. It does not change the value that is stored and used in calculations.** Once truly understood, that fact prevents an entire genus of errors, and it is the one beginners most reliably get wrong.

Type 1234.567 into a cell, then format it to show two decimal places (Home ▸ Number, or Ctrl+1 for the full Format Cells dialog ▸ Number ▸ 2 decimals). The screen now reads 1234.57. But the cell still stores 1234.567: the format is a mask painted over the true value. Prove it: in the next cell type =A1*2. You might expect 2469.14 (that is 1234.57 × 2). Excel returns 2469.134, which with two-decimal formatting displays as 2469.13, because it multiplied the stored 1234.567 × 2 = 2469.134, not the shown 1234.57. The one-cent gap between 2469.13 and 2469.14 is the mask and the value disagreeing, and in a large model those pennies compound into totals that "don't foot" and reviewers who lose trust.

The same trap in its most famous form: put 1.4 in two cells, format both to zero decimal places. Each shows 1. Sum them with =A1+A2 and the result (1.4 + 1.4 = 2.8) displays, at zero decimals, as 3. The user sees "1 + 1 = 3" and thinks Excel is broken. Excel is flawless; the display rounded each input to 1 while the math used 1.4. This is not a bug to fix but a truth to internalise: what you see is not what you compute.

So how do you actually change a value? You must round it for real, with a function that writes a new stored number: =ROUND(A1, 2) stores 1234.57 (a genuinely different number) and now ×2 gives exactly 2469.14. ROUND(number, digits) rounds to the given number of decimal places (negative digits round to tens, hundreds: ROUND(1234.567, -2) = 1200). Formatting is cosmetic; ROUND is surgical. Use formatting to make a report readable; use ROUND when the stored precision itself must change (e.g., you never want fractions of a cent to survive into a downstream calculation).

The format toolbox. The Home ▸ Number group holds the common formats (General, Number with thousands separator, Currency, Accounting, Percentage, Date, Short/Long). The keyboard equivalents are worth knowing: Ctrl+Shift+~ sets General, Ctrl+Shift+$ Currency, Ctrl+Shift+% Percentage, Ctrl+Shift+# Date, Ctrl+Shift+! Number with a thousands separator. Ctrl+1 opens the full Format Cells dialog for custom formats. Two things every analyst should feel in their bones: a percentage format multiplies the display by 100 and adds a %, so a cell showing 20% actually stores 0.2, and =A1/2 on it gives 0.1 (shown 10%), which trips up beginners who think the stored value is 20. And a date is really a stored number: Excel counts days from 1 January 1900 (serial 1), so 20-Jul-2026 is stored as the integer 46223; format that same cell as Number and the date turns into a plain count, format a plain count as Date and it blooms into a date. Dates are numbers wearing a costume, which is exactly why you can subtract two dates to get the days between them.

Entering and filling data (Flash Fill, series)

Entering, precisely. Enter commits a cell and moves down; Tab commits and moves right; Shift+Enter and Shift+Tab reverse those. To enter the same value into many selected cells at once, type it and press Ctrl+Enter: the whole selection fills with that entry (a fast way to stamp a label down a column). To put a line break inside a cell, Alt+Enter. To edit a cell in place rather than overtype it, press F2 (or double-click): this drops you into the cell with the cursor at the end, invaluable for surgical edits to a long formula.

Static timestamps. Ctrl+; (semicolon) stamps today's date as a fixed value; Ctrl+Shift+: (colon) stamps the current time. These are static: they do not change tomorrow. Contrast the volatile functions =TODAY() and =NOW(), which recompute every time the sheet calculates. When you want a permanent "data pulled on" marker, use the keyboard stamps; when you want a live clock, use the functions. Confusing the two is how a report's "as of" date silently rolls forward and misleads a reader.

Fill series versus copy. Type 100 in a cell and drag its fill handle (the small square at the bottom-right of the selection) downward, and by default Excel copies 100 down. But type 100 in one cell and 125 in the next, select both, and drag: Excel detects the step of 25 and continues the series: 150, 175, 200, 225, …. Two seed values teach it the pattern. This works for numbers, dates (drag Jan to get Feb, Mar, …; drag a date to step days), and weekday/month names. For explicit control use Home ▸ Fill ▸ Series, where you set a step value and stop value exactly. To fill down from the cell above without dragging, Ctrl+D; to fill right from the cell to the left, Ctrl+R. And the professional's shortcut: double-click the fill handle and the formula or pattern fills down automatically to match the length of the adjacent column: no dragging, no overshoot, correct even at 10,000 rows.

Flash Fill: the pattern-reader. Flash Fill (Ctrl+E, or Data ▸ Flash Fill) watches you type and infers a transformation from examples. Suppose column A holds full names like Rajesh Kumar and you want first names in column B: type Rajesh in B2, start typing Suresh in B3, and Excel offers to fill the whole column by extracting first names. Accept with Enter. It splits, joins, re-cases, inserts characters, pads, and reformats: all from one or two worked examples, no formula required. It is astonishing for one-off cleanups: reconstructing a padded SKU, pulling a domain out of an email, reformatting a phone number, stitching City, State from two columns. Two cautions: Flash Fill produces static values, not live formulas, so it does not update if the source changes: re-run it or use a formula if the data is dynamic; and it occasionally guesses the wrong pattern from too few examples, so give it a second example and eyeball the result. We use it on the US SKU list in Worked Example 2.

Paste Special done right

Ordinary paste (Ctrl+V) brings everything from the copied cells: values, formulas, formats, borders, conditional formatting, data validation, the lot. Nine times out of ten in analysis you want only part of that, and grabbing the wrong part is how models get quietly wrecked. Paste Special (Ctrl+Alt+V, or Home ▸ Paste ▸ Paste Special) opens a dialog to choose precisely what transfers.

The four you will use constantly:

• Values (Ctrl+Alt+V, then V; in current builds Ctrl+Shift+V pastes values directly). This pastes the results of formulas as static numbers, dropping the formulas themselves. Essential when you want to freeze a computed column, break a link to another workbook, or send someone numbers without your logic. This is also the single most dangerous paste, because pasting values on top of a formula silently replaces live logic with a dead number, and nothing warns you; see Common Mistakes.
• Formats (then T). Copies only the look (number format, font, fill, borders) and none of the content. The keyboard-free way to make one range look like another.
• Formulas (then F). Content without the formatting.
• Transpose (the checkbox, or E). Flips rows and columns: a copied block that is 3 rows tall and 5 columns wide pastes back 5 rows tall and 3 columns wide (the cell count is unchanged at 15; only the orientation flips). Invaluable when a source lays data out the wrong way for your model.

There is also a hidden gem in the dialog: the Operations (Add, Subtract, Multiply, Divide). Copy a cell containing 1, select a range of numbers-stored-as-text, Paste Special ▸ Multiply, and every cell is multiplied by 1, which coerces text into real numbers in place. That multiply-by-1 trick is one of the fastest cures for the numbers-as-text disease we meet next. The old-school route to the same dialog, Alt+E+S, still works and is worth knowing because it is muscle memory for a generation of analysts.

Freeze, split, and navigating big sheets

Scroll down a thousand-row sheet and your header row vanishes off the top; now every column is a guessing game. Two tools keep your bearings.

Freeze Panes (View ▸ Freeze Panes) pins rows and/or columns so they stay on screen while the rest scrolls. Select the cell below and to the right of everything you want frozen, then View ▸ Freeze Panes ▸ Freeze Panes: click B2 first and both row 1 and column A stay put. The two shortcuts on that menu cover the common cases: Freeze Top Row (headers stay visible) and Freeze First Column (row labels stay visible). Keyboard: Alt+W+F+F. Freeze is the right tool when you want a permanent anchor for the whole session.

Split (View ▸ Split, or Alt+W+S) instead divides the window into independently scrollable panes (up to four) so you can view two far-apart regions of the same sheet at once, say row 5 and row 5,000 side by side to compare. Unlike freeze, both panes scroll; you drag the split bars to size them. Use Split for comparison, Freeze for anchoring. Remove either from the same menu.

Two more navigation aids for large workbooks: Ctrl+Page Down / Ctrl+Page Up move between worksheet tabs, and holding Ctrl while pressing arrow keys (from earlier) is how you cross a big region without the scroll wheel. Zoom with Ctrl+mouse-wheel or the slider bottom-right when you need the bird's-eye view of a model's structure.

File hygiene, AutoSave, and version history

Good analysis dies in a corrupted or lost file, so treat file discipline as part of the craft.

Format. Save working files as .xlsx: the modern, compressed, formula-preserving format. .xls is the pre-2007 legacy format (smaller row limit, no modern features); avoid it unless a counterparty demands it. .csv is plain text with no formulas, no formatting, one sheet only: perfect for exchanging raw data with other systems, useless as a working model. Know the trap: opening a .csv, working in it, and pressing Ctrl+S saves back to .csv and throws away every formula and format you just added with only a mild warning. When you receive a .csv, your first act is F12 (Save As) to .xlsx.

AutoSave and Version History. In Microsoft 365, the AutoSave toggle sits at the very top-left of the window. It saves continuously only if the file lives on OneDrive or SharePoint: a local-drive file cannot AutoSave, and beginners are burned when they assume protection they do not have. For cloud files, Version History (File ▸ Info ▸ Version History, or click the filename in the title bar) keeps timestamped snapshots you can open, compare, and restore: the safety net for "I broke it an hour ago." For any file, Ctrl+S saves now and F12 is Save As.

Naming and singularity. Name files so a stranger (or you in six months) can tell what they are and which is current: Project_Descriptor_YYYY-MM-DD_v03.xlsx beats final_final_USE THIS.xlsx. The deeper principle is a single source of truth: one authoritative file per analysis, versioned deliberately, never five near-identical copies drifting apart in an inbox. Cloud files with Version History make the copies unnecessary; use them.

Keyboard-first: the 25 moves

Analysts who look fast are not thinking faster; they have simply removed the mouse from the loop for everything except pointing at a chart. Below is the core vocabulary. Do not memorise it as a list; use the ones relevant to each section above until your fingers know them, then keep this table as a reference. Everything here is Windows Microsoft 365 (on macOS, swap Ctrl for ⌘ in most cases).

The last row is not really a shortcut but a combination, and combinations are the point: Ctrl+Shift+↓ to select a column of numbers, then Alt+= to sum them, is two keystrokes that replace a mouse-drag and a menu hunt. Fluency is stringing these together without looking. Spend the drill time here; it pays back every single day for the rest of your career.

Common mistakes & how experts think differently

1. Merged cells. Merging cells (Home ▸ Merge & Center) to make a pretty title looks harmless and is a landmine. A merged block breaks Ctrl+Shift+arrow selection (the jump stops or overshoots unpredictably), makes sorting throw "This operation requires the merged cells to be identically sized" and refuse, blocks Ctrl+T over the region, and confuses every formula that points into it (only the top-left cell holds the value; the rest are empty). The expert almost never merges inside a data region; for a centred heading across columns they use Format Cells ▸ Alignment ▸ Center Across Selection, which looks identical and breaks nothing. Rule: merged cells are for a final printed cover page, never for live data.
2. Formatting a number instead of changing it. The beginner formats a cell to two decimals and believes the value is now rounded; the total then fails to foot by a penny and they cannot see why. The expert knows the format is a mask and reaches for =ROUND() whenever the stored precision itself must change, and can predict the "1 + 1 = 3" surprise before a colleague reports it.
3. Numbers stored as text. A column pasted from a PDF, a web page, or a system export often arrives as text that looks like numbers: left-aligned instead of right, often flagged by a little green triangle and a warning "Number stored as text." Text does not sum: SUM silently ignores it and returns a total that is too low, and lookups fail to match. The expert spots it instantly (left-alignment is the tell), and fixes it with the multiply-by-1 Paste Special trick, =VALUE(), the green-triangle ▸ "Convert to Number," or Data ▸ Text to Columns ▸ Finish (which re-parses the column as its natural type). Never trust a total until you have confirmed its inputs are real numbers.
4. Not using Tables. The beginner leaves data as a loose range, then hard-codes =SUM(D2:D5000) and wonders why the new rows in 5001+ are missing from the total, or sorts one column alone and scrambles every record. The expert converts to a Table first, so totals auto-expand, references are named and self-documenting, and sorting is boundary-safe. Ctrl+T is the reflex before analysis begins.
5. Overwriting a formula with Paste Special ▸ Values (or the reverse). Pasting values over a live formula replaces logic with a frozen number and gives no warning; three refreshes later the model is stale and no one knows which cells are dead. The expert pastes values deliberately and sparingly, usually into a new column reserved for it, and treats "is this cell a formula or a hard-coded number?" as a question they can always answer (select and glance at the formula bar; or Formulas ▸ Show Formulas, Ctrl+` ``, to X-ray the whole sheet at once).

The thread through all five: the expert holds a mental model of what a cell truly contains (value or formula, number or text, stored or merely shown) and never confuses the surface for the substance. That habit, more than any function, is what this white belt is really teaching.

Worked examples

Worked example 1: Jodhpur handicraft stall: from a text paste to a totalled Table (India, ₹)

Chandni runs a handicraft stall in Jodhpur's Sardar Market and sends you her day's sales as a block of text pasted out of a messaging app. It lands in your sheet in A1:C11, a header row and ten product rows, but the numbers came in as text (left-aligned, green triangles), and there is no Revenue column and no total. Your job: make it a clean, totalled Table. The pasted data:

Solution. Step 1: Table first (Ctrl+T). Click any cell in the block, press Ctrl+T, confirm "My table has headers," Enter. Rename it in Table Design ▸ Table Name to Sales. The block is now a first-class object; note it has exactly 10 data rows: confirm with Ctrl+↓ from the header, which lands on row 11.

Step 2: fix the numbers-as-text. The Units and Price columns are left-aligned with green triangles: text, not numbers. Select both columns' data (click the Units header's data, Ctrl+Shift+→ then Ctrl+Shift+↓), and use the green-triangle warning ▸ Convert to Number, or copy an empty cell, select the block, and Paste Special ▸ Multiply. They snap to right-aligned real numbers. Nothing sums correctly until this is done.

Step 3: add the Revenue column with a structured reference. In the cell to the right of the Price header type Revenue and Enter; the Table auto-expands to include the new column. In its first data cell type =[@Units]*[@Price] and Enter: the structured reference means "this row's Units times this row's Price," and because it is a Table the formula fills down all ten rows automatically. Row by row it computes 6×450 = 2,700; 9×380 = 3,420; 14×120 = 1,680; 7×260 = 1,820; 5×650 = 3,250; 3×1,200 = 3,600; 4×880 = 3,520; 11×340 = 3,740; 8×275 = 2,200; 6×520 = 3,120.

Step 4: total it. Turn on Table Design ▸ Total Row (or click into the Revenue column below the data and press Alt+= for AutoSum). The total row sums Revenue to ₹29,050. As a cross-check, SUM(Sales[Units]) gives 73 units sold across the day.

Read it. In four moves and almost no mouse you turned a text blob into a live, totalled, self-expanding Table: tomorrow Chandni adds three rows beneath it, the Revenue formula and the total absorb them with zero edits. The day's revenue is ₹29,050 on 73 units (an average realisation of ₹29,050 ÷ 73 ≈ ₹397.95 per unit, a figure you now have the discipline to compute on real, not text, numbers). Everything that could have gone wrong here (a total that ignored text cells, a Revenue column that forgot the last row) was prevented by Table first, numbers real, structured reference.

Worked example 2: US SKU expense list: leading zeros lost, amounts as text (US, $)

A US reseller exports a purchasing file. Each SKU is really a five-digit code with leading zeros (00742, 01050, …), but the export stored them as numbers, so Excel dropped the leading zeros and you see 742, 1050, 89. The Amount column, meanwhile, came in as text, so it will not sum. Eight rows in A1:B9:

Solution. Step 1: reconstruct the padded SKU two ways. The clean, formula way: in a new SKU_Clean column type =TEXT(A2,"00000"). The TEXT(value, format_code) function returns the value as text formatted by the code; "00000" forces exactly five digits, padding with leading zeros. So 742 → "00742", 89 → "00089", 7 → "00007": each exactly 5 characters long (=LEN("00089") returns 5, a good self-check). The fast, one-off way: Flash Fill. Type 00742 in the first cell of the new column, begin typing 01050 in the second, press Ctrl+E, and Excel infers "pad to five digits" and fills the rest as static text. Use TEXT when the source may change; use Flash Fill for a throwaway cleanup. (Note: these produce text, which is correct: a SKU is a label, not a quantity; you never do arithmetic on it, and leading zeros must survive.)

Step 2: coerce the text Amounts to real numbers. The Amount column is left-aligned text. The cleanest in-place fix is Data ▸ Text to Columns: select the column, Data ▸ Text to Columns ▸ Delimited ▸ Finish (accepting defaults): Excel re-parses each entry as its natural type and the amounts become right-aligned numbers. Equivalent cures: Paste Special ▸ Multiply by a cell holding 1, or wrap each in =VALUE(B2). Until this is done, SUM ignores every value and reports 0.

Step 3: total it. With the Amount column now genuinely numeric, select it and press Alt+= (AutoSum), or, if the block is a Table, switch on the Total Row. The amounts sum: 1,284.50 + 342.00 + 2,199.99 + 75.25 + 840.00 + 1,050.75 + 499.00 + 625.40 = $6,916.89.

Read it. Two classic import diseases, two clean cures. Leading zeros vanish whenever a genuine code (SKU, ZIP, phone, account number) is stored as a number: the fix is to store it as text with TEXT(…,"00000") or Flash Fill, never to "just reformat," because reformatting a number that already lost its zeros cannot bring them back (the information is already gone; you would have to reconstruct it). And a text Amount column silently zeroes your total until coerced. The verified purchase total is $6,916.89; had you trusted the raw SUM before coercing, you would have reported $0.00 and looked foolish. The discipline (inspect alignment, fix type, then total) is identical to Worked Example 1 across a different pair of gremlins, which is the whole point: the diseases are few, and you now know all of them.

Practice set

Work each problem before reading its solution. Assume Microsoft 365, A1 notation, and clean inputs unless a problem says otherwise. Figures are illustrative.

P1 (guided). Revenue column and AutoSum. A Table Orders has Units and Price columns with five rows: (8, ₹125), (15, ₹60), (6, ₹340), (12, ₹90), (9, ₹150). Write the structured-reference formula for a Revenue column and give the AutoSum total.

Solution. In the first Revenue cell: =[@Units]*[@Price], which fills down the Table automatically. Row values: 8×125 = 1,000; 15×60 = 900; 6×340 = 2,040; 12×90 = 1,080; 9×150 = 1,350. The Total Row (or Alt+=, equivalently =SUM(Orders[Revenue])) gives ₹6,370. A common slip is to hard-code =SUM(E2:E6); correct today, but it silently omits any sixth row added tomorrow: the reason the structured reference SUM(Orders[Revenue]) is preferred.

P2 (guided). Displayed versus stored. Cell A1 holds 1234.567, formatted to show 2 decimal places (so the screen reads 1234.57). What does =A1*2 return and display, and what would it be if the value were truly 1234.57? What does this prove?

Solution. =A1*2 computes on the stored value: 1234.567 × 2 = 2469.134, which with 2-decimal formatting displays as 2469.13. If the cell truly stored 1234.57, the product would be 1234.57 × 2 = 2469.14 (displayed 2469.14). The one-cent gap proves the format is only a mask; to make the stored value actually 1234.57 you must write =ROUND(A1,2). The tempting wrong answer, 2469.14, is exactly the error a beginner makes by trusting the screen over the storage.

P3 (guided). Numbers stored as text. A pasted column of amounts is left-aligned with green triangles, and =SUM() over it returns 0. Name three distinct ways to convert the whole column to real numbers, and say which cell property first tipped you off.

Solution. The tell is left-alignment (numbers default to right-aligned; text to left), reinforced by the green triangle and the "Number stored as text" warning. Three fixes: (1) select the range and use the warning triangle ▸ Convert to Number; (2) Paste Special ▸ Multiply by a cell containing 1 (arithmetic coerces text to number in place); (3) Data ▸ Text to Columns ▸ Finish, which re-parses the column. A fourth is =VALUE(cell) in a helper column. Merely changing the number format to "Number" does not fix it: that is the trap; formatting is a mask and cannot change a value's underlying type.

P4. Fill series. You type 100 in A1 and 125 in A2, select both, and drag the fill handle down through A6 (six cells total). List the values and their sum.

Solution. Two seeds set a step of 25, so the series is 100, 125, 150, 175, 200, 225; the sum is 975. The trap: dragging from a single seed (100 alone) copies rather than steps, giving 100 six times (sum 600). Two seed cells are what teach Excel the pattern.

P5. Structured reference total. A Table Sales has a Revenue column with values 540, 900, 1680, 1820, 3250. Give the formula that totals it and the result, and explain why it is safer than the coordinate form.

Solution. =SUM(Sales[Revenue]) returns 8,190. It is safer than =SUM(D2:D6) because the structured reference names the column, so it automatically includes any rows added later and never points at the wrong rectangle after an insert or sort: the coordinate form must be maintained by hand and silently goes stale.

P6. Text-to-number coercion. A column contains the text entries "1250", "980", "2340", "560". After coercing them to real numbers, what does AutoSum report? What did SUM report before coercion?

Solution. After coercion the total is 1250 + 980 + 2340 + 560 = 5,130. Before coercion SUM ignores text and returns 0: the dangerous "too-low total" failure. Always confirm inputs are numeric (right-aligned) before trusting any total.

P7. Transpose dimensions. You copy a range that is 3 rows tall and 5 columns wide and Paste Special ▸ Transpose into an empty area. What are the resulting dimensions, and how many cells does the result contain?

Solution. Transpose swaps the axes, so 3×5 becomes 5 rows by 3 columns. The cell count is unchanged at 3 × 5 = 15. The common error is to expect the shape to stay 3×5 (only the contents moved), transpose moves the orientation: row 1 becomes column 1.

P8. Merged-cell diagnosis. A colleague's data region will not sort ("This operation requires the merged cells to be identically sized"), and Ctrl+Shift+↓ behaves erratically. What is the likely cause, how do you fix it, and what should they have used for their centred heading instead?

Solution. The region contains merged cells, which break sorting, jump-selection, and Table creation. Fix: select the region, Home ▸ Merge & Center ▸ Unmerge Cells, then convert to a Table (Ctrl+T) to keep it clean. For a heading centred across several columns without merging, use Format Cells (Ctrl+1) ▸ Alignment ▸ Center Across Selection, which looks identical and leaves every cell independently addressable.

P9. Static versus live date. You want a permanent "data pulled on 20-Jul-2026" stamp that will not change tomorrow. Which entry do you use, and which function would be wrong here and why?

Solution. Use the static keyboard stamp Ctrl+;, which writes today's date as a fixed value. =TODAY() would be wrong because it is volatile: it recomputes to the current date every time the sheet calculates, so the "pulled on" stamp would silently roll forward and misdate your data. (=NOW() is the volatile equivalent for date-and-time; Ctrl+Shift+: is its static counterpart.)

P10. Rescue a CSV. You are emailed q3_export.csv, add several formula columns, and press Ctrl+S. What just happened, and what should your very first action have been on opening the file?

Solution. Saving with Ctrl+S writes back to .csv, a plain-text format that stores no formulas and no formatting: your added columns are saved only as their current values (and any formatting is discarded), with only a mild warning. The first action on opening any .csv should have been F12 (Save As) to .xlsx, converting it to a real workbook before doing any work, so formulas survive.

Applied mini-project

Clean the messy sales export.

You receive regional_sales_export.csv: a two-region weekly sales dump that arrived from a point-of-sale system, and it is a mess in every way you have now learned to name. Your task is to turn it into one clean, trustworthy, totalled Table that a manager could read without a single follow-up question.

The file's problems (all present at once):

• It opened as a .csv, so nothing is a formula and no formatting survived the export.
• Some numeric columns (Units, Unit_Price) arrived as text: left-aligned, green triangles, and any SUM over them reads 0.
• The Store_Code column is a five-digit code whose leading zeros were dropped (you see 88, 1204, 705 where the real codes are 00088, 01204, 00705).
• Someone "helpfully" merged the two cells above each region block into a centred banner, which now blocks sorting and Table creation.
• There is no Revenue column and no totals, and the Sale_Date column mixes a few live =TODAY() cells with real dates.

Do this, in order:

1. Rescue the format. F12 ▸ Save As ▸ .xlsx before touching anything, so your work will persist as formulas.
2. Kill the merges. Select the banner rows, Home ▸ Merge & Center ▸ Unmerge; replace any needed heading with Format Cells ▸ Alignment ▸ Center Across Selection.
3. Table it. Select the data block and press Ctrl+T; name it Sales in Table Design.
4. Fix the types. Coerce Units and Unit_Price to real numbers (Text to Columns ▸ Finish, or Paste Special ▸ Multiply by 1); rebuild Store_Code as text with =TEXT([@Store_Code_Raw],"00000") or Flash Fill (Ctrl+E).
5. Freeze the live dates. Select the Sale_Date column, copy, and Paste Special ▸ Values in place so no =TODAY() cell can roll forward and misdate the record.
6. Add Revenue and total. New column Revenue with =[@Units]*[@Unit_Price]; turn on the Total Row (or Alt+=) to sum Revenue and count rows.
7. Anchor the view. Freeze the header row (View ▸ Freeze Panes ▸ Freeze Top Row) so a scrolling reader never loses the column names.
8. Save it right. Name the file Regional_Sales_Clean_YYYY-MM-DD_v01.xlsx and confirm AutoSave/Version History is on if it lives on OneDrive.

Deliverable and self-check. One .xlsx with a single clean Sales Table: every number right-aligned and real, Store_Code showing its leading zeros as text, no merged cells anywhere in the data, a working Revenue column, a Total Row that foots, the header frozen, and the dates static. Score yourself honestly: 2 points each for (a) types all real: nothing left as text that should be numeric; (b) zeros reconstructed and stored as text; (c) merges gone and sorting works; (d) Revenue and Total correct and auto-expanding; (e) dates frozen and file saved as versioned .xlsx. Pass at 8/10 with no zero on any item. Keep the file: the "Table-first, types-real, totals-live" reflex you just drilled is the opening move of every data task in the rest of this track.

Lab: open the Clean the messy sales export workbook lab in Practice ▸ Excel and pass its checks.

Reading & resources

Microsoft's own documentation (free, authoritative, and current):

• Microsoft Support, "Overview of Excel tables" and "Create and format tables": the definitive reference for Ctrl+T behaviour, structured references, and the total row. [Free] [Beginner]
• Microsoft Support, "Available number formats in Excel" and "Format numbers": confirms in Microsoft's words that formatting changes display, not the stored value. [Free] [Beginner]
• Microsoft Support, "Use Flash Fill in Excel" and "Fill data automatically in worksheet cells": the fill handle, series, and Ctrl+E. [Free] [Beginner]
• Microsoft Support, "Move or copy cells using Paste Special" and "Keyboard shortcuts in Excel": the Paste Special options and the full shortcut list to drill from. [Free] [Beginner]
• Microsoft Support, "Freeze panes to lock rows and columns" and "What is AutoSave?": freezing, splitting, and the OneDrive/SharePoint requirement for continuous saving. [Free] [Beginner]

Practice and habit-building:

• Exceljet, "Excel keyboard shortcuts" and its short articles on Tables and number formats: crisp, example-led, ideal for building the muscle memory in the 25-moves table. [Free] [Beginner]
• Chandoo.org, the "Excel basics" and "Excel keyboard shortcuts" posts: an analyst-oriented take with an Indian-context flavour. [Free] [Beginner]
• Leila Gharani and ExcelIsFun (YouTube): free video walkthroughs of Tables, Flash Fill, and Paste Special; watch one on Tables before the mini-project. [Free] [Beginner]
• John Walkenbach / Michael Alexander, Excel Bible (current Microsoft 365 edition), Parts on the interface, ranges, and data entry: the standing desk reference when a detail here needs depth. [Paid] [Beginner-Intermediate]

Do this, not just read: the only way this sticks is repetition on a real keyboard. Take any spreadsheet you have, hide the mouse for ten minutes, and navigate and total it with the 25 moves alone. The awkwardness is the learning.

The Modern Analyst's Addendum

Everything above teaches this skill from first principles, by hand: that is how you learn it, and the mastery check still tests it that way. This addendum shows how a working analyst amplifies the same skill today. It adds; it never replaces.

AI-Augment this skill

``ai-augment-json { "skill": "Fast, auditable navigation and data entry — keyboard moves, Tables (Ctrl+T), Paste Special, Flash Fill, freeze panes, and file hygiene (.xlsx vs .csv, AutoSave)", "use": "Ask an assistant to draft the keyboard-and-Table checklist for a new workbook, or to explain a symptom you're staring at -- a column that's left-aligned with green triangles and won't SUM, or a pivot that silently missed last week's rows -- then execute the fix yourself with the actual tool (Text to Columns, Ctrl+T) rather than trusting the explanation.", "tools": ["Microsoft Copilot in Excel", "Google Sheets' Gemini side panel", "a general-purpose LLM chat (Claude or ChatGPT) for explaining an error symptom in plain English"], "prompt": "My Revenue column is left-aligned, shows a green triangle in every cell, and =SUM() over it returns 0. What is going on and what are the two or three ways to fix it in Excel, in order of how much they change the underlying data?", "verify": "Whatever the assistant proposes, confirm it against the sheet itself, not against the explanation: after coercing to numbers, does =SUM() now return the correct total, and does =ISNUMBER() on the column return TRUE for every cell? An assistant can describe a fix fluently and still be vague about which specific cells it applies to.", "diy": "You must be able to convert a raw range to a Table with Ctrl+T, diagnose and fix numbers stored as text, and reconstruct a padded code with Flash Fill or TEXT -- with no assistant in the room. The mastery gate tests exactly these moves, unaided." } ``

Modern Data Analysis

By hand first. Worked Example 1 turned Chandni's ten-row text paste into a Table with a structured-reference Revenue column, totalling ₹29,050 on 73 units. The whole point of Ctrl+T over a plain range was that the Table auto-expands: tomorrow's new rows are absorbed with zero edits.

Today's workflow. Prove that claim rather than take it on faith, and prove its opposite too, because the opposite is the mistake most inherited workbooks actually contain. A Table's SUM and a hardcoded range SUM(D2:D11) look identical on day one. They diverge the day someone adds rows: the Table's total updates; the hardcoded range keeps reporting the old figure, with no error, no red cell, nothing a reader would notice.

Tools & sources (IN + US). Excel Tables and the Total Row for the discipline itself; the same habit applies to any file an analyst pulls in for a first look: India: NSE's daily equity Bhavcopy (end-of-day market data, published as a downloadable CSV built for exactly this Ctrl+T-and-total workflow) and MOSPI's data.gov.in time-series downloads; US: FRED's CSV/Excel export for any series (for example CPIAUCSL) and SEC EDGAR's quarterly Financial Statement Data Sets: all four are flat files an analyst Ctrl+T's into a Table before doing anything else with them.

```python # recomputed in-session — R3 # WE1's own ten row-revenue figures (module text), extended by 5 more days at the same # average, comparing a Table-style SUM (auto-expands) against a frozen SUM(D2:D11)-style range. we1_rows = [2700, 3420, 1680, 1820, 3250, 3600, 3520, 3740, 2200, 3120] we1_total = sum(we1_rows) assert we1_total == 29050 # matches the module's own WE1 total exactly

avg_per_row = we1_total / len(we1_rows) new_rows = 5 true_new_total = we1_total + new_rows * avg_per_row # what the Table (auto-expanding) reports hardcoded_report = we1_total # what a frozen SUM(D2:D11) still reports missing = true_new_total - hardcoded_report print(f"Table total: {true_new_total:.0f} Frozen-range total: {hardcoded_report} " f"Missing: {missing:.0f} ({missing/true_new_total:.1%} of the true total, no error shown)") ```

Verify. The script reproduces WE1's own ₹29,050 total from the module's own ten row-figures before touching anything else, and WE2's US total recomputes the same way to $6,916.89; both match the module's stated numbers exactly. The extension (5 more days at the same average) is a constructed illustration, clearly labelled as such, not new module data: with 5 rows added beyond the frozen range, the hardcoded SUM misses 33.3% of the true total while showing no error at all: the entire argument for Ctrl+T over a plain range, made numeric instead of asserted.

Where this goes next: galaxy cross-links

• G2 · CS2.02 Version Control with Git: Ctrl+Z and a Table's self-describing structure are a single undo and a readable range name; Git is the same instinct (a durable, inspectable record of every change) generalised to any file, with a history a spreadsheet cannot give you.
• G2 · DA1.02 pandas I: Data Wrangling: a pandas DataFrame is the direct code-native analog of the Table you just learned: typed columns, no manually-guessed ranges, and an operation that is either applied to the whole column or fails loudly, never half-applied.

Flashcards

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.

Mastery check

Two parallel forms. Closed book, no software, calculator allowed, ~25 minutes per form. Numeric answers within ±2% score as correct. Pass threshold: ≥ 85%, with 12 one-point items, that is 11 of 12. Passing either form unlocks the next node: formulas, references, and logic. If you score 9–10, redo the related practice problems and sit the other form after a short break.

Form A

A1 (MCQ). The Name Box (far left of the formula bar) can be used to: (a) change a cell's font (b) type an address like Z500 and jump there, or name a selected range (c) insert a chart (d) toggle AutoSave

A2 (numeric). A Table row has Units = 14 and Price = ₹265. What does =[@Units]*[@Price] return (in ₹)?

A3 (numeric). AutoSum a Revenue column with the values 420, 135, 660, 275, 510. What is the total?

A4 (MCQ). The main reason to press Ctrl+T on a data block before analysis is that a Table: (a) makes the file smaller (b) provides structured references, auto-expansion, filters, and a safe total row (c) prevents anyone from editing it (d) converts text to numbers automatically

A5 (numeric). A cell stores 78.6 but is formatted to show 0 decimal places. What number is displayed?

A6 (numeric). You seed a fill series with 5 and 10 and drag down. What is the value in the 8th cell?

A7 (MCQ). Pasting with Paste Special ▸ Values over a cell that held a live formula will: (a) keep the formula and update the value (b) replace the formula with a static number, with no warning (c) raise an error and stop (d) convert the formula to text

A8 (numeric). You copy a block that is 4 rows by 6 columns and Paste Special ▸ Transpose. How many rows does the result have?

A9 (MCQ). Flash Fill (Ctrl+E) is best described as: (a) a live formula that recalculates (b) a pattern-reader that fills static values inferred from your examples (c) a way to freeze panes (d) a number-formatting tool

A10 (MCQ). A colleague's region won't sort ("merged cells must be identically sized") and jump-selection misbehaves. The cause and best structural fix are: (a) numbers stored as text; use VALUE (b) merged cells; unmerge and use Center Across Selection for headings (c) frozen panes; unfreeze (d) a corrupted file; reopen

A11 (numeric). After coercing the text entries "1450", "260", "830" to real numbers, what does AutoSum report?

A12 (short). State, in one sentence each, (i) what a number format changes and does not change, and (ii) how you make a value's stored precision actually change.

Form A key. A1: b. Jump-to and named ranges are the Name Box's jobs. A2: 14 × 265 = ₹3,710. A3: 420+135+660+275+510 = 2,000. A4: b. The four Table disciplines (note (d) is false: a Table does not auto-convert text). A5: 78.6 rounds for display to 79 (the stored value is still 78.6). A6: seeds 5 and 10 give step 5, so the nth value is 5+5(n−1); the 8th is 5+35 = 40. A7: b. The silent formula-destroyer. A8: 4×6 transposes to 6 rows by 4 columns. A9: b. A10: b. A11: 1450+260+830 = 2,540 (before coercion, SUM would read 0). A12: (i) a number format changes only how a value is displayed, never the value stored or used in calculations; (ii) use =ROUND(value, digits) to write a genuinely new stored number.

Form B

B1 (MCQ). To jump instantly to cell AB900 from anywhere, the fastest way is to: (a) scroll with the wheel (b) type AB900 in the Name Box and press Enter (c) use Find (d) press Ctrl+End

B2 (numeric). A Table row has Units = 18 and Price = ₹145. What does =[@Units]*[@Price] return (in ₹)?

B3 (numeric). AutoSum an Amount column with the values 230, 145, 905, 410. What is the total?

B4 (MCQ). Which statement about Excel Tables is TRUE? (a) They cannot be sorted (b) A formula typed in one Table cell fills the whole column and new rows are absorbed automatically (c) They store numbers as text (d) They disable filters

B5 (numeric). A cell stores 142.4 but is formatted to show 0 decimal places. What number is displayed?

B6 (numeric). You seed a fill series with 3 and 7 and drag down. What is the value in the 6th cell?

B7 (MCQ). You want to send a client only the numbers from a model, with no formulas revealed. You should: (a) delete the formula bar (b) copy, then Paste Special ▸ Values into a new area (c) hide the columns (d) change the number format

B8 (numeric). You copy a block that is 7 rows by 3 columns and Paste Special ▸ Transpose. How many columns does the result have?

B9 (MCQ). Ctrl+E (Flash Fill) would be the efficient tool to: (a) sum a column (b) reconstruct a padded SKU like 00089 from 89 across many rows from one or two examples (c) freeze the top row (d) create a chart

B10 (MCQ). A pasted column of amounts is left-aligned, shows green triangles, and =SUM() over it returns 0. The most likely cause is: (a) the numbers are negative (b) the numbers are stored as text and must be coerced to numbers (c) the column is too narrow (d) AutoSave is off

B11 (numeric). After coercing the text entries "990", "1240", "575", "300" to real numbers, what does AutoSum report?

B12 (short). Explain in one or two sentences why saving a working model as .csv is risky, and what format you should use instead.

Form B key. B1: b. The Name Box is the jump tool. B2: 18 × 145 = ₹2,610. B3: 230+145+905+410 = 1,690. B4: b. Auto-fill and auto-expansion are core Table behaviours; (a),(c),(d) are all false. B5: displays 142 (stored value still 142.4). B6: seeds 3 and 7 give step 4, so the nth value is 3+4(n−1); the 6th is 3+20 = 23. B7: b. Values-only paste strips the formulas. B8: 7×3 transposes to 3 rows by 7 columns. B9: b. Pattern reconstruction is Flash Fill's specialty. B10: b. Left-alignment + green triangle + zero SUM is the numbers-as-text signature. B11: 990+1240+575+300 = 3,105. B12: .csv is plain text that stores no formulas and no formatting, so saving a model to it silently discards all your logic; keep working files as .xlsx (Save As / F12), using .csv only for raw data exchange.

Teach it back & journal

Feynman prompt. Explain to a smart friend who has only ever "typed into boxes" in a spreadsheet why the sentence "the number you see is not always the number Excel is using" is true, using the 1 + 1 = 3 demonstration (two cells each holding 1.4, formatted to zero decimals). Then explain, in the same breath, why a SKU like 00089 must be stored as text while a price like 89 must be stored as a number, and what goes wrong if you get either backwards. If your friend can predict both surprises before you show them, you understand the display-versus-storage idea, which is the deepest concept here.

Journal prompt. For one working day, force yourself to navigate and total spreadsheets without the mouse, using only the 25 moves. Note which three shortcuts felt most unnatural and which one saved you the most time. Then write a paragraph on this: every senior analyst you will ever watch looks "fast" not because they think faster but because they removed the mouse from the loop for selection, entry, and totalling: the exact skills you have just drilled. Which single habit from here (Table-first, Ctrl+Shift+arrow, checking a total's inputs are real numbers, or freezing the header) would most change how a colleague sees your work, and what will you do this week to make it automatic?

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.