- Types of Data You Enter into Excel
- Entering Different Types of Data into a Cell
- Controlling the Next Cell Selection
- Using Copy, Cut, Paste, Paste Special to Enter Data
- Using Text to Columns to Separate Data in a Single Column
- Inserting Symbols and Equations into a Cell
- Using Web Queries to Get Data onto a Sheet
- Using Series to Quickly Fill a Range
- Editing Data
- Working with Tables
- Fixing Numbers Stored as Text
- Spellchecking Your Sheet
- Finding Data on Your Sheet
- Using Data Validation to Limit Data Entry in a Cell
Entering Different Types of Data into a Cell
How you intially type data into a cell affects how Excel interprets it. You can save yourself some time if you let Excel format your data, but it will only do so properly if it can understand what you want. The following sections will help you help Excel understand what you want.
Typing Numbers into a Cell
Numbers are the simplest thing to type into a cell. You select a cell, type in a number, press Enter or Tab, and you’re done.
Typing Text into a Cell
If you simply select a cell and start typing without any forethought, you might get unexpected results. For example, select a cell and type the ZIP Code for Chester, MA, which is 01011, and press Enter or Tab. The beginning 0 disappears and all you see is 1011, as shown in Figure 3.1.
Figure 3.1. Excel tries its best to decipher the data you enter, but sometimes you have to help it out.
The reason this happens is because Excel assumes you are typing in a number, and numbers do not start with zeros. Although ZIP Codes are numeric, they aren’t numbers—that is, you don’t do any math with them. You need to plan ahead—know that you are entering numeric data that should be treated like text, and you need to let Excel know this. To let Excel know that you are entering a number that should be treated like text, type an apostrophe before you type the number.
Select another cell and this time type ‘01011 and then press Enter or Tab. You’ll notice that the beginning zero remains. But you don’t see the starting apostrophe. Also, the ZIP Code is aligned to the left side of the cell instead of the right. This is another default action Excel takes when you enter data—it aligns the values for numbers, dates, and times to the right and text to the left.
When you type alphabetic characters into a cell, you don’t need to worry about a leading apostrophe. Nor do you need to worry about the apostrophe if you are mixing numbers and letters in the cell. But if you do have an apostrophe because it’s habit, that’s OK because Excel ignores leading apostrophes. However, if you need an apostrophe at the beginning of a cell, then enter two apostrophes—only the second one will show.
Typing Dates and Times into a Cell
Dates and times are another category in which it’s important for Excel to know what you are typing in. But in this case, the important thing to remember is to not put an apostrophe or other character before your date or time. Excel is very smart about date and time entry, and if you simply type it in, it does a very good job of deciphering your data.
Excel uses the system-configured date format. For example, in the United States, when entering numeric dates, the month comes first. For example, May 14, 2012 is written as 5/14/12.
If you enter only a month and day, Excel will append the current year. But this also means that if you enter a fraction that could be interpreted as a date, such as 3/4, Excel will convert it to a date, 3/4/12. To enter a fraction, you must format the cell as Fraction before entering it.
Dates must always include a day, month, and year, even if not all three will appear when the cell is formatted (see Chapter 4, “Formatting Sheets and Cells,” for more details on how formatting affects what you see but not actually what’s in the cell).
When entering times, you must enter it using a 24-hour clock, also known as military time or include the a.m. or p.m.
When entering a date, the time is included. But you might not see the part you didn’t type in until the cell is formatted to show it.