INTRODUCTION

One way to format data in your worksheet is to change the font used to display it. This gives data a different look and feel, which can help differentiate the type of data a cell contains. You can also change the font's size for added emphasis.

TIP

Finding font names

If you know the name of the font you want to apply, select the down arrow next to the Font field and type the first letter of the font name. You will immediately be moved to the portion of the list that starts with the typed letter.

TIP

Formatting options

To format only a portion of a cell's data, select only that portion and then change the font. You can also select a font (or other options) before you begin typing. Then all the data in a cell will be that font.

INTRODUCTION

There might be times when data is too wide to be displayed within a cell, particularly if you just applied formatting to it. Excel provides several alternatives for remedying this problem. You can select columns and specify a width, or force Excel to automatically adjust the width of a cell to exactly fit its contents.

TIP

Specific widths

To resize a column to an exact width, open the Format menu, choose Column, and select Width. Enter the exact width in the dialog box that appears and click OK. To automatically make an entire column the width of the widest cell in that column, open the Format menu, choose Column, and select AutoFit Selection. Or, move the cursor over the right side of the column header and double-click when the cursor changes to a two-headed arrow.

INTRODUCTION

Generally, cells present a white background for displaying data, but you can apply other colors or shading to the background. You can even combine these colors with various patterns for a more attractive effect. In addition, you can change the color of the data contained within your worksheet's cells.

CAUTION

Choosing colors

Be sure a shading or color pattern doesn't interfere with the readability of your data. To improve readability, you might need to make the text bold or select a text color that goes well with your cells' background color. Also, be aware that if you print the worksheet to a noncolor printer, the color you select prints gray—and the darker the gray, the less readable the data. Yellows generally print as a pleasing light gray that doesn't compete with the data.

INTRODUCTION

You can alter the display of different numbers depending on the type of data the cells contain. By formatting numeric data, you can display data in a familiar format to make it easier to read. For example, sales numbers can be displayed in a currency format, and scientific data can displayed with commas and decimals.

TIP

Percent style

If you click the Percent Style button on the Formatting toolbar, your numbers will be converted to a percentage, displayed with a % symbol.

TIP

Handling the #### error

If, after you apply a style to cells, any cells display the error #########, it simply means that the data in the cell exceeds the current cell width. Refer to the task "Changing Column Width" to fix the problem.

INTRODUCTION

When you enter numbers into Excel cells, the default format is called the General format. No specific number format (discussed in the next task) is applied. The General format is used when you are recording counts of items, incrementing numbers, or do not require any particular format. If you have applied another format to your cells, but want to return to this default General format, follow the steps in this task.

TIP

Quickly applying the General format

Another way to quickly format numbers in the General format is to open the Edit menu, choose Clear, and select Formats. Excel clears all the formatting and returns the numbers to the General format (unless you originally entered them in a different format).

INTRODUCTION

When you apply the Number format in Excel, it uses two decimal places by default. You have the option to alter the number of decimal places, use a comma separator, and even determine the way you want negative numbers to appear (for example, with a minus sign, in red, in parentheses, or some combination of the three).

TIP

Number format options

Use the Decimal places field in the Number tab of the Format Cells dialog box to change the number of decimal places used. To specify how negative numbers should appear, choose an option from the Negative numbers list.

TIP

Displaying fractions

Excel offers other number-related formats. For example, the Fraction format enables you to determine the type of fraction you want to appear and the number of digits that are displayed.

INTRODUCTION

When you apply the Currency format in Excel, it uses two decimal places and a dollar sign by default. You have the option to alter the number of decimal places, display a symbol for a different currency, and even determine the way you want negative numbers to appear.

TIP

Currency format options

Use the Decimal places field in the Number tab of the Format Cells dialog box to change the number of decimal places used. To choose a different currency symbol, select it from the Symbol drop-down list.

TIP

Currency-related formats

The Accounting format automatically lines up the currency symbols and decimal points for the cells in a column. The Percentage format multiplies the cell value by 100 and displays the result with a percent symbol.

INTRODUCTION

When you apply the Date format in Excel, it displays the date and time serial numbers as date values. There are numerous different date types you can assign to your dates. For example, you might find it easier to skim through dates as numbers with or without the assigned year visible. Or, perhaps you would rather use the actual name of the month (as opposed to a numeral) for reference.

TIP

Using Time and Custom formats

You can use the Time format if you want to display just the time (not the date) in your spreadsheet. In addition, you can use the Custom format option to create a Date and Time format all your own.

INTRODUCTION

When you type numeric data into a cell, the display defaults to a Number format. When you apply the Text format in Excel, it displays numbers as text regardless of whether the data in the cell is numeric or text-based. This can be convenient when you want to enter a number, but want to make sure Excel warns you with a Smart Tag if you try to use it in any type of formula or function.

TIP

Immediate number text

Another way to immediately make a number a textual cell entry is to type an apostrophe (') before you type the number. This tells Excel that the number is to be treated as text.

INTRODUCTION

You can format the data contained in one or more cells as bold, italic, or underlined (or some combination of the three) to draw attention to it or make it easier to find. Indicating summary values, questionable data, or any other cells is easy with this type of formatting.

TIP

Combination formatting

You can use several formatting techniques in combination, such as applying bold, italic, and underlining all at the same time. Simply select the text you want to format and click each of the buttons on the toolbar.

INTRODUCTION

Using Excel's Merge and Center feature, you can group similar data under one heading. Columns of data usually have column headers, but they can also have group header information representing multiple columns.

TIP

Inserting a row

If no blank row exists above the row of cells you want to Merge and Center (from step 1), click the row you want the new row to be placed above, open the Insert menu and select Row.

TIP

Undoing Merged and Centered Cells

Select the cells to separate, open the Format menu, select Cells, click the Alignment tab, deselect the Merge Cells check box, and click OK.

INTRODUCTION

Excel provides several ways to format data. One way is to align data. The most common alignment changes you make are probably to center data in a cell, align data with a cell's right edge (right-aligned), or align data with a cell's left edge (left-aligned). The default alignment for numbers is right-aligned; the default alignment for text is left-aligned.

TIP

More alignment options

If you want more alignment options than are readily available on the Formatting toolbar, open the Format menu, choose Cells, and click the Alignment tab in the dialog box that appears. In the Horizontal drop-down list, scroll through the additional options available to you.

INTRODUCTION

Depending on the formatting changes you make to a cell, data might not display properly. Increasing the font size or forcing data to wrap within a cell might prevent data from being entirely displayed or cause it to run over into other cells. You can frequently avoid these problems by resizing rows.

TIP

Specific heights

To resize a row to an exact height, open the Format menu, choose Row, and select Height. Enter the exact height in the dialog box that appears and click OK; the exact row height will be set.

TIP

AutoFitting rows

To automatically make an entire row fit the height of the tallest cell, open the Format menu, choose Row, and select AutoFit Selection.

INTRODUCTION

In addition to aligning the data in your cells horizontally, you can align your cell data in a vertical format. Perhaps you want the data in your cells to align to the top of the cell, the bottom of the cell, or the center of the cell, or to justify within the cell. Cell data defaults to the bottom of the cell, but you can change this according to the look you are going for.

TIP

Justifying cell text

If you choose the Justify option in step 3, you must have enough text in the cell to fill from the top to the bottom of the cell—for example, a sentence. The text will automatically wrap to fit the cell.

INTRODUCTION

Excel lets you alter the orientation of cells—that is, the angle at which a cell displays information. The main reason for doing this is to help draw attention to important or special text. This feature can be convenient when you have a lot of columns in a worksheet and you don't want your column headers to take up much horizontal space, or if you simply want the information to stand out.

TIP

Rotating data

Click the half circle in the Orientation section of the Alignment tab to quickly change the angle at which data is rotated within the selected cell(s).

INTRODUCTION

Excel provides several ways to format data. One way is to allow text to wrap in a cell. For example, suppose a heading (row or column, for example) is longer than the width of the cell holding the data. If you are trying to make your worksheet organized and readable, it is a good idea to wrap the text in the heading so it is completely visible in a cell.

TIP

Aligning wrapped text

You might need to alter the column width to have the data wrap at the location you desire. Note, too, that you can align data that has been wrapped, which can give your text a cleaner look. Refer to the tasks "Changing Horizontal Data Alignment" and "Changing Vertical Data Alignment" earlier in this part to learn how to align data in cells.

INTRODUCTION

Each side of a cell is considered a border. These borders provide a visual cue as to where a cell begins and ends. You can customize borders to indicate other beginnings and endings, such as grouping similar data or separating headings from data. For example, a double line is often used to separate a summary value from the data being totaled. Changing the bottom of the border for the last number before the total accomplishes this effect.

TIP

Removing borders

To remove a border, select the bordered cells, click the down arrow next to the Borders button on the Formatting toolbar, and choose the No Border option from the list that appears. Be careful, though; you might eliminate an intended border in a nearby cell. That's why there are all kinds of border options on the drop-down list.

INTRODUCTION

Another alignment option you might want to use is to indent entries within a cell. Doing so can show the organization of entries—for example, subcategories of a budget category.

TIP

Increasing column width

To make the effect of the indent stand out, you might need to increase the width of the indented column. To do so, refer to the task "Changing Column Width" earlier in this part.

INTRODUCTION

Excel allows you to quickly clear all the formatting you have added to cell data, returning numbers and text to their original format.

TIP

More clear options

If you open the Edit menu, choose Clear, and select Contents, the formatting will remain intact, but the text and data (contents) will be deleted (just as if you simply pressed the Delete key on the keyboard). If you open the Edit menu, select Clear, and choose All, all the formatting and contents (and comments) will be removed from the cell.

INTRODUCTION

Hiding rows is a good way to hide calculations that aren't really critical for your audience to see. You also can hide other rows that you want to include in the worksheet, but don't want to display. It's kind of tricky to unhide a row, because you need a way of selecting the hidden row; you'll learn how here.

CAUTION

Caution

Hidden elements don't print when you print the worksheet.

TIP

Dragging to hide rows

You also can hide a row by dragging its bottom border past the top border of the row you want to hide.

INTRODUCTION

Hiding columns is a good way to hide calculations that aren't really critical for your audience to see. You also can hide columns that you want to include in the worksheet, but don't want to display. It's kind of tricky to unhide a column because you need a way of selecting the hidden column; you'll learn how here.

CAUTION

Caution

Hidden elements don't print when you print the worksheet.

TIP

Dragging to hide columns

You also can hide a column by dragging its right border past the left border of the column you want to hide.

INTRODUCTION

Hiding worksheets is a good way to hide data and information that aren't really critical for your audience to see.

CAUTION

Caution

Hidden elements don't print when you print the worksheet.

INTRODUCTION

Using all the formatting capabilities discussed to this point, you could format your worksheets in a very effective and professional manner—but it might take a while to get good at it. In the meantime, you can use Excel's AutoFormat feature, which can format selected cells using predefined formats. This feature is a quick way to format large amounts of data and provides ideas on how to format data manually.

TIP

Modifying AutoFormats

If you find a format in the AutoFormat dialog box that almost—but doesn't quite—meet your requirements, you can apply that format, but then make any necessary changes directly in the worksheet.

INTRODUCTION

If you have taken the time to format a specific cell just so, you might decide you want to apply those same formatting options to other cells. Instead of repeating each step in the format process over and over again, you can simply use the Format Painter button.

TIP

Removing cell formatting

To quickly remove any cell formatting, select the cells you want to return to their default settings; then open the Edit menu, choose Clear, and select Formats.

INTRODUCTION

Instead of assigning your data an existing Excel style (for example, Normal, Currency, Percent, and so forth), you can create your own style and apply it to cells. You begin by applying the specific formatting (for example, font, font style, font size, font color, and cell color) that you want the style to have, and then give the style a specific name.

TIP

Saving the style

The next time you exit Excel, you will be notified that you made a change to your global template and asked if you want to save the changes. If you want to keep the style you just created, click the Yes button; otherwise, click the No button.

TIP

Default styles

Notice that there are also default styles—including Hyperlink, Normal, Percent, and so on—that Office provides for Excel workbooks.

INTRODUCTION

There might be times when you want the formatting of a cell to depend on the value it contains. For this, use conditional formatting, which lets you specify conditions that, when met, cause the cell to be formatted in the manner defined for that condition. If none of the conditions are met, the cell keeps its original formatting. For example, you can set a conditional format such that if sales for a particular month were above $4,000, the data in the cell is bold and red.

TIP

Painting a format onto other cells

You can copy the conditional formatting from one cell to another. To do so, click the cell whose formatting you want to copy. Then click the Format Painter button. Finally, drag over the cells to which you want to copy the formatting.

TIP

When to use conditional formatting

Use conditional formatting to draw attention to values that have different meanings, depending on whether they are positive or negative, such as profit and loss values.