User:Rewood/Microsoft Excel for Windows 2000

What is Excel 2000?

Microsoft (MS) Excel 2000 is a spreadsheet program. Like all spreadsheets, Excel is ideal for recording, calculating, analyzing and graphing data. Excel has numerous built-in calculation formulas and formatting styles that you can apply to your work. You can also create your own formulas and customize your work to suit your specific needs.

Starting Excel

Excel is available to you as part of your Microsoft Office2000 software package. There are several ways to access Excel, depending upon the computer you are working on. In most cases, the Excel icon will appear as part of the Programs submenu of the Start menu. The Start menu can be accessed by clicking on the Start button, located on the Taskbar. The Taskbar is usually located at the bottom of your screen. If your computer is on a network, the Excel icon may also appear as a shortcut on your desktop or in a network program manager such as Novell Application Launcher. Once you have located the Excel icon, either from the Start menu or a desktop shortcut, click on icon and the Excel application will "launch" (begin running).

Launching Excel From the Start Menu

1. Click on the Task Bar Start button.
2. Position the mouse pointer over the All Programs selection until All Programs is highlighted.
3. Now, move the mouse pointer into the Programs submenu and position it over the Excel icon until the icon is highlighted.
4. Click on the highlighted Excel icon. Launching Excel From the Novell Applications Window
   1. Click on the Application launcher icon on the windows taskbar.
   2. Position the mouse pointer over the Excel icon, scroll down the menu if needed.
   3. Double Click on the highlighted Excel icon.

Creating or Opening an Excel File When you launch Excel, an opening screen appears, followed by the appearance of a blank work space called a worksheet. There are several worksheets in each workbook. At this time, you can begin to enter data into the new worksheet, or you can retrieve a previously created and saved Excel file.

To open a previously saved file

1. Click on the File menu
2. Select Open on the File pull-down menu
3. When the location dialogue box appears, click on the correct disk drive and the folder that your file is located in, and click on Open.

Excel Layout

What is a Workbook? The workbook is your collection of data, tabulations and graphs for a specific project. The workbook can consist of a number of worksheets where you key in your data. The worksheets can be identified at the bottom of the page by small tabs. These tabs can be renamed and you may wish to group data logically onto separate worksheets.

For instance, if you are creating a departmental budget for the current year, your workbook might be called budget.xls. (Excel automatically adds the .xls extension for all Excel files). You can then name each worksheet in the workbook a different name which represents a unit within the department.

To rename a worksheet, simply double click on the current name tab. When you do so, a cursor will appear in the current name and you can add and remove whatever text you would like.

Parts of the Workbook

1. Menu - Uniform Menu bar exists throughout Microsoft Office.
2. Toolbar - The tool bar displays buttons that you can click on to do frequent tasks such as formatting, summation, chart design and editing.
3. Name Box – Displays the name of the active cell of selected range
4. Formula Bar - The formula bar is a field that allows you to enter or edit data in a cell. It will display the formula for a selected cell instead of the results of that cell. Notice that the formula bar has an equals sign (=) to the left of the data entry cell.
5. Column Heading - Columns are labeled with letters
6. Active Cell - The active cell is the place in your worksheet where you enter information; it is highlighted with a black border, and its address is shown in the name box at the far left side of your toolbar.
7. Row Heading - Rows are labeled with numbers, starting with 1
8. Active worksheet - The active sheet will be displayed in white, and the title tab at the bottom of the sheet will be in bold type.

What Can Go in a Cell? A cell can contain alpha and numeric values that are typed directly into a cell, representing the literal value of the number or character in the cell.

An example of a constant number might be something like $43,043 or 9/18/94. A character entry might be a person's name such as "John Smith".

Every cell, whatever is in it, can be referenced by other cells. These cells are referenced by their name, which is determined by taking the column letter and row number.

So the first cell, in the upper left hand corner is in column A, row 1. It would be cell A1. If you want cell B1 to have the same number in it as cell A1, you might reference A1 by typing "=A1" into cell B1. If you want cell B1 to be one more than whatever is in cell A1, you would use a function like "=A1+1" in cell B1.

This also means that if you change the number in cell A1, the number in cell B1 changes as well. In the example above, cell B1 is selected and has the formula "=A1+1" in it (as seen in the formula bar). If we were to change the number two in cell A1 to three, the number in cell B1 would change to four (3+1). Formulas always begin with an equal sign (=). Other formulas would be of the form: "=SUM (j3:j43)", where you are telling Excel to sum the data found in cells j3 through j43.

Next, we'll learn more about formulas and how to construct them.

Creating and Using Formulas

One of the strengths of a spreadsheet program is the ability to manipulate data by adding, subtracting, dividing and multiplying values. To do this, you will create or apply formulas, within an active cell, which might look like this = ((A4+A5)*(B6-B4)). The following symbols or operators are used to create formulas:

• = Indicates that the information you are entering is a formula instead of a value.

• () Parentheses are used to enclose a formula, for example, = (A4+A5). Parentheses can be nested multiple times if necessary. When internal parentheses are not used, Excel will use the logical order of operations to evaluate a formula expression.

• + Indicates that you wish to add the values of the cells in the formula, for example, = (A4+A5+A6).

• - Indicates that you wish to subtract the values of the cells in the formula, for example, = (A7-A5-A2) or = (A10-A8).

• * Indicates that you would like to multiply the values of the cells in the formula, for example, = (A2*B2).
• / Indicates that you want to divide the values in the cells in the formula, i.e. = (A2/B2).
• : Indicates that you want to apply a formula or function to a range of cells, i.e. B4:B10.

The ( ) format tells Excel which cells and functions you want to specify. Other more complex mathematical formulas are also available through your function button on the toolbar, and as a pull-down menu in your formula field after you have selected a cell range. See below for more information on the function button.

How do we "Sum" totals? There are several ways to sum up a series of cells. First, if you have a large number of cells with numbers in each, try selecting the bottom most empty cell. Click on the Sum button. Excel will try to sum up all cells near it. Any large row or column of cells near the end of the selected cell will be summed, and the formula added to that cell. The alternative is to select the cells you want to sum and then click the sum button above. This should give you an opportunity to ensure that the correct cells are selected as they should have a moving dotted line around Adding, Moving and Removing Adding Columns and Rows You may find that you need more columns and/or rows to record all of the information you wish to place on a worksheet. Excel makes it easy for you to do so.

In your practice worksheet, place your cursor in one of the column headings and click. The entire column will be highlighted. Click on the Insert menu. Select Columns. A new column will be inserted, and the old column will now be the next column in succession. Now move to a row and click on the row heading. The entire row should be highlighted. Right click and select Insert=>Rows. A new row will be inserted, and the old row will move down.

Copying a Formula to an Entire Row or Column You can copy a formula to a large number of cells simply by selecting the formula from the Formula Bar and copying it. Once copied, select the cell you would like the formula to go into and paste the formula in that cell's formula bar. If you would like the formula to go into all cells in a row or column, simply click on the heading of the column or row. The entire row or column will be selected and you can paste the formula into all selected cells. "Filling" Cells Excel will automatically fill in cells for us depending on what we need to go into those cells. For example, if you type "January" in cell A1, and "February" in cell A2, Excel will attempt to fill in the rest of the months for you.

Notice the small black square in the lower right hand corner of the selected cell. If you place your cursor over this box, your cursor should turn into a plus sign and allow you to click and drag the selected cell. You may notice as you move the cursor that there is a pop-up displaying the months to the side. This is showing you which month is going into each cell. After the 12 months are placed, the months will begin repeating again with January. The same situation will apply if you are copying a formula or adding other information. Excel will attempt to figure out what you would like in the next cell (logically) and will continue to add it. This is handy, but don't forget to check and see what Excel has done to your cell or formula. It will not always be correct.

Moving Selected Cells

To move a group of cells in Excel 2000, simply click and drag to select them, then use an appropriate method to 'Copy' the cells. Once selected (they should have a moving dotted line around the edges) you have two choices. One is to select the same number of cells in the same size and shape (same number of cells across and down) and then choose 'Paste'. The alternative is to select the top, right- most cell that you would like to be used in the new location of the table and then choose 'Paste'. You will not be able to select a random place or number of cells and click 'Paste'; you must be exact.

Inserting Another Worksheet

To insert a worksheet, go to Insert => Worksheet. You should notice an additional worksheet at the bottom of the Excel screen. To change the name of the worksheet, double- click quickly on the worksheet title and a cursor should appear which will allow you to edit the name of the worksheet. Deleting a Row or Column To delete a row or column, move the cursor into the row or column heading and click to highlight the row or column. Then right click and select Delete. You can also choose Delete from the Edit pull-down menu.

Formatting the Worksheet

You may wish to apply formatting to your worksheet "as you go", or you can apply formatting after you have entered data and formulas. Using formatting, you can highlight column and/or row headings and add symbols such as "$" signs to your output. You can specify the number of decimal places that the worksheet can display, and whether or not to include commas in the output. You can also apply a filter to your data, allowing you to focus on a specific part of the worksheet. In Excel, you can apply pre-designed "Auto-Formatting" to all or part of the worksheet, or you can customize the formatting to suit your needs exactly.

Choosing a Format Style

AutoFormat allows you to select a layout style from a selection of pre-designed formats. The formats range from sophisticated to calendar-style layouts. To do this click on the Format menu and then choose the Auto- Format option. Otherwise, you may wish to apply certain formatting options to all or a portion of your data without using the "AutoFormat" option. There are other tools discussed below that will help you to make the same look that Auto- Format applies.

Centering a Category Title That Spans Several Columns

If you have an "umbrella" heading that includes several items on rows below it, you may want to center the heading so that it can be seen over several rows. This is called "merging cells". To do this, move the cursor to the row that contains the heading and highlight the cells in which you would like the title to be seen. Click on the Merge and Center button on the toolbar: Or, you can right click and choose Format Cells . . . , then click on the Alignment tab and place a check in the Merge Cells box under Text Control (left click to place a check mark in the box).

Applying Special Type Styles to Cell Content

It's easy to apply boldfacing, italics, or underline styles to text or numbers in cells. Simply highlight the cell or cells, and then click on the Bold, Italics, or Underline buttons in the tool bar. As always, these formatting options can also be accessed by right clicking after you have highlighted the cells or by selecting Edit to see the pull-down editing menus.

Formatting Numerical Output

You can determine how numerical data will appear in your spreadsheet by using a range of formatting options. You can choose whether or not to display a comma and/or a dollar sign, whether or not negative amounts are designated by parentheses or by a negative sign, and how many decimal places are displayed. You can also select how a date or time will appear in the spreadsheet. There are a number of great shortcuts available on the toolbar. For access to all of the formatting options, highlight the cell(s) you wish to format, then right click and select Format Cells (or go to the Format menu, and select "Cells"). A dialog box with a number of tabs (Number, Alignment, Font, Borders, Patterns, Protection) will appear. Click on the appropriate tab to display the formatting options available.

Adjusting Column and/or Row Width

Notice that when you added some formatting to the cells with numbers in them, several of our cells returned a ##### in them. This is because our cell width is no longer wide enough to hold the new figure. Let's increase the cell width. The easiest way to increase cell width is to move your cursor to the column heading and place it on the border between two cells. Notice that the cursor changes to a crosshair. Hold down your left mouse button and drag the column edge to increase or decrease the column width. You can also right click while the crosshair is present, and a pull-down menu with column adjustment options will appear.

Another way to adjust a column is to use the Auto Width option. To automatically adjust a column to accommodate the width of any cell in the column, select the column that you want to adjust, and then click on the Format menu. Select Column, and then select AutoFit.

Applying a Filter to the Data

It is sometimes necessary to set apart some data from other figures on a worksheet. For example, you may want to examine a certain trend in a list of data. In such a case, you will need to distinguish the data by filtering out the other content on the worksheet. Start by selecting the cell or cells that you would like to work with. Click on the Data menu. Select Filter => Auto Filter. You will notice a series of drop- down menus appear at the head of the column that contains the data you have entered. The drop down menu reflects all the data in each cell of the column. With your left mouse button, select an item on the drop down menu that you want to focus on. Once you have selected a certain item, all other data will be temporarily hidden from view until you select all items again. To deselect the Filter option, click on Data => Filter and uncheck the Auto Filter option.

One of the ways that spreadsheets are used is to create "what if" scenarios. These scenarios are created by changing one or more of the formulas or data items in the spreadsheet. The spreadsheet will then automatically update all cells that contain data resulting from a formula which depends upon the data that has been changed. In order to see this in action, change the number in a cell that is referenced in another cell's formula. You should see the formula cell change, based on the change you made to the number in the referenced cell.

SIDE NOTE

If you make a mistake in a cell, there are at least four ways to edit it: If you wish to re-enter data, simply highlight the cell and begin typing. The new data will replace the existing data. If you wish to make minor changes without overwriting existing data, highlight the cell to correct, and then click once in the formula bar to edit the information there. A second way to edit without overwriting is to place the cursor in the cell where you wish to make the change, and then double click. The insertion point will appear where you placed the cursor, and any data you enter will cause existing data to move over. Another way to edit without overwriting, you can also highlight the cell to correct, and then press F2. You will be able to edit the cell without going to the formula bar. It appears to be easier to press F2 to edit a cell.

Using Formulas and Functions

Functions are commonly used formulas that are built-in to the spreadsheet program. Functions return values that can be displayed in the worksheet. You must give the function the correct information (called arguments) to make the calculation(s). In Excel, this is not hard, as the program will often suggest the appropriate values. To view the functions that are built-in to Excel, click on the function symbol in the toolbar at the top of the screen. The functions are listed in categories which include financial, date and time, statistical, and so forth. The summation function is so frequently used that it rates its own button on the tool bar.

Using Functions to Analyze Data

The function symbol has a great deal of options for Excel created functions you can apply to selected cells. To see a list of these functions, simply click on a cell and press the symbol function button in the toolbar. There are different types of functions available in each of these categories: Financial Database, Date & Time, Text, Math & Trig, Logical, Statistical, Information Lookup, & Reference All.

There is also a category for "Most recently Used" formulas. These formulas include everything from complicated mathematical formulas to simple AVERAGE functions to depreciation functions for businesses. The formula selected will appear at the bottom of the pop- up window, explaining what the function is and how it should be used. To explain how functions work in Excel, we will discuss the "if- then" statement. This is a very popular and basically simple function that says the following: =IF (logical test, value if true, value if false) This formula is the layout for how the function will work.

First, there is a logical test; a statement that can be determined as true or false. For example, you might use "A1>5" as a logical test. If this is true, you can place an additional formula after the logic: "A1+1". If this is false, you can place a final formula after the "True" information.

So say you want to give a bonus point to people who have earned more than 5 points, and no bonus points to those who have 5 or fewer points. The "if- then" statement function would look like this:

=IF (A1>5, A1+1, A1) 

If you would like the cells to include text rather than numbers, the text will need to be placed in quotes. So if you wanted those over 5 points to have the word "BONUS" in the cell, and those with 5 points or fewer to say nothing, your function would look like this:

=IF (A1>5,"BONUS","") 

Other formulas and functions follow the same kind of instructions.

Remember also that you can reference a cell that has a function. The output of that cell can be used in other cells with functions as well. When one change is made, it will affect all subsequent cells and functions that reference it. Along those lines, it is very useful to place important information in a separate cell that can be referenced.

For example, if your spreadsheet calculates purchases, and needs to include tax, you might want to place the current sales tax in a separate cell. Then all functions can take the total and calculate the tax based on what is in the tax cell. If the tax changes for some reason, you can change only one cell, the cell that shows the current tax, and all your functions calculating tax throughout the spreadsheet will automatically update correctly. This can save you a great deal of time and effort.

Using Charts in Excel

Excel 2000 has several tools to allow the user to create a chart quickly and easily. This is a nice way to display the data you are talking about and also makes it easier for the user to visualize the information. In a chart, each column or row is a data series. Each individual value within the row or column is a data point.

In creating a chart, you can include the row or column heading. This information will be used as category labels or in the legend. If this information is not included, Excel will create default headings which can be modified after the chart is created. Using the Chart Wizard The chart wizard will assist you in creating a chart by leading you though a series of windows that ask you to specify the details of the chart. You may select the type of chart, the data range displayed on each axis and the legend information. You can also format the chart for a desired look.

1. Click the Chart Wizard button on the toolbar.
2. Select a Chart type from the list. You may also want to select a chart sub- type. Select Next>
3. Select the data orientation by clicking on the miniature spreadsheet at the far right of the line. It will display your spreadsheet with the default selected section of the spreadsheet. To change this, just click and drag to include the desired rows and/ or columns. Select Next>
4. Select the tab to make changes to the appropriate section. Adjust the chart options as needed to best fit your graph. Select Next>
5. Select where you want to Place Chart. Select Finish> Adjusting Chart Properties Excel will allow you to format almost anything about the display of your chart or graph.

You can click on the outside lines and drag to change the size, or to move the legend. You can also right click on either axis, on the titles, on the legend or on the graph itself, then select "Format" to make adjustments.

Some notable choices Excel gives you:

1. Color, font and placement of the legend and each axis
2. Alignment of axis labels
3. Data scale of each axis
4. Advanced options for displaying points You can also use this to change the entire type of graph (for example, pie chart to line chart).

Another handy tool is the Chart toolbar, which pops up automatically when your chart is created (it can also be opened by going to View- Toolbars- Chart). This will allow you to change the data axis and display formation at a single click.

Printing and Saving Your Workbook

Naming and Saving Your Workbook

It's always good policy to save your workbook soon after you open it and begin entering data. A flicker of the lights can spell disaster if you haven't saved your work. You'll want to name your workbook something that is relevant to your assignment so that you can quickly recall and locate the title. Incorporating dates by using numbers in the file name can also be useful when trying to decide just which file you wish to work with.

For example: File Content Workbook Name (filename) Budget for 2002 budget02.xls Timesheet for the week of 1/2/03- 1/8/03 TS_we_1803.xls Fall Semester 2002 Grades Grdfal02.xls By default, Excel will automatically save the workbook as an Excel file, with a file extension of .xls.

If for some reason you need to change the file type, click on the Save as type drop-down menu found in the Save As dialogue box and select the appropriate file type. When naming your workbook it is also a good idea to avoid spaces in the title as this can cause problems opening an saving the file properly. Dashes may also be a problem but underscores are fine.

Printing Your Workbook

Excel 2000 allows the user a great deal of flexibility in formatting printed output of worksheets.

1. Begin by setting up the page. 2. Select File => Page Setup from the menu bar. This will open a dialog box with multiple tabs. You will be on the "Page" tab. 3. Select the orientation of the page to print; portrait of landscape You can scale the size of your output so that it will print out on a specified number of pages, or choose a relative size in %. To print full size, use the default selection of 100%. Finally, choose the paper size you will be using.

Now, move to the "Margins" tab and apply the margins that you want. For our example, set the left and right margins to 0.75" and the top and bottom margins to 1.0". You can also set the page to center the printing output either vertically or horizontally or both.

Next, select the "Header/Footer" tab and set the Headers and Footers for the page. When viewing the Header/Footer tab, you will see a preview pane for the header and a preview pane for the footer. Under each preview pane is a drop-down box with an arrow which contains some commonly used headers and footers. Click on the arrow to choose from pre-defined headers and footers, or click on "Custom Header" or "Custom Footer" to define your own.

Last, set the print area from the "Sheet" tab, using the cell range notation. You can type in a cell range, or you can click on the worksheet button in the data entry cell to show the worksheet. Select the cells you wish to print. By default, Excel will not print gridlines on your worksheet. If you want gridlines, check the "Gridlines" box in the "Print" section on the "Sheet" tab. Other options are available here as well.

At any time during the Page Setup process, you can click on the "Print Preview" button to see what your printed document will look like. This feature makes it easy to make changes to how your document is printed out before you actually produce hard copy.

Once you have set up your page printout, you can print by selecting Print from the File menu, or by pressing the Print button in the toolbar.