WORKPLACE SKILLS · EXCEL PRACTICE

Excel Cell References: A Practical Guide to Copying Formulas

A formula can work in one cell and give the wrong result when copied. Understanding which parts of a reference move helps you spot the problem before it spreads through a worksheet.

Start with one simple formula

Imagine a small table of prices. Cell A3 contains 50 and B1 contains a tax rate of 0.2. You want B3 to calculate the tax amount. Enter =A3*$B$1. The result should be 10.

Now put 80 in A4 and 120 in A5. Copy the formula from B3 down to B5. The price reference should change for each row, while the tax-rate reference should stay in B1. The resulting tax amounts should be 16 and 24.

Read the dollar signs

A relative reference such as A3 can change when copied. An absolute reference such as $A$3 fixes both the column and row. Mixed references fix one part: $A3 fixes the column, while A$3 fixes the row. Microsoft explains these reference types in its official cell-reference guide.

Predict the copied formula before checking it

Take the formula in B3 and predict what will appear in B4. Write your prediction down, then inspect the formula in Excel. Check the formula itself as well as the displayed number. A result that looks reasonable can still come from the wrong cell.

For another exercise, copy references one cell to the right. Predict how A3, $A$3, A$3 and $A3 will change. Check each one in a blank part of the workbook. This small exercise lets you isolate the reference behaviour without a large table getting in the way.

Use a change in input to check the logic

Change B1 from 0.2 to 0.1. The three tax amounts should now be 5, 8 and 12. If only one row changes correctly, inspect the other formulas. If a copied formula points at an empty cell or a price instead of the rate, the reference may have moved when you intended it to stay fixed.

Next, change one price. The result in its row should update without changing the other prices. These checks answer different questions: does the shared rate update every row, and does each row use its own price?

Keep the practice task small

You do not need a complex financial model to practise this skill. A three-row table is enough to make your prediction visible. Use fictional values, label the inputs clearly and keep a copy of the starting version.

After the exercise, explain the formula in one sentence: “Use the price in this row, multiplied by the rate in B1.” If you can connect that sentence with the moving and fixed references, you have a concrete way to check the formula again later.

Take the next step

Try a small sales table with quantity and unit price in separate columns. Decide which inputs are different in each row and which are shared. Build one formula, copy it and test a change in the shared input. Keep the focus on the reasoning rather than speed.

Original practice examples by The Job Tests, operated by Sasflynet Kft. Microsoft Excel is a third-party product; no affiliation or endorsement is implied.