An Introduction to Data Analysis in MS Excel 2400-SP-EXCEL-WAD
The aim of the course is to present the stages of data analysis and the tools in MS Excel used for working with data at an intermediate level.
Course topics:
✓ Entering and editing data
Spreadsheet structure, selecting cells, entering data, inserting new cells, deleting data and cells, copying, cutting, and pasting data, creating ranges, custom lists, fill handle, keyboard shortcuts.
✓ Cell formatting
Standard cell formatting (general, number, percentage, currency, accounting, date and time, special) and custom cell formatting (using symbols: @, space, zero, asterisk, question mark, hash, underscore, and complex formatting).
✓ Finding, Replacing, and Cleaning Data
The Find and Replace tools (Find Next, Find All, Replace All, Advanced Options, Special Characters).
✓ Worksheet Design
Designing and formatting tables, designing user-friendly worksheets using graphic elements (including shapes, WordArt, and text boxes), creating organizational charts using SmartArt graphics.
✓ Formulas in Excel
Formula syntax, inserting formulas, dragging formulas, copying formulas, arithmetic expressions, working with parentheses, relative and absolute references, formula errors.
✓ Basic Functions
Using functions (inserting functions and using them efficiently), statistical, text, date, and time functions, best practices for working with functions.
✓ Advanced Functions
Complex functions, nested formulas, logical functions (logical tests, IF, AND, OR, NOT functions), lookup functions (VLOOKUP, HLOOKUP, VLOOKUP. HORIZONTAL, VLOOKUP), business examples.
✓ Working with Data
Sorting data (basic, custom, multi-level, based on cell values, colors, custom lists), subtotals, filtering data (numeric, dates, text, simple and complex, multiple criteria, Advanced Filter tool), data tables (design, properties, calculations, referencing data).
✓ Pivot Tables
Data analysis using pivot tables. Creating pivot tables, changing pivot table styles, updating pivot tables, statistics and calculation options, grouping and filtering variables, advanced pivot table options, fields and calculated fields, creating reports and summaries.
✓ Charts
Visualizing data using charts. Chart properties and formatting, chart styles, chart types: automatic, recommended, line, column, bar, pie, combo, treemap, scatter, and bubble. Advanced chart formatting: defining data series in charts, modifying advanced chart elements, interactive charts.
✓ Keyboard Shortcuts
Efficient use of keyboard shortcuts in daily work with various types of data and different numbers of observations.
✓ Business Examples
Real-world business examples involving data of various types and structures. Creating analytical reports.
Course coordinators
Type of course
Mode
Learning outcomes
Participants gained the ability to use MS Excel independently and take advantage of its versatile features. Thanks to the course, they know how to use Excel efficiently in their daily work, utilizing keyboard shortcuts, and are able to create analytical reports for analyzing data with varying structures and sample sizes.
Assessment criteria
An independent assessment task to be completed by the participant upon course completion. A minimum score of 50% is required to pass.
Bibliography
Materials prepared by the lecturer and made available to participants on e-learning platform