Worksheets can be printed from Excel with minimal effort. Normally, when a worksheet is printed, Excel looks for data-filled cells and prints those cells as well as all blank cells in between the data-filled ones. Page breaks may appear in undesired places within a spreadsheet.
Before printing the worksheet, it is often a good idea to perform a spelling check. In our case, some of our survey-takers made spelling mistakes when they were transcribing our respondents' comments.
Checking Spelling
The Spelling Checker will compare the words in our document with those in the main dictionary, which is included with Microsoft Office. The spelling dictionary will not include most proper names, acronyms, or technical terms that we may use. As we proceed through the spelling checker, we can always add any highlighted words to the main dictionary so that they won't be flagged again.
Step1. To navigate to the Review tab,
Click the Review command tab
Step2. To launch the spell check feature,
Click
NOTE for MacOS Users: There are certain dependencies among Office applications. You must have a custom dictionary language selected in Microsoft Word, or else the Spelling dialog box won't open in Excel 2016 for Mac. Refer to the Microsoft Word 2016 for Mac online help to select a custom dictionary language.
The Spelling dialog box opens and begins the spell check. Excel detects a misspelled word.
Step3. To continue,
Click
Step4. Continue spell-checking the document, using the ignore, change or change all buttons.
If we see an error, we can choose a suggested spelling or make the change manually.
A dialog box will appear when the spell check is complete.
Step5. To end the spell check,
Click
Step6. Save the workbook.
We are finished with our spelling check. Let's continue to prepare our document for printing.
Using Different Views
A view is a way of looking at the data in Excel. The three views in Excel 2016 are:
- Normal view - The default view, which may or may not show page breaks
- Page Break Preview - A view that shows where page breaks occur and lets you manually adjust page breaks
- Page Layout view - A view in Excel 2016 that shows individual pages and gives complete access to Excel commands. It is helpful in getting a worksheet ready for printing
Let's use Page Layout view as we prepare for printing.
Step1. To see the various views, on the Ribbon,
Click the View command tab
Step2. To switch to Page Layout view, in the Workbook Views group,
Click
NOTE: These view buttons also appear in a small format on the right side of the Status bar.
Step3. Scroll to the top of the worksheet.
Creating Headers
In this view, we can see the header and footer sections. A header is a line of information that appears at the top of each printed page, whereas a footer appears at the bottom of each page.
There are three sections in which to add a header. As we point to each section in the header, it will be highlighted to make it easy to enter text in the correct place.
Step1. To begin to access the header in the worksheet,
Point to the left header section
The left header section is highlighted:
We will insert a header in the top left portion of the worksheet while we are in the Page Layout view.
Step2. To access the header and footer tools,
Click in the left header section
The Design tab appears under the Header & Footer Tools, which gives us the tools we need to customize any headers or footers. We will first add page numbers to the left side of the heading.
Step3. To access the relevant tools, if necessary,
Click the Design tab under the Header & Footer Tools tab
NOTE for MacOS Users: To access the relevant tools, Click the Header & Footer command tab.
Step4. To add page numbers to the left section of the header, on the Header & Footer Design tab,
Click
Next, we will type the word "of" (surrounded by spaces) and then add a button to display the total number of pages.
Step5. To type the preposition, type:
Spacebar of Spacebar
Step6. To add the total number of pages, in the Header & Footer Elements group,
Click
The text "&[Page] of &[Pages]" appears in the header as a code, which indicates that Excel will automatically display the pages.
Next, we will add the current date to the right section of the header.
Step7. To access the right header section,
Click in the right header section
Step8. To add the current date, in the Header & Footer Elements group,
Click
The "&[Date]" appears in the header as a code, which indicates that Excel will automatically update the date whenever this worksheet is printed. Next, we will add a title to the sheet in the center header section.
Step9. To access the center header section,
Click in the center header section
Step10. To enter the title, in the center header section, type:
Technology Survey Results
Step11. To return to the worksheet,
Click anywhere in the worksheet
Step12. Save the workbook.
NOTE: You can adjust side or top margins by clicking the top or bottom border of the margin area in the horizontal or vertical ruler. When a vertical or horizontal two-headed arrow appears, drag the margin to the size that you want. Header and footer margins will adjust automatically.
Previewing the Document
It is always a good idea to use the Print Preview feature before printing a worksheet in order to see exactly how the document will print. This can help avoid surprises and wasted paper.
If we simply want to print a worksheet without adjusting any settings, we can use Excel's Print button, located in the Backstage view.
The default print settings are as follows:
- Prints the active worksheet
- Prints one copy
- Prints in portrait mode
- Uses .75-inch margins for the top and bottom, and.70-inch margins for the right and left margins
- Doesn't scale the printed output
- Prints with no headers or footers
- Doesn't print cell comments
- Doesn't print cell gridlines
- On wide worksheets that span multiple pages, prints down and then over
Let us preview this document and see how the current layout looks.
Step1. To start the print preview,
Click File
The Backstage view appears.
Step2. To view the Print Preview,
Click Print
NOTE for MacOS Users: To open the Print dialog box, press: Command key+p.
Step3. To advance to the next page, at the bottom of the screen,
Click
NOTE: You may have a different number of pages, depending on what fonts and style you chose earlier in the workshop.
We see a preview of the printout, and it appears that we have extra columns appearing as subsequent pages. Let's take some steps to improve the printout.
Step4. To return to the worksheet,
Click
NOTE for MacOS Users: To exit the dialog box, press Esc key.
First, we will change the orientation and then adjust the scaling.
Changing Orientation
To view more columns on the page, we will change the orientation to landscape.
Step1. To access the Page Layout options, if necessary,
Click the Page Layout command tab
Step2. To view the orientations, in the Page Setup group,
Click
Step3. To choose the Landscape orientation,
Click Landscape
The worksheet will now print in landscape mode.
Scaling Pages
Depending upon some of the formatting selections, all of the fields in this worksheet may or may not appear on one page. We can set a manual scaling factor or let Excel scale the width so that the fields will fit on one page horizontally. Scaling can range from 10 to 400 percent.
Let's scale the width to fit all the fields on one page.
Step1. To scale the width, in the Scale to Fit group,
Click
A drop-down menu appears. We will select the 1-page option so that all of the fields will fit horizontally on one page.
Step2. To choose 1 page,
Click 1 page
We will leave the height set at automatic, which will allow the document to vertically flow to as many pages as needed.
Using the Page Setup Dialog Box
We will repeat some rows at the top of each page, and so we will open the Page Setup dialog box.
Step1. To open the Page Setup dialog box, in the Page Setup group,
Click
The Page Setup dialog box opens to the appropriate Sheet tab:
Repeating Rows or Columns on Each Page
When a worksheet is set up with titles in the first rows or columns, data appearing on multiple pages can be difficult to follow. Therefore, it is often helpful for someone reading a printed version of a worksheet to be able to see the column headings on subsequent pages of data. When we request that Excel repeat rows or columns while printing, this information is easily visible on each page.
Based on the structure of this worksheet, we will repeat the header rows on every page.
Step1. To indicate what rows will repeat, by the "Rows to repeat at top:" field,
Click
The dialog box is condensed so we can specify the rows to repeat at the top of the page.
Step2. To select the first two rows, in the row header area,
Click row heading 1
You see:
3. To confirm the choice, press:
Click
We are returned to the Page Setup dialog box. The first row, which contains the column headers, will now appear on all of the pages.
Printing Gridlines
By default, Excel does not print gridlines, regardless of whether they are displayed on the worksheet. In this case, we want to print the gridlines as they appear on the screen.
We will now turn on gridlines.
Step1. To turn on gridlines, in the Print section,
Click the Gridlines checkbox
Excel will now print the gridlines.
The Sheet tab contains several additional options:
- Print area: A print area can be defined. If the Print area is left blank, then Excel will print the entire worksheet.
- Black and white: Excel ignores any colors and prints the worksheet in black and white.
- Draft quality: Excel prints in draft mode. Embedded charts, drawing objects, gridlines, or borders will not be printed, which will reduce print time.
- Row and column headings: Excel will print row and column headings to help identify specific cells on a printout.
- Comments: Excel prints cell notes by using the option specified.
- Cell errors as: This option lets you choose how to print cell error values.
- Page order: When a large report is printed, Excel breaks the report into page-size sections based on the current margin and page-size settings. By default, Excel works in "down, then over" order. If preferred, you can have Excel print each horizontal section before moving down to the next vertical section by selecting the "Over, then down" option.
Previewing the Final Document
Next, we will switch to Backstage view and see how the customized settings affect the look of our worksheet. We will do this with a Print Preview view, which allows us to see how the document will look before we send it to the printer.
Step1. To preview the worksheet before printing, in the Page Setup dialog box,
Click
The Backstage view opens with a preview of the report in the right pane:
NOTE: Note the filter triangles in the column heading cells do not appear in the printed version.
Step2. To view the print selection options,
Click
A drop-down menu appears. From here we can choose to Print Active Sheets, Print Entire Workbook (prints all the worksheets that contain data in the workbook), Print Selection (prints any selected range of a worksheet), or Ignore Print Area, which overrides the specified print area.
NOTE for MacOS Users: To view equivalent settings, press: Command key+p, then Click Show Details.
Leave this setting on Print Active Sheets.
Step3. To close this menu, press:
Esc key
The table below summarizes other print options in the Settings section of the Backstage view:
Collated (1, 2, 3 - 1, 2, 3 - 1, 2, 3) | Uncollated (1, 1, 1 - 2, 2, 2 - 3, 3, 3) |
Print One Sided | Print on Both Sides (can choose to flip pages on short edge or on long edge) |
Portrait Orientation | Landscape Orientation |
Normal Margins | Wide, Narrow, Custom |
No Scaling | Fit Sheet on One Page, Fit All Columns on One Page, Fit All Rows on One Page |
Step4. To advance to the next page, at the bottom of the screen,
Click
NOTE: You may have a different number of pages, depending on what fonts and style you chose earlier in the workshop.
We could also specify the number of copies that we want to print by adjusting the value in the Copies field at the top of this pane.
After we have viewed the document and have adjusted any print settings, we could print the document by clicking the print icon. However, we won't print at this time.
Step5. To save the workbook, in the left pane,
Click Save
All print settings will be saved with the workbook.
Step6. To exit Excel and close the workbook,
Click
NOTE for MacOS Users: To exit Excel, press: Command key+q.