Learning Outcomes
Microsoft Excel has the basic features of all spreadsheets, using a grid of cells arranged in numbered rows and letternamed columns to organize data manipulations like arithmetic operations. It has a battery of supplied functions to answer statistical, engineering and financial needs.
MANAGE WORKBOOK OPTIONS AND SETTINGS
MANAGE WORKBOOKS
- Save a workbook as a template
- Copy macros between workbooks
- Mange Document Versions
- Reference data in another workbook
- Reference data by using structured references
- Enable macros in a workbook
- Display hidden ribbon tabs
MANAGE WORKBOOK REVIEW
- Restrict editing
- Protect a worksheet
- Configure formula calculation options
- Protect workbook structure
- Mange workbook versions
- Encrypt workbooks with a password
APPLY CUSTOM DATA FORMATS AND LAYOUTS
APPLY CUSTOM DATA FORMATS AND VALIDATION
- Create custom number formats
- Populate cells by using advanced Fill Series options
- Configure data validation
APPLY ADVANCED CONDITIONAL FORMATTING AND FILTERING
- Create custom conditional formatting rules
- Create conditional formatting rules that use formulas
- Manage conditional formatting rules
CREATE AND MODIFY CUSTOM WORKBOOK ELEMENTS
- Create custom color formats
- Modify fonts
- Insert Form Controls
- Create and modify cell types
- Create and modify custom themes
- Create and modify simply macros
- Configure form controls
PREPARE A WORKBOOK FOR INTERNATIONALIZATION
- Display data in multiple international formats
- Apply international currency formats
- Manage multiple options for +Body and +Heading fonts
CREATE ADVANCED FORMULAS
APPLY FUNCTIONS IN FORMULAS
- Perform logical operations by using AND, OR, and NOT functions
- Perform logical operations by using nested functions
- Perform statistical operations by using SUMIFS, AVERAGEIFS, AND COUNTIFS functions
LOOK UP DATA USING FUNCTIONS
- Look up data by using the VLOOKUP
- Look up data by using the HLOOKUP function
- Look up data by using the MATCH function
- Look up data by using the INDEX function
PERFORM DATA ANALYSIS AND BUSINESS INTELLIGENCE
- Import, transform, combine, display, and connect to data
- Consolidate data
- Perform what-if analysis by using Goal Seek and Scenario Manager
- Use cube functions to get data out of the Excel data model
- Calculate data by using financial functions
APPLY ADVANCED DATE AND TIME FUNCTIONS
- Reference the date and time by using the NOW and TODAY functions
- Serialize numbers by using date and time functions
TROUBLESHOOT FORMULAS
- Trace precedence and dependence
- Monitor cells and formulas by using the Watch Window
- Validate formulas by using error checking values
- Evaluate formulas
- Calculate data by using financial functions
FORMAT AND MODIFY TEXT BY USING FUNCTIONS
- Format text by using RIGHT, LEFT, and MID functions
- Format text by using UPPER, LOWER, and PROPER functions
DEFINE NAMED RANGES AND OBJECTS
- Name cells
- Name data ranges
- Name tables
- Mange named ranges and objects
CREATE ADVANCED CHARTS AND TABLES
CREATE ADVANCED CHARTS
- Add trend lines to charts
- Create dual axis charts
- Save a chart as a template
CREATE AND MANAGE PIVOT TABLES
- Create PivotTables
- Modify field selections and options
- Create slicers
- Group PivotTable data
- Reference data in a PivotTable by suing the GETPRIVOTDATA function
- Add calculated fields
- Format data
CREATE AND MANAGE PIVOTCHARTS
- Create Pivot Charts
- Manipulate options in existing Pivot Charts
- Apply styles to Pivot Charts
- Apply Styles to Pivot Charts
- Manipulate options in existing Pivot Charts
- Apply styles to pivot Charts
- Drill down into PivotChart details
