TestOut Pro Certified: Microsoft Excel Advanced

Workbooks and Worksheets

Manage workbooks

• Protect a workbook
• Set Calculation Options
• Add authoring languages and proofing
• Set AutoSave and AutoRecover

Manage worksheets

• Protect a worksheet
• Lock or unlock ranges of a protected worksheet
• Set Edit Ranges
• Reference data in multiple worksheets

Formatting and Data

Use advanced formatting

• Manage rules
• Configure conditional formatting
• Use Flash Fill
• Use Fill Series
• Configure custom date formats
• Configure custom number and currency formats

Manage data

• Use data validation
• Remove duplicates
• Sort data
• Analyze and consolidate data
• Configure subtotals

Functions, Formulas, and Macros

Use advanced functions

• Use time functions: TODAY(), NOW(), WORKDAY(), WEEKDAY()
• Use lookup functions: VLOOKUP(), HLOOKUP()
• Use logical functions: IFS(), AND(), NOT(), OR()
• Use financial functions: PMT(), NPER()
• Use math functions: SUMIFS(), AVERAGEIFS()

Define advanced formulas

• Use nested functions
• Perform What-If Analysis using Goal Seek and Scenario Manager
• Troubleshoot formulas

Use macros

• Create and run macros
• Set macro security settings

Charts and Tables

Manage Charts

• Create and modify advanced charts
• Create and modify PivotCharts

Manage Tables

• Create and modify PivotTables
• Uses slicers to filter data