Microsoft Excel 2019
Advanced
Student Area
Module 9: Exploring Financial Tools and Functions
OBJECTIVES:
- Work with financial functions to analyze loans and investments
- Create an amortization schedule
- Calculate interest and principal payments for a loan or investment
- Perform calculations for an income statement
- Interpolate and extrapolate a series of values
- Calculate a depreciation schedule
- Determine a payback period
- Calculate a net present value
- Calculate an internal rate of return
- Trace a formula error to its source
Module 10: Analyzing Data with Business Intelligence Tools
OBJECTIVES:
- Retrieve data with the Query Editor
- Create and edit a query
- Chart trends and forecast future values
- Add data to the Excel Data Model
- Manage table relations in Power Pivot
- Create PivotTable drawing data from several connected tables
- Drill through a hierarchy of fields
- Create maps with the map chart type
- Create Map presentations with 3D maps
Module 11: Exploring PivotTable Design
OBJECTIVES:
- Change a PivotTable layout
- Display and hide PivotTable grand totals and subtotals
- Sort PivotTable contents
- Filter PivotTable contents
- Group items within a PivotTable field
- Apply calculations to a PivotTable
- Create PivotTable conditional formats
- Manage the PivotTable cache
- Create calculated items and calculated fields
- Analyze PivotTables based on the Data Model
- Create a table measure using DAX
- Retrieve PivotTable data with the GETPIVOTDATA function
- Explore Excel Database functions
Module 12: Developing an Excel Application
OBJECTIVES:
- Create a WordArt graphic
- Plot data with a funnel chart
- Hide error values with the IFERROR function
- Validate data entry
- Hide worksheet rows and columns
- Hide worksheets
- Protect worksheets and workbooks from edits
- Unlock worksheets cells to allow edits
- Display the Developer tab
- Record and run a macro
- Assign a macro to graphic or macro button
- Edit macro code in an editor