Excel 2007, Getting started with Excel

 

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.