Microsoft Excel 2013 (Exam 77-420)
(Basic/ Intermediate Excel) Duration: 16 Hrs.

Create and manage workbooks and worksheets

  • Create workbooks and worksheets
  • Navigate through worksheets and workbooks
  • Inserting hyperlinks
  • Format worksheets and workbooks
  • Configuring data validation
  • Customize options and views for worksheets and workbooks
  • Customizing the Quick Access Toolbar
  • Customizing the ribbon
  • Configure worksheets and workbooks to print or save

Manage cells and ranges

  • Insert data in cells and ranges
  • Format cells and ranges
  • Modeling data
  • Conditional Formatting
  • Using Sparklines
  • Creating named ranges
  • Working with data groups and summaries
  • Subtotal

Manage tables

  • Create tables
  • Modify tables
  • Filter and sort tables

Apply formulas and functions

  • Utilize cell ranges and references in formulas and functions
  • Summarize data by using functions
  • Utilize conditional logic in functions
  • Format and modify text by using functions
 

Create charts and objects

  • Create charts
  • Format charts
  • Adding SmartArt graphics to sheets

Microsoft Excel 2013 Expert (Exams 77-427 and 77-428)
(Advanced Excel) Duration: 24Hrs.  

Manage and share workbooks

  • Manage multiple workbooks
  • Prepare workbooks for review
  • Tracking changes
  • Protecting workbooks for sharing
  • Manage workbook changes
  • Displaying all changes
  • Reviewing changes
  • Managing comments
  • Merging workbooks

Apply custom formats and layouts

  • Apply custom data formats
  • Creating custom formats (number, time, date)
  • Using advanced Fill Series
  • Apply advanced conditional formatting and filtering
  • Creating custom conditional formats
  • Using functions to format cells
  • Creating advanced filters
  • Apply custom styles and templates
  • Prepare workbooks for internationalization and accessibility

Create advanced formulas

  • Apply functions in formulas
  • Using nested functions
  • Using the IF, AND, and OR functions
  • Using the SUMIFS, AVERAGEIFS, and COUNTIFS functions
  • Look up data by using functions 6
  • Using the VLOOKUP and HLOOKUP functions
  • Using the LOOKUP function
  • Using the TRANSPOSE function
  • Apply advanced date and time functions
  • Using the NOW and TODAY functions
  • Using functions to serialize dates and times
  • Create scenarios
  • Using what-if analysis tools
  • Sidebar: Enabling iterative calculations
  • Sidebar: Using the watch window
  • Using the Scenario Manager

Create advanced charts and tables

  • Create advanced chart elements
  • Adding trendlines to charts
  • Creating dual-axis charts
  • Creating custom chart templates
  • Create and manage PivotTables
  • Creating PivotTables
  • Modifying field selections and options
  • Creating slicers
  • Using PowerPivot
  • Create and manage PivotCharts
  • Creating PivotCharts
  • Manipulating options in existing PivotCharts
  • Applying styles to PivotCharts
 

Working with macros

  • Record/ Play a macro
  • Modify the macro
  • Copying macros between workbooks