This course aims to take you along the path to mastering EXCEL and importantly to see how using the program can improve your efficiency at work. We show how the program works using real work examples.
This Excel intermediate course will build on your basic Excel skills and show you more complex formulae, how to build formulae across worksheets, linking data and charting your data. We also cover protecting your sheets and workbooks.
|
OBJECTIVES |
You will be able to:
- Use Logical Functions, Financial and Statistical Functions
- Nest Functions
- Multiple Worksheet Management and Formulas
- Create Charts
- Protect Worksheets & Workbook
|
METHODOLOGY |
- Guided Learning, group-paced, hands-on classroom training with real life examples for business applications
- Challenging exercises are set based on practical applications for typical organizational functions such as HR, Admin, Production, Sales etc.
- Useful class manual are provided for each participant.
| |
|
DAY 1 |
|
- Excel Interface, Entering data, Formatting
|
|
- Inserting and deleting sheets
- Changing the default number of workbook sheets
|
|
- Concept And Purpose
- Naming Individual Cells
- Naming Range Of Cells
- Deleting And Amending Named Ranges
- Using Name Ranges in Formulae/Functions
- Named Ranges As Navigation Aid
- Manipulating text and numbers
|
|
Cont. LINKING SHEETS AND FILES | |
- Viewing Different Files On One Screen
- Saving a workspace
- Window Split
- Freeze Panes
- Viewing Different Parts Of The Sheet On One Screen
- Watch Window
|
2:00 |
USING FORMULAS & FUNCTIONS | |
- Changing the default number of workbook sheets
- Linking sheets in the same file
- Relative /Absolute Cell Reference
- Using Text/number Functions
- Understanding Arrays
- Calculating using Date Function
Excel Functions
- Benefits And Purpose
- IF Statements
- Nested If
- SUMIF And SUMIFS
- AVERAGEIF And AVERAGEIFS
- COUNTIF and COUNTIFS
- ISERROR And IFERROR
- Nesting IF WITH AND, OR
|
|
DAY 2 |
9:00 |
EXCEL FUNCTIONS RECAP | |
|
|
- Vertical Lookup (Vlookup)
- Horizontal Lookup (Hlookup)
|
|
APPLYING AND MANAGING CONDITIONAL FORMATTING | |
- Data Bars
- Colour Scales
- Icon Sets
- Top/Bottom
|
|
ESSENTIAL LIST MANAGEMENT | |
- Sorting Data (By Values, By Cell Colour, By Font Colour, By Cell Icons)
- Multi Column Sort
- Filter (By Values, By Cell Colour, By Font Colour, By Cell Icons)
- Multi Column Filter
- Advanced Filter
- Adding Subtotals
- Group and Outline
- Data Form
- Format As Table Feature
- Data Consolidation
|
2:00 |
CHARTS/GRAPHS TECHNIQUES | |
- Creating Chart Using Shortcut Keys
- Saving Custom Chart as Templates
- Setting Chart as Default
- Applying Trendlines
- Formatting and Editing Series, Plot Area, Data Points Chart Area, legends, etc...
- Using Sparklines (Line, Column, Win/Loss)
|
|
|