Below is a somewhat less well finished instruction sheet that filled a
specific time-constrained need. None-the-less it shows the clarity I can
bring to complicated subject matter. This Excel instruction sheet was
for a mid-level user with limited mathematicaly skills.
Sean Arthur, Ghostwriter
Work Instruction Example Three: Excel Spreadsheet Basics Part I
Craaytech Information System Work Instruction
Basics of Cell Formulas in Excel Spreadsheets
July 30, 2010
Basic Cell Formula Concepts and Language
How do you make spreadsheet Cells interact?
You enter mathematical instructions into the cells.
You can put mathematical calculations in spreadsheet cells.
You can put logical arguments in cells.
We will concentrate on mathematical calculations between cells.
At the heart of a spreadsheet is the SUM function. Cells are all based on
adding, or Summing, because they were originally designed like a book
keeper's ledger, which added up columns of numbers. Even negative
numbers and negative functions were originally sums of columns of
numbers. This is why almost every calculation is a SUM… More on this
Here are the basic parts of the spreadsheet:
Each sheet is called a worksheet.
Each collection of sheet(s) is called a workbook.
When you save a sheet it has a name, and you have to give the book a
If you don't name your work, Excel calls them Sheet1 and Book 1 etc…
Each mathematical calculation or logical argument is called a formula.
Each cell can contain a formula.
A formula that uses numbers from other cells uses a "range" of cells.
The range a formula refers to can be from the same sheet, a different
sheet in the same workbook, or from a sheet in a different workbook.
When a formula uses other cells' numbers, it is called "referencing".
Basic Formula Writing:
It is easier to demonstrate a basic SUM function and then explain the parts
of the SUM formula. We start with the AutoSum button.
Copyright 2009-2015 Sean Arthur . No parts of this website may be used without Sean Arthur's written permission.
2 ) REFERENCES
Basic summing references a consecutive range of cells.
Highlight the range as below, from top to bottom, or the first to last in the
range to sum and include the last square in which you want the total to
appear. Click on the AutoSum button, and it makes the SUM for you. See
the illustrations below.
Here is a column of numbers
Select the cells that
contain the numbers
you want to SUM, as
at right, leaving a
blank cell for the
And then click the
The column is summed,
as here at right.
3 ) THE FORMULA BAR
When you click on the cell that has a formula "under" it, or inside it, you will
see the formula in the Formula Bar. In this example, clicking on Cell B9
displays the formula. See below.
4 ) FORMULA SYNTAX
It is very important to understand the syntax, or language the formula is
It reads like this: Cell B9 equals a reference. The Reference equals the
result of the SUM function (or action, or calculation). The SUM function acts
on what is inside the brackets. Inside the brackets is the range of all the
values (or numbers) inside the cells between B2 and B8. The colon ( : )
means "inclusive to". At least in this basic case.
Also, capitaliztion and spacing MUST be strictly followed. As in the example
above, SUM is capitalized, and there are no spaces.
Similarly, the brackets work in pairs. The left, (, opens the instruction and
the right, ), closes it.
Strange as it seems, subtraction is the Summing into the cell the result of
two (or more) numbers subtracted. See Below:
The formula reads: The value in Cell D11 equals the formula that
equals the total of the function or calculation inside the brackets. Inside
the brackets the value in B9 is reduced by the value in D9.
6 ) Percentages
To get a percentage, the smaller number will be divided by the larger number.
We have to tell the spreadsheet that the cell will have a percentage in it, so it
can put it in the correct format.
The dividing symbol in the spreadsheet is the forward slash, " / "
In this case we will call Column B Total Parts, and Column D Bad Parts.
We have already figured out how many Good Parts we have, when we
subtacted 839 from 4769. This is D11, 3930.
The percentage of Good Parts (3930) in the total (4796) is the smaller number
divided by the larger. This gives us 0.824072132522541413294191654434892 .
Since percentages are expressed as parts of a hundred, we convert.
The spreadsheet does this for us. See illustration below.
7 ) Changing how the cell displays a number.
Right click on the cell and left click on the Format Cells option, as below.
In the box that appears, pick Number tab if
not already picked,
then pick Percentage, and pick the number of
decimal places. Click OK to close.
The sheet will round off and display your
percentage. See below.
8) Cell Formating : Changing the Look
of Cell Contents
Along with the Number tab in the above example
are the rest of the cell formatting options.
Alignment for text direction inside the cell, Font,
Border, Patterns and Proctection.
You can experiement with these choices. If you
protect a cell or group of highlighted cells make
sure you WRITE DOWN THE PASSWORD
IMMEDIATELY. It is surprising how easily you can
forget a password. If you are distracted at the
wrong moment for long enough, you may forget a
password made just minutes ago.
9 ) Summary of further Arithmetic operators
You can use the following arithmetic operators.
+ (plus sign)
- (minus sign)
/ (forward slash)
% (percent sign)
3^2 (the same as 3*3)
10 ) Referencing Other Cells: External Referencing
Here is what Microsoft says:
"How to Create a formula to calculate data on another worksheet or
If you are linking to a new workbook, save the new workbook before creating
1. In the workbook that will contain the formula, select the cell in which you
want to enter the external reference.
2. If you are creating a new formula, type = (an equal sign).
If you are entering the external reference elsewhere in the formula, type the
operator or function that you want to precede the external reference.
3. If you want to create a link to another worksheet in the active workbook,
click the worksheet that contains the cells you want to link to.
If you want to create a link to a worksheet in another workbook, switch to the
other workbook, and then click the worksheet that contains the cells you
want to link to.
4. Select the cells you want to link to.
5. Complete the formula. When you finish entering the formula, press
This means that instead of typing in a cell reference you click on it and
hit enter. The order you move back and forth between the sheet with
the original cells and the one where you are combining is very specific.
See Bellow .
Step 1. Click on the cell where you want the new number.
Step 2. IF THERE IS NO FORMULA, and all you want is to copy the number
from another location, click once with the mouse in the formula bar and then
type an equal sign in the formula bar.
Step 3. Go to the sheet where the cell is containing the number and
click on that cell.
You should see the cell highlighted with a broken hashmark and the cell's
address in the formula bar, as above.
Step 4. Hit Enter.
Step 5. You should be returned automatically to the sheet where you want
the linked number.
You should see the value of the referenced (or linked) cell. See below.
Note the Syntax:
The reference is in this form:
Cell name (C2 in this case) equals (NO SPACE)Sheet Name (NO SPACE)
exclamation mark (NO SPACE) Cell Name on the other sheet.
Think of the exclamation mark as an instruction that means find a sheet with
this name and copy the contents of the cell name that follows.
11 ) Adding calculations to a cell that references cells in other sheets:
Do the following:
Step 1: As above, select the cell where the result will appear.
In the formula bar type equal sign.
The sheet should automatically be in SUM mode, so you don't have to type
SUM. As below.
Step 2: Also as above, go to the sheet where the first number you want in your
result is located and click on it and hit enter.
As before, you should be automatically returned to the starting cell. See Below.
Step 3. Click back into the formula bar. Type in your arithmetic operator. In
this example, plus, " + " . As below.
Step 4. Next, go to the sheet with the next cell to reference, click in it and then hit
enter. You should (again) be automatically returned to the starting cell, and the
cell should have the calculated result. See below.
The order of these operations is not flexible. If you are not getting the correct cells
to link or reference, it is likely you are not making the links between the formula
bar and the referenced cells correctly.
The order is almost always: operator, other sheet's cell, and hit enter key, another
operator, then another sheet's cell and hit enter key.
And that is it for spread sheet basics.
This button is the always-useful
It is on one of the toolbars at the top of
the spreadsheet. See at right.