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.

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.

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

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

Δευτέρα, 23 Σεπτεμβρίου 2024, 08:15 – 16:00
Τρίτη, 24 Σεπτεμβρίου  2024, 08:15 – 16:00

Κόστος:
€430

Επιχορήγηση:
€280

Τελικό Κόστος:
€150