Eshrak Kader
Looking for ways to insert page numbers when printing your worksheets in Excel? Then, you’ve come to the right place. In this article, I’ll introduce you to 6 simple ways to use formula for page number in Excel.
Download Practice Workbook
You can download the practice workbook from the link below.
6 Ways to Use Formula for Page Number in Excel
Let’s consider the dataset shown in the B4:E26 cells. Here, the dataset is divided into two parts. Now, the first portion is the PC Shipment Tracker which shows the Product names, the Delivery Date, Deadline, and delivery Status respectively. Next, the second portion consists of the Appliance Shipment Tracker as shown below. So, without further delay, let’s see the methods one by one.
We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.
Method-1: Using CONCATENATE Function
If you’re in a hurry and have to insert page numbers in a handful of pages then our first method may come in handy. Here, we’ll utilize the CONCATENATE function to merge the numbers and the text strings. So, let’s see the process in detail.
Steps:
- To begin, go to the C25 cell and enter the expression shown below.
=CONCATENATE("Page ",1, " of ",1)
In this formula, the CONCATENATE function combines the numbers and texts to return the output as Page 1 of 1.
- Next, press the CTRL + P keys to bring up the Print window.
Lastly, the results should look like the image given below.
Just like that, you’ve inserted page numbers in your worksheet, it’s that easy.
Method-2: Using Ampersand Operator to Print Page Number
For our next method, we’ll use the Ampersand (&) operator to insert the page number in a cell. So, let’s see the process bit by bit.
Steps:
- Firstly, move to the C27 cell and enter the expression shown below.
="Page "&" 1"&" of"&" 1"
In this formula, the Ampersand (&) operator combines the texts Page, 1, of, and 1 to return the output as Page 1 of 1.
- Secondly, press CTRL + P to open the Print window before proceeding to print.
Eventually, the results should appear as the screenshot shown below.
Method-3: Using TEXTJOIN Function to Print Page Number
Another similar approach involves using the TEXTJOIN function to insert the page numbers. So, let’s begin.
Steps:
- Firstly, go to the C27 cell and type in the following expression.
=TEXTJOIN(" ",TRUE, "Page", "1", "of","1")
Formula Breakdown:
- =TEXTJOIN(” “,TRUE, “Page”, “1”, “of”,”1″) → concatenates a range of text string with a delimiter. Here, “ ” is the delimiter argument which is the White Space character. Next, TRUE is the ignore_empty argument which ignores empty cells. Lastly, the “Page”, “1”, “of”, and “1” are the text1, text2, text3, and text4 arguments respectively.
- Output → Page 1 of 1
- Secondly, press CTRL + P to preview the data before printing.
Finally, this generates the results shown in the screenshot below.
Method-4: Using Insert Tab with Formula for Printing Page Number
For our second method, we’ll see the conventional approach of inserting page numbers in Excel worksheets. In fact, we’ll use the Insert Tab to put page numbers in our worksheet. So, let’s see it in action.
Case-1: Inserting Page Number in Header Section
In the first case, we’ll insert the page number in the Header of our worksheet. So, let’s begin.
Steps:
- At the very beginning, navigate to the Insert Tab >> now, and click on the Header & Footer option in the Text section.
This opens the worksheet in the Page Layout view showing the Header and the Footer.
- Now, the Header consists of 3 sections (Left, Center, and Right) where you can insert page numbers. In this case, we chose the Right section.
- Next, click the Page Number option at the top Ribbon.
After completing this step, the result shown in the picture appears.
&[Page]
Here, the Ampersand (&) operator refers to the placeholder for Page Number.
- Then, type of and again press the Page Number option to obtain the expression given below.
&[Page] of &[Page]
- Finally, press the CTRL + P key on your keyboard to preview the result before printing.
Read More: How to Insert Page Number Using VBA in Excel (3 Macros)
For the second case, we’ll repeat the same procedure to insert the page number in the Footer. So, let’s start.
Steps:
- Initially, go to the Insert Tab >> next, click on the Header & Footer option.
After this step, the Footer appears at the bottom of the page as shown below.
- Now, choose a section of the Footer (here it is the Center section) >> click the Page Number button and type of and again click the Page Number button.
Now, this generates the result as shown in the screenshot below. In addition, you may copy the formula given below.
&[Page] of &[Page]
- Eventually, press the CTRL + P key to preview the Print window.
Read More: How to Insert Page Number in Excel Cell Not in Header
Method-5: Using Page Layout Tab to Insert Page Number on Multiple Pages
Another way to insert page numbers on multiple pages involves using the Page Layout tab. It’s simple & easy, just follow along.
Steps:
- Firstly, go to the Page Layout Tab >> click on the tiny arrow button.
Immediately, the Page Setup wizard pops up.
- Secondly, press the Custom Footer button.
This opens up the Footer dialog box.
- Thirdly, select the Center section and press the Page number button.
- Following this, type in of and again click the Page Number button. Moreover, you can also copy and paste the expression from here.
&[Page] of &[Page]
- Consequently, enter CTRL + P to preview the results in the Print window.
Here, the first image refers to the first page of the preview.
In turn, the second image indicates the second page of the preview.
Read More: How to Insert Sequential Page Numbers Across Worksheets
Method-6: Utilizing Print Option with Formula for Printing Page Number in Excel
You can insert the page number just before printing your Excel sheet. Hence, the sheet will have the page number when printed. Therefore, let us go through the steps.
Steps:
- Firstly, press the CTRL + P shortcut key to go to the Print option.
In an instant, this opens the Print Window.
- Now, click the Page Setup button at the bottom.
After this step, the Page Setup dialog box pops up.
- Next, navigate to the Header/Footer tab >> and select the Custom Header option.
Now, the Header wizard appears.
- Then, choose any of the 3 sections where to insert the page number. In this case, we chose the Right section.
- In turn, enter the expression given below and click the OK button.
&[Page] of &[Page]
Subsequently, the Print preview should look like the picture given below.
Read More: How to Change Page Number in Excel (With Easy Steps)
Remove Page Numbers in Excel
Removing page numbers in Excel is also very straightforward. Now, allow me to demonstrate the process in the steps below.
Steps:
- Firstly, move to the Page Layout tab and click the small arrow button.
After completion of the above step, the Page Setup wizard pops up.
- Secondly, in the Header field, click the drop-down >> from the drop-down list, select the (none) option >> click the OK button.
- Lastly, type CTRL + P to go to the Print option.
Just like that, all the page numbers are gone, it’s that simple!
Read More: How to Remove Page Number from Page Break Preview in Excel
Practice Section
For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.
Conclusion
I hope all of the methods mentioned above to use formula for page number in Excel will now prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.
=CONCATENATE(“Page “,1, ” of “,1) … this is just wrong. This is not page numbers but just joining some text.
Hello S,
Thank you for your question. Firstly, I would like to apologize for the misunderstanding. Admittedly, the method in question is in fact a quick and dirty way to manually insert page numbers if your document contains only a handful of pages.
That said, the widely accepted process of inserting page numbers in Excel is described in Method 4. Hopefully, this helps.