May 3, 2015

Advanced Excel course

Advanced Excel courseThe advanced Excel course is focusing on creating complicated dashboards. These include graphical parts, clear structure, advanced data validation drop downs, ActiveX option buttons and sliders.

This course is mostly beneficial for those people who are involved into reporting, forecasting and analysis of various data and indicators on periodic basis. (monthly, weekly, daily). It is also very beneficial for leaders who are managing teams of less excel experienced employees.

In majority of the situations that involve analysis and reporting, the people are wasting a lot of time to restructuring, reconciling, recalculating, repopulating formulas and redesigning the data. This approach is wrong.

The best practice for periodic reporting is to have one front-end data that contains all charts, pivot tables and graphs that has everything structured clearly. From period to period, only the back-end raw data has to be updated. Everything else is done automatically by just having all formulas and tables setup efficiently. This will be the main principle learned during the advanced excel course.

This course is valid for following versions:
Excel 2007; Excel 2010; Excel 2013; Excel 365
The following features are included in the course:
  • Principle of front-end and back-end data.
  • Accurate and efficient data linking and structuring
  • Advanced data validation drop-downs that dynamically depend on each other. (using INDIRECT, OFFSET, MATCH nested functions)
  • Working with efficient data connections. Linking to MS Access, text files and online databases.
  • Creating efficient graphs, charts and visual effects.
  • Using “option buttons”, active-x controls.
  • Creating simple macros for repetitive operations.
  • Embedding excel functions into Data validation, name manager and conditional formatting context.
  • Efficiently linking excel data into power point and other applications (99.9% of users do it wrong).

The following formulas are used during this course. Before attending this course you should be comfortable with using most of them.

  • Text and formatting formulas: TEXT, REPT, UPPER, LOWER combined with previously learned LEN, PROPER, MID, LEFT, RIGHT.
  • Calculations: SUMIFS, COUNTIF, COUNTA, COUNTBLANK. AVERAGE, MAX, MIN, LARGE, SMALL.
  • Logical formulas: IF, IFERROR, ISNA, ISERROR, AND, OR.
  • Reference formulas: INDIRECT, MATCH, INDEX, OFFSET.

This course is going to be relatively difficult. Thus, I recommend attending the intermediate course first, or test your skills on below attached document. If you complete more than 9/13 tasks, you are eligible to attend the advanced level course:

download intermediary tasks (948KB, xlsx)

The expected duration of the intermediate excel course is 17 hours. Presentation part is 10 hours (split into several sessions 2 hours each per week) and practical homework part that will take in total approximately 7 hours to be completed.

Participants will receive a digital certificate via email that can be added to LinkedIn.

 

After completing this course, you will be able to prepare dashboards that are more efficient than the ones below:
Advanced Excel course HR_Dashboard

Attendances will receive a digital certificate via email that can be added to LinkedIn.

You can apply to this course directly on the website through events interface or by contacting us at contact@luckygiga.com

The group course includes refreshments.

 

 

We accept benefity.cz points as payments.

[events_list]

Leave a Reply

Your email address will not be published. Required fields are marked *