Mail Merge - Without Word

Category: Printing / General VBA | [Item URL]

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.

The basics

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

Category: Printing | [Item URL]

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:

  1. Activate the sheet that contains the desired setup info. This is the "source" sheet.
  2. 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.
  3. 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

Category: Printing | [Item URL]

If you need to print just a small portion of your worksheet:

  1. Select the cells that you want to print
  2. Choose File - Print
  3. In the Print dialog box, choose the Selection option
  4. 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

Category: Printing | [Item URL]

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:

  1. Select Row 5
  2. Choose Data - Group and Outline - Group (or, use Alt+Shift+RightArrow)
  3. Select Row 10
  4. Press F4 (this key repeats the last command)
  5. Select Row 15
  6. Press F4
  7. Select Row 20
  8. 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

All Tips

Browse Tips by Category

Tip Books

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

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2019, J-Walk & Associates, Inc.
Privacy Policy