Printing is one area in Excel where I find that users don't take full advantage of the options provided by Excel. Below, I present 9 Excel print tips which I am sure you'd like to know to make the task of printing your worksheets seem like a breeze.
So let's begin without any ado.
1. Print Row and Column Titles
This cool tip will help you print the row and column titles of your data on every page when you want to take a print of a large set of data.
Suppose you want to take a print of a large data set. Normally, the row and column titles will be printed only at the beginning of the data and not on the subsequent pages. This can make reading the data in the subsequent pages difficult. You can avoid this trouble by printing your row and/or column titles on every page.
The steps to do this are given below.
a. Go to Page Layout -> Page Setup -> Print Titles
b. Page Setup Window will open. Go to Sheet tab and specify the following:
- Print Area: Select the entire data which you want to print
- Rows to repeat at top: Row titles which you want to print on every page
- Columns to repeat at left: Column titles which you want to print on every page
c. Click OK
Done! Now when you'd print the data, the row titles and/or column titles will appear at the top/left on every page.
2. Printing Comments
Suppose you have comments in your worksheet and want have those comments to be printed along with the other data in the worksheet. Now if you print the comments in the same way as it appears on screen, chances are it will block the contents of some other cells and make your print look clumsy.
A better way is to print all those comments at the end of pages.
The steps to do this are given below.
- Go to File Tab -> Print -> Page Setup -> Sheets tab
- In Print section, select “At the end of the sheet” in the Comments drop-down
- Click OK
Now, all the comments will be printed at the end of the sheet.
3. Scaling
This option solves one of the most common problems that one faces while trying to take prints of Excel documents. You might have been in a situation where you wanted to print the whole document on a single page, but are not able to do so because of the size of the document.
In such situations, you can use 'Scaling' option to adjust your entire data to fit into a single page.
The steps are given below.
a. Go to File tab -> Print
b. In the 'Settings' area, you'll find the option to scale the sheet to fit on a single page. It is set to 'No Scaling' by default. Here you can choose to:
- Fit Sheet on One Page
- Fit All Columns on One Page, or
- Fit All Rows on One Page
Although this is a very useful feature, over-scaling the data to fit on a page can affect the quality of the printed output. So you need use this option diligently.
4. Center on Page
Suppose the data that you want to take a print of is not big enough to cover the whole page. If you want you can take a print of such data in a way that the data gets printed at the center of the page.
The steps are given below:
a. Go to File tab -> Print -> Page Setup -> Margins tab
b. In 'Center on page' area you'll see two options:
- Horizontally: It will align your data into the center of the page horizontally
- Vertically: This will align your data into the center of the page vertically
c. Select one or both the options and click OK
5. Start Page Numbering with a Specified Number
Suppose you want to print an Excel spreadsheet and want to start your page number from a specified number (say 6) in the header/footer. Excel gives you the option to specify that number. The rest of the pages will follow that page number.
The steps to do this are given below:
- Go to File tab -> Print -> Page Setup -> Page tab
- In the input box “First page number”, enter the number from which you want to start you page numbers
- Click OK
6. Change Cell Error Values
Errors don't look good in spreadsheets, even more so when these sheets are printed. Excel provides you the option to replace the errors in your worksheets with one of the 3 options – , — (Double Minus), or #N/A.
Here are the steps.
- Go to File tab -> Print -> Page Setup -> Sheet tab
- Select replacement value from 'Cell errors as' drop down
- Click OK
7. Specifying Page Order for Printing
You can specify whether you want to print your pages in vertical order or horizontal order.
Steps are given below:
a. Go to File tab -> Print -> Page Setup -> Sheets tab
b. In the 'Page order' area, you'll see two options. Select the appropriate one.
- Down, then over (vertical)
- Over, then down (horizontal)
8. Custom Header/Footer
You'd be amazed by the variety of things you can insert in your Headers and Footers. The following is the list:
- Page Number
- Number of pages
- Date
- Time
- File Path
- File Name
- Sheet Name
- Picture
Note that you can insert more than one of these options. You can also format text and image.
The steps to insert custom header / footer are given below.
- Go to File tab -> Print -> Page Setup -> Header/Footer tab
- Click on 'Custom Header…' or 'Custom Footer…' button, as applicable
- In the Header / Footer dialogue box, you can insert one of more of the given options in either left, right or center section of the header or footer. Select your options and apply any formatting changes if you want.
- Click OK
Done! Your custom header and footer would now appear on all the pages.
9. Show Margins
Show Margins option makes it really easy for you to adjust the margins by simply dragging them, thereby eliminating the need to do trials and errors by entering different margin number.
Follow the below steps:
- Go to File Tab -> Print
- Below the print preview, on the bottom right, you'll see a 'Show Margins' button. Click on it. It will show all the margins in the preview.
- Drag the margins as per requirement
Wrap Up
Excel provides more than enough options for you to print worksheets in exactly the way you want to print. I really hope that the above tips will help you print Excel sheets easily.
See ya soon! Happy Excelling!
(This article was first published on the blog of the author - madaboutexcel.com)
CAclubindia
