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.

SPONSORED REPORT

2019 State of Financial Reporting Survey

We surveyed nearly 600 finance and accounting professionals on their month-end close and reporting processes. See the results.

VIDEO

What RPA is and how it works

Robotic process automation is like an Excel macro that can work on multiple applications, says Danielle Supkis Cheek, CPA. RPA can complete routine, repetitive tasks such as data entry, freeing up employee time from lower-level chores.