Use Excel Auto-fill for Formulas and More!
Problem: You have a standard salary multiplier for overtime hours that you want to calculate the same way every time. The formula you generate needs to appear along an entire row of data, and across several worksheets, but you don’t want to type it out each time.
Solution: Copy your formula to multiple cells instead of re-typing it. As long as you are using “relative references” (coordinates without “$” markers in them), Excel will adjust the formula each time it copies to apply to the associated data. For example, copying a formula that multiplies cell A1 by B1 to produce a value in C1 can be copied to C2 through C15, and Excel replaces A1 and B1 with A2 and B2 for row 2, A3 and B3 for row 3, etc.
Step-by-Step: Copy a Formula Using the Fill Handle
Use the Fill Handle to copy a formula to several adjacent cells.
- Select the cell that contains the formula you want to copy.
- Hover your cursor around the black square in the lower right corner of the cell until your cursor turns into a plus sign.
- Click and hold the left mouse button while dragging the handle to include all cellswhere you would like the formula copied.
- Release the mouse button to populate the cells with the formula.
Hot Tip: Copy the formula to non-adjacent cells by selecting the cell that contains the formula then right-click to select Copy (or Ctrl+C), then select the destination cells and right-click to choose Paste (or Ctrl+V).
Bright Idea: There may be times when you do not want Excel to adjust all or part of the formula for you. For example, if you have a table of standard multipliers, you might want the same cell to always be used to multiply. You can create an “absolute reference” by inserting a “$” sign to the left of the part of the cell coordinate that you want to stay the same. If you always want to use cell C2, you would write it as “$C$2”.
