How to set up an Excel template so reports print correctly
Set an Excel template to print correctly by working in View > Page Layout, defining the print area, repeating header rows through Page Layout > Print Titles, setting Scale to Fit to 1 page wide by Automatic tall, adding footers with the &[Page] of &[Pages] codes, then saving the workbook as an .xltx so every new file inherits the settings.
Why does Excel print badly by default?
Excel has no page. A Word document knows where A4 ends from the first keystroke, and a worksheet is an unbounded grid that discovers its edges only when someone presses print. The default result is a report split across nine sheets of paper with orphaned columns and no header after page one.
Switch to View > Page Layout before building anything. It shows margins, headers and page edges live, so the layout is designed against real paper.
How do you set the print area?
Select the range that should print, then choose Page Layout > Print Area > Set Print Area. Everything outside it is ignored, which keeps working notes and lookup tables off the printed output without hiding them.
A print area is stored per worksheet, so set it on each sheet that prints. To add a second, non-adjacent block, select it and use Add to Print Area, which puts it on its own page.
How do you repeat header rows on every page?
Open Page Layout > Print Titles and put the header rows in Rows to repeat at top, entered as $1:$3. Those rows then appear at the top of every printed page, which is the difference between a readable twelve page table and an unusable one.
Columns to repeat at left does the same job horizontally, entered as $A:$B, and is worth setting whenever a wide table carries row labels such as account names or site IDs. The button greys out if the dialog is opened from the print preview screen, so open it from the ribbon.
What scaling setting should be used?
Set Page Layout > Scale to Fit to Width: 1 page and Height: Automatic. That keeps all columns on one sheet of paper whilst letting the report run to as many pages tall as the data needs.
Fitting to 1 page wide by 1 page tall is where reports become unreadable, because Excel shrinks the whole sheet to whatever percentage is required and a long table can end up at 25 per cent. Anything below about 70 per cent stops being legible in print. When a report will not fit at a sensible percentage, change the orientation to landscape or reduce column widths.
How do you add page numbers and document details?
Insert > Header & Footer opens those areas directly in Page Layout view. For full control, open the Page Setup dialog launcher and choose Header/Footer > Custom Footer. The footer codes are plain text. &[Page] of &[Pages] produces "3 of 14", &[File] inserts the workbook name and &[Tab] inserts the worksheet name. A footer combining all three tells a reader exactly which file a loose printed page came from, which matters for anything that goes to a board or a tender panel.
How do you control where pages break?
Use View > Page Break Preview and drag the blue lines to move a break. Excel records the result as a manual break, and it holds when the data changes. To force a break, select the row below it and choose Page Layout > Breaks > Insert Page Break, which is how each cost centre gets its own page.
Should gridlines and headings print?
Decide deliberately. Page Layout > Sheet Options has separate View and Print tick boxes for both Gridlines and Headings. Printed gridlines suit a data table someone will annotate by hand, and they undermine a formatted report that already has borders. Headings are worth printing only for checking formulas.
Set Margins > Custom Margins and tick Centre on page Horizontally whilst you are in there. An uncentred table on an A4 page is the most common reason a competent report looks amateur.
How do the settings carry into every new workbook?
Save the file through File > Save As and choose Excel Template (*.xltx), which places it in the Custom Office Templates folder and makes it available under File > New > Personal. Every workbook created from it inherits the print area, print titles, scaling, margins and footers.
Print settings are stored per worksheet, so apply them to each sheet before saving. Right-click a sheet tab and choose Select All Sheets to apply Page Setup changes across the workbook in one pass. At Ideaseed, print setup is the most common fix on Excel templates arriving for a health check, and it takes minutes to do once inside the template.
Why is PDF better than printing for distribution?
File > Export > Create PDF/XPS Document fixes the pagination permanently, so the recipient sees the same page breaks and layout regardless of their printer. A printed file goes through whatever driver the recipient has, and a different default paper size shifts every break.
Printing in Excel is a template decision. Set the print area, print titles, sensible scaling and footers inside an .xltx, and every report built from it comes out of the printer correctly the first time. For anything being distributed, export to PDF so the pagination cannot move.
If your Excel reports need reformatting before every send, a free template health check will tell you why.
Need a branded template? We can turn it around overnight.
Australia's Microsoft Office template specialists with a South Africa team that delivers while you sleep.
RAVE REVIEWS
who we work with
.svg)
.svg)
.svg)
.svg)
