Excel courses

MS Excel Working Environment

• Introduction to the Excel working environment - title bar, quick access toolbar, ribbon, tabs, groups, working area, status bar, name box, formula bar.
• Names of rows, columns and cells.
• Effective use of help.

Working with Files and Worksheets

• Adding, removing, renaming and moving worksheets.
• Creating a new workbook from scratch.
• Opening and saving existing workbooks.

Working with Cells and Cell Formatting

• Entering text and numeric data into cells.
• Formatting text and cells for better clarity.
• Copying cells and using the "Paste Special" option.
• Selecting a continuous and non-continuous range of cells.
• Working with basic number formats.
• Formatting cells as a table.
• Creating automatic series for quick data entry.

Formulas and Functions

• Using basic formulas for calculations - addition, subtraction, multiplication and division.
• Automatic functions: sum, average, count of numbers, maximum, minimum.
• Copying formulas and functions for efficient work.

Sorting and Filtering Data

• Sorting data by different criteria.
• Filtering data using the automatic filter.

Getting Familiar with Excel, New Features and Main Differences Compared with a Lower Version of Excel

• MS Excel working environment, option to customize the quick access toolbar and ribbon
• Cell formatting, creating a custom format
• AutoComplete function, data validation

Conditional Formatting, Combining Multiple Conditions

Writing Formulas in MS Excel

• Copying formulas, difference between relative, absolute and mixed cell reference

Structured References

Basics of Working with Functions, Function Syntax

• Different ways of inserting a function - their advantages and disadvantages
• Overview and examples of the most frequently used mathematical, statistical, time, logical and lookup functions

Basics of Using Formulas and Functions

• Syntax of formulas and functions, nesting functions, division of functions into categories, working with names and references, naming ranges, naming ranges also with formulas, absolute and relative references, dynamic ranges and their intersections, linking worksheets - reference to another worksheet

Debugging Formulas

• Watch window, formula checking mode and panel, arrows, tools, evaluate formula, error checking

Circular References

• Removing intentionally circular references

Conditional Calculations and Logical Functions

• IF, nested IF, AND, OR, SUMIF, COUNTIF

Lookup Functions

• HLOOKUP and VLOOKUP, INDEX and MATCH, INDIRECT and ADDRESS

Review and Summary of Excel Basics

Excel User Interface

• moving around a worksheet and working with sheets, selecting a continuous and non-contiguous cell range, selecting a table cell formatting format settings, creating a custom format conditional formatting combining multiple conditions

Writing Formulas in MS Excel

• inserting formulas into cells, copying formulas AutoComplete function structured references difference between relative and absolute cell reference

Basics of Working with Functions, Function Syntax, Nesting Functions in MS Excel

• automatic functions: sum, average, minimum, maximum, count function categories, different ways of inserting a function - their advantages and disadvantages overview and examples of the most frequently used mathematical, statistical, time, logical and lookup functions

Sorting, Filtering and Summaries in Microsoft Excel

• tool for sorting data in a table automatic filter: custom automatic filter, use of wildcard characters in text criteria simple summaries (subtotals) and their use in practice

Overview of Options and Settings in the Program

• Main menu, toolbars, working environment settings and similar.

Creating Formulas

• Rules for writing formulas, relative and absolute cell reference, reference to a cell in another sheet or document, copying cells, sequences and series, use of the Paste Special function, swapping columns for rows and vice versa, definition of custom sequences, copy formatting function

Working with Data

• Sorting, filtering data using the automatic filter function, simple and compound subtotals, basics of working with a pivot table

Working with Large Tables

• Splitting the window, freezing panes, splitting, merging, finding, replacing, go to, options for printing large tables

Page Setup

• Appearance, margins, numbering, scaling before printing, creating a custom header and footer, repeated printing of first rows and columns

New Features in MS Excel and Main Differences Compared with a Lower Version of Excel

• Review of working with formulas and functions in Excel, function syntax, nesting functions
• Creating forms, controls and their connection with a cell
• Options for protecting cells, a sheet and locking a file with a password
• Naming a range, use in formulas and functions
• Referencing cells in other sheets and files

Freezing Panes

Logical Functions

IF, IFS, AND, OR

Lookup Functions

Databases and Tables

• advanced filter options, when to use database functions options for setting data validation and suitable use of functions intended for searching in tables

Use of Logical Values and Operations with Them

• use of the find and select operation use of the text to columns operation

Grouping Rows and Columns

Useful Use of Conditional Formatting

Use of OFFSET, COLUMN, ROW, RANK Functions

Basic Rules for Working with Tables in Excel

• Review of functional possibilities.
• Working with a database as a source for a PivotTable.

Creating a PivotTable

• Working with row fields, column fields, page fields and the data area.

Modifying a PivotTable

• Setting the properties of the table value field.
• Setting the properties of the table label field.
• Setting the properties of the whole table.
• Formatting a table using an automatic format.
• Working with a calculated field of a PivotTable.
• Displays in sheets using the page field.

Creating a PivotChart

• Formatting a chart.
• Selected numeric axis formats.
• Changing axis ranges.
• Adding data and data series to a chart.
• Changing the chart type.
• Access to external data sources, multiple consolidation ranges.

Options for Automating Data Processing

• Creating a macro - running a data query and subsequent processing with a PivotTable, creating a predefined chart and using it.
• Discussion.
• Conclusion.

Working with Objects

• Explanation of the concept of an object and its position in Office programs Inserting and creating objects in Excel Changing the size, format and other settings of different objects

Creating a Chart

• Description of different chart styles Explanation of the suitability of using different chart types and subtypes Preparing a table for chart creation Quick chart layout Creating sparklines (cell mini-charts) as cell content Creating different types of charts in Excel (column, line, pie, bar, area, scatter, map, radar, histogram, waterfall, funnel, etc.) Combo chart (using different chart types for individual series) Creating charts from non-continuous areas, editing source data Creating a chart from a named table

Editing a Chart

• Chart design and formatting, editing chart appearance, setting themes and custom color schemes, i.e. editing charts (colors, size, font, etc.) Use and formatting of chart elements (chart axes, axis titles, chart title, data labels, data table, error bars, trendline, gridlines, legend, up/down bars, etc.) Filtering data in a chart Chart placement (floating object, separate sheet) Primary and secondary chart axis Practical advice when creating charts Creating chart templates and their subsequent use, template management Pivot chart

Exporting Charts

• Printing charts Using charts in Word or PowerPoint Linking charts

Discussion and Further Learning Options After Completing the Training

Getting Data from Different Sources

• From Excel, from text files, from the web, from a folder, from PDF

Editing Data in the PowerQuery Editor

• Using the first row as headers
• Replacing values
• Changing the type of values in a column, using local settings
• Splitting a column
• Standard mathematical operations
• Adding a conditional column and a column from examples

Removing Duplicates

Creating a Data Model

• Multiple tables in the data model
• Relationships between tables, cardinality
• Filter direction
• Date table

Hierarchy

Calculations in the Data Model

• new column in a table - calculated field summary calculations for the whole table - measures

Functions Using Relationships Between Tables

• Related RelatedTable

Group of X Functions

Measures, Calculation Context, Filters and Anti-Filters in the DAX Language

Calculate Function

Getting Data from Different Sources

• From Excel, from text files, from the web, from a folder, from PDF

Editing Data in the PowerQuery Editor

• Using the first row as headers
• Replacing values
• Changing the type of values in a column, using local settings
• Splitting a column
• Standard mathematical operations
• Adding a conditional column and a column from examples
• Removing duplicates

Creating a Data Model

• Multiple tables in the data model
• Relationships between tables, cardinality
• Filter direction
• Date table
• Hierarchy

DAX Language

• Creating a measure
• Adding a column to a table
• Operations and functions of the DAX language

Data Visualization

• Different visuals - card, table, matrix, charts, map
• Formatting visuals
• Using themes, creating a custom theme
• Sorting data in charts
• Creating a custom tooltip
• Creating a details page

INTRODUCTION

• Introduction to Business Intelligence and differentiation of the product group PowerQuery, PowerPivot, PowerMap, PowerView, Power BI Adding and installing add-ins into the Microsoft Excel environment

POWERPIVOT

• Familiarization with the editor for data modeling

Data Model

• Loading data into the Model Processing and editing database data import from different file types Linking tables and creating relationships

Output Alternatives to Microsoft Excel from the Data Model

• Measures and creating calculated fields Key performance indicators Hierarchy and groups Slicers

POWERQUERY

Introduction, Description of the VB Development Environment

• Introduction to the topic, description of the VB development environment (project explorer, toolbox, window for viewing the graphical appearance of objects, code window, properties window), setting some IDE properties, docking windows, history, versions

Basics of Macros

• Explanation of the term macro, security, options for creating a macro, automated macro creation - advantages and disadvantages, macro editing, macro programming - getting familiar with Visual Basic for Applications (VBA), personal macro workbook

Data Types, Variables, Constants and Operators

• Variable declaration, basic data types, variable scope, data type conversion, constants, operators (+, -, / .... AND, OR...)

Flow Control and Branching

• Conditional and unconditional jumps, loops (For each, Do, While, With), program flow control - IF and SELECT commands

Arrays and Collections

• Working with arrays, static and dynamic arrays