Copy Formatting From One Excel Worksheet to Another

Share this content

Have you ever taken the trouble to format a worksheet - adding bold to your titles, centering column headings, changing the number format to currency in some rows and comma format in others, changing the width of columns - and then wished you could just snap your fingers and take all that formatting over to another worksheet?

The process is actually performed almost as quickly as snapping your fingers. Here's how to copy all of your formatting (not the actual content of the cells, just how they look) from one sheet to another:

  1. Click on the Select All button in the upper left corner of the worksheet that contains the desired formatting. [The Select All button is the little gray square above row number 1 and to the left of column A.]
  2. Click on the Format Painter button on the Standard toolbar. [The Format Painter button is the one with the paintbrush.]
  3. Click on the sheet tab at the bottom of the screen for the sheet that is to receive the formatting, or open another Excel file that you want to format.
  4. Click on the Select All button in the new sheet. Presto! All of your formatting has transferred!

View more helpful tips!


Please login or register to join the discussion.

great article and so easy to do. saved the day!

i love you

aku cinta kamu !! Great!

This is a great tip but it does not copy the contents of the cells. If I want to copy the contents of the cells to (because this is a sheet for data entry), how do I do that?
Thanks in advance.

Noel Mathur: Click on the Select All button and Copy / Paste into the new worksheet.

The format painter seems to work for the cell formatting, but not column width or page layout (i.e. margins / Orientation) how do I recreate those?

I figured out how to make a copy of a sheet:
Right click on the sheet tab at the bottom of the window, click "Move or Copy", select the sheet you want to copy, check the "Create a copy" option, and click "OK"!

Good article. Worked for me.

Thanks for the tip. Useful... but any chance with contents of the cells with a command using click button with specified ascending number for the sheets? I am doing it manually now but prefer to do with a click.

ace - made my life loads easier! thank you

One thing is this only works in the same instance of Excel. It will not work if you have the destination sheet open in a separate instance of Excel.