- •About the Author
- •About the Technical Editor
- •Credits
- •Is This Book for You?
- •Software Versions
- •Conventions This Book Uses
- •What the Icons Mean
- •How This Book Is Organized
- •How to Use This Book
- •What’s on the Companion CD
- •What Is Excel Good For?
- •What’s New in Excel 2010?
- •Moving around a Worksheet
- •Introducing the Ribbon
- •Using Shortcut Menus
- •Customizing Your Quick Access Toolbar
- •Working with Dialog Boxes
- •Using the Task Pane
- •Creating Your First Excel Worksheet
- •Entering Text and Values into Your Worksheets
- •Entering Dates and Times into Your Worksheets
- •Modifying Cell Contents
- •Applying Number Formatting
- •Controlling the Worksheet View
- •Working with Rows and Columns
- •Understanding Cells and Ranges
- •Copying or Moving Ranges
- •Using Names to Work with Ranges
- •Adding Comments to Cells
- •What Is a Table?
- •Creating a Table
- •Changing the Look of a Table
- •Working with Tables
- •Getting to Know the Formatting Tools
- •Changing Text Alignment
- •Using Colors and Shading
- •Adding Borders and Lines
- •Adding a Background Image to a Worksheet
- •Using Named Styles for Easier Formatting
- •Understanding Document Themes
- •Creating a New Workbook
- •Opening an Existing Workbook
- •Saving a Workbook
- •Using AutoRecover
- •Specifying a Password
- •Organizing Your Files
- •Other Workbook Info Options
- •Closing Workbooks
- •Safeguarding Your Work
- •Excel File Compatibility
- •Exploring Excel Templates
- •Understanding Custom Excel Templates
- •Printing with One Click
- •Changing Your Page View
- •Adjusting Common Page Setup Settings
- •Adding a Header or Footer to Your Reports
- •Copying Page Setup Settings across Sheets
- •Preventing Certain Cells from Being Printed
- •Preventing Objects from Being Printed
- •Creating Custom Views of Your Worksheet
- •Understanding Formula Basics
- •Entering Formulas into Your Worksheets
- •Editing Formulas
- •Using Cell References in Formulas
- •Using Formulas in Tables
- •Correcting Common Formula Errors
- •Using Advanced Naming Techniques
- •Tips for Working with Formulas
- •A Few Words about Text
- •Text Functions
- •Advanced Text Formulas
- •Date-Related Worksheet Functions
- •Time-Related Functions
- •Basic Counting Formulas
- •Advanced Counting Formulas
- •Summing Formulas
- •Conditional Sums Using a Single Criterion
- •Conditional Sums Using Multiple Criteria
- •Introducing Lookup Formulas
- •Functions Relevant to Lookups
- •Basic Lookup Formulas
- •Specialized Lookup Formulas
- •The Time Value of Money
- •Loan Calculations
- •Investment Calculations
- •Depreciation Calculations
- •Understanding Array Formulas
- •Understanding the Dimensions of an Array
- •Naming Array Constants
- •Working with Array Formulas
- •Using Multicell Array Formulas
- •Using Single-Cell Array Formulas
- •Working with Multicell Array Formulas
- •What Is a Chart?
- •Understanding How Excel Handles Charts
- •Creating a Chart
- •Working with Charts
- •Understanding Chart Types
- •Learning More
- •Selecting Chart Elements
- •User Interface Choices for Modifying Chart Elements
- •Modifying the Chart Area
- •Modifying the Plot Area
- •Working with Chart Titles
- •Working with a Legend
- •Working with Gridlines
- •Modifying the Axes
- •Working with Data Series
- •Creating Chart Templates
- •Learning Some Chart-Making Tricks
- •About Conditional Formatting
- •Specifying Conditional Formatting
- •Conditional Formats That Use Graphics
- •Creating Formula-Based Rules
- •Working with Conditional Formats
- •Sparkline Types
- •Creating Sparklines
- •Customizing Sparklines
- •Specifying a Date Axis
- •Auto-Updating Sparklines
- •Displaying a Sparkline for a Dynamic Range
- •Using Shapes
- •Using SmartArt
- •Using WordArt
- •Working with Other Graphic Types
- •Using the Equation Editor
- •Customizing the Ribbon
- •About Number Formatting
- •Creating a Custom Number Format
- •Custom Number Format Examples
- •About Data Validation
- •Specifying Validation Criteria
- •Types of Validation Criteria You Can Apply
- •Creating a Drop-Down List
- •Using Formulas for Data Validation Rules
- •Understanding Cell References
- •Data Validation Formula Examples
- •Introducing Worksheet Outlines
- •Creating an Outline
- •Working with Outlines
- •Linking Workbooks
- •Creating External Reference Formulas
- •Working with External Reference Formulas
- •Consolidating Worksheets
- •Understanding the Different Web Formats
- •Opening an HTML File
- •Working with Hyperlinks
- •Using Web Queries
- •Other Internet-Related Features
- •Copying and Pasting
- •Copying from Excel to Word
- •Embedding Objects in a Worksheet
- •Using Excel on a Network
- •Understanding File Reservations
- •Sharing Workbooks
- •Tracking Workbook Changes
- •Types of Protection
- •Protecting a Worksheet
- •Protecting a Workbook
- •VB Project Protection
- •Related Topics
- •Using Excel Auditing Tools
- •Searching and Replacing
- •Spell Checking Your Worksheets
- •Using AutoCorrect
- •Understanding External Database Files
- •Importing Access Tables
- •Retrieving Data with Query: An Example
- •Working with Data Returned by Query
- •Using Query without the Wizard
- •Learning More about Query
- •About Pivot Tables
- •Creating a Pivot Table
- •More Pivot Table Examples
- •Learning More
- •Working with Non-Numeric Data
- •Grouping Pivot Table Items
- •Creating a Frequency Distribution
- •Filtering Pivot Tables with Slicers
- •Referencing Cells within a Pivot Table
- •Creating Pivot Charts
- •Another Pivot Table Example
- •Producing a Report with a Pivot Table
- •A What-If Example
- •Types of What-If Analyses
- •Manual What-If Analysis
- •Creating Data Tables
- •Using Scenario Manager
- •What-If Analysis, in Reverse
- •Single-Cell Goal Seeking
- •Introducing Solver
- •Solver Examples
- •Installing the Analysis ToolPak Add-in
- •Using the Analysis Tools
- •Introducing the Analysis ToolPak Tools
- •Introducing VBA Macros
- •Displaying the Developer Tab
- •About Macro Security
- •Saving Workbooks That Contain Macros
- •Two Types of VBA Macros
- •Creating VBA Macros
- •Learning More
- •Overview of VBA Functions
- •An Introductory Example
- •About Function Procedures
- •Executing Function Procedures
- •Function Procedure Arguments
- •Debugging Custom Functions
- •Inserting Custom Functions
- •Learning More
- •Why Create UserForms?
- •UserForm Alternatives
- •Creating UserForms: An Overview
- •A UserForm Example
- •Another UserForm Example
- •More on Creating UserForms
- •Learning More
- •Why Use Controls on a Worksheet?
- •Using Controls
- •Reviewing the Available ActiveX Controls
- •Understanding Events
- •Entering Event-Handler VBA Code
- •Using Workbook-Level Events
- •Working with Worksheet Events
- •Using Non-Object Events
- •Working with Ranges
- •Working with Workbooks
- •Working with Charts
- •VBA Speed Tips
- •What Is an Add-In?
- •Working with Add-Ins
- •Why Create Add-Ins?
- •Creating Add-Ins
- •An Add-In Example
- •System Requirements
- •Using the CD
- •What’s on the CD
- •Troubleshooting
- •The Excel Help System
- •Microsoft Technical Support
- •Internet Newsgroups
- •Internet Web sites
- •End-User License Agreement
Chapter 9: Printing Your Work
Adjusting Common Page Setup Settings
Clicking the Quick Print button (or choosing File Print Print) may produce acceptable results in many cases, but a little tweaking of the print settings can often improve your printed reports. You can adjust print settings in three places:
•The Print screen in Backstage View, displayed when you choose File Print
•The Page Layout tab of the Ribbon
•The Page Setup dialog box, displayed when you click the dialog launcher in the bottomright corner of the Page Layout Page Setup group on the Ribbon
Table 9.1 summarizes the locations where you can make various types of print adjustments in Excel 2010.
TABLE 9.1
Where to Change Printer Settings
|
Print Settings |
Page Layout |
Page Setup Dialog |
Setting |
Screen |
Tab of Ribbon |
Box |
|
|
|
|
Number of copies |
X |
|
|
|
|
|
|
Printer to use |
X |
|
|
|
|
|
|
What to print |
X |
|
|
|
|
|
|
Specify worksheet print area |
|
X |
X |
|
|
|
|
1-sided or 2-sided |
X |
|
|
|
|
|
|
Collated |
X |
|
|
|
|
|
|
Orientation |
X |
X |
X |
|
|
|
|
Paper size |
X |
X |
X |
|
|
|
|
Adjust margins |
X |
X |
X |
|
|
|
|
Specify manual page breaks |
|
X |
|
|
|
|
|
Specify repeating rows and/or columns |
|
|
X |
|
|
|
|
Set print scaling |
|
X |
X |
|
|
|
|
Print or hide gridlines |
|
X |
X |
|
|
|
|
Print or hide row and column headings |
|
X |
X |
|
|
|
|
Specify the first page number |
|
|
X |
|
|
|
|
Center output on page |
|
|
X |
|
|
|
|
Specify header/footers and options |
|
|
X |
|
|
|
|
Specify how to print cell comments |
|
|
X |
|
|
|
|
|
|
|
continued |
177
Part I: Getting Started with Excel
TABLE 9.1 |
(continued) |
|
|
|
|
|
|
|
|
|
|
Print Settings |
Page Layout |
Page Setup Dialog |
Setting |
|
Screen |
Tab of Ribbon |
Box |
|
|
|
|
|
Specify page order |
|
|
X |
|
|
|
|
|
|
Specify black-and-white output |
|
|
X |
|
|
|
|
|
|
Specify how to print error cells |
|
|
X |
|
|
|
|
|
|
Launch dialog box for printer-specific settings |
X |
|
X |
|
|
|
|
|
|
Choosing your printer
To switch to a different printer, choose File Print, and use the drop-down control in the Printer section to select a different installed printer.
Note
To adjust printer settings, click the Printer Properties link to display a property box for the selected printer. The exact dialog box that you see depends on the printer. The Properties dialog box lets you adjust printer-specific settings, such as the print quality and the paper source. In most cases, you won’t have to change any of these settings, but if you’re having print-related problems, you may want to check the settings. n
Specifying what you want to print
Sometimes you may want to print only a part of the worksheet rather than the entire active area. Or you may want to reprint selected pages of a report without printing all the pages. Choose File Print, and use the controls in the Settings section to specify what to print.
You have several options:
•Active Sheets: Prints the active sheet or sheets that you selected. (This option is the default.) You can select multiple sheets to print by pressing Ctrl and clicking the sheet tabs. If you select multiple sheets, Excel begins printing each sheet on a new page.
•Entire Workbook: Prints the entire workbook, including chart sheets.
•Selection: Prints only the range that you selected before choosing File Print.
•Selected Table: Appears only if the cell pointer is within a table (created with
Insert Tables Table) when the Print Setting screen is displayed. If selected, only the table will be printed.
Tip
You can also choose Page Layout Page Setup Print Area Set Print Area to specify the range or ranges to print. Before you choose this command, select the range or ranges that you want to print. To clear the print area, choose Page Layout Page Setup Print Area Clear Print Area. To override the print area, select the Ignore Print Areas check box in the list of Print What options. n
178
Chapter 9: Printing Your Work
Is Printing Getting More Complicated?
If you’re new to Excel, the information in Table 9.1 might be a bit overwhelming. Why does Excel provide three ways to adjust printing options? After all, before Excel 2007, Excel provided the Page Setup dialog box, plus a Print dialog box. Everything you needed was in these two dialog boxes. It was fairly simple.
With the introduction of the Ribbon in Excel 2007, though, things got a bit more complicated. Some of the more common print settings were in the Page Layout Page Setup Ribbon group, where they are easily seen. These are also the settings that determine how the Page Layout View is displayed. The Page Setup and Print dialog boxes were still used.
New Feature
In Excel 2010, the Print tab in Backstage View replaces the Print dialog box. The Page Layout Page
Setup Ribbon group remains unchanged. And users still need to use the Page Setup dialog box to make some changes. n
Table 9.1 might make printing seem more complicated than it really is. The key point to remember is this: If you can’t find a way to make a particular adjustment, it’s probably available from the Page Setup dialog box.
Note
The print area does not have to be a single range. You make a multiple selection before you set the print area. Each area will print on a separate page. n
If your printed output uses multiple pages, you can select which pages to print by indicating the number of the first and last pages to print by using Pages controls in the Settings section. You can either use the spinner controls or type the page numbers in the edit boxes.
Changing page orientation
Page orientation refers to how output is printed on the page. Choose Page Layout Page Setup Orientation Portrait to print tall pages (the default) or Page Layout Page Setup Orientation Landscape to print wide pages. Landscape orientation is useful when you have a wide range that doesn’t fit on a vertically oriented page.
If you change the orientation, the onscreen page breaks adjust automatically to accommodate the new paper orientation.
Page orientation settings are also available when you choose File Print.
Specifying paper size
Choose Page Layout Page Setup Size to specify the paper size you’re using. The paper size settings are also available when you choose File Print.
179
Part I: Getting Started with Excel
Note
Even though Excel displays a variety of paper sizes, your printer may not be capable of using them. n
Printing multiple copies of a report
Use the Copies control at the top of the Print tab in Backstage View to specify the number of copies to print. Just enter the number of copies you want and then click Print.
Tip
If you’re printing multiple copies of a report, make certain that the Collated option is selected so that Excel prints the pages in order for each set of output. If you’re printing only one page, Excel ignores the Collated setting. n
Adjusting page margins
Margins are the unprinted areas along the sides, top, and bottom of a printed page. Excel provides four “quick margin” settings, and you can also specify the exact margin size you require. All printed pages have the same margins. You can’t specify different margins for different pages.
In Page Layout view, a ruler is displayed above the column header and to the left of the row header. Use your mouse to drag the margins in the ruler. Excel adjusts the page display immediately. Use the horizontal ruler to adjust the left and right margins, and use the vertical ruler to adjust the top and bottom margins.
From the Page Layout Page Setup Margins drop-down list, you can select Normal, Wide, Narrow, or the Last custom Setting. These options are also available when you choose File Print. If none of these settings does the job, choose Custom Margins to display the Margins tab of the Page Setup dialog box, shown in Figure 9.4.
To change a margin, click the appropriate spinner (or you can enter a value directly). The margin settings that you specify in the Page Setup dialog box will then be available in the Page
Layout Page Setup Margins drop-down list, referred to as Last Custom Setting.
Note
The Preview box in the center of the Page Setup dialog box is a bit deceiving because it doesn’t really show you how your changes look in relation to the page; rather, it displays a darker line to let you know which margin you’re adjusting. n
In addition to the page margins, you can adjust the distance of the header from the top of the page and the distance of the footer from the bottom of the page. These settings should be less than the corresponding margin; otherwise, the header or footer may overlap with the printed output.
By default, Excel aligns the printed page at the top and left margins. If you want the output to be centered vertically or horizontally, select the appropriate check box in the Center on Page section of the Margins tab.
180
Chapter 9: Printing Your Work
FIGURE 9.4
The Margins tab of the Page Setup dialog box.
Understanding page breaks
When printing lengthy reports, controlling where pages break is often important. For example, you probably don’t want a row to print on a page by itself, nor do you want a table header row to be the last line on a page. Fortunately, Excel gives you precise control over page breaks.
Excel handles page breaks automatically, but sometimes you may want to force a page break — either a vertical or a horizontal one — so that the report prints the way you want. For example, if your worksheet consists of several distinct sections, you may want to print each section on a separate sheet of paper.
Inserting a page break
To insert a horizontal page-break line, move the cell pointer to the cell that will begin the new page. Just make sure that you place the pointer in column A, though; otherwise, you’ll insert a vertical page break and a horizontal page break. For example, if you want row 14 to be the first row of a new page, select cell A14. Then choose Page Layout Page Setup Breaks Insert Page Break.
Note
Page breaks are visualized differently, depending on which view mode you’re using. See “Changing Your Page View,” earlier in this chapter. n
181
Part I: Getting Started with Excel
To insert a vertical page-break line, move the cell pointer to the cell that will begin the new page. In this case, though, make sure to place the pointer in row 1. Choose Page Layout Page
Setup Breaks Insert Page Break to create the page break.
Removing manual page breaks
To remove a page break you’ve added, move the cell pointer to the first row beneath (or the first column to the right) of the manual page break and then choose Page Layout Page Setup Breaks Remove Page Break.
To remove all manual page breaks in the worksheet, choose Page Layout Page Setup Breaks Reset All Page Breaks.
Printing row and column titles
If your worksheet is set up with titles in the first row and descriptive names in the first column, it can be difficult to identify data that appears on printed pages where those titles do not appear. To resolve this problem, you can choose to print selected rows or columns as titles on each page of the printout.
Cross-Reference
Row and column titles serve pretty much the same purpose on a printout as frozen panes do in navigating within a worksheet. See Chapter 3 for more information on freezing panes. Keep in mind, however, that these features are independent of each other. In other words, freezing panes does not affect the printed output. n
Caution
Don’t confuse print titles with headers; these are two different concepts. Headers appear at the top of each page and contain information, such as the worksheet name, date, or page number. Row and column titles describe the data being printed, such as field names in a database table or list. n
You can specify particular rows to repeat at the top of every printed page or particular columns to repeat at the left of every printed page. To do so, choose Page Layout Page Setup Print Titles. Excel displays the Sheet tab of the Page Setup dialog box, shown in Figure 9.5.
Activate the appropriate box (either Rows To Repeat At Top or Columns To Repeat At Left) and then select the rows or columns in the worksheet. Or you can enter these references manually. For example, to specify rows 1 and 2 as repeating rows, enter 1:2.
Note
When you specify row and column titles and use Page Layout view, these titles will repeat on every page (just as when the document is printed). However, the cells used in the title can be selected only on the page in which they first appear. n
182
Chapter 9: Printing Your Work
FIGURE 9.5
Use the Sheet tab of the Page Setup dialog box to specify rows or columns that will appear on each printed page.
Scaling printed output
In some cases, you may need to force your printed output to fit on a specific number of pages. You can do so by enlarging or reducing the size. To enter a scaling factor, choose Page Layout Scale to Fit Scale. You can scale the output from 10% up to 400%. To return to normal scaling, enter
100%.
To force Excel to print using a specific number of pages, choose Page Layout Scale to Fit Width and Page Layout Scale to Fit Height. When you change either one of these settings, the corresponding scale factor is displayed in the Scale control.
Caution
Excel doesn’t care about legibility, however. It will gladly scale your output to be so small that no one can read it. n
Printing cell gridlines
Typically, cell gridlines aren’t printed. If you want your printout to include the gridlines, choose Page Layout Sheet Options Gridlines Print.
Alternatively, you can insert borders around some cells to simulate gridlines. See Chapter 6 for information about borders.
183
Part I: Getting Started with Excel
Inserting a Watermark
A watermark is an image (or text) that appears on each printed page. A watermark can be a faint company logo, or a word such as DRAFT. Excel doesn’t have an official command to print a watermark, but you can add a watermark by inserting a picture in the page header or footer. Here’s how:
1.Locate an image on your hard drive that you want to use for the watermark.
2.Choose View Workbook Views Page Layout View.
3.Click the center section of the header.
4.Choose Header & Footer Tools Header & Footer Elements Picture.
5.Using the Insert Picture dialog box, locate the image from Step 1.
6.Click outside the header to see your image.
7.To center the image in the middle of the page, click the center section of the header and add some carriage returns before the &[Picture] code. You’ll need to experiment to determine the number of carriage returns required to push the image into the body of the document.
8.If you need to adjust the image (for example, make it lighter), click the center section of the header and then choose Header & Footer Tools Header & Footer Elements Format
Picture. Use the Image controls in the Picture tab of the Format Picture dialog box to adjust the image. You may need to experiment with the settings to make sure that the worksheet text is legible.
The accompanying figure shows an example of a header image (a globe) used as a watermark. You can do a similar thing with text, of course.
184