A coworker approached me recently with a problem. Her stakeholder was resisting the move to a new Excel template – preferring an out-of-date Word version – because it was proving tricky to print everything out quickly, a step he liked to use to review before submitting requests. I helped her streamline the template so that it would be much easier to quickly print the whole thing using the minimum amount of paper.
First, it’s best to optimize your document by ensuring that its contents will fit (more or less) neatly into either a portrait or landscape view.
For example: Extraneous data far off to the side or the bottom should be moved closer to the main content or to another sheet. My friend’s template contained some values that are used for named ranges to populate dropdown lists, which had been stashed many columns off to the side. Depending on how you’re going to print, you could move this sort of thing closer to the main content so it will get printed with the entire workbook. The advantage is that this will not require another sheet of paper, but the challenge is that you don’t want to shrink the contents of that page too much when squeezing it all onto a single page. Another option is to move these fields to a new sheet which can be excluded by not selecting it when you print only the active sheets (more on that in a minute).
My coworker was concerned her stakeholder would just print the whole book every time, so it was better to save a sheet a paper and just move it closer on the same worksheet tab.
Once you have your workbook ready (or if you just want to take a look and see a preview of how it will come out), either go to File, Print or hit Ctrl-P.
You have a little work to do here. Change Print Active Sheets to Print Entire Workbook. Even if you plan to ultimately print just selected worksheets, this will help you go through all of the pages and change the default print options for each.
Now, if your content is more horizontally shaped then vertical (which is so often the case in Excel), switch from Portrait Orientation to Landscape Orientation. At the bottom, change No Scaling to Fit Sheet on One Page:
Your preview will update automatically, so you can see how well this works and make adjustments where necessary. Use the scrollbar on the preview pane to move down to the next page and change the orientation and scaling as needed. Repeat for all worksheets in your document.
Now you can print, if you like. Or if you just want to save your changes to the file or template, click the Save button before going back to the Home tab. Either way, save and your settings will become the default print options for the document – with the exception of Print Entire Workbook. Excel will always default to Print Active Sheets, so you’ll have to train your team and any stakeholders to adjust this setting when printing or teach them to just print the sheets they need.
To selectively print worksheets, choose the sheets you want using Shift-click or Ctrl-Click so that only the sheets you want to print are highlighted. Then go to the Print screen where you can print only the active sheets if you prefer.