THE MAGAZINE FOR COMPUTER APPLICATIONS
Circuit Cellar Online offers articles illustrating creative solutions
and unique applications through complete projects, practical
tutorials, and useful design techniques.

DO YOU EXCEL IN ELECTRONICS?

Part 1: Learning the Basics of Excel
by Aubrey Kagan

Start ı Data Acquisition and Analysis ı Autofill ı Copying Formulas ı Relative and Absolute ı Naming Cells ı Determining the Cost ı Sorting ı Using Look Up ı Figuring the Total ı Sources and PDF

RELATIVE AND ABSOLUTE

Now, 0.1 ı 10 doesnıt equal 0, so letıs investigate. When cell B6 is highlighted, the formula in the formula bar will read =A6*B2. When you copy in Excel, references to cells are updated in the direction of travel. Copying two columns across and three rows down will increment the cell references in the same proportion. This is great in many applications, but can introduce a problem such as in Photo 6 because the B2 cell has no relevant data.

You want the multiplication to be B6 ı B1. To date, Iıve used relative referencing of cells. If you donıt want transposition of the cell references to occur, you have to use absolute referencing. To denote the absolute reference, mark a reference to a cell with a dollar sign ($). Itıs simple, just remember that money can buy you anything! If you donıt want transposition across columns, precede the column letter with $. Similarly, to prevent transposition across rows, precede the row number with $.

To demonstrate, click the cursor in cell C5, edit the formula bar to read =A5 * $B$1, and hit Enter. The contents of cell B5 will remain as zero. Click in cell B5 again (there is a way to decide what direction the cursor will move by hitting Enter, see Tools/Options/Edit) and copy. Click in B6 and drag to B15 (as seen in Photo 7) and paste to get the results you see in Photo 8.

These are results that are expected. Clicking on any cell between B5 and B15 will indicate a product of the cell to the left of it and cell B1.

PREVIOUS • NEXT