Copy Formatting in Excel
Formatting is the one thing in excel you need to master because the formatting is the key thing to prepare reports or make the data clear and crystal. You probably might have seen amazing formatting styles with colors and other stuff. You know what, we can copy only formatting in excel with simple methods. In this article, we will take you through the top 3 ways of copying the formatting of cells in excel.
#1 – Copy Formatting Using Paste Special
Below are the examples of copy formatting in excel:
We hope you are aware of the technique of Paste Special in excel, using this we can do amazing things.
- For example, look at the below image of excel.
- We have a formula in cell B8 and also it has some formatting of color as well as number formatting. Now if we copy the cell B8 and paste it to C8 cell we will get the total of column C along with formatting exactly the same as the B8 cell.
But imagine the scenario, we need not copy the formula but we need to copy only formatting of cell B8 to cell C8. So this is not possible with the regular copy & paste method rather we need to employ paste special method.
- First, copy the cell B8 and then choose the C8 cell.
- Now press the paste special shortcut key “ALT + E + S” to open below paste special window.
- As you can see in the paste special window we have several paste special options, so from this choose “Formats” option to paste only formatting of the copied cell (B8 Cell).
- So now the only format of the cell B8 is copied and pasted to cell C8.
- There you go we have got only formatting of the cell B8 to the cell C8.
- So, overall shortcut key to copy and paste only formatting is “ALT + E + S + T” to quickly copy and paste as formats.
#2 – Copy Formatting Using Format Painter
We hope you all know about painter brush which is used to paint, a similar concept has been integrated to excel as well.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
- Using “Format Painter in Excel” we can paint the formatting of one cell to another cell easily.
Using this we can copy the format of the cell to another cell and formats like “Number Formatting, Font Size, Font Type, Font Color, Cell Background Color, Font Styles, Alignments, and Cell Borders”.
- For an example look at the below cell formatting.
In cell A1 we have the number with the formatting of the cell but in cell D1 we have the only number which is without formatting. So we will use Format Painter to copy the formatting from cell A1 to D1.
- Place your cursor in cell A1 and click on “Format Painter”.
- As soon as you press the option of “Format Painter” you will see a brush option.
- Now click this brush on cell D1 to apply the formatting of cell A1 to D1.
So all the formatting of cell A1 is copied to cell D1 with just a click of the mouse.
- If you see the above format painter can be used only once, but imagine the below scenario.
This time we have several numbers in different cells, so every time we need to press the option of “Format Painter”, so instead of pressing every time we can make the Format Painter active for more than one time.
- So for this, we need to double click on Format Painter and after double-clicking on Format Painter we can use this for any number of times.
- Now keep clicking on the cells that you need to apply the formatting for.
Like this, we can copy formatting using format painter.
#3 – Copy Formatting Using Fill Handle
Fill handle in excel is also can be used to apply to format, for example,
- look at the below data.
- We have values in cells A1, B1, and C1. In cell A1 we have formatting applied for we need to apply the same formatting. For this place, a cursor on the right corner of the cell A1 and you will see PLUS icon which is “Fill Handle”.
- Use your left click of the mouse and drag it to the right side.
- Oh!!!! We have got the same value as in the cell A1 but wait, at the end of the selection we can see a small icon which is “Auto Fill Options”.
- Now you will see options of “Auto-Fill”, from this choose “Fill Formatting Only”.
- Now we get back our old values with the formatting of cell A1.
Like this, we can copy formatting using different methods in excel.
Things to Remember
- ALT + E + S + T is the shortcut to paste special of formatting.
- Format Painter can be used only once if you want to use it for more than one time then press on Format Painter twice.
This has been a guide to Excel Copy Formatting. Here we learn how to copy formatting in excel using paste special, format painter and fill handle along with examples and downloadable excel templates. You may learn more about excel from the following articles –