Microsoft Office 2010 Excel Level 3
(8 Hours; 8 a.m. – 5 p.m.)

Creating PivotTables and Macros Creating PivotTables (p. 460 - 475) Creating PivotCharts (p. 478 - 479) Changing Macro Security (p. 481) Recording Macros (p. 483) Running Macros (p. 487) Assigning Macros (p. 488 - 499)  Using Financial Functions and Data Analysis Payment Function (p. 506) Future Value Function (p. 506) Goal Seek (p. 511) Solver  (p. 513)

Advanced Functions and Auditing 3-D Cell References in Formulas (p. 536) Introducing Lookup Functions (p. 539) Creating Formulas with Criteria  IF Functions (p. 547 - 548) Logical Functions is Formulas   (p. 549 – 550) Tracing Formulas (p. 561- 562) Using Advanced Formatting and Analysis Tools Working with Grouped Worksheets (p. 586) Consolidating Worksheet Data (p. 590) Working with Data Validation (p. 594 - 597) Circling Invalid Data (p. 601) Remove Duplicate Records (p. 603) Using Data Tables (p. 606) Creating Sparklines in a Cell  (p. 612 – 613)

Creating PivotTables and Macros – Excel has many features to help you perform sophisticated data analyses, such as the PivotTable and the PivotChart.  PivotTables let you summarize worksheet data dynamically to view them in various ways. In this lesson, you will arrange your data with simple drag-and-drop commands and have Excel automatically create summary formulas in the rows and columns.  You also will create PivotCharts to achieve the same power and flexibility for charting data.   Many Excel workbooks are used on a recurring basis.   Often the same tasks are performed in these workbooks over and over.  Excel allows you to create macros to automate repetitive tasks.  In this lesson you will create macros and custom buttons in an Excel workbook.

Using Financial Functions and Data Analysis – Several Excel tools allow you to perform a what-if analysis on worksheet data.  For example, you might ask, “What if our company obtained a loan for 9 percent rather than 8 percent?”  By changing the interest rate used in a formula to various rates, you could see the effect on the monthly loan payment.  Excel’s built-in financial functions may be used for various types of calculations.  In this lesson, you will use the PMT (Payment) function to determine the monthly payment for a new car.  You also will use the FV (Future Value) function to determine the future value of investments. 

Advanced Functions and Auditing – Complex worksheets for decision making often require advanced functions based on the values in other cells.  You may set up detailed worksheets with an identical design for various categories or time periods of a project and summarize the data in a separate worksheet.  In this lesson, you will use 3-D cell references in formulas to create the summary calculations.  The HLOOKUP and VLOOKUP functions help to use one piece of information to find another in a list.   You will use the VLOOKUP function to locate commission rates for each salesperson.  With an IF function, you may flag a cell with a text label, display a value, or perform a calculation when specific criteria are satisfied.  In addition you will learn how to work with formula auditing tools, which are particularly useful in locating errors in complex formulas that are dependent on other formulas.

Using Advanced Formatting and Analysis Tools – In this lesson, you will consolidate data from detailed worksheets by position and category.  Many Excel workbooks are designed by experienced Excel users but are used by individuals with little Excel experience.  Excel’s Data Validation tool can assist users with data entry by forcing values to fall within a specified range or allow you to input data from a drop-down list.  Excel’s Remove Duplicates tool may be used to delete duplicate records when data is imported from different sources. Data tables assist with what-if analyses by adjusting variables in a formula.  You also will create sparklines, or mini charts, to present changing data patterns in cells right next to the worksheet data.