Introduction to Excel

When You open Excel, you will get a screen similar to the following:

A spreadsheet is made up of cells. You should put one piece of data in each cell. Using Format->Cells->Number (Tab) you can pick the number of decimal places in a number. You refer to each cell with Column and Row, e.g. A3.

A group of adjoining cells is a range. For example, B1, B2, B3, B4, B5, B6 is a range that is written B1:B6. You indicate the boundaries of the range with this notation top left cell : bottom right cell.

Cells can contain numbers, text or formulas. A formula is a calculation that you want the spreadsheet to do, the calculation can be based on other cells. A formula is indicated with an = in front of it. For example, =A2+A3 would add together the contents of A2 and A3 and put the solution in whatever cell has the formula in it.

Basic Math and Formulas:

Name Formula Example
Addition + =A2+A3
Subtraction - =A2-A3
Multiplication * =A2*A3
Division / =A2/A3
Exponents ^ =A2^3 - cubes whatever is in A2
Sum sum =sum(B1:B6)
Average average =average(B1:B6)
Maximum max =max(B1:B6) - finds largest number in range
Minimum min =min(B1:B6) - finds smallest number in range
Count count =count(B1:b6) - counts the number of cells in the range

Fill Down and Fill Right:

Ctrl+D (or Edit->Fill->Down) will take your formula and copy it down the column. It makes some changes, however. It increases all of the numbers in the cell references by one. For example the formula = A2 * B2 would become = A3 * B3.

Ctrl+R (or Edit->Fill->Right) will take your formula and copy it across the row. It also changes the formula. For example the formula = A2 * B2 would become = B2* C2.

It you don't want the cell references to change when you fill down and fill right, you can make them absolute. By adding a $ in front of the piece of the cell you don't want to change, it will remain the same after filling.

To name a range:

More Complicated Formulas

(a) Random Numbers

General Form =int(rand()*(b-a)+a)

creates a random number between a and b.
Example =int(rand()*(10-1)+1

create a random number between 1 and 10

(b) Ifs

General Form =if(test, do if true, do if false)

if the test is true, it does the action in do if true,
otherwise it does the action in do if false..
Example =if(A3=7, "correct", "incorrect")

if A3 is 7, it prints "correct", if A3 is not 7, it prints "incorrect"
Nested If =if(A3=7, "correct", if(A3=8, "close", "incorrect"))

if A3 is 7, it prints "correct", if A3 is 8, it prints "close",
otherwise it prints "incorrect".

(c) Or

General Form =or(test1, test2, test3,...)

prints true if test1 is true or test2 is true, etc.
can have as many pieces as you what.
Example =or(A3=1, A3=3, A3=5)

prints true if A3 is 1, 3 or 5, false otherwise.
Inside an If =if(or(A3=1, A3=3, A3=5), "odd", "even")

prints odd if A3 is 1, 3 or 5, even otherwise.
Inside a Nested If =if(A3=7, "correct", if(or(A3=8, A3=6), "close", "incorrect"))

if A3 is 7, it prints "correct", if A3 is 8 or 6, it prints "close",
otherwise it prints "incorrect".

(d) And

General Form =and(test1, test2, test3,...)

prints true if test1 is true AND test2 is true, etc.
can have as many pieces as you what..
Example =and(A3<10, A3>5)

prints true if A3 is between 5 and 10, false otherwise.

(e) Countif

General Form =countif(range, test)

counts all of the instances of test in the range provided.
Example =countif(B1:B6, 7)

counts all of the 7's in the range B1:B6
More Complex Examples =countif(B1:B6, "<10")
            counts all of the numbers less than 10 in B1:B6

=countif(DATA, ">10")
            counts all of the numbers over 10 in the named range data.

=countif($B$1:$B$6, 8)
            counts all of the 8's in the absolute data range B1:B6.

Frequency Distributions

If you have a list of raw data (e.g. Heights of people: 162, 172, 130, 180, 200, 159, 160, 185), you can quickly generate groups of data to graph.

Decide how large the groups will be and create a chart for the data. We want for example:

If the groups are one unit apart,
create a chart with the group
entered (1,2,3,4,5 in this case)

In the D column enter the
following formula:

=countif($A$1:$A$20, C4)

This will count all of the
instances of the number next
to the formula.

It can be filled down.

 

If the groups are farther apart, create a chart with the units in it (column C).

In the D column enter the
following formula:

=countif($A$1:$A$20, "<10")

You can fill it down and change the "<10" part accordingly.

In the E column in E6, type = D6-D5. This will put the correct total in each slot.

Pieces of a Chart in Excel