Faragiri

Learn The basics Excel

A logistics office in Shymkent: what a cell actually holds, the four reference forms and when each is the only one that works, and the eight keys worth learning.

7Sections
4Min read
4Depth

Zaripa builds a rate card in a logistics office in Shymkent: 18 destinations down the side, 6 weight bands across the top, and one formula that has to work in all 108 cells.

Typed once and dragged, it produces 108 wrong answers. The reason is that a reference has four forms, and only one of them survives being dragged in two directions at once.

A cell holds three separate things

What is in it, what is shown, and how it is shown. They are independent, and almost every confusing spreadsheet moment comes from mistaking one for another.

Formula bar vs cell display

The three independent layers operating inside every spreadsheet cell

Confusing what is stored with what is shown causes most arithmetic errors and failed lookup formulas.

  1. Underlying storageFormula or raw valueThe true content visible in the formula bar, such as =B4*1.2 or unrounded decimals
  2. 02Calculated valueThe mathematical result computed by Excel and passed into subsequent cell references
  3. 03Formatted displayThe visual appearance on the worksheet, rendered as 3, 3.00, or local currency

Changing formatting alters only the visual layer; a column displaying whole numbers can still introduce fractional discrepancies into summary totals.

A cell containing =B4*1.2 holds a formula, shows a number, and might display it as 3 or as 3.00 or as a currency. Changing the display changes nothing about the value, so a column that looks like whole numbers can still be adding up to something with decimals in it.

The tell is the formula bar, which shows what is actually there. A number that sits on the left of its cell without being told to is text, and text that looks like a number is the cause of most failed sums.

The four reference forms

A dollar sign locks whatever comes immediately after it, so there are four combinations and each is the right answer to a different question.

One formula, 108 cells

Four reference forms, and the rate card needs the two nobody learns

Everybody knows the top-left and the bottom-right. A grid dragged in two directions at once is built entirely out of the other two.

the column movesthe column is locked

the row moves

A1Moves both ways. The right answer for a cell doing the same job to its own row
  • =B4*1.2 dragged down
  • becomes =B5*1.2
$A1Column locked, row free. Dragged across the six weight bands it still reads column A
  • =$A2*B$1 dragged right
  • still points at the distance

the row is locked

A$1Row locked, column free. Dragged down the 18 destinations it still reads row 1
  • =$A2*B$1 dragged down
  • still points at the rate
$A$1Neither moves. One constant sitting somewhere on the sheet
  • a single markup cell
  • a fixed exchange rate

The grid is about copying, and a formula that is never copied works with any of the four. Everything here is a statement about what happens when one cell becomes 108.

A1 moves both ways when copied, which is what you want for a cell doing the same job to each row of its own data.

$A$1 moves neither way, which is the form for a single constant sitting somewhere on the sheet.

The two mixed forms are the ones people never learn and they are what Zaripa's rate card needs. $A2 locks the column and lets the row move: dragged across the six weight bands, it keeps pointing at the distance in column A. A$1 locks the row and lets the column move: dragged down the 18 destinations, it keeps pointing at the rate in row 1.

Her working formula is =$A2*B$1, typed once in the top-left cell and dragged across and down. One formula, 108 correct cells.

F4 pressed while the cursor is on a reference cycles through all four forms, which is faster than typing dollar signs and is how people who do this all day do it.

The reference that survives inserted rows

Everything above breaks the moment somebody inserts a row in the middle, because a range written as B2:B19 does not know about row 7 appearing.

Row insertion resilience

Fragile coordinate boundaries versus self-expanding table references

Formulas written against raw coordinate ranges fail silently when new records appear in the middle of a dataset.

Coordinate range (B2:B19)Standard grid coordinates
Ctrl+T table (Rates[Freight])Structured table reference
Inserted row handlingIgnores new row 7 inserted outside original formula boundsExpands calculation automatically to include newly added rows
Formula readabilityCryptic cell coordinates obscure the operational purposeExplicit column names state business logic directly
Long-term maintenanceQuietly produces inaccurate sums as data grows over timeMaintains structural integrity across years of workbook use

Pressing Ctrl+T converts raw cells into a structured table, making formulas durable against row insertions and structural edits.

The fix is a table. Selecting the range and pressing Ctrl+T names it, and formulas written against it use column names instead of coordinates, so a new row is included automatically and a formula reading =SUM(Rates[Freight]) is still correct after somebody inserts fourteen rows.

This is the single largest reliability difference between a spreadsheet that survives a year and one that quietly stops being right.

The eight keys

Ctrl and an arrow jumps to the end of a block of data, which is how you find out a sheet has 40,000 rows rather than 900. Adding Shift selects to there.

Speed and navigation

Five core keyboard shortcuts that replace repetitive mouse clicking

Mastering foundational keystrokes keeps hands on the keyboard and uncovers sheet boundaries instantly.

Core shortcuts
  • Ctrl+Arrow and Ctrl+Shift+ArrowJumps to data edge or selects to boundary, revealing 40,000 rows instantly
  • Ctrl+HomeReturns the active cursor immediately to cell A1 from anywhere in the sheet
  • F2 and F4F2 edits the formula in place; F4 cycles through the four reference locking states
  • Ctrl+; and Alt+EnterCtrl+; inserts current date as a static value; Alt+Enter creates in-cell line breaks
  • Ctrl+Shift+VPastes clipboard contents as values only, converting dynamic calculations to constants

These keyboard shortcuts prevent navigation fatigue and ensure formula references are toggled cleanly without manual typing.

Ctrl+Home returns to A1 from anywhere. F2 edits in place rather than overtyping. F4 cycles references, and in some contexts repeats the last action. Ctrl+; enters today's date as a fixed value rather than a formula that changes tomorrow. Alt+Enter puts a second line inside one cell. And Ctrl+Shift+V pastes values only, which is how a formula becomes a number on purpose.

Freeze the headers before anything else

View, then Freeze Panes, with the cursor one row below and one column right of what should stay visible. On an 18 by 6 grid it barely matters; on Zaripa's 4,000-row shipment log it is the difference between reading a sheet and guessing which column you are in.

Freeze Panes navigation

Locking header context while scrolling through thousands of records

Placing the freeze point below header labels keeps column definitions visible across deep logs.

Excel View -> Freeze Panes
  1. 01Row 1: Destination | Weight Band | Distance | Base Rate
  2. 02Cursor placed at cell B2 before toggling Freeze Panes
  3. 034,000 shipment rows moving beneath fixed headers
  1. 01Frozen header bandStays pinned at top of viewport during vertical scrolling
  2. 02Freeze split coordinateEstablishes boundary between locked labels and scrolling cells
  3. 03Scrollable data gridPrevents guessing column meanings when scrolling deep into logs

On small grids freeze panes is optional; on 4,000-row logistics sheets it is the difference between immediate comprehension and costly misreading.

Where people actually stall

Not on the dollar signs, which are explainable in a minute. On not knowing that mixed references exist, so a grid like the rate card gets built as 108 separately typed formulas, each one a chance to be wrong and none of them checkable.

108 rate card cells

Typing separate formulas versus dragging one mixed reference

Spreadsheet users stall not on basic syntax but on failing to recognize how mixed references build two-way grids.

  • 108 separately typed formulasBuilding without mixed references
    • Requires typing calculations individually in every cell
    • Introduces 108 distinct opportunities for typos
    • Results in different formula variations across rows
  • One mixed reference draggedUsing =$A2*B$1
    • Typed once in top-left cell and dragged across and down
    • Locks distance column and rate row systematically
    • Creates uniform, verifiable calculations across all 108 cells

Rate card construction

A sheet where similar calculations appear in slightly different forms across rows is the telltale symptom of missed mixed references.

The tell is a spreadsheet where the same calculation appears in different forms in different rows. That is always a sign that the reference forms were the missing piece.

What to use, as of 2026

Excel in Microsoft 365, which in 2026 recalculates dynamic array formulas that spill into neighbouring cells, so one formula can fill a range. That replaced copying a formula down 4,000 rows and hoping the copy reached the bottom.

Workbook standards

Four capabilities that define modern spreadsheet construction

Dynamic array spilling and structured tables eliminate manual formula dragging across large sheets.

Modern spreadsheet standard
  • Dynamic array spillMicrosoft 365 formulas spill into neighbouring cells automatically, ending 4,000-row copy-drag operations
  • Cross-platform reference parityGoogle Sheets and Excel share identical dollar sign rules, ensuring skills transfer without relearning
  • Ctrl+T structured tablesSelf-expanding table boundaries replace fragile coordinate ranges across all versions
  • AI formula debuggingAssistants explain formula errors rapidly while humans verify sheet layout and reference bounds

Productivity foundation

While language models interpret formula syntax effectively, validating whether a formula aligns with physical sheet geometry remains a user responsibility.

Google Sheets, free with a Google account, which uses the same reference forms and the same dollar signs, so nothing on this page has to be relearned.

Ctrl+T tables, present in every version since Excel 2007, which replaced named ranges that had to be redefined whenever data grew.

A model will explain what a formula in front of you does and why it broke, free on every major assistant, and it is good at that. It writes formulas against a sheet it cannot see, so a reference it invents is a guess about your layout, which is exactly what the four forms are about.

Building something longer than one operation out of these references is building one, and why it breaks, and pulling a value from another table by matching a key is VLOOKUP, XLOOKUP and INDEX MATCH. Turning the 4,000-row log into a summary is summarising thousands of rows in seconds, under the most used analysis tool in the world.

Alongside this

Above this