Chapter 10  Printing and Mailing Financial Statements Reports

About This Chapter

This chapter discusses the techniques you need to print or mail full Financial Statements reports, and includes the following sections:

F       Overview, page 163, introduces printing and mailing techniques.

F       Changing and Customizing the Default Settings in a Workbook,
page 164, describes various techniques you can use to customize your Excel workbook for printing purposes, for example, by adding the date and time into the header, adding a company logo and scaling the print out so that all columns appear on one page.

F       Other Important Printing Techniques, page 170, describes other important techniques that may be useful when printing workbooks, for example, selecting a print area, printing Comments and hiding data.

F       Custom Views, page 179, describes Excel's Custom Views technique, which enables you to save a set of print setup options that is unique for each print area in the worksheet, as well as create a menu of Custom Views.

F       Report Manager, page 181, describes the Report Manager add-in, which lets you create and save as many reports as you need for future printing, as well as how to create an Custom Report Manager by adding a VBA macro.

F       Mailing Financial Statements Reports, page 186, describes how to e‑mail full Financial Statements reports from a new saved and formatted workbook.

Changing and Customizing the Default Settings in a Workbook 

The default print-related settings for a standard Excel workbook may not always fit your needs. If they do not, certain items can be changed before printing and mailing.

Setting the Workbook to Black and White

Ø     To change the black and white settings:

1.       Select Page Setup from the File menu, and then select the Sheet tab.

2.       Select or deselect the Black and white check box, as appropriate.

Correcting the First Page Number

Ø     To set the first page number correctly:

1.       Select Page Setup from the File menu, and then select the Page tab.

2.       In the First page number box, change the setting from Auto to 1 (or the appropriate number, as required).

3.       Make this change in all the worksheets in the workbook.

Changing the Scaling

Scalingis used for precise printing, without blank extra pages and without a column or row wrapping onto an extra page. Scaling to one page wide by one page tall is necessary to avoid printing a blank page in addition to the page you printed.

Ø     To scale the worksheet:

1.       Select Page Setup from the File menu, and then select the Page tab.

2.       In the Scaling area, select the Fit to option button, and then type 1 page(s) wideby 1 tall.

Another common problem during printing is a column being wrapped onto an extra page. For example, if you select a print area that includes columns A to F,column F may be printed on an extra page. For a report with many pages, in which you want to constrain the report to one page wide but allow it to span many pages in height, change step 2 to 1 page(s) wide by ___ (blank) tall.

Adding Information to Headers and Footers on All Printed Pages 

Ø     To add the date, time, file name or worksheet name to the footer:

1.       Select Page Setup from the File menu, and then select the Header/Footer tab.

2.       Click Custom Footer. The Footer window appears.

3.       In the Left section area, click the Date , Time , File  and Tab  icons, as required.

4.       Click OK to close the Footer window, and then OK again.

Ø     To add the fill path to the footer:

F       Follow steps 1 and 2 in the previous procedure, and click the Add Path icon.

Using a Macro to Add Information to Each Worksheet 

In Excel 97 and 2000, you can add a macro that will automatically adds the relevant information, including the path, to appear on each worksheet as you print it from the workbook. When using this technique, the full path for where you saved the workbook will be printed.

Ø     To add the macro:

1.       Press <Alt+F11> to open the Visual Basic Editor.

2.       In the Project pane, double-click the ThisWorkbook module to open it.

3.       Two Combo Boxes, General and Declarations, appear at the top of the module sheet (the right pane). From the General Combo Box, select Workbook.

4.       From the Combo Box on the right, select Workbook_BeforePrint.

5.       In the module sheet, type the following code:

Private Sub Workbook_BeforePrint (Cancel As Boolean)

ActiveSheet.PageSetup.LeftFooter = _

   "&A&F&T&D " & ActiveWorkbook.Path

End Sub

The letters A, F, T and D are the worksheet name, workbook name, time and date, respectively.

In Excel 2002, the middle line of code will be shorter:

6.       Save the file.

Inserting the Company Logo in the Header 

Ø     To insert a logo using Excel 2002 and later:

1.       Select Page Setup from the File menu, and then select the Header/Footer tab.

2.       Click Custom Header. The Header window appears.

3.       In the Left section area, click the Picture icon (the second icon from right). The Insert Picturewindow appears.

4.       Search for and select the company logo or picture you want to add.

5.       Click Insert and then click OK.

Ø     To insert the company logo using Excel 97 and 2000:

1.       Select cell A1.

2.       From the Insertmenu, select Picture and then From File.

3.       Search for and select the company logo or picture you want to add, and then click Insert.

4.       Adjust the picture to the height of the row.

5.       Select Page Setup from the File menu, and then select the Sheet tab.

6.       From Print titles, inthe Rows to repeat at top text box, select .

7.       Select row 1, and then press <Enter>. The range now appears in the Rows to repeat at top text box.

8.       Click OK.

9.       Repeat these steps for each worksheet in the workbook.

Changing the Default Settings for All Worksheets at Once

It is also possible to change the default settings of every worksheet in the workbook at one time.

Ø     To change the default settings of all worksheets:

1.       In the workbook, right-click any worksheet tab and select Select All Sheets from the displayed menu.

2.       From the Filemenu, select Page Setup, and modify the settings, as described in the previous sections.

3.       When you have finished, select the active worksheet again by right‑clicking a worksheet tab and selecting Ungroup Sheets from the displayed menu.

Other Important Printing Techniques

F       FromthePage Setup window, via the Page tab.

F       Manually inserting the page number in a custom footer via the Footer window.

F       Printing a page number in portrait layout on a page, and the data in landscape layout (as described in Printing Page Numbers in a Report Containing both Portrait and Landscape Layouts, page 174).

F       Utilizing Report Manager (as described in Report Manager, page 181).

Selecting the Print Area

Ø     To define a contiguous print area:

F       Select a print area in the worksheet, and from the File menu, select Print Area, and then Select Print Area.

Ø     To define a non-contiguous print area:

1.       Select a range of cells in the worksheet, and then press <Ctrl> while selecting another range of cells.

2.       From the Filemenu, select Print Area, and then Set Print Area.

Each print area will be printed on a separate page.

In Excel 2000 and later, you can join non-continuous ranges to form a single contiguous range to print them as a single print area.

Ø     To add non-contiguous ranges to a single contiguous range for printing:

1.       Select a range of cells in the worksheet, and then press <Ctrl> while selecting another range of cells.

2.       In Excel 2000, the Paste All icon appears on the Clipboard toolbar.

In Excel 2002 and later, press <Ctrl+C+C> to open the Clipboard pane,

OR

From the Edit menu, select Clipboard.

3.       Select a cell and click the Paste All icon. The ranges you copied are pasted in order.

4.       Define these continuous ranges as a single print area, as described in To define a contiguous print area, page 171.

Identifying and Selecting the Print Area in a Worksheet

Each worksheet has only one print area. When you select the print area, Excel creates a name for it, Print_Area. If you do not know what the print area is, select Print_Area from the Name box (to the left of the Formula bar).

Printing Comments

As discussed in Chapter 8, Customizing the Financial Statements.xls Workbook and Presenting Information, Excel lets you add Comments to cells, which is a text box in which you can type free text. Refer to that chapter for information regarding printing Comments as well.

Hiding Data Before Printing

In general, only relevant data should be printed in a report, which means that irrelevant data must be hidden. There are number of ways of doing this:

F       Hiding columns or rows before printing, as described in Custom Views, page 179.

F       Changing the font color in cells whose data should not be printed to white, as described in Hiding Errors in Formulas Before Printing, page 173.

F       Hiding parts of a worksheet by using a white text box without a border. This is done by using the Text Box icon on the Drawing toolbar.

Hiding Errors in Formulas Before Printing

Formulas that return errors should be hidden before a report is printed. This is done by changing the font color to white.

Ø     To hide errors in formulas:

1.       Select the Print_Area from the Name box, and from the Formatmenu, select Conditional Formatting. The Conditional Formatting window appears.

2.       From Condition 1,select Formula Is.

3.       In the Formula text box, type:

=IsError(A1)

4.       Click Format and select the Font tab.

5.       Under Color, select white and click OK.

6.       Click OK.

In Excel 2002 and later, cell errors can be hidden by selecting <blank> from the Cell errors as Combo Box in the Page Setup window's Sheet tab, as shown below.

Figure 10‑1: Cell Errors As Combo Box

Copying Page Settings to Other Worksheets

Changing and updating page settings can take a long time, and it is much easier to copy them from one worksheet to another, even if you still need to change some of the parameters you have defined in the new worksheet.

Ø     To copy page settings to other worksheets:

1.       Select the worksheet whose print settings you want to copy.

2.       Press <Ctrl> and click the worksheet tab of any worksheet you want to copy the page setup.

3.       From the Filemenu, select Page Setup, and click OK. This will cause the page settings from the worksheet selected in step 1 to be copied to all of the selected worksheets.

4.       When you have finished, select the active worksheet again by right‑clicking a worksheet tab and selecting Ungroup Sheets from the displayed menu.

Printing Page Numbers in a Report Containing both Portrait and Landscape Layouts

What do you do if one of the pages in a report is set up in landscape layout, while all the other pages are set up in portrait layout? When all the pages are combined into a single report, the page number that should be at the bottom of the landscape page will not be printed at the bottom, but at the right side (that is, the footer of a page printed in landscape layout). In this case, you must print the page number from a cell in the worksheet, not in the footer.

Figure 10‑2: Profit and Loss Statement

Ø     To print page numbers in a report containing both portrait and landscape layouts:

1.       Select column A.

2.       Right-click and select Insert from the displayed menu. A new column is inserted.

3.       In cell A1, type the page number of the report.

4.       Select the range A1:A30.

5.       Open the Format Cells window by pressing <Ctrl+1>, andselect the Alignment tab.

6.       In the Text alignment area, select Left from the Horizontal Combo Box, and Center from the Vertical Combo Box.

7.       In the Text control area, select the Merge cells check box.

8.       In the Orientation area, change the text orientation to -90 degrees (if the worksheet direction is right to left, text orientation should be +90 degrees).

9.       Click OK.

10.   Select Page Setup from the File menu, and then select the Sheet tab.

11.   In the Print area box, change B1 to A1. The new print area is new A1:N30 (the print area includes the new column).

12.   Select the Margins tab, and reduce the right margin to 0, so that the page number appears at the bottom of the printed page.

13.   Select the Header/Footer tab, and select Custom Footer.

14.   If it appears in one of the sections, delete & [Page], and click OK. The page number is now displayed horizontally and centered in column A.

Inserting a Watermark Behind the Text

Ø     To insert a Watermark:

1.       Right-click a toolbar, and select the WordArt toolbar from the displayed menu.

2.       On the WordArt toolbar, click the Insert WordArt icon.

3.       From the WordArt Gallery, select any example, and click OK.

4.       In the Edit WordArt Text window, type Confidential (or any other text), and select the font and the font size from the Combo Boxes.

5.       Click OK.

6.       Right click the WordArt in the worksheet and select Format WordArt from the displayed menu. The Format WordArt window appears.

7.       Select the Colors and Lines tab and in the Fill box, select Color, and then No Fill.

8.       In the Line box, select Color, and then select a color that is not too light.

9.       Click OK.

10.   Right-click the WordArt and select Order, and then Send to Back, from the displayed menu.

11.   Adjust the object's size and location to suit the worksheet.

Custom Views

Ø     To add a Custom View:

1.       Before defining the Print_Area,hide the rows and columns that you do not want to print.

2.       Define the Page Setup options for the page to be printed, as described previously in this chapter.

3.       From the View menu, select Custom Views. The Custom Views window appears.

4.       Click Add. The Add View window appears.

5.       Type the Name of the Custom View in the Name text box, and click OK.

Printing a Custom View

Custom Views can be printed, as required.

Ø     To print a Custom View:

1.       From the View menu, select Custom Views. The Custom Views window appears.

2.       Select the Custom View you want to print, as shown.

3.       Click Show, and then click the Print icon on the toolbar.

Report Manager

Ø     To install the Report manager add-in:

1.       From the Toolsmenu, select Add-ins.

2.       If the add-in appears in the list of available add-ins, there is no need to install it. Go to step 5.

3.       Click Browse.

4.       Locate and select a file called Reports.xla, and thenclick OK. The Report Manager add-in will now appear in the Add-Ins available list.

5.       Select Report Manager from the list, andclick OK.

Adding and Saving a Report with Report Manager

Report Manager enables you to add and save reports, as required.

Ø     To add and save a report:

1.       From the Viewmenu, select Report Manager, and click Add. The Add Report window appears.

2.       In the Report Name text box, type the name of the report.

3.       In the Section to Add area, open View (or Sheet).It is recommended to print from Custom Views.

4.       Select the first Custom View to add to the report, Balance Sheet.

5.       Click Add. The Balance Sheet Custom View moves to the list at the bottom of the Sections in this Report area.

6.       Repeat steps 2 through 5 to add other Custom Views, as required.

7.       Select the Use Continuous Page Number check box if you want to print continuous numbers at the bottom of the page.

Advantages of Using Custom Views vs. Sheets when Adding a New Report

Choosing from Views (in the Report Manager window) to add a new report using the Report Manager technique, instead of choosing from Sheet, is like buying an insurance policy for safe printing. The pages are printed according to the print page setup that were defined and saved earlier.

Printing, Editing or Deleting a Report

Reports can be printed, edited or deleted with Report Manager, as required.

Ø     To print a report:

1.       From the View menu, select Report Manager.

2.       Select the report you want to print, and click Print.

Ø     To add pages, change the pages' printing order, to delete pages from the report or delete the entire report:

1.        From the View menu, select Report Manager.

2.       Select the report you want to edit, and click Edit.

Ø     To delete a report:

1.        From the View menu, select Report Manager.

2.       Select the report you want to print, and click Delete.

Creating a Custom Report Manager 

Rather than use the Report Manager to print the Financial Statements report, you can add a macro that will operate from a Custom Menu (shown in Figure 10‑4, and described in Chapter 8, Customizing the Financial Statements.xls Workbook and Presenting Information.

Figure 10‑4: Print Financial Statements Reports in a Custom Menu

Ø     To create and add a Custom Report Manager:

1.       Press <Alt+F11> to open the Visual Basic Editor.

2.       In the Project pane, double-click VBAProject(FinancialStatements.xls)

3.       From the Insert menu, select Module.

4.       Select the module, and press <F4>. The Properties window appears.

5.       In the Name row, change the module's name to Printing.

6.       Type the code below into the module sheet:

Mailing Financial Statements Reports

It is simple and easy to send an entire workbook by e-mail. To do this, simply click the E-Mail icon (the fourth icon from the left) on the standard toolbar and send the attachment workbook to the address you want.

To send selected worksheets from an entire workbook, however, is slightly more complex. If, for example, you want to send only worksheets that contain the Financial Statements reports, you should first save the Custom Views you created to a new workbook, and then send the entire workbook by mail, as described below.

Ø     To send selected worksheets from a workbook:

1.       Open a new worksheet, in this example, worksheet 17 – Printing.

2.       Type a list of Custom Views names into column A and the page number to add and print at the bottom of each page in column B, as shown.

3.       Add the following macro to any module:

This macro adds a new workbook, saves each one of the Custom Views based on the list in worksheet 17 – Printing and then saves the new workbook (named Financial Statements) with the current date and time in the same folder as the previously saved Financial Statement.xls workbook. You can now mail the entire new saved workbook to any of your colleagues.