Excel Basics, Page 2

Excel Basics, Page 1

MS Office Contents

Home

Excel Basics, Page 3

When you enter data in a cell in Excel 97, the data is displayed in the cell and in the formula bar. You can edit data in either place. To edit in the formula bar, simply click and edit as you would any other text. When you're finished, press [Enter] to keep the changes you made.

To edit the data in the cell, double click in the cell to insert the cursor, then make any required changes.

In the picture to the right, the displays in the formula bar and the cell are different. When you enter a formula in a cell then press [Enter], the result of the formula is displayed in the cell. The formula bar displays the formula itself. If you double click in the cell, the formula will be displayed there too, so you can edit it in either place.

The cursor location, C4, is shown in the Reference Box. Notice also that the row 4 and column C headings have a darker border on the bottom and right edges, making it easier to determine the cell address at the cursor location.

Entering Data in a Single Cell:

Click on the cell and type the entry. As you type, the entry will appear both in the cell and in the formula bar just above the worksheet. If you make an error, click in the formula bar and correct it there, or double-click in the cell and make your corrections.

Press the [Enter] key to move the active cell outline/selection to the next cell down in the same column, or use the right or left arrow keys to move the selection one column in either direction.

New in Excel 97

If you enter data straight across a range of cells then press the [Enter] key, the cursor will return to the first cell in the next row. For example, if you've just finished entering a record in cell Q27 and press [Enter], the active cell will become A28.

NOTE: To change the direction that the selection moves when you press [Enter], click on Options in the Tools menu, and select the Edit tab. Click on the checkbox in front of Move Selection After Enter to insert a check if there isn't already one there, then select a direction from the dropdown list.

Not So New in Excel!

Data Entry Form: Do you know about Excel's automatic data entry form? It's been there all along and can make entering a large amount of data a lot easier. First you need to set up your spreadsheet with column headings. If you're creating a customer list, for example, your column headings might include LastName, FirstName, Title, Company, Address1, Address2, etc.

Once you've set up the headings, select Form… from the Data menu. The Office Assistant might ask you to specify which row you want to use for headers, and then will create a data entry form using the headers in your sheet. It's significantly easier to enter data this way than it is to move around in a large spreadsheet.

Types of Data:

• Numbers: You can enter numbers into a cell either as constant values (a specific value that will not change unless you edit the cell) or as a formula (the value that results from a formula can change when other values in the worksheet change). Formulas always begin with an equal sign (=). (See Working with Formulas.

  New in Excel 97

  When a number is too wide for a cell, Excel will automatically resize the column to the appropriate width unless the column has been manually resized, in which case it will display the familiar pound signs (#####) instead of the number you entered or the result of a calculation. You can easily widen the column to accommodate the largest entry, when necessary (see Manipulating Columns & Rows).

  Accuracy: Excel stores numbers with 15 places of precision, no matter how many decimal places you choose to display in your worksheet. You can see the actual number in the formula bar when you click on the cell containing it.

  When you do calculations in a worksheet, Excel uses all 15 places, so the result that you see will not necessarily be the result you would expect from the numbers that are displayed.

  For example, you enter a formula that divides one number by another in a column formatted to display whole numbers only, with no decimals. The division results in a number with several decimal places and the result is added to the first number. Excel rounds the result to the nearest whole number. The fractional portion is not displayed.

  If the column has been formatted to display two decimal places, the result of the calculation will be rounded to the nearest 10^th or 100^th.

  If you want to do calculations on the basis of the displayed values only and not to the full 15 places of accuracy, click on Options in the Tools menu and select Precision as displayed on the Calculation tab. Caution: once the 15 places of accuracy are reduced to 1 or 2 places, you won't be able to change back; once those numbers are gone, they're gone for good.

• Text: any combination of characters or numbers that Excel does not interpret as a number, date, time, logical value, or error value is interpreted as text. To enter a number as text, type a single quote (‘) before the entry (example: zip codes beginning with a zero – the leading zero is dropped if Excel thinks it's a number. Adding a single quote at the beginning tells Excel to leave the entry as you typed it, with the leading zero in place. See Formatting Cells for another, better method of formatting cells as text cells.).
   
• Dates & Times: Excel recognizes several date and time formats and, unlike early spreadsheets, will not interpret them as division problems! You can, of course, do math on dates: on an IBM compatible PC, January 1, 1900 is considered day 1, January 2 is day 2; May 17, 1994 is day 34,471. Assigning serial numbers to dates means that they can be used in calculations. You can subtract March 1, 1937 from May 17, 1994; Excel will return an answer of 20,896. You can also do calculations with time, either in 12 or 24 hour format. HOWEVER, you cannot enter dates or times directly into formulas; they must be enclosed in double quotes: ="5/17/94"-"3/1/37"
   
• Logical and Error Values: Unless you are working with formulas, functions, or macros, you shouldn't normally see these values, nor would you enter them into a spreadsheet. They are values that the program returns when it cannot perform a particular function because of erroneous values, invalid entries, etc. An example might be a formula that requires division by zero, which is not allowed, or perhaps a calculation that is written to get one of its variables from a cell that contains no data.

  If you see an error such as #NULL!, #DIV/0!, #NUM!, use the Search function in Excel Help to look up the specific error and learn how to correct it.

Numbers & Number Formats

There are two kinds of numbers in Excel. The first kind are numbers that you use in calculations, e.g., totaling a column or multiplying the number of hours by an hourly rate.

You might also enter numbers that you want to appear as text (phone numbers, zip codes, etc.). What follows refers to non-text numbers.

By default, Excel accepts numeric entries in a number of formats; you can pick other formats from an extensive list. If necessary, you can design custom formats.

• Number entries can contain the characters 0-9, and the following: + - ( ) , / $ % . E e.
• You can include commas in numbers such as 1,000,000.
• You can enter negative numbers by preceding them with a minus sign or enclosing them in parentheses. Plus signs in front of numbers are ignored by the program.
• A single period in a numeric entry is treated as a decimal point.

Here are the results of numeric entries:

To select a different format (e.g., to display a date as 3/4/97 instead of 4-Mar), select the cell(s) or column(s) to be formatted. With the mouse pointer in the highlighted cell or range, right-click and select Format Cells from the shortcut menu (or in the Format menu, select Cells…), then click on the Number tab.

Select a category and then a format. For instance, select m/d/yy to display dates as 3/4/95 instead of 4-Mar. You can apply the format to a single cell simply by clicking on it before changing the format; or apply it to a range of cells or a whole column by selecting the range or column, then choosing a format (click on the column header to select the whole column). If you need a format that is not included in the listing, it's easy to create your own (see below).

To insert the current date, press [Ctrl + ;] (semicolon). The date will be inserted using the m/d/yy format (7/18/97). To see a list of the date formats available, click on Cells... in the Format menu. Click on the i>Number tab, then click on Date. Scroll through the list to see the formats.

To create your own date format, click on Custom at the end of the Category list and type in the format in the Type text box. For example, to create a format to show the entire year, enter m/d/yyyy.

Entering Text

Text entries in Excel are generally referred to as labels and can be any combination of letters, numbers, and symbols. Text is automatically left-aligned. Longer text entries can overlap the cell to the right if it doesn't contain an entry. If the cell to the right of a text entry does contain data, the text entry display will be truncated. If you widen the column or click on the cell and read it in the formula bar, you'll see that the text is all there, even though it may appear to be truncated.

If you enter numeric values that you want to use as text (zip codes, dates, etc.), remember to format the cells as Text, use one of the special formats, or begin the entry with a single quote (‘).

Entering Formulas

Manually: Type a formula/equation into a cell almost as you would write it with pencil and paper. However...

• In Excel, all formulas must begin with the equal sign (=). If you leave out the equal sign, Excel will assume that your entry is text and it won't be calculated.
• Some mathematical operators are different:

Addition	+ (Plus sign)
Subtraction	-  (Minus sign)
Multiplication	*  (Asterisk)
Division	/  (Forward Slash)
Exponentiation	^  (Caret – Shift + 6)
Parentheses	()

Excel uses standard algebraic rules in determining the order of operation in formulas:

1. exponentiation
2. multiplication
3. division
4. addition
5. subtraction

If you don't include parentheses, Excel will evaluate equations based strictly on the order of operations shown here. The results can be very different from what you had in mind!

The formula

=10+2*0.25

produces a result of 10.5: the 2 is multiplied by 0.25. The result of the multiplication is 0.5, which is then added to the 10.

The results are quite different when you use parentheses:

=(10+2)*0.25

produces a result of 3: the calculation within the parentheses is done first, the result is multiplied by 0.25.

You can use as many parentheses as you need to in a calculation. The only rule is that they must be used in pairs (opening and closing). If you forget one, Excel will remind you by displaying the message Parentheses do not match or Error in Formula. Correct the error either in the cell (double-click to insert the text cursor) or in the formula bar.

Entering Formulas Using Cell References

The cell reference is, in a manner of speaking, a cell's address or identifier. Cells are referenced by identifying the column and row intersection where they are located. B7 refers to the cell that is located in column B (the second from the left) in the 7th row from the top.

If you have a problem figuring out the reference for a particular cell, just click on it and read it in the name box.

To calculate the totals for each region, you might click in E9 and type in

=b7+c7+d7+e7

or you could use the AutoSum function (explained below)  by clicking on cell E9, on the AutoSum button, then dragging through cells b7 through e7. The equation will look quite different:

=SUM(B7:E7)

but the result will be the same: the total of cells b7+c7+d7+e7.

Excel Functions

Excel 97 contains more than 250 functions – predefined equations that operate on one or more values and return a single value. They include financial functions to calculate loan payments, appreciation, and depreciation; math and trig functions; statistical functions; logical functions; etc. Functions will be covered in a separate article.

AutoSum is probably the most widely used and useful Excel function, allowing you to quickly and easily added up a column or row of numbers, or even a non-contiguous range of numbers.

1. Click in the cell where you want the total to appear.
2. Click on the AutoSum button on the toolbar Excel will put a dashed border around the cells it thinks you want to total.
    
3. If it guesses correctly, press [Enter] or click in another cell. You'll see the total in the cell where the Sum function was entered. If you click on the cell with the Sum function, you see the formula in the Formula bar.
    
4. There are two ways to total cells other than the range Excel selected:
    
   1. Click on the first of the cells then hold down the [Ctrl] key while you select the other cell(s) you want to include in the calculation, or
   2. Insert the AutoSum function as described in 1-3 above, then edit it manually.

New in Excel 97

AutoComplete: "If Excel recognizes the pattern you're typing when you enter a sequence of characters, it will attempt to complete the pattern using a feature called AutoComplete. AutoComplete can be a major time-saver for you if you manage lists in Excel or find that you are entering the same values or functions over and over again. If you activate the AutoComplete feature while entering data, review the insertion and, if it makes sense to you, press [Enter] and move on."

You can turn AutoComplete on or off by selecting Options from the Tools menu, selecting the Edit tab, then either adding or removing the check mark next to Enable AutoComplete for cell values.

Entering Comments

It's easy to attach a note or comment to a cell: just click on the cell and select Comments from the Insert menu (or use the Comments button, which you'll probably have to insert in the toolbar. See Customizing Toolbars for instructions.)

Type your note in the text box that appears, then click in any other cell. You may see the entire note displayed or just a small red triangle in the upper right corner of the cell. If you only see the indicator, read the note by moving the mouse pointer over the cell that contains it: the note will pop up automatically.

If your name doesn't appear as the author at the top of the note, click on the Tools menu, select Options, then click on the General tab. Put your name in the User name: field.

Excel Basics, Page 1

MS Office Contents

Home

Excel Basics, Page 3