At a glance
1. Protect, unprotect and re-protect worksheets
There is no built-in way to unprotect or protect multiple sheets, but you can apply a macro to achieve what you want. Note the macro warning at the bottom of this answer.
When you don’t use a password, the macros you need are straightforward.
The companion video will demonstrate how to use macros if you haven’t used them before and how to save macros.
Four macros are shown in Figure 1. These macros are all in the companion Excel file.
The first two macros go through every sheet in the file and either protect or unprotect each sheet without using a password.
The last two macros work on the active sheet – the sheet that is currently selected.
To perform the same actions but with a password, in this case ABC123, the macros are slightly different and are shown in Figure 2.
Macro Warning: Macros clear the Undo list. After you run a macro, you can’t undo it or anything you did before you ran the macro. It is a good idea to save the file before you run a macro. If something goes wrong, you can close the file without saving it.
2. Calculate time and a half and double time from a total overtime figure using IF functions or MIN and MAX
The structure that I will use to demonstrate the formulas is shown in Figure 3.
IF functions can be used to calculate the overtime split. The MAX and MIN functions can also be used for the calculation. Let's see examples of both techniques.
IF functions
The IF function formula for column D2 is:
=IF(C2>2,2,C2)
This has been copied down the column.
This formula means that, if the value in cell C2 is greater than 2 then display 2. Otherwise, display the value from cell C2.
There are a couple of options for the formula in cell E2. We could use another IF function, such as:
=IF(C2>2,C2-2,0)
This has been copied down the column.
This formula means if the value in cell C2 is greater than 2 then subtract 2 from the value in cell C2, otherwise display zero.
We are only splitting the total overtime between time and a half and double time, so we don’t need another IF in cell E2. We can just subtract the time and a half value from the total overtime.
=C2-D2
MIN and MAX functions
When you are dealing with decisions to display a value or zero, you can often use the MIN or MAX functions to perform the calculation. The alternative MIN function for cell D2 is:
=MIN(C2,2)
This displays the lowest value between C2 and 2. It will never display more than 2.
The formula for cell E2 using the MAX function is:
=MAX(C2-2,0)
This displays the highest value between C2 subtracting 2 and zero.
3. Understand the XLSTART folder
XLSTART is a system generated folder that holds specific files that Excel can use. It is located on the C drive of a PC, but its precise location depends on your version of Excel.
XLSTART can be used to save files, templates and file shortcuts. Any Excel files or file shortcuts saved to XLSTART will open automatically when Excel starts.
It is typically better to save a file shortcut to this folder rather than the file itself.
If you have files in the XLSTART folder, it is a good idea to copy these files to another, backed-up folder location, because the C drive may not be backed up.
Installing an updated version of Excel may clear the XLSTART folder or create a copy based on the version. This is another good reason to keep separate copies of the files you have in the folder.
The most common file that is saved in the XLSTART folder is the Personal Macro Workbook, called PERSONAL.XLSB. The Personal Macro Workbook is a special file that Excel creates when you record your first macro. It doesn’t contain any personal information, only your personal macros.
This file opens each time Excel starts, so all your personal macros are always available. The Personal Macro Workbook is also a hidden file, so when Excel is opened, the file is invisible, even though it is still open and available in the background.
Excel allows you to create templates for a new workbook and a new worksheet. Save these two templates into the XLSTART folder so that Excel can find them. Figure 4 shows a template for a new workbook called Book.xltx. The file name to use for a sheet template is Sheet.xltx.
4. Follow a link by double clicking on the cell
The functionality of double clicking a cell to follow the link is turned on when you turn off another setting, which can make it hard to find.
Turn off the functionality to edit directly in the cell and that allows double clicking to follow a formula link.
To enable the double-click shortcut:
- Click the File ribbon tab.
- Click Options on the bottom left.
- Click Advanced on the left.
- Untick Allow editing directly in cells – see Figure 5.
- Click OK.
A keyboard shortcut offers the same link functionality. Select the cell and press Ctrl + [ – see Figure 6.
If you use either technique, there is a keyboard shortcut to return to the cell from where you followed the link. Press the F5 function key and then press Enter – see Figure 7.
