1. Understand the use of spreadsheets and Excel.
2. Learn the parts of the Excel window.
3. Scroll through a worksheet and navigate between worksheets.
4. Create and save a workbook file.
5. Enter text, numbers, and dates into a worksheet
6. Resize,insert, and remove columns and rows
7. Select and move cell ranges
8.Insert formulas and functions
9. Insert, delete, move and rename worksheets
10. Work with editing tools
11. Preview and print a workbook
Important Points:
1. Spreadsheet is a collection of text and numbers laid out in a
rectangular grid used l for analyzing and reporting information. budgeting, what
if, inventory.
2. Workbook is 256 sheets. A sheet is 256 columns A to IV and 65536 rows.
Two types of sheets. Worksheet contain data, chart sheet contain an excel chart.
Each
3. Excel Window components:
| Feature | Description |
| Office Button | A button that provides access to workbook level features and program settings |
| Quick Access Toolbar | a bar that displays the name of the active workbook and the excel program name |
| Ribbon | The main set of commands organized by task into tabs and groups |
| Workbook window | Window that displays an excel workbook |
| Vertical Scroll bar | a scroll bar used to scroll vertically through the workbook window |
| Horizontal scroll bar | a scroll bar used to scroll horizontally through the workbook window |
| Zoom Controls | Control for magnifying and shrinking the content displays in the active workbook window |
| View Shortcuts | Button used to change how the worksheet content is displayed-Normal, page Layout, page break preview view |
| Active cell | The cell in which you are currently working. |
| Column headings | The letters that appear along the top of the worksheet window A to IV |
| Formula bar | The bar located immediately below the toolbars that displays the contents of the active cell. |
| Name box | The box that displays the cell reference, or column and row location of the active cell in the workbook. |
| Row headings | the numbers that appear along the left side of the worksheet window. 1 to 65536 |
| Select All button | Square button located at the intersection of the column and row heading that you click to select the entire content of the workbook. |
| Sheet tabs | Tabs located at the bottom of each worksheet in the workbook that display the names of the sheets. |
| Sheet Tab scrolling buttons | Buttons to scroll the list of sheet tabs in the workbook.. |
Excel Navigation keys
| Press | to move the active cell |
| up,down,left,right arrow keys | Up, down,left,right one cell |
| Home | to column A of the current row |
| Ctrl-home | to cell A1 |
| Ctrl-End | to the last cell in the worksheet that contain data |
| Enter | down one row or to the start of the next row of data |
| Shift-Enter | Up one row |
| Tab | one column to the right |
| Shift-Tab | one column to the left |
| Page up, Page Down | up or down one screen |
| Ctrl-Page up, ctrl-page down | To the previous or next sheet in the workbook |
4. go to key. click the home tab, in the editing group click find and
select, click go to, type in the cell reference, click ok.
5. Developing a planning analysis worksheet:
a. What is goal of the worksheet. What problems do you want to solve?
b. What are the desired results? output
c. What is required data input?
d. What are the required data calculations to get the results.
e. What form should; my solution take?
6. Data in worksheet: text string, numeric values, dates/time and
calculated values.
7. The formula bar displays the content of the active cell.
8. Formula must begin with =.
9. Entering multiple line of text in cell: enter line of text; Alt-Enter
gives you new line in cell.
9a. Dates are treated as numbers: Allowed formats: mm/dd/yyyy- mm/dd/yy -
mm-dd-yyyy - Month dd, yyyy - dd-month-yy:
10. Formula is mathematical expression that calculate a value.
11. Arithmetic operators - +, -, *, /.
12. Order of precedence - Exponential (^), Multiplication (*) or Division
(/), addition (+) and subtraction (-)
13. Parentheses ( ) is used to change the order of operations.
14. Boolean operators And, Or, XOR, NOT
15. Relational operators >, <, >=, <=, <>, =
16. AutoComplete - once text is entered into cell, excel anticipates the
text you are about to enter in another cell.
16a. Excel displays only as much text as fits into the cell truncating
the rest of the text entry.
16b. A pixel is a single point on a computer monitor whereas a
point is 1/72 of an inch.
17. Changing size of column or row.
a. click column or row heading,click home tab on the ribbon, click format button
in cells group, click column or row,width or height, enter the width or height
you want, click ok.
b. drag the column or row heading dividing line to the right or up to increase
the column or row height or width.
c. double click the column or row heading to auto adjust.
18. Auto Fitting eliminates any empty space by matching the
column to the width of its longest cell entry.
19. Inserting a column or row.
a. select the column or row.
b. in the cells group on the home tab, click insert, a new column or row will be
inserted.
20. Save workbook - click the save button on the quick access toolbar.
Navigate to where you want to save the file, put in the name you want, and
verify that excel workbook appears in the save as type box. click save.
| Format | Description |
| Microsoft Excel 4.0,5.0,97,2000,2002,2003 | Save workbook in earlier version of Excel |
| Single file web page | Save the workbook as a single web page file that can be read by IE 4.0 and later |
| Template | save the workbook as a template to be used for creating other Excel workbooks. |
| Web Page | Saves the workbook in separate files that are used as the basis for a web site in a format that is readable by most browsers. |
| XML spreadsheet | Save the workbook as an XML document. |
21. Cell range is a group of worksheet cells.
22. Range reference indicates the location and size of a cell range.
23. Select adjacent range - click on cell in upper left of range, drag
and select cells.
24. Nonadjacent range - select first range, use ctrl-click to select next
nonadjacent range.
25. Move or copy a cell or range of cells - highlight cells, move pointer
to border of selection, Press and hold the left mouse button, drag to new place.
26. AutoSum feature is a quick and convenient way to enter the SUM
function. Select the cell that you want a sum, click the AutoSum button on the
toolbar..
27. Insert Row or Column: select cell where you want to insert. Click
insert on menu bar. click rows or columns. In insert dialog box make selection.
28. Delete row or column: click the row or column header, Edit, Delete.
29. Edit mode is used to edit cell contents. Enter edit mode by double
clicking cell.
30. Undo and redo can be used for changing actions.
31. Working with formulas - formula is an expression that returns a value- A
formula is written using arithmetic, relational, and boolean operators that
combine different values and follow the rules of order of precedence.
Arithmetic
| Mathematical Operator | Use |
| ^ - exponential | 3^3 = 27 |
| * - multiply | 3*3 = 9 |
| / - divide | 5/2= 2.5 |
| \ - integer division | 5\2 = 2 |
| mod - modular division | 5 mod 2 = 1 |
| + - add | 5 +2 = 7 |
| - - subtract | 5 - 2= 3 |
| ( ) parentheses | ( execute inside first ) |
Logic
Relational operators.
| > | greater than | A > B |
| < | less than | A < B |
| >= | greater than or equal to | A >= B |
| <= | less that or equal to | A <= B |
| = | equal to | A = B |
| <> | not | <> A |
Boolean Operators
AND, OR, XOR, NOT
AND
| A | B | C | Result |
| True | True | True | True |
| False | True | True | False |
| False | False | True | False |
| False | False | False | False |
OR
| A | B | C | Result |
| True | True | True | True |
| False | True | True | True |
| False | False | True | True |
| False | False | False | False |
XOR
|
A |
B | C | Result |
| True | True | True | False |
| False | True | True | True |
| False | False | True | True |
| False | False | False | False |
Not
| A | Result |
| True | False |
| False | True |
Operator Precedence used in evaluating expressions
| High | ^ | Arithmetic |
| Unary + or - | ||
| * / \ mod | ||
| + - | ||
| <, >, <=, >=, =, <> | Relational | |
| NOT | Logical | |
| AND | ||
| Low | OR and XOR |
32. A function is a named operation that returns a value by
supply the argument of the function which can be a cell or a range of cells. For
example =sum(b3:b12) will add up the data in the range of cell b3 to b12.
| Do not place important data in a formula because the worksheet displays the formula result rather then the actual formula. |
| Keep formulas simple. Use functions in place of long complex formulas. |
|
Break up formulas to show intermediate results. |
33. Delete a worksheet. select the sheet tab, right-click the sheet
tab, choose delete.
34. Insert a new worksheet: Click insert worksheet tab to the right of
the worksheet Sheet3 tab.
35. Rename worksheet: Double sheet tab, type in new name, hit enter.
36. Reposition worksheet. click sheet tab, click and drag along row of
sheet tabs and release when in position. 37. Undo last action-on the quick
access toolbar, click the undo button
38. Redo last action-on the quick access toolbar, click redo button.
39. Using Find and Replace
a. In the editing group on the home tab, click find and Select button, then
click replace.
40. Spell Checker check word against the programs internal dictionary.
Click cell A!, click the review tab on the ribbon, click the spelling button in
the proofing group, and select the option you want.
41 . Previewing and Printing a Worksheet.
42. Page Layout view shows how the worksheet will appear on the page or
pages sent to the printer. Click the page layout button on the status bar.
43. Page break preview display the location of the different page
breaks within the worksheet.
Click the page break preview button on the status bar.
44. Portrait and Landscape orientation: Click the page layout tab on the
ribbon, in page setup group click the orientation button, Click landscape or
portrait.
45. Printing Formulas. With worksheet active press Ctrl + ` (grave accent
not single quote), This will display the formulas.
46. Printing the workbook- click the office button, click print, click the name
box, click print.