When we are handling a large spreadsheet in Excel, we need to hide and unhide columns to get relevant information easily. In this article, we will talk about how we can hide and unhide columns in Excel with a plus minus sign. We will also look at some additional ways in which we can hide or unhide a column or multiple columns in Excel.
📁 Download Excel File
Download the practice workbook below.
Overview of Hiding Columns in Excel
We might need to hide a column of multiple columns in Excel for various reasons. It might be because we want to view data of two non-adjacent columns and therefore we need to hide the intermediate columns. We might need to hide helper columns that we use in order to perform some operations using functions. Also, you might want to hide certain column contents from other viewers when you’re sharing such a spreadsheet with a number of people. So, hiding columns is a task that’s done very commonly.
Learn to Hide-Unhide Columns in Excel with Plus-Minus Sign with These 2 Methods
To demonstrate things easily, we will consider a dataset where we have last quarter sales for individual months in dollars of different companies. At first we will find the total sales using the SUBTOTAL Function and then we will hide the columns of individual months and only present the total last quarter sales of individual companies. The dataset is shown below:
1. Using Auto Outline Option
Excel has an Auto Outline feature in the Groups section that helps us to outline a worksheet in a simple, straightforward way. This feature can be used to hide columns using the minus sign and reveal the hidden columns by using the plus sign. In this step, at first, we will use the SUBTOTAL Function to find the total last quarter sale. The steps to hide a column using Auto Outline feature are as follows:
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select the cell G6 and write the following formula in the formula bar to find out the total last quarter sale.
- Then we drag the Fill Handle to find out the total last quarter sale values for all companies.
- The new table will look like the following image.
- We go to the Data tab and select the Group from the Outline From there, we select the option Auto Outline.
- On top of the cell references, an outline window will show up with a minus sign. The minus sign will be throughout the cells that have been used in the SUBTOTAL Function range.
- After that, we click on the minus sign. We will see that the intermediate columns will collapse. If we press the plus sign, the collapsed columns will show up and unhide themselves. The collapsed table will look like the following image.
2. Utilizing Group Feature
The Group Feature option also hides columns but in this approach, we need to select the columns we want to hide. It lies on the same Outline tab in the Editing Ribbon. The steps for this process are as follows:
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select the cells for which the corresponding columns we want to hide. Here, we select the cells C1, D1, E1, and F1. We do this by selecting C1, pressing Shift, and selecting F1 to select the multiple cells.
- After that, we go to the Outline tab and select the Group subsection from the Group section.
- A Group window will appear. We select Columns and press OK.
- An outline window will appear below the formula bar with a minus sign over the range of selected cells.
- We click on the minus sign and we can see the collapsed table similar to the following image. The plus sign then shows up and if we click on it, the collapsed form will expand to show the initial table.
How to Hide Columns Using Format Option in Excel
The Ribbon Tab in Excel has a segment named Cells. Using the Format Cells option, we can hide certain columns in Excel apart from the methods mentioned using Plus-Minus Sign. The steps for this process are as follows:
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select the cells or any column which we want to hide. Here, we select the cells C1:F1.
- After that, we head over to the cells group present in the Ribbon tab and select Format.
- A drop-down menu will appear. We select Hide Columns from the Hide and Unhide option in the drop down menu.
- The information in columns C to F will disappear and collapse. The output will look like the following image.
How to Hide Columns Manually from Context Menu in Excel
Another alternative approach to hiding columns is to manually hide selected columns after selecting the columns and right-clicking to get a drop-down menu. This approach has the following steps:
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select the columns that we want to hide. For multiple-column selection, we use the Ctrl key or Shift key to select a continuous range of columns. Let us select the cell range C1:F1.
- Then we right click and a drop-down menu will appear. We select the Hide option.
- The columns will disappear and we will get the following image as the output.
How to Hide Columns Using VBA Code in Excel
Implementing VBA Code to hide columns is another approach that we can look at. To do this we need to follow these steps:
⬇️⬇️ STEPS ⬇️⬇️
- At first, we go to the Developer tab and select Visual Basic.
- A new tab will appear. We then go to Insert and then select Module.
- In the module window, we write the following VBA Code. Here we specify the columns C to F to be hidden by the function Sub Hide_Entire_Columns().
Sub Hide_Entire_Columns() Columns("C:F").EntireColumn.Hidden = True End Sub
- After writing the code, we press Run.
- The columns will disappear and the result will look like the image below.
📄 Important Notes
🖊️ The VBA Code hides the columns on the currently open worksheet or the sheet where we create the module.
🖊️ Before we group columns we should make sure that all the data in our sheet remains visible to avoid incorrect grouping.
🖊️ When we use the auto outline to group data, it groups the columns that we have used in the SUBTOTAL Function range.
📝 Takeaways from This Article
We have taken the following summed-up inputs from the article:
📌 We can hide-unhide columns in Excel by using Auto Outline and Group Feature options both belonging to the Data Groups section in Excel.
📌 We can use the Ribbon Tab and Cell Formatting to hide and unhide columns as well. Also, right-clicking after selecting certain columns can also let us manually hide columns.
📌 A simple VBA Code can also hide and unhide cells in Excel.
I hope that this article has provided you with insights about how we can hide-unhide columns in Excel using plus-minus sign and also how we can do the same thing by other approaches. If you want to know more about Excel, please visit our site ExcelDen and if you have any queries regarding this topic, feel free to let us know down below in the comment section.