In this Excel tutorial, we will learn how to
– Hide cells, rows, and columns using different features
– Hide an entire group of data in Excel
– Lock and hide cells to protect confidential data
Here, we will use Microsoft 365 to execute all the methods in this article. However, all these features are available in Excel 2013, Excel 2016, Excel 2019, and Excel 2021.
We will show you how to hide cells in Excel so that we can get e easy visualization, prevent exposing confidential data, and protect our valuable information.
Download Practice Workbook
How to Hide Cells Across Columns in Excel? (3 Easy Ways)
To hide cells along the column, you need to follow the below steps. This is a primary feature in Excel and in this method, we will execute hiding columns. We will show you three ways to do so using the keyboard shortcut, Format group, and Hide command.
1. Using the Keyboard Shortcut to Hide Cells Across Columns
In this method, we will learn how to hide columns using keyboard shortcuts.
- Initially, select the entire column by clicking on the Column Bar. Once the column is selected, press CTRL + 0 (Zero) from the keyboard to hide the column.
- Now the final output will be below.
2. Using Format Group to Hide Cells Across Columns
We can apply the Hide & Unhide command from the Format group to hide columns in Excel.
- To hide columns from the toolbar, select the entire column by clicking on Column Bar and click Home >> Cells >> Format >> Hide & Unhide >> Hide Columns.
- Now, follow the below image to get the output.
3. Hiding Columns Using Hide Command
There is another way to hide the columns. Select the column and right-click on the mouse.
- Consequently, select Hide from the option bar and complete this process.
- After applying this method, here is the final output.
How to Hide Cells Along Rows in Excel? (3 Easy Ways)
To hide cells in Excel, select one or more rows that are nonadjacent, then press CTRL while selecting the rows. We will show you three ways to do so using the keyboard shortcut, Format group, and Hide command. In this part, we will apply the keyboard shortcut, Format group, and Hide command to hide cells along rows in Excel.
1. Hiding Cells Applying the Keyboard Shortcut
You can similarly hide one or more rows at a time using the keyboard shortcut.
- Press CTRL+9 to hide the rows. Here, select 9 from the typing keys instead of the Numeric keypad on the keyboard.
- Then you get the final output as below.
2. Using Format Group to Hide Cells Across Rows
In this method, we will use the Hide Rows command from the Format group to hide the rows.
- In the beginning, select cells and click on Home >> Cells >> Format >> Hide & Unhide >> Hide Rows.
- Here is the final output.
3. Hiding Cells Using Hide Command
To hide the cells, we will use the Hide command from the Context Menu.
- Select the cells >> Right-click the mouse to open the Context Menu >> and select the Hide command to hide the cell similar to before.
- Here is the final image after completing this process.
How to Hide Cell Values in Excel?
To hide cell values in Excel, you can apply the custom option from the Dialog Box Launcher next to Number.
- Initially, select the cells to hide and go to the Home tab >> Number >> Dialog Box Launcher from the toolbox.
- Now, the Format Cells dialog box will pop up, and select Custom from the Category.
- Now, write down (;;;) in the Type option and click OK to hide cell values.
- Finally, the output shows the cell values are hidden here.
Note: If you want to unhide the values then select General, Number, or any format from the toolbar but custom.
How to Lock and Hide Cells in Excel?
To lock the hidden cells, follow the below steps as sometimes you need to lock the cells to keep the important information unchanged. There, we will hide the formula from the formula bar.
- First, select the range G5:G13 the total result range, and right-click the mouse.
- Format cell dialog box will open and select Cell Format >> Protection >> Locked and Hidden.
- This option will not work without locking the total sheet. Now lock the sheet by clicking Review >> Protect >> Protect Sheet from the Toolbar.
- Finally, the formulas are hidden as below.
How to Hide an Entire Group in Excel? (2 Easy Ways)
To hide an entire group, you can use the Outline group option and keyboard shortcut to hide the entire group using the below process.
1. Using Outline Group to Create a Group
In this method, we will learn how to create a group from the toolbar and hide the group.
- To create a group, select the cells and click on Data >> Outline >> Group from the toolbar to create a group.
- Click on the (-) sign to hide the group.
- The final output will be similar to the one below.
2. Using the Keyboard Shortcut to Create a Group
There is another way to create groups, and that is the keyboard shortcut. Apparently, if you select ALT + SHIFT +RIGHT after selecting the cells, Excel will create a group.
- Now, here is the final output below.
Things to Remember
- You cannot hide a cell if the column or the row is merged. So, make sure to unmerge that row or column before applying these hide cell options.
- Be careful to hide data that are referred to another cell and if you hide that that will still remain in the Excel as well.
- If you print any sheet that has hidden cells then the hidden data will be shown in the printed hard copy.
Frequently Asked Questions
Ans: Yes. Hidden cells can also be used in formulas. Initially apply the formula in the cell, then hide the cell values. After that, the formula will work but the values will be hidden.
Q2: How do I unhide cells in Excel?
Ans: Select cells and click Home >> Cells >> Format >> Hide and Unhide >> Unhide Rows/Columns.
Ans: You can apply the standard procedure of printing sheets. If you print the sheet with hidden data. You get the printed sheet with the confidential data visible.
In this article, we learned how to hide columns and rows, lock cells, and then hide formulas from the formula bar. We have used Keyboard shortcuts, and Excel features, and created groups to execute all these processes. We covered everything regarding this topic. Hopefully, From now on, you can simplify your data view, organize the worksheet efficiently, and hide sensitive information as well.
Please let us know in the comment section if there are any queries or suggestions, or you can also visit Exceldemy to explore more.