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

Printing Columns and Rows

by Allen Wyatt
(last updated January 22, 2018)


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, 2013, 2016, 2019, and Excel in Office 365. 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. ...


Picking Which Outlook Address to Use in Word

If your Outlook contacts have more than one address each, you may want to select which one is used in Word. This tip ...

Discover More

Turning Off Hyperlink Activation

Does it bother you when you enter a URL and it becomes "active" as soon as you press Enter? Here's how you can turn off ...

Discover More

Copying to Very Large Ranges

Using the mouse to select a large cell range can be frustratingly slow. If you want to make copying to a large range of ...

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)

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

Printing Multiple Pages On a Piece of Paper

If you want to save paper on a printout, you might consider printing multiple pages on a single piece of paper. This can ...

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

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}] 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 1 + 3?

2018-01-22 18:38:24


Thanks Anne- I didn't know the linked picture option existed!

2018-01-22 03:24:38


If you want to print the "crossed" data as it appears on the worksheet, how about a couple of simple macros:

Sub MyHighlight()

Cells.Font.Color = vbWhite
Selection.EntireRow.Font.Color = vbBlack
Selection.EntireColumn.Font.Color = vbBlack

End Sub
Sub HighlightOff()

Cells.Font.Color = vbBlack
End Sub

Select the cell of interest, run the first macro before printing and the second after.

You could achieve a nice "grayed out" effect by changing the first line of the first macro to Cells.Font.Color = RGB(200,200,200)

These macros assume you have a black and white worksheet. You could develop a more complex macro that copied the sheet, changed the formatting on the copy (maybe making each cell's font colour the same as its fill colour), print and then delete the copied sheet, all in a "Print Selection" macro

2014-06-10 09:51:16


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


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. ((

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:


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:


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;
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


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.

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

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.


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.