How to Copy Cell Formatting in Microsoft Excel

Formatting your Excel worksheet is an important step in making your work presentable. Microsoft Excel offers you several ways to copy a cell’s format. For example, Format Painter is a direct way of copying the formatting of one cell and applying it to another. But there are other ways of copying formatting and this article will help you find the one that works for you and the type of your work.

1. Copy Formatting in Excel with Paste Special

When you want to copy cells in Microsoft Excel, you notice there are several paste options at your disposal. Paste Formatting allows you to copy only the formatting of the cell, but not its content.

Let’s see how it works in an example. You want to copy the formatting of cell A2 which has a red background and white font color, to cell B2 which has a different value in it without any formatting:

1. Select and right-click the single cell from which you want to copy the formatting. In our example that’s cell A2.
2. Select Copy from the menu, or press Ctrl + C on your keyboard to copy.

3. Select the cell to which you want to copy this formatting. In our case, that’s a B2 cell. Right-click the selected cell, and click the arrow next to the Paste Special option in the menu.
4. Select the icon for formatting. It’s the icon with a brush over the clipper.

Another way to do it is:

1. Select and copy the cell from which you want to paste the formatting. Then, select the cell (or a range of cells) to which you want to apply this formatting.
2. In the Home tab select Paste feature, and then select Paste Special from the drop-down menu.

3. A Paste Special dialog box will open. Select Format, and then click the OK button to apply the formatting on the selected cell.

This method will also work if you want to paste the formatting to multiple cells in the same worksheet or in other worksheets in a workbook. In that case, you’ll have to select the range of cells to which you want to apply the copied formatting.

2. Copy Formatting in Excel with Format Painter

Format Painter is the most helpful tool in Microsoft Excel if you want to copy cell formatting. It can help you reproduce all formatting settings such as Number format, Font size, face, and color, cell background color, text alignment, and more. Here’s how you can use this tool to copy cell formatting to other cells in your worksheet:

1. Select the cell from which you want to copy the formatting.
2. Go to the Home tab and find the Format Painter tool in the Clipboard group. Once you select it, your pointer will change to a paintbrush icon.

3. Click on the cell to which you want to apply formatting.

You can also use the Format Painter tool to apply copied cell formatting to a range of cells. All you need to do is select the sample cell, then select the Format Painter tool, and drag your cursor over the range of cells you want to apply the formatting.

If you double-click the Format Painter tool button in the Home tab, this will lock it to your cursor. You can use this feature to select and apply formatting to multiple non-adjacent cells.

If you want to copy the formatting of one column, to another column. You’ll select the header of the sample column, select the Format Painter tool, and then click the header of the target column. This will paste the formatting row-by-row.

3. Copy Cell Formatting with the Drag and Drop Menu

The Drag and Drop menu is a neat feature that’ll allow you to quickly move the formatting from one cell to another. Here’s how you can do it:

1. Select the sample cell from which you want to copy formatting.
2. Move your cursor to the right border of the selected cell. Your cursor should change into four arrows pointing in different directions.
3. Right-click your mouse and hold the button down. Move your mouse to the target cell where you want to paste the formatting and release the mouse button.
4. Once the menu opens, select Copy Here as Formats Only option.

4. Copy Formatting with Fill Handle

Fill handle is a useful Excel feature users can use to auto-fill cells or copy formulas. But it can also be used to copy the Excel formats. Here’s how:

1. Select the cell from which you want to copy the format.
2. Hover your cursor over the fill handle at the bottom-right corner of the selection. When you do this your cursor will change to a black cross.

3. Hold the handle and drag it over the cells where you want to apply the copied formatting.
4. Release the fill handle and select the Auto Fill Options drop-down menu.
5. Select Fill Formatting Only.

Note: If you can’t see the auto-fill drop-down arrow you’ll have to turn it on. Here’s how:

1. Go to the File tab and select Excel Options from the left panel menu.
2. Select Advanced in the left panel, and then check the box next to Enable the Fill Handle, in the Editing Options section.

There’s a chance you still can’t see the Fill Handle drop-down menu. If this is the case:

1. Go to the File tab and select Options at the bottom of the left panel.
2. Select Advanced, and then tick the box next to Show Paste Options Button when content pasted, in the Cut, Copy, and Paste section.

3. Click the OK button in the lower-left corner of the Excel Options window to confirm.

5. Excel Shortcuts to Copy Formatting and Apply

Unfortunately, there’s no single Excel keyboard shortcut that you can use to copy cell formats. The typical Ctrl + C and Ctrl + V are the commands to copy and paste the content.

You can use a sequence of shortcut keys that’ll make your formatting copying quicker. Here’s a list of useful cell formatting copy shortcuts:

1. To access Paste Special with keyboard shortcuts press Shift + F10 and then R while still holding the Shift button. Then hit the Enter button.

Shift + F10 displays the context menu.

Shift + R selects the Paste Only formatting option.

2. To quickly access the Excel Format Painter button press the following keyboard buttons one by one: Alt, H, F, P.

Alt gives you access to keyboard shortcuts for ribbon commands.

H selects the Home tab on the ribbon.

F and P select the Format Painter tool.

Mastering the art of copying cell formatting in Microsoft Excel is a valuable skill that can significantly streamline your workflow. Whether you’re working on intricate spreadsheets or simple data tables, these techniques will save you time and ensure a consistent and polished look across your Excel documents.