Excel Views and Reports Easy Retrieval and Review of Multiple Views Helen Bradley
Wed 8/21/02 -- Your instructions: print a worksheet in landscape mode for one department; hide a couple of columns and print it in portrait mode for your weekly report; print just the total (not subtotal) rows for your boss. Then do it again tomorrow and repeat every day of the week. Sounds familiar? Sounds like you need help. This week, we provide it by showing you how to use Microsoft Excel's Custom View and Report tools.
The Office spreadsheet's Custom View feature lets you save a copy of how your Excel screen looks so you can recreate or return to it later by choosing it by name. You can save the size of the window, its position on screen, the zoom percentage, and any titles you may set, as well as filter and print settings. In addition, because a Custom View is created for an entire workbook, it will save a record of the currently selected sheet and any hidden sheets. Custom Views for viewing or printing a workbook are simple to set up, and can achieve with one click what it'd take minutes to do manually.
Create a View. Views are created by example -- you set up the screen the way you want it to look, then tell Excel you want to save the setup. Therefore, begin by organizing the screen; if you plan to use your Custom View for printing rather than perusing data, specify your print settings and if necessary set a print area (select the area to print and choose File/Print Area/Set Print Area). When you're done, choose View/Custom Views/Add. Type a name for your view and enable the checkboxes for "Print settings" and "Hidden rows, columns and filter settings" as required. Click OK.
Returning to a View. You can create multiple Custom Views for a single workbook, each containing its own screen and/or print settings. If you're working on a workbook with multiple sheets, be aware that Views are created globally for the entire workbook and that the active sheet is saved with the view, so use names which describe not only the settings but also the active sheet (if appropriate).
Views are saved along with the workbook. Later, you can load or return to one by choosing View/Custom Views, picking the name from the list, and clicking the Show button.
Office XP's missing manager. Excel's Report Manager offers even more different-perspective power than the Custom Views feature -- but we must begin by saying that you won't find it if you're using Excel 2002. To get it, Office XP users should visit Microsoft's Download Center page to download (165K) and install it.
That done, open Excel and choose Tools/Add-Ins; find the Report Manager in the list, click its checkbox, and choose OK. Now, open the View menu and the Report Manager option should appear there.
Using Reports. Excel's Report Manager allows you to define a Report for printing. The latter can contain multiple worksheets, Views, and/or Scenarios (for a Scenario refresher course, see this May column -- Ed.), and be continuously page-numbered if you like.
Once you've defined a Report, it's a simple process to select and print it, and because Reports are saved with the workbook, you only need to create it once and it'll be available for use at any time. If you combine Reports with Views, you can make custom solutions to meet complex printing requirements as simple to use as picking an option from a menu.
To create a report, first create any View, Scenario, or Scenario Report that you'll be using as part of the Report. Then choose View/Report Manager/Add. Type a name for your Report that identifies what it will contain, then select the sheet that'll be the first one to be printed. If there's a saved View or Scenario that you want to use with this sheet, select the View or Scenario checkbox and then select the appropriate name from the list. Click Add to add this first sheet to the report.
To add other sheets, repeat the process, selecting the sheet and any related View or Scenario and clicking Add. You can adjust the order in which sheets print and opt for continuous page numbering if required. Click OK when you're done.
Printing a Report. As with Views, you can create multiple Reports for a single workbook, each with its own list of sheets to print and special settings (which you can create using Views). To print a Report, choose View/Report Manager, select the report's name from the list, and click Print. Select the number of copies to print and click OK to send job to your printer.
Edit Reports and Views. Need to modify a Report? Choose View/Report Manager, the report to edit, and click the Edit button. You can add more sections (sheets) to the report, or remove any you no longer want to include and alter sheets' print order. To edit a View, select it and click Show. Make your changes then create the View again using the same name; you'll be prompted to delete the current settings and continue -- choose Yes to save the new View in the old one's place.
