When working with data in Excel, you will often format data (such as color the cells or make them bold or give a border), to make these stand out.
And if you have to do this for many cells or range of cells, instead of doing it manually, you can do it once and then copy and paste the formatting.
In this tutorial, I will show you how to copy formatting in Excel. You can easily do it by using the Format painter option, using the Fill handle, or Paste special.
Copy the Formatting to a Single Cell
We’ll first see how to copy a formatting to a single cell in Excel. Let’s say that you have cell A2 formatted as an accounting number, with red background and white font color.
In Cell C2 we have the plain number without any format.
- Select a formatted cell that has the formatting that you want to copy (A2 in our example)
- Click on Format Painter in the Home tab. This will change the cursor into a paintbrush with a plus icon
- Click on a cell where you want to copy a format (C2)
Whenever you select a cell and choose Format Painter in the toolbar, the mouse cursor turns into a white cross with a brush.
This is how you know that the formatting is copied to the clipboard and you can paste it where you want.
Just the way we copied the formatting from one to another in the same sheet. you can also copy formatting to another sheet or another workbook. Simply select the cell from where you want to copy the formatting, enable format painter, select the sheet/workbook where you want to paste it, and select the cells in the destination sheet.
With Format Painter, you can easily copy the following formatting:
- Cell background-color
- Font size and color
- Font (including number format)
- Font characteristics (bold, italics, underline)
- Text alignment and orientation
- Cell borders (type, size, color)
- Custom Number Formatting
- Conditional Formatting
Personally. I find it a huge time saver to copy conditional formatting from one cell to another in the same sheet or other sheets. Excel is smart enough to adjust the rules in conditional formatting in case you’re using custom formulas.
Copy the Formatting to a Range of Cells
Just like you can copy the formatting from one cell to another cell, you can also copy it to a range of cells.
In this case, you need to select a range of cells on which you want to apply the format painter.
Suppose you have a dataset as shown below where you want to copy the formatting from cell A2 to the range of cells in C2:C7
- Select a formatted cell (A2)
- Click on Format Painter in the Home tab
- Select a range of cells where you want to copy a format (C2:C7)
As a result, the format from A2 is copied to the selected range.
PRO TIP: When you click on the Format Paint icon, it allows you to format a cell or range of cells only once. Once you’re done, it’s disabled. So if you want to copy formatting to two ranges of cells, you will have to enable format painter twice. Alternatively, when you double-click on the Format Paint icon, it remains enabled and you can copy formatting to multiple cells or ranges.
Copy the Formatting Using Paste Special
When you copy and paste cells in Excel, you noticed that there are usually multiple paste options, such as: Paste text, Paste values, etc.
One of these options is Paste formatting.
This allows you to copy only the formatting from cells to cells.
- Select and right-click a cell from which you want to copy the formatting (A2)
- Click Copy (or use the keyboard shortcut CTRL+C).
- Select a range of cells to which you want to copy the formatting (C2:C7);
- Right-click anywhere in the selected range;
- Click the arrow next to Paste Special;
- Choose the icon for formatting.
The result is the same as using the Format painter.
You can also notice that all formatting that you copied by Format painter is also copied using the Paste special option.
Just like format painter, you can also use the Paste Special technique to paste formatting on the cells or range of cells in the same sheet or other sheet/workbook.
Pro Tip: If you have to copy the formatting from a cell to multiple cells that are scattered through the worksheet, you can use the paste special technique to copy formatting to one cell, and then repeat the process by using the F4 key. So copy the formatting once, then select another cell and press F4, and it will repeat your last action (which was to paste the formatting).
Copy the Formatting Using the Fill Handle
As you probably already know, the fill handle is the little black cross that appears when you position a cursor in the right bottom corner of the cell (as shown below).
This cursor allows you to copy a cell (or range of cells) down the rows.
Apart from copying the cell values, the fill handle also allows you to copy the formatting.
Let’s say that you have a list of numbers in column A, where the first value in the list (A2) is formatted, while other values (A3:A7) are not formatted at all.
What I want is to copy the formatting from cell A2 to all the cells below it.
Below are the steps to do this:
- Position the cursor in the right bottom corner of a cell from which you want to copy the formatting (A2) until the black cross (fill handle) appears;
- Drag the fill handle down to the end of the range which we want to format (A7). If you want to copy the cell to the end of the range (until the first blank cell in the range), just double-click the fill handle.
- When you drop the cursor, by default both value and formatting will be copied to the range. Now, you need to click on the AutoFill Options icon next to the end of the range and choose Fill Formatting Only.
Now, you can see that values are not changed, while the formatting is copied to the whole range.
While I have shown how to use the Fill Handle to copy formatting for one column only, you can use it the same way for the data in a row of data that spans across multiple rows and columns.
One drawback of using the fill handle is that your data needs to be in the same column or row where you have the cell from which you are copying the formatting. This also means that you cannot use this method to copy the formatting to cells or range of cells that are in another sheet or workbook.
So these are some of the methods you can use to copy formatting from one cell to another cell or range of cells in Excel.
I hope you found this tutorial useful!
Other Excel tutorials you may also like:
- Using Conditional Formatting with OR Criteria in Excel
- How to Format Phone Numbers in Excel
- How to Highlight Every Other Row in Excel (Conditional Formatting & VBA)
- How to Copy Multiple Sheets to a New Workbook in Excel
- How to Create Barcodes in Excel
- How to Autofill Dates in Excel (Autofill Months/Years)
- How to Bold Text using VBA in Excel