Excel 2010: Hide/Unhide Columns, Rows, Sheets

Excel 2010 has an excellent feature to conceal the selected rows, columns, and sheets, this could be very handy especially when you want to protect specific content from any sort of detrimental usage. Launch Excel 2010 workbook in which you want to hide rows, columns or sheets. For illustration, we have included a workbook containing multiple sheets in which we will be hiding rows, columns etc.

sheets

Hiding Rows

In desired spreadsheet select the rows (for multiple non-contagious selection hold Ctrl key) you want to hide and navigate to Home tab.

select rows

From Cells group, click Format button. Now from Hide & Unhide options, click Hide Rows.

hide

Upon click it will automatically hide the selected rows. The rows 2,3,5 and 7 numbers are now hidden.

hidden

Hiding Columns

For hiding the columns in specific sheet, select the columns you want to hide.

hide columns

Repeat the same procedure for it, from Format –> Hide & Unhide options, click Hide Columns, upon click the selected columns will be hidden.


hidden c

Hide Sheets

Now select the sheet which you want to hide.

hide data bars

Click Hide Sheet from Hide & Unhide options.

hide sheet

Upon click the whole sheet will be concealed.

sheet hidden

Unhide Rows, Columns, and Sheets

For revealing the hidden sheets, rows and columns,  click Format, and from Hide & Unhide options, click the respective option available in the menu.

unhide

You can also check out previously reviewed guides on Tracking Formula Precedents & Dependents and Show complete formula instead of Evaluated Result in Excel 2010.

Advertisement
  • Bryan

    The steps to unhide colums and rows are missing. You cannot just go into the menu and select unhide. You first have to select the correct area of the sheet for this to work. These are the most important steps and they are missing!

    • dr

      Bryan – How can you selected hidden areas? They are hidden.

  • Efren

    What must be the reason why the hide and unhide sheet commands are not bolden or not active?

  • tom

    I read somewhere else to use the ‘Go to’ function and select a cell on a hidden row. From this post, I used the Format function to unhide rows. Only the row that contained the selected cell from the Go To function was unhidden.

  • paul

    hiding is easy. But it’s a royal pain to unhide.

    I select the row before the hidden ones and choose unhide from the format menu. Nothing.
    I select the row after the hidden ones and choose unhide from the format menu. Nothing.
    I select the reow before the hiddens ones AND the row after the hidden ones. I select unhide from the format menu. Nothing.

    I give up, how do I unhide something that I hid???

    I

    • Linda

      highlight the column before the hide AND after the hide. i.e. if you hid B and C, highlight A and D, right click on the top margin and click on unhide. If you hid A, highlight the whole document by clicking on the little arrow above the little column which numbers the rows, then right click and click on unhide. That would work if you don’t know what you hid as well.

      • Kathy

        thank you linda, i was looking forever how to unhide, perfect instructions

        • shelle

          Linda you are a life saver!!  I worked forever yesterday afternoon trying to get my hidden columns.  Thank you!! Fantastic instructions.

          • Danka

            Is there are way to e-mail file with the hidden column so that receipient doesn’t see hidden columns

            • Jostmejr

              Use Tableau

      • Jostmejr

        Loved your response.  Excellent instructions that helped me a lot.  I also discovered that ‘hiding’ is only placing column width to ’0′ and that placing the column width mouse slightly to the right of the ‘hidden’ column(s) (the mouse changes from a solid vertical to a split vertical) you can drag the hidden(s) to a wider (unhidden) width.

      • Jostmejr

        Loved your response.  Excellent instructions that helped me a lot.  I also discovered that ‘hiding’ is only placing column width to ’0′ and that placing the column width mouse slightly to the right of the ‘hidden’ column(s) (the mouse changes from a solid vertical to a split vertical) you can drag the hidden(s) to a wider (unhidden) width.

        • Fay Bulbulia

          hi there,

          Highlight the entire page by the arrow in the corner, Next to colum A and ontop of number 1, right click and select unhide,

          all hidden colums and rows will ne unhidden.

          • KarinJ

            That’s the only way it worked for me! Thanks fay.

      • guest

        Thanks Linda! With all the “unhide” instructions highlighting the whole page  was the only way it finally worked for me.

      • RoyalCat

        Even if its after 2years- thank you very much, you just rescued me- i finally managed to unhide!!!

  • Kevin

    I would like to hide a worksheet and require a password to unhide … possible?

    • http://www.facebook.com/tony.mirenda Tony Mirenda

      They re-organized Excel in 2010 and it is a ROYAL PAIN IN THE ASS. But here is how you encrypt a spreadsheet that requires a Passwork to open:

      Go to “File” tab

      Go to “Info” ribbon

      Select “Protect Workbook” button

      Select “Encrypt with Password” selection

      Choose a password and follow the remaining instructions.

  • Kesavan

    Thanks for this post. very useful. Its works for me. Hi All, Select the entire rows & columns and click on format->hide & unhide-> Unhide columns instead of selecting particular column. It will works for all hidden columns.

  • madhu

    it is too good and looks very effective to present managers

  • BeatifulMind

    If you filter on any column, the hidden rows become unhidden automatically. Is there a way to prevent this. For example let’s say rows 1 – 10 in column A were hidden. You had filtering on for all columns. You click on down arrow in cloumn B and clear check mark from ‘Blanks’. You’ll notice that the hidden rows 1 – 10 in column A will become visible if those had a non-blank value in column ‘B’.

  • whotmewory

    This sucks. MY rows go from number 11 to number 485: I cannot unhide or otherwise make visible the rows 12 through 484. What now?

  • fred

    All my sheets (3) have been hidden. When I go to unhide only one sheet appears which comes up but then I can’t get back to the original sheet. I just want the standard format with all sheets visible at the bottom. They are linked and I need to do updates.

    • CK

      I have a similar problem. Somehow my file ended up a version (3) after I hid rows. I just noticed that my formulas in the unhidden rows, that pointed to the hidden rows have all become #VALUE!. I cannot unhide my rows, since all options are greyed out. None of the options listed above are available to me. I have never had this happen before. Does anyone have a solution? HELP!

      • halebery

        just put your cursor to the row that is next to the last hidden row until your cursor changes to to horizontal and 1 vertical across and double click it..there you go your row is unhidden.