Abrar-ur-Rahman Niloy
While printing an extended dataset, Microsoft Excel normally breaks it into different pages. You can also print a large one on one page. But it generally congests all the data. So page breaks are a good choice. Adding page number to broken pages help them track for later purposes. You can add these numbers in headers, footers, or cells. Although it is not as straightforward as other Microsoft Office applications, Excel still has many features while adding page numbers. This article will discuss how to insert a page number in the Excel cell and footer if you don’t want one in the header.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
2 Suitable Examples to Insert Page Number in Excel Cell and Not in Header
As mentioned above, there are three places you can add page numbers in Excel. If you do not want to add a page number to the page header, you can add one to an Excel cell on the page, or you can insert one in the footer. We will be going over both of the cases for the following dataset.
1. Inserting Page Number in Excel Cell
If you want to insert the page number in an Excel cell, you need to use the help of VBA. Microsoft Visual Basic for Applications (VBA) is Microsoft’s Event Driven Programming Language. To use this feature you first need to have the Developer tab showing on your ribbon. Click here to see how you can show the Developer tab on your ribbon.
Once you have that, follow these detailed steps to insert the page number in Excel cells and not in the header.
Step 1: Open VBA Window
VBA has its own separate window to work with. You have to insert the code in this window too.
To open the VBA window, go to the Developers tab on your ribbon. Then select Visual Basic from the Code group.
Step 2: Insert Module
VBA modules hold the code in the Visual Basic Editor. It has a .bcf file extension. We can create or edit one easily through the VBA editor window.
To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.
As a result, a new module will be created.
Step 3: Insert VBA Code
Now select, the module if it isn’t already selected. Then write down the following code in it.
Sub Page_Count()
Dim xVCount As Integer
Dim xHCount As Integer
Dim xVBreak As VPageBreak
Dim xHBreak As HPageBreak
Dim xNumPage As Integer
xHCount = 1
xVCount = 1
If ActiveSheet.PageSetup.Order = xlDownThenOver Then
xHCount = ActiveSheet.HPageBreaks.Count + 1
Else
xVCount = ActiveSheet.VPageBreaks.Count + 1
End If
xNumPage = 1
For Each xVBreak In ActiveSheet.VPageBreaks
If xVBreak.Location.Column > ActiveCell.Column Then Exit For
xNumPage = xNumPage + xHCount
Next
For Each xHBreak In ActiveSheet.HPageBreaks
If xHBreak.Location.Row > ActiveCell.Row Then Exit For
xNumPage = xNumPage + xVCount
Next
ActiveCell = "Page " & xNumPage & " of " & Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
End Sub
After that, you can close the VBA window.
Step 4: Turn on Page Layout View
After you have inserted the code, turn on the page layout view before running the code.
Now the sheet will look like this.
Step 5: Run VBA Code
Finally, select the cell where you want to put the page number in. We have selected cell F34 for this.
Then go to the Developer tab again. This time, select Macros from the Code group.
Now select the Macro name you have just entered. Our macro name was Page_Count. So we have selected that.
After that, click on Run.
Consequently, the page number will appear on the cell.
Read More: How to Insert Page Number Using VBA in Excel (3 Macros)
Similar Readings
- How to Remove Page Number from Page Break Preview in Excel
- How to Show Page Number in Excel Background (2 Ways)
If you do not want to insert the page number in the header or a cell in the middle of the page, rather you want to add it in the end, then you can add page numbers in footers too. You can insert page numbers in footers by three methods. Each is given in its sub-section.
2.1 Inserting Page Number from Page Layout View
To insert page numbers in Excel footers using the Page Layout view feature, follow these steps.
Steps:
- First of all, go to the View tab on your ribbon.
- Then select Page Layout from the Workbook Views group.
- Now the dataset will look like this.
- After that, select the place on the footer where you want to insert your page number.
- At this instant, the Header & Footer tab will appear on the ribbon. Select Page Number from the Header & Footer Elements group in it.
The footer will now look like this.
- Finally, click on another cell and you will have the page number at the Excel footer.
2.2 Inserting Page Number Using Page Setup Dialog Box
You can achieve a similar result by using the Page Setup dialog box too. To see how this method works, follow these steps.
Steps:
- First of all, go to the Page Layout
- Then select the dialog box launcher on the bottom right of the Page Setup group as shown in the figure below.
- As a result, the Page Setup box will appear. Now select the Header/Footer tab in it.
- Then select the style of how you want your page number to show on footers from the drop-down of the Footer section.
- Then click on OK.
- If you are not in the Page Layout view, go to the View tab and select Page Layout from the Workbook Views
This will also insert the page number in the Excel footer for all pages.
There is another way to insert a page number in the Excel footer. It revolves around using the Header & Footer feature from the Insert tab.
Follow these steps to see how it is done.
Steps:
- First of all, go to the Insert tab on your ribbon.
- Then go to the Text group in it.
- After that, select Header & Footer.
- As a result, the Page Layout view will automatically pop up.
- Now select the footer position where you want to insert the page number.
- At this point, the Header & Footer tab will appear on the ribbon.
- Then select Page Number from the Header & Footer Elements from the tab.
It will now look something like this.
- Finally, click on a cell on the spreadsheet and you will see the page number will be on the Excel footer.
Read More: How to Insert Sequential Page Numbers Across Worksheets
Conclusion
That concludes different examples of all the ways you can insert a page number in an Excel cell and Excel footers if you do not want them in your header. Hopefully, you can easily add page numbers to your Excel cells now. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.
For more guides like this, visit Exceldemy.com.
Hi, I followed all the instructions; however, the numbers are not sequential when you want to print them. In my case, the cell where I run the VBA displays “Page 1 of 9,” and the last page displays the same, rather than “Page 9 of 9.” I don’t want to use the header and footer because I only recreated the form from Word to Excel.
Any help on this matter is highly appreciated.
Thank you.
Dear CHARLES DEL MAR,
Thank you very much for reading our articles. You want to add the page number in the cell following the method Inserting Page Number in Excel Cell. You mentioned that every time you run the code and get “Page 1 of 9”, even in the last cell where it is supposed to show “Page 9 of 9”. Based on your complaint, we checked all the steps and run the VBA code again. We get an accurate result. See the image below.
I think you need to check the Status Bar at the bottom of the sheet, where the page number shows. We get the same result showing in the Status Bar after running the VBA code.