A solid course of data analysis
Excel, Access, SQL, statistics, visualization
- Practical, hand-on course,
- taught by experienced analysts and experts,
- analysing financial and business data,
- wide variety of tools: from visual data expoloration to statistical methods and SQL.
- Price: 890 EUR
- instalment payment plans available for private persons
- more information here
Our course is intended for:
- graduates, junior analysts, people who want to become analysts or broaden their existing skill-set
- employees of all industries, including public institutions, dealing or planning to deal with data processing,
- people who use Excel and other office software but want to know more
Requirements: from participants we do not require any experience in data analysis, but only basic MS Excel skills.
Valuable knowledge and skills
The course is taught by instructors who have many years experience not only in MS Office programs or databases but also in practical data analysis, finances and statistics. They are professionals, teaching in a lively manner, introducing many realistic examples.
The main idea of this training course is to introduce the wide variety of tools and techniques of data analysis and working with databases. We focus our training on practical exercises – participants have an opportunity to practise the discussed techniques on realistic data, similar to the data they work with. During the course we discuss:
- the most important Excel tools and techniques of managing data (including Pivot Tables, Solver and Analysis Toolpak).
- statistical methods of data analysis
- data virtualization and creating interactive visualizations in Excel
- using databases in MS Access and MS SQL Server
- SQL: database language
After completing the course the students will be able to:
- analyse large amounts of data
- use the most important tests and statistical methods
- make predictions and analyse trends on the basis of existing data
- keep MS Access and MS SQL Server databases up to date
On the completion of the course the students receive course completion certificate.
Theoretical knowledge is important but practice matters most.
- during the course particpants analyse the real data,
- solid training materials, which enable students to learn easily both at home and during the class
If you need more information or have any questions regarding the course please contact our office.
Excel as a tool in data analysis
- How to use Excel effectively
- Introduction to Excel
- Formatting and conditional formatting
- Relative and absolute cell references
- Naming cells
- Formulas and the most common functions
- Data and time functions
- Logical functions (including if conditional function)
- Information functions
- Text functions
- Lookup functions
- Decision making functions
- Mathematical and statistical functions
- Database functions
- Financial functions
- Array formulas
- Databases in Excel
- Table design- good practices
- Exploring data: sorting and filtering
- Removing duplicates
- Data consolidation
- Subtotals and outlines
- Collaboration in Excel and sharing worksheets with other users
- Data validation
- Drop-down lists
- Worksheet and cell protection
- Hidden formulas and worksheet protection
- Custom forms and controls
- Pivot Tables
- Creating Pivot Table
- Pivot Table modifications
- Filtering and sorting; report filter
- What-If analysis
- Use of external data
- Data import from external databases (MS Access, SQL Server),
- Data import from text files
- Data import from websites
- Data export
- Get and transform data using Power Query
- Data import (e.g. from the Internet and databases)
- Preparation of data for analysis – introduction to the formulas of the M language
- Convert reports to a form that allows further analysis
- Data visualization using conditional formatting
- Use of built-in templates
- Create rules based on formulas
- Imaging shares, discrepancies and exceptions
- Types of charts and how to use them
- Chart styles– templates and custom
- Custom charts
- Charts on grouped data
- Frequency charts
- Charts with time series
- Pivot Charts
- Excel Pivot Table Slicers
- Interactive Scenario Analysis
- Interactive charts, selecting series, charting on-the-fly
- Practical examples
- Analysis Toolpak – running and usage
- Introduction to statistical methodology
- Basic terms: population, sample, random variables, hypothesis, statistical significance
- Random sampling
- Collecting data, creating questionnaires and surveys
- Descriptive statistics
- Basic functions of descriptive statistic: average, standard deviation, variance, median, skewness, kurtosis
- Frequency, relative and cumulative frequency; mode and quantiles
- Histograms and frequency distributions
- Data visualization and descriptive statistics visualization: histograms, pivot tables, sparklines
- Using charts in trend analyzing
- Mathematical statistics
- Basic terms: probability, probability distribution, the most common distributions (normal distribution, Student’s t distribution, Chi-squared distribution)
- Generating random numbers from different distributions
- The most important statistical tests in practice: Student’s t test, z- test, F -test, Chi-squared test
- Examining relations between data:
- Covariance and correlation – calculation and interpretation
- Variance analysis
- Trends prediction and examination: regression analysis.
- Time series and smoothing – predictions, moving average, curve fitting, exponential smoothing.
- Introduction to Microsoft Access 2016
- Understanding purpose and features of Access
- How it all works? Forms, Tables, Queries, Reports.
- User interface: Ribbon and Quick Access Toolbar
- Using Access templates databases
- Import/export of databases between Excel and Access
- When to use Access and when Excel?
- Designing databases – basics
- Creating tables
- Data types
- Understanding relationships, primary keys and foreign keys
- Default values
- Form wizard
- Working in design view and layout view
- Reports wizard
- Working in design view and layout view
- Printing reports and data
SQL Language in MS Access and MS SQL Server
- Relational databases – basics
- Basics of relational theory
- Table, row, column
- Key, primary key
- Foreign keys and relationships between tables
- Basic views and operations in database applications (MS SQL Server and MS Access examples)
- SQL – Structured Query Language
- Simple queries – SELECT
- Functions and operators
- Filtering rows – WHERE clause
- Sorting – ORDER BY clause
- TOP clause
- Joining multiple tables
- Grouping rows,
- Aggregate functions
- Filtering row groups – HAVING clause
- Set operations (UNION, INTERSECT, EXCEPT)
- DDL – Data Definition Language
- Data types
- Creating tables – CREATE TABLE
- Integrity constraints
- Auto-numbering rows
- DML – Data Manipulation Language
- Adding data- INSERT
- Modifying data – UPDATE
- Removing data – DELETE
Audience and prerequisites
The participants should have the basic knowledge of Excel, no knowledge of data analysis is required.
Course participants receive completion certificates signed by ALX.
There are currently no entries
Enter your e-mail address and we will notify you about future dates
- Online (English) – your home, office or wherever you want
- Warsaw (EN) – Jasna 14/16A
- any other location (London, UK, EU) on request
Open training groups are no larger than 14 participants.
Private training can be conducted for groups of any size, according to the Customer's preference. To assure high quality and good learning environment, the group size should not exceed 14.
On-demand training costs and registration
The prices provided in the general tables cover the cost of an open (public) training for one person.
In the case of an on-demand training at the Customer's premises, you are required to provide the necessary facilities (room, computers, projector) and we will send an instructor to run the training. The price for a session is calculated for the whole group.
The cost does not change significantly along with the number of participants. There is no minimum number of participants but we recommend no more than 10-12 people because the training becomes much less effective with a bigger group..
The cost of an on-demand training consists of:
- the base training cost, plus
- travel and accommodation charges for the instructor
The base price of an on-demand training varies, but it is usually around the price of an open training for 3-5 persons (regardless of your group size).
The typical travel and accommodation price for the EU/Schengen zone is under 1250 GBP (1500 EUR) for a 5 day session. We do our best to keep it as low as possible in your location.
The payment can be in GBP or EUR, whichever you prefer. The quotation we will send you with the exact amount in your currency will be valid for 3 months, regardless of any changes in the exchange rate.
For more information, or to register to a group, please contact us at firstname.lastname@example.org and tell us know:
- the course(s) that you are interested in,
- your location,
- your preferred dates,
- the number of people you wish to train,
And any other questions that you may have.
We can also customise the training program of any of our trainings (or create a new one) according to your needs – whether you want to focus on particular solution used in your company, include material concerning a technology we do not usually cover, or create a tailor-made training.
For individual persons for whom all scheduled open training is inconvenient, we offer individual consultation. Virtually all of our training courses are available as such.
During individual consultations, while it can be a direct equivalent of a group training in terms of subject covered, we can also provide more than that – we can solve problems, help choose appropriate solutions and provide advice on the most effective use of chosen tools.
The price includes:
- course materials,
- snacks, coffee, tea and soft drinks,
- course completion certificate,
- one-time consultation with the instructor after course completion.