Printer Friendly

Creating Custom Views in Excel.

It's not unusual to have several worksheets where you frequently show data in two different formats. The worksheet in Figure 1, for example, could be printed in landscape mode for a monthly report. Then if you hide all columns except A, E, I, M, Q, and R, you will have a quarterly report that looks best printed in portrait mode. Switching between the reports is time-consuming--you have to hide/unhide columns and change the settings in the Page Setup dialog. This month, we'll look at two different tools that will make it easier to switch back and forth between two different formats for a worksheet in Excel: the View Manager and the Grouping symbols.

[FIGURE 1 OMITTED]

Setting up Custom Views

A custom view will hold the settings for hidden columns, hidden rows, print settings, and filter settings. Once you've defined two or more views, you can quickly switch between the views to change from a monthly to a quarterly report.

Start with all of the months visible. Go to File, Page Setup to define settings appropriate for a wide report. This might include landscape orientation, narrow left and right margins, a custom header indicating this is the monthly report, and so on.

Go to the View menu and choose Custom Views. Initially, there won't be any views defined in the Custom Views dialog. Click the Add button to display the Add View dialog. Give the view a name, such as "Monthly." Leave the boxes checked to indicate that the Monthly view should remember the current print settings, hidden rows, hidden columns, and filter settings (see Figure 2). Click OK to create the first view.

[FIGURE 2 OMITTED]

Back in the Excel worksheet, now set up the report in its quarterly format. Hide the monthly columns in B:D, F:H, J:L, and N:P. In the Page Setup dialog, choose a portrait report, centered horizontally. Edit the custom header to indicate that this is the quarterly report.

Define the new view by selecting View, Custom Views, Add. Give it a name, such as "Quarterly," and click OK.

You can now switch between views by using View, Custom Views. Select either Monthly or Quarterly from the list, and Excel will automatically hide or unhide the monthly columns as appropriate.

If you are using Excel 2003 or earlier, there's a faster way to switch views. Select Tools, Customize. In the Customize dialog, go to the Commands tab. From the Categories list, select View. In the Commands list, "Custom Views ..." will be the sixth item shown. Drag the Custom Views icon onto any toolbar, then close the Customize dialog. You've now placed a dropdown menu that lets you choose between the available views, as shown in Figure 3. Unfortunately, Microsoft removed the Custom Views dropdown from Excel 2007.

[FIGURE 3 OMITTED]

Setting up Custom Groups

Another solution is to set up custom groups in Excel. Groups allow you to quickly show or hide certain rows or columns. The most common place to see groups is after using the Subtotal command in Excel. You can manually set up your own groups.

Select columns B, C, and D. From the Excel 2003 menu, choose Data, Group and Outline, Group. Excel will add some group and outline symbols above row 1 to indicate that columns B:D roll up to column E. To quickly create additional groups, choose columns F, G, and H and press F4 to repeat the last command (Group). Do the same for columns J, K, and L and columns N, O, and P. Remember to hit F4 after highlighting each group of columns in order to repeat the Group command.

You can also collapse rows. Select rows 2:9 and press F4, then repeat for rows 11:17 and 19:26.

You will now have tiny "1" and "2" group and outline buttons to the left of column A and above row 1. Click the "1" button to collapse the detail rows or columns. Click the "2" button to show the detail rows or columns. Figure 4 shows the report after pressing both of the "1" group and outline buttons.

[FIGURE 4 OMITTED]

Custom Views or the Group symbols allow you to quickly hide and unhide sets of columns or rows. The Custom Views take a bit more time to set up, but they add the flexibility of different page setup settings for each view. Using the Group symbols provides some flexibility because they let you quickly expand just one group by using the + or - symbols for that group. SF

Bill Jelen is the host of MrExcel.com and will bring his Power Excel seminar to IMA's 90th Annual Conference & Exhibition. Send questions for future articles to IMA@MrExcel.com.

Excel is a topic at IMA's Annual Conference, June 6-10, 2009, in Denver, Colo. For information, visit www.imaconference.org.
COPYRIGHT 2009 Institute of Management Accountants
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2009 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Title Annotation:EXCEL
Author:Jelen, Bill
Publication:Strategic Finance
Article Type:Report
Geographic Code:1USA
Date:Apr 1, 2009
Words:810
Previous Article:Breaking down boundaries: how finance can help business partnering succeed.
Next Article:Complex data designs.
Topics:


Related Articles
Creating running totals in a pivot table.
Reporting if all records for a department are complete.
Excel4apps releases GL Wand 3.90.

Terms of use | Privacy policy | Copyright © 2020 Farlex, Inc. | Feedback | For webmasters