Using the Excel Formatting Toolbar

The Excel Formatting Toolbar contains the most common formatting options.

Select the cell, range or worksheet to fomat.
Click the appropriate buttons on the toolbar.

Here is a graphic of the menu options:

The only button that isn't as straighforward as the rest is the Merge & Center button. It is used, as the name suggests, to merge cells and center the text within the merged cells. For example, if you want a title over the range A1: H1, you would enter the text for the title into cell A1, highlight the range A1:H1 and click the Merge & Center button. All the cells will be merged into one and the text will be centered within. (You can override the centering if you wish by clicking one of the other aligment buttons. The cells will still be merged but the alignment is changed.)

Using the Excel Menu

If you want to use formatting that is not contained in the most common options on the toolbar, you will need to use the menu (either the Formatting menu or the Right-Click short-cut menu). In the Format Cells dialog box you have many options for formatting the selected cells. They are discussed individually below.

Excel Number Formatting

On the number tab of the Format Cells dialog box, you can specify what type of numbers that you want to see.

The Category box gives you options of the different types of number formats. As you choose different categories, the rest of the box will change.

The preview box will give you a preview of all of your choices.

The pane under the preview box will give you options within that category such as the number of decimals. The example below is the Currency category.

Special Number Formats

Excel provides extra formatting for Zip Codes, Phone Numbers and Social Security codes. These are found in the "Special" category.

Excel Custom Number Formats

If the built-in number formats do not meet your needs, you can build your own based on an existing format and using the Custom Formats catetgory.

You create an example in the Type: box. In your example you can specify up to four sections of formats for your custom formatting separated by semi-colons (;).
First section- Positive number formatting - example: #,###.00
Second section- Negative number formatting - example: [Red] (#,###.00)
Third section- Zero formatting - example: 0.00
Forth section- text formatting - example: "sales "@

Above examples put together into a Type: example- #,###.00_);[Red] (#,###.00);0.00;"sales "@

If the number will always require a specific number of numbers, you can use zeros (0) a place holders in your example. If a number is optional, you will use the pound sign (#) as a placeholder.

A space is represented by an underscore in numbers or within quotes in text.

All text must be enclosed in quotes.

An ampersand (&) will join two items together.

The At Sign (@) will allow varying text.

Here's a simple example. Suppose you want the text "Emp# " to appear before a typed Employee Number that contains 8 numbers with a dash between the 3rd and 4th numbers. In the Type: box you would type "Emp# "000-00000 and click OK.

Everytime an Employee Number was typed (with no dash) into the cell Emp# and a space would precede the number and a dash would separate the 3rd and 4th numbers.

Alignment

To adjust alignment of text within the cell, click on the Alignment tab within the Excel Format Cells dialog box.

Text alignment

Choose from the drop lists to adjust the Horizontal & Vertical alignment. The default for Vertical alignment is to align to the bottom of the cell. If you should have very tall row heights, adjusting this property to top might add to the legibility of your spreadsheet.

You can also adjust cell indentation here. Use the spinner button to add indentation or just type in the inch measurement you want.

Text Control

This area gives you a control over how the text behaves when you type into the cell.

Wrap text- The row height will adjust to accomodate the text.
Shrink to fit- The font size will adjust to fit the specified cell size.
Merge cells- Merges the highlighted cells.

Right-to-left

This drop box will allow you to adjust the reading order of the text. (You shouldn't have to use this unless you are using a foreign language.)

Orientation

This area will allow you to slant the text within the cell. You can specify the degree of slant by one of two methods: click on the dots within the righ-hand box OR adjust the degrees box using the spinner button or by typing the degree.

Font

By using the Font tab on the Format Cells dialog box you have more font formatting options than you would by using the formatting toolbar.

Font

 This list allows you access to all of the typeface options that you have loaded on your machine. Some are TrueType which means that the font will look the same on the screen as on your printer. Others may be scalable printer resident fonts which means that what shows on the screen may not match what comes out on the printer. The icons to the left of the font name will indicate the font type and a description will appear at the lower portion of the dialog box.

Font Style

 In the Font Style box you can add bold, italics or both to your font.

Size

 The size box will allow you to adjust the size of the font. The size of the font will get larger as the number in the size box increases. Even though the list shows only whole number increments, you can specify a font size in between such as 10.5.

Underline

You can apply different types of underlining.
Single- one line under the text or number
Double- two lines under the text or number
Single accounting- one line covering the entire cell
Double accounting- two lines covering the entire cell.

Color

 Change the color of the font by choosing one of the colors in the the drop-down color palette.

Normal Font

Checking this box will restore the selection to the default font and remove any added formatting changes.

Effects

 Allows you to specify Strikethrough, Superscript, or Subscript of the selected text.

To apply super or sub script formatting to only one character within the text:
While in the edit mode of the cell, select the character by dragging the mouse I-beam over the character in the Formula Bar.
Then, click on Format, Cells on the menu and click on the Font Tab.
Check the appropriate Effects check box and click OK.

Preview

 The Preview window will allow you to see all of the formatting that you have chosen for this cell.

Border

See the graphic below for the description of the Border tab.

Patterns

The Patterns tab will allow you to choose a fill color or a pattern (with or without color) to the selected cell(s).

Protection

The Protection tab is discussed more in our page on Protecting Workbooks.

By default, all cells are locked and unhidden.

Excel Format Painter (on the Formatting Toolbar)

The Format Painter will ease the formatting process. It allows you to copy the formatting from an example cell to other cells. To copy formatting using the Format Painter follow these steps:

Adjacent Cells

 1. Highlight the cell whose formatting you want to copy.
 2. Click on the Format Painter button.
 3. Your mouse pointer will change to a selector tool with a brush.
 4. Click and drag over the cells to paint the copied formatting.

Non-adjacent Cells or Multiple Cells

 1. Highlight the cell whose formatting you want to copy.
 2. Double-click on the Format Painter button.
 3. Your mouse pointer will change to a selector tool with a brush.
 4. Click on each cell to paint. (or drag each range.)
 5. Click on the Format Painter button again to turn it off.

Excel Conditional Formatting

You many want to format cells based on the cell values. You can use the conditional formatting feature to apply formatting based on the values.

 1. Highlight the cell(s) to format.
 2. Click on Format, Conditional Formatting on the menu.
 3. In the Conditional Formatting box, set the condition kupon which you wish the formatting to be in effect.
 4. Click the Format button and select the formatting to apply.

 5. Click OK.
 6. Excel will apply the formatting according to the cell value.

Clearing Formats in Excel

At times you will want to take the formatting off of a cell without affecting the data. Remember that the Delete key on the keyboard and the Shortcut menu item, Clear Contents will delete the data but will leave the formatting. To clear the formatting but leave the data, click on Edit, Clear, Formats on the menu.