Hide Column In Excel

What Is Hiding Column In Excel?

Hiding column in Excel allows you to hide a single or a set of adjacent or nonadjacent columns in a spreadsheet. When you use the hide column in the Excel option, the column becomes invisible in the spreadsheet rather than getting deleted.

This functionality enables users to hide critical data they want to conceal from others or may not need until later. For example, consider the table in the image below. It shows units of rice and wheat for the years 2015 to 2020. Column D displays the total units (the sum of rice and wheat quantities each year).

Suppose you do not want column B data (Rice Units) temporarily. But since you use it to calculate the total units in column D, you can hide it. The result will be depicted as below:

Please Note: In the above illustration, column B gets hidden, but the formulas in cells D2:D7, which use cell references to column B cells, remain unaltered.

Also, the hide column function in Excel makes it more practical for the user to work with data in non-contiguous columns and view them alongside each other. It is an advantage when a spreadsheet has several columns, and you need to use specific columns’ data at a given time.

How To Hide Column In Excel

Here are a few methods to hide columns in Excel.

1. Shortcut

The hide column in Excel shortcut is the most straightforward way to conceal columns. Look at the below table of data.

1. Select a cell in the column (C5 in this case) you want to hide.
2. Press Ctrl + 0 keys, the keyboard shortcut.

With the option hide column in Excel applied, you will see the columns B and E on either side of the hidden columns C and D, alongside each other.

Hide column in Excel shortcut works well when you need to hide multiple columns quickly. You must select a minimum of one cell in each column you want to hide and apply the shortcut keys mentioned above.

2. Using Ribbon

You can execute the command hide column in Excel using the Excel menu or ribbon. The function is available in the Home tab.

For example, you have a table listing your monthly income, loan, and miscellaneous expenses. And you calculate your monthly savings in column E using these variables as depicted in the image below.

Column C displays your monthly loan, which is constant. Thus, you can use the function hide column in Excel from the Home tab in the ribbon to conceal it as you are aware of the amount. The procedure is as follows:

Step 1: Select any cell in column C, the column you need to hide.

Step 2: In the ribbon above the spreadsheet, go to the Home tab and click on Format.

Step 3: Choose Hide & Unhide from the drop-down menu and select the Hide Columns setting.

The output will be:

Please Note: You can also select adjacent or nonadjacent columns and apply the command hide column in Excel from the Home tab in the ribbon, as in steps 2 and 3.

3. Context Menu

When you select a cell or cell range and right-click, the list of functions appears on the screen. It is the context menu.

You can access the function hide column in Excel, from the context menu, for hiding columns when you do not want to view the specific data.

Consider the table shown in the image below. It provides customer details and their payment status for their registered broadband service.

Suppose you want a table that lists all the customer details, except their addresses, for a quick review. Then, you can use the function hide column in Excel and conceal column B temporarily from the display.

The procedure is as follows:

Step 1: Click on the column header, column B. The entire column B gets selected.

Step 2: Right-click on column B to access the context menu and select the Hide option.

Please Note: Click on the column header to select the entire column. Otherwise, you will not see the Hide function in the context menu.

The entire column B will get hidden, and the output will be as depicted below:

Please Note: The steps vary slightly while hiding adjacent and nonadjacent columns when executing the command hide column in Excel from the context menu.

• To hide adjacent columns, select them at the column headers and right-click anywhere on the selected columns to open the context menu. Then choose the Hide function.
• For hiding non-contiguous columns, select the respective column headers and right-click on one of the chosen column headers to open the context menu. Then select the Hide function.

4. Column Width As Zero

We can use the option hide column in Excel from the context menu as well. You will have to set the column width for the column you want to conceal as 0.

For example, the table below shows popular shampoo brands and their users in millions from 2015 to 2020 in the United States. Column H displays the total (the sum of users in the six years).

Suppose you want to analyze the data for 2018-2020. You may want to hide the 2015-2017 data, as it will help you focus only on the relevant data. You can apply the option hide column in Excel by setting the column width for columns B:D as 0. Here are the steps:

Step 1: Select the headers of the columns you want to hide. So, select column headers B:D.

Please Note: Click on the column header to select the entire column. Otherwise, you will not see the Column Width function in the context menu when you right-click.

Step 2: Right-click anywhere on the selected columns to open the context menu. Next, click on the Column Width option.

Please Note: If the columns are non-contiguous, select the headers for the columns you want to hide and right-click on any one of the headers to open the context menu. Otherwise, you will not see the Column Width function in the context menu.

Step 3: Enter the value 0 in the Column Width window, as depicted below. Click on OK.

As you set column width for columns B:D as 0, the three columns will get hidden, and the outcome will be:

Please Note: The command hide column in Excel affects horizontally merged cells. For example, in the above illustration, the table has cells B1:G1 merged. But after hiding the required columns, the merged cells’ settings appears to be E1:G1. So, the suggestion is to unmerge the cells and then use the hide function.

5. VBA Code

VBA allows you to execute the function auto hide column in Excel to conceal columns in a spreadsheet.

Consider you need to work with the student database (columns A:C) from the table depicted in the image below. So, it is best to hide the attendance and overall performance details (columns D:E).

You have the option auto hide column in Excel using VBA to conceal columns, and here are the steps:

Step 1: Go to File > Options > Customize Ribbon. Check the Developer box and click on OK.

You will see the Developer tab in the Excel ribbon. Click on Visual Basic.

The Visual Basic Editor will open as depicted below:

Alternatively, you can use the shortcut keys Alt + F11 to open the Visual Basic Editor.

Step 2: Click on Insert > Module.

Step 3: The Module 1 window will open where you can enter the VBA code to hide columns D and E, as depicted below.

Code: 

Sub Hide_Column()

Range(“D:E”).EntireColumn.Hidden = True

End Sub

Step 4: Click the option Run Sub.

As depicted below, the columns D and E in your spreadsheet will get hidden.

Hide_Column is the function name used to hide the required columns in the above example. The keyword Range specifies the column range, and since we need to hide the entire columns, we use the keywords EntireColumn and Hidden.

Likewise, you can modify the code for hiding a single column or nonadjacent columns.

For hiding a single column, say, A, the code will be:

or

And if you want to hide two nonadjacent columns, A and C, the VBA code will be:

Hide And Expand Column Using Group Feature

When you have a worksheet with many columns, you can arrange them in groups using the Group feature in the Data tab. Using the hide column in Excel, you can hide a group to conceal the columns in the specific group. Also, you can expand the group to unhide the relevant columns.

For example, the table below presents quarterly sales details of various products for a popular brand.

You can group columns for each quarter as follows:

Step 1: Select columns C:E and click on Data > Group. The three columns get grouped as depicted below:

However, if you select only the column cells with data, in this example, cells C2:E10, and click on the Group option, you will see a pop-up box to choose Rows or Columns. Select the option Columns and click OK to get the same result.

Step 2: Iterate the process by selecting columns G:I, K:M, and O:Q individually to form three groups, as depicted below.

Alternatively, you can select the required columns and press the Shift + Alt + Right Arrow keys. It is the shortcut for applying the Group function.

If you want to hide a column range, say C:E, click on the ‘–‘ (minus sign) above column F.

The minus sign changes to a ‘+,’ and the three columns get concealed.

Likewise, you can click on the ‘+’ sign to expand the group and unhide the specific columns.

Please Note: You can click on outline numbers 1 and 2, present in the top-left corner of the spreadsheet, to hide and expand all the groups, respectively. Excel allows a maximum of 8 levels of outlines.

You can also remove the groups in the following ways:

1. First, click on the Ungroup arrow in the Data tab and choose Clear Outline. It will ungroup all the grouped columns.
2. If you want to ungroup particular columns, select them and click on the Ungroup option in the Data. You can also use the keyboard shortcut, Shift + Alt + Left Arrow, to choose the Ungroup function.

While the methods described above can hide the columns in a spreadsheet, you also have an option to use the feature hide column in Excel with password. The steps are:

1. Select the column you want to hide.
2. Next, right-click on the selected column and choose Format Cells.
3. Select both Locked and Hidden in the Protection tab and click on OK.
4. Selected the column to hide, go to Home > Format > Hide & Unhide > Hide Columns.
5. Go to Home > Format > Protect Sheet.
6. A dialog box appears where you need to enter a password. Choose the actions that users can take on the protected sheet. Click on OK, and you need to reenter the password in the second dialog box to confirm it and click on OK again.

In this way, you can use the option hide column in Excel with password. It ensures no one unhides the columns you want to conceal as it freezes all possible ways to unhide the hidden columns.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the hide column in Excel, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Hide Column in Excel. Here we learn how to auto hide columns using shortcut & password, with examples & a downloadable template. You can learn more from the following articles – 

• Data Table in Excel
• Cell References in Excel
• LEN Excel Function