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