Hiding Excel Column(s)

For example, a column containing calculations may be hidden. This helps avoid confusion amongst users of a shared worksheet.

A column is hidden when its data needs to be concealed from other Excel users, it is unused and not required for a while, its presence is making comparisons between the remaining columns difficult, and so on. The purpose of hiding an excel column is to allow viewing the relevant areas of a worksheet at a given time.

How to Hide Columns in Excel? (Top 4 Methods)

The techniques of hiding columns in Excel are listed as follows:

  • “Hide” option of the context menu“Ctrl+zero (0)” shortcutColumn width as zeroVBA code

Let us discuss these methods one by one with the help of examples.

Note: All the following examples demonstrate the process of hiding adjacent (contiguous) columns. For hiding multiple non-adjacent columns, refer to the second question under the heading “frequently asked questions.” This is given at the end of this article.

Example #1–Hide Columns Using the “Hide” Option of the Context Menu

The following table displays the IDs, names, gender, age, and department of some employees of an organization. Consider one column of the table as one column of an Excel worksheet. So, the dataset begins from column A (emp ID) and ends with column G (department).

The first name (column B) and last name (column C) have been concatenated (joined) to form the full name (column D). Since columns B and C are not required (for some time), we want to hide them using the “hide” option of the context menu.

Example #2–Hide Excel Columns Using the “Ctrl+Zero (0)” Shortcut

The following image shows a dataset similar to that of example #1. Notice that the number of columns has been reduced this time. Columns A to D are displayed, which consist of the employee ID, first name, last name, and full name respectively.

  • With the help of the mouse, click the label of column B appearing on top. This selects column B entirely. Next, press the keys “Shift+right arrow” to select the entire column C.The selection is shown in the succeeding image. Notice that in column A, the numbers have been formatted as text. This has been done to place zeros before each number in cells A2 to A9.Note 1: For “Shift+right arrow” to work, hold the “Shift” key, and at the same time, press the right arrow. When the shortcut “Shift+right arrow” is pressed after selecting a column, the selection is extended to an adjacent column on the right.When the shortcut “Shift+right arrow” is pressed after selecting a cell, the selection is extended to an adjacent cell on the right.Note 2: When the numbers of column A were formatted as text, green triangles (shown in the first image of example #2) had appeared on the upper-left corner of each cell. From the “trace error” button, we clicked “ignore error” (for each cell) to remove such green triangles. Right-click the selection and choose “hide” from the context menu. The same is shown in the following image. The final dataset, with columns B and C hidden, is shown in the following image. Notice that there are double vertical lines (shown in a red box) between the column labels A and D. These lines indicate that columns B and C have been hidden. Another indication of hidden excel columns is the change in the sequence of the column labels. After label A, labels B and C are skipped, and straightaway label D is displayed.

Another indication of hidden excel columns is the change in the sequence of the column labels. After label A, labels B and C are skipped, and straightaway label D is displayed.

Ignore the green triangles in column A of the subsequent images. These are displayed because the “ignore error” option from the “trace error” button has not been clicked, unlike in step 1 of the preceding example (example #1).

We want to hide columns B and C by using the shortcut “Ctrl+zero (0)” in Excel.

The steps to hide the stated columns using the given technique are listed as follows:

Step 1: Select columns B and C, which need to be hidden. For this, click the label of column B with the mouse and drag it across to column C. When the mouse pointer is placed on the label of column B, it changes to an arrow pointing downwards.

Note: Alternatively, select any cell of column B and press the keys “Ctrl+space” together. Column B is selected entirely. Next, press the keys “Shift+right arrow” to select column C.

Step 2: Once the columns to be hidden (columns B and C) are selected, press the keys “Ctrl+zero (0)” together.

The steps 1 and 2 are shown in the following image. Hence, columns B and C (selected in step 1) are hidden. Notice that, in the final output, the double vertical lines separate the labels of columns A and D.

Example #3–Hide Excel Columns by Setting the Column Width as Zero

Working on the dataset of example #2, we want to hide columns B and C by setting the column widthColumn Using Column WidthA user can set the width of a column in an excel worksheet between 0 and 255, where one character width equals one unit. The column width for a new excel sheet is 8.43 characters, which is equal to 64 pixels.read more as zero.

The steps to hide the mentioned columns using the given technique are listed as follows:

Step 1: Select the columns to be hidden. So, select columns B and C entirely.

Step 2: Right-click the selection and choose “column width” from the context menu. Set column width as zero and click “Ok.”

Both steps 1 and 2 are shown in the following image. The final dataset, with columns B and C hidden, is also displayed in this image. Double vertical lines appear between the labels of columns A and D.

Note: When a column’s width is entered as zero, the column is hidden from the worksheet.

Example #4–Hide Columns Using VBA Code

Working on the dataset of example #1, we want to hide columns B and C with the help of a VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more. Consider the dataset to be in “sheet1” of Excel.

The steps to hide columns using a VBA code are listed as follows:

Step 1: From the Developer tab, click “visual basic.” This is shown in the following image.

Note: If the Developer tabDeveloper TabEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more is not displayed on the ribbon, it must be enabled from the File tab. For the detailed steps, click the given hyperlink.

Step 2: A new window titled “Microsoft visual basic for applications” opens. Double-click “sheet1.” Next, from the Insert tab, click “procedure.” Specify the name of the procedure and paste the following code:

Worksheets(“Sheet1”).Columns(“B:C”).Hidden = True

This is shown in the following image.

Step 3: Save the file with the .xlsm extension as it supports macros. This extension is used for a macro-enabled workbook, which has been created in Excel 2007 or the newer Excel versions.

Step 4: From the Run tab, click “run sub/user form.” The output is shown in the following image. Notice the double line (shown in a red box) between the column labels A and D. This indicates that the columns in between (i.e., columns B and C) are hidden.

Frequently Asked Questions

This has been a guide to hiding columns in excel. Here we discuss the top 4 methods to hide columns in Excel, including the “hide” option of the context menu, shortcut (Ctrl+0), column width (as zero), and VBA code. You can learn more about Excel from the following articles –

To hide a column means removing one or more excel columns from the display. Such hidden columns do exist in the worksheet even though they become invisible. Columns are usually hidden when they are neither being used nor meant to be deleted.The steps to hide a column in Excel are listed as follows:a. Select the column to be hidden.b. Right-click the selection and choose “hide” from the context menu.The column selected in step “a” is hidden.Note: For more techniques of hiding a column, refer to the examples of this article.

The steps to hide multiple columns in Excel are listed as follows:a. Select all the columns that need to be hidden.• For selecting multiple contiguous columns, drag across the column labels with the mouse. Alternatively, select the first column and press the keys “Shift+right arrow” to select columns on the right.• For selecting multiple non-contiguous columns, click the first column label. Thereafter, hold the “Ctrl” key and click the other column labels.b. From the Home tab, click the “format” drop-down in the “cells” group.c. Under “visibility,” select “hide & unhide.” Next, choose “hide columns.”The columns selected in step “a” are hidden.

The steps to hide and lock columns in Excel are listed as follows:a. Select the entire worksheet by pressing either the keys “Ctrl+A” or the “select all” button. The “select all” button is located at the top-left corner of the Excel worksheet.b. Right-click the selection and choose “format cells” from the context menu. The “format cells” dialog box opens. Uncheck the “locked” option in the “protection” tab. Click “Ok.”c. Select the columns to be hidden and locked. Open the “format cells” dialog box again and click the “protection” tab. Check the “locked” option and click “Ok.”d. Hide the columns selected in the preceding step (step c). For this, keep the columns to be hidden selected and click the “format” drop-down from the Home tab. Next, click “hide columns” from the “hide & unhide” option.e. From the Review tab, click “protect sheet.” The “protect sheet” dialog box opens. Keep the following checkboxes selected:• “Protect worksheet and contents of locked cell”• “Select locked cells”• “Select unlocked cells”f. Enter a password and confirm it. Click “Ok” to proceed. The “protect sheet” dialog box closes.The columns selected in step “c” are hidden. Since the worksheet is protected, these hidden columns cannot be unhidden by the usual ways to unhide a column. The advantage of locking hidden columns is that their content is concealed from the other users of the worksheet.Note: To unhide the hidden columns, unprotect the sheet in the first place. To unprotect the sheet, click “unprotect sheet” from the Review tab. Next, enter the password and click “Ok.”Thereafter, unhide the columns by selecting, right-clicking, and choosing “unhide” from the context menu. Alternatively, select the columns and choose “unhide columns” from the “hide & unhide” option of the “format” drop-down (in the Home tab).

  • VBA Hide ColumnsExcel Hide ShortcutHow to Unhide Columns in Excel?How to Freeze or Lock Multiple Columns in Excel?FLOOR Function in Excel