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