In this article, we’ll learn how to hide columns in Excel. We will also learn how to unhide the hidden columns with or without VBA macros.
Here we’ll use the Context menu, keyboard shortcut, Column Width command, Group feature, Format feature, Format Cells dialog box, and VBA macros to hide multiple adjacent or non-adjacent columns.
To compare data in columns that are not adjacent, you may need to view them side by side. In this case, hide columns are very crucial for users. We have also attached a practice worksheet. Click in the below download box to get the practice file.
1. Using Context Menu to Hide Multiple Columns in Excel
We can hide multiple columns in Excel by using the Hide command from the Context menu. If we press right-click only the columns heading then the Hide command is visible in the Context menu. Otherwise, the Hode columns are not visible in the Context menu. In our dataset, we will hide multiple columns D, E, and F.
- Select entire columns D, E, and F ⇉ Press right-click on the columns heading ⇉ The Context menu appears ⇉ Select the Hide command.
- As a result, you can hide the selected multiple columns like the following image.
2. Apply Keyboard Shortcut to Hide Used or Unused Columns (Adjacent or Non-Adjacent Columns)
We can hide adjacent or non-adjacent multiple columns or used or unused columns in the data table by using the keyboard shortcut.
2.1 Use Keyboard Shortcut to Hide Columns from Used Data Table
Sometimes, while working in MS Excel, we want to hide some cells, columns, or rows for the convenience of our work. We have a dataset containing information on some student's marks in several subjects. In our dataset, we’ll hide columns D and F.
- Select entire columns D and F ⇉ Press Ctrl + 0.
- As a result, you can hide columns D and E from your data table by using the keyboard shortcut like the following image.
Notes: If you want to hide single or multiple adjacent or non-adjacent rows by using the keyboard shortcut, select the rows that you want to hide and press the Ctrl + 9 keys.
2.2 Apply Keyboard Shortcut to Hide Unused Columns in Excel
Here, we’ll learn how to hide unused columns in an Excel sheet.
- Select cell H4 ⇉ Press Ctrl + Shift + Right Arrow (→) key to select entire unused columns ⇉ Press Ctrl + 0 to hide unused columns.
- Finally, you can hide the unused columns like the following image.
3. Hide Columns by Setting Column Width as Zero
Now, we’ll hide columns by using the Column Width feature in Excel. We set the column width as zero(0) to hide columns.
- Select range C8:D8 ⇉ Home tab ⇉ Cells group ⇉ Format ⇉ Column Width
- Hence, the Column Width dialog box will appear ⇉ Enter 0 in the Column Width box ⇉ OK.
- As a result, you can hide columns C and D which have been given in the below image.
4. Use the Home (Format Button) Tab Ribbon to Hide Multiple Columns in Excel
Using the Format feature from the Home tab, you can hide both adjacent and non-adjacent columns. You can hide both single or multiple (adjacent and non-adjacent multiple columns) columns in the same way. In our dataset, we’ll hide adjacent columns only.
- Select range C6:E8 ⇉ Home tab ⇉ Cells group ⇉ Format ⇉ Hide & Unhide ⇉ Hide Columns
- As a result, you can hide the multiple adjacent columns.
Notes: It is not necessary to select entire columns while using the Hide Columns feature to hide multiple columns. You can select a cell or a range of cells instead of entire columns to hide columns.
5. Using Group Feature from Data Tab to Hide and Show Columns in Excel
Using the Group feature in the Data tab to hide and show columns is one of the easiest ways in Excel. You can group columns only if they are adjacent; non-adjacent columns cannot be grouped by using this method. You can also hide even a single column in Excel by using this method. In our dataset, we will hide the student's marks in all subjects by using the Group feature.
- Select columns D to G ⇉ Data tab ⇉ Outline group ⇉ Group
- As a result, you can group the columns D to G ⇉ Click on the Minus(-) symbol to hide the grouped columns.
- Finally, we can hide the grouped columns like the following image.
Notes: You can also unhide the hidden columns by clicking the Plus(+) symbol in the above image.
6. Applying Format Cells Dialog Box to Hide Data of Cells or Columns in Excel
Sometimes we need to hide the data from the data table without hiding columns or cells in Excel. We can do that by using the Custom Format cells in Excel.
- Select range D4:G13 ⇉ Press Ctrl + 1.
- As a result, the Format cells dialog box pops up ⇉ Number tab ⇉ Click on the Custom option from the Category drop-down list ⇉ Insert ;;; in the Type box⇉ OK.
- After that, you can hide the data of the cells without hiding columns and formatting the cells in Excel
7. Execute VBA Code to Hide Columns in Excel
You can use VBA code to hide single or multiple adjacent or non-adjacent columns in Excel.
- Press the Alt+F11 keys.
- Visual Basic Editor window will appear ⇉ Go to Insert tab ⇉ Module.
- Write down the following VBA code and save it with the .xlsm extension.
- Hence, press the F5 key to run the VBA code.
Sub Hide_Multiple_Columns()
Columns("D:G").Hidden = True
End Sub- After running the VBA code, you can hide columns D to G.
How to Unhide All Hidden Columns Using VBA Macros
You can also use another VBA code to unhide all hidden columns in Excel. To do that, insert another module and write down the below VBA code in that module.
Sub Unhide_All_Hidden_Columns()
Cells.EntireColumn.Hidden = False
End Sub- After running the VBA code, you can unhide all hidden columns.
How to Unhide Multiple Columns in Excel
Here, we use the Context menu to unhide multiple adjacent or non-adjacent columns in Excel. From our dataset, we’ll unhide the hidden columns D, E, and F.
- Press Ctrl + A to select the entire worksheet ⇉ Press right-click on the columns heading ⇉ The Context menu appears ⇉ Select the Unhide command.
- After that, you can unhide the hidden columns.
Conclusion
We learned from the above discussion how to hide multiple adjacent or non-adjacent columns by using the Context menu, keyboard shortcut, Column Width command, Group feature, Format feature, Format Cells dialog box, and VBA macros. We also learned how to unhide the hidden columns with or without VBA macros. If you have any queries or suggestions about this article, leave it in the comment section.
Tags: Range