Printing Tables And Charts In Excel

By Author: Ed Bolton
Total Articles: 17
Comment this article

Microsoft Excel was originally conceived as a calculator in which you could store your data. If you create reports in Excel, it’s probably because you can enter numbers onto a sheet and use them to derive key business metrics. That’s a bit of an over-simplification, because you may import the data from your own more specialized business software. However, the core strength of Excel is that you can develop your own spreadsheets to get the information you want.

One of the reasons Excel is so popular is that it’s aesthetically pleasing. The criss-cross of squares is very elegant and easy to grasp. If it’s easy for a developer to understand, it follows that your tables and charts may be in an optimal format for a wider audience. Using the Office suite, you can embed your spreadsheets into Word documents and Powerpoint presentations.

Sometimes, though, the spreadsheet is all you need. Or it makes more sense to write your text in the spreadsheet, and not the other way round. In these cases, it is likely you will want to print your Excel file. This poses an immediate problem. Unlike Word ...
... or Powerpoint which contain clearly defined pages and slides, a spreadsheet is basically an infinite grid of boxes. It is not self-evident what needs printing.

The first thing you need to do is to define your print area. You do this by selecting the desired cells, selecting Page Layout->Print Area->Set Print Area. If there are any charts or images on top of the selected cells, they will be included unless you have specifically stated they should not be. If you have any empty rows or columns acting as a margin on your sheet, these can be excluded as Excel automatically adds a margin to all your print outs.

If you now print your sheet, you may encounter one of three common problems:



1. You have e.g. a single table/chart and it has broken out across multiple pages. This means you have to place your printed pages side-by-side to compare like with like. The solution to this can be found under Page Setup. By default, Excel assumes you wish to print everything out full size at 100% zoom. However, you can manually adjust the zoom level or state that you wish to fit everything to one page width.
2. You have multiple tables/charts that are breaking out over multiple pages, but the individual items could all fit on single pages. This requires a Page Break. Similar to Word, you can insert page breaks into your Excel file. Unlike Word, page breaks are associated with rows not paragraphs. So you can select the top cell of a new page and choose to insert a page break
3. You have a single table with too many rows of data to fit on a single page. The problem here is not that it doesn’t print cleanly but that you cannot see column headings beyond the first page and it may not be immediately obvious what each column of data represents. Under Page Setup, you can define Print Titles. This is a specific set of rows, or columns, that will repeat at the top of your Excel spreadsheet. As well as helping you print cleaner tables, it also enables you to repeat key information e.g. a date/logo at the top of each printed sheet.

These basic tips should help you go a long way. Especially if you keep your inputs on different tabs to your outputs. If you have a more complex printing problem, you can always seek out Excel help online. A typical advanced problem would be one in which you want to limit the rows displayed whilst printing without having to manually show/hide rows each and every time you wish to print your sheet.

Finally, it’s worth noting you can access any printer from Excel. That means you can produce pdfs and hard copies of your files.

About the author: Ed Bolton is the founder of Excel4Business, and provider of Excel help.