Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101

Home > Jan's CompLit 101 > Working with Numbers > Format > Cells > Apply Formatting
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Numbers

    Format: Cells: Apply Formatting

In Excel your formatting choices are on the ribbon but also are grouped neatly in a single dialog with six tabs. There are so many choices! It will take you a while to become familiar with all of them.

In this section you will apply text formatting. Later you will work with number formats, alignment, borders, and fills.

Dialog: Format Cells (Excel 2016)

Default settings for Format Cells - Font tab
'Normal font' resets the dialog to the default font settings.

Icon Step-by-Step

Step-by-Step: Apply Formatting

 Icon Step-by-Step

What you will learn: to edit an existing header in Page Layout View
to apply text formatting with dialog
to apply text formatting with ribbon
to merge cells and center cells
to unmerge cell

Start with: Icon: Class disk trips9-Lastname-Firstname.xlsx (saved in previous lesson)

Edit Header: Page Layout View

  1. Open  trips9-Lastname-Firstname.xlsx  from your Class disk in the  excel project2  folder to Sheet1.

  2. Switch to Page Layout view.
    (Button is on the Status bar)
  3. Edit the Right section to read  Excel Project 3 .

    Trips9-format-Firstname-Lastname with edited header

  4. Icon: Class disk Use Save As to save the file to a new folder,  excel project3 , on your Class disk as  trips9-titleformatted-Lastname-Firstname.xlsx .

Format Cell: Dialog

  1. Switch to Normal view. Button: Format > Format Cells (Excel 2007)
  2. Select cell A1 =  World Travel Inc. 
  3. From the ribbon on the Home tab in the Cells tab group, click the button Format and then Format Cells....
    The Format Cells dialog appears. It has 6 tabs for formatting options.
  4. Icon: Experiment Experiment: Format Cells dialog
    on each tab to see what you can use it for.
    Open the drop lists to see what the choices are.
    Especially notice the choices on the tabs Number and Alignment.
    Altogether there are a LOT of choices!

    When you have inspected all of the choices on all of the tabs...

  5. Dialog: Format Cells > Font - A1 formatted (Excel 2007)Return to the Font tab.
    Cell A1 is still selected.  
  6. Make the following choices:
    Font = Matura MT Script Capitals
    Font Size = 24
    Font Color = Blue, Accent 1, Darker 25%

    Button: Font Color > Blue, Accent 1, Darker 25% (Excel 2010)

  7. Click on OK to close the dialog and apply your choices.

Format Cell: Ribbon

Some of the most common formatting choices are also available on the ribbon. Handy! 

  1. Ribbon: Home > Font tab group - A2 formatting (Excel 2007)Select cell A2 =  Anniversary Specials 
  2. On the  Home tab in the Font tab group, use the buttons to format this cell as:

    A2 formatted - Arial, 18 pt, Blue Accent 1 Darker 25%, Bold (Excel 2007)Font = Arial;
    Font Size = 18;
    Font style = Bold;
    Font Color = Blue, Accent 1, Darker 25%

Format Cell: Merge and Center

  1. Select range A1:E2Select the range
    A1:E2, which is the first two rows across the width of your data.
  2. Message: The selection contains multiple data values. Merging into one cell will keep the upper-left most data only. (Excel 2007)On the Home tab click the Merge and Center button Button: Merge and Center (Excel 2007) Button: Merge and Center (Excel 2016) .
    You get a warning! 

    Message: Merging cells only keeps the data in the top-left cell. (Excel 2013)Warning Merging can lose data!
    Only the data in the upper left cell is kept when cells are merged. This is very different from the way Word handles merged table cells. In Word each cell's contents becomes a separate paragraph in the merged cell.

    At least Excel will warn you that you are about to lose data.

  3. Click Cancel.

    Icon: TroubleProblem: Text in A2 vanishes
    If you clicked on OK in the message box, then all of the selected cells merge into a single cell but only the text from the first cell is kept.
    Solution: Undo immediately.

  4. Cells A1:E1 merged and centeredSelect just A1:E1 this time - all on the same row.
  5.  Click on Button: Merge and Center (Excel 2007) Button: Merge and Center (Excel 2016) the Merge and Center button again.
    The text in A1 is centered across the selection.
  6. Titles merged and centeredSelect range A2:E2.
  7. Click Button: Merge and Center (Excel 2007) Button: Merge and Center (Excel 2016) the Merge and Center button. 
    The text in A2 is centered over the selected range.
  8. Icon: Class disk Save.

Unmerge Cells: Ribbon and Dialog

If you change your mind or have not selected the right cells to merge, you can unmerge in two ways, using the ribbon or the Format Cells dialog. This section is just practice with no changes to save.

Unmerge with Ribbon:

  1. Button: Merge and Center > Unmerge Cells (Excel 2007)Click on the title, World Travel Inc., in the merged cell on row 1.
    The merged cell is selected.  
  2. Click the arrow beside the Merge and Center button. 
    The menu opens.
  3. Title unmerged.Click on Unmerge Cells.
    The text moves back to A1 but it is still centered in that single cell. That makes part of the title hang out of view to the left.
  4. Click in cell A3 to deselect.
  5. Undo.

Unmerge with Dialog:

  1. Dialog: Format Cells > Alignment - uncheck Merge cells (Excel 2007)Click on the subtitle in row 2.
  2. On the Home tab, click the dialog box launcher Button: Dialog Box Launcher for the Alignment tab group.
    The Format Cells dialog opens to the Alignment tab.
  3. Uncheck the box by Merge Cells.
  4. Unmerged - Subtitle back into A2 but still centered.Click on OK to close the dialog.
    The subtitle is back into A2 but still centered.
  5. Undo.

tip Add button to Quick Access Toolbar: If you find yourself doing a lot of unmerging or having to take out centering, you could customize your Quick Access Toolbar to include Button: Merge (Excel 2007) a Merge Cells button (without the centering) and Button: Unmerge (Excel 2007) an Unmerge Cells button.