Catalog

How to Hide Columns in Excel (Step by Step)

September 6, 2023 2.1K views

In our daily tasks, the need to hide columns in Excel arises for various reasons, such as protecting sensitive data or simplifying large spreadsheets. This guide aims to address this common need, offering clear instructions and insights into different methods. Like a patient teacher, we'll walk you through the process, highlighting the convenience and power of WPS Office for this task

Part 1: How to Hide a Single Column in Excel

In this method, you will learn how to hide a single column in Excel using the built-in options. Whether you need to temporarily conceal a column or organize your data more effectively, this straightforward technique will guide you through the process.

Step 1: Select the column you want to hide.

You can select a column by clicking on the column header. The column header is the letter at the top of the column.

Step 2: Use the shortcut key or the ribbon to hide the column.

There are two ways to hide a column in Excel. You can use the shortcut key Ctrl+0, or you can use the ribbon.

Hide a Single Column in Excel


To use the shortcut key, simply press Ctrl+0 while the column you want to hide is selected.

To use the ribbon, follow these steps:

1. Click on the Home tab.

2. In the Cells group, click on the Format button.

3. In the Format Cells dialog box, click on the Hide & Unhide tab.

Format Cells dialog box


4. In the Hide columns section, select the Hide columns checkbox.

5. Click OK.

The column you selected will now be hidden.

Step 3: Verify that the column is hidden.

To verify that the column is hidden, scroll to the left or right of the column. The column header will no longer be visible, and the cells in the column will be hidden.

Part 2: Hide Columns Within a Continuous Range

Discover how to hide columns that are part of a continuous range. This method is particularly useful when you want to mask specific sections of your data while keeping a seamless appearance for your spreadsheet.

Step 1. Select the columns you want to hide.

Step 2. Use the shortcut key Ctrl+Shift+0 or the ribbon to hide the columns.

shortcut key Ctrl+Shift+0


Step 3. The columns will now be hidden.

Here are some additional tips:

  • You can also hide multiple non-contiguous columns by holding down the Ctrl key while clicking on the column headers you want to hide.

  • Once you have hidden a column, you can still access the data in the column by selecting the cells in the column and pressing Ctrl+` (grave accent).

  • To unhide a column, simply select the column header and press Ctrl+0.

logo

pressing Ctrl+`

Part 3: How to Hide Multiple Column Selections in Excel

Sometimes, you may need to hide multiple non-contiguous columns in your Excel sheet. This method will teach you how to efficiently select and hide these columns, providing you with a versatile approach to managing your data visibility.

Step 1. Select the columns you want to hide. You can select multiple columns by holding down the Ctrl key while clicking on the column headers you want to hide.

Step 2. Use the shortcut key Ctrl+0 or the ribbon to hide the columns.

shortcut key Ctrl+0


Step 3. The columns will now be hidden.

Here are some additional tips:

  • You can also hide multiple non-contiguous columns by holding down the Ctrl key while clicking on the column headers you want to hide.

  • Once you have hidden a column, you can still access the data in the column by selecting the cells in the column and pressing Ctrl+` (grave accent).

  • To unhide a column, simply select the column header and press Ctrl+0.

logo

Bonus tip: You can also use a macro to hide multiple columns in Excel. Here is a macro that you can use:

Sub HideColumns()

'This macro hides multiple columns in Excel.

'Declare variables.

Dim i As Integer

Dim ColIndex As Integer

'Get the first column to hide.

ColIndex = InputBox("Enter the first column to hide:")

'Loop through all the columns to hide.

For i = ColIndex To ColIndex + 10

'Hide the column.

Cells(1, i).EntireColumn.Hidden = True

Next i

End Sub

To use this macro, simply copy and paste it into the Visual Basic Editor (VBE). Then, press F5 to run the macro. The macro will ask you to enter the first column to hide. Enter the column number and press Enter.

Visual Basic Editor (VBE)

Part 4: How to Hide Columns in Excel Using a Shortcut

Efficiency is key when working in Excel. Learn a quick shortcut to hide columns without navigating through menus. This time-saving method is perfect for those who frequently need to adjust column visibility.

You can use the shortcut key Ctrl+0 to hide columns in Excel. To do this, follow these steps:

Step 1. Select the columns you want to hide.

Step 2. Press Ctrl+0.

Press Ctrl+0


Step 3. The columns will now be hidden.

Here are some additional tips:

  • You can also use the shortcut key Ctrl+Shift+0 to hide multiple columns.

  • Once you have hidden a column, you can still access the data in the column by selecting the cells in the column and pressing Ctrl+` (grave accent).

  • To unhide a column, simply select the column header and press Ctrl+0.

logo

Part 5: How to Hide and Expand Columns Using the Group Feature

Explore the power of Excel's grouping feature to hide and expand columns as needed. This method offers an organized way to manage your data presentation, allowing you to collapse or reveal specific sections effortlessly.

The group feature in Excel allows you to group together columns or rows so that you can hide or expand them as a unit. To use the group feature to hide and expand columns, follow these steps:

Step 1. Select the columns you want to group.

Step 2. Click on the Data tab.

Data tab


Step 3. In the Outline group, click on the Group button.

the Outline group


Step 4. In the Group dialog box, select the Columns option.

Step 5. Click OK.

The columns you selected will now be grouped together. You can hide or expand the group by clicking on the minus (-) sign or plus (+) sign next to the group header.

the minus (-) sign or plus (+)


To ungroup the columns, follow these steps:

Step 1. Click on the group header.

Step 2. Click on the Data tab.

Step 3. In the Outline group, click on the Ungroup button.

the Ungroup button


The columns will now be ungrouped.

Here are some additional tips:

  • You can also use the shortcut keys Shift+Alt+Right Arrow and Shift+Alt+Left Arrow to expand and collapse the selected group.

  • You can also use the shortcut keys Ctrl+Shift+H and Ctrl+Shift+U to hide and unhide all grouped columns.

logo

Part 6: Comparison of the Five Methods

In this section, we'll provide a comprehensive comparison of the five methods discussed above. By understanding the strengths and applications of each technique, you'll be better equipped to choose the approach that suits your specific Excel needs.

Here are the 5 methods for hiding columns in Excel, with my personal experience of using them:

  • Shortcut key: Ctrl+0. Quick and easy, can hide any number of columns.

  • Ribbon: Format Cells dialog box. More versatile, but not as quick.

  • Group feature: Group columns and hide group. Useful for related columns, can be expanded to view hidden columns.

  • Macro: Create a macro to hide columns. Flexible, but complicated if you don't know macros.

  • Personal experience: I use shortcut key for most tasks, group feature for related columns, and macro for large number of columns or specific hiding.

I hope this is helpful!

Part 7: Why Use WPS Office?

WPS Office logo


While the methods discussed in this guide are relevant to both Microsoft Excel and WPS Office Spreadsheet, we strongly advocate for the adoption of WPS Office. Here's why you should consider downloading and using WPS Office for your Excel tasks:

  • Price: WPS Office is free to use, while Microsoft Office is not.

  • Compatibility: WPS Office is compatible with Microsoft Office file formats, so you can easily open and edit files created in Microsoft Office.

  • Features: WPS Office has a wide range of features that are comparable to Microsoft Office, including a powerful spreadsheet editor, a word processor, a presentation software, and a PDF reader.

  • User-friendly interface: WPS Office has a user-friendly interface that is easy to learn and use.

If you are looking for a free, powerful, and user-friendly office suite, I would recommend giving WPS Office a try.

Trustpilotstars4.8
WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
avator
Algirdas Jasaitis
logo

Part 8: How to hide columns in WPS Office?

· Hide a single column

1. Right-click on the target column for operation.

2. Choose Hide from the pop-up list.

· Hide columns within a continuous range range

1. Right-click to select a continuous column range.

2. Choose Hide from the pop-up list.

· Hide multiple column selections

1. Long-press Ctrl on the keyboard, while using the mouse to left-click the columns for operation.

2. Right-click on the last column(s) selected, and choose Hide from the list.

FAQs About Excel Hyperlink to Another Sheet

• How to Hide Columns in Excel on Mac?

Step 1. Select Columns: Highlight the columns you want to hide.

Step 2. Right-Click: Right-click on the selected columns.

Step 3. Choose "Hide": Click "Hide" from the menu.

That's it! The selected columns are now hidden from view. To unhide them, select adjacent columns, right-click, and choose "Unhide."

• How to Hide Columns in Excel with a Password?

Step 1. Select Columns: Highlight the columns you want to hide.

Step 2. Right-Click: Right-click on the selected columns.

Step 3. Choose "Format Cells": Click "Format Cells" from the menu.

Step 4. Protection Tab: Go to the "Protection" tab in the Format Cells dialog box.

Step 5. Check "Hidden": Check the "Hidden" option to hide the selected columns.

Step 6. Set Password (Optional): To protect the hidden columns with a password, go to the "Review" tab, click "Protect Sheet," and set a password.

Step 7. Confirm and Save: Confirm the password (if set) and save your changes.

The selected columns are now hidden and protected. To view the hidden columns, you'll need to unhide them by reversing these steps.

• How Do I Hide and Unhide Columns in Excel?

To Hide Columns:

Step 1. Select Columns: Click and drag to highlight the columns you want to hide.

Step 2. Right-Click: Right-click on the selected columns.

Step 3. Choose "Hide": Click "Hide" from the context menu.

To Unhide Columns:

Step 1. Select Adjacent Columns: Click and drag to highlight the columns adjacent to the hidden ones.

Step 2. Right-Click: Right-click on the selected columns.

Step 3. Choose "Unhide": Click "Unhide" from the context menu.

Hiding and unhiding columns in Excel allows you to focus on specific data or reveal hidden information when needed.

Summary

This comprehensive guide has provided a detailed exploration of effective column hiding techniques within Excel, presenting step-by-step instructions and valuable insights. Notably, the guide underscores the seamless integration of these methods with WPS Office, an adept and budget-friendly solution that enhances the Excel experience. By adopting WPS Office, users gain access to a proficient platform that complements and amplifies the strategies elucidated in this guide, culminating in heightened efficiency and productivity within spreadsheet tasks.



15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.