Working with Decimal Places

All of Excel's number formats use either two or zero decimal places. The exception is General format, which uses as many places as needed for a value. You can establish a fixed number of decimal places or let Excel automatically round numbers for you. The following sections examine both ways to work with decimal places.

Establishing a Fixed Number of Decimal Places

To establish a fixed number of decimal places, use a numeric format other than General format. Two tools on the Formatting toolbar enable you to change the number of decimal places for numbers. The tools are Increase Decimal (its icon contains .0 and .00 with a left arrow) and Decrease Decimal (its icon contains .0 and .00 with a right arrow). Here's how these tools work:

Click the Increase Decimal button each time you want to move the decimal point one place to the left.
Click the Decrease Decimal button each time you want to move the decimal point one place to the right.

Open you're My Budget worksheet and change the number of decimal places from two to zero for the numeric values. Doing so shows you how simple Excel makes such formatting.

Hiding Zeros

Worksheets are often cluttered with zeros as a result of calculations or information that hasn't been entered. Formulas frequently display a zero when referenced cells are blank. These zeros can make a worksheet look confusing.

The Summary sheet in the My Budget workbook provides a good example of formulas that produce unwanted values of zero. This worksheet shows several columns where data has not been entered. Therefore, the cells with the formulas that total the empty columns produce zeros. In this case, you might want to suppress the zeros.

There are a couple of ways to hide zeros in a worksheet:

Use the Tools, Options command to hide all values of zero in the worksheet. In the Options dialog box, click the View tab. In the Window Options section, click the Zero Values check box to remove the checkmark, which hides all zeros on the worksheet.
Create a custom number format in the Format Cells dialog box to hide zeros in a range of cells.

Working with Dates

Dates and times are actually numeric values that have been formatted to appear as dates and time. You can change the way Excel displays the date and time if you want.

The Date and Time categories are in the Category list on the Number tab in the Format Cells dialog box. You can use the Date format to display date and time serial numbers as date values with slashes or hyphens. The default Date format is the month and day separated by a slash, for example, 7/2 . To display only the time portion, use the Time format.

The Time format lets you display date and time serial numbers as time values with hours, minutes, seconds, AM, or PM. The default Time format is the hour and minutes separated by a colon , for example, 11:00 . You can perform calculations on the time values. To display only the date portion, use the Date format.

Understanding Date and Time Formats

Excel offers a wide variety of date and time formats, which are listed in Table 46.3.

Table 46.3. Excel's Date and Time Formats

Date/Time Format	Sample Date/Time
`m/d`	`7/2`
`m/d/yy`	`7/2/04`
`mm/dd/yy`	`07/02/04`
`d-mmm`	`2-Jul`
`d-mmm-y`	`2-Jul-04`
`dd-mmm-y`	`02-Jul-04`
`mmm-yy`	`Jul-04`
`mmmm-yy`	`July-04`
`mmmm-d,yyyy`	`July 2, 2004`
`m/d/yy h:mm`	`7/2/04 7:30`
`m/d/yy hh:mm`	`7/2/04 19:30`
`hh:mm`	`13:35`
`h:mm AM/PM`	`1:35 PM`
`h:mm:ss AM/PM`	`1:35:50 AM`

Flylib.com

Category list

Similar pages

Working with Decimal Places