Advanced Excel

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