In my excel sheet I have three columns A, B, C that are very thin, but have a lot of lines (>500). Now if I print this out, I get 10 pages that are almost empty. Only at the left border of each page I see this three columns.
What I want to do, is to print that whole sheet in a multicolumn layout. 2 or 3 columns per page would be enough. Currently, I'm doing it by copying the values out of Excel, inserting it into a Word document and then use the multi-column layout function of Word. Isn't there a way to achieve that directly in Excel?
To clarify my problem: Currently my pages look like that
A B C
A B C
A B C
A B C
. . .But what I want is this:
A B C A B C A B C
A B C A B C A B C
A B C A B C A B C
. . . . . . . . .6 Answers
I don't think Excel can do that. A possible work-around is to use Microsoft Word and snake the columns around: How to snake columns to use fewer pages
Using MS Word to Snake Columns (#msword)
This is probably the easiest to use for a one time usage, since I've not put in a dialog into my Excel macro described later.
- Select and copy columns from Excel spreadsheet.
- Paste into Word, this takes a few moments
- Select the rows to be repeated at top in the table, then indicate this in Word with Table --> Rows to repeated
- Select entire table using anchor, or selecting cell(s) in the table and then Table --> Select --> Table
- Indicate number of rows using the columns button, or Format --> Columns if you want more control over placement
This example works well. The post offers other possibilities, too.
If you have a source table called "datatable", put this formula on an extra sheet into every cell (where the printed data should appear):
=OFFSET(datatable!$A$2;(ROW()-2)+((COLUMN()-1-MOD(COLUMN()-1;3))/3*4);MOD(COLUMN()-1;3))(the above is translated from german into english. The tested german formula is: =BEREICH.VERSCHIEBEN(datatable!$A$2;(ZEILE()-2)+((SPALTE()-1-REST(SPALTE()-1;3))/3*4);REST(SPALTE()-1;3)))
expected result:
This will transform the content of the sheet "datatable":
A1 B1 C1
A2 B2 C2
A3 B3 C3
A4 B4 C4
A5 B5 C5
A6 B6 C6
...into:
A1 B1 C1 A5 B5 C5 ...
A2 B2 C2 A6 B6 C6 ...
A3 B3 C3 ...
A4 B4 C4 ...Explanation:
Basics:
e.g. OFFSET( datatable!$A$2; 3; 4 ) references the cell relative to $A$2, 3 rows down, 4 columns to the right.
ROW() and COLUMN() are numbers, representing the position of the current cell in the current sheet (e.g. $A$1 = column 1/row 1, $C$2 = column 3/row 2).
The (-1) everywhere are necessary because we are using offsets, and the first row and column is 1, but we want the first referenced cell to be $A$2, not $A$2 plus offset 1.
The (ROW()-2) is for a headline in the output-/print-table (It shold be -1 if you don't have a headline).$A$2 is for a headline in the source "datatable". Without headline it should be referenced with $A$1 (instead of $A$2).
Details:
The 3rd parameter OFFSET(...;...;MOD(COLUMN()-1;3)) is the column offset, it will always reference one of the first 3 columns of the source "datatable".
That is $A$2 plus column offset 0, 1, 2, 0, 1, 2, 0, 1, 2, ...
In the 2nd parameter OFFSET(...; (ROW()-2) + ((COLUMN()-1-MOD(COLUMN()-1;3))/3*4) ;...) selects the row offset.
Basically the math here is y + x * 4.
4 is the number of rows you want to display.
3 is the number of colums in the source "datatable".
The x is made of the column and modulo 3.COLUMN()-1 is 0, 1, 2, 3, ... and so on.MOD(COLUMN()-1;3) will be 0, 1, 2, 0, 1, 2, 0, 1, 2, ...
COLUMN()-1-MOD(COLUMN()-1;3) will be 0, 3, 6, 9, ... and so on, where the value is changing only every 3rd column.
Division by 3 will result in 0, 1, 2, 3, ...
Multiplication with 4 will result in 0, 4, 8, 12, ...
(For explaination about Modulo, see also MOD() in the excel help, or modulo operation in wikipedia)
(Thanks to Jake Kacher, for the basic idea)
You can use a simple formula:
=OFFSET(Sheet1!$A$1,(ROW()-2)*5+COLUMN(),0)to let the data flow horizontally in 5 columns.
Then you can select the page size, print the first row on each page, etc.
Simple, neat, no VBA needed.
The easiest way I found was to create a new worksheet and simply recreate the table manually separating into separate columns.
e.g.
A1 =Sheet1!A1
B1 =Sheet1!B1
C1 =Sheet1!C1
D1 blank (for a divider column)
then
E1 =Sheet1!E51
F1 =Sheet1!B51
G1 =Sheet1!C51
And copy this down over 50 rows. This then turns a single column 100 row table into a 2 column 50 row table. Adjust to suit your needs.
Of course this is manual, and depends upon the row count remaining the same. It wouldn't be difficult to dynamically calculate the number of rows required from the number of rows in the original table and the number of columns you want. The only issue is if the original table expands beyond what will fit on a single page when separated into columns on the new sheet.
Paste in word
Select entire table
Click LAYOUT
Click COLUMNS
Select the number of columns you desire
3If you have Adobe Indesign you can place excel file in it and make this layout with ease.
