How to Copy Formatting In ExcelFormat Painter, Paste Special Action & Fill Handle

Imagine that you’re working your way in Excel.

You’re inputting data as fast as you can without regarding the formatting.

After you’re done inputting all the data, you format each cell (that contains data) one by one… and then you stop after finishing 10 cells.

You ask yourself, “isn’t there a more efficient way of doing this?”.

I know how annoying it could be to look at an excel sheet and find one or more cells sticking out because they do not follow the formatting of the other cells in their row or column.

Just take a look at this:

Pretty annoying, right?

The cells that don’t conform with the formatting of the others are sticking out.

And you just know that they’re not in the right formatting.

And so, you will have to fix the formatting of these so that they’ll become uniform with the rest of the data.

Doing it manually for a few cells can be okay.

But doing it for many cells can be inefficient.

Rather than doing it manually, you may want to copy and paste the formatting instead.

So to answer your hypothetical question above, yes, there is a more efficient way of doing things.

In fact, there’s more than one way. We will be learning these different ways to copy formatting in Excel.

Copy Formatting to a Single Cell (Using the Format Painter)

We’ll start simple first. Let’s learn how to copy formatting to a single cell using the format painter.

The format painter button can be found on the home tab, right below the cut and copy buttons:

What this button does is it copies the format of a selected cell or range of cells to another cell or range of cells.

Suppose we have the following data:

What we want to do here is to copy the format of cell A2 (the one with the accounting format) to C2 (the one with the plain number format):

To do so, we’ll have to do the following steps:

• Select cell A2, the cell that we want to copy the formatting.

• Click the Format Painter Button. This should change the mouse icon into a paintbrush icon with a plus sign beside it. It should look like this:

• The next and final step is to click the cell where we want to paste the formatting. In this example, that would be cell C2.

And there. We were able to copy the format of cell A2 to C2.

The Format Painter is a useful tool for copying and pasting the formatting of one cell to another.

You’ll know that it’s in effect when the mouse cursor changes to a brush with a plus sign.

You can also use it to copy and paste the formatting to a range of cells.

Basically, the Format Painter copies everything on a cell except its data value. It can copy the following:

• Font, font size, color, and characteristics (bold, italics, underline)
• Number format (e.g. accounting, percentage, date)
• Text alignment and orientation
• Cell borders (type, color, size
• Custom number formatting (if any)
• Conditional formatting

Copy Formatting to Multiple Cells (Using the Format Painter)

We can also use the format painter to copy the formatting of one cell to multiple cells or a range of cells.

Suppose we have the following data:

What we want to do is to copy the format of cell A2 to cells C2 to C9 using Format Painter.

To do so, we have to do the following steps:

• Select cell A2. This is the cell that we want to copy the formatting.
• Click the Format Painter button. As a reminder, it is located on the home tab right below the Cut and Copy buttons. The icon should change to a brush beside a plus sign.
• Select the cells where you want to paste the formatting. In this example, we want to paste the formatting to cells C2 to C9.

• The copied formatting will be pasted to the selected cells as follows:

Do note that this is only really useful for cells that are adjacent to each other.

This includes cells from the same rows or columns.

For multiple cells that are far from each other, we have to use the format painter for each non-adjacent cell.

To aid in this, we can double-click the Format Painter button.

You’ll notice that after applying the format to the target cell, the icon will turn back to normal.

This means that Format Painter is no longer in effect.

When you double-click the Format Painter button, the icon (brush with a plus sign) will stay the same even after selecting a cell to paste the format.

This means that Format Painter is still in effect.

To disable it, you only need to click the Format Painter button again.

Copy Formatting Using Paste Special

Another way to copy the formatting of one cell is to use Paste Special.

This command lets us copy a lot of attributes such as values, text, and of course, formatting (among other things).

Suppose we want to use this command to copy and paste the formatting of one cell to another.

The data that we’ll be using is the following:

To use paste special to copy and paste the formatting cell of one cell to another, we’ll have to do the following steps:

• Select cell A2. This is the cell that we want to copy the formatting
• Either use the keyboard shortcut Ctrl + C to copy the cell or right-click it and click Copy.

• Select the cell/s where we want to paste the formatting. In this example, we’ll be selecting cells C2 to C9.

• Right-click any of the selected cells. This should show us a range of actions to select from. We will choose the Paste Special action.

• If we click the arrow beside the Paste Special action, we will be given a range of options. We will click the icon that is shown below. Alternatively, we can press the R button.

• The result should look like this:

As we can see, it’s the same result when we used the Format Painter.

If we want to copy and paste the formatting to multiple cells that aren’t adjacent to each other, we’ll have to use the paste special: formatting action once.

Then for the succeeding cells, we only have to use the F4 key.

What the F4 key does is that it repeats your last action, which in this case was pasting the formatting.

Do note that you can use Format Painter and Paste Special to copy the formatting of a cell from one Excel sheet or book to another cell in another Excel sheet or book.

The same cannot be said for the next method.

Copy Formatting Using the Fill Handle

The last method that we’ll discuss in this article involved the use of the Fill Handle.

For those unfamiliar with it, it’s the black cross icon that appears when you position your cursor in the bottom right corner of a selected cell.

The Fill Handle allows us to copy certain attributes of the selected cell to adjacent cells (cells with the same row or column).

This is also applicable to multiple rows and/or columns as long as they are adjacent.

As you can guess, we can use the Fill Handle to copy the formatting of the selected cell to other cells.

We will be using the following data as our example:

We want to copy the formatting of cell A1 to the rest of the cells in the same column (cells A3 to A8) using the Fill Handle.

To do so, we’ll have to do the following steps:

• Select cell A1. This is the cell that we want to copy the formatting. Position the cursor on the bottom right corner of cell A1. This should make the Fill Handle appear.

• Drag the fill handle down until the end of the range of cells that we want to paste the formatting. Alternatively, if we want to copy the formatting until the next first blank cell in the range, we can just double-click the fill handle.

• After doing so, you’ll notice that everything from cell A1 is copied. Everything from values to formatting. Don’t panic though. This is just the default function of the fill handle. The data from the other cells are still safe. What we need to do here is click the AutoFill Options icon that is next to the last cell with the selected range. Click it and choose the Fill Formatting Only option.

• Doing so will result in the following:

Only the formatting is copied. The values remain unchanged.

The advantage of the Fill Handle is that it’s easy and convenient to use.

However, it is useful for cells within the same sheet.

Also, it’s only useful for adjacent cells. We cannot use it for cells that are scattered along the sheet.

Closing Remarks

The above methods are basic ways of copying formatting from one cell to another in Excel.

All of the methods should result in the same outcome. It’s only a matter of preference which method you choose to use.