Chapter 3: Preparing Workbooks to Print and Share

A workbook you build for yourself only has to work. A workbook you hand to someone else has to print cleanly, open in their software, and not carry hidden information you didn't mean to share. This chapter covers the MO-210 skills for getting a workbook ready for other people: page setup, headers and footers, printing, other file formats, inspecting a workbook, and comments and notes.

3.1 Page setup

Page setup controls how a worksheet fits on paper or in a PDF. Most settings are on the Page Layout tab, and all of them are in the Page Setup dialog box. To open it, click the small arrow (the dialog box launcher) in the corner of the Page Setup group.

3.1.1 The Page Layout tab

Command	What it does
Margins	Chooses Normal, Wide or Narrow margins, or Custom Margins to type exact sizes.
Orientation	Chooses Portrait (tall) or Landscape (wide).
Size	Chooses the paper size, such as Letter or A4.
Print Area	Sets or clears the range that prints. See "Print areas and print settings" below.
Breaks	Inserts or removes manual page breaks.
Print Titles	Opens the Sheet tab of Page Setup to repeat rows or columns on every page.
Width, Height, Scale	In the Scale to Fit group, shrinks the sheet to fit a number of pages wide or tall, or to a percentage.

3.1.2 The Page Setup dialog box

The dialog box has four tabs:

• Page: orientation, scaling (Adjust to a percentage, or Fit to a number of pages wide by tall), paper size and the first page number.
• Margins: top, bottom, left, right, header and footer margins, and Center on page horizontally or vertically.
• Header/Footer: built-in and custom headers and footers (see the next section).
• Sheet: the print area, print titles, and print options such as gridlines, row and column headings, black and white, and how to print comments and notes.

3.1.3 Print titles

When a list runs onto several pages, only the first page shows the column headings. Print titles repeat rows at the top of every page, or columns at the left of every page. Choose Page Layout › Print Titles, click in Rows to repeat at top, and select the heading row in the worksheet (Excel writes it as $1:$1). Use Columns to repeat at left to repeat columns the same way.

3.2 Headers and footers

A header prints at the top of every page, and a footer prints at the bottom. Each has a left, center and right section.

3.2.1 Adding a header or footer

Choose Insert › Header & Footer. Excel switches to Page Layout view and places the cursor in the center header section. Type text, or use the Header & Footer tab to insert elements that update automatically:

Element	Code it inserts	Prints
Page Number	&[Page]	The current page number
Number of Pages	&[Pages]	The total number of pages
Current Date	&[Date]	Today's date when printed
Current Time	&[Time]	The time when printed
File Path	&[Path]&[File]	The workbook's folder and file name
File Name	&[File]	The workbook's file name
Sheet Name	&[Tab]	The worksheet's name
Picture	&[Picture]	An image, such as a logo

You can mix codes with text. For example, typing Page then inserting Page Number, then typing of and inserting Number of Pages produces "Page 1 of 3."

Click a different section to move between left, center and right. Use Go to Footer to switch to the footer. Click any cell in the worksheet to leave the header, then return to Normal view if you like.

3.2.2 Built-in headers and footers

The Header and Footer buttons on the Header & Footer tab, and the drop-downs on the Header/Footer tab of Page Setup, list ready-made combinations such as "Page 1 of ?" and "Sheet1, Confidential, Page 1." Choose one to fill all three sections at once.

3.2.3 Header and footer options

The Header & Footer tab's Options group has four check boxes:

• Different First Page gives the first page its own header and footer, or none.
• Different Odd & Even Pages gives odd and even pages different headers and footers.
• Scale with Document shrinks the header and footer when the sheet is scaled to fit.
• Align with Page Margins lines the header and footer up with the page margins.

3.3 Print areas and print settings

3.3.1 Setting a print area

A print area limits printing to one range. Select the range and choose Page Layout › Print Area › Set Print Area. Excel creates a defined name, Print_Area, for that sheet, and only that range prints.

• Add to Print Area adds another range. Each range prints on its own page.
• Clear Print Area removes the print area, so the whole used range prints again.

You can also type the print area in the Print area box on the Sheet tab of Page Setup.

3.3.2 Print settings

File › Print (Ctrl+P) shows a preview and the print settings:

Setting	Choices
Copies	The number of copies.
Printer	Which printer, or Microsoft Print to PDF.
What to print	Print Active Sheets, Print Entire Workbook, Print Selection, and the check box Ignore Print Area.
Pages	A range of pages to print, such as 2 to 3.
Collated	Prints whole copies in order (1, 2, 3, 1, 2, 3) or groups each page (1, 1, 2, 2, 3, 3).
Orientation, paper size, margins	The same as on the Page Layout tab.
Scaling	No Scaling, Fit Sheet on One Page, Fit All Columns on One Page, Fit All Rows on One Page, or Custom Scaling Options.

Changes you make here are saved with the workbook, the same as on the Page Layout tab. On the exam you'll configure print settings but won't actually print.

3.4 Saving in other file formats

Excel's own format is the Excel Workbook (.xlsx). Sometimes you need a different one: a PDF for someone who doesn't need to edit the file, a CSV file to load into another program, or a macro-enabled workbook. Choose File › Save As (or File › Save a Copy when AutoSave is on), choose a location, and pick a format from the Save as type list.

Format	Extension	Keeps	Use it for
Excel Workbook	.xlsx	Everything except macros	The default
Excel Macro-Enabled Workbook	.xlsm	Everything, including macros	Workbooks with macros (chapter 12)
Excel Binary Workbook	.xlsb	Everything, in a smaller, faster file	Very large workbooks
Excel 97-2003 Workbook	.xls	Most content; newer features are lost or changed	People using very old versions of Excel
Excel Template	.xltx	Everything, as a starting point for new workbooks	Forms and reports you reuse
CSV UTF-8 (Comma delimited)	.csv	Values of the active sheet only; no formulas, formatting or other sheets	Loading data into another program
Text (Tab delimited)	.txt	Values of the active sheet only	Same as CSV, with tabs
PDF	.pdf	The printed appearance	Sharing a file that won't be edited
OpenDocument Spreadsheet	.ods	Most content	LibreOffice and other programs that use the OpenDocument format

3.4.1 Exporting to PDF

Choose File › Export › Create PDF/XPS Document › Create PDF/XPS, or choose PDF as the Save as type. Click Options to choose what to publish: the selection, the active sheet(s), the entire workbook or a table, and which pages. The PDF uses each sheet's page setup, print area, headers and footers.

File › Export › Change File Type lists the same formats as Save As, grouped by kind.

3.5 Inspecting a workbook

Before you share a workbook, check it for three kinds of problems: hidden information, accessibility problems and compatibility problems. All three checks are under File › Info › Check for Issues.

3.5.1 The Document Inspector

Inspect Document looks for information you might not want to share:

• comments and notes
• document properties and personal information, such as the author's name
• hidden rows, columns and worksheets
• invisible content, such as objects formatted as hidden
• headers and footers
• data model, embedded documents, add-ins and custom XML data
• macros, when the file is macro-enabled

Check the categories to search for and click Inspect. For each category with a result, the inspector shows a Remove All button. Click it only for the categories the task names, because removing data can't be undone. Click Close when you're done.

3.5.2 The Accessibility Checker

The Accessibility Checker looks for things that make a workbook hard to use with a screen reader or for people with low vision. Start it with Check for Issues › Check Accessibility or Review › Check Accessibility. The pane lists:

• Errors, such as pictures and charts with no alternative text.
• Warnings, such as hard-to-read text contrast, merged cells, tables without a header row, and sheets left with default names such as Sheet1.
• Tips, for less serious problems.

Click an item to select the object it's about. Expand Additional Information to see why it's a problem and how to fix it, or click the arrow next to the item for a Recommended Actions list, which often fixes the problem in one click.

Common fixes:

• Missing alternative text: right-click the object and choose View Alt Text, then type a description. (Chapter 6 covers alt text for charts.)
• Default sheet name: double-click the sheet tab and type a meaningful name.
• Merged cells: select them and choose Home › Merge & Center to unmerge, then use Center Across Selection (chapter 4) if you need the look.
• Hard-to-read contrast: change the font or fill color.

3.5.3 The Compatibility Checker

The Compatibility Checker (Check for Issues › Check Compatibility) lists features that won't work in earlier versions of Excel, such as new functions or chart types, and how many times each appears. Click Find to go to each instance. Select versions to show limits the list to particular versions. Check Check compatibility when saving this workbook to run the checker whenever the file is saved in an older format.

3.6 Comments and notes

Excel has two ways to attach a remark to a cell:

• A comment is a conversation. People can reply to it, mention each other, and mark it resolved when they're done. A cell with a comment shows a purple corner marker.
• A note is a sticky note. It has no replies and stays until someone deletes it. A cell with a note shows a red corner triangle.

3.6.1 Working with comments

To	Do this
Add a comment	Select the cell and choose Review › New Comment (or right-click › New Comment). Type, then click Post or press Ctrl+Enter.
Reply	Open the comment, type in Reply, and post.
Edit	Point at your comment and click Edit.
Resolve	Click … (More thread actions) › Resolve thread. A resolved comment stays in the file and can be reopened.
Delete	Choose Review › Delete, or … › Delete thread.
Move between comments	Review › Previous Comment and Next Comment.
See all comments	Review › Show Comments opens the Comments pane.

Typing **[@*]* and a name in a comment mentions that person and notifies them, if the workbook is stored in OneDrive or SharePoint.

3.6.2 Working with notes

To	Do this
Add a note	Select the cell and choose Review › Notes › New Note (Shift+F2). Type the note and click outside it.
Edit	Review › Notes › Edit Note, or right-click › Edit Note.
Show one note all the time	Right-click › Show/Hide Note.
Show all notes	Review › Notes › Show All Notes.
Delete	Right-click › Delete Note, or Review › Delete.
Turn notes into comments	Review › Notes › Convert to Comments.

3.6.3 Printing comments and notes

By default, comments and notes don't print. On the Sheet tab of Page Setup, the Comments and notes list chooses At end of sheet (printed on a separate page) or As displayed on sheet (notes print where they appear; show them first).

3.7 Practice