MS Excel (Intermediate) - 2007/2010
Course Date: 9th July
Member Rate: €110.00
This course is designed for those who may have been using MS Excel 2007 or 2010 for some time, but have never received formal training. We will work through basics including the use of shortcut keys & some less familiar formatting options. We will then move on to more intermediate topics such as working with and linking multiple worksheets, creating charts, managing lists (Sorting & AutoFilter) among other options.
Objectives:
On completion of this course participants will be able to use much of the functionality with MS Excel including:
• Competent use of Shortcut Keys
• Use formatting tools such as the format painter
• Write and copy formulas – Relative / Absolute
• Work with multiple worksheets and write formulas across sheets / Link Sheets
• Create professional looking charts and use drawing tools to enhance these
• Use the database functionality of MS Excel – Sorting & Auto Filter
Course Outline:
Excel Basics (An Overview)
- Review of Excel Basics concentrating on quick methods of performing commonly used commands / features including an overview of Speed Keys
Formatting
- Advanced Formatting Options
- Using the Format Painter
- Deleting Formats
- Using AutoFormat
- Conditional Formatting
Viewing and Modifying Worksheets
- Zoom Setting
- Splitting a Worksheet
- Freezing Titles
- Print Titles
- Hiding and Unhiding Columns and Rows
Formulae and Functions
- Recap of Basic Formulae
- Recap of Basic Functions
- Copying Formulae
- Relative vs. Absolute cell addressing
Custom Lists
- Working with Text Lists in Excel
- Creating Custom Lists
- Incrementing Numbers & Dates
Working with Multiple Worksheets
- Switching between sheets
- Inserting /Renaming / Deleting Sheets
- Moving / Copying sheets
- Group Edit
- Linking sheets using Formulae
- Breaking links
Charts and Drawing Tools
- Creating a chart
- Changing the Chart Type
- Changing / Adding chart elements
- Formatting charts
- Moving / Resizing the chart
- Creating combined charts
- Displaying data on a secondary basis
Database Features
- Sorting Records
- Using AutoFilter
Working with Dates
- Formatting Dates
- Date Calculations
- The Today Function
This course is ideal for anyone who has a good working knowledge of MS Excel 2007 or 2010 but may never have attended formal training.
MS Excel (Advanced) 2007/2010
Course Date: 12th June
Member Rate: €110.00
This course is designed to follow-on from the MS Excel Intermediate course. Having already familiarised yourself with the most efficient way of performing everyday tasks, and having covered some intermediate tasks, it is now time to build on that knowledge. A large proportion of the course is dedicated to using the Database Features in MS Excel - this is a very powerful side to Excel that not many people utilise to its fullest potential. Useful functions such as IF and Lookups are covered in addition to automating tasks using Macros etc.
Objectives:
On completion of this course participants will be proficient in the more advanced functionality of MS Excel including:
• Advanced Data Analysis – Advanced Filtering, Creating Names Ranges, Sub-Totalling feature, Database Function, Pivot Tables
• Recording Macros
• IF, Lookup and other functions
• Goal Seek & Scenario Manager
• Protecting worksheets & workbooks
Course Outline:
Essentials and shortcuts
- An overview / recap of some essential Intermediate topics
- Run-through of shortcuts
Named Ranges
- Creating a named range
- Redefining a named range
- Users for named ranges
Text Manipulation
- Upper / Lower / Proper Functions
- Text to columns
- Concatenation
- Trim
- Right / Left / Mid Function
Database Features
- Sorting Records
- Using AutoFilter
- Removing duplicates
- Subtotalling lists
- Database Functions - DSUM / DAVERAGE / DMAX / DMIN / DCOUNT
Working with Pivot Tables
- Pivot Table Concepts
- Setting up the Pivot Table Layout
- Refreshing Pivot Table Data
- Using PivotTable AutoFormat
- Filtering PivotTable Data
- Creating calculated fields
- Grouping dates and values
More on Functions
- Using the COUNT Functions
- Using LOOKUP Functions (Vlookup & Hlookup)
- Using the IF Function
- Using IS Functions
- Using the SUMIF Function
- Creating Formulae with Nested Functions
- PMT Function
What IF Analysis
- Using Goal Seek
- Scenario Manager
Auditing
- Using the Auditing Toolbar
- Tracing Precedents & Dependents
Macros
- Recording & Running Macros
- Assigning a Macro to the quick access toolbar
Content, Worksheet & Workbook protection
- Data validation
- Protecting Worksheets
- Protecting Workbook Access
- Assigning Password Protection
- Removing a Password
- Preventing Changes to a Workbook
