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.
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:
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.
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:
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.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 years agoMay 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.· 1 years agoMay 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.· 1 years agoterima kasih, kawan
- To post as a guest, your comment is unpublished.· 1 years agoHola, 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.· 2 years agoI 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.· 4 years agoHello
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!