To copy formatting from one cell or a range of multiple cells, you need to select it first and then copy it. After that, select the cell or range where you want to paste the formatting. Here you can use the paste special option to only paste the formatting and leave the rest.
In this tutorial, we will learn different ways to copy and paste only formatting from a cell or a range of cells in Excel.
Copy Cell Formatting in Excel using Paste Special
- First, select the cell or the range and press Ctrl + C to copy the cell.
- After that, select the cells where you want to paste the formatting.
- Now, go to the home tab and open the paste option from there, or use the keyboard shortcut Alt + H + S to open the paste special.
- Next, select the format and click OK to paste the formats only.
And once you click OK, you get the formatting to the selected cell. This way, you can quickly copy and paste the formatting from a cell or a range of cells.
Copy Cell Formatting using Format Painter
Below, you have cell A1 with a value and accounting number format. And now you need to get the same formatting for cell B1.
- Select cell A1.
- Go to the home tab and click on the format painter button.
- This will convert the mouse cursor into a paintbrush.
- Selecting no other cell or option, click on cell B1. It will instantly apply cell formatting to cell B1.
Keyboard Shortcut to Copy Formatting and Apply
You can also use a keyboard shortcut to copy and paste formatting.
- Select the cell and press the shortcut key Ctrl + C.
- Select the cell where you want to apply the formatting and press the key Alt ⇢ H ⇢ V ⇢ R.
Use Drag and Drop Menu to Copy Cell Format
- Select cell A1 or where you have the formatting.
- Hover your mouse on the border of the cell and press the right-click button from the mouse and hold the button.
- Drop it to the cell where you want to paste the formatting.
- Release the button to get the menu.
- Select the option “Copy Here as Formats Only”.
Copy Formatting with Fill Handle
If you have formatting on a cell and you want to copy and paste that formatting to the cells downwards, you can use the fill handle for that.
- Select cell A1, and hover your mouse on the bottom right corner of the cell.
- Hold the mouse left button and drag it down to cell A2.
- You’ll get the AutoFill options at the bottom of the cell.
- Click on the drop-down and select “Fill Formatting Only”.
If you can’t see the auto-fill drop-down, go to the File Tab ⇢ Excel Options ⇢ Advanced ⇢ Editing Options ⇢ Enable Fill Handle. And, if you still can’t see it, go to the File Tab ⇢ Excel Options ⇢ Cut, Copy, and Paste ⇢ Show Paste Options Button when content is pasted.