There is a much better way

Nothing ruins a professional Excel report like a “Summary” line floating on its own page when you send it to a printer or PDF. While you could manually adjust your print area for every update, the real pro move is automation. Here’s how to use the OFFSET function to create a print area that truly thinks for itself.

The sloppy PDF problem

The frustration of the “as-is” gamble

Credit: Tony Phillips/How-To Geek

You might be thinking, “Why bother? Can’t I just hit “Print” and let Excel figure it out?” You could, but relying on Excel’s default printing is a gamble. To see why, let’s look at a common real-world scenario: a professional service invoice.

The invoice features a branded header with a logo, client details, and an itemized table for services. The challenge is that this list changes every month. Sometimes you have two items in the table; sometimes you have 20. Below that list sits a “Total” summary box that must be included in every printout.

If you rely on Excel’s default “as-is” printing, you face two major problems. First, if you ever typed a note in row 50 and deleted it, Excel may still remember that row as part of the sheet’s used range. This can result in a multi-page printout or PDF where the last few pages are completely blank. Second, printing “as-is” offers no guardrails. If you have internal calculations or messy notes to the side of your invoice, Excel will include them in the printout, cluttering your professional document with messy scratchpad math.

That’s why you should create a print boundary that “shrink-wraps” the document—starting at the logo and ending exactly where the “Total” box lands—no matter how much the data in between grows or shrinks.

Step 1: Build the dynamic print area

Cracking the code of the height calculation

To solve the “sloppy PDF” problem, you need to create a dynamic named range. However, Excel will replace dynamic formulas with static cell references if you later use tools like Set Print Area or modify the print area in Page Setup. To protect the formula, you need to store it under a separate “proxy name”:

1. In the Formulas tab, click Name Manager.
2. Click New.
3. In the New Name dialog, clear the Refers to field and paste the following formula:

   =OFFSET($A$1, 0, 0, COUNTA($A:$A)+8, 5)

4. In the Name field, type InvoiceRange.
5. Click OK and Close to close the two dialog boxes.

Let’s take a moment to understand exactly what this OFFSET formula is doing:

• $A$1: This is the anchor. The dollar signs convert the reference from relative to absolute, meaning the print always starts at the very top-left corner of the sheet.
• COUNTA($A:$A)+8: This is the height calculation. COUNTA counts every non-empty cell in column A. Adding +8 acts as a “padding” variable to account for the empty rows. 14 cells in column A contain values, and the +8 in the formula takes the area to row 23. If you add more empty rows, move the summary box further down, or use a different template altogether, you’ll need to adjust this number to keep everything in frame.
• 5: This locks the width to five columns (A through E). This acts as a guardrail, ensuring your printout never spills over into the side margins. If you change the number of columns in your layout, you’ll need to adjust this number accordingly.

Now, you need to connect the proxy to the print area:

1. Select the active cells in your invoice (A1:E23), and in the Page Layout tab, click Print Area > Set Print Area. You won’t see anything change, but this step creates a special workbook name called Print_Area, which you’ll use to store the dynamic formula.
2. Reopen the Name Manager, select the Print_Area entry, and clear the Refers to field.
3. With the cursor still in the Refers to field, press F3, and double-click InvoiceRange.
4. Click the checkmark to confirm.
5. Click back into the Refers to field, and see the “marching ants” highlight the dynamic area.
6. Click Close.

Step 2: Connect the formula to Excel’s printer

Hijacking your page setup

Once the formula that determines your dynamic print area is in the Name Manager, you need to make sure the Page Setup menu is actually listening to it:

1. In the Page Layout tab, click the dialog box launcher icon in the bottom-right corner of the Page Setup group.
2. Open the Sheet tab, and clear the Print area field.
3. With your cursor still in the Print area field, press F3 to open the Paste Name menu, and double-click Print_Area.
4. Click OK twice to close the two dialog boxes.

Step 3: Test your new automated setup

Achieving the “set-and-forget” workflow

Now for the satisfying part, where you see the workflow in action:

1. Go to your itemized list and select the bottom-right cell of the current table.
2. Press Tab. Because the list is formatted as an Excel table, Excel automatically generates a new row and pushes the “Total” line downward. Enter some dummy data and repeat this process five or six times.
3. Type some random notes outside the invoice area (such as in column G and row 32).
4. Press Ctrl+P to open the Print Preview. Notice that the print area has expanded perfectly to include every new row and the “Total” box. Importantly, your messy notes are nowhere to be found.
5. To really prove that your automation is still in place, reopen the Name Manager, and click in the Refers to field for InvoiceRange and Print_Area to see that the “marching ants” still dance around the dynamic area.

The days of fighting with manual print settings are over. By anchoring your layout and automating the height, you have created a spreadsheet that respects your boundaries. Syncing this “set-and-forget” workflow with other printout tweaks—like adjusting orientation and using custom margins—is a sure-fire way to get you the perfect Excel printout.

OS

Windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.