Software

Hide everything but the working area in an Excel worksheet

Hiding unused cells makes it easier for users to focus on their worksheet data. Here's an easy way to clear the decks and simplify the display.

You usually hide a column or row to conceal or protect data and formulas. But you can also hide unused regions of a sheet to keep users from exploiting unused areas or to help keep them on task by not allowing them to wander. By hiding unused rows and columns, you present a sheet that focuses on just the work area. To demonstrate, we'll use the sample worksheet shown in Figure A, which has a small working area and a whole lot of unnecessary rows and columns.

Figure A

To hide unused rows in Excel 2003, select the row beneath the sheet's last used row. (Select the row header to select the entire row.) Next, press Ctrl + Shift + Down Arrow to select every row between the selected row and the bottom of the sheet. Then, choose Row from the Format menu and select Hide. Repeat this process to hide unused columns, only select the column header in the first empty column. Press Ctrl + Shift + Right Arrow and then choose Column from the Format menu instead of Row.

In Excel 2007, the selection process is the same. However, you'll need to click Format in the Cells group on the Home tab, choose Hide & Unhide, and select Hide Rows (Figure B) and Hide Columns.

Figure B

Note: Before you hide anything, make sure you don't inadvertently hide an obscure area. To find the last cell in the sheet's used range, press Ctrl + End. Figure C shows our worksheet with the extraneous rows and columns hidden -- a cleaner, less busy interface for your Excel users.

Figure C

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).

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

39 comments
Helen Phelan
Helen Phelan

Hmmm, I just lock everything except the active cells and turn off the grid lines. Gives a nice uncluttered finish and keeps my lookups and DSets clear of user interferance.

resolveaustralia
resolveaustralia

I work in Excell 2007. I can hide both rows and columns but I can not unhide columns. Only rows. There is no Unhide Columns option. Only Unhide

Rick.McKinney
Rick.McKinney

I received a spreadsheet that allowed me to edit any cell in the entire sheet if I clicked in that cell or pasted data into that cell. However, there was definitely a "work space" defined. Just as in this example, the work area was very visible... none of the gridlines in the unused area were showing. More importantly, only the work area applied formatting, formulas, etc. For example, I could click on the row number to highlight the entire row... any formatting that I added was only applied to the defined work area. The unused cells did not accept the work area. I could also insert rows into the work area that would copy the formatting and expand the work area.

chuckfab1
chuckfab1

Not that I'm a big fan of the Page Break Preview view, but why not just go to the View Menu (Excel 2003) and change the view from Normal to Page Break Preview?

BDEye51
BDEye51

A Simple, two step action to accomplish almost the same end: Go to upper left corner cell and select entire worksheet, then go to Format, Fill Color and select White. If you have different fill color already in cells they all will become white.

tnboren
tnboren

I can't see any difference between fig A and fig C. Same number of colums, same number of rows, same content.... what am I missing?

Majestic100
Majestic100

I'm working with Excel 2003 and contrary to your tip it doesn't work for column's (?) Fine for rows but I get error window stating "cannot shift objects off sheet" when performing instructions for column's

tweek101
tweek101

I thought the article will show a shortcut like highlighting what is in use and by pressing a combination of keys the rest will be hidden. I guess I am reaching for the stars :).

martin
martin

A good tip. But it is part of a 'bigger' solution. Most spreadsheets, especially when built by your colleague that has 5 more years experience in Excel, contain the 'user area' alongside the formula's. Split the whole thing up in user-area (or even: input and output), tables or fixed data and formula's. O, and while you're at it, never ever hide cells in the formula-sheet, just protect them! Martin

mfoster
mfoster

Select the rows or columns and right-click and select "Hide"

jbenton
jbenton

select the whole worksheet then press ctrl-shift-9 to unhide rows or ctrl-shift-0 to unhide columns

Rick.McKinney
Rick.McKinney

Sorry... I realized I didn't ask the question. I can't duplicate the formatted work area that I described in my response. Anyone know how to do this?

b.krisanski
b.krisanski

Not a huge issue, but most end users I have dealt with don't like having the watermarked "Page 1" etc showing when working on a spreadsheet. Having said that, I work in Page Break Preview mode quite a bit, so all what you get used to I suppose.

b.krisanski
b.krisanski

I too thought that you could achieve relatively the same thing by formating the area outside of the work area. The only real downside to this that I see is that you will get worksheet bloat i.e. file size will significantly increase. One thing I have learned over the years is that when you are dealing with large spreadsheets with lots of data - DO NOT format ENTIRE rows or columns (only do what is necessary) as this will be one of the major causes for signifcantly increasing your spreadsheet file size.

jpl1953es
jpl1953es

Please, pay attention to the fact that in Fig. A you see unused rows and columns surrounding your workarea, whereas in Fig. C there is a grey space that hides all unused cells

jbenton
jbenton

i get this message when i have comments in cells towards the right copy these and paste to a temporary worksheet then delete the original comments; you should now be able to hide your columns, do this then paste the comments back to their original position and they now display to left instead of right if they would have gone over the edge if anyone knows a simpler way to do this, please post

jpl1953es
jpl1953es

Hi: I've tried your response and it works for me in Excel 2003 but I need to point that selection of all unused columns is made via Shift+Ctrl+Right Arrow. To restore original workarea, click on upper left square and in a double step, click Format->Row (or Column)->Unhide

andymann
andymann

A little vba will hide the rows above and below your selected area and the columns to either side of the selected area. Like you I thought the article would show how to do this and we'd see some relatively simple code, which at the moment I'm too lazy to do, but not too lazy to copy.

tweek101
tweek101

I thought the article will show a shortcut like highlighting what is in use and by pressing a combination of keys the rest will be hidden. I guess I am reaching for the stars .

samuel.a.dyck
samuel.a.dyck

In addition to that, put the data/formulas that you want to protect into a different worksheet and protect/hide that sheet and turn off the display of the tabs. It will confuse new-bies, but can save you some heart-ache.

jbenton
jbenton

Hide the row and column headers (from tools, options, view) Also turn off the gridlines (use selective borders to corral the data); sheet tabs too if it's a single-working-sheet workbook Lock all cells apart from the user-alterable ones and protect the worksheet Finally, if it's a small enough working area, hide the scroll bars too You can really frustrate your colleagues this way!

jbenton
jbenton

press ctrl-end to go the the last used cell, arrow down and right, hold ctrl-shift and press down and right, hold ctrl and press 9 then 0

andymann
andymann

An alternative is to restrict the scroll area, makes navigation handy (eg Ctrl-right won't send you to column IV if the row is empty to the right) It's the ScrollArea Property: Worksheets(1).ScrollArea = "a1:x100"

resolveaustralia
resolveaustralia

Tried it. no luck. I am not certain if this is specific to my machine (as it is work based and under policies) but I have had no luck

peter.corbett
peter.corbett

Agree entirely. Hiding all the unused rows and columns significantly increases the filesize. If the spreadsheet is complex, this additional formatting will slow things considerably more

Marshwiggle
Marshwiggle

I was having the same problem, but w/ rows. I copied the sheet, selected all the cells and deleted comments, then was able to hide the rows I wanted. Very strange, since the cells that contained comments were several rows above those I was trying to hide.

pmdoyle65
pmdoyle65

I see what your saying. It's an obnoxious thing to have to do but it does work.

Majestic100
Majestic100

Tried again but this time on my desktop. Not a problem. All worked as described in instructions Retried on my laptop but............ Can hide rows but not columns. Get the message "Cannot shift objects off sheet". Now that's an anomolay I can't explain !!??!!

pmdoyle65
pmdoyle65

In addition to what is being said, if you right click on the column or row once the are highlighted, you can then click on HIDE. This is quicker than going to the menus uptop. When you want the cells unhidden then click the upper left corner to highlight them all and right click the column or row and then click UNHIDE.

jbenton
jbenton

when you have selected your area: press ctrl-F3, type in a name (eg x) and accept; press ctrl-A ctrl-9 ctrl-0 to select and hide the whole worksheet; press ctrl-G, enter the name chosen above and accept to reselect your area; press ctrl-shift-9, control-shift-) to unhide just this portion - simples!

ssharkins
ssharkins

I'm not big on practical jokes, but one year, around April Fools Day I posted a macro that displayed all text in reverse. Man... did I get some hateful letters! :)

jbenton
jbenton

Could also be that you have some columns frozen out to the left of the visible area. Try 'unfreezing' (available, if frozen, under the Window menu) or maybe the columns are very narrow - select whole sheet and Format, Columun, width (then Row, height) to something sensible. All this will of course reformat the rest of your sheet, so keep a copy you can go back to! don't you just love it when your IT supplier tinkers with an established product (albeit not perfect, but just leave it alone! Ours resets the previous files list to the standard 4 entries every day - to save disk space!!)

b.krisanski
b.krisanski

Hi, It does work in 2007, I just tried it. You must highlight the rows (or columns) first before pressing CTRL+SHIFT+9 (or 0).

Majestic100
Majestic100

Resolved at last. Not an issue between one PC and another but between one spreadsheet and another - I didn't use same spreadsheet in both machines. Issue is due to fact that there are already hidden columns in the working space. By unhiding these, you can then hide all columns outside the working space, then go back and hide those not required to be on view within the working space. Bit convoluted but does the job. Assume "bug" fixed in 2007

pmdoyle65
pmdoyle65

Now I am using Excel 2007 SP1. I was able to hide columns and rows with data with no issue. The cells do have formatting to them that they do sums for some previous rows. Are the rows or columns protected in anyway?

jpl1953es
jpl1953es

Do you have Excel 2003 SP3?, this is the only thing could cause differences. My experiement was done on Excel 2003 SP3 running on WinXP Pro. The only thing done is to activate an attribute of the columns (HIDDEN) nothing moved, in fact, if selected rows/columns contains data, no boring messages appear and, obviously, the became hidden

ssharkins
ssharkins

Thanks for the additional right-click tip!