| Table of Contents | | Previous | | Next |
In this course we will be using Microsoft Excel 2002. The primary capabilities of Excel include spreadsheets and graphs but Excel has many of the same editing and formatting features as Word.
Many spreadsheet functions can be performed in multiple ways. i.e. To cut and paste you can use the Edit menu, right-click inside the document or use the scissors and clipboard icons on the standard toolbar. The three primary ways of accessing commands are
When you open a new document you obtain a new workbook. Each new workbook initially contains three worksheets. To move between worksheets use the little tabs at the bottom of the Excel window. To rename or remove a worksheet right-click on a tab. Use the insert menu to add worksheets. Reorganize the worksheets by dragging and dropping the tabs.
Each worksheet has 256 columns and 65,536 rows for a total of 16,777,216 cells. You can only enter information in the cell that is current. Excel indicates which cell is current in three ways
Excel only has three data types and hence only three possible types of entries for each cell.
To enter information into a cell move the cell pointer to the correct cell and type. To move to the next cell use the Tab, Enter or arrow keys. To change the direction of data entry associated with the Enter key from vertical to horizontal use the Edit/Fill menu. You can use AutoFill to quickly title the columns with the 12 months, the 7 days of the week or label the rows from 1 to 500 by typing in the first few entries and dragging the fill handle on the lower right hand corner of the cell. To put the same entry in many different cells at the same time, select the cell ranges to hold the information by holding down the Ctrl key as you select the cells. Construct the entry on the formula bar and press Ctrl-Enter.
Note that Excel has two independent pointers. The cell pointer which indicates the current cell maintains its shape as an outline around the current cell. The mouse pointer which moves independently from the cell pointer changes shape according to state of Excel. Some of the various shapes of the mouse pointer and their functions are
The cell reference A1 represents column A, row 1. By placing the colon operator between two cell references, we are able to represent various ranges of cells as shown in the list below.
A reference that refers to the same cell or range on multiple sheets is called a 3-D reference. For example, suppose we have created a workbook containing four worksheets named January, February and March.
You can create your own formulas using cell references such as A2, B2 and the arithmetic operators + (plus) for addition, -(minus) for subtraction, * (asterisk) for multiplication, / (slash) for division and ^ (caret) for exponents. Note all formulas must begin with an equals sign. If the equals sign is not present, Excel will interpret the formula as either text or a number.
In addition to creating formulas you can use built in functions. The simplest is the AutoSum which is found on the standard toolbar. The complete menu of functions can be found with the Paste Function icon on the standard toolbar. These functions are grouped into the following categories. Note, to extend the number of available formulas normally available in Excel go to Tools/Add-Ins.
Some examples of formulas entered using Paste Function include
Suppose we have created a workbook containing four worksheets named January, February and March and we want to create a quarterly report. If the amount of money spent for food each month is stored in cell A11, then we can find the average spent on food per month during the first quarter using the formula =AVERAGE(January:March!A11) .
Array formulas can return multiple values. Suppose x-values are in column A and y-values are in column B. To find the slope and y-intercept of the least squares line, select two cells in which to place the results. Use the Paste Function to select the LINEST function. Enter the appropriate ranges for the x-values and y-values and press Ctrl + Shift + Enter. The slope and y-intercept should appear in the cells you selected. Note there are several other array formulas in Excel including FREQUENCY (computes a frequency distribution from raw data), TRANSPOSE (computes the transpose of a matrix),
A very convenient feature of Excel is the use of AutoFill to copy a formula to a range of cells. For example to sum the rows of the following table use the AutoSum icon to set up the original formula as shown in red. Then drag the fill handle (lower right hand corner) down to copy the formula to the cells below. Since Excel adjusts the cell references according to the direction of copying, the cell references are known as relative cell references. The default in Excel is to use relative cell references.
| A | B | C | |
| 1 | 5 | 20 | =SUM(A1:B1) |
| 2 | 10 | 3 | =SUM(A2:B2) |
| 3 | 30 | 15 | =SUM(A3:B3) |
Sometimes, such as when you want to divide a range of numbers by a single number, then you can use the dollar sign ($) to create absolute cell references such as in the formula A1/$B$1. When using AutoFill to copy this formula, the numerator is updated and the denominator is not. To make only the row part absolute use A1/$B1. To make only the column part absolute use A1/B$1 and to toggle between all the possibilities use the F4 key.
| A | B | C | |
| 1 | 5 | 20 | =A1/$B$1 |
| 2 | 10 | =A2/$B$1 | |
| 3 | 15 | =A3/$B$1 |
Often it is convenient to use names rather than cell references in formulas. You can name cells using by selecting the cell and typing the name in the name box where the cell reference usually appears. To set this up go to Tools/Options/Calculations/Workbook options/Accept Labels in Formulas. From then on you can use the names in formulas. Unfortunately, you cannot use the fill handle with named cell. Another method for naming formulas is to use the data table headings. This method allows you to use the fill handle. For example to compute the first distance of 100, enter the formula =car1 rate*car1 time. The remaining distances can be computed using AutoFill.
| rate | time | distance | |
| car1 | 50 | 2 | 100 |
| car2 | 60 | 3 | 180 |
| car3 | 70 | 4 | 280 |
To toggle between viewing all the formulas and their values use press CTRL + ` (grave accent). To see all the formulas on a more permanent basis, go to Tools/Options/View and select formulas.
To select a block, select the upper left cell and move the mouse to the lower right cell and use a Shift, Left-Click. To select nonadjacent blocks of cells hold down the Ctrl key. To select a row or column, click the row number or column letter. To select a range of rows or letters, drag through the appropriate row numbers or column letters.
You can use the scissors, paper and clipboard icons to copy or move text. Or you can use the mouse as detailed below. To move a block of cells, select them and drag and drop. To copy a block of cells repeat the previous procedure except hold down the Ctrl key as you drag and drop. To insert a block of cells in a densely populated area without deleting existing contents repeat the procedure with the Shift key. To delete an entire row or column go to Edit/Delete. To insert rows and columns go to Insert/Rows or Insert/Columns. The Find and Replace utility is useful for long worksheets.
Format cells by selecting the cells and going to the Format/Cells dialog box or by using the formatting toolbar. This allows you to format numbers as currency, with commas, as percentages, in scientific notation,.... You can also select font families, sizes and characteristics. You can use the Format Painter to copy the formatting of a particular cell to as many cells as you wish. Change the default alignment within cells using the Align Left, Align Center and Align Right icons. Format tables by going to the Format/AutoFormat dialog box and selecting a predefined table format. To adjust column widths (row widths) put mouse between the column letters (row numbers) and drag to manually adjust or double-click to AutoFit. It is possible to hide the data in a row or column by adjusting the heights or widths. To unhide a column (row) drag the mouse over the surrounding columns (rows) and choose Unhide from the shortcut menu. Excel 2002 supports conditional formatting via the Format/Conditional Formatting dialog box. As an example conditional formatting allows you to color the contents of a cell based on the numbers inside.
Many different types of charts can be created using the chart wizard. To use the wizard first select the data for the chart. Click the Chart Wizard Button and use the dialog boxes to create a chart. Once the chart is created you can use the Chart toolbar to make changes to it such as background colors and effects, gridlines, labels, adding additional data, ... . The Chart Objects drop-down button is a convenient method for choosing the part of the chart you wish to modify. Another method is to use the left mouse button to select the part of the chart you wish to work with and then right-click to pull up a shortcut menu. Some of the objects can be resized and moved. Charts containing three dimensional objects can be rotated to offer a new perspective.
Enter column headings and one sample record. Be sure to boldface the column headings. If you do not format the column headings beyond the default provided by Excel, the program will not be able to distinguish between the field names and the first row. Go to Data/Form on the menu and Excel will create a Data Form. Any time you wish to see the Data Form go to Data/Form.
Use the Data Form to add new records. Click New to add a new record. Enter information into the first field. Use the Tab key to move to the next field. Use the Enter key after finishing the last field.
Use the scroll bar located on the Data Form to move between records. For a large database use the Criteria button. Enter a criteria such as C* (asterisk is a wildcard) to limit your search to names starting with C or >=50000 to limit your search to incomes greater than 50000.
Choose Data/Sort on the menu bar. Select the field(s) you want to sort on.
To hide all the records except those you wish to see go to Data/Filter/AutoFilter. Excel then adds drop-down list buttons to every cell with a field name in that row. If the options presented are not sufficient, select Custom and set up a more advanced filter.
To make the grid appear larger use the Zoom feature and set set the desired level of magnification. Sometime you may want to compare two separate sections of a worksheet. To split the windows horizontally (vertically) pull the split bar located at the top of the vertical scroll bar (right of the horizontal scroll bar). To make the panes disappear, double-click on the split bar. To display several worksheets in a single window, go to Window/New and click the tab of the worksheet you want displayed in this window. Repeat for all the worksheets you want in the window and then go to Window/Arrange. Close the windows using the Close button and go to Window/Arrange to fill the gap created by closing the window. To add electronic comments to a cell, select a cell and go to Insert/Comment. To see/hide the comments go to View/Comments. To correct all you spelling errors go to Tools/Spelling. To put the same information on several worksheets select the worksheets using the mouse and by holding down the Ctrl key. Then any editing will take place on all the selected sheets simultaneously.
Activities
|
| Table of Contents | | Previous | | Next |