Excel 2007 ® Business and Personal Finances

1. Excel 2007® Business and Personal Finances How can Excel 2007 help you plan a project and chart its progress?

2. 1. Lesson 4: Analyze Data The formulas, charts, and graphs in Excel 2007 will help you analyze, develop, and display the data needed for your project without having to make manual calculations.

3. Lesson 4: Analyze Data View This Presentation to Answer the Following Questions: •How can I sort and filter data? •How can I tell Excel what mathematical operation to perform? •What are relative and absolute references? •How can I create, modify, and position charts? •What is the PMT function? •What vocabulary words should I review?

4. 2. Lesson 4: Analyze Data To sort and filter data, use the Sort A to Z button, located on the Ribbon. The Sort A to Z button arranges numbers in increasing order or alphabetizes words. To sort in decreasing numerical order or in reverse alphabetical order, use Sort Z to A.

5. 3. Lesson 4: Analyze Data To see only the data you want to see, use the AutoFiltercommand to filter the worksheet. To use AutoFilter, you must first click on a cell in the range, or group of cells, that you want to filter.

6. 4. Lesson 4: Analyze Data Some worksheets contain so much data that is difficult to understand the data or chart the progress of a project. Being able to filter data so that you can see only the information you need is helpful to see that you are getting closer to you goal. It is easier to manage your time and stick to a plan for a project if you chart your progress.

7. 5. Lesson 4: Analyze Data You can tell Excel which numbers to use and what mathematical operation to perform by creating a formula. Every formula begins with an equal sign (=) and includes values or cell references.

8. 6. Lesson 4: Analyze Data You can edit the formula in the formula bar. You can also edit a formula by double-clicking the cell that contains the formula.

9. 7. Lesson 4: Analyze Data To save time, create a formula by choosing a function, or preset formula, from a list. After you have chosen the function you want to use, fill in the correct numbers or cell references.

10. 8. Lesson 4: Analyze Data To copy a formula from cell to cell, use the Copy and Paste commands. Excel will automatically modify the cell references in the new formula so it calculates correctly.

11. 9. Lesson 4: Analyze Data A relative reference is a cell reference that changes when a formula is copied into a new location. A relative reference shows how a formula is relative to the data in a particular cell. If the formula moves, the reference moves with it. A relative reference is written with the column letter and row number, such as B2.

12. 10. Lesson 4: Analyze Data An absolute referencedoes not change when you copy the formula to a new location. An absolute reference is a locked cell that maintains a constant reference to the original cell when copied and pasted.

13. 11. Lesson 4: Analyze Data Use an absolute reference when more than one formula should refer to the same cell. To write an absolute reference, place a dollar sign ($) in front of both the column letter and the row number. The dollar signs lock the formula to the cell $E$12.

14. Lesson 4: Analyze Data Academic Skills Check Why is it important to check your progress toward a goal? Answers may include: It is easier to stick manage a project and stick to a plan if you chart your progress so that you can see that you are getting closer to your goal.

15. Lesson 4: Analyze Data Tech Check How do you sort numbers in increasing order in Excel? Answer: Use the Sort A to Z button to sort numbers in increasing order in Excel.

16. Lesson 4: Analyze Data Tech Check How can you create formulas in Excel? Answer: To create formulas in Excel, key information into the cell or formula bar, or choose a preset formula from a list of functions.

17. 12. Lesson 4: Analyze Data A chart is a graphic that organizes data visually. Using a graphic such as a chart or graph allows you to organize data so it can be evaluated at a glance. An upward line on a graph shows that sales are increasing, while a downward line shows they are declining.

18. 13. Lesson 4: Analyze Data The Charts group allows you to create charts based on the data that you select. Common types of charts include bar charts, column charts, line charts, and pie charts.

19. 14. Lesson 4: Analyze Data Use Chart Tools to make the data easier for others to read or to make your point. You can change the look of the chart to highlight sales trends, salary increases, or the decrease of a loan balance as payments are made.

20. Lesson 4: Analyze Data Another way to modify a chart is to change the chart type. Give a clustered column chart a completely different look by changing its chart type to a line, bar, or area chart.

21. Lesson 4: Analyze Data Use a bar chart to compare items to each other.

22. Lesson 4: Analyze Data Use a line chart to help you compare sales over time.

23. 15. Lesson 4: Analyze Data To avoid cluttering a worksheet with numbers and charts, put the data in one sheet and move the accompanying charts to another sheet in the same workbook.

24. Lesson 4: Analyze Data Formulas in Excel can help you to incorporate items like monthly payments and interest rates into a budget. To create an effective budget, you need to know both your income, or how much you can spend, and expenses. Be sure to consider taxes, interest rates, and other expenses.

25. 16. Lesson 4: Analyze Data The PMT function calculates the monthly payment for a loan using the amount of the loan, the interest rate, and the number of payments. The PMT function is very useful if you want to create a budget.

26. 17. Lesson 4: Analyze Data The Function Arguments dialog box will help you calculate your monthly loan payments with PMT. Rate is the interest rate. Nperis total number of payments. Pvis present value.

27. 18. Lesson 4: Analyze Data When the results from the PMT function are red and in parentheses, they represent money owed on the loan. In finance, negative amounts are usually represented in parentheses, and in red.

28. Lesson 4: Analyze Data Academic Skills Check What do you need to know to create an effective budget? Answers may include: You need to know both income and expenses to create an effective budget.

29. Lesson 4: Analyze Data Academic Skills Check Why is it sometimes better to present data as a graphic than as numbers or words? Answers may include: Using a graphic such as a chart or graph allows you to organize data so that it can be evaluated at a glance, making it easier to understand.

30. Lesson 4: Analyze Data Academic Skills Check How are negative numbers usually represented in finance? Answer: In finance, negative amounts are usually represented in parentheses, and in red.

31. Lesson 4: Analyze Data Tech Check What are some of the more common types of charts? Answer: Common types of charts include bar charts, column charts, line charts, and pie charts.

32. Lesson 4: Analyze Data Tech Check What are some of the ways you can use to modify charts? Answer: You can use Chart Tools to change the way the chart looks or you can change the chart type, for example, from a bar chart to a line chart.

33. chart 19. Lesson 4: Analyze Data Vocabulary Review chart A graphic that organizes data visually so that you can compare different kinds of data or evaluate how data changes over time.

34. 20. Lesson 4: Analyze Data Vocabulary Review range A group of cells.

35. 21. Lesson 4: Analyze Data Vocabulary Review sort To put a list of data in ascending or descending order.

36. 22. Lesson 4: Analyze Data Vocabulary Review filter A feature that allows you to use only the data you need.

37. 23. Lesson 4: Analyze Data Vocabulary Review relative reference A cell reference that changes when a formula is copied to a new location.

38. 24. Lesson 4: Analyze Data Vocabulary Review absolute reference A cell reference that doesn’t change when a formula is copied to a new location.

39. 25. Lesson 4: Analyze Data Vocabulary Review function A preset formula that is used to solve an equation.

40. 26. Lesson 4: Analyze Data Vocabulary Review PMT A function that calculates payments for a loan based on interest rate, number of payments, and the amount of the loan.