How to insert page number into cell / header / footer in Excel?

When you print an Excel file, you may need to insert the page numbers into the pages to make the printed file neatly and readably. In this article, I will introduce some quick tricks to insert the page numbers into cell, header or footer for you.

Insert page number into header / footer in one worksheet

Insert page number into header / footer across multiple worksheets

Insert page number into cell with VBA code

Remove all page numbers at once in Excel

Insert page number into header / footer in one worksheet

Normally, we can insert the page numbers into header or footer quickly by using the Header & Footer function, please do as follows:

1. Go to the worksheet which you want to insert the page numbers into the header or footer.

2. Then click Insert > Header & Footer, and your worksheet will be in Page Layout view, see screenshot:

3. Click the header or footer where you want to insert the page number, and then a Design tab with Header & Footer Tools is displayed in the Ribbon, then click Design > Page Number, see screenshot:

4. And you can see the placeholder &[Page] appear in the selected section, then click anywhere outside the header or footer area to display the page numbers. See screenshot:

5. Now, you can reset back to the normal view by clicking View > Normal, and when you print this worksheet, you will see the page numbers have been inserted into the header or footer that you selected.

Note: If you want the page numbers displayed as 1 of 15, 2 of 15 format, you just need to directly enter this &[Page] of &[Pages] into the header or footer field box, see screenshot:

Insert page number into header / footer across multiple worksheets

Above method can help you to insert the page numbers into one worksheet, if you want to insert the page numbers into all worksheets of the workbook to make all pages are numbered in sequential order, please do as this:

1. Select all of the sheet tabs, then go to Page Layout tab, and click Dialog Box Launcher Button icon in the Page Setup group, see screenshot:

2. In the Page Setup dialog box, please click Header/Footer tab, and then click Custom Header or Custom Footer to go to set the header or footer that you want, see screenshot:

3. And in the Header or Footer dialog box, please define the location where the page numbers are inserted by clicking inside the Left section:, Center section:, or Right section: box as you need, and then click Insert Page Number icon, and placeholder &[Page] appears. See screenshot:

Note: Enter this &[Page] of &[Pages] into the header or footer field box if you like the page numbers displayed as 1 of 45, 2 of 45…

4. Then click OK > OK to close the dialog boxes, when you print this workbook, in the print preview, you can see all page numbers are inserted into the header or footer in sequential order.

Insert page number into cell with VBA code

It is easy for you to insert the page numbers into header or footer, but, if you need to insert the page numbers into a worksheet cell, there is no direct way for you to do it. The following VBA code may help you to solve this problem.

1. Click a cell where you want to display the page number of this cell.

2. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Insert current page number into a cell:

Sub pagenumber()
'updateby Extendoffice 20160506
    Dim xVPC As Integer
    Dim xHPC As Integer
    Dim xVPB As VPageBreak
    Dim xHPB As HPageBreak
    Dim xNumPage As Integer
    xHPC = 1
    xVPC = 1
    If ActiveSheet.PageSetup.Order = xlDownThenOver Then
        xHPC = ActiveSheet.HPageBreaks.Count + 1
    Else
        xVPC = ActiveSheet.VPageBreaks.Count + 1
    End If
    xNumPage = 1
    For Each xVPB In ActiveSheet.VPageBreaks
        If xVPB.Location.Column > ActiveCell.Column Then Exit For
        xNumPage = xNumPage + xHPC
    Next
    For Each xHPB In ActiveSheet.HPageBreaks
        If xHPB.Location.Row > ActiveCell.Row Then Exit For
        xNumPage = xNumPage + xVPC
    Next
    ActiveCell = "Page " & xNumPage & " of " & Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
End Sub

4. Then press F5 key to run this code, and the page number of this cell is displayed in the selected cell, see screenshot:

Remove all page numbers at once in Excel

To remove all of the page numbers, you can apply the following steps:

1. Select all of the sheet tabs, and go to the Page Layout tab on the ribbon, then click Dialog Box Launcher Button icon in the Page Setup group, see screenshot:

2. In the Page Setup dialog box, click Header/Footer tab, and then choose (none) from the Header or Footer drop down list, see screenshot:

3. Then click OK button, all of the page numbers are removed from the workbook at once.