Free Newsletter
If you would like to receive our weekly newsletter just send
an email to
Mary Hanson at:
ComputerTutorTeam@gmail.com.
There are no fancy sign-up forms – just mention newsletter in the subject line of your email.
We've been sending out this PC Help Newsletter since 1994, and it contains no advertising of any kind – just useful computer tips.
Furthermore, your email address is safe with us – we don't share it with anyone under any conditions whatsoesver.
PLEASE NOTICE:
The information in this article applies to various spreadsheets, such as Excel, Quattro Pro, Lotus 123, OpenOffice Calc, and the spreadsheets that come with Google Docs and Microsoft Works.
Top of Page
If you would like to receive our weekly newsletter just send
an email to
Mary Hanson at:
ComputerTutorTeam@gmail.com.
There are no fancy sign-up forms – just mention newsletter in the subject line of your email.
We've been sending out this PC Help Newsletter since 1994, and it contains no advertising of any kind – just useful computer tips.
Furthermore, your email address is safe with us – we don't share it with anyone under any conditions whatsoesver.
PLEASE NOTICE:
The information in this article applies to various spreadsheets, such as Excel, Quattro Pro, Lotus 123, OpenOffice Calc, and the spreadsheets that come with Google Docs and Microsoft Works.
Top of Page
|
Replicating a Formula in a Spreadsheet
My article on Spreadsheets-101 (Spreadsheet Basics) generated a number of questions regarding the use of the "little black square" in the lower right corner of an active cell. Well, dragging this square across a number of other cells will fill them with some type of incremental data, based on the contents of the originating cell. The names of months are a typical example. Type January into any cell, press ENTER, and drag the black square to the right. Each dragged-over cell will be filled in with the name of a month incrementally from January to wherever you stop. If you do the same after typing Jan into the beginning cell, all the others will display similarly abbreviated names, such as Feb and Mar. If you begin with a month other than January the cells will still display the sequentially appropriate names. The same incrementation will apply if you drag the cursor downward. If you drag it upward or to the left, however, the names will appear in reverse order. All the above also applies to names of the week. Replicating Numbers Regarding numbers, if you type a figure into a cell and drag the little square, the number will simply be repeated from one cell to the next. However, if you precede the number with a word (as in Space 100) the target cells will display Space 101, Space 102, Space 103, etc. If you would like the incrementation to display only even numbers (as in Space 100, Space 102, etc.) do this: Type Space 100 into any cell and Space 102 into the next cell. Then, mouse-select both cells, grab the black square in the right-hand cell, and drag it to the right. If you want a series of cells to display, say, every fifth number, type 5 into a cell, followed by 10 into the cell to its right. Select the two cells and do as above to display 5, 10, 15, etc. Well, these exercises are fun, but the main purpose of dragging the little black square is to replicate formulas for solving various math problems. Replicating Formulas Let's say you have a week's daily expenses to total. Begin a new spreadsheet and type Sun into A1 and use the black square trick to fill in the rest of the week. Now type four random "expense" amounts into the cells under each day (A2 through G5) with Sunday's expenses itemized in A2 through A5. I used small, simple numbers in the illustration below to make these instructions easy to follow. Once you understand the procedure, you'll find that calculating large, complex numbers is just as easy. Notice also the Information Bar that displays the currently active cell (A6) and the formula that produced the total in A6. We find Sunday's total in A6 by entering the formula =SUM(A3:A5) (or by clicking the Sigma symbol Σ in the the toolbar and then pressing ENTER). Then we grab the black square in A6 and drag it across B6 through G6 to display the rest of the week's daily totals. Here's how it works: Dragging the black square replicates A6's formula into each cell to its right, but increases the column designation by a factor of one each time (i.e.: A becomes B, B becomes C, C becomes D, etc.). Thus =SUM(A3:A5) (Sunday's formula) becomes =SUM(B3:B5) for Monday's formula, =SUM(C3:C5) for Tuesday's total, =SUM(D3:D5) for Wednesday's, and so on for the rest of the week. More about these functions soon. In the meantime, experiment – it's fun. |