Excel Guides

Relative Worksheet References When Copying in Excel

When copying a worksheet in Excel, any relative references in the worksheet are automatically updated to reflect the new location of the copied worksheet. This can be useful if you want to create a new worksheet that is based on an existing one, but with some changes. For example, you could copy a worksheet that contains formulas that reference other cells in the same worksheet, and the formulas would automatically update to reference the cells in the new worksheet.

However, there may be times when you want to keep the original cell references when copying a worksheet. For example, you may have a workbook with multiple sheets, and you want to copy one of the sheets to another workbook while keeping all of the cell references intact. Or, you may want to copy a sheet within the same workbook, but keep the cell references pointing to the original sheet. In these cases, you can use absolute cell references instead of relative ones.

An absolute cell reference is a cell address that does not change when the formula is copied to another cell. Absolute references are indicated by adding a dollar sign ($) before both the column letter and row number in the cell address. For example, if you wanted to reference cell A1 in an absolute manner, you would enter $A$1 in the formula.

You can also use mixed references, which are a combination of absolute and relative references. Mixed references are indicated by adding a dollar sign ($) before either the column letter or row number (but not both) in the cell address. For example, if you wanted to reference cell A1 in a mixed manner, you would enter $A1 in the formula. This would make the column absolute (meaning it would not change when copied) but leave the row as relative (meaning it would update when copied).

To use an absolute or mixed reference in a formula, simply enter the appropriate cell address with the dollar signs in front of it. When copying the formula to another cell, Excel will automatically adjust any relative references as needed, but leave any absolute or mixed references unchanged.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.