Writing on a computer or laptop is of course easier and more timesaving than writing by hand. However, sometimes we need to write the same sort of things repetitively. In that case, the overused and life-saving option on a computer or laptop is “Copy”. The article will explain the processes available for how to copy formatting in Excel.
3 Processes of How to Copy Formatting in Excel
Dataset is the set of various types of data to keep records. Creating a new dataset for a different purpose many times requires copying the data from the previous dataset. Copying is required to keep the formatting the same along with the values and other stuff.
Here, let us consider a company that has a dataset of their customer details with a specific format. The dataset contains customer name, contact number, total bill, already paid amount, due amount, and due date. The due amount and due date are highlighted here so that it helps to collect the dues on time.
You can download the dataset from here.
Now, the company wants to have a different table for the billing section which will contain the total bill, paid amount, and due amount along with the same formatting. Let’s find out the different processes of how the company can copy the formatting from the customer details dataset to the billing section one.
1.Copy Formatting Using the Format Painter
Format Painter works to copy format from one or multiple cells, even columns, and paste it in the other one.
You can find the Format Painter in the Home ribbon.
a. Copy Formatting of Only One Cell
For this process, you can follow the following points:
- You should have your data ready to where you want to copy the format to. Let’s say the company has created the billing section dataset.
- Now, they want to format it according to the format of the dataset named Customer details. They start with Total Bit formatting.
- Select Total Bill in the Customer Details dataset.
- Then, click on Format Painter in the Home ribbon.
- You will find a brush symbol along with your cursor.
- Right-click on the Total Bill in the Billing section and you will find the below result:
- For copying the formatting for the whole dataset of the Billing section you must repeat the above steps and the result will look like this:
b. Copy Formatting of Multiple Cells
Process (a) is slower since you need to repeat the steps for each cell you have, and this will not only consume time but also wastes energy and feels monotonous. Nevertheless, for lengthy datasets, you can also copy the formatting of multiple cells using Format Painter. For this you can follow the steps of process (a), just the difference is here you must select multiple cells and click on the Format Painter twice.
This will provide the same result as method (a), but the process will be faster and easier.
You can use the keyboard shortcut of the Format Painter to copy formatting by following ways:
- Select the cell you want to copy.
- Next, press ALT+H+F+P. This will select the Format Painter from the Home ribbon and you will notice a brush symbol along with your cursor.
- Now, right-click on the dataset where you want to paste formatting. The result will be the same as process (a) if you use the billing section dataset.
c. Copy Format of a Column
You can also copy the formatting of a column to a new column. To do so you can follow the below steps:
- Ready your dataset to format.
- Select the column title you want to copy.
- Click on the Format Painter from the Home ribbon. You will notice a brush along with the cursor.
- Take the cursor to the new column title and right-click on it. You will find the desired result.
2.Copy Formatting Using Fill Handle
You can use Format Painter to format a single cell and then use a fill handle to format the rest of the cells in the column of the dataset. Follow the below steps:
- Ready your dataset which needs formatting. Here, we shall also use the billing section.
- Use Format Painter copy formatting and format the first cell of the new dataset.
- Then select the first cell and take the cursor to the left side of the selected cell. A plus (+) sign will appear. Drag it down the column. The result will show as below:
- Due to Autofill, the same data is showing all through the column. You will notice an option symbol at the end of the auto-filled cell.
- Click on it and select Fill Formatting Only.
- Click on it and it will paste only the format keeping values intact. Repeating the process for other columns will provide the desired result as follows:
Read More: How to Copy Cell Format in Excel (4 Methods)
- Dealing with Time Format in Excel (5 Suitable Ways)
- Format Text Using Excel VBA (12 Methods)
- How to Use Excel Cell Format Formula (4 Effective Methods)
- How to Use Format Painter in Excel for Multiple Cells (6 Quick Ways)
3.Copy Formatting Using Paste Special
You can copy formatting along with values and many more options using the Paste Special option. Let’s have a look at various processes of using Paste Special.
a. Copy Formatting within the Same Sheet
Follow the steps below:
- You can select the cells you want to copy
- Select Copy from the menu bar which pops up with right clicking on the selected cells.
You will notice a dotted boundary around with the selected cells.
- Click on the cell where you want to paste the copied portion. Right-click on it and a menu bar will pop up.
Here you can see so many options under the Paste Special choice, and you can choose your desired one.
I have selected the Paste option which is shown below:
This will provide the following result:
The above method can be done using keyboard shortcuts by following the steps below.
- You can use a keyboard shortcut to select the Copy and Paste Special options.
- To copy the cells press CTRL+C.
- For pasting press Shift+F10, S, P if you are using Microsoft 365. Besides, for Microsoft Office version 2016, 2013, or 2010 press Shift+F10, S, R, and then press Enter. Again, for version 2007 you must press Shift+F10, S, T, and then Enter.
b. Copy Formatting to New Sheet
You can copy the formatting from a sheet to a new sheet using Paste Special.
For this, you can follow the steps of (a). The only difference is that you must select move to the new sheet where you want to paste and select a cell from where you want to start pasting.
Here we select the dataset of Customer details and copy it to the new sheet using Paste Special.
Things to Remember
In all cases, you must remember the following things while using the processes demonstrated above:
- Format Painter can only copy formats and not values.
- For copying multiple cell’s formatting, you need to click on Format Painter twice and paste wherever you need. In the end, press Esc to deselect Format Painter.
- The Fill Handle will paste Autofill with the same values. Thus, to show the previous values you must change Autofill to Fill Formatting Only.
- Paste Special has a variety of pasting options and you can use anyone you need.
The article evaluates 3 different processes and sub-processes of 2 of them about how to copy formatting in Excel. Use the method that suits your need and must follow the steps to get the required result.