Creating Custom Views in Excel.
[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.
|Printer friendly Cite/link Email Feedback|
|Date:||Apr 1, 2009|
|Previous Article:||Breaking down boundaries: how finance can help business partnering succeed.|
|Next Article:||Complex data designs.|
|Creating running totals in a pivot table.|
|Reporting if all records for a department are complete.|
|Excel4apps releases GL Wand 3.90.|