You can change the appearance of the data in the cells of a worksheet without changing the actual value in the cell. You can apply numeric formats to numbers to better reflect the type of information they represent—dollars, dates, decimals, and so on. For example, you can format a number to display up to 15 decimal places or none at all. If you don’t see the number format you need, you can create a custom one.
Select a cell or range that contains the number(s) you want to format.
Click the Formatting Palette tab on the Toolbox.
Click the Number panel to expand it.
Click the Number Format list arrow, and then click any of the following formats:
-
General. No specific format.
-
Number. 0.75
-
Currency. $0.75
-
Accounting. $ 0.75
-
Date. 3/17/2008, Wednesday, March 17, 2008
-
Time. 6:00:00 PM
-
Percentage. 75.00%
-
Fraction. 3/4
-
Scientific. 7.50E-01
-
Text. Numbers treated as text.
-
Special. Zip code, phone number, or social security number.
-
Custom. Use a number format code.
To fine-tune the format, click any of the following format buttons:
-
Increase Decimal.
-
Decrease Decimal.
You can apply multiple attributes to the range.
Select a cell or range that contains the number(s) you want to format.
Click the Format menu, and then click Cell.
Click the Number tab.
Click to select a category.
Select the options you want to apply.
To create a custom format, click Custom, type the number format code, and then use one of the existing codes as a starting point.
Preview your selections in the Sample box.
Click OK.
You can make your worksheets more powerful by setting up conditional formatting. Conditional formatting lets the value of a cell determine its formatting. For example, you might want this year’s sales total to be displayed in red and italics if it’s less than last year’s total, but in green and bold if it’s more. The formatting is applied to the cell values only if the values meet the a condition that you specify. Otherwise, no conditional formatting is applied to the cell values.
Select a cell or range you want to conditionally format.
Click the Format menu, and then click Conditional Formatting.
Select the operator and values you want for condition 1.
Click Format, select the attributes you want applied, and then click OK.
Click Add to include additional conditions, and then repeat steps 3 and 4.
Click OK.
The length of a label might not always fit within the width you’ve chosen for a column. If the cell to the right is empty, text spills over into it, but if that cell contains data, the text will be truncated (that is, cut off). A cell can be formatted so its text automatically wraps to multiple lines; that way, you don’t have to widen the column to achieve an attractive effect. For example, you might want the label Interior Vanity Strips to fit in a column that is only as wide as Interior. Cell contents can also be modified to fit within the available space or can be combined with the contents of other cells.
Select a cell or range whose text flow you want to change.
Click the Format menu, and then click Cells.
Click the Alignment tab.
Click to select one or more Text Control check boxes.
-
Wrap Text moves the text to multiple lines within a cell.
-
Shrink To Fit reduces character size to fit within a cell.
-
Merge Cells combines selected cells into a single cell.
Click OK.
When you enter data in a cell, Excel aligns labels on the left edge of the cell and aligns values and formulas on the right edge of the cell. Horizontal alignment is the way in which Excel aligns the contents of a cell relative to the left or right edge of the cell; vertical alignment is the way in which Excel aligns cell contents relative to the top and bottom of the cell. Excel also provides an option for changing the flow and angle of characters within a cell. The orientation of the contents of a cell is expressed in degrees. The default orientation is 0 degrees, in which characters are aligned horizontally within a cell.
Select a cell or range containing the data to be realigned.
Click the Format menu, and then click Cells.
Click the Alignment tab.
Click the Horizontal drop-down or the Vertical drop-down, and then select an alignment.
Select an orientation. Click a point on the map, or click the Degrees up or down arrow.
If you want, select one or more of the Text Control check boxes.
Click OK.
Select a cell or range containing the data to be realigned.
Click the Formatting Palette tab on the Toolbox.
Click the Alignment and Spacing panel to expand it.
Use any of the following alignment buttons:
-
To align cell contents horizontally, click the Align Left, Center, Align Right, or Justify button.
-
To align cell contents vertically, click the Top, Center, Bottom, or Justify button.
-
These buttons are also available on the Formatting toolbar.
You can change the color of the numbers and text on a worksheet. Strategic use of font color can be an effective way of tying similar values together. For instance, on a sales worksheet you might want to display sales in green and returns in red. Or, you may want to highlight column or row headers with colored labels. Either way, using color to highlight numbers and texts makes deciphering your worksheet data easier.
You can fill the background of a cell with a color and a pattern to make its data stand out. Fill colors and patterns can also lend consistency to related information on a worksheet. On a sales worksheet, for example, formatting all fourth-quarter sales figures with a blue background and all second-quarter sales with a yellow background would make each group of figures easy to identify. You can use fill colors and patterns in conjunction with text attributes, fonts, and font colors to further enhance the appearance of your worksheet.
Select a cell or range to which you want to apply colors and patterns.
Click the Formatting Palette tab on the Toolbox.
Click the Borders and Shading panel to expand it.
To add a color to a cell, click the Fill Color button arrow, and then select a color.
-
The Fill Color button is also available on the Formatting toolbar.
To add a pattern to the cell, click the Pattern box, and then select a pattern and color in the palette.
The light gray grid that appears on the worksheet helps your eyes move from cell to cell. Although you can print these gridlines, sometimes a different grid pattern better emphasizes your data. For example, you might put a decorative line border around the title or a double-line bottom border below cells with totals. You can add borders of varying colors and widths to any or all sides of a single cell or range. If you prefer, you can draw a border outline or grid directly on a worksheet.
Select a cell or range to which you want to apply a border.
Click the Formatting Palette tab on the Toolbox.
Click the Borders and Shading panel to expand it.
Click the Border Type button, and then select a border.
Click the Border Color button, and then select a color.
Click the Border Style button, and then select a border style.
-
To remove cell borders, click No Border.
-
These buttons are also available on the Formatting toolbar.
Formatting worksheet data can be a lot of fun but also very intensive. To make formatting data more efficient, Excel includes 18 AutoFormats. An AutoFormat includes a combination of fill colors and patterns, numeric formats, font attributes, borders, and font colors that are professionally designed to enhance your worksheets. If you don’t select any cells before choosing the AutoFormat command, Excel will “guess” which data it should format. You can control individual elements in an AutoFormat so that not all are applied to the current worksheet. These changes are temporary; you can’t permanently alter an AutoFormat.
Select a cell or range to which you want to apply an AutoFormat, or skip this step if you want Excel to “guess” which cells to format.
Click the Format menu, and then click AutoFormat.
Click an AutoFormat in the list.
Click Options.
Select one or more Formats To Apply check boxes to turn a feature on or off.
Click OK.
Did You Know?
You can let Excel choose the range to format. If you don’t select the cells you want to apply the AutoFormat to, Excel will guess which cells you want formatted.
You can copy cell formats with Format Painter. Select the cell or range whose formatting you want to copy, double-click the Format Painter button on the Standard toolbar, select the cells you want to format, and then click the Format Painter button.
A style is a defined collection of formats—font, font size, attributes, numeric formats, and so on—that you can store as a set and later apply to other cells. For example if you always want subtotals to display in blue 14-point Times New Roman, bold, italic, with two decimal places and commas, you can create a style that includes all these formats. If you plan to enter repetitive information, such as a list of dollar amounts in a row or column, it’s often easier to apply the desired style to the range before you enter the data. That way you can simple enter each number, and Excel formats it as soon as you press Return. You can also copy styles from one workbook to another. Once you create a style, it is available to you in every workbook. Any style—whether it was supplied by Excel or created by you or someone else—can be modified.
Select a cell or range that you want to create a style.
Click the Format menu, and then click Style.
Type the name of the new style.
Clear the check boxes with the options you do not want.
Click Modify.
Click any of the formatting tabs, and then make additional formatting changes to the style.
Click OK.
Click OK.
Did You Know?
You can modify a style. Click the Format menu, click Style, click the style you want to modify, click Modify, make the changes you want, and then click OK, and then click OK again.
You can delete a style. Click the Format menu, click Style, select the style you want to delete, click Delete, and then click OK.
Depending on your screen size, the sheet tabs at the bottom of your workbook can be hard to view. You can add color to the sheet tabs to make them more distinguishable. If you want to add artistic style to your workbook or you are creating a Web page from your workbook, you can add a background picture. When you add a background to a worksheet, the background does not print, and it’s not included when you save an individual worksheet as a Web page. You need to publish the entire workbook as a Web page to include the background.
Click the sheet tab you want to add a background to.
Click the Format menu, point to Sheet, and then click Background.
Select the folder with the graphic file you want to use.
Select the graphic you want.
Click Insert.
To remove the background, click the Format menu, point to Sheet, and then click Delete Background.
If you want to print a worksheet that is larger than one page, Excel divides it into pages by inserting automatic page breaks. These page breaks are based on paper size, margin settings, and scaling options you set. You can change which rows or columns are printed on the page by inserting horizontal or vertical page breaks. In page break preview, you can view the page breaks and move them by dragging them to a different location on the worksheet.
To insert a horizontal page break, click the row where you want to insert a page break.
To insert a vertical page break, click the column where you want to insert a page break.
Click the Insert menu, and then click Page Break.
Did You Know?
You can remove a page break. Select the column or row next to the page break, click the Insert menu, and then click Remove Page Break. To remove all manual page breaks, click the diamond at the top left corner of the sheet, click the Insert menu, and then click Reset All Page Breaks.
You can move a page break. Click the View menu, click Normal, point to the page break, and then drag the page break line to a new location. When you move an automatic page break, it changes to a manual page break, which are not adjusted automatically by Excel.
You can set up the worksheet page to print just the way you want. With the Page Setup dialog box, you can choose the page orientation, which determines whether Excel prints the worksheet data portrait (vertically) or landscape (horizontally). You can also adjust the print scaling (to reduce or enlarge the size of printed characters), change the paper size (to match the size of paper in your printer), and resize or realign the left, right, top, and bottom margins (the blank areas along each edge of the paper). You can use the mouse pointer to adjust margins visually for the entire document in Page Layout view, or you can use the Page Setup dialog box to set precise measurements for an entire document or a specific section. Changes made in the Page Setup dialog box are not reflected in the worksheet window. You can see them only when you preview or print the worksheet.
Click the Page Layout View button.
Click the View menu, and then click Ruler to display it.
Position the cursor over the left, right, top, or bottom edge of the ruler until the cursor changes to a double arrow.
A ScreenTip appears indicating the margin name and current position.
Drag to change the margin.
To exit Page Layout view, click the Normal button.
Adding a header or footer to a workbook is a convenient way to make your printout easier for readers to follow. Using the Page Setup command, you can add information such as page numbers, the worksheet title, or the current date at the top and bottom of each page or section of a worksheet or workbook. Using the Custom Header and Custom Footer buttons, you can include information such as your computer system’s date and time, the name of the workbook and sheet, a graphic, or other custom information.
Click the File menu, and then click Page Setup.
Click the Header/Footer tab.
If the Header box doesn’t contain the information you want, click Custom Header.
Type the information in the Left, Center, or Right Section text boxes, or click a button to insert built-in header information. If you don’t want a header to appear at all, delete the text and codes in the text boxes.
Select the text you want to format, click the Font button, make font changes, and then click OK. Excel will use the default font, Arial, unless you change it.
Click OK.
If the Footer box doesn’t contain the information that you want, click Custom Footer.
Type information in the Left, Center, or Right Section text boxes, or click a button to insert the built-in footer information.
Click OK.
Click OK.
When you’re ready to print your worksheet, you can choose several printing options. The print area is the section of your worksheet that Excel prints. You can set the print area when you customize worksheet printing or any time when you are working on a worksheet. For example, you might want to print a different range in a worksheet for different people. In order to use headers and footers, you must first establish, or set, the print area. You can design a specific single cells or a contiguous or non-contiguous range.
Select the range of cells you want to print.
Click the File menu, and then point to Print Area.
Click Set Print Area or Clear Print Area.
Did You Know?
You can avoid repeating rows and columns. For best results when printing a multipage worksheet, you’ll want to coordinate the print area with specified print titles so that columns or rows are not repeated on a single page.
You can add to a print area. Click the cell where you want to extend the print area, click the File menu, point to Print Area, and then click Add to Print Area.
At some point you’ll want to print your worksheet so you can distribute it to others or use it for other purposes. You can print all or part of any worksheet, and you can control the appearance of many features, such as whether gridlines are displayed, whether column letters and row numbers are displayed, or whether to include print titles, columns and rows that are repeated on each page. If you have already set a print area, it will appear in the Print Area box on the Sheet tab of the Page Setup dialog box. You don’t need to re-select it.
Click the File menu, and then click Page Setup.
Click the Sheet tab.
Enter the number of the row or the letter of the column that contains the titles. Or click the Collapse Dialog button, select the row or column with the mouse, and then click the Expand Dialog button to restore the dialog box.
Click OK.
