Excel Format Painter: Copy Formatting Between Cells

01
of 03

Excel and Google Spreadsheets Format Painter

Copy Formatting Between Cells with the Format Painter in Excel
© Ted French

Formatting Worksheets Using Format Painter

The format painter feature in both Excel and Google Spreadsheets allows you to quickly and easily copy formatting from one cell or group of cells to another area of a worksheet.

In both programs, the feature is especially useful when extending the formatting in a worksheet to areas containing new data rather than trying to re-create the formatting found on existing data,

In Excel, the format copying options include copying the source formatting one or more times to one or more locations found:

02
of 03

Multiple Copying With Format Painter

Copy Formatting Between Cells with the Format Painter in Excel.
© Ted French

.Copy Formatting to Other Worksheet Cells in Excel

The following steps were used to apply a variety of formatting options on data in column B in the image above to the data in columns C and D. 

  1. Add all the formatting options you want to use to the source cell(s).
  2. Highlight cells B4 to B8 with the mouse pointer.
  3. Click on the Home tab of the ribbon.
  4. Click on the format painter icon (a paint brush) on the left side of the ribbon—when the mouse pointer is hovering above the worksheet, a paintbrush is displayed with the pointer to indicate that format painter has been activated.
  5. Highlight cells C4 to D8.
  6. The formatting options will be copied to the new location and the format painter is turned off.

Double Clicking the Format Painter for Multiple Copying

As mentioned above, an additional option available in Excel only is to double click on the format painter icon with the mouse pointer.

Doing so keeps the format painter feature turned on even after clicking on one or more destination cells.

Using this option makes it easy to copy formatting to multiple non-adjacent cell(s) located either on the same or different worksheets or workbooks.

To copy formatting to non-adjacent groups of cells in Google Spreadsheets, it is necessary to repeat the steps above for copying formatting to a second worksheet area. 

Turning Off the Format Painter in Excel

Two methods for turning off format painter when it is in multiple copy mode in Excel are:

  1. Press the ESC key on the keyboard.
  2. Click once on the format painter icon on the Home tab of the ribbon.

Keyboard Shortcut for Excel's Format Painter

A simple, two key shortcut does not exist for Excel's format painter.

However, the following keys combinations can be used to mimic the format painter. These keys make use of the paste formats option in the Paste Special dialog box.

  1. Press Ctrl + C - to copy the contents of the source cell(s)—data and applied formatting—the source cell(s) will be surrounded by the marching ants.
  2. Highlight the destination cell or adjacent cells.
  3. Press Ctr Alt + V - to open the Paste Special dialog box.
  4. Press T  + Enter to paste just the applied formatting to the destination cell(s).

Since the marching ants should still be active around the source cell(s), the cell formatting can be pasted multiple times by repeating steps 2 to 4 above.

Create a Macro

If you use format painter frequently, an easier way to apply it using the keyboard would be to create a macro using the above key strokes and then assign a shortcut key combination that can be used to activate the macro.

03
of 03

Google Spreadsheets Paint Format

Copy Formatting in Google Spreadsheets with Paint Format
Copy Formatting in Google Spreadsheets with Paint Format. © Ted Fench

Copy Formatting to One or More Adjacent Cells in Google Spreadsheets

Google Spreadsheets' paint format option, as it is called, is not quite as capable as its Excel counterpart, as it is limited to copying source formatting to only one destination at a time:

  • To areas of the same worksheet
  • To different worksheets in the same file

The Google Spreadsheets' feature cannot copy formatting between files.

The steps used to copy the formatting features from cells B4:B8 to cells C4:D8 shown in the image above are:

  1. Add all the formatting options to the source cells.
  2. Highlight cells B4 to B8 with the mouse pointer.
  3. Click on the paint format icon (a paint roller) on the tool bar.
  4. Highlight the destination cells C4 to D8.
  5. The formatting options from the cells in column B will be copied to the cells in columns C and D and the paint format feature is turned off.

Multiple Copying with Paint Format

As mentioned above, paint format is limited to copying formatting to only one destination at a time

To copy formatting to non-adjacent groups of cells in Google Spreadsheets, it is necessary to repeat the steps above for copying formatting to a second worksheet area. 

Format
mla apa chicago
Your Citation
French, Ted. "Excel Format Painter: Copy Formatting Between Cells." ThoughtCo, Sep. 5, 2017, thoughtco.com/excel-format-painter-copy-formatting-4117553. French, Ted. (2017, September 5). Excel Format Painter: Copy Formatting Between Cells. Retrieved from https://www.thoughtco.com/excel-format-painter-copy-formatting-4117553 French, Ted. "Excel Format Painter: Copy Formatting Between Cells." ThoughtCo. https://www.thoughtco.com/excel-format-painter-copy-formatting-4117553 (accessed October 24, 2017).