Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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 Columns and Rows.

Printing Columns and Rows

by Allen Wyatt
(last updated June 7, 2014)

9

Brent asked if it was possible to print a single column and a single row from a worksheet on the same piece of paper. (His boss wanted to see just the "crossed" information.) Unfortunately, there is no intrinsic way within Excel to specify to print only a single column and a single row. If you select both the column and row you want to print, and then choose to print just the selection, Excel still treats them as separate selections and prints them in that way. There are a couple of workarounds, however.

The first approach is to simply "hide" the information you don't want to print by setting its font color to white. You can do that by following these general steps:

  1. Choose the first range of cells you don't want to appear on the printout.
  2. Change the color of the text in those cells to white.
  3. Repeat steps 1 and 2 for each range you don't want on the printout.
  4. Print your worksheet as normal.
  5. Select the entire worksheet (press Ctrl+A).
  6. Change the color of the text in the cells to Automatic.

Another approach is to copy the row and column to a different worksheet. This is quick and easy to do using the keyboard (Ctrl+C to copy and Ctrl+V to paste), but there is a drawback. If the row or column you are copying contains formulas that rely on other areas of the worksheet, the copied data will not show the proper results. Thus, the best "cut and paste" approach would be as follows:

  1. Insert a new, blank worksheet in your workbook.
  2. Switch to the worksheet that contains the row and column you want to copy.
  3. Select the column you want to copy and press Ctrl+C.
  4. Switch to the new worksheet and select the column where you want it pasted.
  5. Display the Home tab of the ribbon.
  6. Click the small down-arrow under the Paste tool, near the left edge of the ribbon, and then choose Paste Values.
  7. Switch back to the worksheet that contains your original data.
  8. Select the row you want to copy and press Ctrl+C.
  9. Switch to the new worksheet and select the row where you want it pasted.
  10. Again paste the information by following steps 5 and 6.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9549) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Printing Columns and Rows.

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

Defining a Name

One of the great features of Excel is that it allows you to use named ranges. These can make your formulas much easier to ...

Discover More

Adjusting Space Before

If you need to adjust the space that appears before a paragraph, there are several ways you can approach the adjustment. Here ...

Discover More

Erroneous Out of Space Messages

While they are less common these days than they used to be, you still might see an error that indicates Word is out of disk ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Custom Page Numbers on Printouts

Excel is rather weak on giving you control over how page numbers appear on a printout. This is never more apparent than when ...

Discover More

Printing a Chart

It is inevitable that if you spend time creating a chart you will want to print that chart on your printer. Here's how you ...

Discover More

Using Less Paper on Printouts

If a worksheet contains nothing but a bunch of values in column A, you may be loathe to print the worksheet and "waste" a ...

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}] 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 one more than 9?

2014-06-10 09:51:16

Anne

Addendum to my comment.

For Option 2, it's not really paste/special. The feature is not listed there, but it is in the drop down menu of the paste icon as I've described.


2014-06-10 09:14:07

Anne

Why not use snapshots.

I created a spreadsheet where I had done varying analysis in several tables on one sheet.

Subsequently, my boss was only interested in seeing one or two of the tables. So I snapshot the ones she was interested in and place them in another tab.

You can do this one of two ways.

You can use the "camera" feature that comes in the later versions of Excel, starting in 2010. (I'm not sure about 2007) The camera can be added to the tab by selecting Options, All Commands, and selecting 'camera', and adding it to your ribbon. ((http://www.addictivetips.com/microsoft-office/camera-tool-function-in-excel-2010/))

Selecting the rows/columns to display:
Highlight the data you want to display and click the 'camera'. Then go to the tab or area you want to place the snapshot and click there. Your data will then display like a picture, with one difference. Every time you update your data, the snapshot will update.

The 2nd option is to use the copy/paste special.

Copy the selection of data you want to isolate, and using the drop down menu from the paste Icon on the ribbon, under "other paste options" on the bottom, select linked picture (the last option on the right, the one with the link symbol. Using this option will mean that your the data in the pasted picture link will also update each time you update your data.


2014-06-07 22:04:26

Andrew Frodsham

David, you are asking a question already dealt with in the related topic "Printing a Single Column in Multiple Columns".

Copying the data in to Word has some plus points:

Firstly, the columns will automatically 'snake' if you decide to reduce the font size (and/or increase the number of columns) so that you can get more information on one page.

Word will also cope well if you have some cells which take up more than one line of text (cell wrapping).

Word is also able to repeat header row(s) at the top of each column using the 'repeat as header row at top of each page' feature [select the row(s) Layout Tab Repeat Header Rows Button].

Also, by inserting a continuous section break at the end of the block of data, the number of rows required will be trimmed for the number of columns you have set.

For example, if you have 100 rows of data, and 40 rows will fit on a page: if you select a 3-column layout Word will display this as 40 rows in the first two columns and the remaining 20 in the third column. If you insert a continuous section break after the data [page layout Breaks Continuous] it will be displayed as two columns of 34 and a final column of 32 (which is the closest Word can get to a flush bottom as it can get).

The 'line between' option in the columnar set up is often visually attractive.

Using Excel you can use the VB script provided in the Related Topic mentioned above (for a single column of original data).

You could also try the following procedure within Excel:

Give your original data a named range -- I shall use 'Fred'.
Name a single cell 'MaxRows' and enter the number of rows you would like within each output 'column' into that cell.
Name another single cell 'Gap'. Enter the number of blank columns you would like to appear between blocks of snaked-columns in your replicated-data area.

Select the top left cell of where you would like your replicated data to appear (maybe on a new tab) and name that cell 'TopLeft'.
In that cell enter the formula:

=IF((MOD((COLUMN()-COLUMN(TopLeft)),(COLUMNS(Fred)+Gap))+1)>COLUMNS(Fred),"",OFFSET(Fred,(MaxRows*(ROUNDDOWN((COLUMN()-COLUMN(TopLeft))/(COLUMNS(Fred)+Gap),0)))+ROW()-ROW(TopLeft),(MOD((COLUMN()-COLUMN(TopLeft)),(COLUMNS(Fred)+Gap))+1)-1,1,1))

The formula in that cell can now be dragged down the requisite number of rows; and that column of formulae can then be dragged right the requisite number of columns. For example if you have a block of data which is 2 columns x 100 rows and you want it to appear as 4 blocks of 2 columns x 25 rows with one blank column gap between each block, you will drag down an extra 24 rows (25 less the cell already populated) and drag across an extra 10 columns (4 blocks of 2, ie 8, plus 3 blank columns between the 4 blocks (making 11 in total) less the column already populated with formulae).

If the last block of columns does not need to be as long as the others, just delete the surplus formulae.

To get the hang of this it is worth having the left-most column of your original data contain a serial number (1, 2, 3...). It will then be easy to see that you've copied the output formula down and across enough (and not too much).

Strictly speaking, the named range of the original data (in this case 'Fred') ONLY NEEDS TO reference the TOP-MOST ROW you would like to output, and needs to reference only the columns which you would like to output (AND NO OTHERS). The depth (number of rows) of the named range does not make any difference to the number of rows output -- that is dictated by the formulae in the cells in the output area. The named range must also be contiguous; a multi-block named range will fail.



2014-06-07 17:12:36

David Bender

When printing one, two or three columns in Excel, is it possible to get them to wrap on the printed page, so that 60+ rows will all automatically print on sheet of paper?
Currently I copy and paste them into MS word and use the multiple Columns feature.


2014-06-07 13:24:56

Andrew Frodsham

Firstly, name the cell which is at the intersection of the row and the column which you want printing as "Intersection".

Then select your whole spreadsheet and create a conditional format using the following criteria:

=NOT(OR(ROW()=ROW(Intersection),COLUMN()=COLUMN(Intersection)))

using the format of white text.

If your boss wants a different row / column printing, just change the reference of the Named Range.

One of the beauties of this method is that it works for more than one row and / or column. For example, for two rows and two columns name the 2 x 2 intersection, "Intersection".

However, the rows and columns do have to be contiguous, ie the named range, Intersection, can only be one block, not multiple blocks. If you name Intersection multiple areas the conditional formatting will not operate.

To print out all rows and columns, just delete the named range.


2014-06-07 10:14:28

Steve J

Assume data in A5:F10 named DATA

In J5 enter formula;
=IF(OR(ROW()=$J$2+4,COLUMN()=$K$2+9),INDEX(data,ROWS($J$2:$J2),COLUMNS($J$4:J$4)),"")
and copy to J5:O10

in J2 enter the row of the data you want to print (NOT the sheet row)
in K2 enter the column of the data you want to print.


2014-06-07 07:02:12

Steve J

If you want to highlight the junction / intersection of the row & column you can use the following Conditional Formatting rule & set a different format.
Make this the first rule otherwise your formatting gets over written

=AND(COLUMN()=$C$2,ROW()=$B$2)


2014-06-07 06:42:50

Steve J

Error correction

My mistake, you need all 4 rules in place for this to work.


2014-06-07 06:10:18

Steve J

You can achieve this with conditional formatting.
Using 4 rules in the following order.
Use 2 cells to indicate the column & row you wish to highlight (C2 & B2 used below).
Set the formatting as you would like it to print / display. If you just want black to highlight you could just use the last 2 rules to lighten or hide the text.

=COLUMN()=$C$2
=ROW()=$B$2
set the format to a highlight text colour

=COLUMN()<>$C$4 set the text to a light grey or white - if you don't want to see it at all
=ROW()<>$B$2

If you don't understand Conditional Formatting go the "Our Products - Excel Products" link below & purchase Excel Conditional Formatting (No I don't get paid for the endorsement !!)

Hope this works for you.

Steve


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.