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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Need to print an entire workbook? It's as easy as adding a single line of code to your macros.
Discover MoreIf you are using a macro to create your printed Excel output, you may need a way to specify that paper should come from a ...
Discover MoreNeed to print your worksheet on a non-standard paper size? Excel is rather limited in printing to such papers, and here ...
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 © 2024 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