• Foundation
  • About 15 min
  • 5 practice questions

Entering data, selecting ranges, AutoFill and number formats

Type text, numbers and dates correctly, select cells and ranges, fill series with the fill handle, and change how numbers look without changing their value.

If something looks wrong, please tell us.

A spreadsheet can only calculate with what you put in it, and it decides how to treat each entry the moment you press Enter. If a number is stored as text, a total will skip it; if a date is typed in a form the program does not understand, it becomes plain text. Entering data correctly, and quickly, saves a great deal of correction later.

Three kinds of entry

How a cell treats what you type
You typeStored asDefault alignment
KolkataText (a label)Left
2500NumberRight
15/08/2024 (in a day-first setting)Date (kept internally as a number)Right
'007Text that shows 007Left

By default, text lines up on the left and numbers on the right - a quick way to spot a number that was accidentally stored as text. Dates are stored as serial numbers (a count of days), which is why you can subtract one date from another to get the number of days between them. Codes such as PIN codes or roll numbers with a leading zero lose that zero if stored as numbers; typing an apostrophe first ('007) makes the cell keep them as text, in both programs.

Text or number?

  • Text
    • Lines up on the left
    • Example: Kolkata
    • '007 keeps its leading zero as text
    • A total skips a number stored as text
  • Number
    • Lines up on the right
    • Example: 2500
    • Dates are stored as serial numbers
    • Can be used in calculations

Editing and clearing

  • Click a cell and type to replace its contents.
  • Press F2 (both programs), or click in the formula bar, to edit the existing contents.
  • Delete clears the contents of the selected cells; formatting stays.
  • Ctrl+Z undoes the last action; Esc cancels typing before you press Enter.

Selecting cells

  • Drag across cells, or click the first cell and Shift+click the last, to select a range.
  • Hold Ctrl and click to add cells that are not next to each other.
  • Click a column letter or row number to select the whole column or row.
  • Ctrl+A, or the blank box where the row and column headings meet, selects the whole sheet.

AutoFill and the fill handle

The fill handle is the small square at the bottom-right corner of the selection. Dragging it fills the neighbouring cells. If you type Jan and drag, you get Feb, Mar, Apr; Monday gives Tuesday, Wednesday; Item 1 gives Item 2, Item 3. To continue a number pattern, type the first two values, select both and drag: the program repeats the same step. Dragging a cell that holds a formula copies the formula, adjusting its cell references (you will see how in the next lesson).

Worked example 1: a number series

A1 holds 10 and A2 holds 15. Select A1:A2 and drag the fill handle down to A6. The step is 15 − 10 = 5, so A3 to A6 receive 20, 25, 30 and 35. If you had selected only A1, the program would have no step to follow, so always give two values when you want a particular step.

Number formats

A number format changes how a value looks, not the value itself. Open the Format Cells dialog with Ctrl+1 in either program (Calc: Format > Cells; Excel: the Number group on the Home tab offers the common formats as buttons).

Same values, different looks
Stored valueFormatShows as
1234.5Currency, 2 decimal places₹1,234.50
0.18Percentage, 0 decimal places18%
0.125Percentage, 1 decimal place12.5%
3.14159Number, 2 decimal places3.14
a dateLong date15 August 2024 (for example)

Worked example 2: format versus value

A1 holds 1234.5 formatted as currency, so it shows ₹1,234.50. A formula =A1*2 gives 2469, because the calculation uses the stored value 1234.5, not the text you see. Likewise, 3.14159 shown with two decimals still calculates as 3.14159. If you need the value itself changed, use the ROUND function (lesson 4), not a format. Typing 18% directly into a cell stores 0.18 and applies the percentage format at the same time.

If a cell fills with #####, the column is too narrow to show the number or date; widen the column (double-click the right edge of its heading to fit the contents). Nothing is wrong with the value.

Text aligns left, numbers align right. A format changes the look, never the stored value. Two values selected + fill handle = a series with the same step.

Key takeaways: let the program recognise numbers and dates, use an apostrophe for codes with leading zeros, edit with F2, use the fill handle for series and copies, and use number formats (Ctrl+1) for currency, percentages and dates. ##### only means the column is too narrow.

Check your understanding

  1. 1. You type Kolkata into an empty cell and press Enter. With default settings, how is it aligned?

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

    Answer: D. Text entries line up on the left by default, while numbers and dates line up on the right.

  2. 2. A1 holds 4 and A2 holds 8. You select A1:A2 and drag the fill handle down to A6. What appears in A6?

    1. 24
    2. 32
    3. 128
    4. 20
    Show answer

    Answer: A. The step is 8 − 4 = 4, so the series is 4, 8, 12, 16, 20, 24. A6 is the sixth value, 24. The series adds 4 each time; it does not double.

  3. 3. A cell suddenly shows ##### after you type a long number into it. What is the most likely reason?

    1. The column is too narrow to display the number
    2. The formula has divided by zero
    3. The number was stored as text
    4. The cell refers to a deleted cell
    Show answer

    Answer: A. ##### is not an error value; it appears when a number or date is wider than the column. Widening the column shows the value. Division by zero shows #DIV/0! and a deleted reference shows #REF!.

  4. 4. A cell holds 0.4. You apply the Percentage format with no decimal places. What does it show?

    1. 0.4%
    2. 4%
    3. 400%
    4. 40%
    Show answer

    Answer: D. The percentage format multiplies the stored value by 100 for display: 0.4 × 100 = 40, shown as 40%. The stored value stays 0.4.

  5. 5. You want a cell to show the roll number 007 exactly, keeping both zeros. What should you type?

    1. 007
    2. '007
    3. =007
    4. #007
    Show answer

    Answer: B. Typing 007 stores the number 7 and drops the zeros, and =007 is a formula that also gives 7. An apostrophe in front tells the program to store the entry as text, so it shows 007.

Privacy · Terms · Refunds · Delivery