Excel

1. Excel Spreadsheet basics

2. Excel Sheets and Books • Spreadsheet: tool to analyze, chart and manage data for personal, business and financial use • Worksheet: individual sheet within a workbook, default name Sheet1, • default total 3 sheets • Workbook: spreadsheet file, default name Book1, extension .xls

3. Interface • The spreadsheet is laid out as a grid of rows and columns. • Columns are vertical and are labeled with letters. • Rows are horizontal and are labeled with numbers. • Cells are the intersection of columns and rows.

4. The Excel Window • The name box • The active cell • The formula bar • Sheet tabs

5. The Cell • The basic unit of a spreadsheet is a cell, it is rectangular shaped. • Cells are used as containers. • A cell is referenced by letter/number, such as: A3, K908 • Numbers, text or formulas can be stored in a cell.

6. Entering Data into a Cell • Data is entered into the active cell by typing or pasting it. • The data appears both in the cell and in the formula bar • Pressing Enter, Tab or an arrow key completes the entry. • No other work can be done until the entry of data into a cell is completed. • Editing cells • Changing the data stored in a cell • go to the active cell, double click in cell, or hit F2 or go to the formula bar

7. Text & Number Data Types • Excel determines if the data entered is numeric, text or formula. • If the data entered is only numbers (in correct form) the data is numeric. • If the data contains any non number characters the data is treated as text. • If the data entry starts with an “=“ the data is a formula.

8. Formula Data Type • A formula is a math expression the spreadsheet calculates and show the result of in the cell where the formula is stored. • All formulas begin with an “=“ sign. • The math operators are: • Multiplication * Division / • Addition + Subtraction –

9. Formula Example • Start a formula with the equal sign • Use cell references to add the cells • If you use the cell reference instead of the number in the cell you can change the number and the answer will change • Note that Excel color codes the cells involved.

10. Formatting • Formatting is used to make it possible to "see" data. • Organizing data in columns and rows helps but you need to use more • color, fonts and size to "direct the eyes".

11. Autocalc Σ • Uses functions and ranges to make formulas. • A function is a math machine that produces answers. • Use because the math is very high level and we don't know how to do it. • Use for the connivance of it. • Data given to functions is called arguments • Ranges are groups of cells that can be used to provide numbers (arguments) to a function.

12. Replication (copy/paste or fill) • When should a copy not be the same as the original? • Should the formula for B3 be a “copy” of the formula in A3 that adds the 2 numbers above the formula? Add numbers above Add numbers above Yes, but different because “the above” is different cells.

13. Replication (copy/paste or fill) • If you copy a formula and paste it in another cell. • The cell references in the formula can change • to similar references based on the new position. • If A3 contained =A1+A2 it would become =B1+B2 if pasted into B3 • Cell references that change are called relative.

14. Replicate (relative vs. absolute) • Some times you don’t want a portion of the formula to be changed. • The formula in C3 should use the same tax as the formula in C2

15. Replicate (relative vs. absolute) • Cell references that do not change when copied are absolute. • A $ placed in front of a part of a cell reference makes that part absolute. • For example, in =A1*A2, • if you did not want the 2 in A2 to change when copied, • place a $ in front of the part of 2, A$2.

16. Percent Calculations • To calculate a percentage • Multiple the number by the percent (as decimal) • For example 5% of 25 becomes 25*0.05 • To increase a number by a percentage • Add the number calculated in the step above to the original number • For example increase 25 by 5% becomes 25 + 25*0.05

17. Percent Formulas • Let the calculation from the previous slide:25 + 25 * 0.05 be replaced with: A + A * B where A=25 & B=0.05 becomes • A + AB • A(1 + B) factor A • Note: Excel will not accept the above form. • A and B must be cell references. • The multiplication must be explicitly stated. • For example: F3*(1+B2).

18. Charts • Charts allow numbers to be shown (represented) pictorially. • Multiple chart types are available in Excel – column, bar, line, pie, etc. • A chart wizard leads you thru the steps of making a chart • A chart toolbar and a chart menu are available to help in formatting the chart

19. Planning a Chart • Decide what data should be used in the chart. • Ask yourself these questions • What is my purpose in making the chart? • What numbers measure my purpose? • Create a table in Excel to arrange the numbers. • Put all measurements of the same type in one column. • Identify the measurement type with a column header. • List the subject measured alongside each measurement • If several measurements are involved, list each in its own column.

20. Planning a Chart (cont.) • Measurements – duh and ah duh • Subjects – abc, def, hij, klm, nop, qrs, tuv, wxy

21. Making the Chart Chart Wizard Button • Highlite the measurements and their column headers • Click the Chart Wizard button • Choose the chart type best suited

22. Making the Chart (cont.) • Use the series tab to set the X axis labels. Click in box and highlite the subject labels.

23. Making the Chart (cont.) • Set chart options and • Locate the chart

24. Modifying the Chart • Change the data in the table and the charts changes automatically • Use the chart menu or the chart toolbar

25. Interest • Simple interest uses the formula, A(1+B) • A is the capital • B is the interest rate. • Compound interest modifies the above to A(1+B)C • A & B are as above • C is the number of compoundings. • Adjust the rate if it is not on the same schedule as the compounds. For example, • interest rates are often quoted as annual rates, • compoundings may occur monthly, • the interest rate needs to be divided by 12.

26. Pmt Function • Determines monthly loan payments • Needs rate, amount and number of payments • Click on fxin the formula bar • select financial and pmt in dialog box fields • click “help on this function” link • Help offers an example of use • Modify the example to your use

27. If function - Alternatives • Allows program to choose between alternatives. • If such’n’such is true do this else do that • Computer can choose to do this or do that • Make decision based on the truth of such’n’such If such’n’such turns out to be true do this But If such’n’such turns out to be false do that

28. If Function - Excel If function used to determine driving eligibility based upon age.

29. If function – Excel • =If(condition,true,false) • Condition is the comparison involved in the statement of fact. • Data placed in the true section appears in the cell if the condition is true • Data placed in the false section appears in the cell if the condition is false • = if(B2=>C$2,”Yes”,”No”) • Yes or no appears in the cell

30. ,if(A2>=D5,C5 ,C6)))) ,if(A2>=D4,C4 ,if(A2>=D3,C3 =if(A2>=D2,C2 Nested if • To use a nested “if” you need • to recognize the boundaries • (floor / ceiling) • use only one boundary per level • use either all floors or all ceilings • above the floor / below the ceiling • check boundaries in an order to eliminate levels • start at one end and work to the other ,if(A2>=D5,”D” ,”F”)))) ,if(A2>=D4,”C” ,if(A2>=D3,”B” =if(A2>=D2,”A”