Excel 2013: Advanced, MOS Edition

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