2.1 What is a spreadsheet?
It is a rectangular arrangement of boxes looking like a gigantic empty crossword puzzle.
Why bother with one?
You will see that you can do amazing things with them with very little effort. We’ll do some as illustrations. You should try doing them by yourself. When you are done, you will know enough about spreadsheets to use them productively to solve problems and check work. You can do anything doable on a graphing calculator, but you can see all the results and intermediate steps and correct anything any time.
How do you do things?
You enter things into the boxes. You can left click your mouse onto any box, and then type in your entry. (There are analogous ways to do this on a mobile device.) By the way, each box has a name given by its column letter (columns run from A to Z then AA to AZ then BA to BZ etc.; and a row number. Rows run from 1 to thousands.)
What can you enter?
Ordinary prose (or poetry): to do so just type it in.
Numbers: type them in.
Any function you have ever heard of and lots more with variable the content of some other box:
For example, typing =sin(A2) in B2 will put the sine of the number in A2 (given in radians) into box B2.
You must start by typing in an equal sign, then any function you know the name of, or can pick from the list of functions given by the spreadsheet. On my spreadsheet you can click on ‘formulas’ at the top of the page and see and choose the one you want. There are so many listed that you may get dizzy if you try to look at the list, but you will recover.
Of course you can also use parentheses and many functions to make your own complicated formulas using many different functions. For example =sqrt(sin(A2)*exp(A3)/(1+atan(A5)) will give the square root of the product of the sine of what is in A2 and (e to the power that is in A3) all divided by (1 plus the angle whose tangent is in A5, described in radians between \(-\pi\) and \(\pi)\).
OK but you can do all this on a calculator.
The best features come from what happens when you copy what is in one box (or a rectangle of boxes) elsewhere.
When what you have in box B2 refers to some other box, say A2, when you copy B2 somewhere else, the reference box moves with it. Thus if in B2 you have put =sin(A2), and you copy B2 into say, D2.
Then D2 will contain =sin(C2). Copying B2 into R7 will put =sin(Q7) there. If I remember my alphabet correctly, B comes right after A, D right after C, and R right after Q.
OK, how do I copy?
You click on the box you want to copy, press Ctrl and c at the same time, and the entry will enter "the clipboard". You may then move the cursor to where you want to copy, and press Ctrl and v at the same time. Try it and see. (by the way, if you have done something you didn’t want to do, then pressing Ctrl and z at the same time undoes it.)
Suppose I don’t want the reference to change when I copy something?
All you have to do is put a dollar sign (a $) in front of the index (letter or number or both) that you do not want to change. Thus, =sin($A2) will not change the column index which will stay A. Similarly =sin(A$2) anywhere will keep the reference in the second row, and putting dollar signs in front of both will keep the reference box A2 no matter where you copy it.
Even better, you can copy a whole rectangle, or copy a single box everywhere in a rectangle.
Suppose you want to copy the contents of box B2 into the rectangle with corners C5 and E100.
First you click on box B2 and press Ctrl and c at the same time.
The next step is selecting the target block of boxes. To do this you move the cursor to C5 do a left click, and hold down the shift key while you move the cursor to E100. Then do a left click. The blocks in the rectangle should then show that they have been "selected". Finally you press Ctrl and v. This should do it.
Try doing this a few times.
Is there an easier way to copy?
Yes. You can copy material down or to the right (and using the menu you can copy to the left or up as well.)
To do this ‘select’ the material (all in one row) that you want to copy down along with the places below it that you want to copy them into. (Select as described above.) Next press Ctrl and d simultaneously. (you can hold down the Ctrl key and while it is down press on the d key.)
To copy to the right you select appropriately in one column and press Ctrl and r together similarly.
To go up or to the left there is a icon near the top right on the home menu which brings down your choice of whatever direction you want to copy.
Spreadsheets today allow you to do so many things that they are scary. There are on old Excel (2007) 7 columns of menu pages, each of which allows choice among roughly 20 menus, which you can drop down allowing many many options, but you can ignore them all, if you know how to enter functions and copy. Well, if you want to save what you have done in a file, you can press Ctrl and s together. You will then have to state what file you want to save to.
OK what can I do with this stuff?
The copying properties just discussed allow quick generation of functions, derivatives, and sums and integrals of functions, whatever these words mean.