- Email: [email protected]
Printing in Excel
Chapter
3
Printing in Excel CHAPTER OUTLINE
Exercise 1: Quick Print and Print Preview 48 The Print Preview Dialog 48 Exercise 2: The Page Layout Tab 51 Exercise 3: Header & Footers, and Page Breaks 54 Exercise 4: Documentation and Printing Formulas 56 Appendix: Supplementary Material 56
Even in this so-called paperless office world we still need to print our worksheets from time to time. Although we have hardly started on our study of Excel 2016, this is as good a place as any to discuss the printing process. Topics we shall learn about this chapter include:
• • • • • • • • •
The Print dialog and its options The Print Preview feature that can save paper wastage The Print-Area and how to set it Setting the margins and orientation Setting options such as printing gridlines and row/column headers Getting Excel to print the same rows and or columns on every page Printing a selection Inserting page breaks Changing the paper orientation
Excel will not respond to any print command if there is no printer installed on the computer. That does not mean an actual printer must be attached but rather a printer driver must be installed on the PC or Mac. You can perform most of the exercises in this chapter using Microsoft Print to PDF driver if you do not have a printer. Furthermore, Excel will not print or show a print preview if the worksheet is empty. This is true even if you have added a header or footer.
Liengme’s Guide to Excel 2016 for Scientists and Engineers. https://doi.org/10.1016/B978-0-12-818249-9.00003-0 # 2020 Elsevier Inc. All rights reserved.
The things we can do while working in Excel may be divided into two categories: operations performed on the workbook (e.g., formatting a cell to display 2 decimal places) and those done with the workbook (e.g., printing it, saving it, etc.). The last set of operations are collected together in the File tab and this area is sometimes referred to as the backstage.
47
48 CHAPTER 3 Printing in Excel
EXERCISE 1: QUICK PRINT AND PRINT PREVIEW In this exercise, we look at a way to quickly print a worksheet. Normally, this method is used only when we know that the printing parameters for the worksheet have previously been set, but since we will work with a small worksheet the paper wasted will be minimal. (a) Open Sheet6 of Chap2.xlsx where we did the van der Waals calculations. (b) Print the worksheet by clicking on the Quick Print command on the QAT (see Exercise 1 of Chapter 1 to see how to add it if it is not an option). (c) Retrieve the printout from the printer. Note that on this worksheet the last cell that has an entry is H18. That means that by default Excel will print the range A1:H18 on as many sheets of paper (pages) as necessary. With the default font size of 11, our worksheet will occupy less than one page. (d) If you have added the Print Preview command to the QAT, click on it. Otherwise, click on the File tab to open the “backstage” dialog and select Print from the menu on the left-hand panel. In the future, we will condense this to use the command File / Print. The keyboard shortcut + P, like most windows programs. Fig. 3.1 for the print dialog is shows the backstage dialog with Print selected. (Fig. 3.2 shows the Mac Print dialog. Fig. 3.3 shows the full dialog after show details is pressed.) To the left we have the File menu, in the center is the Print dialog which we explore following, and to the right is the print preview. Compare your printed page with the screen—they should be identical. Return to the worksheet by clicking the arrow in the top left corner.
THE PRINT PREVIEW DIALOG We now take a brief look at the controls on the Print Preview dialog. 1. In the top left corner we see a large button labeled Print; clearly, this is the command to send the print preview to the printer. 2. Next to that is the control for selecting how many copies are to be printed. You can use the scroll arrows or type a number in the box to set this value. 3. The Printer control allows us to select which printer we wish to use— assuming we have more than one printer installed. 4. Under this is text reading Printer Properties. Clicking on this will bring up a dialog specific to the selected printer. For example, depending on the printer, a user might be able to specify that a multiple page
The Print Preview Dialog 49
n FIG. 3.1
5.
6. 7. 8. 9. 10.
output should be printed on both sides of the paper or that everything should be in grayscale rather than color. The first control under the Setting heading allows the user to select from the following: print just the active sheets, print all sheets in the workbook, or print a range that has been selected prior to opening the Print Preview dialog. If the print job has many potential pages, the next control lets the user select which pages to print. The Collated control has meaning only when one is printing multiple copies of a multipage job. Next, we have a control from which we may select either portrait or landscape orientation. Then we have a paper size selection tool. The penultimate control allows us to set the margins on the paper.
50 CHAPTER 3 Printing in Excel
n FIG. 3.2
11. The final control (scaling) is used to make the printout fit onto a specified number of pages. Suppose you have a print job that needs two pages but the second page would contain perhaps three rows from the worksheet. The Fit Sheet control lets us squeeze the print job onto one page. It does this by adjusting the font size on the paper but leaves the worksheet unaltered. Some of the options (including 8, 9, and 10) on the Print Preview dialog are also available on the Page Layout tab which we look at in the next exercise. On the Print Preview dialog, there is a control (not visible in Fig. 3.1) which gives the user access to the Page Setup dialog. If your print job takes more than one page then after you have used Print Preview, Excel adds dotted lines to your worksheet showing the page
Exercise 2: The Page Layout Tab 51
n FIG. 3.3
breaks. The exact position is printer dependent. On the author’s worksheet, the first vertical dotted line was between columns J and K and the first horizontal one, between rows 46 and 47. These automatic page breaks, unlike manually entered page breaks, cannot be removed. If they clutter your worksheet, just close the workbook and reopen it.
EXERCISE 2: THE PAGE LAYOUT TAB In this exercise, we review some of the commands available on the Page Layout tab. Some are duplicates of those on the Print preview dialog while others are available only on this tab. Please be “green” and just observe the
52 CHAPTER 3 Printing in Excel
print preview panel or printing to a PDF document rather than wasting paper with an actual print out.
Note that if you access the Page Setup dialog using the link on the Print preview dialog you will not be able to set the titles since the worksheet is hidden from view.
(a) Open Chap2.xlsx and go to Sheet6. (b) We need to make the worksheet a little larger. Select G8:H18 and drag the fill handle to the right as far as column K. Because we selected two columns when we drag to the right the values in row 8 automatically continue to increase by 10. Select A17:K18 and drag the fill handle down to row 50. Use the name manager to set T to be row 8 and V to be A9:A50 to fix the new formulae. (c) Use File / Print Preview and note how the right panel indicates that 4 pages would be needed for a printout. Return to the worksheet. (d) Locate the appropriate control on the Print Preview and change the orientation to landscape printing. Note how the right panel now indicates that 2 pages would be needed for a printout. In the right panel, click the arrow at the bottom so that you can view page 2; this would not be very informative since it does not display the temperature values; we overcome this in the next step. Return to the worksheet. (e) Use Page Layout / Page Setup / Print Titles (Page Layout / Print Titles on a Mac) to open the dialog shown in Fig. 3.4. We will specify that rows 1–8 are to be printed on each page. This can be done by typing within the appropriate box or by using the range selection button (the icon located at the right of a box; it has a red arrow (dark gray arrow in print version)). Look at the effect in print preview. (f) Next, we shall see how we may print just part of a worksheet. Select A1: J40 and use the command Page Layout / Page Setup / Print Area (Page Layout / Print Area on a Mac) to set the print area to that selection. Observe the effect in Print Preview with Orientation portrait. Page 2 of the printout has only a few rows of data (in addition to the titles in rows 1–8). Open the Scaling control in Print Preview and specify Fit Sheet on One Page or Fit all Rows on One Page and observe the result. The group Scale to Fit on the Page Layout tab given even more control. For example, if we had a large worksheet we could specify, for example, that we want it printed 3 pages wide and 5 pages long. Since this scaling is performed by altering the font size one must be careful to maintain legibility! In addition, the Scale to Fit group has a control to alter the scale. One possible use would be to use a scale greater than 100% so that your printer work is enlarged. You may wish to experiment with this. (g) For the next experiment, we need the worksheet reset to print all the rows down to 50. Use one of these methods: (i) with the command Page Layout / Page Setup / Print Area (Page Layout / Print Area on a Mac) specify Clear Print Area (remember that without a specified Print
Exercise 2: The Page Layout Tab 53
n FIG. 3.4
Area everything on the worksheet gets printed), or (ii) select A1:K50 and set this as the print area using the method of step (f). What would happen if we set the print area as A9:K50? Since we have set rows 1 to 8 to be printed on every page, we would still get everything printed. (h) Again, in the Print Preview dialog, observe how our worksheet, when in landscape mode, occupies the left-hand part of the paper. In the bottom right corner, there are two dialog controls we have yet to mention. The first of these is Show Margins. Experiment with this to expand the left margin such that the data is more or less centered on the paper. The second control magnifies the previewed data and is of limited use. Margins can also be adjusted using Page Layout / Page Setup / Margins (Page Layout / Margins on a Mac) while the worksheet is being viewed. There is a simpler way to center the material on the printed page. Return to the worksheet, open the Page Setup dialog using the launcher and click on the Margins tab. Not only can you adjust the margins but there are also controls to center both horizontally and vertically.
If you click on any cell (say C15) within the range A9:K50 and use + the range A9:K50 will be selected since it is the current range—it is a range surrounded by either a worksheet border or blank cells.
54 CHAPTER 3 Printing in Excel
Warning: Do not leave the setting at Print Selection: it will cause confusion when next you go to print: you may see just one cell in the print preview panel.
(i) Finally, we see how to print just part of a worksheet without altering the Print Area setting. Select row 20 through 25. Open the Print Preview dialog; change the first control from Print Active Sheets to Print Selection and observe the effect on the right-hand panel. You may wonder about the plural in print Active Sheets. It is possible to have more than one sheet active and when this has been accomplished one may wish to print all of the active sheets at once. Also, note there is a setting to print the whole workbook—something one should do only with care.
EXERCISE 3: HEADER & FOOTERS, AND PAGE BREAKS Now we shall pretend that this worksheet (Sheet6) is a homework assignment1; it is to be printed and handed in. So we need some way of identifying the author. (a) In the Page Layout tab click on the launcher of the Page Setup group and open the Header/Footer tab in the resulting dialog—see Fig. 3.5. We could accept one of the footers that Excel has generated (Fig. 3.5) or we could make a custom footer as in Fig. 3.6. Examine the result in Print Preview. We have already seen two ways to adjust margins. We shall see other examples of this helpful redundancy—another example of how Excel often provides more than one way to perform a given task. (b) Close the backstage view to return to the worksheet. To the right of the status bar, just before the zoom slider, there are three controls which alter how the worksheet is viewed: Normal, Print Preview, and Page Break. The same three controls are also available under View / Workbook Views (View on a Mac.) Click the middle control and the worksheet is displayed more or less as it will print—there is no indication of how many pages will print. You will see that this view also allows you to add customized headers and footers. (c) Our worksheet has 42 rows of data (rows 9 through 50) and eight rows (1 through 8) that are used as titles on every page. Let us see how we can get the table printed with equal numbers of rows on each of the two pages when the orientation is landscape. Click the third view control on the status bar to display the worksheet in Page Break mode. Under row 33 (this could be elsewhere depending on the user’s font size and margin 1
Off topic: A spelling error in a worksheet that only you work with is one thing but a spelling error in a printed page is something to be avoided. The quick way to check spelling is to . press
Exercise 3: Header & Footers, and Page Breaks 55
n FIG. 3.5
n FIG. 3.6
56 CHAPTER 3 Printing in Excel
n FIG. 3.7
settings) is a blue line indicating a page break. Drag the line until it is under row 29. Use the first view control to return the worksheet to normal view. Now in File / Print observe the result of changing the position of the page break.
EXERCISE 4: DOCUMENTATION AND PRINTING FORMULAS Occasionally one needs to print a worksheet to serve as documentation. This may or may not involve displaying the cell formulas rather than their values. (a) Open Chap2.xlsx and go to Sheet5 where we computed the equivalent resistance for three resistors in parallel. (b) In the Sheet Options group of the Page Layout tab click the box for Headings Print. This will cause the column heading (A, B, C, etc.) and the row headings (1, 2, 3, etc.) to be printed. (c) To make the formula visible use Formulas / Formula Auditing / Show Formulas (Formulas / Show Formulas on a Mac) or the shortcut + , where the second key is to be found to the left of the on the top row of the so-called typewriter keys. (d) Open the Print Preview dialog. The result will be similar to Fig. 3.7. Clearly, we needed to widen column B so that the last formula is clearer.
APPENDIX: SUPPLEMENTARY MATERIAL Supplementary material related to this chapter can be found on the accompanying CD or online at https://doi.org/10.1016/B978-0-12-818249-9. 00003-0.
3
Printing in Excel CHAPTER OUTLINE
Exercise 1: Quick Print and Print Preview 48 The Print Preview Dialog 48 Exercise 2: The Page Layout Tab 51 Exercise 3: Header & Footers, and Page Breaks 54 Exercise 4: Documentation and Printing Formulas 56 Appendix: Supplementary Material 56
Even in this so-called paperless office world we still need to print our worksheets from time to time. Although we have hardly started on our study of Excel 2016, this is as good a place as any to discuss the printing process. Topics we shall learn about this chapter include:
• • • • • • • • •
The Print dialog and its options The Print Preview feature that can save paper wastage The Print-Area and how to set it Setting the margins and orientation Setting options such as printing gridlines and row/column headers Getting Excel to print the same rows and or columns on every page Printing a selection Inserting page breaks Changing the paper orientation
Excel will not respond to any print command if there is no printer installed on the computer. That does not mean an actual printer must be attached but rather a printer driver must be installed on the PC or Mac. You can perform most of the exercises in this chapter using Microsoft Print to PDF driver if you do not have a printer. Furthermore, Excel will not print or show a print preview if the worksheet is empty. This is true even if you have added a header or footer.
Liengme’s Guide to Excel 2016 for Scientists and Engineers. https://doi.org/10.1016/B978-0-12-818249-9.00003-0 # 2020 Elsevier Inc. All rights reserved.
The things we can do while working in Excel may be divided into two categories: operations performed on the workbook (e.g., formatting a cell to display 2 decimal places) and those done with the workbook (e.g., printing it, saving it, etc.). The last set of operations are collected together in the File tab and this area is sometimes referred to as the backstage.
47
48 CHAPTER 3 Printing in Excel
EXERCISE 1: QUICK PRINT AND PRINT PREVIEW In this exercise, we look at a way to quickly print a worksheet. Normally, this method is used only when we know that the printing parameters for the worksheet have previously been set, but since we will work with a small worksheet the paper wasted will be minimal. (a) Open Sheet6 of Chap2.xlsx where we did the van der Waals calculations. (b) Print the worksheet by clicking on the Quick Print command on the QAT (see Exercise 1 of Chapter 1 to see how to add it if it is not an option). (c) Retrieve the printout from the printer. Note that on this worksheet the last cell that has an entry is H18. That means that by default Excel will print the range A1:H18 on as many sheets of paper (pages) as necessary. With the default font size of 11, our worksheet will occupy less than one page. (d) If you have added the Print Preview command to the QAT, click on it. Otherwise, click on the File tab to open the “backstage” dialog and select Print from the menu on the left-hand panel. In the future, we will condense this to use the command File / Print. The keyboard shortcut + P, like most windows programs. Fig. 3.1 for the print dialog is shows the backstage dialog with Print selected. (Fig. 3.2 shows the Mac Print dialog. Fig. 3.3 shows the full dialog after show details is pressed.) To the left we have the File menu, in the center is the Print dialog which we explore following, and to the right is the print preview. Compare your printed page with the screen—they should be identical. Return to the worksheet by clicking the arrow in the top left corner.
THE PRINT PREVIEW DIALOG We now take a brief look at the controls on the Print Preview dialog. 1. In the top left corner we see a large button labeled Print; clearly, this is the command to send the print preview to the printer. 2. Next to that is the control for selecting how many copies are to be printed. You can use the scroll arrows or type a number in the box to set this value. 3. The Printer control allows us to select which printer we wish to use— assuming we have more than one printer installed. 4. Under this is text reading Printer Properties. Clicking on this will bring up a dialog specific to the selected printer. For example, depending on the printer, a user might be able to specify that a multiple page
The Print Preview Dialog 49
n FIG. 3.1
5.
6. 7. 8. 9. 10.
output should be printed on both sides of the paper or that everything should be in grayscale rather than color. The first control under the Setting heading allows the user to select from the following: print just the active sheets, print all sheets in the workbook, or print a range that has been selected prior to opening the Print Preview dialog. If the print job has many potential pages, the next control lets the user select which pages to print. The Collated control has meaning only when one is printing multiple copies of a multipage job. Next, we have a control from which we may select either portrait or landscape orientation. Then we have a paper size selection tool. The penultimate control allows us to set the margins on the paper.
50 CHAPTER 3 Printing in Excel
n FIG. 3.2
11. The final control (scaling) is used to make the printout fit onto a specified number of pages. Suppose you have a print job that needs two pages but the second page would contain perhaps three rows from the worksheet. The Fit Sheet control lets us squeeze the print job onto one page. It does this by adjusting the font size on the paper but leaves the worksheet unaltered. Some of the options (including 8, 9, and 10) on the Print Preview dialog are also available on the Page Layout tab which we look at in the next exercise. On the Print Preview dialog, there is a control (not visible in Fig. 3.1) which gives the user access to the Page Setup dialog. If your print job takes more than one page then after you have used Print Preview, Excel adds dotted lines to your worksheet showing the page
Exercise 2: The Page Layout Tab 51
n FIG. 3.3
breaks. The exact position is printer dependent. On the author’s worksheet, the first vertical dotted line was between columns J and K and the first horizontal one, between rows 46 and 47. These automatic page breaks, unlike manually entered page breaks, cannot be removed. If they clutter your worksheet, just close the workbook and reopen it.
EXERCISE 2: THE PAGE LAYOUT TAB In this exercise, we review some of the commands available on the Page Layout tab. Some are duplicates of those on the Print preview dialog while others are available only on this tab. Please be “green” and just observe the
52 CHAPTER 3 Printing in Excel
print preview panel or printing to a PDF document rather than wasting paper with an actual print out.
Note that if you access the Page Setup dialog using the link on the Print preview dialog you will not be able to set the titles since the worksheet is hidden from view.
(a) Open Chap2.xlsx and go to Sheet6. (b) We need to make the worksheet a little larger. Select G8:H18 and drag the fill handle to the right as far as column K. Because we selected two columns when we drag to the right the values in row 8 automatically continue to increase by 10. Select A17:K18 and drag the fill handle down to row 50. Use the name manager to set T to be row 8 and V to be A9:A50 to fix the new formulae. (c) Use File / Print Preview and note how the right panel indicates that 4 pages would be needed for a printout. Return to the worksheet. (d) Locate the appropriate control on the Print Preview and change the orientation to landscape printing. Note how the right panel now indicates that 2 pages would be needed for a printout. In the right panel, click the arrow at the bottom so that you can view page 2; this would not be very informative since it does not display the temperature values; we overcome this in the next step. Return to the worksheet. (e) Use Page Layout / Page Setup / Print Titles (Page Layout / Print Titles on a Mac) to open the dialog shown in Fig. 3.4. We will specify that rows 1–8 are to be printed on each page. This can be done by typing within the appropriate box or by using the range selection button (the icon located at the right of a box; it has a red arrow (dark gray arrow in print version)). Look at the effect in print preview. (f) Next, we shall see how we may print just part of a worksheet. Select A1: J40 and use the command Page Layout / Page Setup / Print Area (Page Layout / Print Area on a Mac) to set the print area to that selection. Observe the effect in Print Preview with Orientation portrait. Page 2 of the printout has only a few rows of data (in addition to the titles in rows 1–8). Open the Scaling control in Print Preview and specify Fit Sheet on One Page or Fit all Rows on One Page and observe the result. The group Scale to Fit on the Page Layout tab given even more control. For example, if we had a large worksheet we could specify, for example, that we want it printed 3 pages wide and 5 pages long. Since this scaling is performed by altering the font size one must be careful to maintain legibility! In addition, the Scale to Fit group has a control to alter the scale. One possible use would be to use a scale greater than 100% so that your printer work is enlarged. You may wish to experiment with this. (g) For the next experiment, we need the worksheet reset to print all the rows down to 50. Use one of these methods: (i) with the command Page Layout / Page Setup / Print Area (Page Layout / Print Area on a Mac) specify Clear Print Area (remember that without a specified Print
Exercise 2: The Page Layout Tab 53
n FIG. 3.4
Area everything on the worksheet gets printed), or (ii) select A1:K50 and set this as the print area using the method of step (f). What would happen if we set the print area as A9:K50? Since we have set rows 1 to 8 to be printed on every page, we would still get everything printed. (h) Again, in the Print Preview dialog, observe how our worksheet, when in landscape mode, occupies the left-hand part of the paper. In the bottom right corner, there are two dialog controls we have yet to mention. The first of these is Show Margins. Experiment with this to expand the left margin such that the data is more or less centered on the paper. The second control magnifies the previewed data and is of limited use. Margins can also be adjusted using Page Layout / Page Setup / Margins (Page Layout / Margins on a Mac) while the worksheet is being viewed. There is a simpler way to center the material on the printed page. Return to the worksheet, open the Page Setup dialog using the launcher and click on the Margins tab. Not only can you adjust the margins but there are also controls to center both horizontally and vertically.
If you click on any cell (say C15) within the range A9:K50 and use + the range A9:K50 will be selected since it is the current range—it is a range surrounded by either a worksheet border or blank cells.
54 CHAPTER 3 Printing in Excel
Warning: Do not leave the setting at Print Selection: it will cause confusion when next you go to print: you may see just one cell in the print preview panel.
(i) Finally, we see how to print just part of a worksheet without altering the Print Area setting. Select row 20 through 25. Open the Print Preview dialog; change the first control from Print Active Sheets to Print Selection and observe the effect on the right-hand panel. You may wonder about the plural in print Active Sheets. It is possible to have more than one sheet active and when this has been accomplished one may wish to print all of the active sheets at once. Also, note there is a setting to print the whole workbook—something one should do only with care.
EXERCISE 3: HEADER & FOOTERS, AND PAGE BREAKS Now we shall pretend that this worksheet (Sheet6) is a homework assignment1; it is to be printed and handed in. So we need some way of identifying the author. (a) In the Page Layout tab click on the launcher of the Page Setup group and open the Header/Footer tab in the resulting dialog—see Fig. 3.5. We could accept one of the footers that Excel has generated (Fig. 3.5) or we could make a custom footer as in Fig. 3.6. Examine the result in Print Preview. We have already seen two ways to adjust margins. We shall see other examples of this helpful redundancy—another example of how Excel often provides more than one way to perform a given task. (b) Close the backstage view to return to the worksheet. To the right of the status bar, just before the zoom slider, there are three controls which alter how the worksheet is viewed: Normal, Print Preview, and Page Break. The same three controls are also available under View / Workbook Views (View on a Mac.) Click the middle control and the worksheet is displayed more or less as it will print—there is no indication of how many pages will print. You will see that this view also allows you to add customized headers and footers. (c) Our worksheet has 42 rows of data (rows 9 through 50) and eight rows (1 through 8) that are used as titles on every page. Let us see how we can get the table printed with equal numbers of rows on each of the two pages when the orientation is landscape. Click the third view control on the status bar to display the worksheet in Page Break mode. Under row 33 (this could be elsewhere depending on the user’s font size and margin 1
Off topic: A spelling error in a worksheet that only you work with is one thing but a spelling error in a printed page is something to be avoided. The quick way to check spelling is to . press
Exercise 3: Header & Footers, and Page Breaks 55
n FIG. 3.5
n FIG. 3.6
56 CHAPTER 3 Printing in Excel
n FIG. 3.7
settings) is a blue line indicating a page break. Drag the line until it is under row 29. Use the first view control to return the worksheet to normal view. Now in File / Print observe the result of changing the position of the page break.
EXERCISE 4: DOCUMENTATION AND PRINTING FORMULAS Occasionally one needs to print a worksheet to serve as documentation. This may or may not involve displaying the cell formulas rather than their values. (a) Open Chap2.xlsx and go to Sheet5 where we computed the equivalent resistance for three resistors in parallel. (b) In the Sheet Options group of the Page Layout tab click the box for Headings Print. This will cause the column heading (A, B, C, etc.) and the row headings (1, 2, 3, etc.) to be printed. (c) To make the formula visible use Formulas / Formula Auditing / Show Formulas (Formulas / Show Formulas on a Mac) or the shortcut + , where the second key is to be found to the left of the on the top row of the so-called typewriter keys. (d) Open the Print Preview dialog. The result will be similar to Fig. 3.7. Clearly, we needed to widen column B so that the last formula is clearer.
APPENDIX: SUPPLEMENTARY MATERIAL Supplementary material related to this chapter can be found on the accompanying CD or online at https://doi.org/10.1016/B978-0-12-818249-9. 00003-0.