1.2 Entering data so it stays usable

What you'll be able to do

Enter text, numbers and dates so that Excel stores each one as what it is. Spot the two traps that quietly damage Indian data. Fill repeating patterns without typing them all out.

Before you start

Open the first-workbook practice file, the same one as the grid topic. Its list mixes names, numbers, phone numbers and dates on purpose.

The work

1. Same either way: type a value and press Enter to confirm it and move down. Press Tab to confirm and move right. Press Esc before confirming to throw the entry away, and Ctrl+Z to undo one you have already confirmed.
2. Same either way: to change a cell without typing all of it again, press F2. The insertion point lands at the end of what is there. To put a line break inside one cell, press Alt+Enter while editing.
3. Same either way: type 42 in one cell and a word in another. The number sits on the right edge of its cell, the text on the left. Before any formatting, that position is Excel telling you what it stored. Read it after every entry that matters.
4. Same either way: a phone number that starts with 0 loses that zero when Excel stores it as a number. Store it as text instead. Select the cells, press Ctrl+1 to open Format Cells, choose Text on the Number tab, press Enter, and then type the numbers.
5. Same either way: underneath, a date is a number, and that is what makes date arithmetic work. Type dates as day-month-year and check that the cell lands on the right edge. A date that stayed on the left was stored as text, and it will not sort or subtract. Today's date is Ctrl+Semicolon, and the current time is Ctrl+Shift+Colon.
6. Same either way: to repeat the value above into the cells below, select from that cell downwards and press Ctrl+D. It copies the top cell's content and format into the rest of the selection.
7. No documented keyboard shortcut for this — use the menu: to continue a series such as Jan, Feb, Mar, type the first value. Select it together with the cells below, then go to Home → Fill → Series and choose AutoFill. With the mouse, the same thing is a drag of the fill handle, the small square at the corner of the selection.
8. Same either way: Flash Fill works from an example you give it. In the column beside full names, type the first person's first name and press Enter. Then press Ctrl+E, and Excel fills the column by the pattern it saw. Check every row it guessed before you trust it.

One rule sits under all of this: one thing per cell. First name in one column, last name in the next, city in its own. Sorting, filtering and every formula you will ever write depend on it.

Try it

Type up the practice file's list. Make the phone column Text before you enter anything. Enter the dates day-month-year and check that each one lands on the right edge. Then split the full names into two columns with Ctrl+E.

Check yourself