Intermediate Excel XP-2003 (2 Day)
Course Duration
Duration 2 Day. Course timings 0930-1630 (approximate)
Course Aims
This course will provide delegates with more advanced skills that will streamline repetitive tasks, manipulate lists and display spreadsheet data in more visually effective ways.
Exposure to conditional logic, lookup formulas and large dataset manipulation is covered.
Pre-requisites
Prior knowledge and/or experience of Microsoft Excel 2003 is desirable.
Target Audience
This course is designed for users who wish to understand list manipulation and more advanced functions of Excel 2003.
Content/Topics
The Microsoft Excel 2003 Intermediate course will cover the following topics enabling delegates at the end of the course to be able to:
———————————————————————————————————————————————————————
Absolute Formula Referencing
Absolute Vs Relative Referencing
Mixed Absolute References
Multiple Workbooks/Sheets
Arranging Multiple Workbooks
Viewing Workbooks Side by Side
Viewing Multiple Sheets in Same Book
Grouping Sheets
3D Cell Referencing (Sheet)
3D Cell Referencing (Multiple Books)
Edit/Update Links
Copy – Paste
Different Paste Options
Paste Link
Naming
Name Cell
Name Range Cells
Automatic Name Ranges
Use Name in Formula
Conditional Formatting
Embed/Dynamic Conditions
Edit Rules
Manage Rules
Delete Rules
Formulas
Conditional Logic
Not, And, Or Conditions
If Statement
If-And Statement
If-Or Statement
Nested If
Sumif
Countif
Lookup Functions
Vlookup/HLookup
Lookup
Match
Index
Offset
Row, Rows
Column, Columns
Address
Indirect
Protection
Save as Read Only/Modify
Protect Sheet
Protect Cells
Protect Multiple Non-Contiguous Cells
Unprotect
Encryption
Inspect
Comments
Add, Delete, Edit Comment
Print Comment
Format Comment Font
Format Comment Colour
Date/Time Functions
Today, Now, Date
Day, Month, Year
Hour, Minute, Second
Workday, Networkdays
EoMonth
Weekday
Text Functions
Concatenation
Left, Right, Mid
Search
Len
Substitute
T, Text, Value
Proper, Upper, Lower
Information Function
Cell
IsBlank
IsErr
IsOdd, IsEven
IsNumber, IsText
Maths Function
SQRT
ABS
INT
Round, Roundup, Rounddown
Ceiling, Mround
Product
Database Function
DSUM
DCOUNT
DMAX, DMIN
Custom Formats
Create Custom Formats
Add Colours to Custom Formats
Custom Views
Create Custom View
Add/Remove Custom View
Using Custom Views
List Formatting/Function
Create List from Data
Adding Data
Filtering
Summarise
Resize
Print
Data Validation
Whole Number
Decimal
Date
List
Formula
Sort
Sort by Number, Date, Text
Custom Sort
Filter
Filter by Number, Date, Text
Compound Filters
Multiple Value Filters
Wildcards
Advanced Filter
Use Formulas in Advanced Filter
Subtotals
Automatic Subtotals
Manual Subtotals
Grouping and Outlining
Automatic Outline
Manual Outline
Charts
Combination Charts
Manual Outline
Copy Chart as Picture
Log Scale
Bubble Chart