Excel Tutorial – Ctrl P is Power in Control (Printing)

Yeah I know even a child with rock bottom skills know how to print. But Excel’s print is not just a Ctrl + P. Its this and much more. One of the nuisance often encountered by excel users is to print same headings on each page of every print. Or what if your data has overflown from print area i.e. either columns are missing or rows are missing. What to do? Well all of these problems can be sorted with few clicks

Printing in Excel is not ordinary printing because Excel is not an ordinary tool. It handles much of your important information if excel is unable to produce what it has on screen for you then it might be very sad. But Excel comes with tons of features even in often overlooked aspect – Printing.

Ctrl + P means Power in Control!

Here is the combination of many one minute excel tutorials at one place regarding printing in excel. I will explain each one of them one by one:

print titles button smallPrint titles: Print specific rows or columns on each page automatically

For example you like to print first row on each automatically or column, then you don’t have to copy that data on each page separately because excel can handle that for you. Click Page layout tab and in Page Setup group click print titles button. A dialogue box appear:

if you like some rows to appear click once in the criteria field in front of Rows to repeat at top: and move your cursor away to select the row. You will notice as cursor leaves the dialogue box it will turn to black arrow pointing right. If you like to select single row then click on any cell in that row. If you like to select multiple rows then press left click + hold and drag mouse.

Same steps apply for columns if you desire a particular to repeat on each print.

print titles rows

Squeeze whole data on one page or specific number of pages

You can even squeeze the whole data to one page that would normally take many pages. You can squeeze down to one page or to any number of pages you desire. You can do this in two ways:

  • By scaling – discussed as follows
  • By page break – will be discussed below later in the article

By scaling – Page layout tab > Scale to fit group > Set width and height to specific number of pages. By default its automatic i.e. excel will determine on how many pages your data prints. Setting width to 1 page will squeeze all the columns to 1 page. Whereas setting height to 1 page will squeeze all rows to 1 page.

scale to 1 page

You can also have this done more easily from print options in file menu. Click File > Print and in settings just above Page setup link you have option button. Click it and it will give you several options like:

  • No scaling – normal 100% size will be used to print
  • Fit sheet on one page – whole sheet will be printed on one page
  • Fit all rows on one page – only rows will be squeezed to one page (that is same as setting height to 1 page as discussed above)
  • Fit all columns on one page – only columns will be squeezed to one page (that is same as setting width to 1 page as discussed above)

print options

Print grid lines / excel column row headings

By default excel’s grid lines or mesh or cell boxes are not printed. Same is the case with column and rows headings, they are excluded from print outputs. But if you like to have them in print as well follow simple steps:

  • To print grid lines: Page layout tab > Sheet options group > Check the box Print under Grid lines
  • To print column and rows heading: Page layout tab > Sheet options group > Check the box Print under Headings

print grid lines and headings

Put an image at the back of your data to spice it up

Well creativity knows no bound so does excel abilities. If you are bored to see white print every time then you can put an image behind your data. In page layout tab > page setup group > background button. A dialogue box appears. Find the image you like and click OK. You have nice image filling the white space for you and I mean ALL the white space for you!

add background

Print Specific area

print area buttonFancy you have a way that you can print only part of the data and not the complete sheet. Well stop fancying it, its real. You can do this in two ways:

  • By selection – print area
  • By formatting data as table

By selection – Select the data as normal. Click Page layout tab > Page setup group > Print area button. From the drop down select set print area. Now only the area you specified will be printed and rest of the sheet will be ignored.

Select print area

If you like to print multiple patches of data. Just select them and on clicking print area button, from the drop down select add to Add to print area. Each selection will be printed on separate page. Remember, this option will be available only if you already have select at least one print area already.

select multi print area

By formatting data as table – Just convert the data you want to be printed specifically. To convert the data range in table click Insert > Tables group > Tables button. The data range will now convert in a table.

Having a table on the worksheet, an additional option is now available in print options i.e. Print selected table.

Click File > Print > Settings section > Print active worksheet button. You can select any from different options. One will be Print selected table. Select the table and only table will be printed.

print selected table

Page break – splitting one data range on different pages only in print

  • Setting marginsSet orientation