You are here

Home

09 - Fixing cells while copying formulas in Excel

This tutorial introduces how to copy formulas in Excel and how to fix cells while copying. 

Formulas are copied by pulling a small square in cell corner, or by double clicking it. The other ways how to copy formulas is using shortcuts CTRL + C and CTRL + V or shortcut CTRL + D or CTRL + R.

 how to copy formulas in Excel

If you are using formulas is essential to understand the logic how to fix cells in Excel while copying.  Cells are fixed by the dollar sign $ or by pressing F4.

how to fix cells in Excel

From the Print Screen we see that in the formula cells B2 to B7 are changing while cell $B$9 is fixed.

This kind of Excel  fixed cell is used when we have a fixed variable in the formula (constant).

For example, if we want to calculate the salary of employees for a week the formula would look following.

Employee 1: = B2 * $B$1

Employee 6: = B7 * $B$9

- cells B2 to B7 represent the amount of hours that the employees have worked and

- $B$9 represents the hour salary which is the same for each employee. 

 

$A5 - fixes column so if we copy a formula horizontally (to the right) the Column A in the formula remains unchanged whereas if we copy the formula downwards the line value in the formula is changing ($ A6, $ A7 ...)

 

A$5 - fixes row so if we copy the formula vertically (downwards) row 5 in the formula remains unchanged whereas if we copy the formula to the right the column value is changing (B $ 5, C $ 5 ..)

Partner sites:

kosime.sk
Kosenie trávy, čistenie pozemkov a záhradnícke práce