Skip to content
Reading settings
ExcelBeginner~20 min practiceLesson 8 of 34

Copy formulas and paste values

Fill line totals down and make a fixed upload copy.

Content reviewed · How we check lessons

Loading your progress…

In this lesson

Copy row formulas, keep a shared rate fixed and compare a live workbook with pasted values.

Example

An editable order and a fixed upload

Enter Keyboard 2 at 30, Monitor 1 at 180 and Dock 3 at 40. The buyer needs formulas for later edits; the upload system needs fixed line totals. Make a separate values-only sheet for the upload.

How it works

When you copy a formula, relative references move with it. Use an absolute reference for a shared rate that should stay in the same cell.

Paste Values copies the current result. Those numbers will not update when the source changes, so keep the formula workbook for the next revision.

Steps

  1. Enter Item, Quantity, UnitPrice and Total in A1:D1, with the three records in rows 2–4. Save the workbook before trying paste options.

  2. Enter the formula below in D2. Select D2:D4 and use Fill Down or the fill handle to calculate the other lines.

    =B2*C2

    English function names. Formula settings →

  3. Read D3 and D4 in the formula bar: they should use B3*C3 and B4*C4. Check line totals of 60, 180 and 120, then calculate grand total 360 outside the records.

  4. Add an Upload sheet. Copy A1:D4 and paste at Upload!A1 with Paste Special > Values or Paste > Values. Select Upload!D2: the formula bar should show 60 rather than a formula.

  5. Change the source Keyboard quantity to 3. Its line total becomes 90 and the grand total becomes 390. Check that Upload still contains the earlier 60 and totals 360.

  6. Make a new upload snapshot in another sheet or area. Add a date or revision to its name and note which source version it came from.

Try it yourself

  1. Put a delivery rate 0.05 in F2. In E1 type DeliveryFee and enter =D2*$F$2 in E2, then fill it down and inspect all references.
  2. Copy the current source line totals to a new value-only area and calculate its grand total. Document that subsequent source edits will not update those pasted numbers automatically.
Show expected result
  • Initial line totals are 60, 180 and 120, totaling 360. After the keyboard change they are 90, 180 and 120, totaling 390.
  • Delivery fees after that change are 4.5, 9 and 6, totaling 19.5; the shared rate reference remains $F$2 in every row.

If something goes wrong

Every copied formula returns the first line total.
Read the copied references. B2 and C2 need to become B3 and C3 on the next row; reserve absolute references for the shared parameter.
The upload total stays the same after a source edit.
That sheet contains pasted values. Create a new snapshot from the revised source and label it with the new revision.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

1. What is the initial grand total for the three line calculations?
2. Which paste choice creates a result snapshot without formulas?
3. Which reference keeps the delivery rate fixed when filling down?

Choose an answer for all 3 questions to check your understanding.

Optional: try it with Copilot

If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.

Explain which references should move as these formulas fill down and which should stay fixed. Suggest clear names for the source and upload copies.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

Loading your progress…