Excel 2013:

Advanced

Topic-Level Outline

Days:  1

          Prerequisites: Excel 2013: Intermediate or equivalent experience

                         Unit 1 :  Advanced functions and formulas

Topic A: 0  Logical functions

A-1:                               Using the IF function

A-2:                               Using a function to apply conditional formatting

A-3:                               Using OR, AND, and NOT as nested functions

A-4:                               Using the IFERROR function

Topic B: 0  Conditional functions

B-1:                               Using SUMIF

B-2:                               Using COUNTIF

B-3:                               Using AVERAGEIF

B-4:                               Using SUMIFS, COUNTIFS, and AVERAGEIFS

Topic C: 0  Financial functions

C-1:                               Using the PMT function

Topic D: 0  Text functions

D-1:                               Using the LEFT, RIGHT, and MID functions

D-2:                               Using functions to separate names

D-3:                               Using additional text functions

Topic E: 0  Date and time functions

E-1:                               Using date functions

Topic F: 0  Array formulas

F-1:                              Using an array formula

F-2:                              Using an array with the TRANSPOSE function

Topic G: 0  Calculation options

G-1:                         Setting calculation options

                         Unit 2 :  Lookups and data tables

Topic A: 0  Using lookup functions

A-1:                               Examining VLOOKUP

A-2:                               Using VLOOKUP to find an approximate match

A-3:                               Using HLOOKUP to find approximate matches

A-4:                               Using LOOKUP to find a value in a one-column range

Topic B: 0  Creating data tables

B-1:                         Creating a one-variable data table

                         Unit 3 :  Advanced data management

Topic A: 0  Validating cell entries

A-1:                               Observing data validation

A-2:                               Creating a data validation rule

A-3:                               Setting date and list validation rules

Topic B: 0  Advanced filtering

B-1:                         Using Custom AutoFilter criteria

B-2:                         Using the Advanced Filter dialog box

                         Unit 4 :  Advanced charting

Topic A: 0  Chart formatting options

A-1:                               Adjusting the scale of a chart

A-2:                               Formatting a data point

Topic B: 0  Combination charts

B-1:                               Creating a combination chart

B-2:                               Adding a trendline

B-3:                               Inserting sparklines

B-4:                               Creating and using a custom chart template

Topic C: 0  Graphical objects

C-1:                         Adding graphical objects to charts

C-2:                         Formatting graphical objects

                         Unit 5 :  PivotTables and PivotCharts

Topic A: 0  Working with PivotTables

A-1:                               Creating a PivotTable

A-2:                               Adding fields to a PivotTable

A-3:                               Using slicers to filter PivotTable data

Topic B: 0  Modifying PivotTable data

B-1:                               Grouping data and moving fields

B-2:                               Refreshing the data in a PivotTable

B-3:                               Inserting a calculated field

Topic C: 0  Formatting PivotTables

C-1:                               Applying a PivotTable style

C-2:                               Changing field settings

Topic D: 0  Using PivotCharts

D-1:                               Creating a PivotChart

Topic E: 0  PowerPivot

E-1:                         Enabling PowerPivot

E-2:                         Using PowerPivot

                         Unit 6 :  Exporting and importing data

Topic A: 0  Exporting and importing text files

A-1:                         Exporting Excel data to a text file

A-2:                         Importing data from a text file into a workbook

A-3:                         Converting text to columns

A-4:                         Removing duplicate records

                         Unit 7 :  Analytical tools

Topic A: 0  Goal Seek

A-1:                               Using Goal Seek to solve for a single variable

Topic B: 0  Scenarios

B-1:                               Creating scenarios

B-2:                               Switching among scenarios

B-3:                               Merging scenarios from another worksheet

Topic C: 0  Instant data analysis

C-1:                         Using the Quick Analysis tool

                         Unit 8 :  Macros and Visual Basic

Topic A: 0  Running and recording a macro

A-1:                               Running a macro

A-2:                               Recording a macro

A-3:                               Inserting a macro button

Topic B: 0  Working with VBA code

B-1:                         Observing a VBA code module

B-2:                         Editing VBA code

B-3:                         Copying a macro to another workbook

                         Unit 9 :  Accessibility and language features

Topic A: 0  Accessibility considerations

A-1:                               Checking accessibility                              

Topic B: 0  Internationalization

B-1:  Using international symbols

B-2:  Using language options