Understand where to use and how to create a range of powerful functions and how to automate tasks using macros. Learn how to analyse data with pivot tables and ‘what if’ analysis tools.

Open participant course: £390 per person - maximum 10 people

Closed company course: £2,000 per course - maximum 10 people

Delegates who need to exploit the full potential of Excel to build powerful spreadsheets, including a wide array of formulae and functions and who also need to use Excel modelling tools to report on results.

By the end of the course, you will be able to create sophisticated spreadsheets, which include complex functions. You will be able to utilise analysis tools to summarise and extract data, use pivot tables, scenarios, and auditing tools. The use of macros will facilitate automating tasks.

Naming Cells and Ranges:

• Creating named ranges.
• Using names in formulas.
• Editing and deleting named cells and ranges. 

Powerful Formulae and Functions:

• Statistical functions (COUNTIF and SUMIF).
• Logical functions (IF, IFERROR, AND and OR).
• Lookup and reference functions (VLOOKUP, HLOOKUP, INDEX and MATCH).
• Date calculations and functions.
• Math and trig functions (ROUND and INT).
• Text functions.

Formula Auditing:

• Tracing dependents and precedents.
• Formula error checking.
• Formula evaluation techniques.

Protection:

• Protecting files.
• Protecting worksheets.
• Protecting cells.

‘What-If’ Analysis and Forecast Sheets:

• Creating and editing scenarios.
• Scenario summary reports.
• Data tables.
• Goal seek and solver.
• Predicting values with forecast sheet.

Data Validation:

• Validating text, values, dates and lists.
• Validation input messages.
• Error message alerts.
• Locating invalid data.

Analyse Data with Pivot Tables:

• Creating a pivot table.
• Drilling down on pivot data.
• Sorting, grouping and filtering pivot data.
• Filter and timeline slicers.
• Summary values and calculations.
• Formatting pivot tables.
• Pivot chart reports.

Automating Spreadsheets with Macros:

• Recording and running macros.
• Deleting a macro.
• Editing macros.
• Macro virus protection.

This course can be delivered remotely or in a classroom environment, to suit your requirements.

Attendees should have a thorough grounding in building multiple sheet workbooks which include formulas and functions, such as SUMIF. They should also be able to create charts and use filter and sorting options. 

Attendees must have the following equipment to attend this training remotely:

• PC or laptop with a microphone and speakers (webcam is preferred but not essential).
• Access to the internet and web browser application (e.g. Google Chrome).
• Microsoft Excel installed on the PC or laptop.
• An email account for file sharing.
• A second screen is ideal but not essential.