“Data analysis and visualisation: working with Microsoft Excel spreadsheet, advanced level”

Why you should take this training?

The training is designed to demonstrate the advanced possibilities provided by Microsoft Excel using real-life examples. Participants will gain skills in data analysis in the scope of using a spreadsheet, they will learn database tools and functions allowing for processing data and reducing time spent on everyday tasks.

Who is it for?

The training is designed for those who begin their journey with Microsoft Excel, who use the program to a small extent only, and those who want to consolidate their knowledge based on practical examples. It is intended for employees whose job requires them to create data sets, analyse them and draw logical conclusions.

Following the training, the participants will be able to:

1. Work efficiently on large databases using pivot tables, dynamic lists, filtering and sorting options,
2. Perform creative calculations using selected logical, text, mathematical, statistical, financial and database-related functions,
3. Prepare easy-to-read reports using simulation analysis and advanced formatting capabilities,
4. Work simultaneously on a single workbook with multiple collaborators by using file sharing and merging,
5. Easily transfer information from websites and external databases into Excel,
6. Automate simple tasks by using basic macro commands.

Agenda of the training:

I. Databases:

1. The concept of a database in Excel
2. Checking the correctness of data
3. Filtering data using the Autofilter tool
4. Advanced filter
5. Database form
6. Data sorting
7. Data grouping
8. Subtotals
9. Pivot table
10. Pivot table chart

II. Formulas and functions:

1. Inserting and modifying formulas
2. Relative and absolute cell addressing
3. Creating lookup tables
4. Basic functions
5. Inserting functions
6. Use of functions
1. Statistical
2. Date and time
3. Financial
4. Logical
5. Mathematical
6. Databases
7. Text
8. Lookup and reference
7. Editing functions
8. Errors
9. Formula inspection

III. Data analysis tools:

1. 1. Scenarios
2. 2. Report manager
3. 3. Use of automated analysis tools
1. 1. Goal Seek...
2. 2. Solver
3. 3. Data analysis...

IV. Advanced formatting:

1. Conditional formatting
2. Custom data formats
3. Formatting and adjusting graphics

V. Collaboration:

1. Sharing a workbook
2. Tracking changes
3. Merging documents
4. Workbook protection

VI. Data exchange:

1. Creating and editing templates
2. Importing
   1. Objects
   2. Data series
3. Export
   1. Objects
   2. Data series
4. Links to other workbooks
5. Data consolidation
6. Creating a workspace

VII. Customising applications:

1. Recording, editing and running macros
2. Introduction to Visual Basic for Applications