In Excel, cell references are relative by default, changing when formulas are copied to other cells. To copy a formula without changing its cell references, convert it to an absolute reference either by pressing F4 or adding a dollar sign ($) before the row and column numbers. When needing to copy multiple formulas with unchanged references, a specialized method is required. In this article, we will introduce three methods for changing relative reference to absolute reference in Excel.

Change a formula from relative reference to absolute reference by pressing F4

Change multiple formulas’ cell references from relative to absolute with one click by using a handy tool

Change a formula from relative reference to absolute reference by manually adding dollar sign ($)

Change a formula from relative reference to absolute reference by pressing F4

Sorprendente! Utilizzo di schede efficienti in Excel come Chrome, Edge, Firefox e Safari!
Risparmia il 50% del tuo tempo e riduci migliaia di clic del mouse ogni giorno!

Supponendo che la colonna C utilizzi la formula (= A1 * B1) to multiply column A by column B. When we copy the column C to other cells, the calculating results will change, because the formula (= A1 * B1) utilizza il riferimento di cella relativo. Se vogliamo copiare la colonna C senza modificare i riferimenti di cella delle formule, possiamo cambiare i riferimenti di cella da relativi ad assoluti prima di spostarli.

If we don't change the cell references from relative to absolute, after moving the column C to Column E, all values will be changed. See screenshot:

In this situation, we can press F4 to toggle relative references to absolute references. Please do as follows:

1. Put the cursor behind A1 in the formula bar, press F4 una volta, diventerà $ A $ 1 Vedi screenshot:

2. Then put the cursor behind B1in the formula bar, press F4 una volta, diventerà $ B $ 1. Vedi screenshot:

After changing the formula’s cell references to absolute address, we can copy the formula and move to other cell without changing cell reference. And get the value unchanged.

Change multiple formulas’ cell references from relative to absolute with one click by using a handy tool

The above method is easy to change one or two formulas’ cell references, but if there are a range of formulas need to be changed, it may be troublesome and time-consuming. Kutools for Excel può aiutarti a modificare i riferimenti di cella di una formula di intervallo in modo rapido e comodo.

Dopo l'installazione Kutools for Excel, you can change relative reference to absolute reference quickly by following the next steps.

1. Go to select the range that contains formulas you want to change cell references in worksheet.

2. Clic Kutools > Più> Converti riferimenti nel Formula group. In the Converti i riferimenti di formula dialogo, scegliere All'assoluto opzione da convertire. E poi clicca OK.

It will convert cell references in formulas to absolute references. Now, we can copy and move the formula to any cells in current worksheet without changing cell references in formulas.

Change a formula from relative reference to absolute reference by manually adding dollar sign ($)

Fare clic sulla cella della formula che si desidera modificare, quindi aggiungere $ segno di dollaro prima di riga e colonna. Quindi tocca Entere i riferimenti di cella nelle formule verranno modificati da riferimento relativo a riferimento assoluto.

1. Put the cursor before A in A2 in the formula bar, and add $ dollar sign.

2. Put the cursor before 2 in A2 in the formula bar, and add $ dollar sign.

3. Repeat above steps to add dollar sign ($) before row and column to change the formula’s cell references to absolute address.

After changing the formula’s cell references to absolute address, we can copy the formula and move to other cell without changing cell reference. And get the value unchanged.

Articoli correlati