VBA & Macro Problems

How to Display Page X of Y in an Excel Cell Using VBA

Maira Mehtab Sep 30, 2026 868 views

Question details

The user wants to display the current printed page number and the total page count (e.g., Page 1 of 5) directly inside a specific Excel worksheet cell.

How to Display Page X of Y in an Excel Cell

Product: Excel
Device & OS: not provided
Scenario: Formatting a worksheet for printing where page numbers must be visible within the grid or a specific cell, rather than the standard header or footer.
Observed behavior: Excel does not provide a built-in worksheet formula for page numbers in cells; this information is typically restricted to headers and footers.

Before you start

Ensure you have enabled the Developer tab in your ribbon and saved your workbook as a Macro-Enabled Workbook (.xlsm) since this solution requires running a VBA script.

Solution 1Recommended

Use a VBA Macro to Calculate and Display Page Numbers

Since standard formulas cannot read print layout data, using a custom VBA script is the only way to write 'Page X of Y' directly into a specific cell.

This VBA solution calculates the worksheet's current page location and total printed pages, then writes it as a text string into your desired cell. Because it calculates based on the current print layout, the macro must be rerun whenever print areas, scaling, page breaks, or worksheet contents change.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in Excel.

Insert a New Module

Click 'Insert' from the top menu, then select 'Module' to create a blank module for your code.

Paste the Page Number Code

Enter your custom VBA script that utilizes the ExecuteExcel4Macro("GET.DOCUMENT(50)") command to determine total pages and calculates the active cell's page number.

Run the Macro

Close the VBA editor and run the macro from the Developer tab to populate your target cell with the 'Page X of Y' text.

Use a VBA Macro to Calculate and Display Page Numbers

Manual Updates Required: The value written in the cell is static. You must execute the macro again if any worksheet formatting, data, or page breaks are modified.

WPS Office Macros

Easily Manage Macros and VBA with WPS Office

WPS Office Spreadsheet provides excellent support for VBA and macros, allowing you to run custom scripts like page number calculators seamlessly and efficiently.

1. Open your workbook: Launch WPS Spreadsheet and open your existing Excel file.
2. Access the Developer Tab: Navigate to the Developer tab on the top ribbon to access macro tools.
3. Run your Script: Open the VBA Editor (ALT+F11), paste your page number calculation code, and execute it directly within the application.

Fully compatible with Microsoft Excel (.xlsm) formats and VBA scripts.Advanced support for print layout calculations and formatting.Lightweight, free alternative that runs complex macros quickly.Familiar ribbon interface makes accessing the Developer tab easy.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I just use a standard formula to show the page number?

Excel's standard formulas calculate data and do not interact with the print layout engine. Therefore, native formulas cannot determine where page breaks fall or how many pages will be printed.

Can I show 'Page X of Y' in the header or footer instead?

Yes, Excel natively supports this in headers and footers. Go to the Insert tab, click Header & Footer, and select the 'Page 1 of ?' format from the Header or Footer dropdown.

Will the cell page number update automatically if I add more rows?

No, because the VBA macro writes a static text value into the cell. If your layout changes, you must run the macro again to recalculate and update the page numbers.

Excel & Data Problems