Editing and Formatting
Merging Cells
You now have the ability to merge several cells into one cell. Although merging cells may appear similar to another feature in Excel for centering data across several columns, merging is in fact quite different. Figure 3.6 illustrates both merged cells 1 and centered data 2.
){kind=link}
Merging physically combines several cells into one cell in the worksheet, similar to merging cells in a Word table. Centering data across several columns simply changes the display of the data; the cells remain unchanged.
When cells are merged, the uppermost-left cell reference becomes the reference for the merged cells. In the first example of merged cells in Figure 3.6, the cell reference for the merged cells is B2 (C2 and D2 no longer exist).
If the cells you select to merge contain data, only the data in the upper-left cell of the selected group of cells is retained when the cells are merged. Excel warns you that all other data will be lost if you proceed to merge the cells. In Figure 3.6, the cells B12 through B14 3 have been selected. The warning message 4 displays when you attempt to merge the cells. If the cells are merged in this example, only 2000 is retained, Sales and Subtotal are lost.
The Center Across Columns button available on the Formatting toolbar in Excel 5.0 and Excel 95 has become Merge and Center 5 in Excel 2000. When you use this button, the selected cells are merged and the data is centered in the merged cells.
TIP
If you just want to merge the cells, use the Merge Cells button 6 on the Alignment tab in the Format Cells dialog box (see Figure 3.7).
){kind=link}
If you just want to center the data without merging the cells, use the Center Across Selection option in the Horizontal alignment drop-down list 7 in the Format Cells dialog box to center data across several columns.
Use the left, center, or right alignment buttons on the Formatting toolbar to change the horizontal alignment of data in the merged cells.
CAUTION
Some commands, such as inserting rows and columns, cannot be performed on cells that have been merged. The cells must be unmerged (split) before you can use these commands. To split cells, select the cell and clear the Merge Cells check box on the Alignment tab of the Format Cells dialog box (see Figure 3.7).
FIG. 3.6 Examples of merged and centered cells.
FIG. 3.7 The Alignment tab of the Format Cells dialog box.
Indenting and Rotating Data
To enhance the appearance of the data in your worksheets, Excel includes two new formatting options: indenting and rotating.
Data can be indented inside a cell, up to 15 levels. You can rotate data in a cell from 90 degrees to -90 degrees, at 1-degree increments, or display the data vertically.
To indent or rotate data, choose Format, Cells. From the Alignment tab of the Format Cells dialog box (see Figure 3.8), select the desired levels of indention 1 or degrees of rotation 2. Instead of rotating the data, you can also change its orientation to vertical 3.
){kind=link}
You can also indent data with the Decrease Indent button 4 and Increase Indent button 5 on the Formatting toolbar.
Figure 3.9 shows an example of using rotating 6 and indenting 7 data to enhance a worksheet.
){kind=link}
FIG. 3.8 Format Cells dialog box.
FIG. 3.9 Examples of indenting and rotating data.
AutoComplete and List AutoFill
The AutoComplete feature was added to Excel in version 95. When the first few characters you type in a cell match an existing entry in the column, Excel displays the remaining characters for you. The AutoComplete feature completes only text entries; it does not work with numbers or dates.
Similarly, when you insert rows (or columns) into a list, the List AutoFill feature extends any formatting and formulas from the previous row (or column) into the new row (or column). The List AutoFill feature works on all types of entries: text, numbers, and dates. Figure 3.10 shows a list before the Rate column is inserted. Figure 3.11 shows the same list after the column is inserted (using the Insert, Columns command). Notice how the formatting (background colors, borders) in row 3 1 has been extended to the new column.
){kind=link}
){kind=link}
Figures 3.12 and 3.13 show a new entry being added to the bottom of a list. When the hours are entered into cell E8 2, the formula multiplying RatexHours is automatically calculated in cell F8 3. The List AutoFill feature extended the date formatting (in column B) as well as the formula (in column F) to the newly entered data.
){kind=link}
){kind=link}
NOTE
This feature works only in lists—that is, worksheets that contain contiguous data (no empty rows or columns).
FIG. 3.10 A list before a column is inserted.
FIG. 3.11 A list after a column is inserted.
FIG. 3.12 Adding new data to the bottom of a list.
FIG. 3.13 List AutoFill copies the formula automatically.
Validating Data Entry
The Data Validation command is a powerful feature that provides you with three options for controlling data entry:
-
You can control the data that can be entered in a cell by displaying a list of choices or placing restrictions (limits) on the entries.
-
You can create input messages that instruct users on the appropriate data to be entered in a cell.
-
You can display an error message if incorrect data is entered in a cell or have Excel draw circles around invalid entries in the worksheet.
These three options can be used separately or in conjunction with one another.
To validate the data that is entered in a cell, choose Data, Validation. There are three tabs in the Data Validation dialog box. In the Settings tab, shown in Figure 3.14, you select the type of data you want to allow in the cell. The validation criteria options 1 displayed on the Settings tab change based on your selection. If an incorrect entry is made (and you have not established an Input Message or Error Alert), a generic message appears indicating an invalid entry and the user can try again.
){kind=link}
NOTE
Specifying the type of data allowed in a cell does not change the cell formatting. You have to apply the appropriate format to the cell. For example, choosing a date restriction does not apply a date format to the cell. Choose Format, Cells to select the desired format.
The Input Message tab of the Data Validation dialog box is used to create a message that instructs users about the type of data that is expected in the cell. Figure 3.15 shows an example of a message entered in the Input Message tab; Figure 3.16 shows the input message 2 as it appears when the cell is selected.
){kind=link}
){kind=link}
The Error Alert tab of the Data Validation dialog box (shown in Figure 3.17) is used to create a message that displays when an invalid entry is typed. Figure 3.18 shows the error alert message that appears when you enter data that does not match the criteria settings you established on the Settings tab.
){kind=link}
){kind=link}
In addition to these options in the Data Validation dialog box, you can have Excel draw circles around invalid entries in the worksheet. You must establish Data Validation criteria (on the Settings tab of the Data Validation dialog box) to use this feature. Choose the Circle Invalid Data button on the Auditing toolbar to draw circles around cells in the worksheet.
FIG. 3.14 Settings tab of the Data Validation dialog box.
FIG. 3.15 Input Message tab of the Data Validation dialog box.
FIG. 3.16 The Input Message displays when you click on the cell.
FIG. 3.17 Error Alert tab of the Data Validation dialog box.
FIG. 3.18 The Error Alert message when you enter invalid data.
Applying Conditional Formats
Conditional formatting is a powerful number-formatting feature that makes it easy to format a cell based on the data in the cell. Conditional formatting is especially useful for highlighting the results of formulas.
With Conditional Formatting, the font style, font color, cell borders, and cell background color or pattern can be changed to accentuate numbers in your spreadsheet. You can specify up to three conditional formats for a cell. When a cell does not meet the specified conditions, it remains unchanged. This gives you four possible outcomes: the cell meets one of the three conditions (three outcomes) or it doesn't meet any of the conditions (the fourth outcome).
Figure 3.19 shows the appearance of a worksheet before and after conditional formatting. In this example:
){kind=link}
-
If the % Change is negative, the cell displays light gray shading. The % Change for Colorado 1 has gray shading.
-
If the % Change is between 5% and 15%, the cell displays in bold font. The % Change for Ohio 2 is in bold.
-
If the % Change is 15% or higher, the cell displays in bold and italic. The % Change for Tennessee 3 is displayed in bold and italic.
There is no specific condition if the % change is between 0% and 5%, therefore the formatting that already exists in the cells for California and Maryland remains.
The Conditional Formatting dialog box, shown in Figure 3.20, lists the formats applied to the % Change cells in Figure 3.21. Choose Format, Conditional Formatting to access this dialog box.
){kind=link}
){kind=link}
NOTE
If conditional formatting is added to a cell that has a formula, when the formula is copied, the conditional formatting is copied also.
FIG. 3.19 Conditional formatting example.
FIG. 3.20 Conditional Formatting dialog box.