Excel Can Partially Hide Numbers You Need to Keep Private

Some of my reports list Social Security numbers. Since the reports are often viewed by others, I need a convenient but secure way to hide those numbers—such as making only the last four digits visible. How do I set up such a display?

 

The easiest way to do that is to apply a formula using two columns—store the full nine-digit number in one, which then is hidden and protected, and apply a formula that effectively camouflages all but the last four digits in the other, as shown in the screenshot below.

The number in column A is fully hidden. Here is the formula for that:
= “***-****-” & RIGHT(A1,4)

Caveat : When you enter the full number in the first column, be sure to use the dashes, which tell Excel to format the numbers as text.

If you don’t, Excel will interpret the entry as numbers, in which case the formula will not work; the numbers must be in Excel’s text format.

As you can see, this technique can hide all sorts of sensitive information in a spreadsheet—yet keep it available when needed in the same location.

However, if you want to be sure a user can’t peek at the numbers by erasing the formulas, there is a way to protect them. Highlight the cells containing the formula and right-click to display the screenshot shown at left.

Click on Format Cells , which triggers this screen:

Select the Protection tab and place a check in the Locked box. You also have the option of hiding the formula. Before you click on OK , note the advisory at the bottom of the screen: It explains that the worksheet must first be protected before any format changes can take effect—a step I’ll get to in a moment. Click on OK .

You must now be sure to unlock those parts of the worksheet you want to be available to all users for editing—cells for data entry and notes. To do that, highlight just those cells and again open Format Cells and the Protection tab. Be sure the Locked and Hidden boxes are not checked. Click on OK .

To hide the column that contains the full Social Security numbers, highlight the column, click Format, Column, Hide .

Now the final step is to password protect the worksheet so the formula is both hidden and protected and the full Social Security numbers are hidden. Click on Tools, Protection, Protect Sheet .

Place a check at Protect worksheet and contents of locked cells and then select the activities you want to remain available to all users. Before you can click on OK , you must select a password and then confirm it.

Keep in mind that Windows passwords are at best a deterrent to people who lack computer savvy. A Google search of Word Excel passwords reveals a host of products designed to crack Windows passwords.