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?