add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
For most Excel users, printing is one of their least favorite features of Excel – to put it mildly. This chapter discusses all the issues, large and small, most of which are annoying. A thorough understanding of these issues will dramatically reduce the time you spend on printing.
Adding Icons to Toolbars
The Standard toolbar includes two icons that are related to printing, the Print icon and the Print Preview icon.
Other important icons that are not on the toolbar include the following:
© Page Setup
© Set Print Area
© Custom Views
Add these important additional icons as follows:
1. Use the mouse to right-click one of the toolbars.
2. From the shortcut menu, select Customize.
3. From the Commands tab, select the File category.
4. In the Commands dialog box, click and drag the Page Setup icon onto the Standard toolbar.
5. In the Commands dialog box, click and drag the Set Print Area icon onto the Standard toolbar.
6. Select the View category, and in the Commands dialog box, click and drag the Custom Views icon onto the menu bar. (You can add icons to the menu bar, and it is worth using it to add wide icons.)
Because of the first two icons you added, you will not need to use the File menu to access Print and Set Print Area.
For an explanation of the importance of the Custom Views icon, see the Custom Views section later in the chapter.
Changing and Customizing the Default Settings in a Workbook
Below are the default print-related settings for a standard Excel workbook that can be changed to suit your needs. (See also Chapter 13, Customizing Excel for customizing a workbook template.)
© Headers and Footers – the default settings do not include printing the name of the workbook, the name of the sheet, or the date and time of printing.
© Black and White, in the Sheet tab – most printers still print in black and white, but the default setting for printing a workbook is not black and white.
© First Page Number, in the Page tab – the default setting for the first page number is automatic. Change the default to 1. The reason – there is a bug in the automatic numbering for printing reports from the Report Manager. See below for details.
© Scaling, in the Page tab – generally, you print a single page or a collection of single pages (see Customize Views later in this chapter). Change Scaling to Fit to: 1 page(s) wide by 1 tall.
© Inserting a logo into the header during printing – see the explanation below.
Changed default settings are saved as a workbook template (see Chapter 13, Customizing Excel). Using a template lets you save the changes to the default settings for repeated use.
Changing the default settings for all sheets
1. In the workbook, select the tab for one of the sheets.
2. Right-click, and choose Select All Sheets.
3. From the File menu, select Page Setup.
4. Change the default settings as described below.
5. When you have finished changing the defaults, select the active sheet again. Right-click, and from the shortcut menu, select Ungroup Sheets.
Adding information to headers and footers on all printed pages
1. From the File menu, select Page Setup.
2. Select the Header/Footer tab.
3. Select Custom Footer.
4. Select Left section.
5. Click the icons Date, Time, File, Tab.
6. Click OK.
Adding the full path of the saved file
Excel 2002 lets you add the full path for where you saved the file on your computer or network. At step 5, click the Add Path icon. See the figure of the Footer dialog box.
New
In 2002
Using a macro to add data to each sheet printed from the workbook
Add a macro command that will automatically cause information, including the path, to appear on each sheet you print from the workbook. When using this technique, the full path for where you saved the workbook will be printed in Excel 97 and 2000.
1. Open the VBE macro editor, and press Alt+F11.
2. In the Project Explorer, double-click the ThisWorkbook module.
3. Add an event called Workbook_BeforePrint.
4. In the event, type code as follows:
The letters A, F, T, D = the name of the sheet, name of the workbook, time and date.
5. In Excel 2002, the middle line of code will be shorter:
6. Save the file.
Inserting a picture (company logo) in the header
1. Select the Header/Footer tab.
2. Click Custom Header.
3. Select Left area.
4. Click the Picture icon (second from right). In the Insert Picture dialog box, search for and select the logo or picture you want to add.
5. Click Insert.
6. Click OK.
New
In 2002
Inserting a picture (company logo) in Excel 97 and 2000
1. Select Cell A1.
2. From the Insert menu, select Picture, From File.
3. Select the picture you want, and then copy and paste it into Cell A1.
4. Adjust the picture to the height and width of the row.
5. From the File menu, select Page Setup.
6. Select the Sheet tab.
7. Select Rows to repeat at top.
8. Select Row 1.
9. Click OK.
10. Repeat these steps for each sheet in the workbook.
Black and white printing
From the Sheet tab in the Page Setup dialog box, select Black and white.
First page number
From the Page tab in the Page Setup dialog box, change First page number to 1 or whatever number is appropriate.
Scaling
From the Page tab in the Page Setup dialog box, select and adjust the option Fit to: 1 page(s) wide by 1 tall.
For long reports where you wish to constrain the report to 1 page wide but allow it to span many pages in height, change this to 1 page wide by ___ (blank) pages tall.
Important Printing Techniques
Printing the page number and the running page number
Excel offers a number of options for printing a running page number.
© Page Setup using the Page tab.
© Manually inserting the page number in Custom Footer.
© Printing a page number in portrait layout on a page in landscape layout (see page 176).
© Utilizing Report Manager (see page 186).
Selecting the print area
An Excel sheet can have one continuous print area or a number of non-continuous print areas.
Defining a contiguous print area
Select a print area in the sheet, and click the Set Print Area icon (if you added it to the toolbar), or from the File menu, select Print Area, Select Print Area.
Defining a non-contiguous print area
Select a range of cells in the sheet, press Ctrl, and select another range of cells while holding the Ctrl key down. Click the Set Print Area icon, or from the File menu, select Print Area, Set Print Area. Each print area will be printed on a separate sheet.
Tip – Adding non-contiguous ranges to a single contiguous range for printing
In Excel 2000 and 2002, you can join non-continuous ranges to form a single contiguous range in order to print them as a single print area.
The Paste All icon is new addition to Excel 2000 and 2002.
Copy two or more separate ranges of cells separately. In Excel 2000 the Paste All icon appears on the Clipboard toolbar. In Excel 2002, press Ctrl+C+C to open the Clipboard pane, or from Edit, select Clipboard.
Select a cell and click the Paste All icon. The ranges that you copied are pasted in order. Now, define these continuous ranges as a single print area and print.
Unprinted blank pages
Blank pages in the defined print area will not be printed.
New
In 2002
Tip – Identifying and selecting the print area in a sheet
Each sheet 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).
Repeating rows and columns at the top of each page (Sheet tab)
Repeat a row at the top of each page to repeat the printing of data in the cells of a row, or series of rows, that you select.
Sound complicated? Below is an example to demonstrate the technique.
Example:
An Excel sheet contains a list of a company’s customers. In the cells of the first row, you listed the headings customer name, contact person, address, telephone number and fax number. The list has 1,000 rows, and the print area that you defined is A1:E1000.
Select the Sheet tab, and in Rows to repeat at top, select Row 1 of the sheet.
The result – the top of each printed sheet will have a header row. After the header row, the text rows of the sheet are displayed.
Row and column headings (Sheet tab)
Select Row and column headings to print the sheet’s headers, number of rows, and the letters A, B and C as the column headers.
Printing comments (Sheet tab)
Select Comments, At end of sheet to print the comments on an extra page with references to the cells containing the comments. If you select the As displayed on sheet option, the comments will be printed as they appear in the sheet. See the Adding Comments to a Formula section of Chapter 10, Information.
Hiding data before printing
Generally, you print the relevant data in a report. To print only the relevant data, you have to hide the irrelevant data.
Methods for hiding data
© Hide columns or rows before printing. See the explanation below of automatically hiding rows and/or columns by using Custom Views.
© For cells whose data should not be printed, change the font color to white.
© Hide parts of sheets by using a white text box that does not have a border. You can find the Text Box icon on the Drawing toolbar.
Hiding errors in formulas before printing
1. Select the print area, and from Format, select Conditional Formatting.
2. In Condition 1, select Formula Is. In the Formula box, type =IsError(A1).
3. Click Format and select the Font tab. Under Color, select white and click OK. In the Conditional Formatting dialog box, click OK again.
Hiding cell errors while printing
In Cell errors as, select one of the four options for hiding errors or printing cell errors, and the type of error to be printed.
New
In 2002
Scaling (Page tab)
Use Scaling for precise printing, without blank extra pages and without a column or row wrapping onto an extra page. Scaling to 1 page wide by 1 page tall is necessary to avoid printing a blank page in addition to the page you printed.
Problem: A common problem during printing is a column being wrapped onto an extra page. For example, you selected a print area that includes Columns A to F; you printed the data; and Column F is printed on an extra page.
Solution: Type 1 for Number of pages wide. Clear the box for Number of pages tall.
Continuous numbering of pages, first page number
If you want to print two pages from Sheet1 and three pages from Sheet2 and have them consecutively numbered from page 1 through page 5, be sure to print the entire workbook at one time. Use File, Print and select Entire Workbook in the Print what section of the Print dialog.
If you absolutely need to print individual worksheets and keep consecutive page numbering, then you can go to the Page Setup for Sheet2 and on the Page tab, change First page number from Auto to 3.
Note that if you are using Report Manager, you will want to change First page number from Auto to 1 to overcome a bug in the Report Manager.
Copying page settings to other sheets
Changing and updating the page settings takes a lot of time, so you certainly will want to copy page settings from one sheet to another, even if you still need to change some of the parameters you have defined in the new sheet. You will still save a lot of time by copying the page settings from one sheet to another sheet or sheets.
1. Select the sheet whose print settings you want to copy.
2. To select all the sheets in the workbook, select the sheet’s tab, right-click, and choose Select All Sheets.
3. From the File menu, select Page Setup, and click OK. This will cause the page settings from the sheet selected in step 1 to be copied to all of the selected sheets.
4. To cancel the selection of the sheets, select the active sheet’s tab, right-click and select Ungroup Sheets.
Printing page numbers in a report containing both portrait and landscape layouts
Problem
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 (the footer of a page that is printed in landscape layout).
Solution
Print the page number from a cell in the sheet, not in the footer.
Example: A Profit and Loss Statement has 13 columns (A:M). The Profit and Loss Statement is part of a report with a large number of pages in portrait layout, but the Profit and Loss Statement is in landscape layout.
1. Select Column A (see the figure at the end of the explanation).
2. Right-click, and from the shortcut menu, select Insert.
3. In Cell A1, type the number 5 (assuming that 5 is the number of the page in the report).
4. Select the range A1:A29.
5. Press Ctrl+1 (Format Cells).
6. Select the Alignment tab.
7. In Text Alignment, Horizontal, select Left (in Excel 97, select Right). In Text Alignment, Vertical, select Center.
8. In Text Control, select Merge cells.
9. In Orientation, change the text orientation to -90 degrees (if the sheet direction is right to left, text orientation should be 90 degrees).
10. Click OK.
11. Update the print area – from the File menu, select Page Setup.
12. Select the Sheet tab.
13. In Print Area, change B1 to A1. The new print area is A1:N29 (the print area includes the new column).
14. Select the Margins tab.
15. Reduce the right margin to 0 (so that the page number will appear at the bottom of the printed page).
16. Select the Header/Footer tab.
17. Select Custom footer.
18. Delete Page (if it appears in one of the sections).
19. Click OK.
The result - the page number, 5, is displayed horizontally and centered in Column A.
Page break
When you set the print area in a sheet that has more than one page to be printed, Excel divides the print area over individual pages. This division is done according to the Page Setup definitions, including margins, scaling, width and height of columns and rows, and also according to the size of the printing paper. It is best for you to manually insert a page break between the columns that will be printed, according to the subjects of the columns.
Look at the automatic page breaks. From the View menu, select Page Break Preview. In the figure below, the report is divided into two parts, a Profit and Loss Statement (C3:P27) and an appendix (C32:P37) that details general and administrative expenses.
In the example, the print area (C3:P37), which includes all the data in the appendix, is divided over two pages. Each printed page will include the appropriate header (repeated rows at the top of the page) and a page break at the appropriate place to separate the Profit and Loss Statement from its appendix.
1. From the File menu, select Page Setup.
2. In the Sheet tab, enter the Print Area as C3:P37.
3. In the Rows to repeat at top box, select Rows 3:7.
4. In the Page tab, set the Scaling to Fit to: 1 page(s) wide by 2 tall.
5. From the View menu, select Page Break Preview.
6. Select Cell C28. This cell actually marks the separation between the Profit and Loss Statement and its appendix.
7. From the Insert menu, select Page Break.
8. From the View menu, select Normal to return to the normal view.
Removing or changing a page break
To manually change a page break, in Page Break Preview, drag the blue line (either solid or dotted) to another location. To remove the page break that you created by selecting Cell C28, select Cell C28 again, and from the Insert menu, select Remove Page Break.
Removing page breaks from the sheet
To remove all page breaks from a sheet, select all the cells in the sheet (use the keyboard shortcut Ctrl+A, or click the Select All button at the corner of the sheet’s headings). From the Insert menu, select Reset All Page Breaks.
Inserting a watermark behind the text
Reports such as a company’s financial statements are, by their very nature, confidential. Insert the text “Confidential” behind the data in the report in such a way that it does not interfere with reading the report.
Inserting a watermark
1. Display the WordArt toolbar. Select one of the toolbars, right-click, and select the WordArt toolbar.
2. On the WordArt toolbar, click the Insert WordArt icon (A).
3. From the WordArt Gallery, select any example, and click OK.
4. In the WordArt Edit Text dialog box, type Confidential (or any other text), and select the font and the font size.
5. Right-click the WordArt, and from the shortcut menu, select the Colors and Lines tab.
6. In Fill, select Color, No Fill.
7. In Line, Color, select a color that is not too light.
8. Click OK.
9. Right-click, and from the shortcut menu, select Order, Send to Back.
10. Adjust the object’s size and location to suit the sheet.
Printing objects
Excel allows you to include objects (dropdown menus, buttons, text boxes, arrows and more) in a sheet for a variety of purposes. It also allows you to decide if you want them to be hidden when you print or not.
There are a number of ways to prevent objects from being printed. Select File, Page Setup, Sheet. Select the Draft quality checkbox, and click OK.
Right-click the object, and select Format Object type. Select the Properties tab, and select the Print object checkbox if you want to print it.
Tip – Make printing faster
Objects make printing go slower, so you may want to temporarily delete them before printing by pressing Ctrl+6+6. After printing, press Ctrl+6 to reinsert the objects into the sheet.
Selecting print options
With Excel, you can print whatever print areas you choose. From the File menu, select Print. In the Print dialog box, you can print whatever area you want, without changing the Print_Area of the sheet, by using the Selection option (remember, Print_Area is the last print area that was defined).
Select the Entire workbook option to print the print areas of all the sheets in the workbook.
To print a number of reports regularly, use the Report Manager (see below).
Custom Views
A full report consists of a combination of individual sheets. Different print options are defined for each sheet. It is a waste of time to begin redefining print options. It is much more effective and efficient to save print options for repeated use.
Custom Views allows you to save a set of print options that is unique for each print area in the sheet and create a menu of views that let you print any page at any time without redefining the Page Setup options for the page.
Inserting 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.
3. From the View menu, select Custom Views.
4. Click Add.
5. In the Add View dialog box, type the name of the view in the Name box.
6. Click OK.
When you save the view, the defined print options, rows, columns, and hidden filter options are saved. See Chapter 18, Filtering.
Tip – Unhide rows and columns by using Custom Views
To quickly unhide hidden rows and columns, insert a Custom View for the sheet with the rows and columns unhidden.
Printing a custom view
1. Select the view you want to print from the Custom Views dialog box.
2. Click Show.
3. Click the Print icon.
Adding a custom views icon
At the beginning of the chapter, see Adding Icons to Toolbars.
Adding a custom view quickly
You can type the name of the view you created directly into the drop-down list in the icon. Press Enter after typing the name of the view.
Deleting a custom view
From the View menu, select Custom Views, select the name of the view, and click Delete.
Custom views are saved at the workbook level
Custom views are saved in the active workbook. You are not supposed to remember which sheet has the view you want to print. When you select the view, you will select it in the sheet in which it is saved, and that sheet’s page setup will change in accordance with the page setup that was saved in the view.
Caution
The custom view you saved is inflexible, and cannot be edited easily. Every little change requires deleting the view, making the change, and resaving the view.
Report Manager
Excel lets you save pages or views that are connected to entire reports and print them any time you choose. Use the Report Manager add-in to create and save the reports.
Installing the Report Manager add-in
The Office XP (2002) CD does not include the Report Manager add-in. To install it in your computer, you must download the add-in from the Microsoft Web site.
The site’s address is: http://office.microsoft.com/downloads/2002.
Excel 97 and 2000 include the add-in. The name of the file is Reports.xla. If you have an earlier version of Excel, you can install the add-in in Excel 2002 without downloading the file from the Microsoft Web site. The add-in is the same for all versions of Excel.
Installing the add-in
1. From the Tools menu, 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 6
3. Click Browse.
4. Locate and select a file called Reports.xla.
5. Click OK.
6. In Add-ins Available, select Report Manager.
7. Click OK.
Adding a report and saving in Report Manager
1. From the View menu, select Report Manager.
2. Click Add.
3. In the Report Name box, type the name of the report.
4. From Section to Add, open View or Sheet.
5. Select the first view to add to the report Profit and Loss Statement.
6. Click Add. The view Profit and Loss Statementmoves to the white list box at the bottom of the Sections in this Report box.
7. Use this method to add other views as necessary.
8. Select the Use Continuous Page Number box if you want to print continuous numbers at the bottom of the page.
Problem
There is no way to set the first page number or to number additional pages (for example, a page added from the Word program) so that it will print in the report. The first page that is printed will be numbered 1.
Solution
Insert additional views into the report (for example, reinsert the Profit and Loss Statement view), and use the report with the correct number in the footer (you may destroy the extra pages with the incorrect numbers).
Note
You can use Report Manager to control automatic numbering if you change the default setting of First page number from Auto to a number.
From the File menu, select Page Setup, Page tab, and change First page number from Auto to a number. Make this change in all the sheets you want to print.
Using custom views to add pages to reports
Using Custom Views to add a report is like buying an insurance policy for safe printing. The pages are printed according to the print options that were defined and saved earlier.
Printing, editing or deleting a report
1. From the View menu, select Report Manager.
2. Select the report you want to print.
3. Click Print.
To change a report, or add, close, or arrange printing the pages of a report, click Edit.
To delete a report, select the report and click Delete.
Creating a custom Report Manager by writing a small program in VBA
Using the Report Manager to print reports is not a good solution for printing reports from single or multiple workbooks and does not provide an automatic solution for custom numbering of pages.
You can create a print manager by using a macro (this works for Excel 97, 2000 and 2002).
Column A – This column contains numbers between 1 and 3: print from sheet, print by range name, or print from custom view (recommended).
Column B – Type the name of the sheet, range name (be sure to type the exact name of the range including the underscore. To make this simpler, paste the list of names by creating a shortcut with F3 and copying the name) and name of custom view.
Column C – Type the page number to be printed in the footer.
The macro will print from a sheet and automatically add the necessary information into the footer, including page number, workbook name, path, sheet name, date and time of printing.
Explanation and Comments:
1. The loop in the macro causes a separate print for each cell in column A starting at A2.
2. In the loop, the print area is selected using the Select Case technique.
3. The information printed on the left side of the footer: &08 = 8 point font, &D = Date, &T = Time.
4. The macro provided here only prints pages in the current workbook. You can add the option to print from other workbooks, even closed workbooks. Add two new columns, one for path and one for file name. See Chapter 28, Other VBA Techniques to see how to open a closed workbook and how to use the function to determine if the workbook is open or closed.
5. To run the macro, add a button to the sheet and attach the macro to it.
6. You can use this technique to add an unlimited number of reports.