Full day training session

Excel Intermediate

Recommended for those who use Excel on a regular basis and need to get things done quickly and efficiently. You will enhance your spreadsheets with more advanced formulas (linking, IF, VLOOKUP) and methods of resolving errors. You will learn techniques of sorting and filtering and advanced formatting.

You are advised to attend the Essentials course first or at least to have knowledge of basic formulas, including absolute cell references.

 

Excel Intermediate Part 1

 

1. Linking Worksheets and Workbooks

• Switch Open Workbooks
• Copy Sheets to other workbooks
• Simple Formula Linking sheets
• 3D Formula Linking sheets
• View different files at once
• Link workbooks

 

2. Consolidation

• Use consolidate feature with link
• Use consolidate feature with no link

 

3. Named Ranges

• Create Named Ranges Manually
• Create Automatically from column headings
• Use in Formulas
• Print List
• Edit Named Range

 

4. Advanced Formulas (IF, VLOOKUP)

• Use Insert Function
• IF function
• Nesting / IFS
• AND/OR
• SUMIF, COUNTIF, AVERAGEIF
• Vertical Lookup (Vlookup)
• True/False argument
• Horizontal Lookup (Hlookup)
• Match and Index

 

Excel Intermediate Part 2

 

5. Advanced Formatting

• Format Cells with Custom Number formats
• Conditional formatting
• Working with Styles
• Use Text functions to format text
• Paste Special – Transpose
• Text to Columns
• Flash Fill

 

6. List Management

• Find Data
• Freeze Panes
• Sort Data (up/down, left/right, by colour)
• Add automatic Subtotals
• Autofilter (filter tick boxes, text, date, number, colour, custom, Top 10)
• Advanced Filter
• Remove duplicate rows

 

7. Auditing Worksheets

• Find / Go To Errors
• Formula Auditing
• Formula View
• Error Checker
• Tracing Precedents/Dependents
• Watch Window
• Evaluate Formula