Copy Formatting in Excel
Formatting is the one thing in Excel one needs to master because the formatting is the key to preparing reports or making the data crystal clear. You probably might have seen amazing formatting styles with colors and other stuff. But, you know what, we can copy only formatting in Excel with simple methods. So, 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 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 color and number formatting. So if we copy cell B8 and paste it to the C8 cell, we will get the total of column C and formatting 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 and paste method. Rather, we need to employ a “Paste Special” method.
First, we should copy cell B8 and then choose the C8 cell.
Now, press the paste special shortcut key “ALT + E + S” to open the “Paste Special” window.
We have several paste special options, as we can see in the “Paste Special” window. So from this, choose the “Formats” option to paste only the formatting of the copied cell (B8 cell).
Note: Otherwise, you can also press the shortcut key “T” to choose the “Formats” option.
So now, the only format of cell B8 is copied and pasted to cell C8.
There you go, we have got only the formatting of cell B8 to 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 the painter’s brush used to paint. A similar concept has been integrated into Excel.
- 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 easily paint one cell’s formatting to another cell.
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 cell’s formatting, but in cell D1, we have the only number without formatting. So we will use “Format Painter” to copy the formatting from cell A1 to D1.
#3 – Copy Formatting Using Fill Handle
We can also use 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.” url=”https://www.wallstreetmojo.com/excel-fill-handle/”]Fill handle in excel[/wsm-tooltip] function to apply to the format. For example,
- First, we must place the cursor in cell A1 and click on “Format Painter.” As soon as we press the “Format Painter” option, we may 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 mouse click. If we 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 we need to press the option of “Format Painter.” However, instead of pushing “Format Painter” every time, we can make the “Format Painter” active 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 any number of times. Now, we must keep clicking on the cells we need to apply the formatting. Like this, we can copy formatting using format painter.
So all the formatting of cell A1 is copied to cell D1 with just a mouse click.
This time, we have several numbers in different cells, so we need to press the option of “Format Painter.” However, instead of pushing “Format Painter” every time, we can make the “Format Painter” active more than one time.
Like this, we can copy formatting using format painter.
Let us 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 use the same formatting. For this place, we need to place the cursor on the right corner of cell A1, and we may see the “PLUS” icon, which is “Fill Handle.”
Now, we need to use the left click of the mouse and drag it to the right side.
We have got the same value as in cell A1, but at the end of the selection, we can see a small icon, “Auto-Fill Options.”
Now, we may see ” Auto Fill Options, ” from which choose “Fill Formatting Only.”
Now, we need to return to 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 formatting.We can use “Format Painter” only once. If we want to use it more than once, we must press on “Format Painter” twice.
Recommended Articles
This article is a guide to Excel Copy Formatting. Here, we learn to copy formatting in Excel using Paste Special, format painter, fill handle, examples, and downloadable Excel templates. You may learn more about Excel from the following articles: –
- Conditional Formatting using VBAConditional Formatting in ExcelConditional Formatting for DatesFormat in Excel
