Exploring the Types of Data You Can Use

An Excel workbook can hold any number of worksheets, and each worksheet is made up of more than 17 billion cells. A cell can hold any of three basic types of data:

• Numerical values

• Text

• Formulas

A worksheet can also hold charts, diagrams, pictures, buttons, and other objects. These objects aren’t contained in cells. Rather, they reside on the worksheet’s draw layer, which is an invisible layer on top of each worksheet.

About numerical values

Numerical values represent a quantity of some type: sales amounts, number of employees, atomic weights, test scores, and so on. Values also can be dates (such as Feb-26-2007) or times (such as 3:24 a.m.).

Excel’s Numerical Limitations You may be curious about the types of values that Excel can handle. In other words, how large can numbers be? And how accurate are large numbers? Excel’s numbers are precise up to 15 digits. For example, if you enter a large value, such as 123,456,789,123,456,789 (18 digits), Excel actually stores it with only 15 digits of precision. This 18-digit number displays as 123,456,789,123,456,000. This precision may seem quite limiting, but in practice, it rarely causes any problems. One situation in which the 15-digit accuracy can cause a problem is when entering credit-card numbers. Most credit-card numbers are 16 digits long. But Excel can handle only 15 digits, so it will substitute a zero for the last credit-card digit. Even worse, you may not even realize that Excel made the card number invalid. The solution? Enter the credit-card numbers as text. The easiest way is to preformat the cell as Text (choose Home Number and choose Text from the drop-down Number Format list). Or you can precede the credit-card number with an apostrophe. Either method prevents Excel from interpreting the entry as a number. Here are some of Excel’s other numerical limits: Largest positive number: 9.9E+307 Smallest negative number: –9.9E+307 Smallest positive number: 1E–307 Largest negative number: –1E-307 These numbers are expressed in scientific notation. For example, the largest positive number is “9.9 times 10 to the 307th power.” (In other words, 99 followed by 306 zeros.) But keep in mind that this number has only 15 digits of accuracy.

About text entries

Most worksheets also include text in their cells. You can insert text to serve as labels for values, headings for columns, or instructions about the worksheet. Text is often used to clarify what the values in a worksheet mean.

Text that begins with a number is still considered text. For example, if you type 12 Employees into a cell, Excel considers the entry to be text rather than a value. Consequently, you can’t use this cell for numeric calculations. If you need to indicate that the number 12 refers to employees, enter 12 into a cell and type Employees into the cell to the right.

About formulas

Formulas are what make a spreadsheet a spreadsheet. Excel enables you to enter powerful formulas that use the values (or even text) in cells to calculate a result. When you enter a formula into a cell, the formula’s result appears in the cell. If you change any of the values used by a formula, the formula recalculates and shows the new result.

Formulas can be simple mathematical expressions, or they can use some of the powerful functions that are built into Excel. Figure 13-1 shows an Excel worksheet set up to calculate a monthly loan payment. The worksheet contains values, text, and formulas. The cells in column A contain text. Column B contains four values and two formulas. The formulas are in cells B6 and B10. Column D, for reference, shows the actual contents of the cells in column B.