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

Hide Column in Excel Intro Example

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:

Hide Column in Excel Intro Example.1

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.

Key Takeaways
  • The feature hide column in Excel conceals a column or multiple columns in a spreadsheet, making them invisible from the view.
  • You can hide columns using:
    • Shortcut keys – Ctrl + 0
    • Ribbon – Home > Format > Hide & Unhide > Hide Columns
    • Hide option from the context menu
    • Setting column width to 0
    • VBA code
  • You can group columns using the Group feature in the Data tab and use them to hide and show the specific columns.
  • One can hide columns and password protect them, thus ensuring no one can unhide them without your permission.

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.

  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.

Excel Shortcut
 Shortcut Result

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.

Using Ribbon

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.

Hide Column in Excel Using Ribbon.1

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

Hide Column in Excel Using Ribbon.2

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

Using Ribbon.3

The output will be:

Hide Column in Excel Using Ribbon.4

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.

Using Context Menu

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.

Using Context Menu.1

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

Excel Using Context Menu.2

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:

Excel Using Context Menu.3

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

It is also a method to use the option hide column in Excel from the context menu. 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:

Excel Using Column Width as Zero

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

Hide Column in Excel Using Column Width as Zero.1

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.

Hide Column in Excel Using Column Width as Zero.2

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.

Excel Using Column Width as Zero.3

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

Excel Using Column Width as Zero.4

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

Using VBA Code

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

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

Developer tab

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

Developer tab (Visual Basic)

The Visual Basic Editor will open as depicted below:

visual Basic Screen

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

Step 2: Click on Insert > Module.

Hide Column in Excel Using VBA Code.2

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

Hide Column in Excel Using VBA Code.3

Step 4: Click the option Run Sub.

Excel Using VBA Code.4

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

Excel Using VBA Code.5

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:

Hide Column in Excel Using VBA Code.6

or

Hide Column in Excel Using VBA Code.7

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

Excel Using VBA Code.8

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.

Hide Column in Excel Using Group Feature

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.

Hide Column in Excel Using Group Feature.2

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

Excel Using Group Feature.3

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.

Excel Using Group Feature.4

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.Format Cells
  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.
Hide Column in Excel Using Group Feature.4

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)

How do you hide or unhide a column in Excel?


1. Select the columns you need to hide. Right-click and choose the Hide option from the context menu.
2. Select the columns contiguous to the hidden columns. Right-click and choose the Unhide option from the context menu.

How to hide column in Excel with password?


1. Choose the columns you want to hide.
2. Right-click anywhere in the selected columns and choose Format Cells.
3. Open the Protection tab and select both Locked and Hidden checkboxes. Click on OK.
4. The columns remain selected. So, go to Home > Format > Merge & Unmerge > Hide Columns.
5. Go to Home > Format > Protect Sheet. Enter the password and click OK.
6. You will have to confirm the password and click on OK to hide the required columns and password-protect them.

Why can’t I hide columns in Excel?

The reason you cannot hide columns could be that the sheet is protected. First, go to Home > Format > Unprotect Sheet and enter the password. Once you click on OK, options to hide columns will be enabled.

What is the shortcut to hide a column in Excel?

The shortcut to hide a column is Ctrl + 0.

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.

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 – 

Reader Interactions

Leave a Reply

Your email address will not be published.