How to Copy Cell Formatting in Excel Tables

Excel allows you to customize cell formats so that their content is displayed as desired by the user. Moreover, formats can be not only created but also copied or modified. Copying cell formats significantly simplifies the process of formatting data representation in tables.

To copy the cell format, there are two ways to perform this operation: 1 Format Painter and 2 Paste Special.

 

Order of Copying Formats

Format change.

Let's assume that we have a table like this:

Table example.

In the "Amount" column, which is formatted in a special way, a formula is entered.

We want to format the "Product" column similarly.

For this:

1st method: Format Painter

Select any cell from the "Amount" column, click the "Format Painter" button on the "Home" menu tab:

Format Painter.

Then, select with the left mouse button the cells from the "Product" column that you want to format, and release the mouse button.

We get:

Format change.

We see that the values ​​with product names have been preserved in the cells.

2nd method: Paste Special

Paste Special is a very useful tool that provides the following possibilities:

  • copy the formula and paste the values;
  • while preserving the value in the cell, change its format to match another cell;
  • while preserving the cell format, insert a formula from another cell;
  • copy and add a ready-made note from another cell;
  • copy the width of another column;
  • massively multiply, add, divide, or subtract a specified value;
  • vertically flip horizontally positioned data and vice versa.

In addition, the function allows you to combine the above capabilities.

So, to copy the format, select any cell from the "Amount" column, and without removing the cursor from the selected area, right-click to open the menu, choose "Copy":

Copy option.

Then select the desired range of cells in which you want to change the format, right-click in the selected area, choose "Paste Special":

Paste Special option.

In the appeared "Paste Special" dialog box, check the "Formats" option:

Formats option.

and click OK.

We get:

Format change.

download file Download an example of how to copy cell formats in Excel tables

The result is the same for different formatting methods.


en ru