How to Hide Columns Based On Cell Values in Excel

*How to Hide Columns Based on Cell Values In Excel*
Excel is an excellent tool for organizing and analyzing data. It allows users to manage large amounts of information easily and efficiently. However, when working with a lot of data, it can be overwhelming to display everything on one sheet. Fortunately, Excel has several features that make it possible to hide certain Columns based on specific text.
*Chapters:*
0:00 How to Hide Columns Based on Cell Values In Excel
0:21 How to Hide Columns Based on Cell Values Using Conditional Formatting
1:30 How to Hide Columns Based on Cell Values Using VBA
In this advanced excel tutorial, we'll cover how to hide Columns in Excel based on the cell value using Conditional Formatting and VBA code. Let’s start with the conditional formatting.
*Step 1:* Select the range of cells that you want to Hide. In this case “Forecast” Data from Column I to Column M.
*Step 2:* Click on the Home tab and select Conditional Formatting.
*Step 3:* Click on New Rule.
*Step 4:* In the New Formatting Rule dialog box, select "Use a formula to determine which cells to format."
*Step 5:* In the "Use formula to determine which cell to format" field, enter the following formula:
=($B$3="Actual")
*Step 6:* Click on the Format button and select the Fill tab.
*Step 7:* Choose a color that matches the background color of the worksheet to hide the Columns (In this case cell data).
*Step 8:* Click OK to close the Format Cells dialog box.
*Note:* Now When you need to hide the Actual data, select that range of Actual data in Step 1 and also Change the Formula in Step 5 to =($B$3="Forecast")

*Method 2:*
This method involves using a macro to automatically hide or unhide Columns based on the selected cell value. Here's how to do it:
*Step 1:* Right Click on the worksheet name and click on View Code.
*Step 2:* Write the VBA code. In the code window, paste the following code:
*Step 3:* Close the Visual Basic Editor
*Step 4:* Save the workbook in .xlsm format
This code tells Excel to check if a cell value in B3 has been changed. If it has, the macro will check the value of the cell and hide or unhide the Columns based on the cell value. In this example, if the cell value is "Actual", Columns I to M will be hidden, and Columns D to H will be unhidden. If the cell value is "Forecast", the opposite will happen. If nothing is written in cell B3 everything will be visible.
Hiding Columns based on cell value can be useful when working with large amounts of data in Excel. This method allows you to display only the data that is relevant to your needs, making it easier to analyze and manage your information. By using VBA code, you can automate the process and make it even more efficient. There are several ways to hide Columns in Excel based on cell value. The method you choose depends on your preference and familiarity with Excel's features. Whether you choose to use VBA code or conditional formatting, hiding Columns based on cell value can be a useful tool in managing and analyzing large amounts of data.

#Hide #Columns #Excel #MsExcel #ExcelTutorial

Thanks for watching.
----------------------------------------------------------------------------------------
Support the channel with as low as $5
----------------------------------------------------------------------------------------
Please subscribe to #excel10tutorial

Here goes the most recent video of the channel:

Playlists:

Social media:

[krishnamanjunatha]

plz gv explanation on line If Not Application.Interest...
thank u

[omnipop]

Well, this conditional formatting method only hides text. It doesn't hide cells, ranges or columns.

[javiercardenas]

how do you use the macro without saving the file as .xlsm ??

[harhihpraanna]

Hello all..
I need one help.
How to clear Coloum data based on selected coloumn data. What is code



Example. If Cell A1 Data "good " then B1 data should be clear
If cell A1 data "bad" then B1 data should not clear