Mail Merge - Without Word
A companion file is available: Click here to download
Mail merge is the process of merging information from a database into a text document, and then printing the document. The result is a series of personalized documents.
Normally, Microsoft Word is used for this type of operation. Word can perform a mail merge with data stored in an Excel workbook. The process works well, but in some cases it may be preferable to eliminate Word and do all of the work with Excel.
Using Excel to perform a mail merge requires:
- A range that contains the data to be merged. In the example, this is on the sheet named Data.
- A range that contains the text. In the example, this is on the sheet named Form.
- A cell that contains the row number of the "current record." In this example, that cell is C3.
- Formulas that use Excel's INDIRECT function to retrieve data from the current record. These formulas are in column L.
- Formulas within the text that refer to the cells in column L. The example uses several such formulas to personalize the letter.
- A simple macro that loops through the data, incrementing the current record number and printing the sheet.
This example, of course, can be adapted to many other purposes.
This example has a few additional features:
- The ability to specify the first and last records from the database
- Buttons to assist in navigating through the workbook
- An option to preview instead of print
- A Help button that displays instructions
Copy Page Setup Settings To Other Sheets
Each Excel sheet has its own print setup options (orientation, margins, headers and footers, and so on). These options are specified in the Page Setup dialog box, which you access using the File, Page Setup command.
When you add a new sheet to a workbook, it contains the default page setup setting. Here's an easy way to transfer the settings from one worksheet to additional worksheets:
- Activate the sheet that contains the desired setup info. This is the "source" sheet.
- Select the "target" sheets. Press Ctrl and click the sheet tabs of the sheets you want to update with the settings from the source sheet.
- Select File, Page Setup and click OK.
The Page Setup settings of the source sheet will be transferred to all of the target sheets.
Printing Just A Portion Of Your Worksheet
If you need to print just a small portion of your worksheet:
- Select the cells that you want to print
- Choose File - Print
- In the Print dialog box, choose the Selection option
- Click OK
If you've specified a print area, this will not change it.
Note: If your selection consists of a non-contiguous range (a multiple selection), each area is printed on a separate sheet of paper.
Avoid Printing Specific Rows
In some cases, you may wish to avoid printing certain rows in your worksheet. For example, the rows may contain confidential information, or intermediate results that need not be printed.
To avoid printing specific rows, you can hide the rows before you print, and then unhide the rows after printing. If your sheet has many rows that should not be printed, hiding and unhiding the rows may be a tedious process. This tip presents a way to quickly toggle the hidden status of any number of rows. It takes advantage of Excel's Group and Outline feature.
The figure below shows a simple example. In this case, rows 5, 10, 15, and 20 should not be printed.
To set up a simple outline, follow these steps:
- Select Row 5
- Choose Data - Group and Outline - Group (or, use Alt+Shift+RightArrow)
- Select Row 10
- Press F4 (this key repeats the last command)
- Select Row 15
- Press F4
- Select Row 20
- Press F4
The preceding steps created a simple outline on the worksheet, and the outline symbols are displayed along the left side of the sheet. You can hide all of the "grouped" rows by clicking the small "1" button at the top of the outline symbol area (see the figure below).
After you've printed the sheet, click the "2" button to redisplay all of the rows.
Note: To hide the outline symbols, press Ctrl+8. The outline remains, but the symbols are hidden. To re-display the outline symbols, press Ctrl+8 again.
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks