The F4 key in Excel is a shortcut for changing how cell references are applied in formulas. Here's how it works:

Default - Relative References:

By default, Excel uses relative references. This means that when you copy a formula containing a cell reference, the reference will adjust based on its new position.

For example, if you have the formula `=A1+B1` in cell C2, and you copy it down to cell C3, the formula will automatically change to `=A2+B2`. This is because the reference is relative to its original location (one cell down in both row and column).

Fixing References with F4:

The F4 key allows you to convert this relative reference to an absolute reference, which locks the cell location in the formula regardless of where it's copied.

Here's how to use F4:

1. Enter your formula and include the cell reference you want to fix.
2. Press F4 once. This will add dollar signs ($) before both the column letter and row number, making it an absolute reference (e.g., `=($A$1+B1)`).
3. Press F4 again (or keep pressing) to cycle through different reference options:
- Absolute column, relative row (e.g., =$A1+B2): Locks the column but allows the row to adjust.
- Relative column, absolute row (e.g., =A$1+B2): Locks the row but allows the column to adjust.
- Back to relative reference (e.g., =A1+B2): Removes the dollar signs.

When to Use Absolute vs. Relative References:

- Use absolute references when you want the formula to always refer to a specific cell, regardless of where it's copied. This is common for referencing fixed values or specific cells in calculations.
- Use relative references when you want the formula to adjust based on its new position. This is useful when creating patterns or calculations that need to change along with the copied data.

By understanding F4 and the different reference types, you can ensure your formulas work as intended when copied or moved within your spreadsheet.