Excel Printing: Troubleshoot and Optimize Your Printouts

Printing. In this section, we'll take a look at all the issues and problems you may deal with when you go to print and how to resolve them. We'll also basically take a look at all the options you have available to you when you go to print your work from Excel.

Printing can be controlled through the Page Layout tab. In fact, that's where we'll spend most of our time. If I go and click on Page Layout, this is the area where I will see all my page setup options.

Now, this section includes multiple topics, so that's why I have multiple tabs here. In order to efficiently go through this information, we'll have each topic take up its own tab. That way, we can focus on one particular topic at a time and just complete that topic before we move on to the next one.

These are the topics we're going to be covering. We're going to take a look at how you can control your margins, your orientation, print area, page breaks, print titles, scale to fit, viewing grid lines and headings, and headers and footers. Now, I'll start out by saying, and as you can see from the screenshot, that some of these options can be configured in the Print Preview window.

Let's say I head over to the File tab and I click on Print. This takes me to Print Preview. I can see what my print job is going to look like, but also on the left in this second column pane, I can choose to change my margins, which is one of the first topics we'll take a look at.

I can choose from normal, wide, and narrow. I can also control my orientation. Right now, I'm in landscape orientation.

I can change it to portrait. So, let's dive into these topics and go through each one and take a look at how you can control your printing. So, I'm going to go over to margins.

Now, my boss says to me, Garfield, I'd like you to print this on one page. And I say to myself, I don't know what he's talking about. This looks like one page to me.

And then I'm informed that I should probably go into Page Layout to get a sense of what this is going to look like when I print it. So, if I go to the View tab, I can head over to the Page Layout button, and if I click that, I will see what my boss is talking about. The second page is only printing out column nine.

Now, I'll go back to normal view and show you another way that you can get to this. I'm going to click back on Page Layout, but now I'm going to head down towards the bottom right-hand corner, and I have these three buttons here. If I hover my mouse over any of them, it'll tell me what they do.

I'm looking for the Page Layout view. So, if I click here, this is also another way to take a look at this print job. All right.

So, now I have this. How am I going to make everything fit on one page? Well, one strategy is to change the margins. So, over in Page Layout, I'll click the dropdown.

We have normal margins that give me 0.7 inches of space on both the left and the right. I wonder if I made that space smaller, if that could somehow include column nine on the first page. So, I'm going to try that.

I'll head over to Narrow, and with one click, everything now fits on one page, and I can confirm that that's the case. I can see the print preview in the spreadsheet without going to Print Preview. Now, something I might want to adjust is the fact that it looks like there's a lot more space over to the right than there is on the left.

I want to center this information on the sheet. So, I'll go over to Margins, and then I'll choose Custom Margins. This gives me an opportunity to adjust the margins to my preferred size, but I can also center the information on the page horizontally.

I'll click OK, and now that's much better. Let's continue and take a look at Orientation. I'm asked to print this all on one page.

I'm going to say, OK, I know what to do now. I'll head over to Page Layout. I know exactly what they're talking about.

Now, two columns are being printed on page two. Nine and ten are the columns being printed on page two. I've been instructed not to touch the margins this time, so I need to find another solution.

So, this time, since we're on the Orientation tab, I'll go to Orientation. Perhaps this information would be best displayed in Landscape mode. That would allow space for more columns.

Let me try that. I'll head over to Landscape. Problem solved.

So, that's a way that you could use Orientation to make sure that everything fits on one page. Let's continue and look at Print Area. Now, the problem here is not so much printing on one page, but printing specific information.

Let me do a quick print preview. Now, going forward, what I'm going to do is go to the Quick Access Toolbar and add the Print Preview button. It'll make it much easier for me to go back and forth between Print Preview and my current view.

I'll click here. Now, if I look down at the bottom, I see that the problem is I'm printing to 13 pages. Wow, 13 pages.

Besides being an unlucky number, I want to find out why am I printing so many different pages. And when I look at my data, I see there's a lot of additional info. I only want to print the data.

I'm not too concerned that it's printing on two pages, especially since I have 13 currently. So, how I'm going to control that is by selecting the Print Area. I'm going to click somewhere in the table.

I'll press Control-A. When I press Control-A, it selects all the contiguous data. This is exactly what I want to print.

I don't want to print anything else. I'll click the dropdown and choose Set Print Area. What I'm doing here is I'm telling Excel this is the area that I want to print.

Then, I'm going to head over to the Print Preview and take a look and see if that made a difference. When I look towards the bottom, it says 1 of 2. That actually did make a difference. I'm only printing to two pages.

So, I can have additional info. I don't have to delete it. I don't have to hide it.

I don't have to group it. All I have to do is set the print area. Let's take a look at Breaks.

I'm being instructed to break this information up so that I only have 20 rows on the first sheet and the rest should go on the second sheet or page 2. I want my information to display 20 rows of data at a time. So, what I'll do is I'll select Row 21. Then, I'll head over to Breaks.

Then, I'll choose Insert Page Break. When I choose Insert Page Break, I'm all of a sudden now on page 2. I'm going to scroll up and I can see this did exactly what I wanted. I wanted to print information that was only 20 rows long on page 1. Then, when I go to page 2, I have the rest of the information on that second page.

Let me zoom out so you can see. That's what's happening there. That's Page Break.

Let's now go to Page Titles. I'm going to discover the issue here by going over to Print Preview and selecting page 2. I'm on page 1 right now. I'm going to go over to page 2. Oh, no headers.

Page 3, no headers there as well. I'll go to page 4. That's the problem. People are not going to be able to understand the information on these sheets without headers.

I need to make sure the headers show up on each page. I'll go back and I'll head over to Print Titles. If I click Print Titles, I'm going to look for an option that will allow me to repeat row 1 on all the pages.

I'm going to select row 1. Actually, the first thing I need to do is select rows to repeat at top. I'm going to click here. Then I'm going to select row 1. You'll notice that row 1 is there with dollar signs.

This is the character that we use to lock cells into position. We learned this in Absolute Cell Referencing. What I'll do now is simply click OK.

Now I want to check to see how this works. I'll head over to my Print Preview. I'll go back to page 2, page 3, page 4. I can see the headers are repeating at the top of each page.

Job well done. Now I'm going to head over to Scale. I'm being instructed again to make sure everything fits on one page.

I've been told not to change the margins or the orientation. Those two tools have been taken out of my toolbox. I need to figure out a way for everything to print on one page.

I'll head over to Scale to fit. At this point, I usually ask students in the class, Hey, when it comes to this particular print job, is it a problem that this is printing to two pages? Is that a problem of the print job being too wide or too tall? Usually people say too wide. That's perfect because when you go to Scale to fit, you'll know exactly where to focus.

You'll focus on width. You'll click the dropdown, and you'll choose one page. What this will do is it will make the print job one page wide.

What Excel is actually doing is shrinking the font so that everything fits on one page. That solves this situation. I didn't have to change the margins or the orientation.

Let's go to Gridlines and Headers. I have a print job. What I want you to know is as we've been working in these sheets, you haven't been seeing any gridlines.

That's because we took it off to make it easier to read. But I can turn them back on. I'll simply go to View.

Before I do that, let's take a look at a print preview. This is how the print job looks. I would like to display gridlines.

Let me go back and go to the View tab, and I'm going to check the checkbox for gridlines. Now I see the gridlines. Let me do a print preview and see if I can see them.

I still don't see them. So turning on gridlines visually on the sheet is not what's going to have them display. What I'm going to need to do is go over to Page Layout, and when I look at Sheet Options, there's an option called Print Under Gridlines.

This will make sure that the gridlines are now printed. I'll click the checkbox here. I'll do a print preview.

Now I can see the gridlines, and this is what makes the gridlines print. I'll go back. I also would like to see the column letters and the row numbers.

I'll select the checkbox for print here. Then I'll do a print preview. Now I see the column letters and the row numbers.

If I really wanted to prove to someone that I was printing from an actual Excel spreadsheet, I can do that. Last thing we'll take a look at are headers and footers. Headers and footers are not located on the Page Layout tab.

Where we'll have to go for that is the Insert tab. If I click Insert and start to move all the way over to the right, I'll see Header and Footer. One click on Header and Footer, and now I'm actually placed in the header.

That's where my cursor is. I have a brand-new tab, something I should also pay attention to, because this will give me options for what can go in the header or the footer. So I'll make sure that I'm in the header, and there's my header or footer.

Then I'm just going to type in the word Company. So for your situation, maybe you want your company name at the top on every single page. Now I want to go to the footer.

If I click on Go to Footer, it'll take me directly to the footer, and I'm there. What can I insert in the header or the footer? I can insert the page number, total number of pages, the current date, the current time, the file path of this workbook, the name of the workbook, the name of the worksheet, and with Picture, I can choose to insert a company logo. I have all those options available to me.

But I'm going to do something that I think is pretty cool. I'm going to add the total number of pages as well as the current page that I'm on. I can use a template for that that's available in the drop-down for footer.

What I'm looking for is page one of question mark. Question mark is the stand-in for the total number of pages. One click, and I'm puzzled.

I didn't see what happened there. I've lost my header and footer. All I have to do is head down towards my footer, and I'll see it now says page one of two, and page two says page two of two.

Let's do a quick print preview. There it is. And if I go to page two, I see it there as well.

We just looked at how we could access several different options related to printing from page layout and also adding a header and a footer.