=Intro bookend=
Hi.
In this video, we will talk about the basic functionalities of spreadsheet software. Spreadsheets are a powerful tool to deal with tabular data. Knowing how to use a spreadsheet will serve you well in many aspects of your personal and professional life. In finance, a spreadsheet is particularly useful for things like budgeting, and calculating the growth of investments or debts under compounding interest.
I will be using spreadsheets to demonstrate some concepts throughout this course, so I think it would be productive to get us all on the same page as far as core functionality of spreadsheets.
=Learning objectives=
After this lesson, I hope you will be able to do the following:
* Describe main functions of a spreadsheet
* Explain the spreadsheet coordinate system
* Construct formulas refering to other cells as inputs
* Distinguish between absolute and relative cell references
and
* Rely on relative references to create recursive patterns
=Spreadsheet software=
If you have access to a computer, you should have no trouble getting your hands on some spreadsheet software. This wikipedia page has a pretty complete list of options.
While commercial offerings, like those from Microsoft or Apple, tend to be a popular choice, there is no need to spend money for spreadsheets these days. There is a large number of free and open source spreadsheets, the most popular probably being LibreOffice and OpenOffice office suites - they include a spreadsheet, a word processor, and a presentation tool. This slide show for instance, is made with LibreOffice.
There are also web spreadsheets that you can access with your browser, without having to install any software at all. One pretty decent one is ethercalc. You can spin up a spreadsheet right within your browser, no registration or software installation required. It may be missing some more advanced features, but it's a good start.
Whatever your spreadsheet choice, I encourage you to open up a sheet and follow along with what we will be doing in the video. I'll be using LibreOffice. If you are using some other software, you might see slight differences in the user interface, and maybe slight differences in behavior, but things will be largely the same at the basic level.
=Spreadsheet demo=
Let's start by discussing the main spreadsheet layout.
A spreadsheet is a basically a table. Each cell can contain any text or numbers you desire.
A cell with a thicker border indicates that it is the currently active, or selected, cell. You can use a mouse or arrow keys on your keyboard to move your selection.
To add content to a cell, just select it, and start typing. To submit your new content, hit the enter key. To cancel entering content, press the escape key. Undo and redo operations have the same general function as in any other software.
Just with this functionality alone, a spreadsheet would already be a pretty useful tool for information organization and layout. But we are just getting started.
We can enter mathematical expressions for the software to evaluate, by starting an entry with the equal sign. For instance, let's add 1 and 2 together. Like magic, our result is instantly revealed to be a 3. There is support for all the basic mathematical operations. Let's do some more quick examples.
2 times 5, 8 / 2, 3 - 6, 15 percent of 30.
Use the caret character (the one on the number 6 key) to exponentiate. Find the square of 3.
Find the square root of 25. If you didn't know about the sqrt function, you could always just raise it to the power 1/2, right?
Find log base 10 of 100.
Some basic statistical functions. Average of 1,2,5,9. We can confirm that it is indeed 4.25 by calculating it manually.
So, a spreadsheet makes a pretty good calculator, with memory of previous operations.
Note that by default, cell content shows the result of any operations. To see the original formula, look in the formula bar here at the top, or double click the cell to enter edit mode. You can get into edit mode by hittiting the F2 key on the keyboard, as well.
But the real magic starts to happen once we realize that we can refer to other cells as inputs in calculations.
Each cell in a spreadsheet can be precisely identified via its two coordinates. The rows, on the vertical axis, are identified with positive increasing integers. The columns, on the horizontal axis, are identified with letters of the alphabet.
When we move past the 26th column named Z, we start doubling up on the letters. AA, AB, and so on.
So we're not limited to only 26 columns, though there are limits on maximum rows and columns in a sheet, which vary depending on your software.
To refer to a cell, we just use its column and row number. For instance, the currently selected cell is the B14 cell, because we are in column B, and row 14. In the top left corner here we have an indicator that tells us which cell is currently selected.
So let's say we want to use a number from a previous calculation, as input for the next. For instance, let's say we want to multiply the result of the average that we calculated earlier by 4. The brute force approach would be to just retype the result. 4.25 * 4.
But we can also just refer to the cell that contains 4.25 by its name, and accomplish the same result.
This has a number of benefits. First, we don't have to retype the result, which may be prone to typos.
Second, if we decide later that we need to change some upstream calculations - for instance we want to include another number in the average, everything will be automatically recalculated for us. Let's also include 10 in the average. You can see that the result of the first calculation changes, and so does the result of the downstream calculation where we multiply by 4.
To insert a reference to a cell, you can either type the cell coordinates directly, as we did just now, or click on the cell you want to reference while you are editing the formula.
You can also use the arrow keys to navigate the selection to your desired reference cell. You won't often have to resort to typing the reference manually.
This is particularly useful for functions that take multiple cells as arguments. For instance, let's say you are tracking your spending in a spreadsheet, and you have your expenditures on food for the past few months. I'll just type in some random numbers here. If you want to quickly find the average of these, you don't have to retype all the data. Just start entering your average calculations, and when you're ready, select the range of cells you want to use. Magic!
Another useful multi-argument function is sum. Let's find the sum of these here. More magic.
All this would already be pretty awesome and useful. But it gets even better!
When we make a reference to another cell, it looks like a reference to a fixed coordinate. For instance, here's a 1 in this cell, let's add 1 in this next cell. No surprise, the result is 2. If we examine the formula, looks like we are referencing a particular cell, B26.
In reality though, references are relative by default. So what it really refers to is "the cell immediately above".
You can observe that that is the case if we copy the content of this cell, and paste it further down. What's this, it is a , not a 2. What happened? What happened is that we are still referencing one cell above, which now contains a 2, so our result is now 3. Paste it again, and we now have a 4. And so on.
This relative nature of references allows us to easily create recursive data series. It is so common, in fact, that there is built-in functionality to do this automatically over a selected range of cells. One way you could do this is, after copying your cell, select a range, then paste.
Another, which you may have noticed and been wondering what it was, is the so called "fill handle", the little square bump in the lower right hand corner of the border around the selected cell. Grab it with the mouse and drag down, and you'll be doing the same thing - replicating the relative reference formula in future cells.
Let's say we have a bunch of numbers, and we want to divide them all by two? Problem is easily solved using relative references. Let's move over here to column C, and divide the first one by 2. Now, to do them all, just fill this down until the end. Because each reference is relative, pointing to the cell immediately to the left, we have accomplished out task with a minimum of repetitive work on our part. That's precisely what computers are for, automating repetitive operations.
What happens if you want an absolute reference in a bunch of cells, rather than relative? Again, there is more than one way to do it.
One, is to enter edit mode, or use the formula bar, and copy the exact text of the formula. This will bypass the relative-reference magic, and when you paste it, you'll be referring to the exact same coordinates. Notice, this is still pointing to B26.
Another, is to enter a dollar sign in front of the coordinate you wish to lock and make absolute. Then, as you fill down your formula, the reference coordinate will be preserved. You can lock the column coordinate, the row coordinate, or both, depending on your needs. This is a more flexible approach, because this way you can mix absolute and relative references.
For example, say we want to easily generate 20 numbers, starting from 1, in increments of 1. It is easy enough to do, with what we now know.
Start with a 1. Then create the next cell, which is 1 more than the previous one. Then fill it down until we reach 20.
Now, what if we need to change it so the the numbers increment by 2? Well, we'd have to change the increment from 1 to 2 in the first formula cell, then fill it all down again.
If we anticipate having to change the increment often, maybe we will create another cell containing the increment, and have all our other cells reference it.
Let's try. We are still starting with 1. Here on the side, we'll store our increment.
Our next cell will be the sum of the first one, and the increment. So far so good.
Now, if we fill this down one, what happens? We wanted a 3 here, but we're still at 2. Why? Because all references are relative by default, so our reference to previous cell shifted with us, and so did the reference to the increment. We don't want that, we want to make the increment reference absolute. Empty cells are treated as zero by default. Let's put a dollar sign in front of the row number, so that it doesn't slide along with us as we fill down. Try again. Aha, exactly what want. Let's fill this down until we have 20 numbers.
Now, if we want to change the increment, we can just edit the increment cell, and everything else will be automatically recalculated for us. Magic!
=Quiz 1=
Let's practice what we learned.
In this exercise, you will be using a spreadsheet to generate the Fibonacci series, which is a famous integer sequence with applications in mathematics, biology, computer science, finance, astronomy, and other fields. The sequence starts with numbers 0 and 1, and each subsequent number is the sum of the previous two.
Use a spreadsheet to construct the first 20 or so Fibonacci numbers, relying on the power of formulas and relative references.
=Quiz 1 solution=
Here's the solution.
We start with the first two numbers, 0 and 1.
The next number is the sum of the previous two.
Now, we just fill down for a handful of rows, and we're done.
Observe that the relative nature of cell references automatically generates the sequence for us, referring to the cells 1 up and 2 up from the current one, every time.
=Additional reading=
This only scratches the surface of what today's spreadsheets can do. We can make graphs, filter and aggregate data, there's a wealth of formatting options, and many other advanced operations. Now that you are familiar with the basics, if you want to learn how to do something more fancy, just search the web for a tutorial about whatever you're looking for, and you'll be good to go.
=Attributions=