Chapter 2. Adding Information to Worksheets
Now that you've created a basic worksheet, and you're acquainted with Excel and its sometimes-quirky interface, it's time to get down and dirty adding data. Whether you want to plan your household budget, build a sales invoice, or graph your soaring (or plunging) net worth, you first need to understand how Excel interprets the information you put in your worksheet.
Depending on what kind of data you type into a
2.1. Adding Different Types of Data
One of Excel's most important features is its ability to distinguish between different types of information. A typical worksheet contains both text and numbers. There isn't a lot that you can do in Excel with ordinary text (other than alphabetize a list, perform a simple spell check, and apply some basic formatting). On the other hand, Excel gives you a wide range of options for numeric data. For example, you can string your
Most of the time, when you enter information in Excel, you don't explicitly
Ordinary text . This data type includes column headings, descriptions, and any content that Excel can't identify as one of the other data types.
Numbers . This data type includes prices, integers, fractions, percentages, and every other type of numeric data. Numbers are the basic ingredient of most Excel worksheets.
Dates and times . This data type includes dates (like Oct 3, 2004), times (like 4:30 PM), and combined date and time information (like Oct 3, 2004, 4:30 PM). You can enter date and time information in a variety of formats.
True or false values . This data type (known in geekdom as a Boolean value) can contain one of two things: TRUE or FALSE (displayed in all capitals). You don't need Boolean data types in most worksheets, but they're useful in worksheets that include Visual Basic macro code (see Chapter 25) or that use complex formulas that evaluate conditions (see Chapter 12).
One useful way to tell how Excel is interpreting your data is to look at
As Figure 2-1 shows, Excel can display numbers and dates in several different ways. For example, some of the numbers include decimal places, one uses a comma, and one has a currency symbol. Similarly, one of the time values uses the 12-hour clock while another uses the 24-
These differences may seem like a spreadsheet free-for-all, but don't despairyou can easily set the formatting of numbers and dates. (In fact, that's the subject of Chapter 4.) At this point, all you need to know is that the values Excel stores in each cell don't need to match exactly the values that it displays in each cell. For example, the number 4300 could be formatted as plain old 4300 or as the dollar amount $4,300. Excel lets you format your numbers so you have exactly the representation you want. At the same time, Excel treats all numbers equivalently, no matter how they're formatted, which lets you combine them together in calculations. Figure 2-2 shows you how to find the underlying stored value of a cell.
By looking at cell alignment, you can easily tell how Excel is interpreting your data. That's helpful. But what happens when Excel's interpretation is at odds with your wishes? For example, what if you type in something you consider a number but Excel freakishly treats it as text , or vice versa? The first step to solving this problem is grasping the logic behind Excel's automatic decision-making process.
If your cell meets any of the following criteria, Excel automatically treats the content as ordinary text :
It contains any letters . Thus, C123 is text, not a number.
It contains any punctuation that Excel can't interpret
Occasionally, Excel reads your data the wrong way. For example, you might have a valuelike a social security number or a credit card numberthat's made up entirely of numeric
In all these cases, the solution is simple. Before you type the cell value, start by typing an apostrophe ('). The apostrophe tells Excel to treat the cell content as text. Figure 2-3 shows you how it works.
When you precede a numeric value with an apostrophe, Excel checks out the cell to see what's going on. If Excel determines that it can represent the content as a number, it places a green triangle in the top left corner of the cell and gives you a few options for dealing with the cell, as shown in Figure 2-4.
Excel automatically interprets any cell that only contains numeric characters as a number. In addition, you can add the following non-numeric characters to a number without
One decimal point (but not two). For example, 42.1 is a number, but 42.1.1 is text.
One or more commas, provided you use them to separate groups of three numbers (like thousands, millions, and so on). Thus 1,200,200 is a valid number, but 1,200,20 is text.
A currency sign ($ for U.S. dollars), provided it is at the beginning of the number.
A percent symbol at the beginning or end of the number (but not both).
A plus (+) or minus (-) sign before the number. You can also create a negative number by putting it in parentheses. In other words, entering (33) is the same as entering -33.
An equal sign at the start of the cell.
The most important thing to understand about entering numbers is that when you choose to add other details like commas or the dollar sign, you're actually doing two things at once: you're entering a value for the cell and you're setting the format for the cell, which affects how Excel displays the cell. Chapter 4 provides more information about number styles and shows how you can completely control cell formatting.
When typing in a date, you have a choice of formats. You can type in a full date (like
July 4, 2004
) or you can type in an abbreviated date using dashes or
Because you can represent dates a few different ways, working with them can be tricky, and you're likely to encounter some unexpected behavior from Excel. Here are some tips for using dates, trouble-free:
Instead of using a number for the month, you can use a three-letter month abbreviation, but you must put the month in the middle. In other words, you can use 7/4/2004 and 4/Jul/2004 interchangeably.
When you use a two-digit year as part of a date, Excel
If you don't type in any year at all, Excel automatically assumes you mean the current year
. For example, if you enter 7/4, Excel
Excel understands and displays dates differently depending on the regional settings on your computer . Windows has a setting that determines how your computer interprets dates (see the box on Sidebar 2.1). On the U.S. system, Month-Day-Year is the standard progression. But on the UK system, Day-Month-Year is the deal. For example, in the U.S., either 11-7-04 or 11/7/04 is shorthand for November 7, 2004. In the UK, the same notations refer to July 11, 2004.
Thus, if your computer has U.S. regional settings turned on, and you type in 11/7/04 , Excel understands it as November 7, 2004, and the Formula bar displays 11/7/04.
Typing in times is more straightforward than typing in dates. You simply use numbers, separated by a
Finally, you can create
Behind the scenes, Excel stores dates as numbers. It considers the date January 1, 1900 to be day 1. January 2, 1900 is day 2, and so on, up through the year 9999. This system is quite nifty, because if you use Excel to subtract one date from another, you actually end up calculating the difference in days, which is exactly what you want. On the other hand, it means you can't enter a date in Excel that's earlier than January 1, 1900 (if you do, Excel treats your date like text).
UP TO SPEED
Windows has regional settings for your computer, which affect the way Microsoft programs understand things like dates and currency. You can change the settings, and they don't have to
To make a switch, go to the Start menu in Windows and choose Settings
Control Panel, then double-click Regional and Language Options, which
You can fine-tune the settings in your region, too. This makes sense only if you have particular preferences about how dates should be formatted that don't match the standard options. Click the Customize button next to the region box to bring up a new dialog box, and then click the Date tab (shown here).
Use the pull-down
No matter what the regional settings are, you can always use the international date standard, which is Year/Month/Day, though you must supply a four-digit year (as in 2004/7/4). If you use a two-digit year, Excel will assume you are trying to use the Month-Day-Year or Day-Month-Year pattern.
Similarly, Excel stores times as