BASIC EXCEL 2000 TABLE OF CONTENTS © 2000 Clayton State University

What can you do with a spreadsheet?

Parts of the Excel windows

Starting a new worksheet, or opening an old one

Planning your worksheet

Entering text and values

Changing column widths

Formatting cells

Ranges

Editing

Inserting columns and rows

Formatting tools: alignment, bold, italics, underline, merge and center

Deleting columns and rows

Using Undo/Redo

Writing formulas

Relative and absolute cell referencing

Copying

Cutting

Using functions

Fonts and font sizes

Borders and fill colors

Viewing different parts of the worksheet at the same time

Previewing

Printing

Headers and footers

Help

Saving a workbook

Please note: This handout includes the creation of a worksheet as an example. When you see the ð symbol, please make changes as instructed to your worksheet.

WHAT CAN YOU DO WITH A SPREADSHEET?

Spreadsheets are wonderful tools for:
1.	Budgets
2.	Grade sheets
3.	Attendance rosters
4.	Tracking changes over time
5.	What-if analysis…
6.	Anything else that uses numbers. Excel is not good for word processing – use Word or another word processor for that.

PARTS OF THE EXCEL WINDOW

ICONS ON THE STANDARD TOOLBAR

STARTING A NEW WORKSHEET, OR OPENING AN OLD ONE

When Excel starts, it automatically opens a new workbook, which you can use. However, you may want to open an old workbook or start another new one.

To start a new worksheet:
1.	Click on the new icon or
2.	Click on File, New, Workbook or
3.	Press Ctrl+N on the keyboard.

To open an old worksheet:

1.	Click on File, Open, or the click on the open icon, or press Ctrl+O on the keyboard.
2.	Scroll until you find the file you are looking for, then click on it once, and click on Open. You can also double-click on the filename.

PLANNING YOUR WORKSHEET

Each Excel file (called a workbook) contains 3 worksheets, and more can be added. Each worksheet contains 256 columns and over 65,000 rows. It is sometimes rather daunting to have all this space to work in, so it is best to develop a layout for your worksheet before you begin. Let’s say we want to chart some investments over the period of 2 years. It might look like this:

Adding column and row addresses, the spreadsheet starts to take shape:

The intersection of a column and a row is called a cell, and cells are always referenced by the column and row together. Therefore, Name of Stock is in cell A1, Wally’s Widgets is in A2, etc.

The cell where you are currently working, called the active cell, has a darker border than all the other cells. You can move around the worksheet by using your arrow keys, pointing and clicking with your mouse, or by using the GO TO function key.

To use the GO TO key:

1.	Press F5 on your keyboard.
2.	Enter the address of the cell you want to move to.
3.	Click on OK

To move back quickly to cell A1, press CTRL+Home.

ENTERING TEXT AND VALUES

Excel divides cell entries into 3 categories: text, values and formulas. Text is any combination of letter and numbers, including phone numbers, social security numbers and zip codes. No math functions can be done on text entries, and if you try, you will get a =VALUE? error message. Text is also formatted using the General format, which left justifies.

Values are numbers and dates. Dates are viewed by Excel as a value, starting from 1/1/1900, which was assigned the value of 1. By viewing dates as values, Excel can use dates in mathematical formulas, such as adding 30 days to a date to find Net 30. Values are right justified by the General format.

If you use 2 digits to represent years, Excel will interpret the date in the following manner: 

• The years 2000 through 2029 if you type 00 through 29 for the year. For example, if you type 5/28/19, Excel assumes the date is May 28, 2019.*
• The years 1930 through 1999 if you type 30 through 99 for the year. For example, if you type 5/28/98, Excel assumes the date is May 28, 1998.*

To avoid confusion, (both yours and the computer's!), use 4 digits for the year.

Formulas are mathematical operations that you create or function formulas which are built into Excel. All formulas start with an equal sign. When you enter the formula in a cell and press Enter or move to another cell, you will see the results of the formula in the cell. The formula itself will appear on the formula toolbar.

There are many function formulas in Excel, ranging from calculating payments to logical if functions. Each function has a syntax, beginning with an equal sign, then the name of the function, and last, the argument in parentheses. The argument usually tells which cells are to be included in the function. Some functions, however, do not have an argument, such as =NOW().

To enter any information in a cell, either tab (or shift+tab to move backwards) to that cell, use your arrow keys to move to the cell, or point and click on the cell. Then enter your information and press Enter. Enter automatically moves you down one cell. You can also press Tab or an arrow key.

ð Enter this information in a worksheet (it will look messy, but it will be cleaned up in following steps.)

	A	B	C	D	E
1	Name of stock	Qty.	Price	1/1/97 price	1/1/98 price
2	Wally’s Widgets	1000	15	35	43.50
3	No-Nonsense Computers	1500	25	50	65
4	Loud Motor Enterprises	650	115	117	120
5	Clyde’s Carpets, Inc.	250	95.75	90	65

Your spreadsheet should look like this:

CHANGING COLUMN WIDTHS

The worksheet has some obvious problems. For example, part of the stock names in column A have disappeared, and 43.50 changed to 43.5.

The information in column A "disappears" because column A is not wide enough. Text entries are allowed to bleed over to adjacent cells on the right as long as those cells are empty. But when those adjacent cells are filled, the entries in Column A are cut off.

To adjust the column width:
1.	Point to the division between the A and B column at the top of the columns; your mouse pointer should change to a double headed arrow.
2.	Either click and drag to the right or left, or double-click. Double clicking AutoFits to the widest entry in the column.

Cells containing values or formulas fill with #### if the cell is not wide enough. Your formula or value will appear as soon as you widen the cell.

ð Widen columns as necessary in your worksheet.

FORMATTING CELLS

What happened to the zero on 43.50? According to Excel, that zero was not necessary - 43.5 is the same as 43.50. To make the zero appear, you must format the cells.

To format cells:
1.	Click on the cell you want to format.
2.	Click on Format, Cells.
3.	Click on the appropriate tab – in our example we need the number tab.
4.	Choose the formatting option – we need Number for our example. This tab also contains options for the number of decimals and how you want negative numbers to appear.

There are many other formatting options on the numbers tab.

Other available options in the format cells dialog box are alignment, font, border, patterns and protection.

Cells can also be formatted by using the formatting toolbar. You can increase or decrease the amount of decimals and format for currency or percent.

The formatting toolbar:

RANGES

In order to work on more that one cell at a time, you must tell Excel which cells to work on. This is called selecting a range. A range is defined as any rectangular portion of your worksheet, and is designated by its diagonal corners. For instance, the range of A1 to B5 would be written as A1:B5, and would include cells A1, A2, A3, A4, A5, B1, B2, B3, B4 and B5. This range could also be designated as B5:A1; B1:A5; or A5:B1. A range can be one cell or many cells. A range can be "painted" with the mouse or keyed in using the colon, as in A1:B5.

To select a range:
1.	Click on the first cell in the range.
2.	Move the mouse pointer to the middle of the cell – you should see a fat white cross.
3.	Click and drag (hold your mouse button down as you move the mouse) across the rest of the cells in the range. All of the cells will turn black except for the cell you started on, which is the active cell. That cell remains white, but is included in the range.

The range is now selected. Any actions you take will now take effect in all the cells in your selected range. To deselect a range, click anywhere in the worksheet.

ð Format cells C2:E5 for currency, 2 decimal places.

EDITING

If you want to change or add to the contents of a cell, you can edit the cell by pressing the F2 key, which puts you into the edit mode; by clicking on the cell, and then clicking on the formula bar; or by double clicking. In either case, when you have finished editing, press Enter to complete the entry.

While in the editing mode, you can use your arrow keys to position your insertion point. Press Home to move to the far left side of the cell entry or press End to move to the far right side.

INSERTING COLUMNS AND ROWS

You can insert columns and rows into your worksheet. Rows always insert above the active cell, and columns always insert to the left of the active cell.

To insert a new row:

1.	Select the entire row by clicking on the row number at the left side.
2.	Click on Insert, Row.

If you select 2 rows, then insert, row, you will insert two rows. To insert a column, select the column header (alphabet letter at the top), then click on Insert, Column.

ð In our example, insert a new row at the top for a title, and new columns as illustrated. Remember, columns insert to the left of the column you have selected. Key the word Total in cell A7 and INVESTMENT ANALYSIS in cell A1. Key Beg. Balance in cell D2; 1/1/97 Balance in cell F2, and 1/1/98 Balance in cell H2.

FORMATTING TOOLS: ALIGNMENT, BOLD, ITALICS, UNDERLINE, MERGE AND CENTER

There are four alignment icons (left, right, center and full) on the formatting toolbar. These control the alignment within cells. You can also add bold, underline and italics to cells by clicking on these icons on the formatting toolbar. All of these can be used by selecting the cell or range of cells, then clicking on the appropriate icon.

In addition, there is an icon for merge and center. This centers cell entries across a range of cells, a good way to make a title for your worksheet.

To Merge and Center:
1.	Select the range of cells you want to merge.
2.	Click on the Merge and Center icon.

You can also merge and center by selecting the cells, clicking on Format, Cells, Alignment, and Merge Cells.

ð In our example, center cells B2:H2 and merge cells A1:H1.

DELETING COLUMNS AND ROWS

If you need to delete a row or column:
1.	Select the row or column
2.	Click on Edit, Delete

CAUTION: You will lose any information in a row or column that you delete!

USING UNDO/REDO

If you make a mistake and want to undo it, click on the Undo button (it looks like a left handed U-turn). Redo (the right handed U-turn) redoes the last thing you undid. If you need to Undo something that you did a few minutes ago, keep clicking on the Undo button. Excel just works its way back, undoing each thing that you did.

WRITING FORMULAS

As noted above, all formulas and functions begin with the equal sign. In addition, we want to develop "generic" formulas that will change if the contents of the cells change. We could write a formula that says 10+10 = 20. But 10 and 10 will always equal 20. There is no flexibility. However, if we wrote the formula in cell A3 as =A1+A2, and A1 contained 10 and A2 contained 10, 20 would appear in cell A3. Written this way, if we enter a new number in cell A1 or A2, Excel will immediately re-calculate the total in cell A3. This is what allows Excel to "excel" at what-if analysis.

Excel calculates formulas using an order of precedence. An easy way to remember this order is "Please excuse my dear Aunt Sally." P stands for parentheses, which will be done first in a formula. E is for exponents. M and D are for multiplication and division, which are done in the formula working left to right. A and S stand for addition and subtraction, which are also done from left to right.

The following symbols are used in formulas:

Examples:

Add A1 to A2 then multiply by A3 would be written as =(A1+A2)*A3
The parentheses are necessary to insure that the addition gets done first, then the multiplication.

Multiply A1 by A2, then add A3 would be written as = A1*A2+A3.
No parentheses are necessary since multiplication would be done first anyway according to the "Please Excuse My Dear Aunt Sally" rule.

Although uppercase letters have been used in these examples, they are not necessary.

ð In our example, we need to multiply the Qty. by the Price/Share to get the beginning balance, multiply the Qty. by the 1/1/97 price to get the 1/1/97 balance, and multiple the Qty. by the 1/1/98 price to get the 1/1/98 balance. The following example shows the formulas; your spreadsheet will show the results of the formulas, not the formulas themselves in cells D3, F3 and H3. Cell D3 should be $15,000; F3 should be $35,000 and H3 should be $43,500.

RELATIVE AND ABSOLUTE CELL REFERENCING

By default, Excel writes formulas relatively, which means that relative to where the formula was written, the calculations are done on surrounding cells. If you put =A1+A2 in cell A3, this means that from a starting point of cell A3, add the two cells above. If we carried this formula over to cell B3, it would add cells B1 and B2, and so on across the worksheet.

Because the formulas are relative, they can be copied. When copied to a new cell, the formulas re-write themselves to their new location, so using the example above, the formula in B3 would automatically become =B1+B2; in C3 it would become =C1+C2, and so on.

You should use caution, however, when copying formulas, and double-check your results. They may not always turn out like you planned. For instance, if we were to copy the formula in cell A3 down to cell A4, it becomes =A2+A3, because the formula was written to add the two cells above it. When we moved down to A4, the two cells above became A2 and A3.

Unlike relative cell references, absolute cell references enable Excel to ALWAYS find a particular cell, no matter it is in relation to your formula. Absolute cell references are preceded by $’s before the column and row. $A$1 would always find cell A1.

Example: If you ordered a pizza and told the delivery man that you were the 4^th house from the corner, you have given him a relative reference. Starting from the corner, he could count four houses down and deliver your pizza. But suppose someone built a house overnight between you and the corner, making your house the 5^th one from the corner? Who gets the pizza?

On the other hand, if you gave the delivery man your house number, he will still find you, since your house number’s not going to change, no matter how many houses between you and the corner. This is using absolute referencing.

When should you use absolute referencing? Any time you need to make sure you find a particular cell or range of cells – such as a cell that contains an interest rate, a constant, or a table of grades.

COPYING

There are many ways to copy cells. The two easiest are the click and drag method and the Copy and Paste Icons on the standard toolbar.

Copying using the Click and Drag method:
1.	Move the mouse pointer to the bottom right corner of a cell or selected range of cells. You will see a small black plus sign (the fill handle).
2.	Click and drag the mouse to the target cells and let go.

The only problem with the click and drag method is that you copy into every cell you pass through, so if you want to go from column A to column F, you would leave a copy in columns B, C, and D also. Click and Drag is best when you are copying into adjacent cells. To copy into non-adjacent cells, use Edit Copy and Paste.

Copying using Edit Copy and Paste:
1.	Select the cell or range of cells to be copied.
2.	Click on the Copy icon on the standard toolbar.
3.	Click on the target cell.
4.	Click on Paste on the standard toolbar.

If you are copying a range of several cells, you only need to click on the beginning cell in the target. Excel will paste appropriately. Therefore you do not have to match the number of cells in your copy range to an identical number of cells in your target or paste range.

CUTTING

Cutting (moving) can also be done with the Click and Drag method or with the Cut and Paste Icons. When you copy, you will have more than one. When you cut, you will have only one, and it will be in a new location.

Cutting using the Click and Drag method:
1.	Move the mouse pointer to the any border of a cell or selected range of cells. You will see a white arrow.
2.	Click and drag the mouse to the target cells and let go.

 

Cutting using Edit Cut and Paste:
1.	Select the cell or range of cells to be copied.
2.	Click on the Cut icon on the standard toolbar.
3.	Click on the target cell.
4.	Click on Paste on the standard toolbar.

ð In our example, we can copy the formulas in cells D3, F3, and H3 down to cells D4:D6; F4:F6; and H4:H6 (see Example 5). Use either the Click and Drag method or the Edit Copy and Paste method. Your spreadsheet will show the results of the formulas, not the formulas in cells D3:D6; F3:F6; and H3:H6 (see Example 5A).

Example 5

Example 5A

 

USING THE CLIPBOARD TOOLBAR

The Clipboard toolbar is a new feature in Office 2000.  The clipboard holds up to 12 items from cutting or copying, allowing you to paste items that were cut or copied previously.  Try this:

ðKey your name in cell A10 and the date in cell A11.  Click on your name, then click the cut icon.  Click on the date, then click the cut icon.  As soon as you cut the date, the clipboard toolbar should appear with 2 items on it. Hover your mouse over each item for a quick tip about which is which.  Click in cell  A15, then click on the icon with your name on the toolbar.  Your name should be pasted into the spreadsheet.  There is also a copy icon, a paste all icon and a delete (clear clipboard) icon. 

Once activated, the clipboard toolbar will stay open, even if you move to another MS Office application, such as Word or PowerPoint.  You can close the clipboard toolbar by clicking on the x in the top right corner of the toolbar, or by clicking on View, Toolbars, Clipboard. 

ðSelect and delete any names or dates that appear in your spreadsheet from trying the clipboard.

USING FUNCTIONS

Functions are predefined formulas. Each function begins with an equal sign, followed by the function name, then an argument. The argument is in parentheses and may have more than one part to it. Commonly used functions are:

Since you use sum so much, there is an AutoSum button on the standard toolbar – it looks like E . The AutoSum button sums numbers directly above or directly to the left of the current cell. Click on the cell where you want the total to appear, click on the AutoSum, and press Enter.

If you know how to use the function, it is quicker to key it in. You may either key the range or "paint" it. However, if you are uncertain about a function, try the Paste Function button (f_x) on the standard toolbar. The paste function dialog box will help you use the function.

ð In our example, calculate the total invested at the beginning, on 1/1/97 and on 1/1/98. Click on cell D7, then click on the AutoSum or key =Sum(D3:D6). Repeat for F7 and H7, changing the range in parentheses appropriately.

FONTS AND FONT SIZES

Fonts and font size can be changed using either the formatting toolbar or Format on the menu bar.

1.	Select the cell(s) you would like to format.
2.	Click on Font or Font Size icon on the formatting toolbar or click on Format, Cells, Font.

BORDERS AND FILL COLORS

Cell borders and fill colors can be changed using either the Formatting toolbar or Format on the menu bar. Select the cell(s) you would like to format, then:

Using the Formatting toolbar:

Click on the borders or Fill Color icon. There is a small downward arrow to the right of the icon that lists more borders or colors.
Using Format on the menu:
1.	Click on Format, Cells, Border (for borders) or Pattern (for fill).
2.	Click on the line style, color, and position of the border.
3.	Click on OK.

ð In our example, place a double, black border at the bottom of cells A1:H1. Select cells A1:H1, click on the down arrow on the border icon, choose the double line at the bottom.

VIEWING DIFFERENT PARTS OF THE WORKSHEET AT THE SAME TIME

Sometimes your spreadsheet becomes too wide to fit on your screen, or maybe you need to see two columns that are non-adjacent. In this case, use Split or Freeze Panes to take a "tuck" in your worksheet. Split and Freeze panes apply above and/or to the left of the current cell. Using Split, the worksheet divides into sections, each of which can be scrolled independently. Freeze panes splits your worksheet, but keeps column and row headers visible; split does not.

To turn on Split or Freeze Panes:
1.	Position your active cell where you want the split or freeze panes to occur.
2.	Click on Window on the menu, Split or Freeze Panes.

To remove Split or Freeze Panes, click on Window Remove Split or Unfreeze Panes.

PREVIEWING

You can preview your worksheet before printing, thus preventing problems before wasting paper. To preview, click on the Print Preview icon on the standard toolbar, or by clicking on File, Print Preview.

The Preview mode toggles between Zoom In and Out. Your mouse pointer has changes to a magnifying glass. Click once to Zoom In, click again to Zoom Out. If you do not want to zoom every time you click, turn off the zoom feature by clicking on Zoom on the Preview toolbar.

Other icons on the Preview toolbar include:

Next and Previous	Moves between pages of your worksheet. Reminder: It may take more that one page to print your worksheet. Next and previous move you between these pages, not between worksheets.
Print	Takes you directly to the Print dialog box. The Print dialog box can also be reached directly from the worksheet by clicking on File, Print.
Setup	Goes to the Setup dialog box, which controls margins, page orientation, print area and headers and footers. Setup can also be reached directly from the worksheet by clicking on File, Page Setup.
Margins	Shows the margins as they are currently set.
Page Break Preview	Returns you to your worksheet in a smaller view. Page breaks will be marked. To get out of page break view, click on View, Normal.
Close	Returns to the normal worksheet view.

PRINTING

Click on the Print icon on the standard toolbar for a single copy of your worksheet.

For multiple copies, choosing a printer other than the default printer, or to print only certain pages, click on File, Print or press Crtl+P on the keyboard.

HEADERS AND FOOTERS

Headers and footers appear at the top and bottom of printed pages, respectively. You can key any text you want in the header or footer, or you can use the icons that Excel provides.

To create a header/footer:
1.	Click on File, Page Setup; or click on the page setup button in the print preview screen.
2.	Click on the Header/Footer tab.
3.	Click on Custom Header or Custom Footer.

Each header or footer is divided into 3 sections, left, center, and right. The left section is left aligned, the center section is center aligned, and the right section is right aligned. These alignments can not be changed. The seven icons available are, from left to right, font, page number, number of pages, date, time, filename and worksheet name. Position your cursor by clicking in the desired section, then click on one of these icons.

When you click on the icon, a code is inserted at the insertion point. The code for page is &[Page]. This code will insert the proper page number in the header or footer.

HELP

You can get help on a variety of topics by clicking on Help on the standard toolbar, or by pressing F1 on the keyboard. Your office assistant should appear. Key in your topic, click on search, and choose from the available subjects.

SAVING A WORKBOOK

No workbook is permanent unless you save it to disk - either your hard drive, or a floppy disk. The My Documents folder is the default for saved documents in Excel.

Saving a document the first time:
1.	Click on File, Save or File. Save As or the Save icon on the standard toolbar or press Ctrl+S on your keyboard.
2.	In the Save As dialog box, enter a filename. Excel will automatically add the .xls extension.
3.	Click on OK.

ð In our example, save the file as Basic 1.

After you have saved the document initially, the file name (workbook name) should appear in the title bar. If you make changes that you want to save, you must re-save the file.

Re-Saving the file using the same name (Updating)

Click on File, Save; File, Save As; the Save icon on the standard toolbar; or Ctrl+S on your keyboard.

If you want to save the file under a different name, save it in a different folder, or on a different drive, use File, Save As. The ONLY way to do this is to click on File, Save As. Do not use File, Save on the menu, the save icon, or Ctrl+S, as these will write over your original file.

©	2000
	Helen L. Brackett
	Clayton State University

*Copied from the Excel 2000 Help menu.