WallStreetMojo

WallStreetMojo

Home » Excel, VBA & Power BI » Excel Tutorials » Excel Copy Formatting

Excel Copy Formatting

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Copy Formatting in Excel

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

You can download this Copy Formatting Excel Template here – Copy Formatting Excel Template

#1 – Copy Formatting Using Paste Special

Below are the 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 of color as well as number formatting. Now, if we copy the cell B8 and paste it to C8 cell, we will get the total of column C along with formatting exactly 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 & paste method; rather, we need to employ a paste special method.

  • First, copy the 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 below paste special window.

Copy Formatting Example 1-3

  • As you can see in the paste special window, we have several paste special options, 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 the cell B8 is copied and pasted to cell C8.

Copy Formatting Example 1-5

  • There you go, we have got only the formatting of the cell B8 to the 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 painter brush, which is used to paint; a similar concept has been integrated to excel as well.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

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 formatting of the cell, but in cell D1, we have the only number which is without formatting. So we will use Format Painter to copy the formatting from cell A1 to D1.

  • Place your cursor in cell A1 and click on “Format Painter.”

Using Format Painter 1-2

  • As soon as you press the option of “Format Painter,” you will see a brush option.

Using Format Painter 1-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 click of the mouse.

  • If you 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 every time we need to press the option of “Format Painter,” so instead of pressing every time, we can make the Format Painter active for more than one time.

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

Using Format Painter 1-6

  • Now keep clicking on the cells that you need to apply the formatting for.

Using Format Painter 1-7

Like this, we can copy formatting using format painter.

#3 – Copy Formatting Using Fill Handle

Fill handle in excel is also can be used to apply to format, for example,

  • 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 apply the same formatting. For this place, a cursor on the right corner of the cell A1 and you will see the PLUS icon, which is “Fill Handle.”

Using Fill Handle 1-1

  • Use your left click of the mouse and drag it to the right side.

Using Fill Handle 1-2

  • Oh!!!! We have got the same value as in the cell A1 but wait, at the end of the selection, we can see a small icon, which is “Auto Fill Options.”

Using Fill Handle 1-3

  • Now you will see options of “Auto-Fill,” from this choose “Fill Formatting Only.”

Using Fill Handle 1-4

  • Now we get back 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 of formatting.
  • Format Painter can be used only once if you want to use it for more than one time then press on Format Painter twice.

Recommended Articles

This has been a guide to Excel Copy Formatting. Here we learn how to copy formatting in excel using paste special, format painter, and fill handle along with examples and downloadable excel templates. You may learn more about excel from the following articles –

0 Shares
Share
Tweet
Share
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>