Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. 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 8, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


2

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", _
      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.

Finally, if you prefer to not use a macro, you could utilize a formula to come up with the rows and columns to print. Let's say, again, that you have 200 names in column a (in the range A1:A200) and you want to print them in five columns of 40 rows each. You can place this formula into cell B1:

=INDIRECT(ADDRESS(ROW()+40*COLUMN(A1),1))

Select the range B1:E40 (make sure that B1 is the active cell in that selected range), then press F2 and finally Ctrl+Enter. Excel fills the range with the formula. You can now select A1:E40 and print just that selection. (You don't want to print the entire worksheet because the original data is still in the original 200 rows. That's why you need to select the reformatted range and just print that selection.)

For additional resources to solve this problem, refer to the following Web sites:

https://www.ozgrid.com/VBA/MiscVBA.htm#Print
http://dmcritchie.mvps.org/excel/snakecol.htm

Note:

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, Excel in Microsoft 365, and 2021. 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. ...

MORE FROM ALLEN

Deleting an AutoText Entry

At some point you may want to delete an AutoText entry you previously created. Here are the steps to follow.

Discover More

Saving in Multiple Locations

Need to save a workbook in more than one location? Here's a handy macro that can save your workbook in lots of different ...

Discover More

Making Resize to Fit Text the Default

When you insert a text box, Word automatically clears one of the key settings for the new text box. If you want this ...

Discover More

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!

More ExcelTips (ribbon)

Hiding Errors on Printouts

If there are error values in a worksheet, you may not want those error values to appear on a printout. Excel actually ...

Discover More

Executing a Macro After Printing is Done

You can modify Excel's BeforePrint event handler to change how the printing process occurs. Unfortunately, though, Excel ...

Discover More

Massive Printouts

Have you ever wanted to do a simple printout, only to find that Excel spit out dozens of pages, and most of them were ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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 two more than 7?

2024-06-14 08:53:57

David Allen

Isn't another way of doing it is just simply to change the page set up in Print to use multiple pages per sheet (e.g. 2x1)?


2024-06-08 15:26:31

J. Woolley

This dynamic array formula will split A1:A200 into 5 columns of 40 rows each:
    =WRAPCOLS(A1:A200, 40)
The WRAPCOLS function currently requires Excel 365.
My Excel Toolbox includes the following dynamic array function:
    =JoinCols(RangeArrayA, RangeArrayB)
This returns all columns of RangeArrayA joined side-by-side with those of RangeArrayB (RangeArrayA on the left). Both RangeArrayA and RangeArrayB must have the same number of rows.
Therefore, this formula will split A1:A200 into 5 columns of 40 rows each:
    =JoinCols(A1:A40, JoinCols(A41:A80, JoinCols(A81:A120, JoinCols(A121:A160, A161:A200))))
See https://sites.google.com/view/MyExcelToolbox/


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
Subscribe

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.