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 cus- tomer 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.

Figure 1332 Paste a linked picture of the first report section.

5. Drag this picture so that it is centered on the page.

6. Select A18:H18 and then select Home, Borders dropdown, Thick Bottom Border to draw a thick bor- der below the pasted first section..

7. 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.

Figure 1333 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.

PASTE A LIVE PICTURE OF A CELL

Problem: I have a massively large spreadsheet. I’m working on calculations in the top of the spreadsheet but need to monitor a result in W842. It is a pain to travel back and forth to monitor that cell.

Strategy: You can take a picture of the cell and paste it where you can keep an eye on it. Follow these steps:

A live picture of the cell will be pasted.

Figure 1334 G7:G8 is a live picture of cells W841:W842.

As you make changes and the calculations cause the result to change, the picture will update.

Additional Details: The picture can be of multiple cells. Also, it is possible to move the picture by drag- ging it to a new location. You can even paste several pictures, each of a different few summary cells in order to have a dashboard showing key cells from throughout the workbook.

New in Excel 2010, you can easily remove the background from a picture. I can imagine this will lead to a variety of Excel cover sheets with decorative pictures with the backgrounds removed.

Select the picture and choose Remove Background. A new ribbon tab will appear. Your first adjustment is to change the bounding box so it tightly fits around the subject. Making this one adjustment will dramati- cally improve the results of Background Removal.

Below are two copies of the same picture. The picture on the left is the original. The picture on the right is the picture after clicking Remove Background and tightly adjusting the bounding box to the subject.

Figure 1338 Change the bounding box around the subject.

Given the correct bounding box, the Picture Removal tool does a good job of predicting what to remove. The Background Removal tab of the ribbon includes icons where you can mark areas to keep or remove.

This is good for areas that the tool chose to erroneously remove. 4

When you click the Keep Changes icon, the background will be removed.

Gotcha: If you want to enter values in the cells covered by the nowtransparent background, you will usually select the picture instead of the cell. Click outside of the picture and then use the arrow keys to

move to the cells under the picture. It is incredibly tempting to just Figure 1339 After the removal. try to click directly on the cell instead.

INSERTING A SCREEN CLIPPING

Excel 2010 introduced a new Screenshot icon on the Insert tab. If you open this dropdown, you will see a list of available windows that you can paste as a picture into the worksheet. Excel always pastes the entire window, including the title bar. You will then be using the Crop tool to remove all but the relevant portions of the window. Instead, I prefer to use Screen Clipping tool. This tool requires a bit more set up, but then it requires no post-screen-shot work. You might have several different applications open. You want to grab a picture from a web page. From Excel, switch to the browser session where you can see the image.

The Screen Clipping command works on the window that was active immediately before you switched to

Excel. It is important that you switch directly from the browser back to Excel. If you use Alt+Tab to switch applications and you accidentally stop on the wrong application, that application will appear in the screen clipping.