Microsoft Excel “Spreadsheet Applications”

1. Ross Business Technology Applications January 4, 2012 Microsoft Excel “Spreadsheet Applications”

2. Content standards for spreadsheet applications • Explain the uses and advantages of spreadsheets • Explain basic spreadsheet terms • Demonstrate proficiency in creating, saving, opening, and printing a workbook/worksheet • Change worksheet appearance (font, alignment, row height and column width, borders and shading, and page layout) • Utilize editing techniques (cutting, copying, pasting, inserting, deleting, dragging and dropping, sorting and filtering data • Utilize basic formulas (order of operations, auto sum • Apply basic functions MAX/MIN, Average, Count, Sum • Utilize window functions (split panes and freezing titles) • Create charts and templates • Utilize spreadsheet templates

3. Excel is an electronic spreadsheet program that can be used for storing, organizing and manipulating data. Essential Question How do spreadsheet applications simplify data commonly used in business settings? What is Microsoft Excel?

4. Parts of the Microsoft Excel

5. What is a Spreadsheet Go to: http://www.mtgprofessor.com/spreadsheets.htm “Real-World Data” • A grid that organizes data • A spreadsheet, also known as a worksheet, contains rows and columns and is used to record and compare numerical or financial data. • Spreadsheets only existed in paper format, but now they are most likely created and maintained through a software program that displays the numerical information in rows and columns. • Spreadsheets can be used in any area or field that works with numbers and are commonly found in the accounting, sales forecasting, financial analysis, and scientific fields, and budgeting.

6. Active Cell • An active is the cell you are currently working on.

7. Auto sum • A formula that will add up a column of numbers.

8. A number that can be entered into a cell Value

9. Formula • A formula must always starts with “=” signs and what the calculations for each cell

10. Subtraction - minus sign ( - ) Addition - plus sign ( + ) Division - forward slash ( / ) Multiplication - asterisk ( * ) Exponentiation - caret ( ^ ) Mathematical Operators Used in Excel Formulas

11. Basic Mathematical Calculations(Add)

12. Basic Mathematical Calculations(Subtract)

13. Basic Mathematical Calculations(Multiply)

14. Basic Mathematical Calculations(Divide)

15. Cell Reference • The column number and the row letter of a cell

16. Each individual box on the spreadsheet. Data is stored in cells in an Excel spreadsheet. Each small rectangle in a spreadsheet is a cell. A cell is the intersection point of a column and a row. Cell can hold numbers, text, or formulas. “Cell” Fact

17. The vertical reference on the spreadsheet Column

18. To fill a cell with color using the paint bucket tool. Fill

19. Fill handle • The dot at the bottom of each cell while it is active

20. The procedure to select certain information in a spreadsheet. It is done to make it easier to focus on data in a large table of data. For Ex.: A phone book is an example of a database that contains information about a person/business, such as name, address, phone number, organized by location (cities). This is a simple type of filtering. Filter

21. Filter

22. A visual representation of data Graph – Column, pie, scatter, & line

23. Grid Lines • The horizontal and vertical lines on the spreadsheet

24. Row • The horizontal reference on the spreadsheet

25. Selecting • To highlight a set of cells

26. Sheet • One page of a worksheet

27. Sheet tabs • Tabs that identify the worksheets in a workbook

28. Workbook • Many worksheets

29. Worksheet • One page of a spreadsheet

30. What is the cell that you are working on called? What is an individual box on a spreadsheet called? What is a group of worksheets called? What is a grid that organizes data? What is an individual box on a spreadsheet called? QUIZ

31. What is one page of a worksheet? What is a formula that will add up the column of numbers? What is a number that can be entered into a cell? What is something that can be used to compute? QUIZ

32. What is a grid that contains cells? What is the column number and the row letter? What is the dot at the bottom of a cell when it is active? What are horizontal references? What is a tab that identifies a worksheet in a workbook? QUIZ

33. What are vertical references? What do you call it when you highlight a set of cells? QUIZ

34. Autosum Put your mouse in the cell where you want your answer. Then go up to the E shaped icon and click to Autosum column. Fill Click on certain cell and go up and click on the paint bucket and pick a color. Click "No Fill" to turn cell back to white. Format cells Choose format from the menu toolbar. Then follow the menus to change the fonts, colors or alignment. Formulas Type in = and then enter the equation. Graph Go up and click on chart icon to pick different graphs. Gridlines To show the cell lines when printing, go to file, page setup - sheet and click on gridlines. How-To-Do

35. Hiding cells Hide rows by going to Format, Column, Hide. Make corrections Go up to the formula bar and click in the space where you want to correct something. Save work Go up to File - Save as - and name the document. Once it is saved, just click on the save icon after any changes. Sort Click on icon A-Z or Z-A to put column in order. Note: When you want to sort two columns next to each other highlight both columns or it will only organize one column How-To-Do

36. Plan Your Spreadsheet Points to consider • What is the purpose of the spreadsheet? • What information needs to be included? • What headings are needed to explain the information in the spreadsheet? • What is the best layout for the information? in rows or columns?

37. Mathematical Operators • The mathematical operators used in Excel formulas are similar to the ones used in math class. Subtraction - minus sign ( - ) Addition - plus sign ( + or : ) Division - forward slash ( / ) Multiplication - asterisk (* ) Exponentiation - caret (^ )

38. Formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) are equations that perform calculations on values in your worksheet. A formula always starts with an equal sign (=). Create a formula

39. Simple formulas can include values you enter, cell references, or names you have defined. For example, =A1+A2 or =5+2 are simple formulas that add the values in cells A1 and A2 or the values that you specify. Create a formula

40. 1. Click the cell in which you want to enter the formula. 2. Type = (equal sign). 3. To enter the formula, do one of the following: Create a formula

41. Example formula What it does? =5+2 =5-2 =5/2 =5*2 =5^2 Adds 5 and 2 Subtracts 2 from 5 Divides 5 by 2 Multiplies 5 times 2 Raises 5 to the 2nd power Create a formula

42. Writing the Formula • Writing Excel formulas is a little different than the way it is done in math class. • Excel formulas starts with the equal sign ( = ) rather than ending with it. • The equal sign always goes in the cell where you want the formula answer to appear. • The equal sign informs Excel that what follows is part of a formula, and not just a name or a number. • Excel formulas look like this:=3 + 2rather than: 3 + 2 =

43. Cell References in Formulas • The cell reference is a combination of the column letter and row number -- such as A1, B3, or Z345. When writing cell references the column letter always comes first. • So, instead of writing this formula in cell C1:= 3 + 2 write this instead: = A1+A2

44. Pointing involves using the mouse pointer to click on a cell to add its reference to a formula. Pointing is the preferred method for adding cell references to a formula or function because it eliminates the possibility for errors introduced by reading and typing in the cell reference.Pointing allows you to click with your mouse on the cell containing your data to add its cell reference to the formula. Add Cell References Using Pointing

45. Add Cell References Using Pointing 1. After typing an equal sign in cell C1 2. Click on cell A1 with the mouse pointer to enter the cell reference into the formula 3. Type a plus (+) sign 4. Click on cell A2 with the mouse pointer to enter the cell reference into the formula 5. Press the ENTER key on the keyboard • The answer 5 should appear in cell C1.

46. At this time you will be keying a spreadsheet. Sesame Street Company has employed 15 members to be part of the team in order to reach their yearly goal. A few of Sesame Street Company employees represent the company well by reporting to work in a timely manner, are good motivators, and are consider to be assertive employees. They are working 5 hours or more in overtime on a weekly basis. Sesame Street Company has faithful employees. These employees are most likely to be selected to cross-train for supervisory positions. Let’s Practice!

47. Let’s practice! • Overtime Pay Rate is always equals to one time and a half; the same as “1.5” For ex. If you earn $10 per hour and your regular hours work is 40 per week. How much should you get paid per week if you work 40 hours? You worked 45 hours this week. How many hours did your work in overtime?

48. Formulas for Payroll Spreadsheet GROSS PAY = Hourly Rate * Hours Worked FICA Tax = Gross Pay * 7.65% (.0765) Federal Tax = Gross Pay * 12% (.12) State Tax = Gross Pay * 5% (.05) Total Taxes = Fica : Federal : State Net Pay = Gross Pay – Total Taxes

49. Classroom Assignments • Puzzle on Terms • Spreadsheet Vocabulary Quiz • Spreadsheet Elements Worksheet • Creating a Pie, Line, Chart, and Scatter Chart • Create a Payroll Spreadsheet using Formulas • Can I Afford This House Project from “ALEX” Website

50. http://spreadsheets.about.com Reference