Excel Can Partially Hide Numbers You Need to Keep Private

BY STANLEY ZAROWIN


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.

 

 

 

 

 

 

NEWS

IRS sets start date for tax season

The IRS announced that tax season will start in late January and that it will issue refunds to taxpayers despite the partial shutdown of the federal government.

PODCAST

Why CPAs can’t wait on automation tools

What do accounting firms waiting on others to develop AI, automation, and data analytics tools have in common with a baseball fan sitting in a stadium filling with water at an exponential rate? The answer could determine your firm’s fate.