PowerPoint Presentation to Accompany GO! with Microsoft ® Excel 2007 Comprehensive 1e Chapter 8

1. PowerPoint Presentation to Accompany GO! with Microsoft® Excel 2007 Comprehensive 1e Chapter 8 Creating Macros, Using Depreciation and Conditional Functions, and Creating PivotTables and PivotChart Reports

2. Objectives • Create and Run a Macro • Apply Depreciation Functions • Evaluate Worksheet Data with Conditional IF Functions • Create a PivotTable and PivotChart

3. Create and Run a Macro • A macro is a series of commands grouped together as a single command. • Examples include: • Selections from menus and dialog boxes • Keystrokes • Clicks on toolbar buttons • Macro tools are located on the Developer tab. • Do not normally show up on the Ribbon

4. Create and Run a Macro Popular option Excel Options dialog box Show Developer tab in the Ribbon checkbox

5. Create and Run a Macro • The Code group displays the buttons for macros. • Only displays when macros are available • The Trust Center contains security and privacy settings for Microsoft Office 2007 applications.

6. Create and Run a Macro • To display the Trust Center • Click on the Developer tab • Look in the Code group • Click on the Macro Security button • A macro virus is unauthorized code attached to a macro. • May damage or erase files

7. Create and Run a Macro

8. Create and Run a Macro • Macro recorder: Every keystroke and mouse click is recorded and saved with a macro. • A macro can be run (replayed) in any cell. • Each macro needs to be named. • The first character must be a letter. • Spaces are not allowed. • No cell references.

9. Create and Run a Macro • Save macro options are This Workbook, New Workbook, or Personal Macro Workbook. • Macros are stored in a module using the Visual Basic for Applications (VBA) program. • VBA is a programming language. • Each macro is stored in a different module. • They are numbered consecutively.

10. Create and Run a Macro VBA code Microsoft Visual Basic window

11. Apply Depreciation Functions • Depreciation is the amount that an asset decreases in value over time. • Long term assets are expected to last longer than one year. • Examples: car, property owned by a firm • Depreciation is an expense. • There are several methods for calculating the amount of depreciation.

12. Apply Depreciation Functions

13. Apply Depreciation Functions • Types of depreciation: • Straight line • Equal amount each year • Sum of years’ digits • Will depreciate faster during early years • Declining balance • Will depreciate faster during early years • The relative reference feature is used when the actions in the macro are relative to the selected cell.

14. Evaluate Worksheet Data with Conditional IF Functions • Conditional functions test whether a condition is true or false by using logical or comparison expressions: • COUNTIF • SUMIF • AVERAGEIF • COUNTIFS • SUMIFS

15. Evaluate Worksheet Data with Conditional IF Functions • COUNTIF • Counts items within a range that match a specific condition • SUMIF • Adds the cells in a range that meet a specific condition • AVERAGEIF • Calculates the average of a range of data that meets a specified condition

16. Evaluate Worksheet Data with Conditional IF Functions • COUNTIFS • Counts the number of items in a list when there is more than one condition to be met • SUMIFS • Adds data that matches two or more criteria

17. Create a PivotTable and PivotChart • A PivotTable report displays large amounts of numerical data in different ways that can be analyzed. • The source data is the range of the table. • The report will be created in the PivotTable report layout area. • The PivotTable Field List displays each of the column titles (fields).

18. Create a PivotTable and PivotChart Fields section PivotTable Tools tab Pivot Report Layout Area Areas section

19. Create a PivotTable and PivotChart • The Areas section is used to place field names. • Fields that do not contain numbers display in the Row Labels area. • Fields that contain numbers display in the Values area. • Used to summarize data in the PivotTable

20. Create a PivotTable and PivotChart • Data in a PivotTable is linked to the original worksheet and can be updated using the Refresh command. • Custom calculations are calculations that are created in the PivotTable. • You can sort data in PivotTables.

21. Create a PivotTable and PivotChart • A PivotChart visually displays the data. • A PivotChart is interactive and the object changes as the data changes. • Data can be quickly moved so the chart displays different trends.

22. Covered Objectives • Create and Run a Macro • Apply Depreciation Functions • Evaluate Worksheet Data with Conditional IF Functions • Create a PivotTable and PivotChart