How to copy and paste formulas in Excel without changing cell references
By
SpreadCheaters
You can watch a video tutorial here.
Excel is widely used for calculations and is very versatile when it comes to applying formulas. There are situations where we need to copy a formula to other cells but need to keep the cell in the formula constant, that is the cell reference should not change. An example of this is when we want to increase the price of a set of products by a fixed percentage. The reference to the cell containing the percentage has to remain constant while the other cell references can change accordingly.
Step 1 – Create the formula
– Type the formula in the destination cell
Step 2 – Make the cell reference constant
– Type dollar signs (‘$’) in front of the column name and the row number
– Alternatively, select the cell reference and press F4
Note: To keep only the column constant, type a dollar sign in front of the column name only e.g. $C2. Typing a dollar sign in front of the row number only keeps only the row constant e.g. C$2
Step 3 – Copy the formula to other cells
– Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
– Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)
– The cell C2 remains constant while the row number changes accordingly
