Lesson 4: Formatting Cells

/en/excel2010/modifying-columns-rows-and-cells/content/

Introduction

Excel 2010

Spreadsheets that have not been formatted can be difficult to read. Formatted text and cells can draw attention to specific parts of the spreadsheet and make the spreadsheet more visually appealing and easier to understand.

In Excel, there are many tools you can use to format text and cells. In this lesson, you will learn how to change the color and style of text and cells, align text, and apply special formatting to numbers and dates.

Formatting text

Many of the commands you will use to format text can be found in the Font, Alignment, and Number groups on the Ribbon. Font commands let you change the style, size, and color of text. You can also use them to add borders and fill colors to cells. Alignment commands let you format how text is displayed across cells both horizontally and vertically. Number commands let you change how selected cells display numbers and dates.

To change the font:

  1. Select the cells you want to modify.
  2. Click the drop-down arrow next to the Font command on the Home tab. The font drop-down menu appears.
  3. Move your mouse over the various fonts. A live preview of the font will appear in the worksheet.
    Changing the font Changing the font
  4. Select the font you want to use.

To change the font size:

  1. Select the cells you want to modify.
  2. Click the drop-down arrow next to the font size command on the Home tab. The font size drop-down menu appears.
  3. Move your mouse over the various font sizes. A live preview of the font size will appear in the worksheet.
    Changing the font size Changing the font size
  4. Select the font size you want to use.

You can also use the Grow Font and Shrink Font commands to change the size.

The Grow Font and Shrink Font Commands The Grow Font and Shrink Font commands

To use the bold, italic, and underline commands:

  1. Select the cells you want to modify.
  2. Click the Bold, Italic, or Underline command on the Home tab.
    The Bold, Italic, and Underline Commands The Bold, Italic, and Underline commands

To add a border:

  1. Select the cells you want to modify.
  2. Click the drop-down arrow next to the Borders command on the Home tab. The border drop-down menu appears.
    Adding a border Adding a border
  3. Select the border style you want to use.

You can draw borders and change the line style and color of borders with the Draw Borders tools at the bottom of the Borders drop-down menu.

To change font color:

  1. Select the cells you want to modify.
  2. Click the drop-down arrow next to the font color command on the Home tab. The color menu appears.
  3. Move your mouse over the various font colors. A live preview of the color will appear in the worksheet.
    Adding a font color Adding a font color
  4. Select the font color you want to use.

Your color choices are not limited to the drop-down menu that appears. Select More Colors at the bottom of the menu to access additional color options.

To add a fill color:

  1. Select the cells you want to modify.
  2. Click the drop-down arrow next to the fill color command on the Home tab. The color menu appears.
  3. Move your cursor over the various fill colors. A live preview of the color will appear in the worksheet.
    Adding a fill color Adding a fill color
  4. Select the fill color you want to use.

To change horizontal text alignment:

  1. Select the cells you want to modify.
  2. Select one of the three horizontal Alignment commands on the Home tab.
    • Align Text Left: Aligns text to the left of the cell
    • Center: Aligns text to the center of the cell
    • Align Text Right: Aligns text to the right of the cell
    Aligning Text Left Aligning Text Left

To change vertical text alignment:

  1. Select the cells you want to modify.
  2. Select one of the three vertical Alignment commands on the Home tab.
    • Top Align: Aligns text to the top of the cell
    • Middle Align: Aligns text to the middle of the cell
    • Bottom Align: Aligns text to the bottom of the cell
    Top Aligning Text Top Aligning Text

By default, numbers align to the bottom-right of cells, while words and letters align to the bottom-left of cells.

Using the Format Painter

If you want to copy formatting from one cell to another, you can use the Format Painter command on the Home tab. When you click the Format Painter, it will copy all of the formatting from the selected cell. You can then click and drag over any cells you want to paste the formatting to.

Watch the video below to learn two different ways to use the Format Painter.

Formatting numbers and dates

One of Excel's most useful features is its ability to format numbers and dates in a variety of ways. For example, you might need to format numbers with decimal places, currency symbols ($), or percent symbols (%).

To format numbers and dates:

  1. Select the cells you want to modify.
  2. Click the drop-down arrow next to the Number Format command on the Home tab.


    Accessing Number Format commands Accessing Number Format commands
  3. Select the number format you want. For some number formats, you can then use the Increase Decimal and Decrease Decimal commands (below the Number Format command) to change the number of decimal places that are displayed.

Click the buttons in the interactive below to learn about the different number formats.

labeled graphic

More Number Formats

You can easily customize any format in More Number Formats.

For example, you can change the U.S. dollar sign to another currency sign, have numbers display commas, and change the number of displayed decimal places.

Text

Text formats numbers as text, meaning what you enter into the cell will appear exactly as you wrote it. Excel defaults to this setting if a cell contains both text and numbers.

Scientific

Scientific formats numbers in scientific notation.

For example, if you enter 140000 into the cell, then the cell will display the number as 1.40E+05.

Note: By default, Excel will format the cell in scientific notation if it is a large integer. If you do not want Excel to format large integers with scientific notation, use the Number format.

Fraction

Fraction formats numbers as fractions separated by the forward slash.

For example, if you enter 1/4 into the cell, the cell will display the number as 1/4. If you enter 1/4 into a cell that is formatted as General, the cell will display the number as a date, 4-Jan.

Percentage

Percent formats numbers with decimal places and the percent sign.

For example, if you enter 0.75 into the cell, the cell will display the number as 75.00%.

Long Date

Long Date formats numbers as Weekday, Month DD, YYYY.

For example, it would appear in this format: Monday, August 01, 2010.

Time

Time formats numbers as HH/MM/SS and notes AM or PM.

For example, it would appear in this format: 10:25:00 AM.

Short Date

Short Date formats numbers as M/D/YYYY.

For example, August 8th, 2010, would be 8/8/2010.

Accounting

Accounting formats numbers as monetary values like the Currency format, but it also aligns currency symbols and decimal places within columns. This format will make it easier for you to read long lists of currency figures.

Currency

Currency formats numbers as currency with a currency symbol.

For example, if you enter 4 into the cell, the cell will display the number as $4.00.

Number

Number formats numbers with decimal places.

For example, if you enter 4 into the cell, the cell will display the number as 4.00.

General

General is the default format for any cell. When you enter a number into the cell, Excel will guess the number format that is most appropriate.

For example, if you enter 1-5, the cell will display the number as a Short Date, 1/5/2010.

Challenge!

  1. Open an existing Excel 2010 document. If you want, you can use this example.
  2. Select a cell and change the font, size, or color of the text. If you are using the example, change the title in cell A1 to the Verdana font, size 16, with a font color of green.
  3. Select a portion of the content in one cell, and make only that portion bold. For example, if the words Weight (pounds) are in a cell, make only the word Weight bold.
  4. Add a border to a group of cells. If you are using the example, add a border to the descriptions in row 3.
  5. Change the fill color of a group of cells. If you are using the example, add a fill color to row 3.
  6. Try changing the vertical and horizontal text alignment for some cells.
  7. Try changing the formatting of a number. If you are using the example, change the date format in column A.

/en/excel2010/saving/content/