Introduction to Microsoft Excel

Objective: To learn Microsoft Excel basics such as entering data and formulas, moving, copying, and pasting data, using Autofill, using multiple worksheets in a workbook, formatting pages, and formatting worksheets.

NOTE: This information is geared toward Excel 97. The procedures and menus might be slightly different for other versions of Excel.

1. The Basics

a. Workbook. A Workbook is the file in which you work and store your data. Because each workbook can contain many sheets, you can organize various kinds of related information in a single file.

b. Worksheet. Use worksheets to list and analyze data. You can enter and edit data on several worksheets simultaneously and perform calculations based on data from multiple worksheets. When you create a chart, you can place the chart on the worksheet with its related data or on a separate chart sheet. The names of the sheets appear on tabs at the bottom of the workbook window. To move from sheet to sheet, click the sheet tabs. The name of the active sheet is bold.

i) Renaming Worksheets

(1) Double-click the sheet tab.

(2) Type a new name over the current name.

ii) Adding/Deleting Worksheets

(1) To add a worksheet, click “Worksheet” on the INSERT menu.

(2) To delete a worksheet, select the sheet(s) you want to delete. On the EDIT menu, click “Delete Sheet.”

iii) Moving/Copying Worksheets

(1) To move sheets within the current workbook, you can drag the selected sheets along the row of sheet tabs. To copy the sheets, hold down CTRL and then drag the sheets; release the mouse button before you release the CTRL key.

(2) To move or copy sheets to another existing workbook, open the workbook that will receive the sheets. Switch to the workbook that contains the sheets you want to move or copy, and then select the sheets. On the EDIT menu, click “Move or Copy Sheet.” In the TO BOOK box, click the workbook to receive the sheets. To move or copy the selected sheets to a new workbook, click NEW BOOK. In the BEFORE SHEET box, click the sheet before which you want to insert the moved or copied sheets. To copy the sheets instead of move them, select the “Create a copy” check box.

c. Rows, Columns, and Cells

d. Navigation

To move between cells on a worksheet, click any cell or use the arrow keys. When you move to a cell, it becomes the active cell. To see a different area of the sheet, use the scroll bars. Here are some useful keyboard shortcuts:

To Move to…	Press
Beginning of Row	HOME
Beginning of Worksheet	CTRL+HOME
End of Worksheet	CTRL+End
Last Cell in Row	CTRL+Right Arrow
First Cell in Row	CTRL+Left Arrow
First Cell in Column	CRTL+Up Arrow
Last Cell in Column	CTRL+Down Arrow
One Cell to the Right	TAB
Up One Screen	Page Up
Down One Screen	Page Down
To a Specific Cell Address	F5+the cell address you want to go to (e.g. A5 or F23)

e. Toolbars

f. Getting Help (Help menu, Office Assistant)

2. Entering Data

a. Constant Values

i) Numbers (right-aligned): Numbers 0-9 or any of the following characters

$ % () , . - +

ii) Text (left-aligned): Contains any other character other than those listed above.

b. Formulas

i) Must start with an = sign and be followed by a mathematical equation.

+	addition
-	subtraction
*	multiplication
/	division
%	percent (divide by 100)
^	exponentiate

ii) It is always better to use a cell address instead of an actual number. This way, you will rarely have to edit your formulas as conditions change. Examples of formulas are:

=B2+C2
=A27*B1
=((a2-b2)*c2)/d1

iii) Formulas are not case-sensitive.

iv) Formula Bar. Formulas are displayed in an area known as the “formula bar” at the top of the screen just above the column letters and below the ‘File,’ ‘Edit,’ and other menus and toolbars. If you’d like to edit the contents of a cell, navigate to that cell and click once inside the formula bar (or press F2). You can use your arrow keys and Home/End to move through the formula bar. You can add text or use the Backspace/Delete keys to remove text. Press ENTER when you’re finished editing.

v) Order of Operations. Excel always performs operations in this exact order:

(1) anything contained within parentheses is evaluated first, regardless of what the rules say;

(2) percentages;

(3) exponentiation;

(4) multiplication and/or division;

(5) addition and/or subtraction.

NOTE: Sometimes it’s useful to be able to see the actual formulas in the cells, rather than the numbers. To view the formulas in the cells, press CTRL + ` (backward accent). To see the numbers again, press CTRL + ` again.

3. Copying/Moving Data & Formulas

a. Copy/Paste. Highlight the cells you want to copy. Click on the COPY button on the toolbar (or right-click and choose COPY; or click on EDIT, then COPY). Select/highlight the cell(s) you want to copy to, then click on PASTE on the toolbar (or right-click and choose PASTE; or click on EDIT, then PASTE). WARNING! Any text that was in the cell(s) you copied the data to will be replaced!

b. Cut/Paste. Highlight the cells you want to cut. Click on the CUT button on the toolbar (or right-click and choose CUT; or click on EDIT, then CUT). If you would like to paste the data/formula(s) somewhere else, select/highlight the cell(s) to which you want to move the data/formula(s), then click on PASTE on the toolbar (or right-click and choose PASTE; or click on EDIT, then PASTE). WARNING! Any text that was in the cell(s) you pasted the data to will be replaced!

c. After you perform the COPY or CUT operation, if you’d prefer to insert the data elsewhere instead of pasting it, just highlight the cell(s) into which you’d like to insert the data/formula(s), right-click, and choose INSERT COPIED CELLS or INSERT CUT CELLS. This will insert the cut or copied data into the new cells without replacing what was already there (it’ll be moved to make room).

d. Copy Handles. Click on a cell and notice a small black box in the lower right corner of the cell. This is a “copy handle.” You can use this to quickly copy the contents of the cell into one or more adjoining cells. Just point to the copy handle and drag it to the cells to which you wish to copy the data or formula.

4. Cell References

Any time you incorporate a cell’s address into a formula, you have made a ‘cell reference.’ There are three types of cell references:

a. Relative Reference: a reference to a cell address in a formula that changes when the formula is moved or copied. By default, all cell references are relative.

b. Absolute Reference: a reference to a cell address in a formula that will not change when the formula is moved or copied. Excel uses $ signs in formulas to indicate that the item that follows is absolute. For example, if you have a formula in which you will always want to multiply a cell by the contents of cell C1, no matter where the formula is moved or copied to, then type it this way:

=B1*$C$1

If you copy or move that formula to cell B2, then Excel will make the formula look like this:

=B2*$C$1

c. Circular Reference: a reference that occurs when a formula contains a reference to its own cell. This is usually the result of an error in your formula, but there are rare instances when you may find them useful. Refer to Excel’s HELP feature for more information about circular references/iteration.

5. Autofill

Use this feature when you need to fill a column or row with values that increase incrementally. It automatically enters a series of numbers, dates, or text.

a. Months

b. Days

c. Quarters

d. Words and Numbers

e. Dates

f. Numbers

i) Type the first two numbers in a series in two separate, but adjacent, cells.

ii) Select both cells.

iii) Drag the copy handle associated with this pair of cells.

6. Functions

a. =SUM() Totals the values in specified cells. For example,

(1) =sum(a1:a3) would calculate a1+a2+a3

(2) =sum(a1:a3,100) would calculate a1+a2+a3+100

(3) Blank cells = 0

b. =AVERAGE() Calculates the average of a range of numbers. Text fields and blank entries not included in calculations. Zeros are included.

c. =MAX() Returns the largest value in a selected range of cells. Blank entries not included. Text entries not included.

d. =MIN() Returns the smallest value in a selected range of cells. Blanks and text not included.

e. =COUNT() Returns the number of entries in a selected range. Actually counts each cell that contains number data. Blanks and text not included.

f. =COUNTA() Returns the number of entries in a range. Blanks not counted. Text is counted.

g. =IF(condition, value-if-true, value-if-false) Checks the logical condition of a statement and returns one value if true and another value if false. The value returned may be either a number or text. If the value returned is text, it must be in quotes.

7. Inserting Cells, Rows, and Columns

a. Cells. To insert blank cells, select a range of existing cells where you want to insert the new blank cells. Select the same number of cells as you want to insert. On the INSERT menu, click CELLS. Click SHIFT CELLS RIGHT or SHIFT CELLS DOWN.

b. Rows. To insert a single row, click a cell in the row immediately below where you want the new row. For example, to insert a new row above Row 5, click a cell in Row 5. To insert multiple rows, select rows immediately below where you want the new rows. Select the same number of rows as you want to insert. On the INSERT menu, click ROWS.

c. Columns. To insert a single column, click a cell in the column immediately to the right of where you want to insert the new column. For example, to insert a new column to the left of Column B, click a cell in Column B. To insert multiple columns, select columns immediately to the right of where you want to insert the new columns. Select the same number of columns as you want to insert. On the INSERT menu, click COLUMNS.

8. Cell Formatting

You can apply various formatting to cells – alignment, font, borders, patterns, and various number formats. It’s worthwhile to explore some of the many options available to you.

a. Choose the cell(s) you want to format.

b. Click on FORMAT, then CELLS.

c. On the NUMBER tab, you can choose the appropriate formatting for the type of data you’re using. For example, you can choose “currency” to have Excel automatically format any number entered into that cell in the currency format (with a dollar sign, etc.). Cells are automatically defined as having a “general” format. You can change it to “number” or “text” or “date” or “time,” whichever is appropriate. TIP: If you’re entering zip codes, be sure to format the cell as “text.” Otherwise, Excel will drop any leading zeros.

d. On the ALIGNMENT tab, you can choose how the data will be aligned in the cell. You can also choose to wrap text, shrink text to fit in the cell, or to merge selected cells.

e. Click on the FONT tab to choose the font, size, and color, or other effects.

f. On the BORDER tab, you can choose what type of border you would like to place around the cell(s).

g. On the PATTERNS tab, you can choose background colors and patterns for the cell(s).

h. On the PROTECTION tab, you can choose whether to lock or hide the cell(s).

TIME SAVER: Once you’ve formatted a cell and you like the way it looks, you can use the FORMAT PAINTER button on the toolbar to copy all of that fancy formatting to other cells. This will save you from having to format the cells individually. Just highlight the formatted cell(s) and click the FORMAT PAINTER button. Then highlight the cell(s) to which you want to apply the formatting. It’s that easy! If you’d like to apply the formatting to more than one non-contiguous cell, just click the FORMAT PAINTER button twice and then proceed to choose the cell(s) to which you want to apply the formatting. Click the FORMAT PAINTER button again to de-select it.

9. Clear Contents, Formats, or Comments from Cells

a. Select the cells, rows, or columns you want to clear.

b. On the EDIT menu, point to CLEAR, and then click ALL, Contents, Formats, or Comments.

c. Notes:

i) If you click a cell and then press DELETE or BACKSPACE, Excel removes the cell contents but does not remove any comments or cell formats.

ii) If you clear a cell, Excel removes the contents, formats, comments, or all three from a cell. The value of a cleared cell is 0 (zero), and a formula that refers to that cell will receive a value of 0.

10. Delete Cells, Rows, and Columns

a. Select the cells, rows, or columns you want to delete.

b. On the EDIT menu, click DELETE. Surrounding cells shift to fill the space.

c. Note: Excel keeps formulas up to date by adjusting references to the shifted cells to reflect their new locations. However, a formula that refers to a deleted cell displays the #REF! error value.

11. Saving Your Work

a. Save a New, Unnamed Workbook

i) Click FILE, then SAVE (or the SAVE icon on the toolbar).

ii) In the SAVE IN box, select the drive and folder where you want to save the workbook. To save the workbook in a new folder, click CREATE NEW FOLDER.

iii) In the FILE NAME box, type a name for the workbook. You can use long, descriptive file names.

iv) Click SAVE.

b. Save an Existing Workbook

Click FILE, then SAVE (or the SAVE icon on the toolbar).

c. Save a Copy of a Workbook with a New Name or in a New Location

i) Open the workbook you want to make a copy of.

ii) On the File menu, click SAVE AS.

iii) In the FILE NAME box, type a new name for the workbook.

iv) Click SAVE.

v) Tip: To save the copy in a different folder, click a different drive in the SAVE IN box, or click a different folder name in the folder list, or both. To save the copy in a new folder, click CREATE NEW FOLDER.

Sources/More Information

· Click on HELP, then CONTENTS and INDEX (or MICROSOFT EXCEL HELP) to get fast help with Excel questions.

· Walkenbach, John. Excel 97 for Windows for Dummies Quick Reference. NY: IDG Books Worldwide, Inc. 1996.

· http://www.ed2go.com/vtc (online courses – about $69 each)

· There are a lot of free online tutorials, some better than others. Use your favorite Web search engine to search for Excel tutorial (or something similar) and explore for yourself.