

It’s a nightmare: Printing Excel sheets. You permanently feel like Excel hates you when you want to print an Excel sheet. That said, let’s take a look at the basics (like defining the print range) as well as advanced topics (like changing headers and footers on several sheets simultaneously).
Contents
First step: Press Ctrl + p on the keyboard and see the print overview
Starting is simple: Press Ctrl
But the actual work begins now: Defining the print range, page settings and all the other settings in detail. But maybe you got luck and somebody formatted the printout already…
Most settings can also be accessed via the Page Layout ribbon. The advantage to to it within the print screen (as shown in the image above) is that you can see changes right away.
By default, Excel often wants to print your worksheet in a large size and spreads it over many pages. Fortunately there is a quick method of squeezing everything on one page:
Set the print range with just two clicks on the Page Layout ribbon.
The fast way of setting the print range: Select the cell on the worksheet which you want to be printed.
Now click on “Print Area” on the “Page Layout” ribbon (number 1 on the picture on the right hand side). After that, click on “Set Print Area”.
One more advice: There is a tiny arrow in the right bottom corner which opens the window for all the printing preferences (number 2). With this button you can also set the rows and column to repeat. For more information on that topic please check the paragraphs below.
Switch to “Page Break Preview” for fine tuning the print range.
Especially when printing large worksheets on more than one page, you might want to fine tune the print range. For example, change the page break.
Therefore, switch to “Page Break Preview”: Click on “Page Break Preview” on the “View” ribbon. Excel then shows you the pages with blue and dotted blue lines. Not printed areas are greyed out. You can drag and drop these line to your desired format.
Please note that the mouse arrow often doesn’t work correctly: In many cases it doesn’t switch to the drag and drop mouse pointer. If that happens to you: just proceed. Usually the dragging and dropping still works well.
Set the rows and columns to repeat by clicking on “Print Titles” within the “Page Layout” ribbon.
If your worksheet spreads over more than one printed page, you can define rows and columns to repeat. That way, the header row or column will be printed on each page. Large tables are much easier to read.
You have to go through the “Page Layout” ribbon and set the print range there.
Please note: You can’t change these settings if you go through the print preview window (see paragraph below).
If rows or columns to repeat is greyed out…
There is a strange bug in Excel – already for some years. After pressing Ctrl + p for opening the print window you can’t modify the rows and columns to repeat. The “Print area”, “Rows to repeat at top” and “Columns to repeat at left” are greyed out and can’t be changed as shown on the image.
… you have to go through the “Page Layout” ribbon and open the Page Setup dialogue there.
Instead, you have to go through the “Page Layout” ribbon and set the print range there:
Set print headers and footers easily
Very useful: Define headers and footers. The advantage is that you won’t mix up the printed pages. Follow these steps for inserting headers and footers (the numbers are corresponding to the image on the right hand side):
Example: You want to insert “Page 2 of 3”. Therefore enter “Page ” then click on button 4, type ” of ” and click on button 5.
These settings you can also add in different ways by typing the code. If you want to work with the codes, please refer to this table. For those of you who like to do some VBA programming we’ve also added the corresponding VBA code but we won’t go more into detail here:
Header/Footer element | Code for inserting in Header/Footer window | VBA code |
Insert Page Number | &[Page] | &P |
Insert Number of Pages | &[Pages] | &N |
Insert Date | &[Date] | &D |
Insert Time | &[Time] | &T |
Insert File Path | &[Path] | &Z |
Insert File Name | &[File] | &F |
Insert Sheet Name | &[Tab] | &A |
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
Learn more or get it on Amazon!
Change the print settings for several sheets at the same time
Changing the print settings for several worksheets simultaneously is quite easy: Select all the sheets and press Ctrl
Attention: Even if you just want to change one setting (e.g. the headers and footers), everything will be resetted, for example also the print range or orientation. You could use this method as a starting point: Roughly define the print settings for all worksheets and afterwards fine tune each sheet separately.
Please refer to the next point if you want to change or update headers and footers for many sheets at the same time.
Comfortably change headers and footers with Professor Excel Tools – all other print preferences will remain!
As described in our previous point, Excel destroys all existing print settings if you want to change the headers and footers of several sheets simultaneously. You basically have two options:
Our Excel add-in “Professor Excel Tools” provides a simple and comfortable tool for changing printing headers and footers without destroying existing print preferences like the orientation. Even better: It saves your previous print preferences.
This function is included in our Excel Add-In 'Professor Excel Tools' Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.
Place your table in the center of the printout page
You want to center our table on the printout paper?
Again, open the print dialogue by pressing Ctrl
In the lower part of the window you can find the section “Center on page”. Set the tickmarks as desired for centering your table vertically or horizontally on the printout page.
Easily change the orientation from portrait (default) to landscape.
Like before, open the print preview. Either by clicking on “File” and then “Print” or by pressing Ctrl
On the left hand side, select portrait or landscape. It’s that easy…
Most printer offer a two sided print option.
First question: Why do you want to print odd or even pages only? If the reason is that you want to print your Excel sheet on front and back side of the paper, please take a look at the print menu first. Most printers offer such option so that you don’t have to worry about odd and even pages.
Therefore, press Ctrl + p on the keyboard. As usual, you will see the print preview. Then click on the “Printer Properties” button (number 1 on the image). Then the printer menu options – this windows looks different for each printer. You might have to search a little bit for that option.
You still want to print all odd or or even pages? The only option is a short VBA macro.
Open the VBA editor by clicking on “Visual Basic” on the left hand side of the “Developer” ribbon. Right click on and worksheet on the left hand side and click on “Insert” –> “Module”. Paste the following code and press start above:
Sub PrintOddOrEvenPages() Dim TotalNumberOfPages, StartPageNumber, CurrentPage As Double TotalNumberOfPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") StartPageNumber = InputBox("There are " & CStr(TotalNumberOfPages) _ & " pages selected in total." & Chr(13) & Chr(13) _ & "Which page do you want to start with?" & Chr(13) & Chr(13) _ & "This number determines wether odd or even pages will be printed. " _ & "Also make sure that the correct printer is selected as the printing will start right away.") If StartPageNumber > 0 And StartPageNumber <= TotalNumberOfPages Then For CurrentPage = StartPageNumber To TotalNumberOfPages Step 2 ActiveSheet.PrintOut from:=CurrentPage, To:=CurrentPage, Copies:=1, Collate:=True Next End If End Sub
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Comments 4
shahzad
can you please tell how i can repeat rows in footer on every page
I have a long excel sheet and i want to repeat the signature area (for me and client) on the bottom of every page, apparently VBA code will be required for this which i dont know anything about.
please help.
Henrik Schiffner
Hi shahzad,
Sorry, but I need to clarify: Do you have many worksheets on which you want to insert the signature area simultaneously or do you have one very large worksheet?
You don’t necessarily need VBA code for that. Select the worksheets you want to add the signature area on, click on “Print Titles” on the “Page Layout” ribbon. Go to the Header/Footer tab and click on “Custom Footer”. Now you can format the footers for all selected worksheets.
Does that help you?
Best regards,
Henrik
shahzad
hi Henrick
very very thanks for your reply
but the problem still persists.
I have 01 sheet, a lengthy one, 4 or 5 pages(some sheets are loner).
last 3 rows of sheet are for name, signature, date.
similarly top few rows consist of headers, now i want that when i print, top rows should be printed on top of every page (this can be done by print titles).
also i want last 3 rows to print on bottom of every page (including formatting) , this i can’t achieve and need your help for this.
thanks for your time
Henrik Schiffner
Hi shahzad,
ok, I think I understand now. As far as I know, you could achieve this on two ways:
1st: You could split the worksheet manually into 5 pages and copy the signature under each of them. This method is not elegant but could work.
2nd: Try to use the printing footers. If some complex formatting can’t be done there, you also have the opportunity to embed pictures into the footer (at least with recent versions of Excel). If your signature is too complex, try to insert it as a picture.
Either way, please let me know if you could make it work. If you find another way, I’d of course also be interested to learn it.
Good luck and best regards,
Henrik