How to hide tabs, cells, columns, and formulas in Excel
Posted on September 17, 2008 at 5:29 am
Excel is the most common application I use in the office suite and there are many times when I need to hide thing in Excel: tabs, sheets, columns, cells, etc, etc for whatever reason. In this article, I’ll go through a comprehensive list of items you can hide in Excel.
If you’re interested, you may also want to check out my previous article on how to hide text in Word. Now let’s talk about all the stuff we can hide in Excel!
How to hide tabs/sheets in Excel
To hide a sheet or tab in Excel, right-click on the tab and choose Hide. Pretty easy eh?
Once hidden, you can right-click on a visible sheet and select Unhide. All hidden sheets will be shown in a list and you can select the one you want to unhide.
How to hide cells in Excel
Excel does not have the ability to hide cells in the traditional sense that they simply disappear until you unhide them, like with tabs I showed above. It can only blank out the cells so that it appears that nothing is in those cells, but it can’t truly “hide” them because of the fact that if you hide cells, what would you replace them with?
If you greyed it out, you could easily tell there is something hidden, defeating the purpose of “hiding” them. If you try to shift cells up and down, then when you unhide the hidden cells, where should everything shift? You can hide entire rows and columns in Excel so they are not visible, but can only blank out individual cells. Here’s how.
Right-click on the group of cells and choose Format Cells.
On the Number tab, choose Custom at the bottom and in the Type box, enter three semicolons (;;;), without the parenthesis.
Click OK and now the data in those cells is hidden. To get it back, follow the above procedure, but choose the original format of the cells rather than Custom. Note that if you type anything into those cells, it will automatically be hidden and it’ll look like you didn’t type anything into it. However, the original value in that cell will be replaced with what you typed.
How to hide rows and columns in Excel
If you want to hide an entire row or column, select the row(s) and column(s), right-click and select Hide.
To unhide a row or column, you need to select the row/column before and the row/column after the hidden row/column. For example, if Column B is hidden, to unhide it you would need to select column A and column C and then right-click and choose Unhide.
How to hide formulas in Excel
Hiding formulas is slight more complicated than hiding rows, columns, and tabs. If you want to hide a formula, you have to do TWO things: set the cells to Hidden and then protect the sheet.
So, for example, I want to hide my sheet with the following secret formulas on them:
First, I will select three cells in column D, right-click and choose Format Cells. Now click on the Protection tab and check the box that says Hidden.
Note that this will not hide anything. You now have to click on the Review tab and then click on Protect Sheet.
You can enter in a password if you want to prevent people from un-hiding the formulas. Now you’ll notice that if you try to view the formulas (by pressing CTRL + ~), they will not be visible, however the results of that formula will remain visible.
I’m not sure why you would want to do this, but you can also click on the View tab and click on the Hide button under Split. This will hide the entire workbook in Excel!
So now you’ve learnt how to hide workbooks, sheets, rows, columns, cells, and formulas! That’s it. Enjoy!
» Filed Under MS Office Tips
Related Posts
- Link Cells Between Tabs and Workbooks and In Excel 2010
- Excel Tips & Tricks – How to unprotect only a portion of an Excel spreadsheet
- How to use Data Validation in Microsoft Excel to create dropdown lists
- How to securely password protect an Excel file
- Organize Worksheets in an Excel Spreadsheet
Pretty useful.
Thanks….
I guess one could create a whole blog on Excel tips …it’s such a powerful and useful tool !
Nice & easy to understand for Excel beginners!
You mentioned how to hide just about everything in Excel, but as a developer I wanted to know how to hide the source code for a macro. Is there any way to password protect or hide the VBA code for macros?
Kristen said: Is there any way to password protect or hide the VBA code for macros?
You can password protect your VBA project by:
1) Right click in the project explorer window within VB(the one on left)
2) Select “VBAProject Properties”
3) Goto to “Protection” tab
4) Lock project for viewing, enter password.
This will stop people viewing/changing the code in a project without password.
Valuable information! I’m no Excel guru, but your Excel tutorials make me feel empowered!
I’m with Ganesh, very valuable info on Excel. I’ve been looking for this answer for a couple of days. The directions were complete with screen shots, perfect for your visual learners out here on the cloud.
Thanks for empowering!!
Muchas gracias, ya llevaba 2 días buscando el como ocultarlo y solo aparecen comentarios sobre bloqueo.
English:
Thanks very much. I spend 2 days trying to figure out how to hide comments in an Excel sheet.
HELP! I just hide some cells on my Excel spreadsheet and now I can’t unhide them!! They won’t come back. What to do!?!?
So che c’è un modo per non mostrare, tramite protezione, i fogli nascosti. Non ricordo la procedura. Mi potete aiutare? Grazie mille.
Dear Sir….Thank you very much for telling the formula of hidden cell column and formula .But please be clear after hidden the formula we are unable to work in this sheet.i just only wanna hide my formula nothing else.