Quickly Hide Excel Rows and Columns

In some of my Excel workbooks there are calculation rows or columns that are required for producing the end result, but users don't need to see them.


I can manually hide these rows and columns, then unhide them if I need to check a calculation, or adjust a formula.


To make it easy to hide things, I mark the rows and columns with an X.


HideColMark


In row 1, the only entries are the X marks on the columns that should be hidden. In column A, the only entries are the X marks on the rows that should be hidden.


Select the marked columns


Click the row button for row 1, to select the entire row.


On the Ribbon, click the Home button


In the Editing group, click Find & Select, then click Go To Special


(Note: In Excel 2003 and earlier versions, click Edit > Go To, then click the Special button)


GoToSpecial


In the Go To Special dialog box, click Constants


Uncheck all the boxes except Text, then click OK


GoToSpecialConst


Hide the Marked Columns


With the marked columns selected, click the Format command on the Ribbon's Home tab


Click Hide & Unhide, then click Hide Columns.


Note: In Excel 2003 and earlier versions, click Format> Column > Hide.


HideColumns


Hide the Marked Rows


Use similar steps to hide the marked rows. Select column A and go to the Constants that are text, then use the Hide Rows command.


Automate the Steps


If you frequently hide and unhide the columns and rows, record a macro as your perform the steps. Then, run that macro to automatically hide all the marked rows and columns.

11 comments to Quickly Hide Excel Rows and Columns

  • AlexJ

    My approach is to set outlines on the sheet. Then I have macro buttons on a command bar the will set the outline level for the whole sheet to 1,2,3, etc. using

    ActiveSheet.Outline.ShowLevels rowlevels:=Level, columnlevels:=Level

  • Thanks Alex, that's an interesting approach. I don't use outlines too often, but will try your code.

  • AlexJ

    Actually the outlines are really useful. I typically have up to 5 levels
    Level 1 to 3 will be "User" layers – allowing reveal of areas on the sheet based on user actions
    Levels 4,5 are "Design" layers holding helper columns and the like.

    The macros allow me to open up the whole sheet.

    To open up a single set of rows or columns you just need to identify a single cell within the outlined range. Then,
    range.EntireRow.ShowDetail = True
    will open the whole outlined area.

    You need to be carefull to have spacer rows between outline areas or they won't always work.

  • Alex, thanks for the detail on how you use outlines.

    I guess you hide the Outline bars so users have to use your macro buttons to open their ranges.

  • AlexJ

    Exactly – I have a button to toggle outline bars on/off for design/presentation.

    I could forward some demo code if you wish.

  • Thanks, that would be great! If it's okay with you, I'll write about your technique next week.
    Please send email to ddalgleish AT contextures.com

  • sam

    If you hide or unhide columns / Filter out rows frequently, you are better off defining custom views

    Hide the columns you want, Go To Views – Custom Views and Say Add – give a name – Say customer view
    Uhide all the columns -repeat the process – but call it as complete view.

    Create a Drop down validation – having the name of the views

    In a sheet change event – write a code which will display the view when the user selects from the drop down...

  • Contextures Blog » Hide Excel Rows With Outlining

    [...] Quickly Hide Excel Rows and Columns [...]

  • Judith SK

    I want to create a macro which will hide columns A, F, G, O,P,Q and rows 11 to 23.

    I need to create a macro and then another one to unhide these columns and rows.

    Please help.

  • Judith, record a macro while you hide those columns and rows.
    When recording, select Personal Macro Workbook as the place to store them.
    Then record another macro while you unhide them.

    To run the macros later, choose Tools | Macro | Macros
    Select a macro, and click Run.

  • [...] Try this if you choose not to use macro's, the only way I know is is manual. Quickly Hide Excel Rows and Columns | Contextures Blog [...]

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>