1.5 Configure Worksheets and Workbooks for Distribution

1. 1.5.1. Set a Print Area

Printing worksheets You may not want to print the complete worksheet, but only a selected portion of it. In order to do so, Excel provides an option to specify a range of cells as the print area, which will ensure that only this section of the worksheets is selected for printing. To do so, you just select the part of the worksheet you want to print and then go to the Print Layout tab and click on the Print area pull down. There are only two options here: to set or to clear the print area. When I go to the Print command on the backstage, you can see that only that area of the worksheet is shown in the Print preview area. You can clear the print area by pressing the Clear Print Area command. An alternative way to print only a selection of the worksheet is to select what you want to print and, on the print options in the backstage, use this pull domain to specify that you only want to print what has been currently selected.

2. 1.5.2 Save Workbooks in Alternative File Formats

We saw in Objective One. In addition to showing how to import text data into an Excel file, Excel also shows how to reverse the process and export data to other file formats in the Save as dialogue box. The Save as type dropdown list includes an extensive list of different formats for saving the worksheet data, many of which you will never have to use. Saving an Excel file in a different format is just a matter of selecting the type that you want. For example, if I want to create a PDF file, then I just specify PDF as a file type, which includes both PDF, which is an Adobe file format, and CSV, which stands for Comment Separated Values, or Comment Delimited File, which are two commonly used formats. On some occasions, you may get a warning message telling you that some features of the workbook may be lost if you save to the selected file format. This is true of CSV file types, which only contain text data. This means that when saving a workbook in the CSV format, Excel will discard anything that is not pure data. Therefore, your CSV workbook should not use Excel features such as formulas, cell formatting charts, and multiple worksheets. Excel will display the following question as a warning. Accept that this is the case and press yes. When saving the PDF, you'll have options as to which information you want to retain. If I select the Options button, I get this dialogue box, which allows me to specify what pages I want to print and whether I want the whole workbook to be saved. As a PDF file, it's important to note that the default is set to only save the current worksheet, so if you want the complete workbook to be saved as a PDF, you have to modify the default options here. I can also specify whether I want document properties and other nonprinting information to be saved with the file.

3. 1.5.3 Print All of Part of a Workbook

To print in Excel, you need to go to the File tab and select the Print command in the backstage view. like so. There are three options to select from: print the current active worksheets, print all worksheets in the entire workbook, or Print the currently selected range of cells. In the currently active workbook sheets, you usually have only one worksheet as your Active worksheets. When you print the active sheets, Excel will only print the contents of that worksheet. If you want to print more than one worksheet but not the entire workbook at the same time, then you must select all of these worksheets as a group first and then print them. For example, if I want to print both of the worksheets in the Helpless Airlines workbook, then I have to select both worksheets first by holding down the Control key and clicking on each of the worksheets. This allows me to have multiple worksheets selected at any one time. I can now print both worksheets simultaneously. If I just want to print a selection from a worksheet, then I just select what it is that I want to print and then specify the selection option from the Print command.

4. 1.5.4 Set Print Scaling

For scaling features in Excel, you can use the Scale to Fit group on the Page layout tab, or you can use the print settings available for the Backstage Print command. Let's look at the page layout options first. Here you can specify the number of pages wide and the number of pages tall that you want to print. For example, if I specify two pages wide by two pages high, we can see the effect in the Print preview area. Notice that this is actually a custom scaling setting.

As you can see from this dialogue box, the scaling percentage can be changed to fit more information into a smaller space. So if I set this at 50%, that means that I can fit a complete page into 50% of the usual space. Because the page has been shrunk in size, the page has been scaled down. In this case, if I click on the expanded option here, you can see that the dialogue box provides an option to force the printing to fit to a specified number of pages. You can also adjust the scaling for printing by using the print commands from the backstage view. There are several options for your print settings, but if you go to the bottom of the list, you will see the Scale to Fit option. By default, Excel sets the option to no scaling. The pull down menu provides a number of other options.

We have fit on one page, fit all columns on one page, fit all rows on one page, or customized scaling options fit. One page is great if we don't have so much data on the worksheet. If you do, then the text won't be readable because it will have been scaled down to such a small size like this. The print orientation has been set to landscape and currently the print preview shows 13 pages. Using the Scape to Fit feature provides us with four options to choose from. We select the Fit All columns on one page and see the effect. Let's review step by step. So go to the final menu on the ribbon, click on the Print command, open the Scale to Fit dropdown box, and then select Fit All columns on one page. To the right of the print settings is a print preview. Notice that the page numbers went from 13 to three. If you use the next button to page through, you can see that each page includes all of the columns. Sometimes it's easy to select the first option, which is to fit on one page. In our example, if you would have chosen that option, the content would have been so tiny that it would have been impossible to read the print out. Keep something in mind when we make changes using the scaling options: we're modifying only the printer out. Changing the options in the Scale to Fit tools does not affect the standard spreadsheet view on the screen.

5. 1.5.5 Display Repeating Row and Column Titles on Multipage Worksheets

In Objective 1.4.4, we see how we can freeze the display of rows and columns on the screen in order to keep information such as headings on display all the time while we're scrolling through a worksheet. This feature only applies to the screen when you want to do the same thing for printing. Then you use the Print Titles option, which is available from the Page Layout tab. By printing titles, you designate specific data roles, data collections or both to print on every page of the printed worksheet. Typically, these rows and columns contain text labels that identify the purpose of the data in those rows and columns. In this worksheet, you can see that in Print Preview, as I scroll down the print output, the titles disappear and I can only see the raw data, which makes it very difficult to know what they refer to.

I need to keep the titles visible on each page of the printout so that I can easily relate the data to their meaning. If we go to the Page Layout tab and select the Print Titles command, we get the Page Set Up dialogue box, which is open on the Sheet tab, which is where I can control the print titles. You can now select columns or row headers as the order range of cells you want to print by selecting directly from the worksheet. So if I select Row three as the row to repeat at the top of each page, you can see this when I go to the Print Preview option here. You can also select multiple rows, but they must be side by side side. Similarly, I can do the same for the columns I want to repeat. So if I want column A to be on every page, I just click on the header of Column A. You can see the effect on the printer's output.

Study with ExamSnap to prepare for Microsoft Excel Practice Test Questions and Answers, Study Guide, and a comprehensive Video Training Course. Powered by the popular VCE format, Microsoft Excel Certification Exam Dumps compiled by the industry experts to make sure that you get verified answers. Our Product team ensures that our exams provide Microsoft Excel Practice Test Questions & Exam Dumps that are up-to-date.