Jordan wins a copy of Excel Outside the Box for this week’s question: “I have a shared workbook with other co-workers. I only need certain columns in the middle of the spreadsheet. I am using the read version because I don’t update the spreadsheet. Even after I deleted the 50 columns to the right of what I needed, the pages remain and I have to go to the end of the page to drag the page all the way to the left to the point at what I need, so I don’t print out 100 copies of blank pages. Is there a way to avoid having to drag the dotted line — other than to specify printing from page 4 to page 14? It does not work because some of the pages in between are also blanks.”
There are several different ways to attack this problem. While you could try any of these methods, the best long-term solution is to have the people with write-access to the spreadsheet take 3 minutes to set up a custom view for you. Until you can convince them to do the custom view, try any of these methods:
- Instead of clearing the data in the 50 columns to the right, hide those columns. Select some cells that span the 50 columns. Use Alt+O+C+H in any version; or Home, Format, Hide & Unhide, Hide Columns in Excel 2007/2010; or Format, Column, Hide in Excel 2003. Hiding the columns should prevent the extra blank pages from printing.
- Reset the print range they’ve defined. Select just the range you want to see. In Excel 2007/2010, use Page Layout, Print Area, Set Print Area. In Excel 2003, use File, Print Area, Set Print Area.
- Use Print Selection to override the print area. Select the range you want to print. In Excel 2010, when you select File, Print, there is an area that says “Print Active Sheets — only print the active sheets”. This area is a dropdown shown in Figure 1. Click the dropdown to see more choices (Figure 2), such as Print Selection. In all prior versions of Excel, Print Selection was located in the Print dialog, clearly visible beneath the From ___ to ___ pages.
Fig. 1
Fig. 2
The easiest long-term solution is to ask someone with write-access to the file to follow these steps to set up a custom view for you. It will not affect his workflow, but it will make your life a tiny bit easier each time you need to print the file.
First, he should set up a view to preserve all his settings. This is really simple and takes a few seconds. Open the file on his machine with write-access. Use View, Custom Views. In the empty Custom Views dialog (Figure 3) click the Add… button.
Fig. 3
Type a name for the original view, perhaps AllData and click OK (Figure 4).
Fig. 4
By setting up the original view, he can easily go back to the print settings that he needs after doing the next steps.
Select the data you want to print. Use Page Setup, Print Area, Set Print Area. Go back to the View, Custom Views, Add…. Create a view called JustXData or Jordan or Subset or anything. This will create a view that you can use.
The person with Write Access should now switch back to the original view. Go to the Custom Views dialog, choose AllData, and click Show (Figure 5).
Fig. 5
At this point, save the file.
The people with write access will always open the file to the AllData view, so they never have to visit the Custom Views dialog again. They can forget all about custom views.
Each time you want to print the file, open the file as read-only. Choose View, Custom Views, double-click the Jordan view, and print. Only your needed columns will print.
Bill Jelen is the author of 33 books about Microsoft Excel, including Pivot Table Data Crunching. Send questions for future articles to the link in the byline for this article. If your question is chosen, you will win a book from CFO and MrExcel.com.