4.8: Copying and Pasting Formulas

Last updated
Save as PDF

Page ID: 14313

\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

Data in Excel can be cut, copied and pasted using similar shortcut menu options, keyboard equivalents, and ribbon icon commands as seen in Microsoft Word. Following operating system file management principles, moving data is synonymous with using the “Cut” and “Paste” commands, while duplicating data is the same as using the “Copy” and “Paste” commands. Other terminology that should be recognized is “source”, which is the cell or range to be moved or duplicated, and “destination”, which is the upper-left cell in a worksheet where the data is to be pasted.

Copying and pasting formulas contain cell references is a very powerful feature, and to expedite this process, Excel has a nifty feature called AutoFill. The AutoFill procedure utilizes the fill handle, which is the solid, green square in the lower-right corner of the active cell or cell range. Clicking and dragging the fill handle to adjacent cells (horizontally or vertically) will copy and paste the contents and update the destination cells with relative cell references or sequential data aligned with built-in Custom Lists. This should save significant time, and likely improve accuracy, and AutoFill has many uses.

Not only can AutoFill copy formulas, but it also be used to complete lists that have a recognizable pattern. It is helpful to select the source cells that demonstrate a pattern, then hover the mouse pointer over fill handle. The mouse pointer will change from the default pointer icon to a thin, black plus sign. The patterns can be numbers, dates, months, days of the week, etc. If AutoFill incorrectly guesses the series pattern, the AutoFill Options icon is available to change the paste actions. In the screenshots below, the fill handle in cell D5 will by copied to cells D6:D13. The results shown in the second screenshot will update the formula from D5 with relative cell references to rows 6:13. What is happening in cells D7:D8?

Practice 5: Test Scores

In a new Excel workbook, enter the above data.
Make sure to apply bold formatting to row 1, and text wrapping to cell E1.
Format the data as a table with a header row and banded rows, but no Filter buttons.
Apply the Facet theme.
Ensure that cell E1 has the text wrapping format applied and then resize the column width of column E to be 7 points wide and the height of row 1 to 36 points.
AutoFit columns B, C & D.
In cell E2, enter the following formula: =B2+C2+D2 and click Enter. Your worksheet should look like the screenshot at right. Notice that Excel automatically filled in formulas for the rest of column E cells using relative cell references! If this action is unwanted, click the lightning icon, and choose to Undo the Calculated Column.

Press F12 to invoke the Save As command and save the file as Test Scores.xlsx.