CALIFORNIA STATE UNIVERSITY, NORTHRIDGE
 
Beginner's Guide to Excel 2000:
3

Entering Data and Using Formulas
There are three kinds of data you can enter into an Excel worksheet: text (a label), a number (a value), or a formula.

Entering a Label (Text) or a Value (Number)

  1. Click the cell where you want to enter a label or a value.
  2. Type a label (text) or a value (number). A label can include uppercase and lowercase letters, spaces, punctuation, and numbers. When typing values, do so without commas and dollar signs. You can format them later using the Format menu (see Figure 5 in the Appendix).
  3. Tap ENTER.
 

Creating a Formula

When using a formula in your spreadsheet, (a cell containing a formula that references other cells), the sum will automatically change as other cell values referenced in the formula change.This feature is very valuable when editing or adding information to your worksheet.You do not have to remember to update other cells that rely on that cells information.

A formula always begins with an equal sign (=) followed by some combination of numbers, text, cell references, and operators. If a formula is entered incorrectly, an ERROR IN FORMULA message will appear.
 

Formula Operators

An operator is a symbol that represents a mathematical or a logical operation. The most commonly used operators are addition, subtraction, multiplication or division.   However, there are many more operators that can be used.  Tables 1 and 2 (below) list the operators that can be used in Excel formulas and functions.
 
TABLE 1. Logical Operators
TABLE 2. Arithmetic Operators
= Equal
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
+ Addition
- Subtraction
/ Division
* Multiplication
% Percent
^ Exponentiation
 
Arithmetic operators are used in conjunction with numbers to create mathematical formulas. An example of a mathematical formula in Excel would be =23+5. When this formula is entered into a cell in the worksheet, the contents of the cell will read 28, while the Formula Bar will show the formula as =23+5. If the operator is changed to read =23-5, the cell contents will change to read 18.
Cell references may also be used in formulas. For instance, assume you want the content of cell C5 to be the sum of values in cells A5 and B5. Instead of entering the values contained in cells A5 and B5 (and changing the formula each time those values change) you can enter the formula as =A5+B5. Excel will automatically take the values of A5 and B5 and enter them into the formula. The benefit of using cell references in your formula is that as the values change in the cells you reference in your formula, the value in C5 will automatically change to reflect the correct answer.
 

How to Enter a Formula
(see Figure 4, below, for an example)

  1. Click a cell where you want to enter a formula.
  2. Type = (equal sign) to begin the formula.
  3. Type the first argument. Remember, an argument can be a number or a cell reference. You can type in the number or if referencing a cell, you can single click on the cell location to have the cell reference automatically included in your formula.
  4. Next, type an arithmetic operator (see Table 2, above).
  5. Next, enter the next argument.
  6. Steps 4 and 5 can be repeated as many times as needed to add to the formula.
  7. Last, tap the ENTER key. The result of the formula appears in the cell while the formula itself appears in the Formula Bar.
 
 
FIGURE 4. Formula Example
Formula Example
 
 

Worksheet Functions

Some formulas may be simplified by using worksheet functions. A worksheet function is a pre-written formula, a keyword that tells Excel what to do. For example, instead of typing =A1+A2+A3+A4+A5, you may type =SUM(A1:A5). (The colon indicates the range from A1 to A5.) In this example, SUM is a function that Excel knows about. It is important to use the correct syntax and format of a function for correct results.
 
To enter a function do the following:
  1. Click the cell where you want to enter the function.
  2. Type = (an equal sign), then type the name of the function, followed by an opening parenthesis.
  3. Type or click the cell or range you want to insert in the function.
  4. Press ENTER. Excel will automatically add the closing parenthesis to complete the function and will display the results in the cell.
Parentheses are used to separate different parts of a formula. For example, in the formula =SUM(A1:A5), the parentheses separate the worksheet function from the cell references that the function is referring to. This is particularly important in longer or more complicated functions, e.g., =((A2/4)+(A5-B3))*5. If a mistake is made and the parentheses in a formula do not match, an error message will appear.

The parts of a formula contained inside parenthesis are calculated first. Otherwise the order of operation is as shown in Table 3 (below).   If there are operators of equal precedence, the order of operation is from left to right.
 
TABLE 3. Order of Operation in a Formula
 
Operator
Description
: (colon) , (comma) (single space)
 Reference operators
-
 Negation (as in -1)
%
 Percent
^
 Exponentiation
* and /
 Multiplication and Division
+ and -
 Addition and Subtraction
&
 Concatenation (connects two strings of text)
=   <   >   <=   >=   <>
 Comparison
 

Adding a Range Using the AutoSum Function

 
AutoSum Button The AutoSum function is a great tool to use when you want to quickly add contents of a range of cells.
  1. Click the cell where you want the total to appear (usually the last cell in the column or row of cells that you want to add. The cell must be blank).
  2. Click the AutoSum icon button on the Standard Toolbar. AutoSum inserts a formula that uses the SUM function. It displays a moving border that looks like scrolling lines around the selected range of cells. This is called a marquee. Excel puts this around the range of cells it “thinks” you want to add up, and inserts the range reference in the formula.
  3. If this is the correct range, press ENTER.  If not, type or highlight the correct range and press ENTER.
 
Top  |  Contents  |  Overview |  Start |  Data+Formulas  | Editing  | Save+Print  |  Learn More  | Close+Exit  |  Excel Menus
Prepared by Tina Actis-Purtee, User Support Services
March 19, 2001

 
ITR's technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.