Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Partially Blocking Social Security Numbers.

Partially Blocking Social Security Numbers

Written by Allen Wyatt (last updated December 4, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


If you have a worksheet that includes Social Security Numbers in it, you may be looking for a way to protect the numbers by only displaying the last four digits. So, instead of displaying 278-53-6128, you would only want to display ***-**-6128.

The way to accomplish this depends, in large part, on whether the Social Security Number is stored in the cell as a number or as text. If the SSN is entered with its dashes (as in 278-53-6128), then Excel stores it as text. If the SSN is entered without dashes (as in 278536128), then Excel stores it as a number.

If the SSN is stored as a number, you may be tempted to create a custom format that hides the first part of the number. Unfortunately, there is no way to do this with a custom format. You could create a custom format that would hide all except the first digits, as in this manner:


As you can surmise from this example, custom formats don't allow you to mask out anything except the last portion of any value. Another drawback to this approach, however, is that Excel "rounds" the SSN, such that 278536128 is displayed as 279-**-****.

The best solution to displaying only the last part of a Social Security Number is to use a second column for the actual display. Instead of trying to format the number (or text) itself, it is best to use a formula that refers to the number and creates the desired result. For instance, if the SSN is in cell B7, then you would place the following formula in a different cell:

="***-****-" & RIGHT(B7,4)

This formula will work with any SSN, regardless of whether it is stored as a number or as text. The other big benefit to this approach is that it allows you to completely hide the original numbers. Even if you were able to use a custom format to hide the first portion of the number (which you can't), someone could still see the SSN in the Formula bar if the cell containing the number is selected.

Using the formula approach, however, allows you to hide the source column, or use sheet protection to hide the contents of the column. This is a big benefit if your goal is to really protect the Social Security Number from prying eyes.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10941) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Partially Blocking Social Security Numbers.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Using Named Ranges in a Macro

Named ranges are a great capability provided by Excel. You can define all sorts of named ranges in a workbook, but how do ...

Discover More

Finding Other Instances of Excel in a Macro

When processing information using a macro, you may need to know if there are any other instances of Excel running on a ...

Discover More

Changing Character Spacing

Excel allows you to adjust spacing between cell walls and the contents of those cells. It does not, however, allow you to ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Adjusting Center Across Selection with a Cell Value

One of the most common cell alignment settings I use is the one to center across a selection. If you want to vary how ...

Discover More

Using Strikethrough Formatting

Need a line through the middle of your text? Use strikethrough formatting, which is easy to apply using the Format Cells ...

Discover More

Drawing Borders

Adding borders around cells is a common formatting task. You can make the task more intuitive by actually drawing the ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 7?

2021-12-06 11:58:43


There is LITERALLY nothing you can do that cannot be overcome with trivial efforts. As long as A file with the full SSN exists, no chain of effort, even involving VBA, can keep someone from locating the information and getting it.

Even with a user-facing spreadsheet opening a master spreadsheet (with layers in between for no useful reason, if desired) via VBA for the seconds it takes to update a user-facing data table and a password to open it, or something in between... all of it takes trivial effort to find each piece and do it yourself.

A file that cannot be opened... that'd work, but is of no real use, eh?

And I do mean TRIVIAL effort.

Not even to mention that other programs do not have to respect any aspect of Excel file security. And DON'T. Won't swear you can today, 'cause I haven't tried it recently, but up till a couple years ago in my personal experience, Google's spreadsheet would open any Excel spreadsheet regardless of protections.

Examining formulas in the XML file that is really saved is barely past the "trivial" level, but only barely. So while you might keep FORMULATEXT from revealing where a cell's formula looks to, you still can't keep the information from being seen by the simple expedient of looking in a file with a text editor. Nothing more required and all the protections are gone.

The orientation of Excel is the user of the moment matters and very little else does. This makes sense, even to a person like me who holds MS/Excel's feet IN the fire whenever possible. But that means the writer is only of primary importance while writing the spreadsheet, never, ever past that act. Nowhere is this more obvious than where security of anything in Excel is concerned.

If you include full social security numbers in anything user-facing, anything at all, you need fined by the government, disciplined by your employer, and sued by the employees. Outside of an HR network that is not connected to the internet or any network other than itself which actively scans your computer for activity trying to reach outside networks whilst you are on it, and that implements a virtual machine for your session anyway, I suppose.

Excel offers nothing more than, so long as a person is happy not to know or see things, it WILL let you arrange that. But for the &*^#'s that will delve, oh, just for the fun of it, not for any bad reason... there's nothing. Even a nice, climbable chain link fence with a lock on its gate offers more security than Excel.

2021-12-04 11:16:16

Brian L.

Good tips, but it should be noted that businesses should NEVER, EVER store employees’ social security numbers in a spreadsheet. The risk and impact of a SSN breach is just too high - the convenience of spreadsheet analysis just isn’t worth that risk. Regardless of which Excel protection features you apply, any run-of-the-mill hacker could easily crack open the data, if the spreadsheet fell into the wrong hands (e.g., lost or stolen laptop). Keep SSNs in your HR system, and use another employee ID number for ancillary analysis & reporting. See the warnings on this page:

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.