Microsoft Excel - Intermediate

Introduction

A one-day instructor led workshop designed to further your knowledge of Excel, including working with the IF functions SUMIF, COUNTIF and LOOKUPs.   Also becoming familiar with Mathematical operators such as 'more than', 'less then' etc.   Working with analytical tools SUBTOTALS, ADVANCED FILTERS and Conditional Formatting.

Pre-Requisites

To optimise the training, it would be best if attendees have used Excel before to create and alter spreadsheets and perform basic calculations.  They should already be able to create formulas, use basic functions such as SUM, MIN, MAX and COUNT and also be aware of the difference between relative and absolute references.

At the end of this course you will be able to:

• Use of Advanced filters and Subtotals
• Create and Format Tables
• Understand and apply relative and absolute cell references
• Work with the Name Manager
• Use formula Auditing
• Use logical (IF) functions, conditional, COUNTIF and SUMIF.
• Consolidate data
• Apply conditional formatting
• Protect a workbook

Course Content:

Functions

• IF function
• VLOOKUP and HLOOKUP
• Function to combine data from multiple cells
• Nested functions

Tools

• Using Fill Lists
• Naming cells and ranges
• Protecting cells and workbooks
• Linking workbooks
• Linking applications (Excel with Word or PowerPoint)

Database tools

• Sorting data – using conditional formatting
• Sorting data in custom sequences
• Filtering data with AutoFilter
• Creating customised and advanced filters
• Using data forms to enter, delete and search for data
• Using subtotals to calculate sub sets of data

Formatting

• Hiding columns, rows and formulas
• Grouping columns and rows
• Applying conditional number formatting
• Protecting a Workbook