Written by Allen Wyatt (last updated February 8, 2022)
This tip applies to Excel 2007, 2010, 2013, and 2016
I regularly hide and unhide columns in my worksheets. If I have an entire range of columns hidden, I find it a bother to unhide a single column out of all those hidden. For instance, I may hide columns C:M and want to later unhide column F. There are a number of ways this can be done:
There are a few other ways to unhide the column, as well, but (as I said) I find such approaches a bother. Unhiding either a single column or a range of columns is very easy to do through a spiffy little macro. Consider the following:
Sub UnhideSingleColumn() Dim Col As String Dim rng As Range StartHere: Col = InputBox("Enter column to unhide.", "Unhide Column") If Col = "" Then Exit Sub On Error Resume Next ' if not a valid range, an error occurs Set rng = ActiveSheet.Columns(Col) If Err.Number <> 0 Then On Error GoTo 0 Err.Clear MsgBox "Invalid input! Please input a valid column." GoTo StartHere End If rng.EntireColumn.Hidden = False MsgBox "Column " & UCase(Col) & " is now visible.", _ vbOKOnly, "Unhide Specified Column" Set rng = Nothing On Error GoTo 0 End Sub
The macro prompts the user for which column should be made visible, and then tries to select that column. If the column cannot be selected, then an error is generated and the user is again asked for input. If the column can be selected, then its .Hidden property is turned off, thereby making the column visible.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11471) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Unhiding a Single Column.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Want to hide a column so it doesn't appear in the worksheet? It's easy to do using the formatting capabilities of Excel.
Discover MoreOne way you can widen the columns in a worksheet to fit whatever is in the column is by double-clicking the right edge of ...
Discover MoreDo you want to set a column's width based on whatever is in the currently selected cell? There are actually a number of ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-07-05 06:31:35
Willy Vanhaelen
If you prefer not to use the mouse to activate the Name box, you can simply press the F5 key (Go To) and enter F1 in the reference box (or whatever column you want to unhide ) and press Ctrl+Shift+0. That way you can keep your hands on the keyboard.
2021-07-04 04:01:16
Roy
Or you can use the Immediate Window to run the following:
ActiveSheet.Columns("F").EntireColumn.Hidden=False
Don't even have to select the column or a cell in it if you will type the F in. Or use the Name box like Allen to specify the column. Whichever you find fits your comfort with it.
Anything you can do with one line, right?
I have a spreadsheet with a ton of these, gonna add this one too. What I want to do in column A. The command in column B. Any notes about using it in column C. Add to it over the years, look through it now and then. Sometimes I think to insert a new column A to mark some as favorites, Sort them to the top, but I'm basically a lazy man so...
2020-07-14 05:24:58
I used the second opion and it worked to unhide a single column from a series of hidden columns. Thanks. You are a life saver.
2017-04-07 13:56:41
Craig Abt
Recent subscriber.
I hide columns in large data tables all the time just so that i can see the ones i need. But invariably, i end up needing one of the ones I've hidden. This is a nice little navigational tip. Thanks
2017-04-03 10:48:04
Gary Lundblad
Thank you for the tip, but the CTRL+SHIFT+0 does not unhide for me either. I did enter the F1 in the name box, but nothing happens when I type that key combination.
Gary
2017-04-03 03:37:50
Dave
Excellent tip, thanks. This is definitely one for the QAT.
I found that you can also unhide a range of contiguous columns by entering, for example, R:T when prompted.
Ctrl+0 hides columns for me, but Shift+Ctrl+0 does not. Any clues?
Ctrl+9 and Shift+Ctrl+9 hides and unhides rows perfectly.
2017-04-02 10:55:09
Fine
It is working super
Thanks
Srinivasan k
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments