This article was originally written in about 1995 when printing was a key task in any Excel project. In today’s paperless world printing is less of a priority. Without worrying about printing a spreadsheet can be of most any size and actually present more information in a free flowing environment.

So in today’s environment we don’t need to worry about printing?  Maybe, but it’s a short sighted argument as PDF files are now an integral part of our paperless world.  But was is a PDF file? Technically it’s an advanced form of printing to a file.  In other words, we do need to care about printing and all the related issues.  The original article, updated for 2018, follows:

What Range will Print?

Many users are not quite sure exactly how Excel determines what range to print when the Printer Tool used. Here’s a quick review:

  • If you do not highlight a range and click the Print Tool, the entire sheet will be printed.
  • If you highlight a range to be printed and click the Print Tool; the entire sheet will be printed, the highlighting of the range is ignored by Excel.
  • If you highlight a range and click on File Print (in the menu), click on the Selection choice, then the highlighted range will be printed.

By default, Excel prints the entire sheet. Take a look at the Print Dialog box.  Note the selection defaults to Active Sheet(s), which means the entire sheet.

Discussion

There are three choices on what Excel is to print. Active Sheet, the selection (what is highlighted) or the entire workbook.

As mentioned above, Excel prints the entire sheet by default as Active Sheet(s) is always the default.

Clicking on the Printer Tool , is the same as  File Print (in the menu) and, as we have already seen, the entire sheet will print.

The reason for this is that Excel assumes the user has one print range on a sheet and it is saving you time by having Active Sheet(s) pre-selected. If you do no not select a range to print, Excel determines the location of all cells that contains data and prints them. There is no need to highlight the range, Excel knows where all the data is. This is a great feature, assuming you only have one print range!

Now, let’s assume you have two print ranges, perhaps Division 1 Sales in the range A1:G50 and Division 2 Sales in the range A75:G130.  Even though you think of your sheet having two print ranges, Excel thinks you have one: A1:G130. When you select File Print (or click on the Print Tool), both divisions will print (and any blank cells in between. The trick here is to highlight the desired print range and define it as the Print Area.

Now, back to the basic rule that Excel prints the entire sheet. This is not entirely true. If no Print Area has been set, then the entire sheet is printed. But, if a Print Area has been set, then Selected Sheet(s) means print only the Print Area on that sheet.

So now your printing procedures are to:

  • Select the range to print
  • Click on File (in the menu), Print Area, Set Print Area.
  • Click on the Print Tool, only the Print Area will print.

Let’s say that Division 1 is the Print Area. You now select the range for Division 2.  What will print when you click on the Tool Bar?

Division 1 will print as it is the Print Area. The only way to print Division 2 would be to select the range and then click on File Print (in the menu) and click on Selection or set this new range as your Print Area.

In Review

I hope this explanation was not too wordy, but I have seen countless users, both at their workstations and in training classes not understanding these concepts. In summary:

    • Set the PrintArea by clicking on File (in the menu), Print Area, Set Print Area, then all future print jobs will print only that range (on a particular sheet).
    • If you desire to print a range that is not the Print Area, select that range and then select Selection in the File Print dialog box.

Comments

These steps work in Excel 95 and later.  Excel 5 did not have a Set Print area menu selection, making this task more difficult.

To remove the Print Area simply click on  Clear Print  Area menu choice

Print Area is a Defined (range) Name which shows up in the Name List as Print_Area and can be treated accordingly. Other articles discuss Defined Names in more detail.
For those users who use Defined Names the following is good set of procedure for printing:

  • GoTo (Edit GoTo or F5) the desired named range. As a result, the entire range is highlighted.
  • Set the Print Area
  • Print

By getting into this routine, your will work much faster and will spend very little time look for the cells to print and the highlighting is automatic.

Back to creating PDF files.

It’s a good idea to set the print range (Page Layout in the Menu) and then Print Preview. This will help ensure the desired results when creating the PDF file.

 

At the date of this article, Excel 2016 (Office 365) was the most recent Excel version.
7/15/18