Using the $ sign in excel formula to fix the cell reference

If you copy and paste a cell containing formula or drag the cell, the formula reference will also change and move accordingly. For example, if you want to calculate the amount of sales to each customer based on the number of units sold when the sales price is fixed, and the sales price is updated in one cell (B3), you can fix the cell reference of sales price (B3) by putting $ sign like ($B$3) and copy or drag the formula. You can also put the $ sign in formula cell reference by using function key F4.

Further, you can also fix only column or only row by using single $ sign like ($B3) or (B$3). In the same example, if you want to estimate the profit margin at different percentages, you can fix the amount column “C” by using ($C6) and fix the percentage row no 5 by using (E$5) and drag the formula to calculate for all customers and for all percentages.

This feather is very much helpful in this type of calculation and will save lot of time. You can also use the shortcut key F4 by pressing twice to fix the row and by pressing three times to fix the column on a cell reference.