When you use range names in a formula, the cell referencesinsert as absolute references. This is great when you require a constantvalue. Let’s see how we insert range names into a formula, and let’sinvestigate the keyboard shortcut to insert a range name, asfollows:
- Open the RangeName.xlsx workbook.
- We will be creating a formula to calculate the VAT amount on thetotal sales of each sales rep. The VAT % is entered into cell K6. Inorder to use this cell in the formula to work out the VAT amount, wewould need to make the cell absolute (fixed) in the formula so that eachitem is multiplied by the VAT % in cell K6. We would not use the actualvalue in the formula but the cell reference—or, in this case, the rangename, as the % VAT could change. This would mean that we would need toreconstruct the formula every time. Therefore, to save time, the bestthing would be to name the range that contains the VAT %.
- Make sure you click on cell K6.
- Click in the Name Box area and type the word
VAT to name the range K6.
- Press Enter on the keyboard when done.
- Alternatively, use one of the other methods mentioned previously toname the range.
- Now that the cell has a name, we can use it in our formula. Beforewe do this, we will work out the total for each of the sales reps. Dothis before moving on to the next step.
- Click in cell K8 to construct the formula. Type the following:
- Type the range name into the formula directly after the
* sign (if you have forgotten the range namefor cell K6, then press F3 on your keyboard to obtain a list ofall the name ranges for this workbook, then select VAT and press Enter), asillustrated in the following screenshot:
- Press Enter to confirm the formula.
AutoFill to cells K9:K11.
You now have the skills to work with range names in formulas, whichwill save you a huge amount of time and keep your formulas error-freewhen dealing with absolute references.