Sales: 0208 732 5656

Excel top tip: The $ sign for formulas

Excel top tip: The $ sign for formulas

Excel uses some basic intelligence when copying and pasting formulas or dragging formulas down a row or across a column. By default the cell values adjust according to the difference between original cell and new cell where the formula has been copied to.

For example if your formula in cell C2 is “=A2+B2” – copying this formula to D3 would change the formula to “=B3+C3”. In many cases exactly what you want – however there will be occasions this is not what you need as certain cells will always be part of your formula.

Using the $ sign in your formula allows you to keep cell references static when copying to other cells.

A formula in cell C2 of “=$A2+B$2” would ensure the A and the 2 in B2 remain unchanged and would create a formula of “=$A3+C$2” when copied to cell D3.

Using the $ sign does make the formula harder to read, however will make those more complicated spreadsheets much less time consuming when you need to make additions.


No Comments

Post a Comment