Creating a new workbook Press CTRL-N. Or pull down the File menu and click New, click the General tab, and then double-click the workbook icon.
Saving a workbook When you save a workbook for the first time, you assign a file name and indicate where you want to store the file on your computer's hard disk or in another location. Each time you subsequently save the workbook, Microsoft Excel updates the workbook file with your latest changes.
To save a new, unnamed workbook, pull down the File menu and
click Save As. In the Save in list, select the drive and
folder where you want to save the workbook. To save the workbook in a
new folder, click Create New Folder 
.
In the File name box, type a name for the workbook. You can use
long, descriptive file names. Click Save.
To save a workbook that you are currently working on, click Save
.
Opening an existing workbook To open an existing file, press CTRL-O or pull down the File menu and click Open. Navigate to the file you want to open and double-click on it.
Workbooks In Microsoft Excel, 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.
Worksheets 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.
Sheet tabs 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.
There are three main sources of help for MS Excel: 1) the Help menu; 2) the Office assistant; and 3) the Office Update web site (accessible from the Help menu). I primarily use the Help menu, and I use it rather frequently. I generally prefer to turn off the Office assistant, and I rarely use the web site.
To turn off the Office Assistant, right-click on it and then click Hide.
Click Microsoft Excel Help on the Help menu. If the Assistant is turned on, it appears. If the Assistant is turned off, the Help window appears. There are three ways to get help. I list them here in the order in which I use them most frequently. 1) Click the Index tab and search for specific words or phrases. 2) Click the Contents tab to scroll through a table of contents for Help. 3) Click the Answer Wizard tab to type a question in the Help window.
To see a ScreenTip for a menu command, toolbar button, or screen region, click What's This? on the Help menu, and then click the item you want information about.
To see the name of a toolbar button, rest the pointer over the button until the name appears.
A reference identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula. With references, you can use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on other sheets in the same workbook, to other workbooks, and to data in other programs. References to cells in other workbooks are called external references. References to data in other programs are called remote references.
The A1 reference style By default, Excel uses the A1 reference style, which refers to columns with letters (A through IV, for a total of 256 columns) and refers to rows with numbers (1 through 65536). These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number. For example, D50 refers to the cell at the intersection of column D and row 50. To refer to a range of cells, enter the reference for the cell in the upper-left corner of the range, a colon (:), and then the reference to the cell in the lower-right corner of the range. The following are examples of references.
| To refer to | Use |
|---|---|
| The cell in column A and row 10 | A10 |
| The range of cells in column A and rows 10 through 20 | A10:A20 |
| The range of cells in row 15 and columns B through E | B15:E15 |
| All cells in row 5 | 5:5 |
| All cells in rows 5 through 10 | 5:10 |
| All cells in column H | H:H |
| All cells in columns H through J | H:J |
| The range of cells in columns A through E and rows 10 through 20 | A10:E20 |
Relative vs. absolute references Depending on the task you want to perform in Excel, you can use either relative cell references, which are references to cells relative to the position of the formula, or absolute references, which are cell references that always refer to cells in a specific location. If a dollar sign precedes the letter and/or number, such as $A$1, the column and/or row reference is absolute. Relative references automatically adjust when you copy them, and absolute references don't.
Entering Formulas A formula is an equation that performs operations on worksheet data. Formulas can perform mathematical operations, such as addition and multiplication, or they can compare worksheet values or join text. Formulas can refer to other cells on the same worksheet, cells on other sheets in the same workbook, or cells on sheets in other workbooks. The following example adds 25 to the value in cell B4 and then divides the result by the sum of the values in cells D5, E5, and F5.
The structure or order of the elements in a formula determine the
final result of the calculation. Formulas in Microsoft Excel follow a specific
syntax, or order, that includes an equal sign (=) followed by the elements to be
calculated (the operands), which are separated by calculation operators. Each
operand can be a value that does not change (a constant value), a cell or range
reference, a label, a name, or a worksheet function. The table below
illustrates some of the standard symbols used to represent basic mathematical
operations.
|
Symbols used to represent basic mathematical operations |
||||
|
^ |
* |
/ |
+ |
- |
Excel performs the operations from left to right — according to the order of operator precedence — starting with the equal sign (=). You can control the order of calculation by using parentheses to group operations that should be performed first. For example, the formula at right produces 11 because Excel calculates multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3
In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21.
=(5+2)*3
| To practice your use of syntax, enter each of these expressions as a formula into a worksheet cell and check your results: |
4 x (5 +
5) [(3 + 3 +
6) ٪ 4]2 (23 + 3) + (-10) |
Moving and Copying Formulas There is an important difference between moving a formula from one cell to another and copying a formula from one cell to another. When you move a formula, the cell references within the formula stay the same. When you copy a formula, absolute cell references stay the same, but relative cell references will change.
As an example, enter the values 5 and 10 in cells A1 and A2, respectively. In cells B1 and C1, enter the formula =A1. This formula contains a relative reference to cell A1. From the point of view of B1, the formula refers to the contents of the cell one column to the left. From the point of view of C1, the formula refers to the contents of the cell two columns to the left.
First, move the formula in B1 to B2. Point to a spot on the border of cell B1 (use any spot except the fill handle - the small black square in the lower right of the selection box), then click and drag to move the contents down into cell B2. Next, copy and paste the formula in C1 into C2. Select C1, then click and drag the "fill handle" down into cell C2. The results should look like this.
Obviously, moving and copying produce two very different results. Cell B1 is now empty. Cell B2 contains the formula we moved from cell B1, and since the reference to A1 stayed the same, B2 now displays whatever is contained in A1. Cell C1 is unchanged. Cell C2 contains the formula =A2. When we copied this formula, the reference changed from A1 to A2 (two columns to the left of C2).
Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. For example, the SUM function adds values or ranges of cells, and the PMT function calculates the loan payments based on an interest rate, the length of the loan, and the principal amount of the loan.
Arguments Arguments can be numbers, text, logical values such as TRUE or FALSE, arrays, or cell references. The argument you designate must produce a valid value for that argument. Arguments can also be constants, formulas, or other functions.
Structure The structure of a function begins with the function name, followed by an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis. If the function starts a formula, type an equal sign (=) before the function name.
Download and open the workbook named Excel Lesson 1 Workbook. This first sheet in this workbook contains the heights, in feet and inches, of several fictitious people.
Before we work with the numbers in this file, let's touch up the formatting and add some better labels. Through trial and error, along with help from your instructor, colleagues, and the Help menu, complete the following tasks. Don't worry about making mistakes - that's what the Undo command is for! Besides, you can always download a fresh version of the workbook and start over!
At this point, your sheet should look like this.
Enter the formula to convert feet + inches to centimeters into cell C3 and check it for accuracy. Then copy and paste it into the remaining cells in the column. Through a combination of trial and error and Help, experiment with the following tasks.
Format the heights you converted to display to the nearest centimeter (i.e. no decimal places).
Sort the data according to increasing height in centimeters, then according to decreasing height in centimeters.
Sort the data according to increasing height in feet and inches.
Calculate the average height of the group, in centimeters.
Convert the average height in centimeters into feet + inches.
Calculate the standard deviation for the average height in centimeters.