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.

You don't graduate from this. The whole tower stands on it. The most expensive errors in finance are almost always failures of this basic material, and nothing advanced is involved: the mis-totalled model, the number that was really text, the merged cell that broke a sort the night before a deadline.

Think of Excel as an addressable machine. It's a vast lattice of memory locations, each with a name (A1, C5, AZ1200) and each able to hold a raw value or a formula that computes one. All of its power 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.

Get the address system right, along with the ways to select and move across it, the Tables that keep data clean, and the gap between what a cell shows you and what it stores. Everything downstream depends on them. Skip them and you'll spend the rest of the curriculum fighting your own spreadsheets.

What follows treats the grid as an analyst's weapon. It assumes Microsoft 365 throughout and gives every action twice, once as a menu path such as Home ▸ Number ▸ … and once as its keyboard alternative. Speed comes from the keyboard.

By the end you'll be able to paste a filthy sales export, with text where numbers should be, leading zeros gone, formats fighting you, and turn it into a clean, totalled Table you can trust. It takes about ninety seconds and no more than two touches of the mouse.

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 start of the Excel track, the true white belt. If you've used spreadsheets casually, expect some of your habits to be rewired. Most self-taught users navigate with the mouse and format numbers they think they are changing, and both habits have to go.

Feeds forward. Everything. EX0.02 covers formulas, operators, the order of evaluation, and the absolute/relative reference model behind F4. It 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 is in clean Tables holding real numbers, with no text posing as numbers. The commonest reason a lookup "returns nothing" is a type mismatch born at data entry.

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 like the ones you'll clean here. If you can't get it into the grid cleanly, you never get to the analysis at all.

Data entry is the floor of the whole building.

Core teaching content

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 wrecked without anyone noticing. Paste Special (Ctrl+Alt+V, or Home ▸ Paste ▸ Paste Special) opens a dialog where you choose 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 and drops the formulas themselves. You'll want it to freeze a computed column, break a link to another workbook, or send someone numbers without your logic. It's also the most dangerous paste of the lot. Paste values on top of a formula and live logic becomes a dead number, and nothing warns you. Common Mistakes comes back to this.
• Formats (then T). This copies only the look (number format, font, fill, borders) and none of the content. It's the keyboard-free way to make one range look like another.
• Formulas (then F). Content without the formatting.
• Transpose (the checkbox, or E). This flips rows and columns. A copied block 3 rows tall and 5 columns wide pastes back 5 rows tall and 3 columns wide. The cell count stays at 15 and only the orientation changes. It's invaluable when a source lays data out the wrong way for your model.

The dialog also hides a gem, the Operations section (Add, Subtract, Multiply, Divide). Copy a cell containing 1, select a range of numbers stored as text, and choose Paste Special ▸ Multiply. Every cell is multiplied by 1, which coerces the 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 route to the same dialog, Alt+E+S, still works. It's worth knowing because a generation of analysts has it in their fingers.

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 choose View ▸ Freeze Panes ▸ Freeze Panes. Click B2 first and both row 1 and column A stay put. Two shortcuts on that menu cover the common cases. Freeze Top Row keeps headers visible, and Freeze First Column keeps row labels visible. From the keyboard it's 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) divides the window into up to four independently scrollable panes. You can then view two far-apart regions of the same sheet at once. Put row 5 beside row 5,000, say. Unlike freeze, both panes scroll. You drag the split bars to size them.

Use Split for comparison and Freeze for anchoring. Remove either from the same menu.

Large workbooks call for two more aids. Ctrl+Page Down and Ctrl+Page Up move between worksheet tabs, and holding Ctrl while pressing the arrow keys crosses a big region without the scroll wheel. Zoom with Ctrl+mouse-wheel or the slider at the bottom right when you need a 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 handling as part of the craft.

Save working files as .xlsx, the modern, compressed format that keeps your formulas. .xls is the pre-2007 legacy format, with a smaller row limit and no modern features. Avoid it unless a counterparty demands it. .csv is plain text with no formulas, no formatting, one sheet only. It's perfect for exchanging raw data with other systems and useless as a working model. It's an easy trap to walk into. Open a .csv, work in it and press Ctrl+S, and Excel 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.

In Microsoft 365 the AutoSave toggle is at the very top-left of the window. It saves continuously only if the file is stored on OneDrive or SharePoint. A file on a local drive can't AutoSave, and beginners get burned when they assume protection they don't 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. It's the safety net for "I broke it an hour ago." For any file, Ctrl+S saves now and F12 is Save As.

Name files so that a stranger, or you in six months, can tell what they are and which one is current. Project_Descriptor_YYYY-MM-DD_v03.xlsx beats final_final_USE THIS.xlsx. Behind the naming is the idea of 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, so use them.

Keyboard-first: the 25 moves

Analysts who look fast think at the same speed as you do. They've taken the mouse out of the loop for everything except pointing at a chart. Below is the core vocabulary. Don't memorise it as a list. Use the moves that go with each section above until your fingers know them, then keep the table as a reference. These shortcuts are for Windows Microsoft 365. On macOS, swap Ctrl for ⌘ in most cases.

The last row is a combination of two shortcuts, 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 means stringing these together without looking.

Spend the drill time here, because it pays you back every 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. It makes sorting refuse with "This operation requires the merged cells to be identically sized". It blocks Ctrl+T over the region, and it confuses every formula that points into it, since only the top-left cell holds the value and 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. It looks identical and breaks nothing. Merged cells belong on a final printed cover page and never in 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. Then the total fails to foot by a penny and they can't see why. The expert knows the format is a mask and reaches for =ROUND() whenever the stored precision itself must change. They 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. It's left-aligned instead of right-aligned, often with a little green triangle and the warning "Number stored as text." Text doesn't sum. SUM silently ignores it and returns a total that is too low, and lookups fail to match. The expert spots it instantly, because left-alignment is the tell. The fixes are 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've 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 they sort one column alone and scramble every record. The expert converts to a Table first, so totals auto-expand, references are named and explain themselves, and sorting respects the Table's boundaries. 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 them. Ask them whether a cell is a formula or a hard-coded number and they can always tell you. Select the cell and glance at the formula bar, or use Formulas ▸ Show Formulas (Ctrl+` ``) to X-ray the whole sheet at once.

The expert avoids all five in the same way, by holding a mental model of what each cell contains: value or formula, number or text, stored or merely shown. They never confuse the surface for the substance. That habit, more than any function, is what the white belt teaches.

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. The numbers came in as text, left-aligned with green triangles. There's no Revenue column and no total either. Your job is to 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," and press Enter. Rename it in Table Design ▸ Table Name to Sales. The block is now a first-class object with 10 data rows. Check the count 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, so they hold text. Select both columns' data: click the Units header's data, then Ctrl+Shift+→ and Ctrl+Shift+↓. Then use the green-triangle warning ▸ Convert to Number, or copy an empty cell, select the block, and Paste Special ▸ Multiply. Watch the entries 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 press Enter. The Table auto-expands to take in the new column. In its first data cell type =[@Units]*[@Price] and press Enter. The structured reference means "this row's Units times this row's Price," and because this is a Table the formula fills down all ten rows by itself. Go down the rows with it: 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 and 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 Table that expands itself. Chandni can add three rows beneath it tomorrow and the Revenue formula and the total will 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. You can trust that figure because it was computed on real numbers. A total that ignored text cells, or a Revenue column that forgot the last row, would each have spoiled it. Both were prevented by the routine of 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, such as 00742 and 01050. The export stored them as numbers, so Excel dropped the leading zeros and you see 742, 1050 and 89. The Amount column came in as text, so it won't sum. There are eight rows in A1:B9:

Solution. Step 1: reconstruct the padded SKU two ways. The clean way uses a formula. 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, and "00000" forces five digits, padding with leading zeros. So 742 becomes "00742", 89 becomes "00089" and 7 becomes "00007", each 5 characters long. =LEN("00089") returns 5, which makes a good self-check.

The fast, one-off way is Flash Fill. Type 00742 in the first cell of the new column, begin typing 01050 in the second, and press Ctrl+E. Excel infers "pad to five digits" and fills the rest as static text. Use TEXT when the source may change, and Flash Fill for a throwaway cleanup. Both produce text, and that's correct. A SKU is a label you never do arithmetic on, and its leading zeros have to 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 and choose Data ▸ Text to Columns ▸ Delimited ▸ Finish, accepting the defaults. Excel re-parses each entry as its natural type and the amounts become right-aligned numbers. Paste Special ▸ Multiply by a cell holding 1 does the same job, and so does wrapping each entry in =VALUE(B2). Until this is done, SUM ignores every value and reports 0.

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

Read it. That was two classic import diseases with two clean cures. Leading zeros vanish whenever a code (SKU, ZIP, phone, account number) is stored as a number. Store it as text instead, with TEXT(…,"00000") or Flash Fill. Don't "just reformat". Reformatting a number that has already lost its zeros can't bring them back, because the information is gone and you'd have to reconstruct it. A text Amount column, meanwhile, silently zeroes your total until it's coerced. The verified purchase total is $6,916.89. Had you trusted the raw SUM before coercing, you'd have reported $0.00 and looked foolish. The routine is the same as in Worked Example 1: inspect alignment, fix the type, then total. Only the pair of gremlins changed.

There aren't many of these diseases, 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. Type =[@Units]*[@Price] in the first Revenue cell and it fills down the Table automatically. The row values are 8×125 = 1,000, 15×60 = 900, 6×340 = 2,040, 12×90 = 1,080 and 9×150 = 1,350. The Total Row, Alt+= or =SUM(Orders[Revenue]) all give ₹6,370. A common slip is to hard-code =SUM(E2:E6). It's correct today, yet it'll miss any sixth row added tomorrow without telling you. The structured reference SUM(Orders[Revenue]) is preferred for that reason.

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 really 1234.57? What does this prove?

Solution. =A1*2 computes on the stored value: 1234.567 × 2 = 2469.134, which displays as 2469.13 with 2-decimal formatting. If the cell really stored 1234.57, the product would be 1234.57 × 2 = 2469.14, displayed as 2469.14. The one-cent gap proves the format is only a mask. To make the stored value 1234.57 you have to write =ROUND(A1,2). The tempting wrong answer, 2469.14, is 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, since numbers default to the right and text to the left. A green triangle and the "Number stored as text" warning back it up. There are three fixes. (1) Select the range and use the warning triangle ▸ Convert to Number. (2) Use Paste Special ▸ Multiply by a cell containing 1, where the arithmetic coerces text to number in place. (3) Use Data ▸ Text to Columns ▸ Finish, which re-parses the column. A fourth is =VALUE(cell) in a helper column. The trap is to change the number format to "Number", which fixes nothing. Formatting is a mask and can't 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. Drag from a single seed, 100 alone, and Excel copies it instead of stepping, giving 100 six times for a sum of 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's safer than =SUM(D2:D6) because the structured reference names the column. It automatically includes any rows added later and never points at the wrong rectangle after an insert or sort. The coordinate form has to be maintained by hand, and it goes stale without a sound.

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 that inputs are numeric, which means right-aligned, before you trust 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 with only the contents moved. Transpose changes the orientation, so 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 it by selecting the region and choosing 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. It 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's volatile. It recomputes to the current date every time the sheet calculates, so the "pulled on" stamp would roll forward unnoticed and misdate your data. =NOW() is the volatile equivalent for date and time, and 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. Any formatting is discarded, and Excel gives only a mild warning. Your first action on opening any .csv should have been F12 (Save As) to .xlsx. That turns it into a real workbook before you do 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 from a point-of-sale system. It's a mess in every way you've now learned to name. Your task is to turn it into one clean, totalled Table that a manager could read and trust without a single follow-up question.

The file has all of these problems at once:

• It opened as a .csv, so nothing is a formula and no formatting survived the export.
• Some numeric columns, Units and Unit_Price, arrived as text. They're left-aligned with 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 and 705 where the real codes are 00088, 01204 and 00705.
• Someone "helpfully" merged the two cells above each region block into a centred banner, which now blocks sorting and Table creation.
• There's 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 and choose Home ▸ Merge & Center ▸ Unmerge. Replace any heading you need with Format Cells ▸ Alignment ▸ Center Across Selection.
3. Table it. Select the data block and press Ctrl+T, then name it Sales in Table Design.
4. Fix the types. Coerce Units and Unit_Price to real numbers with 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. Create a new column Revenue with =[@Units]*[@Unit_Price]. Turn on the Total Row, or use 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's stored on OneDrive.

Deliverable and self-check. Hand in 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. Be strict with yourself when you score it, 2 points each for: (a) types all real, with 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". These confirm 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", for the fill handle, series, and Ctrl+E. [Free] [Beginner]
• Microsoft Support, "Move or copy cells using Paste Special" and "Keyboard shortcuts in Excel", for 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?", for 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 and example-led, and 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), with 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), the Parts on the interface, ranges, and data entry. It's the standing desk reference when a detail here needs depth. [Paid] [Beginner-Intermediate]

Reading won't make this stick. Repetition on a real keyboard will. Take any spreadsheet you have, hide the mouse for ten minutes, and navigate and total it with the 25 moves alone. It'll feel awkward, and 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

{
  "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 reason to use Ctrl+T over a plain range was that the Table auto-expands, absorbing tomorrow's new rows with zero edits.

Today's workflow. Prove that claim instead of taking it on faith, and prove its opposite too, because the opposite is the mistake most inherited workbooks contain. A Table's SUM and a hardcoded range SUM(D2:D11) look identical on day one. They part ways the day someone adds rows. The Table's total updates, while 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 are the tools for the habit itself, and the habit applies to any file an analyst pulls in for a first look. In India, there's NSE's daily equity Bhavcopy, which is end-of-day market data published as a downloadable CSV built for this Ctrl+T-and-total workflow, and MOSPI's data.gov.in time-series downloads. In the US, there's FRED's CSV/Excel export for any series, CPIAUCSL for example, and SEC EDGAR's quarterly Financial Statement Data Sets. All four are flat files you'd turn into a Table with Ctrl+T before doing anything else with them.

# 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. Before touching anything else, the script reproduces the first worked example's ₹29,050 total from its ten row figures. The US total from the second worked example recomputes the same way to $6,916.89. Both match the figures stated above. The extension of 5 more days at the same average is a constructed illustration and is labelled as such. It adds no new 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. That's the case for Ctrl+T over a plain range, put in numbers 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.

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 idea. Every senior analyst you'll ever watch looks "fast" without thinking any faster than you. They removed the mouse from the loop for selection, entry and totalling, the same skills you've just drilled. Which single habit from here would most change how a colleague sees your work: Table-first, Ctrl+Shift+arrow, checking a total's inputs are real numbers, or freezing the header? 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.