Even in the digital age, much of what is created on the computer gets printed. You can make a great spreadsheet, but if it doesn’t look good in print, it loses credibility. Here is the formatting I use to make almost any spreadsheet print well.
Subscribe to the newsletter for more Excel tips and tricks.
In our example, we have a list of sales by territory, region, and month. You need to print it for a meeting — or because your boss said so. There are a lot of print formatting issues here. Not all the months are showing on the page — it awkwardly cuts off at July. The column labels and values are not aligned, making it difficult to read. The column labels are also only printing on page one, meaning there are no column references on the other pages. There are also no references to the date, page numbers, or file path on this document. Let’s address all these issues step by step.
1. Fit columns on one page
This is an easy way to format all but the biggest worksheets. Hit Ctrl + P to get to the print menu. Then near the bottom-left of the page, select the last box. It defaults to “No Scaling.” Instead select “Fit All Columns on One Page.” I am also going to flip the orientation to landscape to give the columns a little more space. 2. Print titles
Next we want to have each page print with column labels. Return to your spreadsheet and select “Print Titles” on the Page Layout menu of the home ribbon. You will get the below dialog box. You want to have rows 5 and 6 repeated at the top of each page. Drop and drag on these rows to select, then hit OK.
3. Add page numbers, file path, and date
Nothing will impress your boss more than proper headers and footers. I always put three things on mine: page numbers, the date, and the file path. This makes it so much easier to communicate with others about the spreadsheet. “Page two of the file from last Monday is saved on the S drive” is a better reference than “that one report from last week is somewhere on the network.” To do this, go to “Page Setup” on the Page Layout menu of the home ribbon. Hit the lower right diagonal of this box and you will get a Page Setup menu. Click on the Header/Footer tab. Let’s first do our custom footer. You can input something into the left, middle, and right section of the footer. Using the buttons on the middle of the screen, I am going to put a page numbering formula on the left-hand side, and the date on the right.
4. Align column headers with the data
Notice how your column labels and data are not aligned. While the labels are left-aligned, the values are right-aligned. This looks bad. To solve, select all of your data, then hit the left alignment button on the home ribbon. With these steps, almost any worksheet can look great in print.