04/ 05/ 04
Microsoft Excel ’97 Quick Reference Guide
Starting Microsoft Excel Click on Start at the bottom left hand side of your screen. Go up to Programs, then click on Microsoft Excel. Working with an Excel Workbook and Worksheet When you open Excel, a new Workbook automatically appears on your screen. A workbook is a file in which your data is stored when you work in Excel. It
contains worksheets that allow you to enter and manipulate data. A workbook can contain up to 255 worksheets, but the default number of worksheets opened in a workbook is 3. There are two toolbars at the top of your screen
The first toolbar with the red arrow is called the Standard Toolbar. It allows you to do the following functions (These are listed in order as they appear on your
1
04/ 05/ 04
toolbar. You can hold the mouse under any of the buttons on the toolbar to see what function it performs). • • • • • • • • • • • • • • • • • • • • • Open a new workbook Open an existing workbook Print a worksheet Print Preview a worksheet Spell Check a workbook Cut a worksheet Copy a worksheet Paste a worksheet Undo an operation on a worksheet Redo an operation on a worksheet Insert a hyperlink onto a worksheet Web Toolbar Autosum Paste Function Sort data ascending Sort data descending Chart Wizard Map Drawing Zoom Office Assistant / Help
The second toolbar with the pink arrow is the Formatting Toolbar it allows you to format text. It allows you to do the following functions: (These are listed in order as they appear on your toolbar. You can hold the mouse under any of the buttons o n the toolbar to see what function it performs). • • • • • • • • • • • • • • Font Type Font Size Bold Italicize Underline Align left Align center Align right Merge and Center Currency Style Percent Style Comma Style Increase decimal Decrease decimal
2
04/ 05/ 04
• • • • •
Decrease indent Increase indent Borders Fill Color Font Color
The formula bar noted by the blue arrow below allows you to enter formulas to perform calculations on the data in cells.
General Information about the Worksheet A worksheet makes up a workbook and is used to list and analyze data. A worksheet contains columns and rows. Columns are lettered and rows are numbered. See diagram below:
Row – Rows are numbered
Column – Columns are lettered.
3
04/ 05/ 04
A cell is the fundamental storage unit for Excel Data. It includes values and labels.
A cell is where a column and row intersect together.
A value is a number or a formula. A label is text. Each cell has a name reference based on the intersection with the column and row. Adding, Moving, Copying, or Deleting Data To add data to a worksheet, simply select the cell where you want to enter the data and begin typing. When adding numbers to a worksheet, Excel assumes that the number is a positive number. If you wish to enter a negative number, put a minus sign in front of the number. Selecting Data for Moving, Copying, or Deleting To select on cell only: Select the cell that contains the data you need to manipulate and click the cell. To select more than one cell, but not adjacent cells: Hold down the Ctrl key while clicking the cells that contain the data you wish to manipulate. To select adjacent cells: Click one cell and drag the mouse pointer to the last cell you wish to manipulate. To select a whole column: Click on the column letter. To select a whole row: Click on the row number
4
04/ 05/ 04
To select the whole worksheet: Click the button above #1 of Row 1, or hold down the CTRL key and press A. Moving Data To move data from one cell or group of cells to another: Select the cells you wish to move (see instructions in the section “Selecting Data for Moving, Copying, and Deleting). Click on the Scissors icon on the Standard toolbar to cut the data from the cells you have selected.
The data will disappear from those cells and be copied to an invisible clipboard. To put the data back into a new cell or group of cells, click on the Clipboard icon
5
04/ 05/ 04
Copying Data To copy data from one cell or group of cells to another: Select the cells you wish to copy (see instructions in the section “Selecting Data for Moving, Copying and Deleting). Click on the icon next to the scissors icon on the Standard Toolbar.
To copy the information to a new cell or group of cells, click on the Clipboard icon on the standard toolbar.
6
04/ 05/ 04
Deleting Data To delete data from one cell or group of cells to another: Select the cells you wish to delete (see instructions in the section “Selecting Data for Moving, Copying, and Deleting). Once you have selected the cells you wish to delete, you can simply press the delete key on your keyboard. There is another option that allows you to delete the contents of the cell only, not the formatting of the cell. If you wish to clear the contents of the cell or group of cells only, click on Edit at the top of the screen, then click on Clear Contents. Formatting the Worksheet Adjusting the Column Width There are three different ways to adjust the column width in a worksheet. Select the columns in which you wish to increase the width. (Follow the instructions in the section “Selecting Data for Moving, Copying, and Deleting Data”). Once you have selected the columns you wish to increase, click on Format at the top of the screen, and click on Column, then choose Width. The following dialog box will appear:
Type a number in the box until you achieve the desired with, click OK. You can also choose Format from the top of the screen, then click on Column, then choose AutoFit and Excel will determine the appropriate width for the column. Another way to increase the column width is to hold the mouse in-between two columns. The mouse pointer will become a two-sided arrow with the arrows pointing right and left. Once that pointer appears drag the mouse to the right and the column width will be increased.
7
04/ 05/ 04
The last way you can increase the column width is to double click with the mouse between two columns. The width of the column to the left will be increased to fit the text in the column. Adjusting Row Height Select the rows in which you wish to increase the height. (Follow the instructions in the section “Selecting Data for Moving, Copying, and Deleting). Once you have selected the row in which you wish to increase the height, click on Format at the top of the screen, then click on Row, and then choose Height. The following dialog box will appear:
Type a number in until you achieve the appropriate row height. You can also click on Format at the top of the screen, then click on Row, then choose AutoFit and Excel will determine the appropriate height for the row. Another way to increase the row height is to hold the mouse in-between two rows. The mouse pointer will become a two-sided arrow with the arrows pointing right and left. Once that pointer appears drag the mouse down and the row height will be increased. The last way you can increase the row height is to double click with the mouse between two rows. The height of the row to the top will be increased to fit the text in the row. Adding Headers and Footers to a Worksheet Adding Headers to a worksheet To add a header to a worksheet:
8
04/ 05/ 04
Click on View at the top of the screen, then click on Header and Footer. The following dialog box will appear:
Click on Custom Header to add a Header to the worksheet. The following will appear: The blank areas determine where on the worksheet the header will appear. Click in the appropriate blank area and begin typing the header. The header will appear at the top of the screen. Click Ok when you are finished.
Adding Footers to a Worksheet Click on View at the top of the screen, then click on Header and Footer. The following dialog box will appear:
9
04/ 05/ 04
Click on Custom Footer to add a Footer to the worksheet. The following will appear: The blank areas determine where on the worksheet the footer will appear. Click in the appropriate blank area and begin typing the footer. The header will appear at the bottom of the screen. Click Ok when you are finished.
Formatting Text on the Worksheet Text Alignment Microsoft Excel automatically places text to the left of a cell and numbers to the right of a cell. To align text to the left of a cell: Select the cell or cells you wish to left-align. (See instructions in the section “Selecting Data for Moving, Copying and Deleting”). Click on Format, then Cells. The following dialog box will appear:
10
04/ 05/ 04
Click the tab at the top of the box labeled Alignment. The following will appear:
Click the down arrow beside the Horizontal option and change it to Left (Indent), click OK. You can also use the Formatting Toolbar at the top of the screen. Select the cell or cells you wish to left-align and click the button labeled below with the red arrow:
To align text to the right of a cell:
11
04/ 05/ 04
Select the cell or cells you wish to right-align. (See instructions in the section “Selecting Data for Moving, Copying and Deleting”). Click on Format, then Cells. The following dialog box will appear:
Click the tab at the top of the box labeled Alignment. The following will appear:
Click the down arrow beside the Horizontal option and change it to Right, click OK. You can also use the Formatting Toolbar at the top of the screen.
12
04/ 05/ 04
Select the cell or cells you wish to right-align and click the button labeled below with the red arrow:
To align text to the center of a cell: Select the cell or cells you wish to center-align. (See instructions in the section “Selecting Data for Moving, Copying and Deleting”). Click on Format, then Cells. The following dialog box will appear:
Click the tab at the top of the box labeled Alignment. The following will appear:
13
04/ 05/ 04
Click the down arrow beside the Horizontal option and change it to Center, click OK. You can also use the Formatting Toolbar at the top of the screen. Select the cell or cells you wish to center-align and click the button labeled below with the red arrow:
14
04/ 05/ 04
Angling Text To change the angle of text in Excel: Click on Format at the top of the screen, then Cells. The following will appear:
Click on the tab labeled Alignment. The following will appear:
Click the red dot in the circle under Orientation (see above labeled with the red arrow). Drag the mouse around the circle until you get the desired angle in degrees. Click OK when finished.
15
04/ 05/ 04
Indenting Text To indent text in a cell or cells: Select the cell or cells you wish to indent (see instructions in the section “Selecting Data for Moving, Copying, or Deleting.). Click the icon labeled below with the red arrow to increase indent. Click the icon labeled below with the blue arrow to decrease indent.
Number Formats To format a cell or group of cells in a number format: Select the cell or cells you wish to format, (see instructions in the section “Selecting Data for Moving, Copying, and Deleting). Click on Format, then cells, the following dialog box appears:
16
04/ 05/ 04
Under Category, select the type of number formats you want, then click OK. Formulas Formulas are used to calculate values in a worksheet. They are always preceded by an equal sign and always exclude operators. Excel calculates formulas from left to right. It also gives preferences to some operators over others. For example: Excel treats multiplication and division equally. Excel calculates division before addition. Excel treats addition and subtraction equally. You can override operator preference by using parenthesis in the formula. Below are the operator symbols used in Excel: Calculation operators in formulas Operators specify the type of calculation that you want to perform on the elements of a formula. Microsoft Excel includes four different types of calculation operators: arithmetic, comparison, text, and reference. Arithmetic operators perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results. Arithmetic operator + (plus sign) – (minus sign) * (asterisk) / (forward slash) % (percent sign) ^ (caret) Meaning Addition Subtraction Negation Multiplication Division Percent Exponentiation 3+3 3-1 –1 3*3 3/3 20% 3^2 (the same as 3*3) Example
Comparison operators compare two values and then produce the logical value TRUE or FALSE.
17
04/ 05/ 04
Comparison operator = (equal sign) > (greater than sign) < (less than sign) >= (greater than or equal to sign) <= (less than or equal to sign) <> (not equal to sign)
Meaning Equal to Greater than Less than Greater than or equal to Less than or equal to Not equal to
Example A1=B1 A1>B1 A1=B1 A1<=B1 A1<>B1
Functions Functions are simple formulas that are present in Excel. Below is a list of formula Functions in Excel: Function Sum Count Summarizes The sum of the values. This is the default function for numeric source data. The number of items. The Count summary function works the same as the COUNTA worksheet function. Count is the default function for source data other than numbers. The average of the values. The largest value. The smallest value. The product of the values. The number of rows that contain numeric data. The Count Nums summary function works the same as the COUNT worksheet function. An estimate of the standard deviation of a population, where the sample is all of the data to be summarized. The standard deviation of a population, where the population is all of the data to be summarized. An estimate of the variance of a population, where the sample is all of the data to be summarized. The variance of a population, where the population is ll of the data to be summarized.
Average Max Min Product Count Nums
StdDev StdDevp Var Varp
Entering a Formula To enter a formula: Click the cell in which you want to enter the formula.
18
04/ 05/ 04
Type = (an equal sign). If you click the Edit Formula button labeled by the red arrow or the Paste Function labeled by the blue arrow, Excel inserts an equal sign for you.
You can also use the Paste Function button labeled with the blue arrow to choose which function you would like in your formula. Enter the formula into the cell, then press Enter. Entering a Formula with Functions To Enter a formula that contains a function: Click the cell in which you want to enter the formula. To start the formula with the function, click Edit Formula button in the formula bar.
Click the down arrow next to the Functions box .
19
04/ 05/ 04
Click the function you want to add to the formula. If the function does not appear in the list, click More Functions for a list of additional functions. Enter the numeric values, text values, cell references, ranges of cells. When you complete the formula, press ENTER.
For additional questions, please refer to the Microsoft Excel help menu or call Kari Raines at 3-4239.
20