If you have an input sheet in Excel where there are multiple input ranges, then a hyperlink may offer a quick way to select all the input ranges in one step.
You first need to create a range name for the input range(s).
The input range doesn’t have to be a single range; it can be separate ranges throughout the sheet.
This works on a sheet-by-sheet basis; you need a separate range name for each sheet.
You can select multiple ranges by using the Ctrl key.
Start by selecting the first range normally. Then hold the Ctrl key down to select extra ranges with the mouse.
If you make a mistake as you are choosing the multiple ranges, you will need to start again.
Once you have selected the input range(s), click inside the Name Box (left side of screen above the column letters – see Figure 1) and type a name (I’ll use the name Input) and then press Enter.
You must press Enter for the name to be retained. See Figure 2
Type a description into a cell. I’ll enter Input Range in cell A1.
Right click the cell and choose Hyperlink (Ctrl + k also works).
Click the Place in This Document button on the left.
The name you created should be displayed under the Defined Names section. See Figure 3
Click the name and then click OK.
Clicking on the hyperlink cell will activate the input range.
Pressing the Delete key will then clear the range in one step. See Figure 4
Clicking another cell will de-select the input range and then the range is ready for input.
You can undo the deletion using Ctrl + z if necessary.
The Name Box has a drop down. See Figure 5
You can also use that to select the Input range name from any sheet in the file
If you need to modify or delete the range name click the Formulas Ribbon tab and then click the Name Manager. This allows you to edit or delete range names.
Ctrl + F3 also opens the Name Manager dialog. See Figure 6
Neale Blackwood CPA is CPA Australia’s resident Excel expert. He runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected]