In Microsoft Excel, a cell reference is how you refer to the cell address or location. When you press F4 fourth time, you will back to initial setting, so it will put $ before column and row again.Exploring Options for Microsoft Excel Cell References Triple press of F4 – $A1 – adds $ only before column letter. One press of F4 – $A$1 – adds $ before column and row ĭouble press F4 – A$1 – adds $ only before row number
You can speed up your work and use F4 key except of manual input of $ before row number/column letter. Then, if we drag formula right again, we can see that it still takes the value from row 2 accordingly for columns E and F: If we create mixed reference blocking only row number – D$2 – formula will calculate value entered in row 2 for current column: We can notice similar example while dragging down formulas. To avoid such situation we need to use mixed reference to block column B, so address our cell as $B4:Īfter that change, even if we drag formula right, its first value will be always taken from cell in column B for corresponding row. As you can see, when we use relative reference (without $), formula moved right from D4 to F4 is not working properly:
We have to calculate what is the price of some products after getting 10%, 15% or 20% discount. Thanks to them there is no need to change manually formula’s parameters after changing its location in the spreadsheet. Mixed references are commonly used when we want to copy formulas between various rows and columns within Excel spreadsheet. Mixed reference consists of relative and absolute references elements and it is made by entering $ sign only before column letter or only before row number. When we drag down formula from C4 to C15, it multiplies value from cell B1 by value entered into corresponding cell in column B for current row: Mixed reference Thanks to absolute reference, Excel receives an information that it should use specific cells, regardless of where formula will be copied or entered.Ībsolute references are created by adding $ sign before column letter or row number:Īs I mentioned earlier, when we use absolute reference we can drag down or copy formulas in every place in spreadsheet without the need for manual change of formula’s elements. These type of reference are used when Excel spreadsheet contains some fixed values, such as interest rate or currency exchange rate. Absolute referenceĪbsolute reference is opposite to relative one and it allows to change formula location to other cell without changing its address. If you use relative reference, address of formula will be changed when you move formula to other cell:Īfter dragging down formula from D3 to D8 you can notice, that in each row formula changed address: B3 and C3 has been changed to B4 and C4 in formula in row D4 etc. Example:Īs you can see, cell D3 points to cells B3 and C3 – these two are relative references. Relative reference is basic and most popular cell reference type used in Excel. There are three types of cell references in Excel: relative, absolute and mixed.
To make more accurate and complex calculations you need to understand, what are cell references in Excel. This reference allows Excel automatically refresh formula results. Many formulas used in Excel contain references to other cells.