Show only few rows & columns in Excel [Quick tip]

Posted on July 24th, 2012 in Excel Howtos - 89 comments

Each new sheet in MS Excel comes up with a 1,048,576 rows and 16,384 columns. While it has a certain binary romantic ring to it (2^20 rows & 2^14 columns), I am yet to meet anyone using even half the number of rows & columns Excel has to offer.
So why leave all those empty rows & columns hanging in your reports?

Would it not look cool if your reports showed only few rows & columns as needed, like this:

Show only few rows & columns in your Excel reports

Today, lets learn how to do this.

Showing only few rows & columns in Excel

Step 1: Select the column from which you want to hide.

Step 2: Press CTRL+Shift+Right Arrow to select all the columns till XFD.

Step 3: Right click and hide

Step 4: Select the row from which you want to hide.

Step 5: Press CTRL+Shift+Down Arrow to select all rows until 2^20

Step 6: Hide the rows too. And you are done!

See this demo:

Bonus tips: Learn how to make better Excel sheets

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

89 Responses to “Show only few rows & columns in Excel [Quick tip]”

  1. Abdo Attia says:

    thaaaaaaaaaaaannnnnnnnnnnkssssssss
    🙂 

  2. Rob says:

    On cells where there are no formula, will removing the displayed cells improve performance? Will not displaying a few million cells each time have a performance perk, similar to the boost VBA application.screenupdating gives or does it still process the cells despite not being displayed?

  3. Charlie Epes says:

    Excellent Tip!  Thank you -

  4. David R says:

    Is there a way to prevent scrolling after you have hidden all of the unnecessary rows and columns?

  5. Nelson says:

    I use these all the time. Have them set on the Quick Access Toolbar.  Note: just have the bottom right cell you wish to show selected at the time of invoking the VBA code.

    Sub Hide_ColumnsandRows()

        Dim Answer As VbMsgBoxResult
        Dim lastCol As Integer, lastRow As Integer  
        Answer = MsgBox("Please make sure that your current ACTIVE CELL is the cell that you want to be the last visible cell. ", vbOKCancel, "CAUTION!")
        If Answer = vbCancel Then Exit Sub
        If Answer = vbOK Then
                lastCol = ActiveCell.Column + 1
                lastRow = ActiveCell.Row + 1
                Range(Cells(1, lastCol), Cells(Rows.Count, Columns.Count)).EntireColumn.Hidden = True
                Range(Cells(lastRow, 1), Cells(Rows.Count, Columns.Count)).EntireRow.Hidden = True
        End If   
    End Sub

    Sub Reveal_ColumnsandRows()
            Cells.EntireColumn.Hidden = False
            Cells.EntireRow.Hidden = False
    End Sub

    • Viswa says:

      Hi Nelson

      Thanks for sharing the code. I am getting compile error in the line

      Answer = MsgBox(“Please make sure that your current ACTIVE CELL is the cell that you want to be the last visible cell. “, vbOKCancel, “CAUTION!”)

      Please help to resolve

      • Hui... says:

        @Viswa

        Try:
        Answer = MsgBox("Please make sure that your current ACTIVE CELL is the cell that you want to be the last visible cell. ", vbOKCancel, "Caution")

        You may/will have to retype the " marks manually if you copy from here as WordPress converts them to something that looks like a " but isn't

      • Glenn says:

        Awesome, thanks Nelson

  6. David B says:

    Will this also work to eliminate blank fields when I am working with my own or (more often) imported data files that seem to include all rows and columns ...........

  7. This is a great tip.  It's funny how few people do this and leave all the blank rows and columns when there is no way they are going to need them.

  8. Marninei says:

    There's one tip I REALLY wish someone could help me with, also has something to do with endless columns and rows.

    Sometimes it happens that you accidently edit all the cells in a certain row\column, causing you scrollbar to become really small and scrolling becomes very sensitive.
    Is there a way to undo this?

    Thanks

    • Chandoo says:

      You can select the all the rows and go to home > clean > clean formats

      (the icon looks like an eraser)

    • 5antiago says:

      Do you mean when Excel suddenly increases the size of the active sheet, and the scroll bar shrinks to reflect how much more space it is required to control?  Pressing Ctrl-End takes you to row 1048576, everything suddenly becomes slow and the file size balloons to gigantic proportions...

      In this case you have to select all the rows below where you want the scrollbar to "stop" and delete them (then repeat with all the columns to the right of where you want to stop, delete them too).  *Then save the file* and your scroll bars are back to their old size

      (I have no idea why Microsoft thought it'd be a good idea to allow Excel to show spreadsheets that are so large they crash the whole computer, sounds like a triumph of marketing over sense to me) 

  9. Gino says:

    Right click your page tab and select properties.  Set the scrolling limit to whatever you'd like!!

    • David R says:

      I don't see a "Properties" option when right clicking on a tab. Where else might we find this Scrolling Limit option?

  10. Gino says:

    In Excel 2010 (and 2007 I think), you can use ALT-F11 to open your VBA window.  Select the sheet you want in the Project window and use F4 for the Properties sheet.  In there you can set the scroll area to whatever you want.

    As a side bonus - the last parameter in the Properties sheet is Visible.  If you want to really HIDE your sheet - you can set the property to VeryHidden.  For lesser experienced Excel users, they will not know the sheet exists even if they right click on the sheet tabs to see Unhide Sheets.

    Not a foolproof way to hide sheets from experienced users but it can keep your more inexperienced users out of trouble.

    Hope that helps!
    Cheers,
    Gino       

    • Gagan says:

      That's awesome tip Gino

    • Marninei says:

      Hi Gino,

      Thanks for the tip, I can think of several ways of applying it.
      However, it still doesn't make the scroll bar "normal" size again [chandoo, neither did the "clear all"].

      This is an issue I find many people in my office asking me about and has yet to be solved. Any other suggestions?

      Thanks again,
      Oren

      • Hui... says:

        @Gino

        When you say it doesn't make the scroll bar the normal size again
        Is all your data for the worksheet visible on your screen?
        If the answer is Yes, then the large scroll bar is actually the correct size.

        You can reset it by say going to cell A10000 and type a value
        Go back to A1
        The Scroll bar should be reset

        Go back to A10000 and delete the cell

        • Marninei says:

          I had tryed that many times and with no success.

          As you scroll further down in the sheet the the scroll tab (for lack of a better name, not sure that's what it's called) gets smaller and smaller.
          If you don't do anything and go back to A1 the tab becomes big again. If you do edit the cell the tab remains small.
          After going back to the cell and deleting or "clear all"ing 😉 it and then going back to A1 nothing changes and the tab remains small.

          As far as I know, this is also the case with my team memebers.

          • Luke M says:

            @Marninei
            Here's the method I use to reset my scroll bars.
            1. Go to last cell I actually am using.
            2. Select all rows/columns below/to the right of where I am.
            3. Right-click, delete.
            4. Save the workbook. 

          • Marninei says:

            Hallelujah!

            Thank you very much for the help.
            The secret ingredient was in fact the SAVE...

            Hope this magic works for all my other sheets as well.

            Thanks again,
            Oren

    • arg says:

      Hi Gino,

      Thanks for your valuable tip.

      Thanks a lot

  11. Anish says:

    Excellent tip, how do you unhide those rows, if you need to.

  12. Wookiee says:

    That is SO simple and so effective, I feel like an idiot that it's never occurred to me before. Not an utter and complete idiot, for at least I'm smart enough to subscribe to Chandoo.org!

    Thank you for the tip.

  13. Kiev says:

    Simple and easy to apply. i will try it right now. thank you for your tips.

  14. Bharat says:

    Chandoo, this is a great tip. I used to just remove the gridlines and make reports thinking thats the best way. This is a step ahead and gives a clean look. Thanks.

  15. Rao says:

    how to hide Row & col lables?

  16. Mickm says:

    In Excel 2003 it's Tools menu, Options, View, turn off Row & Column headers in the Windows Options section (Not sure re later versions)

  17. Kshitij Sharma says:

    A small correction to the steps written in the tutorial above:

    Step 1: Select the column from which you want to hide.
    Step 2: Press CTRL+Shift+Right Arrow to select all the columns till XFD.
    Step 3: KEEP HOLDING THE SHIFT KEY and Right click on the column headers and select hide
    Do the same for the rows !

  18. Mark F. says:

    Does this increase the size of the file?

  19. Dan says:

    Wow that was a simple one! But something I didn't know! Thanks 😀

  20. Stef@n says:

    Hey

    You can also set the column and rows width to 0

    Regards Stef@n

  21. girdhar says:

    i was searching for this for many months. Atlast i found it
    But disappointed after using it
    My file size is less than 1 MB and has 10 sheets
    If I am hiding rows and columns, in all 10 sheets, from U in column and from 40 in rows then size of file is increasing to 10+MB . 
    Size increases if hiding row/columns
    is there any problem in my file or this is usual/common ?

    • Lasse says:

      girdhar:
      The file size depends on what excel need to store in it.
      Since you are not actually removing anything from the sheet, but simply not showing it, the file size does not shrink.
      On the other hand, excel now have to remember which rows & columns that are hidden, so that extra information increases the file size...

      Chandoo: Thank you very much for this treasure trove! It has become the first place I go when I need to find info on anything excel-related.

      /Lasse

  22. lokesh says:

    thanx chandoo sir again an excellent tip for excel

  23. Shari says:

    Hi Chandoo!  Thanks so much!  I have often wondered how some Excel files that I've downloaded from the web have this clean look--with no extra rows and columns.  Now I know, thanks to your tip!  This is awesome.  I have already begun using this on my spreadsheets at work!

  24. xsaed says:

    what if it is the other way, column A-W is hidden, how to unhide all column instead ofcolumn by column.

  25. Annie says:

    This was helpful in getting rid of unnecessary columns, but im typing a spreadsheet and when i get to the end of one row and push "tab" to start the next row, it will not tab down to the begging of the next row.. is there a way to adjust this?

  26. El says:

    This is a great tool.  I use borders on my spreadsheets.  I noticed that after hiding all the rows and columns, the last columns in my spreadsheet lost its border and now the spreadsheet looks odd with no right-hand border.  Is there a way to fix this or protect the border when hiding the unnecessary columns?  Thanks!

    • gino says:

      format a right hand border on your last column - not a left hand border on the first hidden column - it should show nicely.

  27. Abd ELHakim says:

    Thanx a lot its so easy i was think it s by mecro

  28. Zuber says:

    Thanks was very easy

  29. Bashar says:

    Thanks for the help.

  30. Dime M says:

    Hi. I have a question:
    I have created a data base with more than 350 columns. When trying to open the file again, only the first 256 columns showed and everything else was gone/hidden. I've tried with any unhide option and nothing worked so far. Any suggestion?
    Thanks in advanced!

  31. apt says:

    Hello,

    Thanks for this tip.

    Shokran !

  32. Tzipi says:

    Thanks!!!!! 🙂

  33. ksk says:

    hi.

    want to learn vba .
    how i need to get started....?

  34. Julien says:

    It should not be permitted to be so efficient ! 🙂
    Thanks. Your post helped me a lot.
    Julien
    http://www.ajobilove.net/

  35. […] Try this as a solution: Show only few rows & columns in Excel [Quick tip] | Chandoo.org - Learn Microsoft Excel Online […]

  36. Andrew says:

    20 websites later, I should've looked at yours first. THANK YOU!

  37. Craigles says:

    Ahhhhhh Why was this so hard to find?

    Thank you

  38. anny says:

    OHMYGOD YOU can't imagine the HUGE knot you've just outdone in my brain. all these columns...i don't need them, please go away...how how HOW DEAR LORD????
    these measly appreciative words are not enough. THANK YOU!!!

  39. Puneet Bajpai says:

    Thanks Chandoo....You are too good

  40. Morteza says:

    Very Good.Thanks.

  41. krishna says:

    Thanks

  42. Rafal says:

    This is cool but I want to do exactly the same on the left hand side of the report. I want to have a A4 page like report in the centre/middle of the screen. So while your solution does it beautifuly for the right hand side of the report and for the section underneath it too, how to do it on the left hand side? Hidding columns won't work there. is there any other way?

  43. Ananda says:

    OK! Thanks! I got the point! Thanks for the tips!

  44. Vimal Anbu Selvam R says:

    Restoring the display
    If you need to unhide the rows and columns, first select the entire sheet. Then, in Excel 2003, choose Row or Column from the Format menu and select Unhide. In Excel 2007, click Format in the Cells group on the Home tab, choose Hide & Unhide, and select Unhide Rows (or Unhide Columns).

  45. […] you’re using Microsoft Excel, this link explains how to do it – I couldn’t have phrased it better […]

  46. Kuldip says:

    Thanks a looooot..

  47. Kezea says:

    how many rows and columns on 2013?

  48. Patty Smart says:

    I have 300 columns.
    How would I quickly hide all the columns EXCEPT: 5, 123, 287?

  49. Nicole says:

    Oh thank god I found you. Do you know how many websites I went through for this simple little fix? How is the search engine not figuring out when I say "I only want to see Page 1 in Excel" that I only want to see Page 1? You're a lifesaver!

  50. Fadhly says:

    Thanks. Very helpful 🙂

  51. Mike says:

    On my version of excel the background color of the hidden area is white, not grey... so no difference between the hidden area and the work space... is there a fix? Cheers.

  52. Anuj says:

    How to do this in Office 2016 for Mac, any tips?

  53. george says:

    Thank you sooooooooooooo much

  54. amin uddin says:

    thanks alot
    You solve my big problem as sir given a task
    thanks again

  55. DL says:

    Thank you! A clear, simple answer to surely a common problem for the amateur excel user. Couldn't find it elsewhere. Appreciated.

  56. April says:

    Is there a way to make "Tab" work when going from the end of a row to tab to the beginning of the next? For example: I now only have through column H shown but when I am in H1 and want to tab to A2 it won't do it. Thanks for the help 🙂

    • Hui... says:

      @April
      If you pre-select A2:H100
      The press Tab instead of Enter as you enter data
      Excel will go from H1 to A2, H2 to A3 etc when you press Tab instead of Enter

      • April says:

        But there is no way to make the form do this automatically without having to select the fields each time?

  57. SCV says:

    WELL BUTTER MY BISCUIT

  58. Toli says:

    Wonderful! Thank you!

  59. Pauline says:

    Fantastic, made me look good to my boss. Thank you so much

Leave a Reply