EDITC - Education & Information Technology Centre

Schedule

Training Course Schedule

Microsoft Excel Expert Level For Technical, Scientific & Supervisory Staff

Code:	ED21A07
Type:	IT InstructorLed Courses
Category:	MICROSOFT OFFICE
Course:	Microsoft Excel Expert Level For Technical, Scientific & Supervisory Staff

Date:	16 Feb 2021 08:15 - 18 Feb 2021 16:00

Mastering Excel can be life changing in the workplace regardless of your current position. Working with tables and lists, making decisions based on data analysis, automating calculations with IF functions, comparing lists with VLOOKUP, formatting sheets with Conditional Formatting are some of the few things that you will learn. The course Excel Expert Level introduces the most interesting features of Microsoft Excel. The course focuses on the Excel features for data analysis such as sorting, filtering, advanced filtering, pivot tables, consolidation and subtotals as well as formulae and nested functions in order to create “smart” spreadsheets. It also presents features such as data validation in order to minimize errors and automate data entry. The course also introduces various financial analysis tools such as goal seek, scenario manager and data tables. Finally the course presents ways to protect the spreadsheets and formulae and to create templates.

Duration:	14 hours (2 days)
Location:	Webinar
Language:	Greek
Attendance:	6-16
Speaker:	Maria Knais

Price:	€350.00

Subsidy:	€168.00

Total:	€182.00

Topics

Unit 1: Working with Data lists
• Apply data filters (AutoFilter)
• Perform single and multi-level sorts
• Applying Subtotals in Filtered Lists
Exercise

Unit 2: Working with Advanced Filters
• Extract data with Advanced Filter
• Combining criteria
Exercise

Unit 3: Data validation
• Applying Validation Criteria in cells
• Creating drop down lists for automatic selection of data
• Validating Data
Exercise

Unit 4: Grouping and Outlining
• Creating Subtotals
• Creating Nested Subtotals
• Using Groups and outlines
Consolidation
Exercise

Unit 5: Calculations based on Logical Functions and Nested Functions
• Using IF and nested IF
• Using AND, OR
Exercise

Unit 6: Using Named Ranges and Usage in Functions
• Creating a named range
• Changing a named range
• Selecting fields by using names
• Creating names using labels
• Using names in Functions

Unit 7: Creating Smart sheets with VLOOKUP
• Using VLOOKUP
• Nested functions with IF and VLOOKUP
• Automating Calculations with IF and formulae

Unit 8: Collaborating and Protecting
• Working with notes
• Protecting and Unprotect workbooks, spreadsheets and cells
• Protecting a file using passwords

Unit 9: Creating Templates
• Creating new Templates
• Using the existing Templates
Exercise

Unit 10: Data analysis with PivotTables
• Creating PivotTables
• Summarizing data using various functions
• Applying Filters
• Using Slicers and Timelines
• Creating Pivot Charts
Exercise

Unit 11: Analysis Tools
• Using Goal Seek
Working with Scenarios
Using Solver (Goal Seek)

Unit 12: Auditing
• Using the Auditing tools
Finding and correcting Formulae errors

Who Should Attend
Participants may come from various departments and are dealing or wish to create complex spreadsheets. Participants who seek for ways to utilize all the excel features in order to create “smart sheets” or analyze data will find this course very useful.

Objectives

The participants will be able to use advanced features of Excel in order to:

Filter and analyze data
Analyze data with Pivot Tables
Group and create subtotals
Use popular Functions such as IF function, VLOOKUP, PMT etc
Create nested functions for automation and analysis of calculations
Track formulae and function errors
Protect Workbooks, files, sheets and cells
Use Analysis tools for financial analysis

Prerequisites
Basic knowledge of Microsoft Excel. Additionally participants should have basic knowledge on the creation of formulae and functions.

Methodology

The course is 100% practical. The topics are delivered with short presentations by the instructor followed by a step-by-step demonstration by the instructor and repetition by the students, examples and discussions on how a feature may be used with real life examples and practice through written exercises.

TimeTable
Tuesday 16/02/2021, 08:15 - 16:00
Thursday 17/02/2021, 08:15 - 16:00