Why is Printing Such an Issue in Excel?
Most users learn how to print when using a word processor, like Microsoft Word. In a word processor, we are presented typically with a virtual piece of Letter or A4 sized paper.
This means that whatever we place on the virtual paper (text, images, tables, borders, etc.) will map 1:1 (one to one) on the real paper. There is a direct dimensional relationship between virtual paper and real paper. That means, No Surprises. It is the epitome of WYSIWYG (for you youngsters, it means “What You See Is What You Get”).
But that’s not the case with Excel.
You must remember; Excel is not working with a traditional sized piece of paper. Excel is working with 16K columns and 1M rows.
A Bit of Fun With Numbers
So, how big is an Excel sheet? I mean, if you were to print THE WHOLE sheet on a single piece of paper, how large would it be? No margins; no page breaks; just one giant piece of paper.
To keep the math simple, let’s set each cell on the sheet to a 1-inch by 1-inch size (about 2.5cm).
One printed sheet would be
- 1,365.3 feet wide (416 meters)
- 87,381.3 feet tall (26,633.8 meters)
- 119,304,647.1 square feet in area (36,364,056.4 square meters)
- 55 miles long (26.6 kilometers)
If you printed it on 8½ × 11-inch paper (no margins), it would take:
- 272,747,569 sheets of paper
- 545,495 reams of paper
- Would cost $4,091,213.54 in paper (assuming $7.50 per ream)
This doesn’t even factor in the taxes, ink/toner and the myriad of printers you will burn through during the print process.
And that’s just for 1 sheet!!!
Granted, this is just a fun mental exercise, but it showcases the key reason that printing is an issue. The virtual sheet size does not come close to matching the real-world paper size. Excel must slice the sheet the best way it thinks to translate that 16.5 mile-long piece of paper into tiny, manageable sheets.
Taming the Printing Beast
Excel’s default print settings rarely work to your advantage.
The default orientation for printing is portrait, while most users are concerned with fitting as many columns to the page as possible while letting the rows run wild.
The page breaks are based on paper size, margins size, and scaling options.
It is rarely a good idea to print to the printer without viewing the printout in Print Preview mode.
If you don’t like how the printout looks in Print Preview mode, you won’t be any happier when sending it to the real world.
It’s ALWAYS a good idea to preview the printout prior to printing a new document for the first time.
Useful Print Settings to Change
To engage the Print Preview mode, select the File tab to activate the Backstage view. From here, select Print.
The first thing we notice, in the lower-left of the screen, is how many pages out printout will be rendered.
For a report that we believe would look best on a single sheet of paper, this indicates one or more issues.
Most print options can be set either in the Print window of the Backstage or from the Page Layout ribbon.
There are a few settings that can only be activated from the Page Layout ribbon or the Print window (there are even a few you must dig further for), so it’s a good idea to be familiar with all the locations Excel places print options.
The advantage of the Print Preview window is that you get instantaneous visual feedback to each of the settings changes.
When you make the same changes from the Page Layout ribbon, you don’t see any indication of a change to the document. However, once you are familiar with the options, you probably won’t require much hand-holding. Going to the Page Layout ribbon and performing a “click-click-click” through the options is a faster way to set the printout options.
Margins
We can set the margins to one of the supplied preset margin combinations; normal, wide, or narrow.
If we want to get creative, we can select “Custom Margins…” to open the Page Setup dialog box and place us on the Margins tab.
From here, we can set each margin option to whatever is required for the job.
These same options can be accessed from the Margins button on the Page Layout ribbon in the Page Setup group.
Orientation
Having the paper be taller than it is wide (portrait mode) or wider than it is tall (landscape mode) can be set from the Orientation button in the Print Preview window, or from the Page Setup group on the ribbon.
Paper Size
You can select a larger or smaller paper from the Paper Size control in Print Preview, or from the Page Setup group on the ribbon.
Deep-Dive Options
If you click the Dialog Launcher (small, diagonal arrow in the lower-right of the ribbon group), you can access the less-frequently used options.
Some of these you may use quite frequently. It’s different for everyone. But for most users, these don’t qualify for such prominence on the ribbon.
One such option that by all accounts should be prominently displayed on the ribbon is the Center On Page option.
Excel defaults printing to start in the upper-left corner of the sheet. You can center the data left-to-right by centering horizontally or center the data top-to-bottom by centering vertically. You can also center the data on the page by engaging both options.
Gridlines
By default, Excel does not print the original sheet gridlines. It will print any line art you apply to the cells, but the native gridlines are suppressed to save ink and to improve appearances.
If you wish to print the native gridlines, select Page Layout (tab) -> Sheet options (group) -> Gridlines -> Print.
Scaling / Zooming
The Scale to Fit group of controls located on the Page Layout ribbon gives is the ability to restrict the printout to a maximum number of pages both in width and in height.
This is especially useful if you have only a few columns but hundreds or thousands of rows. You can set the Width to 1 and the Height to Automatic.
If you wish to zoom in or out of the data proportionately (without altering the aspect ratio), you can increase or decrease the Scale percentage.
All printouts start at 100% scaling. You can lower this to 10% and raise it to 400%.
NOTE: The Width/Height controls and Scale control are mutually exclusive. Setting one deactivates the other.
Reducing Unused Space
Another way to tighten up unused space is to reduce the height of unused rows, or width of unused columns, to only a few pixels. You may also wish to hide the unused rows and columns.
Printing Parts of a Sheet
In the below example, we wish to print only the list of app names and their 2019 sales. We don’t want to print the entire sheet.
If this is a temporary desire, we can highlight the desired data and select File (tab) -> Print -> Settings and select Print Selection.
If this is a more long-term print need, we can highlight the desired data and select Page Layout (tab) -> Page Setup (group) -> Print Area -> Set Print Area.
This will ensure that the apps and prices are the only part of the sheet that prints, even if we change nothing in the Print Preview window.
If you wish to remove the defined print area restriction, select Page Layout (tab) -> Page Setup (group) -> Print Area -> Clear Print Area.
Page Breaks
What if we want to print our list of apps and prices on one sheet and the charts on a separate page?
To ensure there are no external influences on our scaling, set the Width and Height controls to Automatic.
Adding Page Breaks
Select an empty cell below the table of apps and prices (i.e. cell A20) and select Page Layout -> Page Setup (group) -> Breaks -> Insert Page Break.
Notice the line above the selected cell indicating the page break position.
You can repeat this as many times as you like, isolating sections to new pages.
Removing Page Breaks
To remove a manually inserted page break, select an empty cell below the page break you wish to remove and select Page Layout -> Page Setup (group) -> Breaks -> Remove Page Break.
If you wish to remove all manually assigned page breaks, select Page Layout -> Page Setup (group) -> Breaks -> Reset All Page Breaks.
Graphical Page Break Adjustments
To use the mouse to move page break assignments up/down/left/right, select View (tab) -> Workbook Views (group) -> Page Break Preview.
This displays our printout with solid, dark blue lines indicating page break positions we set and dashed blue lines indicating page breaks to which Excel must conform based on printer restrictions.
Depending on where you position the blue lines, Excel will increase or decrease the scale of each are to fit within the defined print area.
This will no doubt require a bit of trial and error to get the perfect look for your printout, but with practice, it will go quickly.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
