• Foundation
  • About 12 min
  • 5 practice questions

Entering data, AutoFill and number formats

Enter text, numbers and dates correctly, extend a series with the fill handle and use number formats such as percentage and currency without changing the stored value.

If something looks wrong, please tell us.

Before you calculate anything, the data must go into the sheet in the right way. In this lesson you learn how to type different kinds of data, how to fill a series quickly and how number formats change what you see. These small skills prevent many beginner mistakes.

Typing data into a cell

Click a cell and type. Pressing Enter accepts the entry and moves down; Tab accepts it and moves right. Pressing Esc cancels the entry. Text is left-aligned and numbers are right-aligned by default, so the alignment itself tells you how the program understood what you typed. If a number is left-aligned, it is probably stored as text, and formulas may ignore it.

A number such as 007 loses its leading zeros because it is treated as the number 7. To keep the zeros, type an apostrophe first (\'007), which makes the cell hold text.

AutoFill and the fill handle

The fill handle is the small square at the bottom-right corner of the selected cell. Dragging it copies or continues the entry across neighbouring cells. If the program can see a pattern, it extends the pattern; if not, it copies.

What the fill handle produces
You enterDrag the handle and you get
MondayTuesday, Wednesday, Thursday and so on
JanFeb, Mar, Apr and so on
1 and 2 (both selected)3, 4, 5 and so on
5 and 10 (both selected)15, 20, 25 and so on
A single number such as 77, 7, 7 (copied) in most programs
A formulaThe formula, with relative references adjusted

To make a number series you must give the program two cells so it can find the step. The step is the difference between the two numbers.

Number formats: what you see and what is stored

A number format changes how a value is shown, not the value itself. The main formats are General, Number (with chosen decimal places), Currency, Percentage and Date. Entering 0.25 and applying the percentage format shows 25%; the stored value is still 0.25. If a column is too narrow for a number, the cell shows ####, and widening the column fixes it.

Worked examples

Example 1. Type 5 in A1 and 10 in A2, select both and drag the fill handle down to A5. The step is 10 - 5 = 5, so the cells hold 5, 10, 15, 20 and 25.

Example 2. Type 100 in B1 and 90 in B2, select both and drag down to B5. The step is -10, so the series reads 100, 90, 80, 70, 60.

Example 3. Type Friday in C1 and drag the handle down three cells. The days continue in order and wrap around: Saturday, Sunday, Monday.

Example 4. A cell holds 0.075. After the percentage format is applied it shows 7.5%. A cell holding 0.25 shows 25%. Both are only display changes, so a formula using the cell still uses 0.075 or 0.25.

Example 5. A cell shows #### after you type a large number. The number is fine; the column is too narrow. Drag the column border to widen it, and the number appears.

A format changes the look, not the value. Two cells give a series; one cell gives a copy.

Key takeaways. Text goes left and numbers go right. The fill handle extends patterns, and two starting cells fix the step. Number formats such as percentage change only the display, and #### means the column is too narrow.

Check your understanding

  1. 1. A1 holds 5 and A2 holds 10. Both are selected and the handle is dragged down. What is in A4?

    1. 20
    2. 15
    3. 25
    4. 10
    Show answer

    Answer: A. The step is 5, so the cells hold 5, 10, 15, 20, 25. A4 is the fourth cell and holds 20.

  2. 2. What does the fill handle do with Jan typed in one cell?

    1. Copies Jan only
    2. Shows an error
    3. Continues Feb, Mar
    4. Changes it into 1
    Show answer

    Answer: C. Month names are recognised as a pattern, so dragging continues Feb, Mar, Apr and so on.

  3. 3. A cell holds 0.25. After the percentage format is applied, the cell shows:

    1. 0.25%
    2. 2.5%
    3. 250%
    4. 25%
    Show answer

    Answer: D. The percentage format multiplies the display by 100 and adds a % sign, so 0.25 shows as 25%. The stored value stays 0.25.

  4. 4. A cell shows ####. What is the usual reason?

    1. The formula has an error
    2. The column is too narrow
    3. The file has been damaged
    4. The cell has been locked
    Show answer

    Answer: B. The value is too wide for the column. Widening the column displays the number.

  5. 5. By default, a number typed into a cell is aligned to the:

    1. Right
    2. Left
    3. Centre
    4. Top
    Show answer

    Answer: A. Numbers are right-aligned and text is left-aligned by default, which helps you see how the entry was understood.

Privacy · Terms · Refunds · Delivery