当您复制包含公式的单元格时，您会注意到公式中的单元格引用也会上下移动相应数量的单元格。这种类型的单元格引用称为相对引用。
当我们复制（Ctrl + C）上图中的公式，并将其粘贴（Ctrl + V）到另一个单元格时，您会注意到单元格引用从B列变为D列，因此总数不同.
如果要防止Excel在复制单元格时更改单元格引用，则需要使用绝对引用。要创建绝对引用，请在要冻结的公式中单元格引用的两个部分之前插入一个美元符号 ( $
存在三种不同类型的引用，相对引用、绝对引用和混合引用。以下是一些示例：
- 相对引用：A1 告诉Excel将单元格引用更改为相应的列和行。
- 混合引用：$A1 告诉Excel你总是想引用 A 列。
- 混合引用：B$1 告诉Excel你总是想引用第 1 行。
- 绝对引用：$B$1 告诉Excel你总是想引用单元格 B1。
当您在公式中选择或输入单元格引用时，有一种输入美元符号的速记方法。在键入公式并完成单元格引用时，按 F4 在 4 种引用类型组合之间切换。假设您开始输入公式并输入=100*B1。
- 按F4，您的公式变为=100*$B$1（始终参考单元格 B1）
- 再次按F4，您的公式变为=100*B$1（始终参考第 1 行）
- 再次按F4，您的公式变为=100*$B1（始终参考 B 列）
- 再次按F4，您的公式将返回原始相对引用=100*B1（始终更改对相应列和行的引用）
在公式中输入每个单元格引用时，您可以暂停按F4，直到获得当前单元格引用的正确引用类型。
要复制使用绝对引用输入的公式并保留单元格引用，请选择包含公式的单元格并将其复制 ( Ctrl + C )，然后单击要将公式粘贴到的目标单元格。
确保主页(Home)选项卡是功能区上的活动选项卡。单击“(Click)主页”(Home)选项卡的“粘贴(Paste)”部分中的“粘贴(Paste)”按钮上的箭头。从下拉菜单中选择公式。(Formulas)
您会注意到总计显示在目标单元格中的原始单元格中，并且在目标单元格的公式栏中(Formula Bar)显示的公式包含与原始单元格中的原始公式相同的绝对引用。
注意：(NOTE:)将公式复制并粘贴到目标单元格并不意味着公式在原始单元格中更新时也会在目标单元格中更新。
有一种方法可以将包含公式的单元格复制并粘贴到目标单元格，这样原始单元格中的公式结果在更改时始终显示在目标单元格中。您可以链接到原始单元格。
为此，请再次选择并复制带有公式的原始单元格，然后单击要将链接粘贴到原始单元格的单元格。单击(Click)主页选项卡的(Home)粘贴(Paste)部分中的粘贴(Paste)按钮以显示下拉菜单。从菜单中选择粘贴链接。(Paste Link)
您会再次注意到，原始单元格的总数显示在目标单元格中。但是，这一次，公式栏(Formula Bar)显示对包含公式的原始单元格的绝对引用。每次原始单元格中的结果发生变化时，目标单元格中显示的总数也会更新。
注意：(NOTE:)如果您只想将公式的结果粘贴到目标单元格中，请在粘贴到目标单元格时从“粘贴(Paste)”下拉菜单中选择“粘贴值”。(Paste Values)
如果您想以特殊格式在更显眼的位置显示工作表的某个部分的总计（可能用于演示文稿），并且您希望保持包含链接的单元格更新，则粘贴链接是一项方便的功能。享受！
Preserve Cell References when Copying a Formula in Excel
When you copy a cell containing a formula, you will notiсe that the cell references in the formula also move a corresponding number of cells across and down. This tyрe of cell reference is called a relative reference.
When we copy (Ctrl + C) the formula in the image above, and paste (Ctrl + V) it into another cell, you’ll notice that the cell references change from the B column to the D column, so the total is different.
If you want to prevent Excel from changing the cell references when copying cells, you need to use absolute references. To create an absolute reference, insert a dollar sign ($) before both parts of the cell reference in the formula you want to freeze, as illustrated in the following image.
There are three different types of references, relative, absolute, and mixed. The following are some examples:
- Relative Reference: A1 tells Excel to change the cell references to the corresponding columns and rows.
- Mixed Reference: $A1 tells Excel you always want to refer to column A.
- Mixed Reference: B$1 tells Excel you always want to refer to row 1.
- Absolute Reference: $B$1 tells Excel you always want to refer to cell B1.
There is a shorthand method for entering the dollar signs as you select or enter cell references in a formula. As you are typing a formula and finish a cell reference, press F4 to toggle between the 4 combinations of reference types. Let’s say you started typing a formula and you typed =100*B1.
- Press F4 and your formula changes to =100*$B$1 (always refer to cell B1)
- Press F4 again and your formula changes to =100*B$1 (always refer to row 1)
- Press F4 again and your formula changes to =100*$B1 (always refer to column B)
- Press F4 again and your formula returns to the original relative reference =100*B1 (always change the reference to the corresponding columns and rows)
You can pause while entering each cell reference in the formula to press F4 until you get the right reference type for the current cell reference.
To copy the formula entered using absolute references and preserve the cell references, select the cell containing the formula and copy it (Ctrl + C) and click the destination cell into which you want to paste the formula.
Make sure the Home tab is the active tab on the ribbon. Click the arrow on the Paste button in the Paste section of the Home tab. Select Formulas from the drop-down menu.
You will notice that the total displays in the destination cell from the original cell, and the formula that displays in the Formula Bar for the destination cell contains the same absolute references as the original formula in the original cell.
NOTE: Copying and pasting a formula into a destination cell does not mean that the formula will be updated in the destination cell when it is updated in the original cell.
There is a way you can copy and paste a cell containing a formula to a destination cell such that the results of the formula in the original cell always display in the destination cell as they change. You can link to the original cell.
To do this, select and copy the original cell with the formula again and click the cell into which you want to paste the link to the original cell. Click the Paste button in the Paste section of the Home tab to display the drop-down menu. Select Paste Link from the menu.
You will notice that, again, the total from the original cell displays in the destination cell. However, this time, the Formula Bar displays an absolute reference to the original cell containing the formula. Every time the results change in the original cell, the total displayed in the destination cell updates as well.
NOTE: If all you want to do is paste the result of the formula into the destination cell, select Paste Values from the Paste drop-down menu when pasting into the destination cell.
Pasting a link is a handy feature if you want to display a total from one part of a worksheet in a more prominent location with special formatting, possibly for a presentation, and you want to keep the cell containing the link updated. Enjoy!
