Intermediate Excel 2007 (1 Day)
Course Duration
Duration – 1 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 2007 is desirable.
Target Audience
This course is designed for users who wish to understand list manipulation and more advanced functions of Excel 2007.
Content/Topics
The Microsoft Excel 2007 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 Logic
If Statement
If-And Statement
If-Or Statement
Nested If
IfError
Sumif(s)
Countif(s)
Lookup Functions
Vlookup/HLookup
Lookup
Match
Index
Offset
Conditional Formatting
Embed/Dynamic Conditions
Colours, Data Bars, Colour Scales, Icons
Edit Rules
Manage Rules
Delete Rules
Duplicate, Unique
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
Text Functions
Concatenation
Left, Right, Mid
Search
Len
Data Validation
Whole Number
Decimal
Date
List
Circle Invalid Data
Sort
Sort by Number, Date, Text
Custom Sort
Sort by Colour, Icon
Filter
Filter by Number, Date, Text
Compound Filters
Filter by Colour, Icon
Multiple Value Filters
Wildcards
Subtotals
Automatic Subtotals
Manual Subtotals
Grouping and Outlining
Automatic Outline
Manual Outline