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 by using functions
Look up data by using the VLOOKUP function, look up data by using the HLOOKUP
function, look up data by using the MATCH function, look up data by using the INDEX
function
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
Perform data analysis and business intelligence
Reference the date and time by using the NOW and TODAY functions; 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
Troubleshoot formulas
Trace precedence and dependence, monitor cells and formulas by using the Watch
Window, validate formulas by using error checking rules, evaluate formulas
Define named ranges and objects
Name cells, name data ranges, name tables, manage named ranges and objects
Create advanced charts and tables (25-30%)
Create advanced charts
Add trendlines to charts, create dual-axis charts, save a chart as a template
Create and manage PivotTables
Create PivotTables, modify field selections and options, create slicers, group PivotTable
data, reference data in a PivotTable by using the GETPIVOTDATA function, add calculated
fields, format data
Create and manage PivotCharts
Create PivotCharts, manipulate options in existing PivotCharts, apply styles to PivotCharts,
drill down into PivotChart details