Chapter 12. Formatting Worksheet Data
Excel makes formatting data easy with many simple, yet powerful, tools. In addition to applying traditional formatting common to all Office applications, you can wrap cell text and merge columns to create headings in Excel.
As expected for a spreadsheet application, Excel offers many options for formatting numbers, including formatting as currency, percentages, fractions, dates, times, and more.
If your worksheet contains a lot of data, Excel makes it easy for you to find a specific cell or even replace cell content automatically. Freezing row numbers and column letters also makes it easier to find what you’re looking for.
Formatting your worksheet data using colored cell styles or as a table are other options that enhance your table’s design and functionality.
Wrapping Text
Wrapping text in cells is useful when the text is too long to fit in one cell, and you don’t want it to overlap to the next cell.
Select the cell or cells that contain the text you want to wrap.
Click the Home tab.
Click the Wrap Text button.
Excel wraps your text in the selected cells.
Tip: Wrap Text Automatically
To wrap worksheet text automatically, select the cells you want to format, click the Format button on the Home tab, and select Format Cells from the menu. On the Alignment tab of the Format Cells dialog box, select the Wrap Text check box. 
Merging and Centering Text
The Merge & Center button makes it easy to create attractive headings that display across multiple columns. For example, if your worksheet contains data in columns A through H, you could merge and center a heading across these columns.
Select the cells to be included in the heading.
Click the Home tab.
Click the Merge & Center button.
Excel merges and centers the selected cells.
Note: Identify Merged Cells
Notice the gridlines have disappeared and the cells appear to be joined together.
Formatting Numbers
By default, values display as general numbers. You can also display values as currency, percentages, fractions, dates, or in many other formats.
Select the cell or cells you want to format.
Click the Home tab.
Click the Accounting Number Format button to format the selected cells as your default currency.
Excel formats your selected cells.
Tip: More Number Formatting
For additional number formatting options, right-click the cells you want to format and select Format Cells from the menu. The Numbers tab on the Format Cells dialog box includes formatting options not available on the Ribbon Home tab.
Caution: What Happened to My Number?
Don’t be alarmed if some of the cells display a series of number signs (######) or in scientific format (1E+08) instead of your values. This is because the column width is too small. You learn how to change column width later in this chapter.
Applying Cell Styles
Cell styles enable you to apply colored formatting instantly to your worksheets. These styles can make your data easier to read.
Select the cells you want to format with a cell style.
Click the Home tab.
Click the Cell Styles button.
Click a cell style in the gallery.
Excel applies the cell style.
Tip: Cell Styles for Headings
One of the most common uses of cell styles is to format titles and headings. Select your heading cells, and then use a cell style to distinguish heading cells from regular data.
Tip: Themed Cell Styles
The Cell Styles gallery also includes themed cell styles in a variety of colors and shadings. You can use these as an alternative to a standard heading or as a way to call attention to other important data.
Formatting as a Table
Formatting worksheet data as a table is another way to distinguish cell data with color. You can also filter and summarize table data using the tools on the Table Tools–Design tab.
Select the cells you want to format as a table.
Click the Home tab.
Click the Format as Table button.
Select a table design from the gallery.
In the Format as Table dialog box, select the My table has headers check box if you want to distinguish table headers, and then click OK.
Excel applies the table design you selected.
The Table Tools–Design tab displays, offering additional table formatting options.
Note: Filter Tables
When you format cells as a table, Excel also enables you to filter your data. See “Filtering Data” later in this chapter for more information.
Tip: Add Table Totals
To add a total row to your table, click the Total Row check box on the Table Tools–Design tab.
Adjusting Column Width
The default width of an Excel column is 8.43 characters, but you can adjust each column from 0 to 255 characters wide.
Position the mouse pointer to the right of the column you want to adjust. The mouse pointer becomes a crosshair.
Press and hold the mouse button, and drag the column line to the right to increase the column width or to the left to decrease it.
Release the mouse button. The column width will be changed.
Tip: Adjust Columns Automatically
To automatically adjust column width to exactly fit the contents of the cell, double-click the mouse after the pointer becomes a crosshair.
Adjusting Row Height
The default height of an Excel row is 15 points, but you can adjust each row from 0 to 409 points.
Position the mouse pointer below the row you want to adjust. The mouse pointer becomes a crosshair.
Press and hold the mouse button and drag the row line down to increase the row height or up to decrease it.
Release the mouse button. The row height will be changed.
Tip: Adjust Rows Automatically
To automatically adjust row height to exactly fit the contents of the cell, double-click the mouse after the pointer becomes a crosshair.
Finding Data
If your worksheet contains a lot of data, it can be hard to find the right cell at times. Fortunately, Excel makes it easy to search your worksheet data.
Click the Home tab.
Click the Find & Select button.
Select Find from the menu.
In the Find and Replace dialog box, enter the data you’re searching for.
Click the Find Next button to find the next instance of this data.
Tip: Replace Data
In addition to finding data, you can also find and replace it. To do so, click the Replace tab in the Find and Replace dialog box.
Freezing Panes
You can freeze columns, rows, or both so that your row and column headings remain in view as you scroll instead of scrolling off the screen with the rest of the worksheet. This is particularly helpful with larger worksheets.
Click the View tab.
Click the Freeze Panes button.
Select Freeze Panes from the menu to freeze both columns and rows.
Tip: Unfreeze Panes
To unfreeze panes, click the Freeze button and select Unfreeze Panes from the menu.
Tip: Customize Freezing
From the Freeze Panes menu, you can optionally choose to freeze only the top row or only the first column.
Click the Home tab.
Click the Select All button to select all your worksheet data.
Click the Sort & Filter button.
Select Custom Sort from the menu.
Note: Column Headers
Column headers are useful for identifying your worksheet data. For example, you can use headers to identify the months of the year, product names, regions, and so forth.
Select the My data has headers check box if your worksheet includes column headers.
Specify the column you want to sort by.
Specify your preferred sort order, such as A to Z or Z to A.
Click OK.
Tip: Sort on Multiple Columns
To sort on more than one column, click the Add Level button in the Sort dialog box, and add an additional column.
Filtering Data
Filtering data is another way to control what displays on your Excel worksheet. By applying a filter, you can hide data temporarily, making it easy to focus on the data you need to see.
Click the Home tab.
Click the Select All button to select all your worksheet data.
Click the Sort & Filter button.
Select Filter from the menu.
Excel displays a down arrow to the right of each column. Click the arrow to the right of the column you want to filter.
Click to remove the check next to any data you don’t want to display.
Tip: Remove a Filter
To remove a filter you applied, click the Sort & Filter button, and select Filter from the menu.
Tip: Sort with a Filter
Applying a filter also makes it easier to sort columns. From the Filter menu, you can sort from smallest to largest or from largest to smallest.