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 excelPaste Special In ExcelPaste special in Excel allows you to paste partial aspects of the data copied. There are several ways to paste special in Excel, including right-clicking on the target cell and selecting paste special, or using a shortcut such as CTRL+ALT+V or ALT+E+S.read more; 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 the formatting of cell B8 to cell C8. So this is not possible with the regular copy & paste method; rather, we need to employ a 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 the “Formats” option to paste only the 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 the formatting of the cell B8 to the cell C8.
- So, the 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.
- Using “Format Painter in ExcelFormat Painter In ExcelFormat painter in Excel is a tool used to copy the same format of a single cell or a group of cells to the other cells. You will find it on the home tab in the clipboard section.read more,” 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 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.
Follow the steps to copy cell formatting using format painter.
- 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, the 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 excelFill Handle In ExcelThe fill handle in Excel allows you to avoid copying and pasting each value into cells and instead use patterns to fill out the information. This tiny cross is a versatile tool in the Excel suite that can be used for data entry, data transformation, and many other applications.read more 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 the 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.
Recommended Articles
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 –
- Conditional Formatting using VBA
- Conditional Formatting in Excel
- Conditional Formatting for Dates
- Format in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
45.6K subscribers