Software

Restrict Excel's work area by disabling empty cells

Susan Harkins shows you how to restrict users to the working spreadsheet by disabling, or appearing to disable, empty cells.

Most spreadsheets are contained in just a few columns and rows. That leaves a lot of space for users to run afoul. If there are empty cells, most users will try to fill them up.  Sometimes that tampering causes trouble and you're the lucky one who'll have to make things right again.

You can protect the sheet and limit users to specific input cells, but that's just part of the picture. If a user can see empty cells, he or she will want to use them. One thing I do to to discourage this kind of pilfering is to turn off unused columns (and rows, if applicable).

Seeing only the actual spreadsheet cells is a visual clue to users that this isn't your ordinary workbook. For some reason, the absence of empty cells seems to complete the sheet; instead of users seeing a canvas that they can alter, they see a custom application. They do their work and they move on. They just seem less curious and motivated to mess around in the sheet. At least, that's been my experience.

There are a couple of ways to make the unused sections of a sheet disappear:

  • Turn the gridlines off.
  • Hide the unused columns (and rows).

My favorite method is to turn off the gridlines, which is easy enough to do. In Excel 2003, do the following:

  1. From the Tools menu, choose Options.
  2. On the View tab, uncheck Gridlines in the Window Options section.
  3. Click OK.

In Excel 2007 and 2010, do the following:

  1. In Excel 2007, click the Office button, then click Excel Options. In the left pane, select Advanced. In Excel 2010, click the File tab. Click Options and then click Advanced.
  2. In Excel 2007 and 2010, uncheck Show Gridlines in the Display Options For The Worksheet section.
  3. Click OK.

Although this is the easiest solution, it's not always the best because it hides gridlines for the entire sheet, including the working area of the sheet, and that might not be what you want. In the figure above, the table format displays some gridlines, but displaying once you've disabled the feature can be a bit too much trouble.

Hiding the unused columns and row, as follows, is another option:

  1. To hide unused columns to the right, select the first unused column (in the sheet below, that would be column F).
  2. Press [Ctrl]+[Shift]+[Right Arrow] to select the selected column and all the columns to the right of the selected column.
  3. Right-click the selection.
  4. Choose Hide from the resulting context menu.

Similarly, to hide rows, select the first blank row and press [Ctrl]+[Shift]+[Down Arrow]. This method retains gridlines, but displays the hidden columns and rows in gray, which I find a bit distracting.

To unhide the columns and rows, select the entire sheet by clicking the Sheet Selector (the gray cell that intersects at the top-left of the header cells. Then, choose Row or Column from the Format menu and select Unhide.

Besides protecting a sheet, how to you restrict users? Do you think doing so is a good idea?

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.

3 comments
stapleb
stapleb

I like the idea of hiding rows and columns, but it might help to add security just in case you have a "fiddler" who wants to unhide them, and works out how to do it. Oh, and being the Keyboard Queen, Ctrl + 9 hides rows and Ctrl + 0 hides columns. Ctrl + Shift + ( unhides rows and Ctrl + Shift + ) unhides columns. The beauty of these keyboard shortcuts, if you only want to hide one row or column, you only have to be in a cell in that row or column. To unhide, just highlight a cell either side.

patobrien.3g
patobrien.3g

You can also restrict where the cell pointer can actually select cells for each worksheet with something like the following VBA in the worksheet's code module: Private Sub Worksheet_Activate() Me.ScrollArea = "A1:G15" End Sub

glennho
glennho

Excellent tip! (that I often forget to employ)... It pays to also include a module to UN-limit the scroll area, for that inevitable day when you need to expand the working area. Something like this: Sub UnlimitScrollArea() Me.ScrollArea = "" End Sub

Editor's Picks