Print consecutive page numbers across multiple worksheets
This article describes how to print page numbers in consecutive order through specified worksheets and how to repeat column headers on each printed page.
Table of Contents
1. Print consecutive page numbers across multiple worksheets
This article explains how to print multiple worksheets so the page numbers are in sequence across all selected worksheets.
The problem is that when you print each worksheet individually, the page numbering resets to 1 for each new worksheet. To overcome this, you can use the methods described below to print specific worksheets or all worksheets with continuous page numbering that spans across all worksheets.
What you will learn in this section:
- How to select multiple worksheets.
- How to enable the "Header and Footer" edit mode.
- How to insert page numbers to the footer of each page.
- How to exit the "Header and Footer" tab.
- How to exit the "Header and Footer" edit mode.
- How to print preview the selected worksheets.
- Navigate pages in "print preview" mode.
- Verify page numbers in "print preview" mode.
- How to deselect or "ungroup" worksheets.
- The issues you may experience if you don't deselect worksheets.
Instructions:
- Select all worksheets you want to show page numbers on. Press with right mouse button on on one of the tabs located at the bottom left corner of your screen. A pop-up menu appears. Press with mouse on "Select All Sheets."
If you want to only select a specific range of worksheets, use the SHIFT key. Here is how:- Press with mouse on the first worksheet tab (usually labeled "Sheet1") at the lower left corner of the Excel window.
- Press and hold the SHIFT key on your keyboard.
- While holding the shift key, press with left mouse button on the last worksheet tab (usually labeled "SheetX", where X is the number of worksheets).
This will select all worksheets in between the first and last tabs. You can now perform actions on all selected worksheets, such as showing page numbers etc.
The image above shows three highlighted tabs (Ex 2, Ex 3, and array) and one regular tab (Sheet1).Tip!Use the CTRL key on your keyboard to select specific worksheets instead of all worksheets if needed.
- Go to tab "Insert" on the ribbon.
- Press with left mouse button on "Header and Footer" button.
- A new tab named "Header & Footer" appears on the ribbon, you are now in the "Header & Footer" edit mode.
- Press with left mouse button on "Go to Footer" button. This takes you to one of three boxes in the footer. Press with mouse on one of the boxes where you want the page number to show up.
- Press with left mouse button on "Page number" button. This creates the page number. The "Header & Footer" edit mode shows &[Page] in the selected Footer box.
- Press with left mouse button on in an empty box next to the footer box. Press Escape key on your keyboard to exit the "Header & Footer" tab on the ribbon.
The "Header & Footer" edit mode is, however, still active. Go to "View" tab on the ribbon", press with left mouse button on the "Normal" button to show the regular worksheet again. - Go to "File" on the ribbon.
- Press with mouse on "Print", a "print preview" box is now visible on the right side of the screen and print settings on the left side of the screen.
- Check that page numbers are shown at the bottom of the "print preview" box.
- Press with left mouse button on the black arrow at the bottom of the screen to go to the next page, see the image above. Check that all your pages contain page numbers.
- Press with left mouse button on Print button to print the select worksheets.
The next step is important, make sure you ungroup the select tabs before continuing your work with your workbook. If you don't ungroup tabs (worksheets) after selecting all of them, any changes you make to one worksheet will be applied to all selected worksheets. This can lead to unintended consequences, such as:
- Formatting changes: If you change the formatting of one worksheet, it will be applied to all selected worksheets.
- Data changes: If you enter or edit data in one worksheet, it will be replicated in all selected worksheets.
- Deletions: If you delete data or cells in one worksheet, it will be deleted in all selected worksheets.
- Insertions: If you insert new rows, columns, or cells in one worksheet, it will be inserted in all selected worksheets.
To avoid these issues, it's essential to ungroup the worksheets. Here is how:
1.2Â How to ungroup tabs
- Press with right mouse button on on one of the tabs. A popup menu appears, see the image above.
- Press with mouse on "Ungroup Sheets".
The image above shows only one worksheet selected, this means that the tabs are now ungrouped.
2. Repeat headers on each page
The image above shows a data table containing four columns: Username, Name, Industry, and State. This section of the article demonstrates how to repeat these header names across all printed papers automatically.
This will improve readability and make it easier to understand the data when readers turn the pages. Instructions:
- Go to tab "Page Layout" on the ribbon.
- A dialog box appears.Go to tab "Sheet" on the dialog box.
- Press with left mouse button on the arrow next to field "Rows to repeat at top:".
- Select the row that contains the header names you want repeat across all printed pages.
- Press with left mouse button on the "Print preview" button, see the image above.
- A new window appears named "Print".
- Use the arrows located at the very bottom to navigate through the pages.
- Verify that each page has the selected column names at the very top.
The image above shows the second page in "print preview", the data has the column names repeated to this page as well. - Press the "Print" button to print the pages or press with left mouse button on the back arrow to go back to Excel.
Print category
This article describes how to create a button and place it on an Excel worksheet, then assign a macro to […]
This article demonstrates different techniques on how to take a screenshot of your worksheet. You can do that by simply […]
Have you ever wondered how these lines got there on a worksheet? They are called "print preview lines" and they […]
Excel categories
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
