How to avoid mistakes

In offices and in private, Excel files are the midpoint of important facts and figures. Incorrect input is not only annoying, but it also costs time, money, and nerves. And it’s not necessary, either. You can make sure that people only can enter what makes sense and then spend your time with more pleasant things.

Here, you’ll learn several ways to make sure everything runs smoothly.

All screenshots are done using Excel 365. Excel 2019 is rather rare and mostly is only optically different.

Chances are you don’t need example data. But if, you can download them at

www.shortandspicy.online/

And now have fun securing your Excel files!

For a while lot was unclear about who uses which version. That problem should be solved now with the start screens displaying the installed version.

The functional difference between Office 365 and 2019 is the cloud connection, that is inextricable part of 365 and almost non-existent in 2019. Also, Excel 2019 looks somewhat different. Still, the differences are so small, you seldom will notice. Most of the features we’re going to cover are the same in both versions and most of the former versions, too.

Only the last chapter is entirely meant for users of Excel 365. Its features won’t work in any other office version.

This would clearly be the hardest way to lock out any other user. Attempting to open the file, a password is asked and if one doesn’t know it, it won’t open. Not at all. Neither for the creator of the file if the password is forgotten. Important company data could be inaccessible forever. Therefore, password protection is sometimes forbidden in companies. It would be better to think twice and maybe better take a protected folder in the company network, where the administrator can get access and rescue your files.

If you still decide it was a good idea to block the access that strictly, it works this way: Click File to open the Backstage Area.

Then, navigate to Save as…. In Excel 365, go to More Options…, in Excel 2019, go to Browse….

You now get the well-known Save as… dialogue.

Left of the Save button you’ll find the Tools. From the menu opening on click, select General Options….

Now a window opens to specify a password to be asked when opening the file. A good password is long, contains numerals, letters and characters and is not to be found in any dictionary. To remember it later, think of an important event in your life and make it a sentence. Then, only use the first letters, numbers, and punctuation marks to get an expression that is impossible to guess for anybody else.

Then, you have to repeat the password one more time and of course, to save the file.

If you then attempt to open it, you’ll be asked for the password. Whoever doesn’t know it, cannot open the workbook.

By the way: whenever in the example files in this book a password is asked, it is only a symbolic one: a

Often, you may want to allow people to open the file, but in read-only mode - to view everything, but no touches allowed. There are three options to get it done.

The first option works pretty much like the complete lock-out we just covered. But when in the General Options, this time put a Password to modify.

It works. Still, some people who indeed only intend to view the content, only skim the text and misunderstand what it says:

Often, users believe they are not getting access at all. But that’s a misunderstanding! Anyone can click Read Only to open the file and read or even copy its content. It only can’t be changed in whatever way. If you feel this would be your way of choice, better tell the others how to handle it. Otherwise, your intention may easily fail the practice test.

Probably the most convenient way is to allow users to open the file without restrictions. They can view, click, copy, and print anything they like. Only if they attempt to change the content, they will get a message that this was prevented. People who indeed don’t edit anything will feel free and trusted. And very likely, they will understand that the author of a file wants to keep control of what happens.

To get this done, go to the Review tab and click the Protect Sheet button.

You’ll be asked for a password. As long as only checking, you may safely leave the box empty. Later, do pick a safe password as explained.

Once applying the password, you’ll be asked for confirmation. After that, everybody can view anything, but not edit a thing. This can only be done after entering the password.

If that window made you curious - we’ll get back to it in chapter 5.

You can make sheets completely invisible. But if doing that, one can only hope that the person who hid it later remembers what he or she has done! Otherwise, this person could be terrified noticing important information is completely lost…

Right-click the name of the sheet you’d like to hide and select from the context menu Hide. After that, it’ll be gone.

If you want to get it back, again right-click one of the remaining sheets, select Unhide… and make your decision which one you’d like to get back.

As you see, at this point it’s not terribly secret. To make it inaccessible indeed, you also need to protect the workbook structure. This will be our topic in the next chapter.

We have seen now how to protect the whole file and the content of a sheet. Still, it happens that people delete whole sheets. That would be really bad as this procedure cannot be made undone. Gone for now is gone for good. Pressing CTRL + Z does not help. All you can do on a local hard disk is to close the workbook without saving and then to re-do everything from the last saving. Working on a cloud storage, one can go back to previous versions, thus maybe keeping the steps before deleting the sheet. Anyway, the warning by Excel should be taken seriously:

In order to prevent your sheets from getting deleted and also, to control hide and unhide, in the Review tab you’ll find Protect Workbook.

Also here, you do not need to set a password - still, without it, the benefit will remain pretty limited.

As usual, you then need to confirm the password one more time.

After that, do remember your password. Otherwise, hidden sheets will remain hidden forever.