Microsoft Excel: How to make Social Security numbers private

By J. Carlton Collins, CPA

Q. I have a report in Excel containing names of employees, including their Social Security numbers. I'd like to print the report showing only the last four digits of each employee's Social Security number, but I don't know how. Can you help me?

A. I recommend using text arithmetic to solve your problem. To do this, create a new column containing the formula ="*** - ** - "&RIGHT(B2,4), to insert asterisks and dashes along with the last four digits of each employee's Social Security number, as illustrated in column C below. Once completed, you could then hide column B and print the desired report.

A slightly faster approach might be to right justify the Social Security numbers in column B and then shrink the width of column B, as illustrated below. This is not an elegant solution, but a quick trick you might employ nonetheless. Please note that these approaches work only for documents you distribute in printed form.

techqa-ssn

About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a CPE instructor, and a JofA contributing editor.

Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2007 through 2016 versions, unless otherwise specified.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.

RESOURCES

Keeping you informed and prepared amid the coronavirus outbreak

We’re gathering the latest news stories along with relevant columns, tips, podcasts, and videos on this page, along with curated items from our archives to help with uncertainty and disruption.

VIDEO

Excel walk-through: Sparklines

Want to liven up your spreadsheets with some color and graphical elements? Kelly L. Williams, CPA, Ph.D., shows how to use Excel sparklines, which illustrate data trends and patterns via small charts that fit in a single Excel cell.