Chapter 4. Formatting Worksheet Data

The primary purpose of Excel’s formatting tools is to make your worksheet more readable. For example, you can change the display of the numbers so that they don’t contain decimal places. This allows your audience to see the needed data without being inundated with superfluous numbers.

Excel’s formatting options can be partitioned into two buckets: cell formatting and data formatting. Cell formatting is essentially the act of changing the look and feel of the cells themselves. For instance, you can change the cell background colors, add borders, change alignment, add underline or bold effects to cell contents, and wrap text. Data formatting is the act of formatting the data in the most readable and appropriate way. For example, you can format a number to show as currency complete with the dollar symbol. Or you can format a date so that it shows as Jan-10 instead of 01/01/2010.

In this chapter, you’ll explore the various types of formatting you can apply to both your cells and your data.

Formatting Numbers and Text

Changing the Font and Font Size

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.

Select the cells whose font and font size you want to change, or click the All Cells button (to the left of column A and above row 1) to format all the cells in the worksheet.

Click the Font field down arrow in the Home tab and scroll through the available fonts. When you find the one you want to use, click it to select it.

Click the Font Size field down arrow and scroll through the available sizes (in points). When you find the size you want to use, click it to select it.

The font and font size you selected are applied.

Changing Column Width

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 fixing 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.

Move the mouse pointer over one side of the column header and then click and drag the column edge to the desired width (the column size displays in the Name Box).

To resize multiple columns simultaneously, select the columns that you want to alter.

Click and drag one of the selected columns’ header edges to the desired width and release it.

All the selected columns are resized to the same width.

Changing the Color of the Cell Background and Cell Text

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.

Select the cells whose background color and/or font color you want to change.

To change the color of the text in the selected cells, click the Font Color down arrow on the Home tab and choose a color from the list (here, white).

To change the color of the selected cells’ background, click the Fill Color down arrow and choose a color from the list (here, blue). Excel applies the colors you chose.

Formatting the Display of Numeric Data

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.

After you select the cells you want to format, click the Increase Decimal command on the Home tab twice (once for each decimal place you want displayed).

Click the Comma Style command on the Home tab to add a comma to the numeric data.

Click the Currency Style command on the Home tab to format numbers in the selected cells with a dollar sign ($), commas, a decimal point, and two decimal places.

Using a General Format

When you enter numbers into Excel cells, the General format is the default. 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.

After you select the cells you want to format, click on the down arrow in the Number Format drop-down box (on the Home tab).

Click the General option in the Category list.

Excel changes the format.

Using a Number Format

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).

After you select the cells you want to format, click on the down arrow in the Number Format drop-down found on the Home tab.

Click the Number option in the Category list.

Excel changes the format.

Using a Currency Format

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.

After you select the cells you want to format, click on the down arrow in the Number Format drop-down box on the Home tab.

Click the Currency option in the Category list.

Excel changes the format.

Using a Date Format

When you apply the Date format in Excel, it displays the date and time serial numbers as date values. There are numerous 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.

After you select the cells you want to format, go to the Home tab and click on the down arrow in the Number Format drop-down box.

Click the Short Date option in the Category list.

Excel changes the format.

Using a Text Format

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 that isn’t meant to be calculated or used in a mathematical operation. For instance, you may need to enter a customer number which has leading zeros. Formatting this number as text ensures that the leading zeroes remain visible.

After you select the cells you want to format, click on the down arrow in the Number Format drop-down box (on the Home tab).

Click the Text option in the Category list.

Excel changes the format.

Applying Bold, Italic, and Underline

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.

Select the cells in which you want to apply bold formatting and click the Bold command.

Select the cells in which you want to apply italic formatting and click the Italic command.

Select the cells in which you want to apply underline formatting and click the Underline command.

The bold, italics, and underlining are applied to the selected cells.

Using Merge and Center on Cells

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.

Select the cells you want to merge together, including the cells that don’t contain any data.

Click the Merge and Center command on the Home tab.

The cells in the group header are merged, and the data is centered. Repeat the steps in this task as needed to group additional columns in your worksheet.

Changing Horizontal Data Alignment

Excel provides several ways to format data, and one way is to align it. The most common alignment changes you make will probably be 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.

Select the cells in which you want to align the data to the left and click the Align Left command on the Home tab.

Select the cells in which you want to align the data to the right and click the Align Right command.

Select the cells in which you want to center the data and click the Center command.

The alignments are applied to the selected cells.

Changing Row Height

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.

Move the mouse pointer over the bottom edge of the row header. Click and drag the row to the desired height; the row size is displayed in the Name Box.

To resize multiple rows simultaneously, select the rows you want to alter.

Click and drag one of the selected row’s bottom edges to the desired height, then release it. All the selected rows are resized to the same height.

Changing Vertical Data Alignment

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.

Select the cells in which you want to align the data.

Go to the Home tab and click the Middle Align command.

The data is vertically aligned within the cell.

Changing Cell Orientation

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.

After you select the cell or cells whose orientation you want to change, click the Orientation command in the Alignment group on the Home tab.

Choose Angle Counterclockwise as shown here.

The data reorients within the cell. (You might need to increase or decrease the height and width of the cells.)

Wrapping Data in a Cell

Another way to format data 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.

After you select the cell or cells whose text you want to wrap, click on the Wrap Text command in the Alignment group on the Home tab.

The data in the selected cells is automatically wrapped.

Changing Borders

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.

Select the cells to which you want to add some type of border.

Go to the Home tab and click the down arrow next to the Borders command, then choose an option from the list that appears—for example, All Borders.

The border is applied.

Indenting Entries in a Cell

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.

After you select the cell or range whose data you want to indent, click the Increase Indent command the number of times you want the entries indented.

To decrease the indent, select the cell or range whose indent you want to decrease.

Click the Decrease Indent command to decrease the number of indents.

The indentation is decreased.

Clearing Formatting

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

Select the cells whose formatting you want to clear.

Go to the Home tab and click on the down arrow next to the Clear command, and then choose Clear Formats.

The cell data remains, but all the formatting is gone.

Hiding and Unhiding Rows

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.

Click any cell in the row or the whole row that you want to hide.

To hide the row, click the drop-down arrow next to the Format command on the Home tab. Select Hide & Unhide, Hide Rows. (You can tell that row 5 is hidden by the jump in the row-header numbering.)

To unhide the row, click the drop-down arrow next to the Format command on the Home tab. Select Hide & Unhide, Unhide Rows.

The row is unhidden.

Hiding and Unhiding Columns

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.

Click any cell in the column you want to hide.

To hide the column, click the drop-down arrow next to the Format command on the Home tab. Select Hide & Unhide, Hide Columns. (You can tell that column C is hidden by the jump in the column-header lettering.)

To unhide the column, click the drop-down arrow next to the Format command on the Home tab. Select Hide & Unhide, Unhide Columns.

The column is unhidden.

Hiding and Unhiding a Worksheet

Often, you’ll have worksheets that are meant for your eyes only. An example of this would be a worksheet where you document changes in the workbook over the course of development. This kind of worksheet would be administrative in nature and not meant for your audience. In this scenario, hiding the worksheet would be ideal.

After you select the tab of any sheet you want to hide, right-click on the tab and choose Hide. Excel hides the sheet.

To unhide the sheet, right-click on any tab and choose Unhide.

The Unhide dialog box opens, listing sheets that are hidden in your workbook. Double-click the worksheet name you want to unhide.

Using Format As Table

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 Format As Table 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 manually format data.

Select the cells to which you want to apply Format As Table.

Click on the down arrow next to the Format As Table command (found on the Home tab), then scroll through the available formats and click the one you want to apply to your data.

Click OK on the next pop-up to confirm that the selected range is correct.

The AutoFormat is applied.

Copying Formatting

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 command.

Click the cell with the formatting that you want to copy and apply to other cells.

Click the Format Painter command on the Home tab; the mouse pointer changes to a Format Painter pointer (paintbrush symbol).

Click and drag the mouse pointer to select the cells to which you want to apply the copied formatting.

Release the mouse command. The formatting is applied to the data in the selected cells.

Creating and Applying a Formatting Style

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.

Apply any specific cell formatting that you want the style to use in your worksheet (here, Arial, Bold, 12 pt, White text, Red fill color).

With the cell that contains the desired formatting selected, click on the down arrow next to the Cell Styles command on the Home tab then go to the bottom and choose New Cell Style.

Type a descriptive name for the new style in the Style name field (for example, Sales Header) and click OK.

Select the cell(s) to which you want to apply your newly created style.

With the cells that you want to apply the custom style to selected, click on the down arrow next to the Cell Styles command on the Home tab and then choose the Sales Header style in the Custom style group at the top.

The style is applied to the cells you selected.

Using Conditional Formatting

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 are above $30,000, the data in the cell is bold and red.

Select the cells to which you want to apply conditional formatting, then click on the down arrow next to the Conditional Formatting command on the Home tab and choose New Rule.

In the conditional Formatting dialog box, choose the Format only cells that contain option.

Leave the first drop-down list as Cell Value. Display the second drop-down list to select the type of condition (for example, greater than).

Type the value of the condition (the number that the cells must be “greater than”).

Click the Format command to set the format to use when the condition is met.

Click the options you want to set in the Format Cells dialog box (for example, Pink in the Color field and Bold in the Font style list), and click OK.

Click OK in the Conditional Formatting dialog box.

Excel applies the formatting to any cells that meet the condition you specified.