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: Hiding Columns Based on a Cell Value.

Hiding Columns Based on a Cell Value

by Allen Wyatt
(last updated June 21, 2018)


Excel's great conditional formatting capabilities allow you to change the formatting of cells based on the content of a cell. There is no way, unfortunately, to easily hide entire columns of data based on the value of a particular cell.

You can, however, achieve the desired effect by using a macro to analyze the cell and adjust the Hidden attribute of the row you want to conditionally hide. The following simple macro, for instance, examines the contents of cell B4 and, if the cell contains 0, hides column H. If cell B4 does not contain 0, then column H is displayed.

Sub HideColumn1()
    If Range("B4").Value = 0 Then
        Columns("H").EntireColumn.Hidden = True
        Columns("H").EntireColumn.Hidden = False
    End If
End Sub

If you want the hiding and unhiding of the column to be done in real time, you can use the following version of the macro. Just make sure that you put this version in the code window for the worksheet on which you want it to work.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("B4").Value = 0 Then
        Columns("H").EntireColumn.Hidden = True
        Columns("H").EntireColumn.Hidden = False
    End If
End Sub

Notice that the guts of the two macros are the same. The only difference is that the second version is triggered by an event within Excel—the changing of which cell is currently selected. This means that every time you move from one cell to another, the value in B4 is checked and column H is either hidden or unhidden.

If it is possible that the contents of cell B4 could be empty, then it is possible that Excel will interpret that emptiness as a zero value. In that case, you can modify the macro just a bit so that it checks for an empty cell.

Sub HideColumn2()
    Dim rCell As Range
    Set rCell = Range("B4")

    Columns("H").EntireColumn.Hidden = False
    If (Not IsEmpty(rCell))
      And (IsNumeric(rCell)
      And (rCell.Value = 0) Then
        Columns("H").EntireColumn.Hidden = True
    End If
End Sub

This version of the macro actually checks three conditions: that B4 is not empty, that it contains a numeric value, and that the value is 0. If all three of these conditions are met, then column H is hidden.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

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


Understanding Underlines

Excel provides a variety of underlining styles you can use when you need to underline information within a cell. Here's ...

Discover More

Finding Where Templates Are Stored

The first step in modifying templates is to find out where they are stored on your system. Here's the easiest way to ...

Discover More

Capitalizing the Word "I"

The first-person, singular pronoun "I" should always be capitalized, unless you are exercising poetic license. Word may ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Double-Clicking to Widen Columns Won't Work

One 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 More

Hiding and Unhiding Columns

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 More

Unhiding Columns that are Persistently Hidden

If you were trying to format a worksheet and nothing you did could make the first two columns appear, would you be ...

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 three minus 0?

2019-10-29 02:21:15

pradeepkumar M


Emp Code Emp Name Basic Da HRA
001 John 10,000.00 0.00 5000
002 dominic 20,000.00 0.00 7000
003 Pradeep 30,000.00 0.00 3500
004 Dom 15,000.00 0.00 4500
005 Randy 30,000.00 0.00 500

i want to hide DA column which contains 0.00 for all employees.. How do i do that using macros???
Please help me

2019-04-17 11:03:34

Bella Romain


I have a sheet where cell E7 has a drop down with options Yes and No.

I would like the entire row 10 to be hidden if the drop down selected is No, but left present if the drop down selected is Yes.

I've tried various options, including (the most successful- but for some reason it hides it on yes, and leaves it present on no) and the above, which doesn't do anything at all.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Sub HURows()
BeginRow = 7
EndRow = 7
ChkCol = 5
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value <> "No" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub

Please can you help? This is completely new to me! Thanks :)

2018-12-08 21:16:07

Thomas G. Crowe

I would like to add to the following code, the ability to evaluate columns E thru K ... it currently only works to hide column H. Can you help?

If Range("H134").Value = 0 Then

Columns("H").EntireColumn.Hidden = True
Columns("H").EntireColumn.Hidden = False

End If

End Sub

2018-10-02 23:13:49

Glen Dsilva

Hi Allen,

I have 6 sheets.

Per sheet i have a set of column with formulaes in (E100:AD100)

I want to have an automatic macro:

if total of either column of (E100:AD100) equals zero then that entire column should be hidden.

If one of the hidden column gets a value then that column should be automatically unhidden.

let me know if you need further clarification.

Thanks in advance.

2016-02-05 11:19:39

Jonas Hammer

I want to hide an entire row IF a cell is TRUE and IF a specific cell in the soon-to-be-hidden-row is TRUE as well, otherwise it should show.

Really hope that you can help me!
Thank you!

2016-01-05 20:59:22


I'd like to create the following functionality in excel:

When I put my cursor in any cell of column “B” I would like the following steps to be taken:
• Selected Blank Cell Action to take: Group columns C-F
• Selected Cell Value is YES Action to take: Ungroup Columns C-F
• Selected Cell Value NO Action to take: Group C-F
If no cell is selected in column B Action to take Group Columns C-F

New Learner

2015-12-14 16:20:45


Hi, how can I make it possible to hide column ranges based on selections in mulitple cells?

For example, if A2 says "no", I want range B;B1 hidden.

But if B2 says "1", I want range Q:B1 hidden. And if B2 says "2", I want range AF:BI hidden, and so on.

This will not work with this Macro set-up, I assume because more than one cell is referencing the same range of columns. Would really appreciate the help!


2015-12-09 13:12:03


How would I make this applicable for multiple if statements and values? For example, if value = 1, hide o:bj columns, if value = 2 hide aa:bj columns, if value = 3 hide am:bj column, etc.


2015-07-23 10:28:00


I'm hoping that someone can help me with my question.

I stumbled upon this tip looking for a means to hide/unhide successive columns based on whether or not there are contents in a previous column, and also for a range of rows, for the purpose of creating a hazard assessment.

For example, in a 3-step assessment, if there is one hazard identified in any of the steps (written in column A), they choose it from a drop down in column B. This would then need to unhide column C, to allow another hazard to be selected, if necessary. And there is room for 10 hazards total.

Ultimately, I'm trying to cut out unnecessary blank cells at the beginning of the assessment process.

Unfortunately, I have absolutely zero background with VBE and would be relying on total copy-paste help, if possible.

2015-07-07 03:24:15


how to Add macro to hide rows in a spreadsheet where the values in a column is 0

2015-05-04 11:19:41

Gary Lundblad

Thank you for your assistance. Willy, I read your second post first, and switched the order of the row and column, and it worked. I didn't try the first solution you offered, because the other one one worked.

Thank you!


2015-05-01 05:54:01

Willy Vanhaelen

@Gary Lundblad

The proper syntax of Cells() is:


You reversed it.

2015-05-01 05:23:14

Willy Vanhaelen


Range("D:E").EntireColumn.Hidden = Range("G2") = 1

Range("D:E").EntireColumn.Hidden = (range("G2") = 1 Or range("G2") = 5)

2015-04-30 11:20:14

Gary Lundblad

I am trying to create a macro that will hide columns based on the value in row 4. True means hide the column and False means don't hide it. For some reason the code isn't working and I'm not sure why. The code I'm using is below.

Sub Collapse()
BeginColumn = 6
EndColumn = 50
ChkRow = 4

Application.ScreenUpdating = False

For ColumnCnt = BeginColumn To EndColumn
If Cells(ColumnCnt, ChkRow).Value = True Then
Cells(ColumnCnt, ChkRow).EntireColumn.Hidden = True
Cells(ColumnCnt, ChkRow).EntireColumn.Hidden = False
End If
Next ColumnCnt
Application.ScreenUpdating = True

End Sub

Thank you!


2015-04-29 05:59:30


@Willy Vanhaelen
your tip form 21.03.2015 has worked for me, however, if I wanted to hide the column based on more than one criteria how would I add this in. Using your previous scenario I would want to hide the column if the value of G2 is 1 or 5. I'm not sure how to show the 'or 5' bit. Thanks

2015-04-17 11:48:03


Is it safe to assume that this same code will work for rows, substituting "row" or "rows" where appropriate?

2015-03-29 08:44:37

Willy Vanhaelen

@David Empsall
If the macro in my comment of 21 March doesn't work, you probably didn't apply my warning in the final note.

To hide non contiguous columns can be achieved by replacing: Range("D:E")
by for example: Union(Columns("B"), Columns("D"), Columns("F"))

Being clear on expressing what you want is often the key to the solution.
What exactly do you mean by 'based on the value of a cell being between 1 and 10' ?
- Do you enter the numbers 1 to 10? What should happen for each number?
- Is it a formula that yields 1 to 10? In this case the technique of this tip is unreliable and should not be used .

2015-03-28 08:18:52

David Empsall

@Willy Vanhaelen
I'm a novice, but I just cannot get the small trigger routine to work at all.
The routine in the main body above works but, as you rightly say, the worksheet is always refreshing itself whether or not the key cell is changed.

I would like to hide a lot of columns (not contiguous ones) based on the valye of a cell being between 1 and 10.

2015-03-23 13:54:05



2015-03-21 09:23:19

Willy Vanhaelen

Although it's basically a nice tip, it appears to be badly worked out referring to the 28-02-2015 comments of 'Rod Grealish' and 'Helyosman'. But there is even more.

It's a bad choice to use the Worksheet_SelectionChange event in the second macro of this tip. The position of the cellpointer is not relevant but the value of cell B4 is. This can be monitored by the Worksheet_Change event. Then you can check if B4 has been changed and only then hide or unhide column H.

@Tony M:

The following simplified macro will solve your problem:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$2" Then
Range("D:E").EntireColumn.Hidden = Range("G2") = 1
End If
End Sub

Note that this macro should not be placed in a module but in the sheet's code page. Right click the sheet tab and select 'View Code'. That's the place to be :-).

2015-03-20 16:19:52

Tony M

My expectation was that when a user chooses a prompt on a cell on the worksheet, that the columns would hide/unhide based on the one cell. The real time macro does not appear to work, or I'm missing something. I copied it and made my adjustments. Please advise.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("G2").Value = 1 Then
Columns("D").EntireColumn.Hidden = True
Columns("E").EntireColumn.Hidden = True

Columns("D").EntireColumn.Hidden = False
Columns("E").EntireColumn.Hidden = False

End If
End Sub

2015-02-28 09:56:54


Hello and thanks,

what's more for the macro called "Sub HideColumn2()" you missed a parenthesis at the line 6.

And (IsNumeric(rCell))

Have a great day.

2015-02-28 06:31:57

Rod Grealish

In the sub HideColumn2() line continuations (a space followed by the underscore character) are needed at the end of lines 1 and 2 of the If statement as follows

If (Not IsEmpty(rCell)) _
And (IsNumeric(rCell) _
And (rCell.Value = 0) Then
Columns("H").EntireColumn.Hidden = True
End If

The If statement and the line before it can be replaced by a direct assignment to Columns("H").EntireColumn.Hidden viz.

Columns("H").EntireColumn.Hidden = (Not IsEmpty(rCell)) _
And (IsNumeric(rCell) _
And (rCell.Value = 0)

The same applies to HideColumn1()

Sub HideColumn1()
Columns("H").EntireColumn.Hidden = Range("B4").Value = 0
End Sub

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.