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: Printing a Single Column in Multiple Columns.

Printing a Single Column in Multiple Columns

Written by Allen Wyatt (last updated June 23, 2018)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


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 are all 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 fewer 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:


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8239) 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: 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. ...


Automatically Selecting Words

When editing a document, Word normally selects entire words as you use the mouse to select text. This tip explains why ...

Discover More

Selecting a Sentence

Need to select an entire sentence? It's easy by making one small adjustment to how you click the mouse.

Discover More

Cropping Graphics in a Macro

Excel allows you to easily paste graphics into a worksheet. Once added, you may want to quickly process the graphics by ...

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 (ribbon)

Changing Orientations within a Single Printout

Excel allows you to print out information in either portrait or landscape orientation, but what if you need both types of ...

Discover More

Can Only Print to Default Printer

It is helpful to be able to print out worksheets when you need to share them with others. It is even more helpful if you ...

Discover More

Printing a Range of Pages

If your worksheet, when printed, requires more than a single page to print, you may want to only print a range of the ...

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 8 - 2?

2020-11-25 07:01:29

Willy Vanhaelen

Instead of using the macro you might consider using my method using a formula. See my comment of 23 June 2018 (at the bottom of this page).

2020-11-24 01:55:46


I've never used a macro before, and this one worked exactly as stated for one column only. Could you highlight in a different colour the places required to increase the number of columns included in the wrap around?

2018-10-22 19:28:15

Lyla Drayson

Thanks Allen, worked great!

2018-08-02 16:19:05

Thomas Papavasiliou

If anyone is interested in a more elaborate macro that prepares a long and narrow data range for printing may use the macro bellow.

Macro prompts user to define, if any, any header rows.

Sub paginate_for_print()

'Macro written 21/06/2014

'If user prefers to let an apparently empty column between the moved data, he can type a space
'in the first to the right empty cell, next to existing data, set that column to the desired width and run the macro
'As this macro is designed to limit the number of printed pages, it runs on a copy of the original sheet
'Original data sheet remains unaltered

' Variable definition
Dim sr As Long, er As Long, rh As Long, hpb As Long, r As Long
Dim sc As Integer, ec As Integer, cc As Integer, ans As Integer, he_rows As Integer, v_pbreaks As Integer
Dim tlt As String, msg As String, he_ro As String, head_to_rep As String
Dim cell As Variant

' Collection of used range limits and columns
With ActiveSheet.UsedRange
sr = .Row
sc = .Column
er = .Rows.Count + sr - 1
ec = .Columns.Count + sc - 1
cc = .Columns.Count
End With

' Initial message and choices for header rows
msg = "This macro will create a new sheet stacking data " + Chr(13)
msg = msg & "for printing purposes." & Chr(13)
msg = msg & "Data row height must be identical." & Chr(13) & Chr(13)
msg = msg + "Do you have any header rows?"
tlt = "Please answer the question..."
ans = MsgBox(msg, vbYesNoCancel + vbQuestion, tlt)

If ans = 2 Then
msg = "Cancel button selected Macro ends."
tlt = "Good choice!"
MsgBox msg, vbOKOnly + vbInformation, tlt
Exit Sub
End If

On Error Resume Next
If ans = 6 Then
msg = "Select header rows or cell/s within header rows."
tlt = "Please follow instructions..."
he_ro = Application.InputBox(msg, Default:=Rows(sr).Address, Title:=tlt, Type:=8).Address
he_rows = Range(he_ro).Rows.Count
head_to_rep = Range(Cells(sr, sc), Cells(sr + he_rows - 1, ec)).Address

If IsEmpty(he_ro) Then
tlt = "Wise decision..."
msg = "Cancel button selected Macro ends."
MsgBox msg, vbOKOnly + vbInformation, tlt
Exit Sub
End If
he_rows = 0
End If

On Error GoTo errorhandling

' Control of identical data rows height (headers excluded)
rh = Range(Cells(sr + he_rows, 1), Cells(er, 1)).RowHeight
If IsNull(Range(Cells(sr + he_rows, 1), Cells(er, 1)).RowHeight) Then
For Each cell In Range(Cells(sr + he_rows, 1), Cells(er, 1))
If cell.RowHeight <> cell.Offset(1, 0).RowHeight Then
msg = "Data row height in rows " & cell.Row & " and " & cell.Row + 1 & " is not identical. Macro stops."
tlt = "Correct the error and try again! "
MsgBox msg, vbOKOnly + vbCritical, tlt
Exit Sub
End If
End If

' Control of existance of data that could be condensed for printing
Application.ScreenUpdating = False 'Accelerates macro execution

Sheets(ActiveSheet.Name).Copy after:=Sheets(ActiveSheet.Name)

ActiveWindow.View = xlPageLayoutView 'This is necessary to display the page breaks

If ActiveSheet.HPageBreaks.Count > 0 Then
hpb_r = ActiveSheet.HPageBreaks(1).Location.Row
tlt = "No need to paginate..."
MsgBox "Sheet is short. Macro ends", vbOKOnly + vbExclamation, tlt
Application.DisplayAlerts = False
ActiveWindow.ActiveSheet.Delete 'Deleting created copy of the original sheet
Application.ScreenUpdating = True
Exit Sub
End If

ActiveWindow.View = xlNormalView

v_pbreaks = 0

' Main part of the macro
Do While Application.WorksheetFunction.CountA(Rows(hpb_r)) > 0
Range(Cells(hpb_r, sc), Cells(er, ec)).Cut Destination:=Cells(sr + he_rows, ec + 1)

' Repeating header rows
If ans = 6 Then
Range(head_to_rep).Copy Destination:=Cells(sr, ec + 1)
End If

' Establishing the same format
Range(Cells(sr, sc), Cells(sr, ec)).EntireColumn.Copy
Cells(1, sc).Offset(0, cc).Range("A1").PasteSpecial Paste:=xlPasteFormats

Application.CutCopyMode = False

If ActiveSheet.VPageBreaks.Count > v_pbreaks And cc > 1 Then

' Inserting, if necessary, a vertical page break to keep the group of transferred data un-split on the same sheet
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=Cells(1, ec + 1)
v_pbreaks = v_pbreaks + 1
End If

sc = sc + cc
ec = ec + cc

' Comment any of the following two lines to fit your preference
' ActiveWindow.DisplayGridlines = True
ActiveWindow.DisplayGridlines = False

Application.ScreenUpdating = True


Exit Sub

msg = "Macro encountered either a non-adequate data input or an unexpected error" + Chr(10)
msg = msg + "Try to re-run checking original data and selections" + Chr(10)
msg = msg + "If the error persists, contact the programmer" + Chr(10) + Chr(10)
msg = msg + "Thomas Papavasiliou" + Chr(10)
msg = msg + "e-mail"
MsgBox msg, vbOKOnly + vbCritical, "If I am still alive, I will answer."

End Sub

2018-06-23 05:39:21

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.

This Site

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.

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.