How can I set default header and footer in Excel.?

Question

I have total 500 sheets in different workbooks. I want to insert same header and footer in all the sheets. Is there any option through which we can set default header and footer for all the workbook files (existing or to be created).

Answer 1

Select all the sheets (Right click on a sheet's name & select)
Go to File
Page Setup
Type what you want in "Custom Header & Footer"
Okay it.

Note: This might not work if you go to the page setup by clicking on the "Preview" toolbar button & selecting "page setup".

You have to remove them in the same way.

Answer 2

There are two easy ways of doing that.

The first is for new worksheets and workbooks. Create a template workbook file or a .XLT file. And set up the header and footer just like you want it in that file. Use Chaminda's answer if you do not know how to change the header or footer. If you need to add more worksheets to the workbook, copy one of the blank worksheets that you've set up and use it as a new worksheet. It should copy over the header and footer settings for the sheet that was copied. The sheet can be renamed after you copy it.

The second easy option which would work better for your pre-exhisting workbooks and worksheets would be to write a macro. You can use the Macro Recorder to help you figure out the coding by recording a macro when you change the header to the way you want it, then going back and looking at the code it created. Then go back and apply those changes to the Worksheets collection. When you apply the changes to the worksheets collection it will make those changes on all of the worksheets.

For instance, the macro I wrote below will add the current date to the right header and the page number and total pages to the center footer on all of the worksheets in the active workbook.

Dim SheetNumber As Integer
Dim NumberOfSheets As Integer

NumberOfSheets = Worksheets.Count

For SheetNumber = 1 To NumberOfSheets
With Worksheets(SheetNumber).PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = "&D"
.LeftFooter = ""
.CenterFooter = "Page &P of &N"
.RightFooter = ""
End With
Next SheetNumber

Answer 3

No, there is no default header/footer in Excel. However, you can easily insert a header/footer in Excel. When you do so, it doesn't show up on every page like in Word; nothing on your sheets or workbooks is changed in any manner. Instead, the header/footer only shows in Print Preview and when you print a page.

To access the Header/Footer dialog box, click View, Header and Footer. It's different than Word and a little tricky, so I'd recommend you type "Add Header/Footer" in the Excel search box and do a quick read through Create Custom Headers and Footers. It'll be worth your effort given the size of your workbooks.

Answer 4

I'd just type it in and format it however, then copy/paste with a few mouse clicks into the cells you want it, save and be done with it. You could end up spending more time trying to save time than you'd use doing it the "old-fashioned" way. After all, once it's copied, it's simple to paste it in everywhere.