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

Building client loyalty with payroll services

In this report, CPA experts detail their tactics for performing successful payroll services, how to mitigate risk in the process, and the impact payroll can have as a value-added service.

PODCAST

Using drones to enhance audits

Hermann Sidhu, CPA, global assurance digital leader at EY, walks us through EY’s exciting new project to use drones to help audit large warehouses and outdoor inventories.