Excel Guides

Hiding Columns Based on a Cell Value in Excel

If you want to hide columns based on a cell value in Excel, there are a few different ways you can do this. One way is to use the conditional formatting feature. This will allow you to set up a rule that will hide the column if the cell value meets certain criteria. Another way is to use a macro. This will require some VBA code, but it can be done. Finally, you can also use a filter. This is probably the easiest method, but it only works if your data is in a table.

Conditional Formatting

To use conditional formatting, select the cells that you want to apply the rule to. Then, go to the Home tab and click on Conditional Formatting in the Styles group. Select New Rule from the drop-down menu. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format. In the Formula box, enter the following formula:

=CELL("col")=3

This formula will hide column 3 if the cell value is TRUE. You can change the 3 to any column number that you want. If you want to hide multiple columns, you can use OR in the formula like this:

=OR(CELL("col")=3,CELL("col")=4)

This will hide columns 3 and 4 if the cell value is TRUE.

Macro

If you want to hide columns based on a cell value using a macro, you will need to use some VBA code. The first thing you need to do is add a reference to the Microsoft Excel Object Library. To do this, go to Tools > References and select Microsoft Excel Object Library from the list. Then, insert a new module and paste in the following code:

Sub HideColumns()
Dim rng As Range
Set rng = Range("A1:A10")
For Each cell In rng.
If cell.Value = "TRUE" Then
cell.EntireColumn.Hidden = True
End If
Next cell
End Sub

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.