Software

Copy Excel page setup settings from one sheet to another

The next time you need to configure the Page Setup options for a new worksheet, try this quick copy trick.
Many print settings, such as orientation, margins, headers, and so on, are specific to a sheet. For instance, you can print Sheet1 in landscape with one-inch margins and Sheet2 in portrait with three-inch margins without resetting options from sheet to sheet. When you add a new sheet to the works, Excel assigns the default page setup settings. If all of your sheets require the same settings, you can simply adjust the defaults. However, it's common to have sheets with different page setup settings in the same workbook. You could manually set all those options. But you may save time by  transferring the settings from one sheet to the another, as follows:

  1. Select the existing sheet, the source sheet, that contains the settings you want to transfer.
  2. Select the target sheet(s) -- the sheets you want to update --  by pressing [Ctrl] and clicking each sheet tab.
  3. Select Page Setup from the File menu and click OK.

That's it. Wasn't that easier than resetting all those settings yourself? The target sheet(s) now have the same print settings as the source sheet. Two warnings:
  • Make sure you select the sheets in the right order -- source, then target(s). If you activate a target sheet first, you'll overwrite the settings you meant to copy.
  • Be sure to ungroup your sheets when you're done. While grouped, Excel will execute any action you take on all the sheets in the group.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

14 comments
Paula1113
Paula1113

So, I'm trying to do a simple project and copy the whole page format and there is no Page Setup under File in this version of Excel...not nice.  I was so excited to not have to copy the headers and footers, too.  Simple, right...Can't follow the directions from the expert.  It looked simple.

srego
srego

Hello, I'm using Excel for Mac 2011. I followed Susan's technique to copy Page Setup attributes from a source worksheet to a group of target sheets. And this is what I learned: - You can indeed copy the margins and header/footer information following the technique - But if a worksheet has a custom Paper Size, that information won't be copied - However if the source sheet has Paper Size = US Letter and the target sheet originally has a custom paper size, the US Letter will overwrite the custom size I also tried recording a macro starting from an active worksheet with a graph but when I applied the macro to another graph it wouldn't work. I'm doing all this because Excel doesn't seem to remember the individual Paper Size of each worksheet when re-opening the file. All the custom sizes revert to US Letter. Maybe it's a Mac thing. Have you seen the same behaviour with copying custom paper sizes in Excel for Windows? Thank you.

aksalaymeh
aksalaymeh

It is really nice, furthermore you can copy data with the same procedure

jsnandi
jsnandi

Superb! A great learning and future time saving.--jsn

rpender
rpender

This was a real eye opener. With this new knowledge, I found that when you have multiple tabs (worksheets) selected, ANY ENTRY that you make in a cell in the SOURCE cell is duplicated into the same cell in all of the TARGET tabs (worksheets). So if you type something it will appear in all the worksheets. If you change the size of a column it changes in all worksheets. If you create a header or footer it will be created in all selected worksheets. If I only knew this 2 weeks ago when I had to enter headers for 19 worksheets in one workbook. Would have saved me lots of copy/pastes. Thanks

lwiese
lwiese

Thanks. I will use this shortcut all the time!

amg8589
amg8589

I dig this! Thanks so much!

lockhaca
lockhaca

use steps 1 & 2 then click Page Layout tab, select margin in dialog box and click ok. It worked!

randomcorey
randomcorey

Nicely done Susan. Certainly one to remember.

rmc400
rmc400

the page margin tip works, but only for page margins. Other page settings such as headers don't carry over that way. Has anyone found a tip to copy all existing page settings to other pages in 2007 without rekeying the settings?

hjertet
hjertet

K?re Lars Flyt v?rkt?jslinien 'Hurtig adgang' ned under 'B?ndet'. Tilpas 'Hurtig adgang' (knap yderst til h?jre p? v?rkt?jslinien). V?lg 'Flere kommandoer' - derefter 'Alle kommandoer'. Find frem til 'Sideops?tning' og 'Tilf?j'. S? har du sideops?tning t?t ved hver gang. Og herefter virker tricket. Fra Hjertet

mnfish
mnfish

1.) Right click the tab you wish to copy. 2.) select "move or copy" in the box that appears when you right click the tab. 3.) Place a check in the box labeled "create a copy" (the other boxes simply tell the worksheet where to put the new copied tab in the lineup of your existing tabs) 4.) Click ok and a new, fully copied tab page appears on your project. 5.) Simply rename the new tab. 6.) Done

JKFentiman
JKFentiman

After setting up the first sheet, do steps 1 and 2, then click Page Layout on the the ribbon, then select the drop down arrow on Sheet Options (under the Gridlines and Heading box). Click OK and the settings should copy over to the other sheets you had selected. The only thing I have found that it will NOT copy over is the print area. You will need to set that up on each page. Another way to copy all the settings, including print area is to set up the first page, then Move or Copy and make a copy of the sheet...but that will only work when you are beginning your workbook, not after you already have several sheets made with data already on them.

Editor's Picks