Copy formatting in Excel defines the process of replicating the visual appearance of a cell or a range of cells to another cell or range. It includes font size, style, color, borders, number formatting, etc. Copying formatting is highly used to maintain a consistent look throughout the worksheet for cells with the same attributes.
In this Excel tutorial, you will learn 3 simple ways to copy formatting. You can use the Format Painter, Fill Handle, or Paste Special feature in a single cell or a range of cells to copy formatting.
In the following dataset, I have arranged the sold mobiles list in the ‘Order ID’, ‘Customer Name’, ‘Device’, and ‘Price’ columns. I have copied the formatting of the first cell to the rest of the cells in the ‘Price’ column.
Here are the 3 ways to copy formatting in Excel:
Using Format Painter
The Format Painter can only copy formats and not values. Just keep in mind that you have to double-click on Format Painter to copy single-cell formatting into a range of cells.
Here are the steps to copy cell formatting to another cell with the Format Painter feature:
- Select a cell with the proper formatting.
- Go to the Home tab > Clipboard group > Format Painter.
Or, you can press ALT + H + F + P to open the Format Painter option.
You will see a brush symbol along with your cursor. - Select the cell where you want to copy the formatting.
Finally, you have copied the formatting to the selected cell.
Similarly, to copy cell formatting to a range of cells:
- Select a formatted cell.
- Double-click on Format Painter.
- Select a range of cells for pasting the copied cell format.
Thus, you will have the copied formatting in multiple cells.
Read More: Copy and Paste Without Changing the Format in Excel
Using Fill Handle Tool
You can copy the formatting of a cell to another cell or a range of adjacent cells with the Fill Handle tool. The process is quite easy and time-saving.
Follow the steps to copy formatting with the Fill Handle:
- Select a cell with proper formatting.
- Drag down the Fill Handle to the adjacent cells.
- Click on the Auto Fill Options icon > Fill Formatting Only.
Thus, you’ll have the formatting copied to the adjacent cells.
Read More: Copy and Paste Formulas Without Changing Cell References
Using Paste Special Feature
The Paste Special feature offers a variety of options such as pasting only values, formulas, or formats of the copied data. You can also copy the formatting to a new worksheet with the Paste Special feature.
Here are the steps to copy formatting in Excel with the Paste Special feature:
- Select a range of cells with proper formatting.
- Press Ctrl + C to copy the data.
- Select a cell where you want to paste the formatting.
- Right-click on the mouse.
- Go to Paste Special… > Other Paste Options > Formatting.
Finally, you will have the copied formatting.
Note: You can also press Alt + E + S + T + Enter to paste only the copied formatting.
Download Practice Workbook
You can download the workbook and practice yourself.
Conclusion
In this Excel tutorial, you have learned to copy formatting with the Format Painter, Fill Handle, or Paste Special feature for a single cell or a range of cells. Hopefully, the article is helpful for you. Please let us know in the comments section below if you have any queries or recommendations.
Frequently Asked Questions
Does copying the formatting in Excel affect data or formulas in cells?
No, copying the formatting in Excel only changes the visual appearance of the applied cells. It does not affect the data or formulas in the cells.
How to copy formatting to non-contiguous cells?
While selecting the cells to change the formatting according to the copied cell, hold down the Ctrl key and use the Format Painter or Paste Special feature.
Is it possible to copy formatting between different Excel workbooks?
Yes, it is possible to copy formatting between different Excel workbooks. Just copy the cell formatting and use the Format Painter or Paste Special feature to paste it into another Excel workbook.
Related Articles
<< Go Back to Copy Paste in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!