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

Implementing a global statutory reporting maturity model

Assess your organization's capabilities and progress toward an ideal state of global statutory reporting. Sponsored by Workiva.

100th ANNIVERSARY

Black CPA Centennial, 1921–2021

With 2021 marking the 100th anniversary of the first Black licensed CPA in the United States, a yearlong campaign kicked off to recognize the nation’s Black CPAs and encourage greater progress in diversity, inclusion, and equity in the CPA profession.