Three Must-Know Excel Tools

By Helen Bradley | Posted July 25, 2007

Excel comes loaded with lots of tools to help you work smarter and easier. Today we’ll look at three tools -- Reports, Views and Outlining -- that can make a big impact on your day-to-day work in Excel.

There are times when creating a single worksheet to meet a range of different purposes comes in handy. For example, I might use one as an on screen reference and then adjust the way it looks to print it. If someone else uses it, they might adjust its look differently to suit their specific needs. If you repeatedly change the look of an Excel worksheet to accommodate different uses or preferences, you can make the process simpler by creating and saving different views of it.

Excel View screenshot
Create a View for each way you want to view your data.
(Click for larger image)
.

Excel also offers similar tools that let you configure custom report templates for printing a worksheet report. For example, let's say you print a worksheet summary page in a landscape orientation one day, but you also need to print the same data portrait style for another task. Using Views, you can create custom report templates for each task and eliminate the need to configure the setup manually each time.

And finally, we'll look at the Excel outline tool, which lets you view only that portion of the worksheet that you actually want to see on the screen. In this month’s column

Customize Your View
By using the View option in Excel you can save the look of the Excel screen so you can return to it at a later date. Typical features you can save using the Views command include the worksheet's zoom, its size and placement, hidden rows and columns, print areas, page setup details, grid lines and more. To create a view, first set up the screen the way you want it to look or set up how you want it to print. Next choose View >> Custom Views >> Add and then type a name for your view. Enable the checkboxes for Print Settings and Hidden Rows, Columns And Filter Settings if required and click OK.

Excel View screenshot
If you use Views a lot you can add a Custom View list box to your toolbar.
(Click for larger image)
.

You can create multiple views so that you might, for example, have one that prepares the worksheet for printing and another to display onscreen for data entry complete with Freeze Panes settings in place. To switch between views choose View >> Custom Views, select the view you want to see and click Show.

If you use Views a lot you can add a Custom Views dropdown list to a toolbar in Excel 2003 or earlier. To do this, right click a toolbar, choose Customize >> Commands tab and click the View option in the Categories list. Locate the Custom Views box and drag and drop it onto a toolbar before closing the Customize dialog.

Views that you create automatically appear in the Custom Views list so you can select them from the list when required. Excel saves Views with your workbook so they are accessible next time you open the workbook. Excel also creates Views globally for an entire workbook so that the view you choose includes a reference to the sheet that was active at the time you created the view.

Excel Outline screenshot
When creating an outline manually, work from the top level down.
(Click for larger image)
.

Outline a Worksheet
The Excel Outline tool lets you create your worksheet as a series of collapsible and expandable columns and rows that lets you see as much or as little of the worksheet as you desire. To create an outline, open your worksheet, select the data and choose Data >> Group and Outline >> Auto Outline. If the outline can be created Excel will do so, if not it will give you an error message explaining this.

If you don't get the outline you need from the automatic tool, you can create the outline yourself manually or add the groupings you want to a partially created outline. To do this, first remove any outlining that has been applied incorrectly to the worksheet by choosing Data >> Group and Outline >> Clear Outline. Now select the first range of rows or columns that should be grouped together (so they can be hidden or viewed collectively), and choose Data >> Group and Outline >> Group. Repeat this step for each of the groups that you want to create.

For example if you have a worksheet with data for one-year and half-year totals, you'll start by grouping all the months of the year including the half-year totals into one group. Group the first six months and then the second six months. You might also group the individual income and expense amounts so these rows can be expanded and collapsed.

Excel Outline screenshot
After grouping all the months, you can then group subsets of the data.
(Click for larger image)
.

Test that everything is working as expected by clicking the Collapse and Expand buttons on the far left or across the top of the worksheet. Once you have your worksheet grouped, you can create separate Views for the various options. For example you might configure one view to show only a summary of worksheet subtotals and totals and another to show all the data.

Excel Outline screenshot
With an outline in place you can collapse the data to show only what you want to see and create a view for this if desired.
(Click for larger image)
.

Create Custom Reports
You can use the Report Manager to define custom reports for printing. The reports can contain multiple sheets, and you can use the Views you have set up to configure the sheet for printing. You can also specify that the report be printed using continuous page numbers. Excel saves reports with the workbook, which lets you can select and print a saved report at any time, and you can store multiple reports in a single workbook.

Excel versions 2003 or 2007 don't include the Report Manager. Instead you must download and install the add-in for Excel 2002 from this Microsoft Web site. Download the add-in file and extract and run the file rptmgr.exe. This creates an add-in file called reports.xla, which you will need to place in the Microsoft Office Library folder. Typically you'll find that folder located at C:Program FilesMicrosoft OfficeOffice11Library or C:Program FilesMicrosoft OfficeOffice12Library depending on the Excel version you're using.

Close and restart Excel and, if you're using Excel 2003 choose Tools >>Add-ins. In the Add-ins dialog, select the Report Manager checkbox and click OK. If you have Excel 2007, click the Microsoft Office button, choose Excel Options and then the Add-Ins group. From the Manage list, click Excel Add-ins and click Go. Click and select the Report Manager checkbox and click OK. When it is installed, you will find the Report Manager on the View menu in Excel 2003 and, in Excel 2007, on the Add-Ins tab.

Excel Report screenshot
Before you can use it, you will need to install the Report Manager in Excel 2003 and 2007.
(Click for larger image)
.

To create a report, choose View >> Report Manager >> Add and then type a name for the report. Select the first sheet to print and, if you have created a view that configures the print options for the sheet, click the View checkbox and choose the View from the dropdown lists. Click Add to add this sheet to the report and then continue to add other sheets that should be printed at the same time. If desired, click the Use Continuous Page Numbers checkbox. When you're done, click OK

Excel Report screenshot
Use the Report Manager to configure a report for printing.
(Click for larger image)
.

To print using a report, display the Report Manager dialog, select the report to print and click Print. You can print multiple copies of the report if desired. As with Views, you can create multiple report layouts on a worksheet and these will be saved with your worksheet so they'll be accessible at any time.

These three tools, outlining, reports and views can be used together or individually to build complex automated solutions for viewing your data and configuring screen layout and printing options.

 

Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, HelenBradley.com

Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today!


Comment and Contribute


     

    Get free tips, news and advice on how to make technology work harder for your business.

    Submit
    Learn more
     
    You have successfuly registered to
    Enterprise Apps Daily Newsletter
    Thanks for your registration, follow us on our social networks to keep up-to-date