Excel Copy Formatting

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

Copy Formatting in Excel

Formatting is the one thing in Excel one needs to master because the formatting is the key to preparing reports or making the data crystal clear. You probably might have seen amazing formatting styles with colors and other stuff. But, you know what, we can copy only formatting in Excel with simple methods. So, in this article, we will take you through the top 3 ways of copying the formatting of cells in Excel.

Table of contents

#1 - Copy Formatting Using Paste Special

Below are examples of copy formatting in Excel.

We hope you are aware of the technique of Paste Special in excel; Using this, we can do amazing things.

  • For example, look at the below image of Excel.
Copy Formatting Example 1
  • We have a formula in cell B8, and also it has some formatting color and number formatting. So if we copy cell B8 and paste it to the C8 cell, we will get the total of column C and formatting the same as the B8 cell.
Copy Formatting Example 1-1

But imagine the scenario, we need not copy the formula, but we need to copy only the formatting of cell B8 to cell C8. So, this is not possible with the regular copy and paste method. Rather, we need to employ a "Paste Special" method.

  • First, we should copy cell B8 and then choose the C8 cell.
Copy Formatting Example 1-2
  • Now, press the paste special shortcut key “ALT + E + S” to open the "Paste Special" window.
Copy Formatting Example 1-3
  • We have several paste special options, as we can see in the "Paste Special" window. So from this, choose the “Formats” option to paste only the formatting of the copied cell (B8 cell).
Copy Formatting Example 1-4

Note: Otherwise, you can also press the shortcut key â€śT” to choose the “Formats” option.

  • So now, the only format of cell B8 is copied and pasted to cell C8.
Copy Formatting Example 1-5
  • There you go, we have got only the formatting of cell B8 to cell C8.
  • So, the overall shortcut key to copy and paste only formatting is "ALT + E + S + T" to quickly copy and paste as formats.

#2 - Copy Formatting Using Format Painter

We hope you all know about the painter's brush used to paint. A similar concept has been integrated into Excel.

  • Using “Format Painter in Excel,” we can easily paint one cell's formatting to another cell.
Using Format Painter 1

Using this, we can copy the format of the cell to another cell and formats like "Number Formatting," "Font Size," "Font Type," "Font Color," "Cell Background Color," "Font Styles," "Alignments," and "Cell Borders."

  • For example, look at the below cell formatting.
Using Format Painter 1-1

In cell A1 we have the number with the cell's formatting, but in cell D1, we have the only number without formatting. So we will use "Format Painter" to copy the formatting from cell A1 to D1.

We must follow the steps to copy cell formatting using format painter.

  1. First, we must place the cursor in cell A1 and click on "Format Painter."


    Using Format Painter 1-2

  2. As soon as we press the “Format Painter” option, we may see a brush option.


    Using Format Painter 1-3

  3. Now, click this brush on cell D1 to apply the formatting of cell A1 to D1.


    Using Format Painter 1-4
    So all the formatting of cell A1 is copied to cell D1 with just a mouse click.

  4. If we see the above format, the painter can be used only once, but imagine the below scenario.


    Using Format Painter 1-5
    This time, we have several numbers in different cells, so we need to press the option of "Format Painter." However, instead of pushing "Format Painter" every time, we can make the "Format Painter" active more than one time.

  5. So for this, we need to double-click on "Format Painter," and after double-clicking on "Format Painter," we can use this any number of times.


    Using Format Painter 1-6

  6. Now, we must keep clicking on the cells we need to apply the formatting.


    Using Format Painter 1-7
    Like this, we can copy formatting using format painter.

#3 - Copy Formatting Using Fill Handle

We can also use the information. This tiny cross is a versatile tool in the Excel suite that can be used for data entry, data transformation, and many other applications." url="https://www.wallstreetmojo.com/excel-fill-handle/"]Fill handle in excel function to apply to the format. For example, 

  • Let us look at the below data.
Using Fill Handle 1
  • We have values in cells A1, B1, and C1. In cell A1 we have formatting applied, for we need to use the same formatting. For this place, we need to place the cursor on the right corner of cell A1, and we may see the "PLUS" icon, which is "Fill Handle."
Using Fill Handle 1-1
  • Now, we need to use the left click of the mouse and drag it to the right side.
Using Fill Handle 1-2
  • We have got the same value as in cell A1, but at the end of the selection, we can see a small icon, "Auto-Fill Options."
Using Fill Handle 1-3
  • Now, we may see " Auto Fill Options, " from which choose “Fill Formatting Only.”
Using Fill Handle 1-4
  • Now, we need to return to our old values with the formatting of cell A1.
Using Fill Handle 1-5

Like this, we can copy formatting using different methods in Excel.

Things to Remember

  • ALT + E + S + T is the shortcut to Paste Special formatting.
  • We can use "Format Painter" only once. If we want to use it more than once, we must press on "Format Painter" twice.