Excel Tips: Workbooks, datasets & directories

TORONTO – Canadian accountants working in large teams know the importance of protecting workbooks. You can protect an Excel workbook from changes to the structure of the workbook by turning on workbook level protection. This will prevent another user from inserting new worksheets, deleting a worksheet or renaming a worksheet. 

To protect your workbook, click on the Review tab of the ribbon and click on the Protect Workbook tool in the Changes group. Excel then will show you the Protect Structure and Windows dialog box. 

The two check boxes in the dialog box enable you to define exactly what you want protected in the workbook. Here is what each choice does:

• Structure. This will prevent any changes to the worksheets. A user cannot add new ones, delete existing ones, change their names or the order of any worksheets.
• Windows. This will stop a user from moving any windows that are being used in the workbook.

At the bottom of the dialog box you can specify a password to use for this level of protection. When you click the OK button, Excel asks you to repeat any password you specified. If you did not provide a password, then Excel protects your workbook directly.

If you later want to remove the protection applied to a workbook, you do so by following the same steps you used to protect it. To unprotect your workbook, click on the Review tab of the ribbon and click on the Protect Workbook tool in the Changes group. If you used a password to protect the workbook, Excel displays the Unprotect Workbook dialog box. At this point, all you need to do is enter the correct password and the workbook is unprotected.

Note that protecting a workbook without protecting the worksheets it contains provides you with little protection. Even though a user cannot delete a worksheet in a protected workbook, they can delete the information in the worksheet. Workbook protection does not protect the contents of your workbook at any level lower than individual worksheets.

Alan Salmon, founder, K2E Canada

Deleting Duplicates from a Dataset

It is very easy to delete duplicate values from a dataset:

1. Select a cell in the dataset.
2. Go to the Data Tab.
3. Click on the Remove Duplicates in the Data Tools group.
4. Excel will highlight the dataset. If there are columns missing in the selection, go back and make sure there are no blank columns in the selection.
5. Make sure that My Data has Headers is selected if your dataset has headers.
6. By default, all the columns are selected. A selected column means that the tool will use all the columns to check for duplicates. Duplicates in an unselected column will be ignored. Select the columns that you want to match for duplicates.
7. Click OK. The dataset will update, deleting any duplicate rows. A message will appear to tell you how many rows were deleted and how many rows remain in the dataset.

There is no "undo" for this process! Save your work before you run this process

Moving Around Between Directories

If your Excel files are in different directories, changing paths in the Open Dialogue box can be a pain. Here is a shortcut that helps to minimize that pain:

Let's assume you are working on a workbook. You then open a second workbook in a different directory. (You find it and open it after clicking your way to it in the Open dialog box.)

Now you want to open a third workbook. When you display the Open dialog box, Excel assumes you want to start from where you opened the previous workbook. But what if you want to open it from the same directory where the first workbook is located? You could use the Open dialog box to go back to the original directory, but that is a pain. Here's a neat shortcut to ease the pain:

1. Open the first workbook.
2. Press F12. Excel then displays the Save As dialog box, starting from the directory in which the workbook was originally loaded. (This is the directory you want.)
3. Close the Save As dialog box by immediately pressing Esc or clicking on Cancel.

Now when you use the Open dialog box, Excel starts in the folder you were last in, which was the one displayed in step two — the one you want.

Alan Salmon is recognized as Canada’s leading analyst in accounting technology. As the founder of K2E Canada Inc., Alan has over 34 years of business, management systems, education and journalism experience.