7 Introduction To Absolute References

Using Absolute Cell References 1. Open a new spreadsheet in Excel 2. Type in the following table.

3. Make the text in A1 bold. In Excel if you copy a formula Excel automatically changes it for you If we type =C4*D3 in cell D4 and the copy that into D5 it will automatically change it to =C5*D4. This is called relative cell referencing. However we want the reference in the formula to D3 (10) to stay constant. Excel solves this problem by using the [dollar] symbol $ to indicate when a particular cell reference is to remain fixed ("absolute") when copied. The column, or the row, or both can be fixed by putting $ in front of the column letter, or the row number, or both. e.g. $D7 means that when copied the column reference will always be D, wherever we copy it to, but the row will change if we copy into cells up or down from the original row. Similarly, G$3 (for example) will always copy as row 3) and $C$5 will always copy as column C row 5. 4. In cell D4 enter the formula =C4*$D$3. A quick way to do this is to type in the equals sign = then click on cell C4, type in the * and click in cell D3, then press the F4 key on the keyboard. This will put in the dollar signs in for you. 5. Replicate this formula into cells D5 to D9. Note that in D5 the formula will become =C5*$D$3. Excel has changed the first part of the formula but not the second. 6. In cell E4 enter the formula =D4*$E$2 remember that you can always click on the cells rather than typing in the references. 7. Replicate this formula into cells E5 to E9. 8. In cell F4 enter the formula =C4*$F$3. 9. Replicate this formula into cells F5 to F9.

1

10. In cell G4 enter the formula =F4*$G$2 11. Replicate this formula into cells G5 to G9. 12. Format the cells C4 to G9 to currency with 2 decimal places. Your spreadsheet should now look like this

13. Add your name, the date and the filename in a footer 14. Save this as Inkjet 1. 15. Print out a copy of your work. 16. Set the spreadsheet to print formulas, row numbers, and column letters. 17. Save this as Inkjet 2. 18. Make sure all data will fit onto one sheet and print out one copy. Close the file. 19. Open Inkjet 1. 20. Insert 30 in H3 and 40 in J3. 21. Insert 6% in I2 and 8% in K2

22. Enter appropriate formulas for 30 and 40 and the percentages 23. Save this as Inkjet 3. 24. Print out a copy of your work.

2