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
Let's assume that we have a table like this:
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:
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:
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":
Then select the desired range of cells in which you want to change the format, right-click in the selected area, choose "Paste Special":
In the appeared "Paste Special" dialog box, check the "Formats" option:
and click OK.
We get:
Download an example of how to copy cell formats in Excel tables
The result is the same for different formatting methods.