Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Using Less Paper on Printouts.
Written by Allen Wyatt (last updated December 18, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
I've got a worksheet that has data in only the first column. In fact, I use cells A1:A100 to store this data. When I print it, the printout uses many pages, but only the left side of each page actually had information in it. If you have the same problem, you may be wondering if there is a way to print the data in columns on a single sheet of paper.
Unfortunately, there is no simple command you can use or magic Wizard you can call up to format the information for printing in columns. Instead, you must manually work with the data a little bit. The first approach would be to use the following steps. (These steps assume you have data that is only one column wide by 100 rows deep.)
Your data is now in four columns, without the original data being disturbed. Format your columns to the necessary width, place a page break just before row 26, and print just the first page of your data. (That last part—adding the page break and printing just the first page—is very important.)
Another option is to utilize the Camera tool, which has been discussed in other issues of ExcelTips. Simply select the information that will appear in the three extra columns, snap the Camera, and place the resulting graphic on the page to be printed.
If you are familiar with other Office tools, you could also copy your entire data table (all 100 rows) to the Clipboard and paste it into Word. You can then format the information in Word to use columns and print as desired. (You can also place headers and footers on your data easier within Word than you can in Excel.)
Finally, you could also look into a third-party add-in or program (such as ASAP Tools) that can handle this type of printing need for you.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9096) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Using Less Paper on Printouts.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Need to only print out certain rows from your data? It's easy to do if you apply the filtering or sorting techniques ...
Discover MoreWhen setting up a worksheet for printing, you can specify that Excel repeat some of your rows at the top of each page ...
Discover MoreNeed to print on both sides of a piece of paper? If your printer can handle it, you can duplex your output from within ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-12-18 06:19:58
Willy Vanhaelen
In the first approach of this tip you can do it with only one relatively simple formula.
If your list to print is in column A and you want to print it in 4 columns of 25 rows each, then enter this formula in B2 (row 1 being your header):
=INDIRECT(ADDRESS(ROW()+25*COLUMN(A:A),1))
Leave the cell pointer in B2 and extent your selection to range B2:D26, press F2 and Ctrl+Enter.
And voila there is your list to print.
Select it (A2:D26) and in the Print Dialog check the "Print Selection" option in the Print settings (no need to insert a page break).
You are ready to print.
The number of columns depends simply on your selection.
If you want to change the number of rows, change 25 in ...ROW()+25... to the number you want.
For 40 rows: =INDIRECT(ADDRESS(ROW()+40*COLUMN(A:A),1))
After printing you can delete the formulas and your sheet is back to what it was before.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments