MOS1.3. Excel Advanced (English)

---

1 Manage workbook options and settings

1.1: Manage workbooks

• Save a workbook as a template
• Hide or display ribbon tabs
• Enable macros in a workbook
• Copy macros between workbooks
• Reference data in another workbook
• Reference table data by using structured references

1.2: Manage workbook review

• Restrict editing
• Protect workbook structure
• Encrypt a workbook with a password
• Manage workbook versions
• Configure formula calculation options

---

2 Apply custom data formats and layouts

2.1: Apply custom data formats and validation

• Create custom data formats
• Populate cells by using advanced Fill Series options
• Configure data validation

2.2: Apply advanced conditional formatting and filtering

• Create custom conditional formatting rules
• Create conditional formatting rules that use formulas
• Manage conditional formatting rules

2.3: Create and modify custom workbook elements

• Create and modify cell styles
• Create custom themes and theme elements
• Create and modify simple macros
• Insert and configure form controls

2.4: Prepare a workbook for internationalization

• Change the Windows regional data format
• Display a specific date or time in a different international format
• Apply an international currency format

---

3 Create advanced formulas

3.1: Apply functions in formulas

• Insert functions into a formula
• Perform logical operations by using the IF, AND, OR, and NOT functions
• Perform logical operations by using nested functions
• Perform statistical operations by using the SUMIFS, AVERAGEIFS, and COUNTIFS functions

3.2: Look up data by using functions

• The VLOOKUP function
• The HLOOKUP function
• The MATCH and INDEX functions

3.3: 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

3.4: 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

3.5: Troubleshoot formulas

• Trace precedence and dependence
• Monitor cells and formulas by using the Watch Window
• Validate formulas by using error-checking rules
• Evaluate formulas

3.6: Define named ranges and objects

• Name a cell or range
• Name a table
• Manage named ranges and objects

---

4 Create advanced charts and tables

4.1: Create advanced charts

• Add trendlines to charts
• Create dual-axis charts
• Save a chart as a template

4.2: Create and manage PivotTables

• Create PivotTables
• Modify PivotTable field selections and options
• Create slicers
• Group PivotTable data
• Reference data in a PivotTable by using the GETPIVOTDATA function
• Add calculated fields
• Format data

4.3: Create and manage PivotCharts

• Create PivotCharts
• Modify PivotCharts
• Drill down into PivotChart details

---