Advanced Excel Corporate Training Course Content

Working with Dates

• Dates and time in Excel
• Inserting and formatting dates
• Entering date functions
• Using dates in formulas

Working with Tables

• What is a Table?
• Creating Tables
• Changing the Table Range
• Inserting Table Columns
• Inserting Table Rows
• Deleting Rows or Columns
• Creating a Table Total Row
• Sorting and Filtering Tables
• Sorting Data in a Table
• What is an AutoFilter?
• Custom AutoFilters
• Removing Filters
• Filtering with Wildcard Characters
• Using an Advanced Filter
• Creating Subtotals
• FlashFill
• Use Slicers to filter tables

Working With Charts

• Creating a Chart
• Manipulating a Chart
• Moving and resizing a chart
• Adding a chart title
• Adding a chart Axis title
• Changing the Type of Chart
• Formatting a Chart
• Recommended Charts
• Quick Analysis Option
• Chart formatting control

Conditional Formulas and Formatting

• The IF function
• Using the Function Library
• Manually entering a function
• Conditional Formatting
• Applying Conditional Formatting
• Creating a Conditional Formatting Rule
• Removing Conditional Formatting
• Using the Manage Rules Option
• Adding Conditional Formatting using the Quick analysis tool

Using Logical Functions

• Working with Names and Ranges
• What Are Range Names?
• Defining and Using Range Names
• Using Logical Functions
• Using the Function Library
• Manually entering a function
• The IF function
• The AND Function
• The OR Function
• Working with Nested Functions
• Nested IF functions
• Applying Logical Functions

Working with Lookup Functions

• Using VLOOKUP to Find Data
• How to Find an Exact Match with VLOOKUP
• Finding the Closest Match with VLOOKUP
• How to use the MATCH Function
• Index Function
• Lookup using Match and Index Function

  Working with Multiple Worksheets and Workbooks

• Using Multiple Worksheets
• Navigating between sheets
• Creating -D formulas
• Consolidating data
• Linking your workbooks
• Linking Workbooks
• Using the Paste Link option
• Creating a formula to link workbooks
• Controlling link updates
• Manually update all of the links or none of the links in a workbook
• Manually update only some of the links to other workbooks
• Dealing with Broken Links

 Documenting and Auditing

• Comments
• Adding, editing and deleting comments
• The Formula Auditing Group
• Excel Workbook and Worksheet Protection
• Protecting your Worksheets
• Unlocking Cells

 Advanced List Management

• Validating Your Data
• Creating Data Validation Settings
• Using Database Functions
• Creating a Criteria Range
• Entering the Database Function

PivotTables and PivotCharts

• What is a PivotTable?
• Preparing Data to Create a PivotTable
• Creating a PivotTable
• Quick Analysis
• Adding Fields to the PivotTable
• Creating a PivotTable Frame (Classic PivotTable Layout)
• Rearranging PivotTable Data
• Hiding and Showing Field Data
• The PivotTable Tools Ribbon
• Modifying Calculations and Data Area Display
• Formatting a PivotTable
• Refreshing a PivotTable
• Time Grouping in PivotTables (Excel )
• Using Slicers to Manipulate PivotTables
• Use Timelines with Pivot Data
• Creating a PivotChart

Using ‘What-If’ Analysis Tools

• Using Scenarios in Excel
• Creating a Scenario
• Using Goal Seek
• Using Solver
• Checking Solver Installation
• Setting Solver Parameters

Macros

• What is a macro?
• Creating a macro
• The Developer Ribbon
• Recording a macro
• Playing a Macro
• Macro Security
• More Macro Tasks
• Recording a Relative Reference Macro
• Running a Relative Reference Macro
• Assigning a Keystroke to a Macro
• Copying a Macro from a Workbook or Template

Working with Large Worksheets

• Viewing and Organising Spreadsheet Data
• The Zoom Feature
• Hide rows and columns
• Using Automatic Outlining
• Grouping Data Manually
• Printing Large Worksheets
• Print Titles
• Page Break Preview
• Inserting a Page Break
• Printing Multiple Sheets

Using Templates

• What is a Template?
• Creating a Workbook from a Template
• Creating a Template