How to Copy Formatting In Excel?

by

When working with data in Excel, you will often format data (such as color the cells or make them bold or give a border), to make these stand out.

And if you have to do this for many cells or range of cells, instead of doing it manually, you can do it once and then copy and paste the formatting.

In this tutorial, I will show you how to copy formatting in Excel. You can easily do it by using the Format painter option, using the Fill handle, or Paste special.

Copy the Formatting to a Single Cell

We’ll first see how to copy a formatting to a single cell in Excel. Let’s say that you have cell A2 formatted as an accounting number, with red background and white font color. 

In Cell C2 we have the plain number without any format.

excel copy the formatting of a cell initial data
  1. Select a formatted cell that has the formatting that you want to copy (A2 in our example)
  2. Click on Format Painter in the Home tab. This will change the cursor into a paintbrush with a plus icon
Excel format painter
  1. Click on a cell where you want to copy a format (C2)
formatting has been copied from one cell to another

Whenever you select a cell and choose Format Painter in the toolbar, the mouse cursor turns into a white cross with a brush.

This is how you know that the formatting is copied to the clipboard and you can paste it where you want.

Just the way we copied the formatting from one to another in the same sheet. you can also copy formatting to another sheet or another workbook. Simply select the cell from where you want to copy the formatting, enable format painter, select the sheet/workbook where you want to paste it, and select the cells in the destination sheet.

With Format Painter, you can easily copy the following formatting:

  • Cell background-color
  • Font size and color
  • Font (including number format)
  • Font characteristics (bold, italics, underline)
  • Text alignment and orientation
  • Cell borders (type, size, color)
  • Custom Number Formatting
  • Conditional Formatting

Personally, I find it a huge time saver to copy conditional formatting from one cell to another in the same sheet or other sheets.

Excel is smart enough to adjust the rules in conditional formatting in case you’re using custom formulas.

Also read: How to Make all Cells the Same Size in Excel

Copy the Formatting to a Range of Cells

Just like you can copy the formatting from one cell to another cell, you can also copy it to a range of cells.

In this case, you need to select a range of cells on which you want to apply the format painter.

Suppose you have a dataset as shown below where you want to copy the formatting from cell A2 to the range of cells in C2:C7

Data to copy formatting from cell to range
  1. Select a formatted cell (A2)
  2. Click on Format Painter in the Home tab
  3. Select a range of cells where you want to copy a format (C2:C7)
Apply format painter on the entire range of cells

As a result, the format from A2 is copied to the selected range.

resulting dataset where range of cells have the copied formatting

PRO TIP: When you click on the Format Paint icon, it allows you to format a cell or range of cells only once. Once you’re done, it’s disabled. So if you want to copy formatting to two ranges of cells, you will have to enable format painter twice. Alternatively, when you double-click on the Format Paint icon, it remains enabled and you can copy formatting to multiple cells or ranges.

Also read: Excel Fill Color Shortcut

Copy the Formatting Using Paste Special

When you copy and paste cells in Excel, you noticed that there are usually multiple paste options, such as: Paste text, Paste values, etc. 

One of these options is Paste formatting. 

This allows you to copy only the formatting from cells to cells.

  1. Select and right-click a cell from which you want to copy the formatting (A2)
  2. Click Copy (or use the keyboard shortcut CTRL+C).
Right-click and copy formatting from the cell
  1. Select a range of cells to which you want to copy the formatting (C2:C7);
  2. Right-click anywhere in the selected range;
  3. Click the arrow next to Paste Special;
  4. Choose the icon for formatting.
Paste formatting only

The result is the same as using the Format painter.

You can also notice that all formatting that you copied by Format painter is also copied using the Paste special option.

Just like format painter, you can also use the Paste Special technique to paste formatting on the cells or range of cells in the same sheet or other sheet/workbook.

Pro Tip: If you have to copy the formatting from a cell to multiple cells that are scattered through the worksheet, you can use the paste special technique to copy formatting to one cell, and then repeat the process by using the F4 key. So copy the formatting once, then select another cell and press F4, and it will repeat your last action (which was to paste the formatting).

Also read: How to Copy and Paste in Excel Without Changing the Format?

Copy the Formatting Using the Fill Handle

As you probably already know, the fill handle is the little black cross that appears when you position a cursor in the right bottom corner of the cell (as shown below).

Fill Handle in selection

This cursor allows you to copy a cell (or range of cells) down the rows.

Apart from copying the cell values, the fill handle also allows you to copy the formatting.

Let’s say that you have a list of numbers in column A, where the first value in the list (A2) is formatted, while other values (A3:A7) are not formatted at all.

Dataset where only the first cell is formatted

What I want is to copy the formatting from cell A2 to all the cells below it.

Below are the steps to do this:

  1. Position the cursor in the right bottom corner of a cell from which you want to copy the formatting (A2) until the black cross (fill handle) appears;
Select the cell from which you want to copy the formatting
  1. Drag the fill handle down to the end of the range which we want to format (A7). If you want to copy the cell to the end of the range (until the first blank cell in the range), just double-click the fill handle.
Apply formatting to all cells below using fill handle
  1. When you drop the cursor, by default both value and formatting will be copied to the range. Now, you need to click on the AutoFill Options icon next to the end of the range and choose Fill Formatting Only.
Select fill formatting only option

Now, you can see that values are not changed, while the formatting is copied to the whole range.

Resulting data where all cells are now formatted

While I have shown how to use the Fill Handle to copy formatting for one column only, you can use it the same way for the data in a row of data that spans across multiple rows and columns.

One drawback of using the fill handle is that your data needs to be in the same column or row where you have the cell from which you are copying the formatting.  This also means that you cannot use this method to copy the formatting to cells or range of cells that are in another sheet or workbook.

So these are some of the methods you can use to copy formatting from one cell to another cell or range of cells in Excel.

I hope you found this tutorial useful!

Other Excel tutorials you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment