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.

Printing a Single Column in Multiple Columns

by Allen Wyatt
(last updated October 30, 2018)


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.

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", _
    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
        x = 1
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:

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Inserting a Document's Size

Want to insert the size of your document directly into the document body? You can do so by using one of the dynamic ...

Discover More

Changing the User Name in Existing Comments

Want to change the name that Word associates with various comments previously added to your document? Here are some ideas ...

Discover More

Changing Portions of Many Hyperlinks

If you need to modify the URL used in a large number of hyperlinks, you can do so by using a macro and a little ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (menu)

Printing Multiple Worksheet Ranges

Need to print more than one portion of your worksheet? If you use named ranges for the different ranges you want to ...

Discover More

Centering Your Worksheet

Got just a small amount of worksheet data to print out? It might look better on the printed page if you center it. Excel ...

Discover More

Multiple Print Areas on a Single Printed Page

Want to print small, non-contiguous areas of your worksheet all on a single page? You might think that defining a ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine more than 8?

2020-10-12 15:41:51


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
x = 1

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:
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:
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!


2017-06-07 11:47:51


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


The trick using Word is fantastic.

Here's a quick GIF showing how its done:

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.


2015-02-10 12:46:36


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


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


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

This Site

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.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.