Transcript Creating worksheets in Microsoft Excel

Excel Terms
Worksheet: a grid of rows and columns
Columns – labeled A, B, C, then AA, BB, etc. – 256 columns
Rows – numbered 1, 2, 3 through 65,536
Cell – the intersection of a row and a column – B5 is the
intersection of Column B, Row 5. Cells can contain either labels,
numbers or formulas.
Workbooks – collections of worksheets in the same file.
Constant – a numeric or text value you type directly into a cell
Moving Around
Tab key: moves the active cell to the right
Shift + Tab: moves the active cell to the left
Enter key: moves the active cell down
Shift + Enter: moves the active cell up
Control + Home: Returns cursor to cell A1
Formatting - Margins
Setting Margins, Nonprinting Grids, etc.
•
Click on FILE
•
Click on PAGE SETUP
•
Choose the appropriate tab and make your selection
Formatting – Wrapping Text
Wrapping Text in the Cell
1. Highlight the cell(s)
2.
Click on FORMAT
3.
Click on CELLS
4.
Click on the ALIGNMENT tab
5.
Click in the box next to WRAP TEXT
Formatting - Numbers
Formatting Numbers
1.
Highlight the cell(s)
2.
Click on FORMAT
3.
Click on CELLS
4.
Click on NUMBER
5.
Choose the category and type of formatting you desire
Formatting - Borders
Adding a Border
1.
Highlight the cell(s)
2.
Click on FORMAT
3.
Click on CELLS
4.
Click on the BORDER tab
5.
Choose which type of border you like
6.
Choose the line style and color
7.
Click on OK
Renaming a Sheet
1. Double-click on the SHEET tab
2. Type in the new name
3. Press ENTER
Moving a Sheet
1. Left click and hold down the mouse button. A down arrow
and “sheet of paper” will appear. Move to the new location
and release the mouse button
Copying a Sheet
1. Right click on the sheet tab
2. Click on MOVE or COPY
3. Choose the appropriate workbook
4. Choose which sheet you want to place it before
5. Make sure you click in the box next to CREATE A COPY
6. Click OK
Deleting a Sheet
1. Right click on the sheet tab
2. Click on DELETE
3. Click OK in the confirmation dialogue box
Operators
Operators are signs that specify the kind of operation you want
to perform.
+ Addition
% Percent (placed after a number)
-
Subtraction
^ Exponentiation
/
Division
*
Multiplication
Creating a Formula
1. Select the cell where you want the result to appear
2. Type an equal sign = to activate the formula bar
3. Type the formula or insert cell references by selecting the
cells on your worksheet
4. Press ENTER after you have completed the formula
OR: Use the SUM () or FUNCTION WIZARD (fx) buttons
Examples of Excel Formulas
=A2+A3 (A2 and A3 are cell references. The = sign indicate addition)
=(D14-25)+100 (D14 is a cell reference. The – indicates subtraction. The 25 and
100 are numeric constants
=B6/C16 (B6 and C16 are cell references. The / indicates division
=B12*C25 (B12 and C25 are cell references. The * indicates division
=SUM(B6:B18) (Sum is a worksheet function. B6:B18 indicates a range of cells,
designated by the colon :)
The AutoSum Feature
1. Place your cell pointer at the location where you want the sum
to appear
2. Press the AutoSum button on the toolbar. Excel puts an outline
around suggested cells
3. If the suggested cells are correct, press ENTER. If not correct,
drag through the correct range and then press ENTER. The
sum will appear in the highlighted cell in your worksheet
The Function Wizard
The Function Wizard provides a list of commonly used functions.
1. Highlight the needed cells
2. Click on the Function Wizard button in the toolbar
3. Select the Function Category from the list
Using Chart Wizard
1. Highlight the needed cells
2. Click the ChartWizard button
3. Choose a Chart Type and Subtype and press NEXT
4. Select the desired options and press NEXT
5. Add a Chart Title, if desired. Choose other desired options and
press NEXT
6. On ChartWizard Step 4 of 4, select whether you want the chart to
be placed on a new worksheet or as an object on the current
worksheet. Press FINISH
Calculating Loan Payments
Create a worksheet with the following information:
A1 – Loan Amount
B1 – 20000
A2 – Down Payment
B2 – 2000
A3 – Interest Rate
B3 - .08
A4 – Term
B4 – 60
A5 - Payment
B5 – PMT(b3/12,b4,b1-b2)
The result is a monthly payment of $365.98. To calculate your
own numbers, change the amounts in the Loan Amount (B1),
Down Payment (B2), Interest Rate (B3) and Term (B4 – in
months). The PMT syntax is =PMT(annual interest rate/12
months,term in months,loan amount-down payment)