Spreadsheets
 |
 |
Before electronic spreadsheets were invented, people had to do the calculations by hand. On any spreadsheet, many different calculations are made, and many are linked together. That means that if one calculation is wrong, it may affect many other calculations. Sometimes the spreadsheets took up many books, and if a single calculation was wrong, it could take hours or days to correct all the errors that resulted from the initial error.
With electronic spreadsheets, the computer does the math automatically. If you find an error, then you can correct it--and the computer instantly re-calculates the entire spreadsheet. This is called automatic recalculation, and it is the most valuable feature of electronic spreadsheets.
The first electronic spreadsheet to become popular was VisiCalc. This program helped personal computers become popular. Many people bought a computer just for this program.
Since then, just about every business that can afford a computer uses spreadsheet software.
First, you should get to know the cell. A cell is a single box. It may contain a label (text), a value (number, date, time) or formula (calculation).
Cells are arranged in columns and rows. A column is a vertical strip of cells (up and down), and a row is horizontal (right and left). Columns are marked by letters of the alphabet and rows are marked by numbers. A cell is named according to the column letter and row number; for example, a cell in column "B" and row "5" is called "B5." This is called the cell's address.
This is a column
This is a row
The highlighted box is a cell with the address "B3"
Previous versions of Excel could have up to 256 (28) columns and 65,536 (216) rows in one worksheet, for a total of 16,777,216 (224) cells. Excel 2007 can have 1,048,576 (220) rows and 16,384 columns (214) for a total of more than 17 billion cells per worksheet.
To move from cell to cell, you can use:
- the arrow keys
- the tab key (to go to the right)
- shift+tab (to go to the left)
- the enter key (to go down)
- shift+enter (to go up)
You can control the width or columns and the height of rows by clicking and dragging the lines between the "headers" (the letters and numbers at the beginning of each column and row).
If you want to change many columns or rows so that they are all the exact same size, then click-and-drag-select the headers of several columns or rows, then change the size of just one of them:
Worksheets
There can be more than one worksheet in a spreadsheet file; when you open an MS Excel spreadsheet, there are three sheets prepared automatically. The sheets are shown at the bottom left by small tabs; click on a tab to go to that sheet.
You can add more worksheets. Just clik on the little button to the right of the last tab, and a new worksheet will be added.
For more options, just right-click on any of the tabs at the bottom, to get this menu.
You can also duplicate an existing worksheet by holding down the control and alt keys, then clicking and dragging the worksheet's tab to a new spot.
A cell is identified by its cell address, which is a combination of the column and row names. For example, if a cell is in COLUMN "B" and also is in ROW "3", then the address of the cell is "B3".
It is also possible to select more than one cell at a time, by clicking-and-dragging. Let's say you start at B3, and finish at B10, so all the cells in between are selected. This is called a range. A range address is written by typing the address of the first cell and the address of the last cell, separated by a colon. For example, B3:B10 .
A range can be more than one row or column. For example, you can have B3:C10, or B3:H5. Look at the range below. How would you type it?
Can you guess? To find the answer, select the space on this line between the stars: ** B2:F6 **
A cell can have many different kinds of things inside of it. This is usually called data. You can put plain text, for example: people's names, names of objects, categories, etc.--or perhaps you can just enter plain text, as you would in a word processor. Any text is OK. This kind of data is called a label.
A cell can also hold numerical data. This would include numbers, currency (money amounts), percentages, fractions, or other kinds of numbers.
A cell can also hold times and dates, or other special values. Each of these is called category of data.
Each cell has a category assigned to it. If it is a "text" cell, then it always displays text (not a number or a calculation, for instance). If it is a "date" cell, then what you type is always expressed as a date. In the beginning, each cell is a "general" cell, which means that it can contain anything.
Warning: once you type in a certain kind of data, the cell may change the category to that kind of data, and not change. For example, if you type a date into a cell, for example, "11/20/06", then Excel sees the "date" and changes the cell into a "date" cell. If you then try to type a regular number into the cell, Excel will change the number into a date. The cell will remain a "date" cell until you change it into something else using the "Format Cells" command (Format menu, Cells command).
A formula is basically a calculation. A spreadsheet really is, a giant, special calculator.
Formulas can be simple or complex. The most basic formulas are simple mathematical equations. For example, 2+2.
BEFORE WE CONTINUE: Here are some vocabulary words and symbols you should know:
|
Sign
|
keyboard sign
|
name
|
verb / noun
|
example
|
|
+
|
+
|
plus
|
add (-ition)
|
2+3=5
|
|
-
|
-
|
minus
|
subtract (-ion)
|
8-2=6
|
|
x
|
*
|
times
|
multiply (-ication)
|
3*4=12
|
|
÷
|
/
|
divide (into)
|
divide (division)
|
15/3=5
|
|
=
|
=
|
equal
|
equals
|
2+2=4
|
Have you ever played a game called "Simon Says"? In this game, a leader gives commands to everyone who is playing. Everyone must follow the leader's commands (for example, "raise your right hand," or "jump up and down"). However, the players must follow the command only if the leader begins the command with the words, "Simon Says." If the leader gives a command without those words and a player follows the command, that player must leave the game.
In Excel, the equal sign = is like "Simon Says" for formulas. A formula must begin with an equal sign. If there is no equal sign at the beginning of the command, then Excel will ignore it. But if you put the = first, then Excel will see it as a formula and it will calculate and show the answer.
For example, if you type"2+2=" into a cell and hit the "enter" or "tab" key, Excel will not change what you typed. However, if you type"=2+2" into a cell and hit the "enter" or "tab" key, Excel show you the answer, "4."
Note that when you type text, it stay on the left side of the cell. However, when you type a formula or even any number and hit "tab" or "enter," the number goes to the right.
There are two "layers" to a spreadsheet. The "top" layer is the one you see, and it has all the text and numbers. The "bottom" layer is hidden "below" the top layer, and it holds all the formulas.
If you type a formula (for example, =6*4) and hit "tab" or "enter," the formula will disappear and the answer to the formula (24) will appear in the cell. Has the formula vanished? Is it gone, destroyed, lost? No--it is still there, on the bottom layer, the formula layer. The answer is "above" it, hiding the formula below.
You can see the formula below in two ways: first, you can select the cell with the formula, and then look at the formula bar at the top of the spreadsheet. There is a bold "equal" sign at the left of the formula bar. The formula bar always shows the formula in any cell you select.
The second way to see a formula is to double-click on the cell which hides the formula.
Think of this as Excel having two layers, one which you can see (the top "data" layer), and one below which you cannot see (the bottom "formula" layer). The formula layer only becomes visible when you look in the Formula Bar or when you double-click on the cell.
Of course, Excel can do more than simple math. But in order to do more complex formulas, you need to use functions. Functions are pre-programmed formulas. Functions are codes, commands to tell Excel exactly what complex calculations to make.
The most common formula is a simple one: SUM. SUM will simple do the calculation you type in. In most cases, you really don't even need to use "SUM." For example, we already did the formula =2+2, and got the answer "4." But you could also write:
=SUM(2+2)
And you would also get the answer "4."
Notice the syntax (form) that I used to write the formula with the function. First, the equal sign; then, the function; and then, in parentheses, the information. The information in the parentheses (2+2) is called an "argument." In this case, we only have one argument "2+2", but there are many cases where two or more arguments are used. In these cases, each argument is separated by a comma. This will be explained more, later.
There are many, many functions. A few more we will study include AVERAGE and COUNTIF.
For a spreadsheet program to really use its power, though, you have to do more than just put plain numbers in calculations. You have to use references.
A reference is when you use a cell or range address (for example, "B2" or "B2:B10") inside a formula. When you do that, we do not call it an "address" any more. Instead, it is called a "reference." One cell address inside a formula is called a cell reference, and a range of cell addresses inside a formula is called a range reference.
References are powerful because they allow you to very easily change the formula. For example, if you type a formula with numbers, you have to type something like this:
=SUM(22+34+17+91+47+12+52+3+18)
In order to change anything, you must open the formula, find the number you want to change, and then select it and change it.
By using cell and range references, this is not necessary. Instead, you simply put the numbers you want to use into cells, for example:
If you use the SUM function and just have one range reference as the argument, then Excel will treat this as an "add" calculation. All the numbers in the range will be added together.
Once you have this arrangement, changing the number in any one of the cells within D2:D10 will change the result showed in the forumla in D12.
In fact, you can even use the result in D12 as a reference in another formula (for example, =SUM(D12*F12) ). You can therefore connect calculations to each other.
The really powerful point is when you want to make a change to any number. Let's say the number "22" in D2 is wrong; it should be "23". Just type the new number into D2, and the calculations will all change automatically. This is the "automatic recalculation" that was introduced above.
Let's think about this in a real-life situation. You work at a store. You must record all of the sales at the store. At 12:00 noon, you just add up all the sales for the morning sales amount. Then, at 6:00 pm, you must add up all of the afternoon sales amount. At the end of the day, you add those two numbers for the total daily sales.
Now imagine that there was a mistake in the morning numbers: you wrote $150 when the real number was just $15. Using a paper spreadsheet, you would have to make several corrections by hand. Using Excel, however, you correct the one number (change "150" to "15") and then Excel will automatically correct all the calculations in both the morning sales amount and the total daily amount.
Imagine Excel also calculates the weekly, monthly, quarterly and yearly amounts as well. In that case, all of those calculations will happen automatically too.
This is only possible because we use references. In other words, each formula doesn't have the numbers inside of it; instead, the formula looks at cells which have the numbers inside of them. Change a cell's number, and all calculations connected to that cell by references will change immediately.
 |
 |