In Excel complex formulas can include several operations. Use complex formulas in Excel to calculate more extensive data.
In order to maintain accurate formulas, it is necessary to understand how cell references respond when you copy or fill them to new cells in the worksheet.
Excel will interpret cell references as either relative or absolute. By default, cell references are relative references. When copied or filled, they change based on the relative position of rows and columns. If you copy a formula (=A1+B1) into row 2, the formula will change to become (=A2+B2).
Absolute references, on the other hand, do not change when they are copied or filled and are used when you want the values to stay the same.
Relative references can save you time when you're repeating the same type of calculation across multiple rows or columns.
In the following example, we're creating a formula with cell references in row 4 to calculate the total cost of the electric bill and water bill for each month (B4=B2+B3). For the upcoming months, we want to use the same formula with relative references (C2+C3, D2+D3, E2+E3, etc.). For convenience, we can copy the formula in B4 into the rest of row 4, and Excel will calculate the value of the bills for these months using relative references.