Log in  \/ 
x
or
x
x
Register  \/ 
x

or

Kutools for Excel 22.00 HOT

300+ Powerful Features You Must Have in Excel

Kutools-for-Excel

Kutools for Excel is a powerful add-in that frees you from performing time-consuming operations in Excel, such as combine sheets quickly, merge cells without losing data, paste to only visible cells, count cells by color and so on. 300+ powerful features / functions for Excel 2019, 2016, 2013, 2010, 2007 or Office 365!

Read More Download Buy now

Office Tab 14.00HOT

Adding Tabbed Interface for Office

Office Tab

It enables tabbed browsing, editing, and managing of Microsoft Office applications. You can open multiple documents / files in a single tabbed window, such as using the browser IE 8/9/10, Firefox, and Google Chrome. It's compatible with Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365. Demo

Read More Download Buy now

Kutools for Outlook 13.00NEW

100+ Powerful Features for Outlook

Kutools-for-Outlook

Kutools for Outlook is a powerful add-in that frees you from time-consuming operations which majority of Outlook users has to perform daily! It can save your time from using Microsoft Outlook 2019, 2016, 2013, 2010 or Office 365!

Read More Download Buy now

Kutools for Word  9.00NEW

100+ Powerful Features for Word

Kutools-for-Word

Kutools for Word is a powerful add-in that frees you from time-consuming operations which majority of Word users have to perform daily! It can save your time from using Microsoft Word / Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365!

Read More Download Buy now

Classic Menu for Office

Bringing Back Your Familiar Menus

Restores the old look and menus of Office 2003 to Microsoft Office 2019, 2016, 2013, 2010, 2007 or Office 365. Don’t lose time in finding commands on the new Ribbon. Easy to deploy to all computers in enterprises and organizations.

Read More Download Buy now

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


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.


The Best Office Productivity Tools

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. 60-day money back guarantee.
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
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@gmail.com · 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.edelweiss@gmail.com · 1 years ago
    terima kasih, kawan
  • To post as a guest, your comment is unpublished.
    madeleiniscg@gmail.com · 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 · 4 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

Feature Tutorials