During this Microsoft Excel Advanced training course you will build on your existing Microsoft Excel skills and gain the necessary knowledge to perform advanced calculations in spreadsheets. You will become familiar with:
Applying advanced functions into formulas
• Understand how to use data lookup, including VLOOKUP, HLOOKUP, INDEX, MATCH, etc.
• Understand how to use reference functions, including ROWS, COLUMNS, ADDRESS, INDIRECT, etc.
• Understand advanced text functions, including, UPPER, LOWER, CONCATENATE, MID, LEN, SUBSTITUTE, TEXT, VALUE, etc.
• Understand advanced maths functions, including, TRUC, ROUND, PRODUCT, SUMIF, SUMPRODUCT, etc.
• Understand data protection, and working with a protected worksheet
• Understand how to set up access to cells, enable worksheet protection, and create a password for a workbook.
Importing and Exporting
• Understand how to import data from other data sources, applying Tab Delimited Text, Comma Delimited Text and Space Delimited Text.
• Understand how to import from an earlier version
• Understand working with connected data and unlinking connections
• Understand how to export excel into other types of files, including text, CSV, and Microsoft Word
• Understand how to apply data validation, including a Drop Down List, using formulas as validation criteria
• Understand how create an input message
Record a simple Macros
• Understand Excel Macros, and basic concept of Excel VBA
• Understand how to record, edit and run a simple Macro
Other advanced Excel features
• Understand data linking, Grouping and Automatic Outline, Subtotals, Data Consolidation, Scenarios
• Understand advanced Pivot Tables techniques
This course is designed for people who have intermediate Excel skills and would like to be able to solve complex equations using Excel.