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.