3. Faster formatting of data
Changing the formatting of a number changes the way it appears but doesn't alter its value.
When Excel makes calculations, it uses the data you originally typed into a cell – and it's this that continues to display in the Formula Bar.
Graphic
$37,260 in E10 appears as 37260 in the Formula Bar.
To apply formatting to numbers, you can click the Number launcher on the Home tab to open the Format Cells dialog box.
Graphic
The Format Cells dialog box opens on the Number tabbed page.
You can select format options from this comprehensive list of number formats and customize the format you apply to a selected cell or cell range.
Graphic
The Category list includes General, Number, Accounting, Date, Time, and Scientific.
By default, numbers you enter in an Excel worksheet have the General format. In this format, the values are recognized as numeric and Excel automatically aligns them to the right of cells.
The General format also displays numbers too long to fit in cells or that are 12 or more digits in exponential notation.
Graphic
With the General category selected, what appears as a currency in the worksheet – $159,780 – appears without formatting in the Sample section as 159780.
The Number format includes decimal places after the number. The default is two decimal places, although you can customize this. You can also choose to add thousands separators.
Graphic
With the Number category selected, the value selected in the worksheet – $159,780 – appears in the Sample section as 159,780. The Decimal places spin box is set to 0. The Use 1000 Separator (,) checkbox is selected.
157
You can apply the Currency format to numbers that represent monetary values. This
automatically adds a currency symbol – a dollar sign by default, although you can change this as applicable. Like the Number format, it also adds two decimal points to numbers – although you can customize this. The Currency format also allows you to specify how negative numbers display.
Graphic
With the Currency category selected, the value selected in the worksheet – $159,780 – appears in the Sample section as $159,780. The Decimal places spin box is set to 0. The Symbol drop-down list box specifies the currency as $ English (U.S.).
Like the Currency format, the Accounting format interprets numbers as currency, adds two decimal points by default, and adds thousands separators. You can also change the currency and the number of decimal points if you want to. The main difference between the two formats is that the Accounting format aligns the decimal points in a column, for easier comparison of values.
Graphic
With the Accounting category selected, the value selected in the worksheet – $159,780 – appears in the Sample section of the Currency category as $159,780. The Decimal places spin box is set to 0.
The Symbol drop-down list box specifies the currency as $ English (U.S.).
Question
Match each description to its corresponding number format. A description may match to more than one format.
Options:
A. Adds decimal places to the number B. Adds a currency symbol
C. Aligns decimal points in a column
D. Aligns numbers to the right, but applies no other formatting
Targets:
1. Number format 2. Currency format 3. Accounting format 4. General format
Answer
158
The Number format adds two decimal places after the number by default, but you can customize this.
The Currency format displays numbers as currency values, with currency symbols, thousands separators, and – by default – two decimal places.
The Accounting format displays numbers as currency values, with currency symbols, thousands separators, two decimal places, and aligns decimal points.
The General format aligns numbers to the right of each cell and is the default format for numeric entries.
Correct answer(s):
Target 1 = Option A
Target 2 = Option A, Option B
Target 3 = Option A, Option B, Option C
Target 4 = Option D
The Date format interprets numbers as dates. You can select a range of different date formats, according to local conventions. These become available depending on the location you specify.
You can specify a short date, or include the month, or day of the week.
Graphic
With the Date category selected, the value selected in the worksheet – $159,780 – appears in the Sample section as 6/17/2337. The Type list box lists different date formats. The format *3/14/2001 is selected. The Locale (location) drop-down list is set to English (U.S.).
The Time format interprets numbers as times of day. Like the Date format, the options available will change according to the location you specify. You can choose to apply 24-hour format, or include an AM or PM suffix.
Graphic
With the Time category selected, the value selected in the worksheet – $159,780 – appears in the Sample section as 12:00:00 AM. The Type list box contains different time formats. The time
*1:30:55 PM is selected. The Locale (location) drop-down list is set to English (U.S.).
The Percentage format interprets numbers as percentages to a number of decimal places. The default is two decimal places, but you can customize this.
159 Graphic
With the Percentage category selected, the value selected in the worksheet – $159,780 – appears in the Sample section as 15978000%. The Decimal places spin box is set to 0.
The Fraction format interprets numbers as fractions of a whole. You can select the denominator of the fraction – for example, the number 8 could be interpreted as eight hundredths or eight tenths, depending on the fraction type you've specified.
Graphic
With the Fraction category selected, the value selected in the worksheet – $159,780 – appears in the Sample section as 159780. The Type list box has several formats, such as Up to one digit (1/4), Up to two digits (21/25), and As sixteenths (8/16)..
Question
Match each description to its corresponding number format.
Options:
A. Separates numbers with colons to indicate a particular point in a day B. Divides numbers into day, month, and year
C. Indicates proportion and includes a specified number of decimal places D. Interprets numbers as part of a whole
Targets:
1. Time 2. Date 3. Percentage 4. Fraction
Answer
The Time format displays numbers as times, divided by colons into hours, minutes, and seconds.
The Date format displays numbers as dates, either using a combination of words and numbers or separating the numbers representing days, months, and years with forward slashes.
The Percentage format adds a percentage symbol after numbers. You can specify the number of decimal places that must display.
160
The Fraction format interprets numbers as part of a whole. Numbers are positioned in the middle of cells.
Correct answer(s):
Target 1 = Option A
Target 2 = Option B
Target 3 = Option C
Target 4 = Option D
The Scientific format is useful for large numbers because it displays numbers in exponential notation. In this notation, a number is represented by the first part of the number, rounded off, with the formula E+n added. The number before E is multiplied by 10 to the nth degree to reach the original number. You can choose the number of decimal places to apply.
Graphic
With the Scientific category selected, the value selected in the worksheet – $159,780 – appears in the Sample section as 2.E+05. The Decimal places spin box is set to 0.
The Text format interprets whatever data is entered into the cell as text. All numbers, text, or combinations of numbers and text appear exactly as they've been entered, and are aligned to the left of the cell.
Graphic
With the Text category selected, the value selected in the worksheet – $159,780 – appears in the Sample section as 159780.
The Special format can be used to apply additional, unique number formats to the data. The options available change according to the location specified. For example, you could format a number as a social security number.
Graphic
With the Special category selected, the value selected in the worksheet – $159,780 – appears in the Sample section of the as 159780. The Type list contains the options Zip Code, Zip Code +4, Phone Number, and Social Security Number. The Locale (location) drop-down is set to English (U.S.).
The Custom format option lets you to customize a number format yourself by modifying existing number formats.
161 Graphic
With the Custom category selected, the value selected in the worksheet – $159,780 – appears in the Sample section as 159780. The Type list box contains several number formats, for example, mm:ss and dd/mm/yy.
Try It
Now you try using the Format Cells dialog box to format the numbers in cells D44 to D48 as currency values with no decimal places. The cells have already been selected.
The Wireless Tech Conference worksheet outlines the estimated and actual cost of items for a conference. A list of numbers in the Actual column, D44 to D48, have been selected.
To complete the task
1. Click the Number launcher
2. Select the Currency category of options
3. Click the down arrow in the Decimal places spin box twice 4. Click OK to apply the changes
You've formatted the cells D44 to D48 so the numbers are in the Currency format with no decimal points.
Let's say you also want to format cells so they show the time in 24-hour format rather than with the AM or PM suffix.
Graphic
A timesheet displays in Excel. The cell range F9:J9 – which displays the start time in the morning – is selected. F9 contains the time 8:55:00 AM.
To do so, you access the Format Cells dialog box, select Time, and then choose the format you want from the Type list.
Graphic
13:30 is selected from the Type list. The Sample section displays 8:55.
The numbers in the cell then display in this customized format, although the format still displays as "Time" on the Number group.
Graphic
162
The cell F9 displays 8:55 whereas the Formula Bar displays 8:55:00 AM.
Question
Match each description to the corresponding number format.
Options:
A. Displays numbers in exponential notation B. Aligns cell entries to the left of the cell
C. Applies unique formats, depending on your location
D. Enables you to modify pre-existing formats to suit your needs
Targets:
1. Scientific 2. Text 3. Special 4. Custom
Answer
The Scientific format displays numbers in exponential notation.
The Text format treats numbers the same way as text. Numbers are displayed in the same way as they're entered into the cell.
The Special format includes a range of location-specific formats.
The Custom format lets you modify pre-existing formats or build a new format.
Correct answer(s):
Target 1 = Option A
Target 2 = Option B
Target 3 = Option C
Target 4 = Option D