The template works with both the Microsoft Store edition and the desktop edition of the app. Make sure you have installed one of them.
How to use:
This template demonstrates how to customize the previous sample at 1/2 Page Invoice Template (c4066) so that it prints two 5.5-inch by 8.5-inch forms on a single 8.5-inch by 11-inch sheet of paper.
Note: The sample at Two Receipts on One Sheet (c1012) shows how to create the second copy by using linked images, which offers a simpler way for creating an identical copy.
- Backup the template.
- Open the template.
- Switch to design mode by clicking the "Design Mode" button on the ribbon.
- Drag your mouse over the row headings of all the rows that comprise the exiting form.
In this case, we select rows 2-39. Note that you need to drag on the row headings to select the entire row but not highlight any single cells.
- Press the CTRL+C keys on your keyboard to copy the selected rows to the clipboard.
- Right-click to select the row heading of the first row where you want to place the duplicate copy. In this case, we chose row 47.
- Choose "Paste" from the shortcut menu. This creates a duplicate copy of the selected range at the location starting at row 47.
The cells inside the duplicate copy have exactly the same format and value as those inside the original form.
However, the content of the cells in the duplication are all "Values", which means they will not change when the original form changes. We'll fix this by pasting links.
- Drag your mouse to select the date and "invoice#" cells in the original form.
Note that you need to select the cells that will change their content when you are creating invoices.
In this example, they are K4 (the '#'' cell) and K5 (the invocie date cell).
It is not necessary to select those label cells that will not be changed, for example, J4 (text "DATE:") and J5 (text "INVOICE#:").
- Right-click one of the selected cells and choose "Copy".
- Click to select the corresponding cells inside the duplicate area. In this case, we select K49 and K50.
- Go to the Excel "Home" ribbon tab.
- Pull down the "Paste" command group, and then choose "Paste Links" from the "Other Paste Options" group. This command assigns formulas to the cells on the duplicate copy so that they show the values from the corresponding cells inside the original copy.
For example, Excel creates a formula for the K49 cell as follows:
For the K50 cell, the formula is as follows:
- Repeat the above steps on all the field cells that change from invoice to invoice. In this sample, you should execute the "Copy-Paste" process for the following cells:
- The cells in the "Bill To" section (target address: F54:F58)
- The cells in the "Ship To" section (target address: J54:J58)
- The cells under the label "P.O.#" , "Sales Rep. Name", "Ship Date", "Ship Method", "Terms", and "Due Date" (target address: D64:K64)
- All the cells in the detail section ("Description" column, "Quantity" column, "Unit Price" column, and "Line Total" column) (target address: D67:K70)
- The cells in the summary section, from "Discount (Amount)" to "Total" (target address: I80:K84).
- The "Notes" field (target address: K82).
- Now drag your mouse to select all the cells inside the duplicate form. In this case, it is D47:K84.
- Right-click one of the selected cells and choose "Format Cells" from the shortcut menu. On the "Protection" tab, make sure "Lock" is checked. Click OK. This makes the entire duplicate form non-manually editable.
- Select both forms, i.e., the cells ranging from D2 to K84.
- Go to the Excel "Page Layout" ribbon tab.
- Choose "Print Area" and then "Set Print Area" from the "Page Setup" group.
This tells Excel what to print when you execute the Print command.
- Go to the Excel menu "File" and then choose "Print". Make sure the entire print area can be printed on a single page.
If not, adjust the line heights.
In this example, we already make sure the original format occupies only half of the paper size, so there will be no problem printing two duplicate copies on a single page.
- Optional: You can also add a cutting line between the two forms.
- Go to the Excel "Insert" ribbon tab.
- From the "Illustrations" group, choose "Shapes" and then the first line shape in the "Lines" group.
- Push down the Shift key on the keyboard and draw the line between the two forms.
By pushing down the Shift key, you make sure the line will be exactly vertical or horizontal (not an oblique line).
- Go to the "Drawing Tools" ribbon tab and set the style and color of the cutting line. In this sample, we set it to be a gray dash-dot line (see screenshots).
- In this sample, we also make sure the two copies will be printed on the center of the paper by checking the "Center on page: Horizontally, Vertically" options on the "Margins" tab of the "Page Setup" dialog box.
- To adjust the space between two duplicate copies, adjust the height of the empty row (row 46).
- Once done, exit design mode.
- Save the template.
Format and Specification
|Format (XLS or XLSX)||.xlsx|
|Line Height (Points)||14.25|
|Papaer Size / Orientation||Portrait|
|Default Margins (Points)|