As an Excel user, you undoubtedly know that you can use the $ sign to block a row or column in a formula. This creates an ‘absolute address’. With an absolute address, you can execute a calculation for an entire column without losing the constant coefficient. To do this, you need to block the address of the cell containing the calculation.
In the example below, I’m going to multiply the values for the cities in the table by 1.1. This factor is in a fixed cell, C3.
You can use the fill handle to apply the formula to an entire column. To make sure you retain the constant factor 1.1, you’ll need to block C3 with the $ sign This results in ‘Solution 1’ in the screenshot below. The formula is as follows: =B7*$C$3
Excel offers another method to block a cell or a cell range: by naming the cell(s). This leads to ‘Solution 2’:
- First, you name the cell that contains the coefficient (C3 = Factor) and the cells containing the values that need to be multiplied (B7:B10 = Values).
- Select the cell(s), then click the name ‘Factor’ or ‘Values’ in the name box.
- Now you can enter your formula as follows: = Values*Factor
Easy, isn’t it?