Microsoft Office Tutorials and References
In Depth Information
Adding Custom Borders and a Background Color to a Range
To Format Cells before Entering Values
When to Format
Excel lets you format
(1) before you enter
data; (2) when you
enter data, through the
use of format symbols;
(3) incrementally after
entering sections of data;
and (4) after you enter
all the data. Spreadsheet
format a worksheet
in increments as they
build the worksheet, but
occasions do exist where
it makes sense to format
cells before you enter
While usually you format cells after you enter values in cells, Excel also allows you
to format cells before you enter the values. For example, at the beginning of this project,
bold was applied to all the cells in the blank worksheet. The steps on the next page assign
the Currency style format with a ﬂ oating dollar sign to the ranges C4:C6 and E4:E6
before the values are entered.
1 Select the range C4:C6. While holding down the CTRL key, select the nonadjacent range E4:E6.
2 Right–click one of the selected ranges and then click Format Cells on the shortcut menu.
3 When Excel displays the Format Cells dialog box, click the Number tab. Click Currency in
the Category list and then click the second format, $1,234.10, in the Negative numbers list.
Click the OK button to assign the Currency style format with a ﬂ oating dollar sign to the
ranges C4:C6 and E4:E6.
What will happen when I enter values in those cells?
As you enter numbers into these cells, Excel will display the numbers using the Currency
style format. You also could have selected the range B4:E6 rather than the nonadjacent
ranges and assigned the Currency style format to this range, which includes text. The
Currency style format has no impact on text in a cell.
To Enter the Loan Data
When you format a cell
to display percentages,
Excel assumes that
whatever you enter into
that cell in the future will
be a percentage. Thus,
if you enter the number
.5, Excel translates the
value as 50%. A potential
problem arises, however,
when you start to enter
numbers greater than
or equal to one. For
instance, if you enter
the number 25, do you
mean 25% or 2500%?
If you want Excel to
treat the number 25 as
25% instead of 2500%
and Excel interprets the
number 25 as 2500%,
click the Excel Options
button on the Ofﬁ ce
Button menu. When the
Excel Options dialog box
is displayed, click the
Advanced button and
make sure the ‘Enable
automatic percent entry’
check box is selected.
As shown in the Source of Data section of the Request for New Workbook document
in Figure 4–2 on page EX 268, ﬁ ve items make up the loan data in the worksheet: the item
to be purchased, the price of the item, the down payment, the interest rate, and the number
of years until the loan is paid back (also called the term of the loan). These items are entered
into cells C3 through C5 and cells E2 and E3. The steps below describe how to enter the
following loan data: Item — Home; Price — $265,000.00; Down Payment — $30,000.00;
Interest Rate — 5.75%; and Years — 18.
1 Select cell C3. Type Home and then click the Enter box in the formula bar. With cell C3 still
active, click the Align Text Right button on the Ribbon. Select cell C4 and then enter 265000
for the price of the house. Select cell C5 and then enter 30000 for the down payment.
2 Select cell E2. Enter 5.75% for the interest rate. Select cell E3 and then enter 18 for the
number of years to complete the entry of loan data in the worksheet (Figure 4–9 on the
Why are the entered values already formatted?
The values in cells C4 and C5 in Figure 4–9 are formatted using the Currency style with two
decimal places, because this format was assigned to the cells prior to entering the values.
Excel also automatically formats the interest rate in cell E2 to the Percent style with two
decimal places, because the percent sign (%) was appended to 5.75 when it was entered.