How to Display Page Numbers in a Cell in WPS Spreadsheets with VBA or Formulas
Question details
The user needs to show printed page numbers directly inside worksheet cells instead of only in headers or footers, using either formulas or VBA.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with a multi-page spreadsheet that needs page numbers visible in cells for printed reports.
- Observed behavior
- WPS Spreadsheets normally inserts dynamic page numbers only in headers and footers; a formula can approximate page numbers, while VBA is needed for true page-break-aware page numbers.
Before choosing a method, confirm whether the number of printed rows per page is stable and note that exact row count. If the report layout changes frequently, skip the formula workaround and enable macros for the VBA method.
Use a Formula to Estimate Page Numbers in a Cell
Use this when the printed layout is fixed and you want a quick non-macro solution.
This formula approach is best for fixed layouts. It calculates a page number by dividing the current row offset by a known rows-per-page value, so it cannot read actual page breaks and must be adjusted whenever page setup changes.
Open your WPS Spreadsheets file, select Page Layout > Print Area, and define the exact range you want to print so the rows per page remain predictable.
Use Print Preview or Page Break Preview from the View tab to count how many data rows appear on a printed page.
In the first data cell where you want the page number, enter the formula ="Page "&INT((ROW()-1)/51)+1, replacing 51 with the number of rows per printed page and adjusting the starting row if the data does not begin in row 1.
Drag the fill handle down the column so every row displays its estimated printed page number.
Use VBA to Calculate Page-Break-Based Page Numbers in Cells
Use this for a dynamic, reliable result that updates when page breaks or print settings change.
Add Page Numbers to Cells in WPS Office Spreadsheets
You can complete this task directly in WPS Office Spreadsheets using either a formula for fixed layouts or a VBA macro for page-break-aware page numbers. The same workflow works with familiar Excel-style menus and macro support.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the Excel-compatible workbook, then switch to the worksheet where you need page numbers in cells.
- 2. Choose a formula or macro method: Use Page Layout and Print Preview to set your print area, then either enter a row-count formula or open the Developer tab for VBA.
- 3. Insert the page-number formula or macro: Fill the target cells with ="Page "&INT((ROW()-1)/51)+1, or run a VBA macro that writes page numbers based on page breaks.
- 4. Check the print result: Go to File > Print Preview to verify the page numbers match the physical printed pages, then save the workbook in .xlsx or .xlsm format.
Frequently Asked Questions
Can a worksheet formula display the real printed page number in a cell?
No. Worksheet formulas cannot read Excel page-break positions. Use a rows-per-page estimate such as ="Page "&INT((ROW()-1)/51)+1 for stable layouts, or use VBA for actual page-break-aware numbers.
How do I adjust the page-number formula if my data starts in row 3?
Replace ROW()-1 with ROW()-2 so the first data row becomes page 1. For example, use ="Page "&INT((ROW()-2)/51)+1 and replace 51 with your actual rows per printed page.
How do I enable macros in WPS Spreadsheets for VBA?
Open the Developer tab, click Macro Security, and choose a setting that allows macros. After enabling, open the VBA editor with Alt+F11 or Developer > View Code, then run the macro.
Will these cell page numbers update automatically after changing the print area?
The formula version only recalculates based on the fixed rows-per-page number, so it may become incorrect. The VBA version must be run again after changing page breaks to update the cells.
