IIL-logo-globe_250x200px (003)

SHARE THIS

How to Hide and Show Rows and Columns in Microsoft Excel

How to Hide and Show Rows and Columns in Excel

By Bob Umlas, Excel MVP

In this article, we’ll explore the various ways to hide and show rows and columns. The bulk of the discussion will address columns, but it applies to rows as well (unless otherwise noted).

You can change a column width by dragging the column separator (the little line between columns) left or right:

dragging the column separator (the little line between columns) left or right

In the following figure, the column separator between D and E is clicked (notice the cursor shape), the mouse is held down, and dragged to the right:

the mouse is held down, and dragged to the right

If you drag it to the left enough it will narrow or hide the column (when the width becomes 0 it’s hidden):

narrow or hide the column

When you let go of the mouse there’s a double-line indicating that column D is missing (this shape varies with the version of Excel used):

column is missingbut when headings are not shown (from the View tab you can turn them off), there’s no indication a column is missing. Here, it looks like cell D3 is selected, but in fact it’s E3 because column D is hidden:

hidden column

Another way to hide the column is select any cell in the column, say D1, then on the Home tab, in the Cells group, there’s a Format dropdown which has access to the Column Width:

Format dropdown

When you issue the command, you get a dialog with the current width, like this:

current width

and you can type in any number from 0 to 255. When you type 0, the column is hidden.

You can also hide a column via Home/Format/Hide & Unhide/Hide Columns.

There’s also a shortcut to hide a column. Whichever column is selected will become hidden when you press ctrl/0 (a row will hide when you press ctrl/9).

All of the above applies to more than one column as well. If you select A1:E1 and use any of the methods, all 5 columns will hide.

Okay, how about unhiding? Again, there are several ways. It’s not as easy to select a hidden column, however! So you can type the cell address into the name box to get the cursor into the column. Or, you can press the F5 key (Go To), and type in the cell address. Here, D1 was entered into the name box:

name box

and then cell D1 became selected. You can unhide this with the column width command we saw above, change the 0 which will be there to 8.43, or any number, etc. This is not literally unhiding – it’s resetting the column width to a number. Unhiding will restore the width to what it was before it was hidden! You can unhide it by Home/Format/Hide & Unhide/Unhide Columns:

Unhide/Unhide Columns

Ctrl/shift/0 (ctrl/shift/9 to unhide rows) will do the job as well. You can also select the columns before and after the hidden column and unhide them (only the hidden one will be affected). Here, C thru E is selected:

And finally, you can carefully position the cursor a little to the right of the D-column separator (in this example) so it looks something like this:

then drag to the right which will reveal column D as well!

Lastly, suppose you have text like this:

And you want to widen column C so it all fits in it. You can drag the column separator between C and D to the right and visually adjust it, or you can double-click the column separator which does a “best-fit” for the longest cell in that column, or you can select a few cells (vertically or horizontally) or the entire column, and use Home/Format/AutoFit Column Width:

Enjoy!

If you are interested in bringing Excel MVP Bob Umlas into your company (either virtually or traditionally) to do Excel consulting and/or training work, contact roxi.nevin@iil.com.


About the Author
Bob Umlas has been using Excel since its inception in 1986. He has had more than 300 articles published on subjects ranging from beginner to advanced, VBA, tips, shortcuts, and general techniques using virtually all aspects of Excel. He is the longest running Microsoft Excel MVP in the world (25 years). He has been the technical editor of (about 20) of Bill Jelen’s (MrExcel) Excel books. Bob speaks at Excel conferences around the world on his favorite topic, Tips & Tricks, which wows even the experts. He is the author of 3 Excel books and is the current leader of NYC’s Excel Special Interest Group (which meets on the 2nd Tuesday of each month).

Books:
This isn’t Excel, it’s Magic!
Excel Outside the Box
More Excel Outside the Box

Scroll to Top