How can I make Excel print row by row, one page wide?
I have an Excel spreadsheet that has columns A-U and rows 1-50.
When I print it at a readable scale, it prints in a three-page-wide format:
page 1 is columns A-F wide and rows 1-20 long,
page 2 is columns G-L and rows 1-20, and page 3 is columns M-U × rows 1-20.
Then pages 4, 5, and 6 go to rows 21-40, and so on.
I don't want to scale the printout to be 1 page wide and n pages tall
because that would make the cells too small.
I would rather have a one-sheet-wide format
in which columns A-U of one row of the spreadsheet
print using two or three rows on the printed page
but keeping the Excel table format, if possible,
and then the next row of the spreadsheet, and so on.
How can I do this?
microsoft-excel printing
add a comment |
I have an Excel spreadsheet that has columns A-U and rows 1-50.
When I print it at a readable scale, it prints in a three-page-wide format:
page 1 is columns A-F wide and rows 1-20 long,
page 2 is columns G-L and rows 1-20, and page 3 is columns M-U × rows 1-20.
Then pages 4, 5, and 6 go to rows 21-40, and so on.
I don't want to scale the printout to be 1 page wide and n pages tall
because that would make the cells too small.
I would rather have a one-sheet-wide format
in which columns A-U of one row of the spreadsheet
print using two or three rows on the printed page
but keeping the Excel table format, if possible,
and then the next row of the spreadsheet, and so on.
How can I do this?
microsoft-excel printing
1
Does it solve the problem if you set the Cell alignment format to Wrap text so the full content of each cell can be displayed on the page (but skinnier than the default setting) - and then scale print so all columns fit on one page?
– mcalex
Feb 20 '17 at 4:24
1
I don’t know what’s in the OP’s spreadsheet (or in her mind), but, if she’s printing on 8½ × 11 inch paper in portrait orientation with one-inch margins, her effective printing surface is only 6.5 inches wide. Dividing that into 21 columns yields an average column width less than 0.31 inches. Even in landscape orientation with quarter-inch margins, the average column width is only 0.50 inches. I expect that data that “want” to be in columns approximately an inch wide would be cramped in such narrow columns, even with “Wrap text” enabled.
– Scott
Feb 20 '17 at 19:00
add a comment |
I have an Excel spreadsheet that has columns A-U and rows 1-50.
When I print it at a readable scale, it prints in a three-page-wide format:
page 1 is columns A-F wide and rows 1-20 long,
page 2 is columns G-L and rows 1-20, and page 3 is columns M-U × rows 1-20.
Then pages 4, 5, and 6 go to rows 21-40, and so on.
I don't want to scale the printout to be 1 page wide and n pages tall
because that would make the cells too small.
I would rather have a one-sheet-wide format
in which columns A-U of one row of the spreadsheet
print using two or three rows on the printed page
but keeping the Excel table format, if possible,
and then the next row of the spreadsheet, and so on.
How can I do this?
microsoft-excel printing
I have an Excel spreadsheet that has columns A-U and rows 1-50.
When I print it at a readable scale, it prints in a three-page-wide format:
page 1 is columns A-F wide and rows 1-20 long,
page 2 is columns G-L and rows 1-20, and page 3 is columns M-U × rows 1-20.
Then pages 4, 5, and 6 go to rows 21-40, and so on.
I don't want to scale the printout to be 1 page wide and n pages tall
because that would make the cells too small.
I would rather have a one-sheet-wide format
in which columns A-U of one row of the spreadsheet
print using two or three rows on the printed page
but keeping the Excel table format, if possible,
and then the next row of the spreadsheet, and so on.
How can I do this?
microsoft-excel printing
microsoft-excel printing
edited Feb 20 '17 at 18:46
Scott
16.1k113990
16.1k113990
asked Feb 20 '17 at 1:19
Katie SimsKatie Sims
612
612
1
Does it solve the problem if you set the Cell alignment format to Wrap text so the full content of each cell can be displayed on the page (but skinnier than the default setting) - and then scale print so all columns fit on one page?
– mcalex
Feb 20 '17 at 4:24
1
I don’t know what’s in the OP’s spreadsheet (or in her mind), but, if she’s printing on 8½ × 11 inch paper in portrait orientation with one-inch margins, her effective printing surface is only 6.5 inches wide. Dividing that into 21 columns yields an average column width less than 0.31 inches. Even in landscape orientation with quarter-inch margins, the average column width is only 0.50 inches. I expect that data that “want” to be in columns approximately an inch wide would be cramped in such narrow columns, even with “Wrap text” enabled.
– Scott
Feb 20 '17 at 19:00
add a comment |
1
Does it solve the problem if you set the Cell alignment format to Wrap text so the full content of each cell can be displayed on the page (but skinnier than the default setting) - and then scale print so all columns fit on one page?
– mcalex
Feb 20 '17 at 4:24
1
I don’t know what’s in the OP’s spreadsheet (or in her mind), but, if she’s printing on 8½ × 11 inch paper in portrait orientation with one-inch margins, her effective printing surface is only 6.5 inches wide. Dividing that into 21 columns yields an average column width less than 0.31 inches. Even in landscape orientation with quarter-inch margins, the average column width is only 0.50 inches. I expect that data that “want” to be in columns approximately an inch wide would be cramped in such narrow columns, even with “Wrap text” enabled.
– Scott
Feb 20 '17 at 19:00
1
1
Does it solve the problem if you set the Cell alignment format to Wrap text so the full content of each cell can be displayed on the page (but skinnier than the default setting) - and then scale print so all columns fit on one page?
– mcalex
Feb 20 '17 at 4:24
Does it solve the problem if you set the Cell alignment format to Wrap text so the full content of each cell can be displayed on the page (but skinnier than the default setting) - and then scale print so all columns fit on one page?
– mcalex
Feb 20 '17 at 4:24
1
1
I don’t know what’s in the OP’s spreadsheet (or in her mind), but, if she’s printing on 8½ × 11 inch paper in portrait orientation with one-inch margins, her effective printing surface is only 6.5 inches wide. Dividing that into 21 columns yields an average column width less than 0.31 inches. Even in landscape orientation with quarter-inch margins, the average column width is only 0.50 inches. I expect that data that “want” to be in columns approximately an inch wide would be cramped in such narrow columns, even with “Wrap text” enabled.
– Scott
Feb 20 '17 at 19:00
I don’t know what’s in the OP’s spreadsheet (or in her mind), but, if she’s printing on 8½ × 11 inch paper in portrait orientation with one-inch margins, her effective printing surface is only 6.5 inches wide. Dividing that into 21 columns yields an average column width less than 0.31 inches. Even in landscape orientation with quarter-inch margins, the average column width is only 0.50 inches. I expect that data that “want” to be in columns approximately an inch wide would be cramped in such narrow columns, even with “Wrap text” enabled.
– Scott
Feb 20 '17 at 19:00
add a comment |
3 Answers
3
active
oldest
votes
Use the page setup settings to scale the printout to be 1 page wide and n pages tall. The exact look of the dialog may depend on your printer.
One way to set this is: Go File > Print and select the Scaling dropdown under the Settings. Select Custom and on the Page tab set "Fit to" 1 page wide and 99 (or any large number) tall.
Edit: what you describe in your latest comment is not possible. Excel prints the grid. It can't re-arrange single rows and break them across to fit on one page.
Thank you for your answer and I'm sorry because I was not exactly clear. Right now when it prints page 1 will be columns A-F wide and rows 1-20 long. Page 2 would be columns G-L and rows 1-20 and page 3 is columns M-U rows 1-20. Then page 4 goes to rows 21-40 and so on. I don't want to lose the size of each cell by scaling I down I would rather Columns A-U of row 2 print and then columns A-U of row 3 and so on. It would need to be 2-3 rows on the page that prints but keeping the table format in Excel if possible.
– Katie Sims
Feb 20 '17 at 1:55
I edited my answer
– teylyn
Feb 20 '17 at 2:08
Is there a way to export the data to another program that could?
– Katie Sims
Feb 20 '17 at 2:23
You could use Word and Mail Merge to print one row of data per page.
– teylyn
Feb 20 '17 at 3:59
add a comment |
Without using an "export to external program" option, you have two choices, each with their drawbacks. Both use the File ⇨ Save As..
dialog
Option one, save as "Formatted Text (Space delimited)". This will create a .prn
file which you can rename as a .txt
file. It creates nice columns of data that is reasonably clean. The problem is that the line length is limited to 240 characters (IIRC), and then the lines are continued. This is odd behavior in that the continuation of the first line will be started after the first part of the last line, not wrapped directly below the first line. So with 80 lines in the sheet, line 81 would be part 2 of line 1, and 161 would be part 3 of line 1.
Option two, save as "Text (MS-DOS)". This will create a .txt
file, which you may not see the extension of if you have "Hide known extensions" turned on. Even if the extension is hidden you'll know it's a normal text file by the icon, and Windows Explorer will call it a "text file" in the "Type" column. The problem with this option is that if the columns vary much in size it gets rather sloppy. That's the size of the data in the column, not the size of one column compared to another. If all the data in a column is nearly the same length, then it will still line up well. This option does not have an imposed line length limit. Notepad, however is limited to 1024 characters per line, IIRC. This isn't likely to be a problem for your case, since it will wrap the lines in a normal fashion, and you intend to do that anyway.
After saving according to either of the two options above, and changing the extension if needed for option one, open the file in Notepad. Turn on word wrap, if it is off, by selecting Format ⇨ Word Wrap
on the menu. Now you can print it from Notepad, controlling the header, footer, and margins as you see fit.
If your dataset has a column with widely varied content lengths, and if your usage of the printouts allows, you should try to move that column, or columns, to the far right of the spreadsheet. Columns like description, address, last name, product name, etc. are candidates for such a move. Dates, prices, phone numbers, and often product codes, are usually fairly consistent in length, and therefore, work best near the front of the line.
add a comment |
It is possible to do something like what you want.
It’s a lot easier if all your columns are the same width,
and you want to put the same number of columns (e.g., 7; i.e., 21÷3)
on each row.
If that’s acceptable to you,
and assuming that you have your data on Sheet1
, A1:U50
, then enter
=OFFSET(Sheet1!$A$1, INT((ROW()-1)/3), COLUMN()-1+7*MOD(ROW()-1,3))
into Sheet2!A1
, drag/fill to the right to Column G
(i.e., the 7th column), and drag/fill down to Row 150 (3×50).
Cells Sheet2!A1:G1
will display Sheet1!A1:G1
.
(You may need to do some work to get it formatted correctly.)
But then Sheet2!A2:G2
(Row 2) will display Sheet1!H1:N1
,
and Sheet2!A3:G3
(Row 3) will display Sheet1!O1:U1
.
And Sheet2
Rows 4-6 will display Sheet1
Row 2, and so on.
For example, this Sheet1
:
yields this Sheet2
:
Then you just print Sheet2.
This is patterned after my answer
to How to split one column in Excel into multiple columns of 55 rows.
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1180662%2fhow-can-i-make-excel-print-row-by-row-one-page-wide%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Use the page setup settings to scale the printout to be 1 page wide and n pages tall. The exact look of the dialog may depend on your printer.
One way to set this is: Go File > Print and select the Scaling dropdown under the Settings. Select Custom and on the Page tab set "Fit to" 1 page wide and 99 (or any large number) tall.
Edit: what you describe in your latest comment is not possible. Excel prints the grid. It can't re-arrange single rows and break them across to fit on one page.
Thank you for your answer and I'm sorry because I was not exactly clear. Right now when it prints page 1 will be columns A-F wide and rows 1-20 long. Page 2 would be columns G-L and rows 1-20 and page 3 is columns M-U rows 1-20. Then page 4 goes to rows 21-40 and so on. I don't want to lose the size of each cell by scaling I down I would rather Columns A-U of row 2 print and then columns A-U of row 3 and so on. It would need to be 2-3 rows on the page that prints but keeping the table format in Excel if possible.
– Katie Sims
Feb 20 '17 at 1:55
I edited my answer
– teylyn
Feb 20 '17 at 2:08
Is there a way to export the data to another program that could?
– Katie Sims
Feb 20 '17 at 2:23
You could use Word and Mail Merge to print one row of data per page.
– teylyn
Feb 20 '17 at 3:59
add a comment |
Use the page setup settings to scale the printout to be 1 page wide and n pages tall. The exact look of the dialog may depend on your printer.
One way to set this is: Go File > Print and select the Scaling dropdown under the Settings. Select Custom and on the Page tab set "Fit to" 1 page wide and 99 (or any large number) tall.
Edit: what you describe in your latest comment is not possible. Excel prints the grid. It can't re-arrange single rows and break them across to fit on one page.
Thank you for your answer and I'm sorry because I was not exactly clear. Right now when it prints page 1 will be columns A-F wide and rows 1-20 long. Page 2 would be columns G-L and rows 1-20 and page 3 is columns M-U rows 1-20. Then page 4 goes to rows 21-40 and so on. I don't want to lose the size of each cell by scaling I down I would rather Columns A-U of row 2 print and then columns A-U of row 3 and so on. It would need to be 2-3 rows on the page that prints but keeping the table format in Excel if possible.
– Katie Sims
Feb 20 '17 at 1:55
I edited my answer
– teylyn
Feb 20 '17 at 2:08
Is there a way to export the data to another program that could?
– Katie Sims
Feb 20 '17 at 2:23
You could use Word and Mail Merge to print one row of data per page.
– teylyn
Feb 20 '17 at 3:59
add a comment |
Use the page setup settings to scale the printout to be 1 page wide and n pages tall. The exact look of the dialog may depend on your printer.
One way to set this is: Go File > Print and select the Scaling dropdown under the Settings. Select Custom and on the Page tab set "Fit to" 1 page wide and 99 (or any large number) tall.
Edit: what you describe in your latest comment is not possible. Excel prints the grid. It can't re-arrange single rows and break them across to fit on one page.
Use the page setup settings to scale the printout to be 1 page wide and n pages tall. The exact look of the dialog may depend on your printer.
One way to set this is: Go File > Print and select the Scaling dropdown under the Settings. Select Custom and on the Page tab set "Fit to" 1 page wide and 99 (or any large number) tall.
Edit: what you describe in your latest comment is not possible. Excel prints the grid. It can't re-arrange single rows and break them across to fit on one page.
edited Feb 20 '17 at 2:08
answered Feb 20 '17 at 1:32
teylynteylyn
17.4k22539
17.4k22539
Thank you for your answer and I'm sorry because I was not exactly clear. Right now when it prints page 1 will be columns A-F wide and rows 1-20 long. Page 2 would be columns G-L and rows 1-20 and page 3 is columns M-U rows 1-20. Then page 4 goes to rows 21-40 and so on. I don't want to lose the size of each cell by scaling I down I would rather Columns A-U of row 2 print and then columns A-U of row 3 and so on. It would need to be 2-3 rows on the page that prints but keeping the table format in Excel if possible.
– Katie Sims
Feb 20 '17 at 1:55
I edited my answer
– teylyn
Feb 20 '17 at 2:08
Is there a way to export the data to another program that could?
– Katie Sims
Feb 20 '17 at 2:23
You could use Word and Mail Merge to print one row of data per page.
– teylyn
Feb 20 '17 at 3:59
add a comment |
Thank you for your answer and I'm sorry because I was not exactly clear. Right now when it prints page 1 will be columns A-F wide and rows 1-20 long. Page 2 would be columns G-L and rows 1-20 and page 3 is columns M-U rows 1-20. Then page 4 goes to rows 21-40 and so on. I don't want to lose the size of each cell by scaling I down I would rather Columns A-U of row 2 print and then columns A-U of row 3 and so on. It would need to be 2-3 rows on the page that prints but keeping the table format in Excel if possible.
– Katie Sims
Feb 20 '17 at 1:55
I edited my answer
– teylyn
Feb 20 '17 at 2:08
Is there a way to export the data to another program that could?
– Katie Sims
Feb 20 '17 at 2:23
You could use Word and Mail Merge to print one row of data per page.
– teylyn
Feb 20 '17 at 3:59
Thank you for your answer and I'm sorry because I was not exactly clear. Right now when it prints page 1 will be columns A-F wide and rows 1-20 long. Page 2 would be columns G-L and rows 1-20 and page 3 is columns M-U rows 1-20. Then page 4 goes to rows 21-40 and so on. I don't want to lose the size of each cell by scaling I down I would rather Columns A-U of row 2 print and then columns A-U of row 3 and so on. It would need to be 2-3 rows on the page that prints but keeping the table format in Excel if possible.
– Katie Sims
Feb 20 '17 at 1:55
Thank you for your answer and I'm sorry because I was not exactly clear. Right now when it prints page 1 will be columns A-F wide and rows 1-20 long. Page 2 would be columns G-L and rows 1-20 and page 3 is columns M-U rows 1-20. Then page 4 goes to rows 21-40 and so on. I don't want to lose the size of each cell by scaling I down I would rather Columns A-U of row 2 print and then columns A-U of row 3 and so on. It would need to be 2-3 rows on the page that prints but keeping the table format in Excel if possible.
– Katie Sims
Feb 20 '17 at 1:55
I edited my answer
– teylyn
Feb 20 '17 at 2:08
I edited my answer
– teylyn
Feb 20 '17 at 2:08
Is there a way to export the data to another program that could?
– Katie Sims
Feb 20 '17 at 2:23
Is there a way to export the data to another program that could?
– Katie Sims
Feb 20 '17 at 2:23
You could use Word and Mail Merge to print one row of data per page.
– teylyn
Feb 20 '17 at 3:59
You could use Word and Mail Merge to print one row of data per page.
– teylyn
Feb 20 '17 at 3:59
add a comment |
Without using an "export to external program" option, you have two choices, each with their drawbacks. Both use the File ⇨ Save As..
dialog
Option one, save as "Formatted Text (Space delimited)". This will create a .prn
file which you can rename as a .txt
file. It creates nice columns of data that is reasonably clean. The problem is that the line length is limited to 240 characters (IIRC), and then the lines are continued. This is odd behavior in that the continuation of the first line will be started after the first part of the last line, not wrapped directly below the first line. So with 80 lines in the sheet, line 81 would be part 2 of line 1, and 161 would be part 3 of line 1.
Option two, save as "Text (MS-DOS)". This will create a .txt
file, which you may not see the extension of if you have "Hide known extensions" turned on. Even if the extension is hidden you'll know it's a normal text file by the icon, and Windows Explorer will call it a "text file" in the "Type" column. The problem with this option is that if the columns vary much in size it gets rather sloppy. That's the size of the data in the column, not the size of one column compared to another. If all the data in a column is nearly the same length, then it will still line up well. This option does not have an imposed line length limit. Notepad, however is limited to 1024 characters per line, IIRC. This isn't likely to be a problem for your case, since it will wrap the lines in a normal fashion, and you intend to do that anyway.
After saving according to either of the two options above, and changing the extension if needed for option one, open the file in Notepad. Turn on word wrap, if it is off, by selecting Format ⇨ Word Wrap
on the menu. Now you can print it from Notepad, controlling the header, footer, and margins as you see fit.
If your dataset has a column with widely varied content lengths, and if your usage of the printouts allows, you should try to move that column, or columns, to the far right of the spreadsheet. Columns like description, address, last name, product name, etc. are candidates for such a move. Dates, prices, phone numbers, and often product codes, are usually fairly consistent in length, and therefore, work best near the front of the line.
add a comment |
Without using an "export to external program" option, you have two choices, each with their drawbacks. Both use the File ⇨ Save As..
dialog
Option one, save as "Formatted Text (Space delimited)". This will create a .prn
file which you can rename as a .txt
file. It creates nice columns of data that is reasonably clean. The problem is that the line length is limited to 240 characters (IIRC), and then the lines are continued. This is odd behavior in that the continuation of the first line will be started after the first part of the last line, not wrapped directly below the first line. So with 80 lines in the sheet, line 81 would be part 2 of line 1, and 161 would be part 3 of line 1.
Option two, save as "Text (MS-DOS)". This will create a .txt
file, which you may not see the extension of if you have "Hide known extensions" turned on. Even if the extension is hidden you'll know it's a normal text file by the icon, and Windows Explorer will call it a "text file" in the "Type" column. The problem with this option is that if the columns vary much in size it gets rather sloppy. That's the size of the data in the column, not the size of one column compared to another. If all the data in a column is nearly the same length, then it will still line up well. This option does not have an imposed line length limit. Notepad, however is limited to 1024 characters per line, IIRC. This isn't likely to be a problem for your case, since it will wrap the lines in a normal fashion, and you intend to do that anyway.
After saving according to either of the two options above, and changing the extension if needed for option one, open the file in Notepad. Turn on word wrap, if it is off, by selecting Format ⇨ Word Wrap
on the menu. Now you can print it from Notepad, controlling the header, footer, and margins as you see fit.
If your dataset has a column with widely varied content lengths, and if your usage of the printouts allows, you should try to move that column, or columns, to the far right of the spreadsheet. Columns like description, address, last name, product name, etc. are candidates for such a move. Dates, prices, phone numbers, and often product codes, are usually fairly consistent in length, and therefore, work best near the front of the line.
add a comment |
Without using an "export to external program" option, you have two choices, each with their drawbacks. Both use the File ⇨ Save As..
dialog
Option one, save as "Formatted Text (Space delimited)". This will create a .prn
file which you can rename as a .txt
file. It creates nice columns of data that is reasonably clean. The problem is that the line length is limited to 240 characters (IIRC), and then the lines are continued. This is odd behavior in that the continuation of the first line will be started after the first part of the last line, not wrapped directly below the first line. So with 80 lines in the sheet, line 81 would be part 2 of line 1, and 161 would be part 3 of line 1.
Option two, save as "Text (MS-DOS)". This will create a .txt
file, which you may not see the extension of if you have "Hide known extensions" turned on. Even if the extension is hidden you'll know it's a normal text file by the icon, and Windows Explorer will call it a "text file" in the "Type" column. The problem with this option is that if the columns vary much in size it gets rather sloppy. That's the size of the data in the column, not the size of one column compared to another. If all the data in a column is nearly the same length, then it will still line up well. This option does not have an imposed line length limit. Notepad, however is limited to 1024 characters per line, IIRC. This isn't likely to be a problem for your case, since it will wrap the lines in a normal fashion, and you intend to do that anyway.
After saving according to either of the two options above, and changing the extension if needed for option one, open the file in Notepad. Turn on word wrap, if it is off, by selecting Format ⇨ Word Wrap
on the menu. Now you can print it from Notepad, controlling the header, footer, and margins as you see fit.
If your dataset has a column with widely varied content lengths, and if your usage of the printouts allows, you should try to move that column, or columns, to the far right of the spreadsheet. Columns like description, address, last name, product name, etc. are candidates for such a move. Dates, prices, phone numbers, and often product codes, are usually fairly consistent in length, and therefore, work best near the front of the line.
Without using an "export to external program" option, you have two choices, each with their drawbacks. Both use the File ⇨ Save As..
dialog
Option one, save as "Formatted Text (Space delimited)". This will create a .prn
file which you can rename as a .txt
file. It creates nice columns of data that is reasonably clean. The problem is that the line length is limited to 240 characters (IIRC), and then the lines are continued. This is odd behavior in that the continuation of the first line will be started after the first part of the last line, not wrapped directly below the first line. So with 80 lines in the sheet, line 81 would be part 2 of line 1, and 161 would be part 3 of line 1.
Option two, save as "Text (MS-DOS)". This will create a .txt
file, which you may not see the extension of if you have "Hide known extensions" turned on. Even if the extension is hidden you'll know it's a normal text file by the icon, and Windows Explorer will call it a "text file" in the "Type" column. The problem with this option is that if the columns vary much in size it gets rather sloppy. That's the size of the data in the column, not the size of one column compared to another. If all the data in a column is nearly the same length, then it will still line up well. This option does not have an imposed line length limit. Notepad, however is limited to 1024 characters per line, IIRC. This isn't likely to be a problem for your case, since it will wrap the lines in a normal fashion, and you intend to do that anyway.
After saving according to either of the two options above, and changing the extension if needed for option one, open the file in Notepad. Turn on word wrap, if it is off, by selecting Format ⇨ Word Wrap
on the menu. Now you can print it from Notepad, controlling the header, footer, and margins as you see fit.
If your dataset has a column with widely varied content lengths, and if your usage of the printouts allows, you should try to move that column, or columns, to the far right of the spreadsheet. Columns like description, address, last name, product name, etc. are candidates for such a move. Dates, prices, phone numbers, and often product codes, are usually fairly consistent in length, and therefore, work best near the front of the line.
answered Feb 20 '17 at 6:43
Gypsy SpellweaverGypsy Spellweaver
1,6131621
1,6131621
add a comment |
add a comment |
It is possible to do something like what you want.
It’s a lot easier if all your columns are the same width,
and you want to put the same number of columns (e.g., 7; i.e., 21÷3)
on each row.
If that’s acceptable to you,
and assuming that you have your data on Sheet1
, A1:U50
, then enter
=OFFSET(Sheet1!$A$1, INT((ROW()-1)/3), COLUMN()-1+7*MOD(ROW()-1,3))
into Sheet2!A1
, drag/fill to the right to Column G
(i.e., the 7th column), and drag/fill down to Row 150 (3×50).
Cells Sheet2!A1:G1
will display Sheet1!A1:G1
.
(You may need to do some work to get it formatted correctly.)
But then Sheet2!A2:G2
(Row 2) will display Sheet1!H1:N1
,
and Sheet2!A3:G3
(Row 3) will display Sheet1!O1:U1
.
And Sheet2
Rows 4-6 will display Sheet1
Row 2, and so on.
For example, this Sheet1
:
yields this Sheet2
:
Then you just print Sheet2.
This is patterned after my answer
to How to split one column in Excel into multiple columns of 55 rows.
add a comment |
It is possible to do something like what you want.
It’s a lot easier if all your columns are the same width,
and you want to put the same number of columns (e.g., 7; i.e., 21÷3)
on each row.
If that’s acceptable to you,
and assuming that you have your data on Sheet1
, A1:U50
, then enter
=OFFSET(Sheet1!$A$1, INT((ROW()-1)/3), COLUMN()-1+7*MOD(ROW()-1,3))
into Sheet2!A1
, drag/fill to the right to Column G
(i.e., the 7th column), and drag/fill down to Row 150 (3×50).
Cells Sheet2!A1:G1
will display Sheet1!A1:G1
.
(You may need to do some work to get it formatted correctly.)
But then Sheet2!A2:G2
(Row 2) will display Sheet1!H1:N1
,
and Sheet2!A3:G3
(Row 3) will display Sheet1!O1:U1
.
And Sheet2
Rows 4-6 will display Sheet1
Row 2, and so on.
For example, this Sheet1
:
yields this Sheet2
:
Then you just print Sheet2.
This is patterned after my answer
to How to split one column in Excel into multiple columns of 55 rows.
add a comment |
It is possible to do something like what you want.
It’s a lot easier if all your columns are the same width,
and you want to put the same number of columns (e.g., 7; i.e., 21÷3)
on each row.
If that’s acceptable to you,
and assuming that you have your data on Sheet1
, A1:U50
, then enter
=OFFSET(Sheet1!$A$1, INT((ROW()-1)/3), COLUMN()-1+7*MOD(ROW()-1,3))
into Sheet2!A1
, drag/fill to the right to Column G
(i.e., the 7th column), and drag/fill down to Row 150 (3×50).
Cells Sheet2!A1:G1
will display Sheet1!A1:G1
.
(You may need to do some work to get it formatted correctly.)
But then Sheet2!A2:G2
(Row 2) will display Sheet1!H1:N1
,
and Sheet2!A3:G3
(Row 3) will display Sheet1!O1:U1
.
And Sheet2
Rows 4-6 will display Sheet1
Row 2, and so on.
For example, this Sheet1
:
yields this Sheet2
:
Then you just print Sheet2.
This is patterned after my answer
to How to split one column in Excel into multiple columns of 55 rows.
It is possible to do something like what you want.
It’s a lot easier if all your columns are the same width,
and you want to put the same number of columns (e.g., 7; i.e., 21÷3)
on each row.
If that’s acceptable to you,
and assuming that you have your data on Sheet1
, A1:U50
, then enter
=OFFSET(Sheet1!$A$1, INT((ROW()-1)/3), COLUMN()-1+7*MOD(ROW()-1,3))
into Sheet2!A1
, drag/fill to the right to Column G
(i.e., the 7th column), and drag/fill down to Row 150 (3×50).
Cells Sheet2!A1:G1
will display Sheet1!A1:G1
.
(You may need to do some work to get it formatted correctly.)
But then Sheet2!A2:G2
(Row 2) will display Sheet1!H1:N1
,
and Sheet2!A3:G3
(Row 3) will display Sheet1!O1:U1
.
And Sheet2
Rows 4-6 will display Sheet1
Row 2, and so on.
For example, this Sheet1
:
yields this Sheet2
:
Then you just print Sheet2.
This is patterned after my answer
to How to split one column in Excel into multiple columns of 55 rows.
answered Feb 20 '17 at 8:37
ScottScott
16.1k113990
16.1k113990
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1180662%2fhow-can-i-make-excel-print-row-by-row-one-page-wide%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Does it solve the problem if you set the Cell alignment format to Wrap text so the full content of each cell can be displayed on the page (but skinnier than the default setting) - and then scale print so all columns fit on one page?
– mcalex
Feb 20 '17 at 4:24
1
I don’t know what’s in the OP’s spreadsheet (or in her mind), but, if she’s printing on 8½ × 11 inch paper in portrait orientation with one-inch margins, her effective printing surface is only 6.5 inches wide. Dividing that into 21 columns yields an average column width less than 0.31 inches. Even in landscape orientation with quarter-inch margins, the average column width is only 0.50 inches. I expect that data that “want” to be in columns approximately an inch wide would be cramped in such narrow columns, even with “Wrap text” enabled.
– Scott
Feb 20 '17 at 19:00