Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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 sheet name / file name / created date / modified date ... into header/ footer / cell:

With Kutools for Excel’s Insert Workbook Information utility, you can quickly insert the sheet name, file name, file path and name, created date, modified date and son on into a cell, header or footer as you need. Download and free trail Kutools for Excel now!

doc insert page numbers 11-11

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


arrow blue right bubble 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:

doc insert page numbers 1

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:

doc insert page numbers 2

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:

doc insert page numbers 3

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:

doc insert page numbers 4


arrow blue right bubble 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:

doc insert page numbers 5

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:

doc insert page numbers 6

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:

doc insert page numbers 7

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.


arrow blue right bubble 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:

doc insert page numbers 8


arrow blue right bubble 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:

doc insert page numbers 9

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:

doc insert page numbers 10

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


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom

 

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Onyx LAI · 1 years ago
    May I know how I should modify above VBA that allows me to execute this to range of cells on a workbook and with 1 short cut key to apply all selected cells?
  • To post as a guest, your comment is unpublished.
    Onyx · 1 years ago
    May I know if I have range of cells that I want to put in page numbers, what should I change? The current VBA only allows me to change one by one.
  • To post as a guest, your comment is unpublished.
    Wep Channel · 1 years ago
    terima kasih, kawan
  • To post as a guest, your comment is unpublished.
    Made Made · 1 years ago
    Hola, hay un error en la formula porque cuando ejecuto (F5) el primer valor numérico de "Pagina XX de XX" no cambia. Solo hace el conteo de páginas el segundo valor. Podrian revisar... Gracias
  • To post as a guest, your comment is unpublished.
    Jim · 2 years ago
    I tried the VBA macro, but I'm getting a subscript out of range error... Did I do something wrong?
  • To post as a guest, your comment is unpublished.
    Antoinette Anderson · 3 years ago
    Hello

    I used the Insert page number into cell with VBA code above and it works a charm.
    My sheet could be either 2 pages long (2 down and 1 across), 4 pages long (2 down and 2 across) or 6 pages long (2 down and 3 across).
    I have inserted the code in each cell of the 6 that require page number to be printed (by selecting the cells each in turn and running the VBA), cell addresses are H1, H35, T1, T35, AF1, AF35.
    Now I would like to add a button to refresh these page numbers without affecting whichever cell may be active when the relevant user may choose to run it, currently I have to select each cell in turn again and run the VBA :(

    Assistance would be hugely appreciated!

    Antoinette