Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Printing a Single Column in Multiple Columns.
Sometimes the data you collect in a worksheet fits very nicely into a single column. For instance, you may have a list of names, and they all are contained in column A of your worksheet. When you choose to print the worksheet, it can consume quite a few pages, all of them nearly blank as the left side of each page contains a name, and the right side contains white space.
In this type of instance, it would be nice to print the single column as if it were multiple columns. That way you could use more of each printed page and fewer overall pages for your print job. Unfortunately Excel contains no intrinsic command or print setting that allows you to automatically reformat your data so it prints better. There are workarounds, however.
One workaround that is often overlooked is just copying the single-column list to a blank Word document. If you paste it there as plain text, you can format each page for multiple columns and actually print the information.
If you would rather not involve Word, you can cut and paste information from the first column into other columns to give the desired number of printing columns. This, of course, should be done in a new worksheet or workbook, so that the original data remains undisturbed. As an example, if you have 200 names in your original list, you can cut 40 names at a time from the list and paste them into columns A through E of a new worksheet. Printing this worksheet requires less pages than printing the original single-column worksheet.
Of course, if you have to do this cut-and-paste often, the chore can quickly become tiresome. In this instance, you can use a macro that does the exact same thing: It slices and dices the original list and pastes it into a number of columns on a new workbook.
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: 
Sub SingleToMultiColumn()
Dim rng As Range
Dim iCols As Integer
Dim lRows As Long
Dim iCol As Integer
Dim lRow As Long
Dim lRowSource As Long
Dim x As Long
Dim wks As Worksheet
Set rng = Application.InputBox _
(prompt:="Select the range to convert", _
Type:=8)
iCols = InputBox("How many columns do you want?")
lRowSource = rng.Rows.Count
lRows = lRowSource / iCols
If lRows * iCols <> lRowSource Then lRows = lRows + 1
Set wks = Worksheets.Add
lRow = 1
x = 1
For iCol = 1 To iCols
Do While x <= lRows And lRow <= lRowSource
Cells(x, iCol) = rng.Cells(lRow, 1)
x = x + 1
lRow = lRow + 1
Loop
x = 1
Next
End Sub
When you run this macro, you are asked to select the range you want to convert, and then you are asked to specify the number of columns you want it to be reformatted as. It creates a new worksheet in the current workbook and copies information from the original into as many columns as you specified.
For additional resources to solve this problem, refer to the following Web sites:
http://www.ozgrid.com/VBA/MiscVBA.htm#Print http://www.mvps.org/dmcritchie/excel/snakecol.htm
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2219) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Printing a Single Column in Multiple Columns.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Spend a lot of time defining print areas in your workbooks? You might benefit by adding a Set Print Area tool that makes ...
Discover MoreNeed to print several portions of a worksheet all on a single piece of paper? Here's an easy way you can get what you ...
Discover MoreWant to print small, non-contiguous areas of your worksheet all on a single page? You might think that defining a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-10-12 15:41:51
Taylor
Dianne - If you have more than one column of data, you can make a few simple modifications:
For example, for a set of data with 3 columns, replace the For Loop with the below for loop. What I did here was multiply iCols by the number of columns in my original data set (3, in this case), and add "Step 3" after that--all in the setup line for the For Loop. Then I added two lines in the embedded Do-While Loop (these will populate the 2nd and 3rd cells in each new row).
For iCol = 1 To iCols * 3 Step 3
Do While x <= lRows And lRow <= lRowSource
Cells(x, iCol) = rng.Cells(lRow, 1)
Cells(x, iCol + 1) = rng.Cells(lRow, 2)
Cells(x, iCol + 2) = rng.Cells(lRow, 3)
x = x + 1
lRow = lRow + 1
Loop
x = 1
Next
I know this is 3 years after you asked, but I thought it might help people who browse this page (I had the same question, but since there was no answer I had to figure it out myself).
2018-02-17 12:16:55
Willy Vanhaelen
Here is a relatively simple formula if you don't want to use a macro or the bypass via Word.
If your list to print is in column A and you want to print it in 4 columns of 40 rows each, then enter this formula in B1:
=INDIRECT(ADDRESS(ROW()+40*COLUMN(A1),1))
Leave the cellpointer in B1 and extent your selection to range B1:D40, press F2 and Ctrl+Enter.
And voila there is your list to print.
Select it (A1:D40) and in the Print Dialog check the "Selection" option in the Print What frame.
You are ready to print.
If you want to change the number of rows, change 40 in ...ROW()+40... to the number you want. For 25 rows:
=INDIRECT(ADDRESS(ROW()+25*COLUMN(A1),1))
The number of columns depends simply on your selection.
There is no need to use another sheet. After printing you can delete the formulas and your sheet is back to what it was before.
2018-02-15 16:21:27
This is the formula for 3 columns. Feel free to remove one of the "if" and adjust the "3" to "2" if you need for two columns, or add if necessary. Hope it helps!
=IF(MOD(COLUMN(),3)=1,IF(OFFSET(AndereWoerter!$A$1,(QUOTIENT(COLUMN(),3))*40+ROW()-1,0)="","",OFFSET(AndereWoerter!$A$1,(QUOTIENT(COLUMN(),3))*40+ROW()-1,0)),IF(MOD(COLUMN(),3)=2,IF(OFFSET(AndereWoerter!$A$1,(QUOTIENT(COLUMN(),3))*40+ROW(),1)="","",OFFSET(AndereWoerter!$A$1,(QUOTIENT(COLUMN(),3))*40+ROW()-1,1)),IF(OFFSET(AndereWoerter!$A$1,(QUOTIENT(COLUMN(),3))*40+ROW()-1,2)="","",OFFSET(AndereWoerter!$A$1,(QUOTIENT(COLUMN(),3))*40+ROW()-1,2))))
2017-06-07 11:47:51
Dianne
This works great if you have one column of data - how would you change the macro to wrap two columns of data?
2017-02-26 16:19:01
Jason
The trick using Word is fantastic.
Here's a quick GIF showing how its done: http://excelexplained.net/Tricks/how-to-snake-a-column-using-word.html
2016-07-29 02:58:59
John Renwick
Such a simple thing: just want to print a long simple list side by side on an excel spreadsheet without adding to forest destruction but have to use a zillion tons of VBA code to do it. Fucking ridiculous!
2016-02-20 12:40:07
Rob Page
Can this be modified so that instead of writing the cells down the first column, then moving on to the next column, it writes them starting at row 1 across numofcolumns, then moves on to write on the next row? That's the order that I need to data to appear in.
Thanks!
2015-02-10 12:46:36
Michelle
Great idea to use Word!
I pasted the Excel column into Word. Instead of converting it to text, I formatted the Word page to have two columns, which is what fit my data. It worked very well!
2014-06-17 16:48:48
Bonnie
I loved the idea of converting to plain text in word - why didn't I think of that?
Great time saver for my purposes!
2014-03-04 12:26:39
Daniel
Hi Allen.
This code is doing exactly what I want it to do short of one thing, I want to be able to select a cell where I want the output to start.
I removed the "Set wks = Worksheets.Add" making the code paste the output always starting in A1. How can I change this to paste in any cell I prefer?
Best regards
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments