Number Format

When you enter numbers or text into a cell in Excel, you can format how the information is displayed by changing the number to appear as a percentage, in Dollars, or in any one of several other formats.

Excel can display numbers in many ways.

1. Open a new workbook.
2. Click cell B2, type 123456, press enter, and then click B2 again.
3. On the Format menu, click Cells.
   
   
    
4. On the Number tab, choose Currency. In Decimal places, click the down arrow until 0 appears, and then click OK.
5. Click B2, in the Formula Bar, type – in front of 12345, press enter, right-click cell B2, and click Format Cells.

Note: Another way to change format is to right-click the cell that you want to format. On that shortcut menu, click Format Cells.

6. On the Number tab, under Category, click Number, under Negative numbers, click 1234 in red, and click OK.
7. Close the workbook, save if you choose.

Formatting numbers within cells with Increase/Decrease Decimal

Note
Use these buttons: to increase and decrease decimal places.

Excel automatically changes the width of a cell as you enter the number.

Formatting numbers in a cell with the Decrease Decimal and Increase Decimal buttons

1. Open a new workbook.
2. In cell B4, enter 12345678999, and press Enter.
3. Add a decimal point between 5 and 6, and press Enter.
4. Click B4, and click the Decrease Decimal button twice.
5. Increase the number of decimal places four times with the Increase Decimal button.
6. Close the workbook without saving.

Entering Dates

Displaying numbers as dates, and formatting date cells

1. Open the TechnologyCalc workbook you created earlier.
2. Right-click the B column header to select the dates and all of column B.
3. On the shortcut menu, click Format Cells.
4. On the Number tab, under Category, click Date.
5. Under Type, choose 4-Mar-97.  Click OK, and check out the results.  Try other date formats, then: go back to 4-Mar-97.
6. Click OK.
7. On the File menu, click Save As.
8. Change the name to Technology2.
9. Click Save.

Merge & Center - Using Formatting Toolbar buttons

Using the Formatting toolbar to change cell formats - Merge & Center

1. Open your Technology2 workbook.
2. Click the cell in column C with Technology Challenge in it to select it, and drag to select all the cells through column F.
3. On the Formatting Toolbar, click the Merge and Center button.
   
   
   
   
4. Select the words Technology Challenge.
5. Click the Italic button.
6. Click the Bold button.
7. Click Save.

Use the Format Painter

Copy formats from one cell or range of cells to another

1. Select the cell or range of cells from which you want to copy the format.
2. On the Standard toolbar, click Format Painter.
3. Select the cell or range of cells to apply (copy) the formatting to.

Apply Cell Borders and Shading

1. Select the cell or range of cells you want to add borders to.
2. Clicking Borders on the Formatting toolbar will apply the most recently selected border style.
3. To apply a different border style, click the arrow next to the Borders button, and then choose from the palette.
   • For a greater range of choices and control, click the Format menu and click Cells...
   • Click on the Border tab to choose color, line style and size, and the location around the cell.
      
4. Add shading by clicking the Fill Color button on the Formatting toolbar.
   • You can also go to the Format menu and click Cells... and click the Patterns tab to choose patterns to fill cells with.

Apply an AutoFormat

1. Select the range of cells you want to format.
2. On the Format menu, click AutoFormat...
3. Click the format you want.  You may want to scroll to see all of the choices.

Resizing Columns

1. Open a new workbook.
2. Click cell D4, and type 12345.6666, and then press enter.
3. Right-click D4,and click Format Cells.
4. Click the Number tab, click Number in the Category pane, click the up arrow in Decimal places to 6, and then click OK.
5. On the Format menu, select Column, and click Width.
6. In the Column Width box, type 6, and click OK.
   A number of #####s appears where the number you previously typed had been.
   
   Note  When "#######" appears in a cell, the cell is too narrow for the data to be displayed.
    
7. Right-click on the Column D header, click Column Width..., type 24, and click OK.
   
   The #####s go away, and your number is back with all its decimal places, plus some empty space to the left.
    
8. Position the pointer between the D and E columns until you see the double arrow, and then double-click.  The empty space goes away but the column width is the exact size of your number.
    
9. Position the pointer between the D and E columns until you see the double-arrow, and then click and drag, watching the little message box tell you how the width is changing as you drag. Drag until the width is about 30.
    
10. Close the workbook without saving, unless you feel the need to hang on to this file for a long time.

Resizing Rows

• Numbers 5-9 above apply to the rows, except you deal with the numbered rows (2 & 3 for example) not the lettered columns, and the double-arrow is horizontally oriented.

Formatting Rows and Columns

Adjusting rows and columns so that the text within them is aligned left, centered, aligned right, or justified is quick and easy: Select the row or column, and use the buttons on the Formatting toolbar.

Centering data or text horizontally

Centering the text in a title row makes the text easier to read.

1. Open the Technology2 workbook.
2. Click on the row header on the left margin of the row with all the titles (with Dates etc.) to select the entire row.
3. On the Formatting toolbar, click the Center button to center all of the text in that row.
4. On the left margin, click row headers 6 through 12 to select all the cells, and click Center again on the Formatting toolbar.
5. Save the workbook as Technology3.

Clear Cell Contents

Clearing cell Contents, Formats, and Comments

1. Select the cells, rows, or columns you want to clear of formats or contents.
2. On the Edit menu, point to Clear, and then click All, Formats, Contents, or Comments.
   • Delete or Backspace deletes contents except for formats and comments.

Insert and Delete Selected Cells

Insert Cells

• Click where and how many you want to insert:
  • Insert new blank cells    Select a range of cells where you want to insert the new blank cells. Select the same number of cells as you want to insert.

    Insert a single row  Click a cell in the row immediately below where you want the new row. For example, to insert a new row above row 5, click a cell in row 5.

    Insert multiple rows  Select rows immediately below where you want the new rows. Select the same number of rows as you want to insert.

    Insert a single column  Click a cell in the column immediately to the right of where you want to insert the new column. For example, to insert a new column to the left of column B, click a cell in column B.

    Insert multiple columns  Select columns immediately to the right of where you want to insert the new columns. Select the same number of columns as you want to insert.
     

• On the Insert menu, click Cells, Rows, or Columns.
• If you are moving or copying a range of cells, and not a row or column, in the Insert Paste dialog box, click the direction to shift the surrounding cells: Shift cells right, Shift cells down, Entire row, or Entire column.

Delete Cells

1. Select the cells, rows, or columns you want to delete.
2. On the Edit menu, click Delete.
3. If you are deleting a range of cells, click Shift cells left, Shift cells up, Entire row, or Entire column in the Delete dialog box.

Note   Excel keeps formulas up to date by adjusting references to the shifted cells to reflect their new locations. However, a formula that refers to a deleted cell displays the #REF! error value.

Define, Apply, and Remove a Style, Apply Font Styles

Define a Style

1. Select a cell that has the combination of formats you want to include in the new style.
2. On the Format menu, click Style...
3. In the Style Name box type a name for the new style.
4. To define the style and apply it to the selected cells, click OK.
5. To define the style but NOT apply it at this time, click Add then click Close.

Apply a Style, Apply a Font Style

1. Select the cells you want to format.
2. On the Format menu, click Style...
3. In the Style Name box click the style you want.

Remove a Style

1. On the Format menu, click Style...
2. In the Style Name box click the style you want to delete.
3. Click Delete.

Changing Column Alignment

Changing the alignment of columns can make a worksheet easier to read.

Align Left

1. In the Technology3 worksheet, click column header B to select the entire column.
2. Click the Align Left button to left-align everything in the column.
3. Re-select B5 (Dates) and click the Center (align) button to re-center that cell.

Align Right is obviously similar.

Rotating text

Rotating the titles allows you to condense the title while keeping column headings readable.
Rotating text on a worksheet is useful when you are recording grades and want to clearly label assignments.
This feature allows you to format any cell on your worksheet.

Note If you try to rotate merged cells, you may find that only the first letter will display.

Note Rotate column heads +90 degrees to read the text from bottom to top; rotate the heads –90 degrees to read the text from top to bottom.

Rotating text

1. Open the Technology3 workbook.
2. Select cells C5 through G5.
3. On the Format menu, click Cells.
4. On the Alignment tab, under Orientation, click and drag the Red Diamond to the vertical position (+90 degrees).

5. Click OK.
6. Now click and drag the Red Diamond to the +45 degrees position.
   
   
    
7. Save the worksheet.

Add ClipArt

1. On the Insert menu, point to Picture then to ClipArt.
2. In the Insert ClipArt dialog box, à
3. Type a keyword into the Search for clips: window.
4. If the choices that appear aren't quite what you want, click the All Categories button, or the Back arrow, to get back to the screen (to the right) that you started with.
5. In that screen, you can probably find a category that contains what you want.
6. Once you have found the clip you want to insert, click on it, then click the Insert clip button at the top of the little menu that opened. à

Adding a map to your worksheet

1. On the Insert menu, click Object, then under Create New click Microsoft Map.
2. Click Australia.
3. Kinda nifty, huh?

Creating do-it-yourself graphics

Now that you've tried your hand at preprogrammed ClipArt, you are ready to create and insert a do-it-yourself graphic. The Drawing toolbar makes it easy to create original, one-of-a-kind graphics.

1. Open a new worksheet and insert any ClipArt you like.
2. Open your Drawing toolbar if it's not already open.
3. Click the ClipArt you have just inserted, and then click Shadow on the Drawing toolbar.
4. Click Shadow Style 6 to place a shadow behind the graphic:
   • Rest your pointer over the Shadow menu buttons and Excel will tell you what they're called, like "Shadow Style 6"
5. Click Shadow again, and click Shadow Settings.
6. On the Shadow Settings toolbar that should be "floating" on your screen somewhere, click the arrow to the right of Shadow Color (Custom).
7. Click any distinctive color, but not gray or black (just for this exercise).
8. Under Shadow Settings, click Nudge Shadow Down four or more times, and click Nudge Shadow Right four or more times.
9. Under Shadow Settings, click the arrow to the right of Shadow Color.
10. Click Semitransparent Shadow.
11. Save as CoolClipArt.

Click here for more information on manipulating graphics.

Back to Previous Lesson   ... TAKE THE TEST ...     Next Lesson:  Using Numbers, Formulas and Functions

Go Back To: Excel Index                                                                                                                                      Last UPDATED 11/14/05