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

Topic E: 0  Date functions

E-1:                               Using Date functions

Topic F: 0  Array formulas

F-1:                         Using an array formula

F-2:                         Applying arrays to functions

                         Unit 2 :  Lookups and data tables

Topic A: 0  Using lookup functions

A-1:                               Examining VLOOKUP

A-2:                               Using VLOOKUP to find an exact match

A-3:                               Using VLOOKUP to find an approximate match

A-4:                               Using HLOOKUP to find exact matches

A-5:                               Using HLOOKUP to find approximate matches

Topic B: 0  Using MATCH and INDEX

B-1:                               Using the MATCH function

B-2:                               Using the INDEX function

Topic C: 0  Creating data tables

C-1:                         Creating a one-variable data table

C-2:                         Creating a two-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

B-3:                         Copying filtered results to another range

                         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  Rearranging PivotTables

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  PivotCharts

D-1:                         Creating a PivotChart

                         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

Topic B: 0  Getting external data

B-1:                         Getting external data from Microsoft Query

B-2:                         Using a Web query to get data from the Web

                         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

                         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