Excel sheet | Excel copy formula with changing cell reference

 To copy a formula in Excel and have cell references change as expected when copied to other cells, use the fill handle or the paste special feature. The default behavior of Excel is to adjust relative cell references when formulas are copied. 

Using the Fill Handle:

1. Enter your formula: Type the formula into the cell where you want to start.
2. Select the cell: Click the cell containing the formula.
3. Drag the fill handle: Locate the small square (fill handle) at the bottom right corner of the cell. Drag it across the cells where you want to copy the formula. As you drag, Excel will automatically adjust the cell references in each copied formula.
4. Release the mouse: Release the mouse button to complete the copy. 

Using Paste Special:

1. Copy the formula: Select the cell with the formula, then click "Copy" on the Home tab.
2. Select the destination cells: Click the range of cells where you want to paste the formula.
3. Paste special: Go to the Home tab and choose "Paste." Click "Paste Special."
4. Choose "Formulas": In the Paste Special dialog box, select "Formulas" under the "Paste" section and then click "OK." 

Understanding Relative and Absolute Cell References:

• Relative references:

  These are the default in Excel and adjust when a formula is copied. If you copy =B4*C4 from D4 to D5, the formula in D5 becomes =B5*C5. 
• Absolute references:

  Use $ signs to fix the row or column (e.g., $B$4). These references remain constant when copied. 

Example:

If you have the formula =B1*C1 in A1, and you drag the fill handle down to A2, the formula in A2 will become =B2*C2. This demonstrates how Excel automatically adjusts relative cell references.