How to Copy Formatting in Excel (3 Processes)

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.

Sample Dataset to explain how to copy formatting in excel

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.

Finding format painter from home tab

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.

Created new table for apply copied formatting

  • 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:

Copied formatting to total bill in the billing section

  • 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:

Copied formatting from the customer section to the billing section

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.

Note:

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.
ALT+H+F+P

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.

Created new column where formatting will be copied

  • Select the column title you want to copy.

Copying Due Date column

  • 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.

New Column formatted from Copied column

Read More: How to Use Format Painter Shortcut in Excel (5 Ways)

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:

Using Fill handle to drag the value in the column

  • 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.

Selecting 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:

Result of formatting in the using fill handle

Read More: How to Copy Cell Format in Excel (4 Methods)


Similar Readings


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

Selecting columns to copy formatting

  •  Select Copy from the menu bar which pops up with right clicking on the selected cells.

Selecting copy by right clicking on the selected portion of the dataset

You will notice a dotted boundary around with the selected cells.

Dotted lines around the selected portion of the dataset

  • Click on the cell where you want to paste the copied portion. Right-click on it and a menu bar will pop up.

Different available Paste Special options

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:

Selecting paste from paste special option

This will provide the following result:

Result of using paste special

Note:

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.
CTRL+C
Shift+F10+S, P

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.

Selecting whole dataset to copy to new sheet

Final result in new sheet

Read More: How to Custom Format Cells in Excel (17 Examples)

Things to Remember

In all cases, you must remember the following things while using the processes demonstrated above:

  1. Format Painter can only copy formats and not values.
  2. 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.
  3. The Fill Handle will paste Autofill with the same values. Thus, to show the previous values you must change Autofill to Fill Formatting Only.
  4. Paste Special has a variety of pasting options and you can use anyone you need.

Conclusion

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.


Related Articles

Syeda Fahima Nazreen

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo