Written By:
Adiste Mae

With this tutorial in Excel, you will now be able to do copy formatting with the use of Format Painter, Fill Handle, as well as paste other special options.

This procedure is compatible with all other versions of Excel. From 2007 to Excel 365.

After all the work you’ve done in worksheet calculation, you might want to add some refinement touches to your work to make it a lot more presentable.

Regardless if it is a presentation in the office you are trying to create or a worksheet summary for the board members and directors, proper formatting ensures important data to stand out as well as convey the most relevant information.

Amazingly, Microsoft Excel does have a simple way of doing copy formatting.

The Excel Format Painter makes formatting one cell and then applying it with ease to another.

At the end of this tutorial, not only will you know the most effective and time-saving ways in using Format Painter in Excel, but you will also find a couple of other ways to do copy formatting in your sheets.

Microsoft Excel Format Painter

Copying formatting in Excel is amongst the most advantageous and seldom-used features.

The way it works is it copies the formatting of one cell and applies it to other cells.

• Number format (General, Percentage. Currency, etc.)
• Font face, size, and color
• Font characteristics like bold, italic, and underline
• Fill color (cell background color)
• Cell Borders
• In all versions of Excel, the button for Format Painter can be found on the Home tab, in the Clipboard group, next to the paste button:

Use of Format Painter in Microsoft Excel

Do the following to copy cell formatting with the use of Excel Format Painter:

1. Choose the cell with the formatting that you want to copy.
2. From the Home tab, in the Clipboard group, then select the Format Painter option. The cursor will then be changed into a paintbrush.
3. Place the cell where you choose to apply the formatting then click on it.

Tips on Excel Format Painter

If more than one cell needs formatting change, clicking the cells one by one would take a lot of effort.

The following steps will surely speed up the work.

1. Copying format to a range of cells.

When copying format to attached cells, select the cells you want to copy the format, select Format Painter, and then drag the paint-brush cursor past the cells that are intended to be reformatted.

2. Copying format to noncontiguous cells

In copying format to non-adjacent cells, the Format Painter button should be clicked twice instead of a single click.

Then the Excel Format Printer will be “locked on”,  and all selected cells and ranges will have the copied format unless Esc or the Format Painter button is clicked.

3. Copying the format of one column to another, row-by-row

To copy the format of an entire column with ease, click on the column’s heading of your choice, select Format Painter, and select the heading of the column we will be copy-formatting to.

As shown in the previous figures, the updated formatting is applied to the targeted column, row-by-row, with the inclusion of column width:

In the same manner, the entire row’s format can be copied, column-by-column. To do this, click first the sample heading, select Format Painter, and click the heading of the row to format.

As you can see, copying formats using Format Painter makes it a lot easier. Often the scenario with Excel, there are a number of ways to do the same thing nonetheless. Later, you will learn two more ways of copying formats in Microsoft Excel.

Copying format down a column with the use of Fill Handle

The fill handle is often used in copying formulas and/or auto-fill cells with data.

However, with just a few clicks, it can also be used to copy Microsoft Excel formats. Here’s what you can do:

1. First is to format the cell based on your preference/
2. The fill handle is a small green square at the bottom right corner. Hover over the fill handle of the selected formatted cell. As you drag the fill handle, it will change into a cross.
3. Hold and run over the cells you want to do the formatting: Doing this will copy as well the value of the reference cell, but no worries, we can undo it.
4. Release first the fill handle, then click Auto Fill Options, and the drop-down menu will show, and then select Fill Formatting Only:

Perfect! The values in cells were reverted back.

Tip: If you want to copy and apply the formatting toward the bottom of the column, click the fill handle rather than dragging it, then select AutoFill Options, and click Fill Formatting Only.

Copy Cell formatting to an entire column or row

The Excel Format Painter works excellently with a few cells and the Fill Handle.

But if we are going to copy the format of a cell specifically to the whole column and/or row for the new format to be applied completely in all cells whether, in a column or row with the inclusion of blank cells, we can use the Formats option in Excel Paste Special.

1. Select first the cell of your choice and then press Ctrl+C to copy the content as well as the formats.
2. Choose the whole column and/or row to apply the format, and click the heading.
3. Right-click selection, and select Paste Special.
4. When the dialog box of Paste Special appears, select Formats, and click Ok.

Alternatively, from the Paste Special pop-up menu, select the Formatting option. A live preview of the recently created format will be displayed, as shown in the figure:

Copy Formatting Shortcuts in Excel

Unfortunately, a single let alone shortcut to copy cell formats was not provided by Microsoft Excel.

But this can be solved with the use of a sequence of shorter alternative routes or patterns.

So if your preference is to work from a keyboard, copy formatting in Microsoft Excel using the following tips will help you along the way.

Shortcuts to Excel Format Painter

1. Click the cell having the necessary format.
2. Press Alt, H, F, and P keys.
3. Select the cell where you want to put it in the desired format.

Important Reminder: In Microsoft Excel, the shortcuts for Format Painter should be pressed one at a time.

• The Alt key allows keyboard shortcuts (ribbon commands)

• H activates the Home tab 

• F, P activates the Format Painter 

Shortcuts to Paste-Special Formatting

One way to quickly copy a format in Microsoft Excel is to use the keyboard shortcuts for Paste Special > Formats:

1. Choose the cell to copy the format from.
2. Ctrl + C to copy the chosen cell to the Clipboard
3. Choose the cells to apply the format
4. In Excel versions of 2016, 2013, and/or 2010, click Shift + F10, S, R, and then select Enter.

If it happens that someone is still using Microsoft Excel 2007, click Shift + F10, S, T, and Enter.

These key sequences will do the job:

• Shift + F10 shows the context menu
• Shift + S chooses the command for Paste Special
• Shift + R only selects only formatting to paste.

These are only some of the easy ways to do copy formatting in Microsoft Excel. I hope you have learned a thing or two.