Format & Arrange:
Copy Formatting

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

Once you have created a formatting scheme, you probably will want to use it on other cells. There are some ways to copy just the formatting.


Format one cell and copy formatting

Where you are:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel BasicsTo subtopics   

Project 3: Format & Arrange
    Format Cells Open arrow to subtopics
    footprintApply Formatting
    footprintCopy Formatting
    footprintPartial, AutoFormat
    footprintStyle - Dialog
    footprintStyle - Style box
    Format ChartTo subtopics
    ArrangeTo subtopics
    ExercisesTo subtopics

Project 4: Groups & FormulasTo subtopics

Project 5: DesignTo subtopics


Icon Step-by-Step 

Step-by-Step: Copy Formatting

 Icon Step-by-Step

What you will learn:

to copy formatting with Format Painter
to copy formatting with Paste Special 
to resize columns

Start with: Class disk trips9.xls (saved in previous lesson)

Format Cell: Format Painter

For quick copying of formatting, the Format Painter can't be beat!

  1. Select cell A4 =  Customer 

    Formatting toolbar choicesFrom the Formatting toolbar, select Arial, size = 12, Bold and Fill = Green, Font Color = White.
    The row resizes automatically because of the larger font size.

  2. Formatting copied from A4 to B4While cell A4 is selected, click on Button: Format Painter the Format Painter button. The pointer changes to Ponter shape: Format Painter the Format Painter shape.
  3. Click on cell B4 =  Trip . (You don't need to drag!) The formats in cell A4 are applied to cell B4 and the pointer returns to the Select shape.
tip To use Format Painter for multiple cells, double-click the button. You must click the button again to turn off Format Painter and go back to the select pointer shape.

Format Cell: Paste Special

  1. Cell B4 copied - border dashes chase each other around the cellSelect cell B4 and click the Copy button. The cell is bordered with a blinking dashed border. The dashes look like they are chasing each other around the cell!
  2. Drag to select cells C4, D4, and E4.
  3. Dialog: Paste Special - Formats chosenChoose  Edit  |  Paste Special…  The Paste Special dialog opens.
  4. Click on the Paste option Formats. Then click OK.

    All of the formatting in cell B4 is applied to C4, D4, and E4.

  5. Press the ESC key to clear the blinking border around cell B4.

    Notice that the columns did not resize when the text expanded due to a larger font size.

Icon: Excel 2000 Icon: Excel 2002Icon: Excel 2003 Resize: Columns

  1. Columns C, D, E selectedDrag across the column headings C, D, and E .
  2. Columns C, D, E resizedDouble click on the right edge of the column heading of one of the selected columns. All the selected columns are resized with AutoFit to be wide enough to read all the characters. Now the text in C4 shows completely.
  3. Class disk Save as  trips10.xls  on your Class disk in the  excel project3  folder.
    full floppy diskHow to handle a full disk.

Resize: Columns

  1. Dragging to select columns C, D, E also selects columns A and B because of merged cells in the columnsDrag across the column headings C, D, and E . In Excel 97 we have a surprise! All the columns to the left are selected, too. This is because of the merged cells in rows 1 and 2 that included columns A through E.

    tip The top left cell of a selected range has a white background and all other cells have a black background. Because cell A1 is part of a merged cell, the whole merged cell is white. This makes it a bit hard to see what is going on.

  2. Click in a cell to unselect.
  3. Selected columns C, D, and E, including merged cellsClick the heading for column C. Hold the CTRL key down and click on column headings D and E.

    Now only those three columns are selected. But the highlighting shows that the merged cells are also selected. Don't forget that the top merged cell is actually selected. It's hard to tell from the highlighting in this case. This is rather confusing. Later versions of Excel do not have this behave. Hurrah!

  4. Double click on the right edge of the column heading of one of the selected columns. Normally this would resize all the columns with AutoFit. But those merged cells won't allow this. Only one column is resized. 
  5. Repeat the double-click on the right edge of column heading for the other two selected columns to apply AutoFit.

    Columns resized

  6. Class disk Save as  trips10.xls  on your Class disk in the  excel project3  folder.
    full floppy diskHow to handle a full disk.