Sift Media
Blogger
Share this content
Graph
filmfoto_iStock_graph

Copy Formatting From One Excel Worksheet to Another

by
Sep 1st 2002
Sift Media
Blogger
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!

Replies (10)

Please login or register to join the discussion.

avatar
By Charlie D
Jun 25th 2015 20:10 EDT

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

Thanks (0)
avatar
By jaci s
Jun 25th 2015 20:10 EDT

i love you

Thanks (0)
avatar
By asu
Jun 25th 2015 20:11 EDT

aku cinta kamu !! Great!

Thanks (0)
avatar
By Noel
Jun 25th 2015 20:11 EDT

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.

Thanks (0)
avatar
By Heidi R
Jun 25th 2015 20:11 EDT

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?

Thanks (0)
avatar
By Heidi R
Jun 25th 2015 20:11 EDT

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"!

Thanks (0)
avatar
By Mark
Jun 25th 2015 20:11 EDT

Good article. Worked for me.

Thanks (0)
avatar
By J Edison Daniel
Jun 25th 2015 20:11 EDT

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.

Thanks (0)
avatar
By Aims
Jun 25th 2015 20:11 EDT

ace - made my life loads easier! thank you

Thanks (0)
avatar
By Rob
Jun 25th 2015 20:11 EDT

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.

Thanks (0)