Fortunately, many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as per need and make databases for the future. Moreover, one interesting thing is that we can copy the formatting of cells easily in Excel. However, I have used Microsoft Office 365 for the purpose of demonstration, and you can use other versions according to your preferences. In this article, I will show you 3 suitable methods to copy formatting in Excel. Hence, read through the article to learn more and save time.
How to Copy Formatting in Excel: 3 Suitable Ways
In this tutorial, I will show you how to copy formatting and the process becomes more interesting with Excel. However, the task is easy and simple. But you will need an arrangement in order to perform the operation properly. Here, I have used 3 different methods to make you understand the scenario properly. For the purpose of demonstration, I have used the following sample dataset. Here, the dataset contains the Customer Name, Due Amount, and Due Date. However, the due amount and due date are highlighted here so that it helps to collect the dues on time.
1. Copy Formatting with Format Painter
First of all, to copy formatting from one cell to another we can use Format Painter in Microsoft Excel for multiple cells. Moreover, it works to copy format from one or multiple cells, even columns, and paste it into the other one. However, it’s an amazingly efficient and simple process to format the cells.
1.1 Only One Cell
For instance, I will copy the formatting of only one cell. Let’s not waste more time. So, follow the steps mentioned below. Here, I have copied the data for David to apply Format Painter.
📌 Steps:
- Initially, select cell C5.
- Then, go to the Home tab and click on Format Painter from the Clipboard section.
- Next, you will find a brush symbol along with your cursor.
- After that, select the cell in which you want to copy the format. Here, I have selected C12.
- Finally, you will get your desired output.
1.2 Multiple Cells
In this part, I will show how to copy the formatting of multiple cells in Excel. Nevertheless, for lengthy datasets, you can also copy the formatting of multiple cells using Format Painter. Additionally, the process is straightforward and easy to understand. In this part, I will use keyboard shortcuts to activate the Format Painter. Hence, go through the steps below in order to complete the operation.
📌 Steps:
- First of all, select the cell you want to copy. Here, I have selected cell C5.
- Thereafter, press ALT + H + F + P and you will notice a brush symbol along with your cursor.
- Now, select the dataset where you want to paste formatting.
- At last, the output will appear as below.
2. Utilize Fill Handle Tool to Copy Formatting in Excel
Furthermore, you can use Format Painter to format a single cell and then use the Fill Handle tool to format the rest of the cells in the column of the dataset. However, the process is quite easy and time-saving. Hence, read through the steps below to complete the operation properly.
📌 Steps:
- In the beginning, copy the formatting into one cell using the Format Painter. Here, I have selected cell D5 to copy and paste into cell C12.
- Afterward, use the Fill Handle tool for the entire column.
- Now, you will notice an option symbol at the end of the auto-filled cell.
- Finally, click on it and select Fill Formatting Only.
- Thus, you’ll get the desired outcome.
3. Use Paste Special Feature to Copy Format
Last but not least, you can copy formatting along with values and many more options using the Paste Special option. Moreover, it is a common tool and we use it regularly in Excel. Now, let’s have a look at various processes of using Paste Special. For the purpose of demonstration, I have mentioned two different scenarios below.
3.1 Within Same Sheet
In general, you can use Paste Special feature to copy format within the same sheet. So, go through the steps mentioned below. For the purpose of demonstration, I have changed the dataset a bit.
📌 Steps:
- Firstly, select the complete or partial dataset.
- Secondly, press Ctrl + C to copy the data.
- Thirdly, select the first cell where you want to copy the data with the format.
- Fourthly, right-click on it and simply select Formatting from the Paste Options.
- Lastly, the final result will appear as the image mentioned below.
3.2 Copy Formatting to New Sheet
Additionally, you can utilize a similar process to copy the formatting to a new sheet in Excel. However, the process is simple and easy to operate. Hence, read through the steps below in order to complete the task properly. For the purpose of demonstration, I have taken a new sheet named Paste Special New Sheet 2 and inserted some data as below.
📌 Steps:
- Initially, select the complete or partial dataset from Paste Special New Sheet 1.
- Then, press Ctrl + C to copy the data similarly.
- Next, go to the new sheet and select cell B4 to paste it by selecting Formatting from the Paste Option.
- Finally, you will receive your desired output.
Things to Remember
- Firstly, the Format Painter can only copy formats and not values.
- Secondly, in order to copy multiple cell formatting, you need to click on Format Painter twice and paste wherever you need. In the end, press Esc to deselect Format Painter.
- Thirdly, the Fill Handle will paste with the same values. Thus, to show the previous values you must change AutoFill to Fill Formatting Only.
- Finally, Paste Special has a variety of pasting options and you can use anyone you need.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
Conclusion
These are all the steps you can follow to copy formatting in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
Related Articles
- Formula to Copy and Paste Values in Excel
- How to Copy and Paste in Excel without Formulas
- How to Copy and Paste Multiple Cells in Excel
- How to Copy and Paste Without Changing the Format in Excel
- How to Copy Only Highlighted Cells in Excel
- How to Copy Horizontal and Paste Vertical in Excel
- How to Copy Paste Vertical to Horizontal in Excel
- Formula to Copy and Paste Values in Excel Automatically
- How to Copy and Paste in Excel and Keep Cell Size
- Copy Rows from One Sheet to Another Based on Criteria in Excel
- How to Copy and Paste a Column in Excel
- How to Copy Merged and Filtered Cells in Excel
- How to Copy and Paste in Excel with Merged Cells
- How to Copy and Paste in Excel When Filter Is On
- How to Disable Copy and Paste in Excel without Macros
- How to Copy Rows in Excel
- How to Copy and Paste Thousands of Rows in Excel
- How to Copy Alternate Rows in Excel
- How to Copy Rows in Excel with Filter
- How to Copy Excluding Hidden Rows in Excel
- How to Copy Rows Automatically in Excel to Another Sheet
- Excel VBA to Copy Cell Background Color
- [Solved] Excel Copy Paste Loses Formatting
- Formula to Copy Cell Value and Format in Excel
- How to Copy Formatting in Excel to Another Sheet
- How to Copy and Paste Formulas Without Changing Cell References in Excel
- [Fixed!] Cannot Copy Merged Cells in Excel
- [Fixed]: Right Click Copy and Paste Not Working in Excel
- Copy and Paste is Not Working in Excel
- [Fixed!] Paste Link Not Working in Excel
- [Fixed!] Copy and Paste Not Working Between Workbooks in Excel
- How to Copy Number Not Formula in Excel
- How to Fill Across Worksheets in Excel
- [Fixed!]: Microsoft Excel Cannot Paste the Data as Picture
- [Fixed!] CTRL C Not Working in Excel