How to Format an Excel Spreadsheet for Printing (10 Tips)

In this article, you will learn how to format an Excel spreadsheet for printing. I’m sure these tips will help print your data more professionally.

With Excel Life is Good.

I love that every moment when I use it. It has been a part of my life from last 6 years and I love everything* about it.

Oh yes, the one thing which I don’t like to do in Excel is printing data. Because I have to take care of a number of things.

But that doesn’t mean excel is not capable of printing data effectively. You can print your worksheets and workbooks in a flash just using some printing options.

So today, in this post, I’ll show you 10 most amazing tips which can help you to print your data without a headache.

10 Printing Tips for Excel users

  1. Print Titles
  2. Page Orders
  3. Printing Comments
  4. Scale to Fit
  5. Custom Header / Footer
  6. Center on Page
  7. Select Print Area
  8. Custom Margins
  9. Change Cell Error Values
  10. Start Page Number with a Custom Number

Formatting an Excel Worksheet for Printing

1. Print Titles

This is one of the most useful printing options in excel.

Let’s say you a have heading row in your data and you want to print that heading row on every page you print.

You can do it with print title option. Here are the steps.

  • Go to “Page Layout Tab” -> Page Set Up -> Click on Print Titles.

  • Now in your page setup window go to sheet tab and specify following things.
    1. Print Area: Select the entire data which you want to print.
    2. Rows to repeat at the top: Heading row(s) which you want to repeat on every page.
    3. Columns to repeat at the left: Column(s) which you want to repeat at the left side of every page if you have any.

  • Click OK.

Now, when you print your data, the heading row and left column will be print on every page.

2. Page Order

Page order option is useful when you have a large number of pages to print.

Read More: Dialog Box launcher of Excel Ribbon

Using “page order” option is quite simple. You can specify the page order while printing. Here are the steps.

  • Go to File Tab -> Print -> Print Setup -> Sheets Tab.
  • Now here, you have two options:
    1. The first option, if you want to print your pages using vertical order.
    2. The Second option, if you want to print your pages using horizontal order.

As I said it’s quite useful to use page order option when you have a large number of pages to print, you can decide which page order you want to use.

3. Printing Comments

You can print your comments in a smart way.

Sometimes when you have comments in your worksheet, it’s hard to print those comments in the same manner they have.

So, the better option is to print all those comments at the end of pages.

Yes, you can do this.

Here are the steps.

  • Go to File Tab -> Print -> Print Setup -> Sheets Tab.
  • In print section, select “At the end of the sheet” using comment drop down.
  • Click OK.

Now, all the comments will be printed at the end of the sheet. Just like below format.

4. Scale To Fit

This is also a quick fix to print data in excel.

I’m sure you have faced this problem in excel that sometimes it’s hard to print your data on a single page.

At that point, you can use “Scale To Fit” option to adjust your entire data into a single page. Just follow these steps.

  • Go to File Tab -> Print -> Print Setup -> Page Tab.
  • From here you can use two option.
    1. First, adjust using a % of normal size.
    2. Second, specify the number of pages in which you want to adjust your entire data using width & length.
    3. Click OK.

Using this option can quickly adjust your data into the pages you have specified. But, one thing you have to take care that you can only adjust your data up to a certain limit.

5. Custom Header/Footer

You can apply a number of decent things with custom header/footer.

Well, normally we all use page numbers in header and footer. But with custom option, you can use some other useful things as well.

Here are the steps.

  • Go to File Tab -> Print -> Print Setup -> Header/Footer.
  • Click on custom header/footer button.

  • Here you can select alignment of your header/footer.

  • And following are options you can use.
    1. Page Number
    2. Page Number with total pages.
    3. Date
    4. Time
    5. File Path
    6. File Name
    7. Sheet Name
    8. Image
  • After that click, OK.

Now at every page, you’ll get your custom header/footer.

6. Center on Page

This option is useful when you have less data on a single page.

Let’s say you just have data in 10 cell to print on a page. So you can align them into the center of the page while printing.

These are the steps.

  • Go to File Tab -> Print -> Print Setup -> Margins.
  • In “Center on Page” you have two options to select.
    1. Horizontally: It will align your data into the center of the page.
    2. Vertically: This will align your data into the middle of the page.
    3. Click OK.

You can use this option every time when you are printing your pages as it will help to align your data in a correct way.

7. Select Print Area

This option can save you a lot of time.

Let’s say you want to print data from some specific section of your worksheet. Now, the simple way is to select that range and use the option “print selection”. Right?

But, what if you have to print that range frequently.

In that case, you can specify the printing area. By this, you can print it without selecting it every single time.

Steps are as follow.

  • Select the range of the cells which you want to set as print area.

  • Go to Layout Tab -> Page Set Up -> Print Area -> Set Print Area.

  • Once you do that, that range will get highlighted with a gray border.

And, if you want to remove that printing area, just go back to the option & select “Clear Print Area”.

You can also select more than one printing area. All you have to do just select both of the ranges and set them as a print area.

Important Note: If you have more one print area in your worksheet excel will print them on different pages.

8. Custom Margins

Custom margins can save you a life.

And, here are the steps to easily adjust margins.

  • Go to File Tab -> Print.
  • Once you click on print, you’ll get an instant print preview.
  • Now, from the bottom right slide of the window, click on”Show Margins” button.

  • It will show all the margins applied.

  • You can change them by just drag and drop.

9. Change Cell Error Values

This option is pretty awesome.

The thing is, you can replace all the error values while printing with another specific value. Well, you have only three other values to use as a replacement.

Here are the steps.

  • Go to File Tab -> Print -> Print Setup -> Sheet.
  • Select replacement value from “Cell error as” drop down.

  • You have three options to use as a replacement.
    1. Blank
    2. Double minus sign.
    3. “#N/A” error for all the errors.
  • After selecting the replacement value Click OK.

10. Start Page Number with a Custom Number

This option is a basic.

Let’s say you are printing a report and you want to start page number from a custom number (5). You can specify that number and rest of the pages will follow that sequence.

Here are the steps.

  • Go to File Tab -> Print -> Print Setup -> Page.
  • In the input box “First page Number”, enter the number from you want to start you page numbers.

  • Click OK.

Important Note: This option will only work if you have applied header/footer in your worksheet.

Conclusion

In the end, I just want to say one thing that printing data in excel is not a problem if you know the right way to do it.

I hope all these tips which I have mentioned above will help you print your data easily.

And, if you have any other tip about printing, please share with me in the comment box.

Read More…

[Editor’s Note: This is a guest post by Puneet Gogia, the founder of Excel blog Excel Champs]


I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as an SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet via programming. This will save me a lot of time. I am keen to learn new things.

1 Comment
  1. Reply
    Vikram Narsaria January 28, 2017 at 10:57 PM

    Excellent article Puneet! Very informative. Loved it. This article is a must read for anybody who wants to have in-depth knowledge about printing from excel worksheets. A loved the methods you described of printing titles on every page and printing comments in the end. These are things I am sure most beginners and even intermediate excel users are not aware of. I found it so good that I shared it on my facebook page as well. (You can find it at http://www.facebook.com/MadAboutExcel). Thanks for writing this amazing article.

    Leave a reply