Hide columns in Excel (full guide with 8 methods)
As an Excel user, you may need to hide columns for various reasons. For example, you want to temporarily hide some columns to simplify the worksheet and make it easier to read and work with, hide columns with sensitive data, or hide some unwanted columns when printing a worksheet.
This tutorial provides 8 step-by-step methods to help you easily hide columns in Excel. Furthermore, if you need to hide rows or unhide columns in Excel, you can also get the relevant methods in this tutorial as well.
Video
Hide columns in Excel
This section provides common and useful methods to help you hide columns in Excel.
Hide columns with the Hide option in the context menu
The most common method to hide columns in Excel is using the Hide option in the context menu. Here I will show you step-by-step how to use this option to hide columns.
Step 1: Select the columns you want to hide
- To hide a column: Click the column letter to select it.
- To hide multiple adjacent columns: Click the column letter of the first column you need to hide, then hold and drag the mouse to the right to select the other columns
(Or you can hold down the Shift key, click on the column letter of the first column and the last column you want to hide). - To hide multiple non-adjacent columns: Click the column letter to select a column, hold down the Ctrl key, then select the other columns you need one by one.
In this case, I click the column letter C to select the “Date of birth” column first, hold and drag the mouse to the column letter D to select the adjacent “Age” column, then hold down the Ctrl key, click the column letter F to select the “Title” column.
Step 2: Apply the Hide option in the context menu
Right click on any column letter of the selected columns, and then select Hide from the right-clicking menu.
Result
The double line between two columns is an indicator that you've hidden a column.
Hide columns with a single click using Kutools
Here the Column list pane of Kutools for Excel is highly recommended for you. With this feature, you can easily hide and unhide columns with a single click without having to select them beforehand.
Usage:
- Select Kutools > Navigation to open the Navigation pane of Kutools.
- In the Navigation pane, click this icon to open the Column list, then you can hide a column by clicking the eye icon to the right of the column header (click the eye icon again to unhide the column).
Hide columns with shortcut
This section demonstrates how to use the shortcut “Ctrl + 0” to hide selected columns in Excel.
Step 1: Select the columns you want to hide
- To hide a column: Click the column letter to select it.
- To hide multiple adjacent columns: Click the column letter of the first column you need to hide, then hold and drag the mouse to the right to select the other columns
(Or you can hold down the Shift key, click on the column letter of the first column and the last column you want to hide). - To hide multiple non-adjacent columns: Click the column letter to select a column, hold down the Ctrl key, then select the other columns you need one by one.
Step 2: Press Ctrl + 0 together
In this case, I select three columns: “Date of birth”, “Age” and “Title”, then press the Ctrl + 0 keys together to hide them all.
Result
The double line between two columns is an indicator that you've hidden a column.
Hide columns with the Format option on the ribbon
If you do not like using the right-clicking menu, you can apply the Hide Columns feature in the Format option on the ribbon. Please do as follows.
Step 1: Select any cell within the columns you want to hide
Just select the columns or any cell within the columns you want to hide. Here I hold down the Ctrl key to select one cell in each of these three columns “Date of birth”, “Age” and “Title”.
Step 2: Apply the Hide Columns option on the ribbon
Go to the Home tab, click Format > Hide & Unhide > Hide Columns in the Cells group.
Result
The double line between two columns is an indicator that you've hidden a column.
Hide columns with plus sign (Group feature)
If you frequently need to hide and unhide specific columns, for example, the columns "Date of birth," "Age," and "Title" need to be hidden most of the time, but sometimes you need to unhide them temporarily to check the data. In this case, you can group the columns and use the plus and minus signs to toggle their visibility.
Step 1: Select the "Date of birth" and "Age" columns
Here I click the column number C to select the “Date of birth” column, and then hold and drag the mouse to the column letter D to select the adjacent “Age” column together.
Step 2: Apply the Group feature
Go to the Data tab, click Group > Group in the Outline group.
Then you can see an outline symbol displayed above the selected columns.
Step 3: Group the “Title” column separately
Select the “Title” column, repeat the above step 1 and 2 to group this column separately.
Result
You can click the minus (-) sign at the top of the group to hide all the columns within the group. See the below demo.
Hide columns with VBA code
Suppose you have a large table with many columns in your worksheet and want to hide multiple adjacent and non-adjacent columns at the same time. You can run VBA code to automate the process of hiding the columns instead of hiding them manually.
The below VBA code helps to hide the columns C:F, L:N and Z in the worksheet at the same time. Please do as follows step-by-step.
Step 1: Open the Microsoft Visual Basic for Applications window
In the worksheet you want to hide certain columns, press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
Step 2: Open the Module code window and enter the code
In the Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the following code in the Module (code) window.
VBA code: Hide certain columns in a worksheet
Sub HideColumns()
'Updated by Extendoffice 20230511
Dim ColumnsToHide As Range
Set ColumnsToHide = Range("C:F,L:N,Z:Z") 'Replace with the range of columns you want to hide
ColumnsToHide.EntireColumn.Hidden = True
End Sub
Step 3: Press the F5 key to run the code
Then all specified columns C:F, L:N and Z are hidden immediately.
Hide unused columns in Excel
Sometimes you may need to hide all unused columns and make only the working area visible in your worksheet. Hiding unused columns in a worksheet can help make the data in the worksheet easier to read. It can also help to reduce visual clutter and make it easier to focus on the relevant data. This section will show you two methods to accomplish this task.
Hide unused columns with shortcut
This section provides two shortcuts to help you select the unused columns, and then hide the selected columns. Please do as follows.
Step 1: Select all unused columns
Select the column to the right of the last column with data.
Here I select the column H by clicking the column number H. And then press Ctrl + Shift + Right Arrow to select all unused columns to the right of the used range.
Step 2: Hide all unused columns
After selecting all unused columns, press the Ctrl + 0 keys to hide them all at once.
Result
Hide unused columns with a single click using Kutools
The previous method requires you to remember shortcut keys. To make it easier, we highly recommend using the Set Scroll Area feature of Kutools for Excel. This feature allows you to hide all unused columns with just one click. Follow the steps below to do it.
Usage:
- Select the columns with data (here I select the columns from A to G).
- Select Kutools > Show & Hide > Set Scroll Area. Then you can see all unused columns are hidden immediately.
Hide rows in Excel
This section briefly demonstrates how to hide rows in a worksheet using the Hide option in the right-clicking menu.
- Select the rows you want to hide. Here I click the row number 5 to select the fifth column, hold down the Ctrl key, then click the row number 8 to select the eighth column.
- Right click on any row number of the selected rows and select Hide from the right-clicking menu.
Note: You also can press the Ctrl + 9 keys to hide the selected rows.
Unhide columns in Excel
To unhide columns, the following method can help.
Unhide all hidden columns
- Click the Select All button (the small triangle in the upper left corner of the worksheet) to select the entire worksheet.
- Right click on any column letter and select “Unhide” from the right-clicking menu.
Unhide certain hidden columns
- Select the columns adjacent to the hidden columns. For example, to unhide column C and D, you would select column B and E.
- Right click the selection and select Unhide from the right-clicking menu.
Related Articles
Automatically hide columns based on date in Excel
One common issue is when you have a worksheet that includes data for multiple time periods, and you only need to focus on a specific range of dates at a given time. Manually hiding and unhiding columns to view the desired date range can be time-consuming and error-prone. In this article, we'll explore how to hide columns based on date in Excel.
Hide columns in real-time based on user input in Excel
Have you ever tried to hide columns automatically based on some specific input values? For example, when entering the text “AA” in a cell, the column A is hidden; when I enter “BB”, the column B and C are hidden; when entering “CC”, the column D and E are hidden…. This article will help you achieve this task with VBA codes.
Hide columns on multiple sheets in a workbook
As an Excel user, you may have encountered situations where you need to hide certain columns across multiple sheets in your workbook. In this article, we will show you how to automate the process of hiding columns on multiple sheets using VBA code.
Hide or unhide columns based on drop down list selection in Excel
Hiding or unhiding columns based on a drop-down list selection can be a useful technique to make your Excel worksheets more dynamic and user-friendly. By allowing users to select from predefined options, you can control which columns are visible and which ones are hidden, depending on their needs and preferences. This article provides a VBA code to help you achieve this task.
Use button to show hide columns in Excel
While Excel provides various built-in options to hide and unhide columns, using a button to automate this task can save you time and effort. In this article, we will guide you through the process of creating a button in Excel that can show or hide specific columns with just one click.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Table of contents
- Video
- Hide columns in Excel
- With the Hide option in context menu
- With a single click using Kutools
- With shortcut
- With the Format option on the ribbon
- With plus sign (Group feature)
- With VBA code
- Hide unused columns in Excel
- With shortcut
- With a single click using Kutools
- Hide rows in Excel
- Unhide columns in Excel
- Unhide all columns
- Unhide certain columns
- Related Articles
- The Best Office Productivity Tools
- Comments