Excel: Don't Line Up

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I need to duplicate a fairly complex form. The form has several sections. The column widths needed for the first section do not line up with the column widths needed for the other two sections.

Strategy: This is a wildly amazing and obscure solution. It has been floating around Excel Web sites for years as a novelty. However, I recently used it in a production application to produce great-looking customer statements. Here's how it works:

  1. Set up various sections of the form on individual worksheets. Make the column widths as wide as they need to be for each section of the form. In the sample, I have four different sections. The statement header has a logo and an address block that are centered on the page. The next other sections have five, three, and six columns.
  2. To pull these parts together, you will build a printable statement on the worksheet that has the company header. On that page, you will paste three linked pictures that give a view of the other worksheets.
  3. Select the cells for Section 1 and then press Ctrl+C to copy.
  4. Go to cell A7 on the main worksheet. In Excel, select Home, Paste dropdown, Paste Picture Link.

  1. Paste a linked picture of the first report section.
    1. Drag this picture so that it is centered on the page.
    2. Select A18:H18 and then select Home, Borders dropdown, Thick Bottom Border to draw a thick border below the pasted first section..
    3. Repeat steps 3 through 6 for Sections two and three.

    You can resize the pictures in the new sheet so they all have the same width, or you can simply center them on the page.

    Results: You can print one unified form that does not look like it came from Excel. Fields in Section 2 are not necessarily lined up with columns in Sheet1. Note that the pictures are live pictures. If you change values on a back sheet, the picture on Sheet1 will automatically update.

  2. Three pictures make up this report.

    Additional Details: In our real statement application, we used a VBA macro to put together the sections. This macro can paste a different number of rows each time.

For more resources for Microsoft Excel