Excel add page number in footer

This Excel tutorial explains how to add page number in footer. E.g. add page number to all worksheets, add page numbers to specific worksheets.

Excel add page number in footer to one worksheet

Select the worksheet you want to add page number in footer

Navigate to PAGE LAYOUT tab > Print Titles

Excel Assign Page Number 02

 

Click on Header/Footer tab > select the desired Footer format > OK

Excel Assign Page Number 03

 

To check the page number in print preview, Go to FILE > Print
Make sure the Settings on the left hand side is Print Active Sheets

Excel add page number in footer 01

Excel add page number in footer to all worksheets

Assume that we have 4 worksheets, we want to add page number in footer in each worksheet.

First of all, select all worksheet tabs. You can do it in two ways:
1. Hold down Ctrl and then left click the mouse on each tab
2. Right click on one of the sheet, and then click Select All Sheets

Excel Assign Page Number 01

 

Repeat the steps in add page number in footer to one worksheet

Navigate to PAGE LAYOUT tab > Print Titles

Excel Assign Page Number 02

 

Click on Header/Footer tab > select the desired Footer format > OK

Excel Assign Page Number 03

 

Now you can see four pages in print preview. Make sure the Settings on the left hand side is Print Active Sheets

Excel add page number in footer 02

 

*If you hide a worksheet, say Sheet2, then Sheet3 will become page 2, and Sheet4 will become page 3

Excel assign specific page number to specific worksheet

Originally we have designed Sheet1 as page 1, Sheet2 as page 2, Sheet3 as page 3. If we select only Sheet2 and Sheet3 to print, they will become page 1 and page 2 automatically. So how do we assign page 2 to Sheet2 and page 3 to Sheet3?

To do that, select Sheet2, navigate to Page Setup > Header/Footer > Custom Footer > type Page 2 in Center section
Now you have hard coded Page 2 for Sheet2

Excel add page number in footer 04

 

Excel assign specific page number to specific worksheet (VBA)

If you have a lot of worksheets to rename, you probably want to do it in an efficient way.

Excel add page number in footer 03

 

Insert the following Procedure in Module

Public Sub add_page_number()

    lastrow = Sheets("index").Range("A" & Rows.Count).End(xlUp).Row
    Sheets(Sheets("index").Range("A" & 2).Value).Select
    
    For i = 2 To lastrow
        Sheets(Sheets("index").Range("A" & i).Value).PageSetup.CenterFooter = "Page" & Sheets("index").Range("B" & i).Value
        Sheets(Sheets("index").Range("A" & i).Value).Select False
    Next

End Sub

Now Sheet2 and Sheet3 have bee configured as your desired page number. More importantly, both Worksheets are “activated”,  meaning if you select Print Active Sheets in Settings, only those worksheet names in column A will be printed.

 

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff196103.aspx

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *