Hiding Options in Excel

Hey Guys, how are you doing? Every once in a while in a trainng session I get a request on “How to hide xyz in Excel ?”, so I though I will dedicate this post on hiding (and un-hiding) the top 9 things that people often ask me about!

So ready for our hide and seek? Alright I count till 3

 

1. How to Hide Sheet Tabs ?

 

 

1. Go to Excel Options. Use the shortcut ALT F I (for Excel 2007) and ALT F T (for excel 2010 and above)
2. Click on Advanced in the Side Panel
3. Roll over to ‘Display Options for this workbook’ and there you can check or uncheck Show sheet tabs

 

 

 

 

 

 

Also read – How to unhide mutiple Sheets at once

 

2. How to Hide the Ribbon ?

 

 

 

 

Hiding the Ribbon is damn quick. Just use CTRL F1 shortcut to hide and  then again to unhide the Ribbon. Hiding the Ribbon gives you more screen space to work. Distraction free ‘Excel’ling synonymous to distraction free writing 😆

 

3. How to Hide Gridlines ?

Trust me, I do it all the time. It gives me a really nice feeling of a neat and tidy worksheet.. Wow! Here is how you too can get the wow factor!

 

 

 

 

 

 

 

 

1. In the View Tab – Click on Gridlines to Hide (or Unhide) them
2. You can also use the shortcut ALT W VG. I also use this shortcut all the time, infact I also wrote a post about unusual shortcuts in Excel that I use

 

4. How to Hide Scroll Bars ?

 

 

 

 

 

 

You can even hide the scroll bars when you are really going out of free visible space. All you gotta do is

• Go to Excel Options. ALT F I (Excel 2007) and ALT F T (Excel 2010). I am such a shortcut’s person
• Again go in Advanced
• Roll over to ‘Display Options for this workbook’ and uncheck on whichever scroll bar you want to  make disappear. I am sure you know how to get it back.. Just do the same 😉

 

5. How to Hide the Formula Bar ?

 

 

 

 

1. Go to View Tab and un-check the Formula Bar
2. I rarely hide it so I don’t know the shortcut for it 🙄

 

6. How to Hide Headers (Rows & Columns) ?

 

 

 

 

 

 

 

1. Again in the View Tab and un-check the Headings Option
2. I do hide the headings sometimes (while making a dashboard) but again I don’t know the shortcut for it 🙄

 

7. How to Hide page breaks ?

 

In case you access the Page Break View, these lines show up as a demarcation of the page and they at times can be quite irritating 👿 ! One such irritation got me on Mr Excel Message Board where I found the answer myself. Here is how you do it

1. Excel Options once again. Expecting you to memorize the shortcut by now 😎
2. Advanced
3. Under ‘Display Options for this Worksheet’, uncheck Show page breaks

 

 

 

 

 

 

 

8. How to Hide Rows and Columns ?

For Hiding/Unhiding Rows

1. Select the rows you want to hide
2. Use CTRL 9 to hide rows. You can do it with a mouse too but I insist on using the key board shortcut.. Please!!
3. Select the rows outside hidden rows
4. Use Ctrl SHIFT 9 to unhide rows

 

 

 

 

 

 

 

 

For Hiding/Unhiding Columns

1. Select the columns you want to hide
2. Use CTRL 0 to hide columns
3. Select the columns outside hidden columns
4. Use ALT H O U L to unhide columns. You need NOT press those keys together, just act like you are normally typing

 

 

 

 

 

 

 

9. How to Hide data ?

There could me many ways to hide the data and one of them could be simply coloring the data font as white (assuming the sheet is also white) but we are going to talk about a custom format to hide the data. Here is a detailed post on custom formatting incase you want to know more about it

 

 

 

 

 

 

1. Select the data that you want to hide
2. Go to Format Cells box. Shortcut is Ctrl 1
3. Under Custom, change the code to ;;; (3 semicolons) which means that you intentionally want the data not to be seen. Don’t worry the data is still there but just not visible 🙂

 

More on Excel Options

1. 9 Quick Screen editing Options
2. Turning off GetPivotData Option
3. Sheet Protection Options

 

Do you often hide any of the objects in excel?  Share your views in comments ..