While accounting solutions like QuickBooks and Peachtree are incredibly useful, sometimes nothing can beat a plain old spreadsheet for ease and versatility. Unfortunately, while Excel may be a great option for tracking accrued vacation or fixed assets (to name a few possibilities), it doesn’t provide the same level of out-of-the-box security as Peachtree or QuickBooks. As such, all it takes is one user who is unfamiliar with how the spreadsheet works, and all your hard work—and data—can be irretrievably lost.
The good news is that with a little tweaking, Excel can provide some basic security measures that will protect the integrity of your spreadsheets. If you are sharing your spreadsheet with others, or storing it in a publicly accessible place, you should seriously consider implementing one or more of these security measures.
Protect All Cells
The simplest option is to protect everything in your spreadsheet. Anyone wanting to edit a cell will first be required to unprotect the sheet by entering a password. To implement this, just go to the Review tab on the ribbon, and click “Protect Sheet”. You can select what users will still be able to do while it is locked—by default they can only select cells—and then enter a password and hit OK. Users will not be allowed to edit any of the spreadsheet until they first go to the Review tab, click “Unprotect Sheet”, and enter the password.
The downside to this method is that once a user has unlocked the spreadsheet, it will remain that way until they relock it. Thus, if you opt for this method, be sure to stress to everyone who edits it that they must re-protect the sheet when they’re done editing.
Protect Most Cells
You also have the option to leave certain ranges of your worksheet permanently unlocked, while the rest is protected. To do this, on the Review tab first make sure your sheet is unlocked, then click the “Allow Users to Edit Ranges” button, click “New…”, and then select the range that you want users to be able to edit. You have the option to add a password to the range, or you can leave it blank (more on this in the next paragraph). Once you have selected your range, click OK. You can add another range, or click the “Apply” button, and then OK. Lastly, go back to the Review tab, click “Protect Sheet”, and protect your entire worksheet, just as discussed above. The end result will be a sheet where everything except for the ranges that you have specified will be locked.
If you do choose to enter a password when you are selecting the range, then once you have locked the workbook, users will be prompted to enter a password to edit the cells in that range, while the rest of the sheet will still remain locked. The beauty of this method is that you can use a different password from the one you use to lock your sheet, thus allowing authorized users to edit specific cells while not giving them the ability to unlock the entire sheet and edit everything. You can also specify different passwords for different ranges, allowing you to further customize your sheet security.
Protect One Cell
Finally, you may just have one or two cells with very complicated formulas that need protecting. In those instances, the simplest approach is to select all the cells in your sheet (press Ctrl-A, or click the little box just to the left of the top of column A), and then right click on any cell and select “Format Cells”. Go to the Protection tab, uncheck the box marked “Locked”, and click OK. Then select the cells you want to protect, and recheck the “Locked” box. Lastly, go to the Review tab on the ribbon, select “Protect Sheet”, and protect your sheet as previously discussed. Since you’ve unchecked the “Locked” box for almost all cells though, they will remain editable, while only your important formula cells will be locked.
You can also complement any of these methods with the “Protect Workbook” option, which will prevent unauthorized users from rearranging, adding, or deleting your tabs.
If you haven’t given consideration to protecting your spreadsheets, you should definitely do that today. In just a few minutes time, you can ensure years of hard work won’t be unintentionally or maliciously destroyed.
About Blackman & Sloop CPAs, P.A.:
Blackman & Sloop is a full-service CPA firm headquartered in Chapel Hill, North Carolina and is actively involved in auditing, taxation, management consulting, financial planning, and related services. The firm directs a large part of its services toward providing management with advice on budgeting, forecasts, projections, financing decisions, financial analysis, and tax developments. The firm also performs review and compilation services and prepares not-for-profit, corporate, individual, estate, retirement plan, and trust tax returns as well as technology consulting services regarding installation and training on QuickBooks. Blackman & Sloop provides services in Raleigh, Durham, Chapel Hill, RTP, Hillsborough, Pittsboro, Charlotte, and the rest of North Carolina. To find out more please visit http://www.blackmansloop.com
Contact: CPA cpa@blackmansloop.com
Toll Free: 1-877-854-7530The Exchange West
1414 Raleigh Rd, Suite 300 Chapel Hill, NC 27517