Course library

• More advanced Data Validation
• OFFSET function
• Advanced Filter
• Database functions
• SUBTOTAL function
• More advanced IF-type functions
• LOOKUP-type functions
• Lookup functions
• External data from the web
• External data from MS Access
• Microsoft Power Query add-in
• External data from a Microsoft SQL Server database
• XML data
• PivotTable
• PivotTable formatting
• Sorting and filtering field items
• Calculations in PivotTables
• Additional PivotTable options
• Data Model
• PivotChart

• Customizing the Quick Access Toolbar
• Customizing the Ribbon
• Advanced work with worksheets across multiple workbooks
• Paste Options
• Copying multiple items
• Importing external data into Excel
• Advanced number formats
• Custom number format
• Worksheet protection
• Advanced work with Excel tables
• Using references in formulas
• Comparing and combining values with operators
• Relative and absolute references
• Conditional Formatting rules
• Formula-based Conditional Formatting
• Function Library
• Text functions
• More advanced text functions
• Math functions
• Statistical functions
• Date and time functions
• Logical functions and conditional calculations
• Nesting functions
• Cell errors and error checking
• Naming cell ranges
• Data Validation
• Advanced series fill
• More advanced sorting
• AutoFilter
• Filtering data with slicers
• Finding and replacing specific values
• Removing duplicates
• Text to Columns
• Flash Fill
• Outline

• Getting to know Excel
• The Excel window
• The Ribbon
• Getting to know the workbook
• Creating and saving a workbook
• Opening a workbook
• Working with worksheets
• Navigating a worksheet
• Selecting a cell range
• Adjusting rows and columns
• Entering data into cells
• Cell values
• Moving and copying cell values
• Copying and pasting via the Clipboard
• Font formatting
• Alignment and orientation
• Borders and fill
• Basic number formats
• Date, time, and percentage formats
• Cell styles
• Windows Clipboard and formatting
• Excel tables
• Conditional Formatting
• Basic arithmetic formulas
• AutoSum
• Number and date series
• Text series and custom lists
• Sorting
• Recommended Charts
• Quick Analysis
• Pictures
• Printing
• Basic program settings
• Help in Excel

• Scheduling meetings
• Accepting a meeting invitation
• Proposing a new meeting time
• Canceling a meeting and calendar options
• Assigning tasks to colleagues
• Viewing your colleagues' calendars
• Sharing Outlook information with other users
• Delegate permissions
• Working offline
• Outlook offline folders
• Importing and exporting data
• Outlook data file
• Email account settings
• Creating a custom form
• Sending custom forms to other users
• Advanced work with custom forms

• People view
• Creating and printing contacts
• Organizing contacts
• Attaching contacts to a message
• Address Book
• Contact groups
• Calendar
• Creating all-day events
• Recurring events
• Editing calendar events
• Managing calendar items
• Sharing a calendar
• Tasks
• Managing tasks
• Journal
• Working with the Journal
• Notes

• Getting to know Outlook
• View options in Outlook
• Using Help in Outlook
• Outlook window layout
• Peeks
• Viewing received messages
• Creating a new message
• Editing email content
• Spell check in emails
• Working with attachments
• Replying to and forwarding messages
• Saving and printing messages
• Creating signatures
• Mail folders
• Junk email
• Flagging messages for follow-up
• Tracking messages
• Searching messages
• Organizing messages into subfolders and categories
• Arranging the message list
• Conversation view
• Quick Steps
• Automatic replies
• Managing email with rules
• Mail settings

• Speaker notes
• Slide transitions
• Animation effects
• Advanced animation effects
• Assigning actions to slide objects
• Hyperlinks
• Presentation timing
• Custom Slide Show
• Set Up Slide Show
• Controlling the slide show
• Presenter View
• Print settings
• Saving a presentation in various formats
• Advanced presentation save options
• Creating a template and theme
• Using custom templates and themes
• Custom program settings

• Inserting a table on a slide
• Tables from other applications
• Editing tables
• Creating and editing SmartArt graphics
• Other types of SmartArt graphics
• Inserting a chart on a slide
• Editing a chart
• Audio in presentations
• Inserting video into a presentation
• Slide footers
• Themes
• Slide backgrounds
• Working with slides
• Importing external data
• Sections in a presentation
• Editing the Slide Master
• AutoCorrect
• Spell check
• Comments
• Comparing two versions of a presentation
• Finding and replacing text and fonts

• Getting around the PowerPoint window
• Presentation view options
• Navigating a presentation
• Using Help
• New presentation
• Working with text boxes
• New slide and slide layout
• Paragraph level
• Tabs
• Paragraph formatting
• Inserting pictures
• Working with pictures
• Further picture adjustments
• Picture effects
• Shapes
• Merging shapes
• WordArt
• Symbols
• Equations

• Document sections
• Page background settings
• Inserting drop caps and special characters
• Custom cover page
• AutoText
• Document pagination
• Searching a document
• Replacing text
• Hyphenation
• Tracking changes in a document
• Working with comments
• Advanced header and footer settings
• Columns
• Creating a document outline
• Master document with subdocuments
• Automatic table of contents
• Custom table of contents
• Table of figures, list of tables, and other overviews
• Working with fields
• Footnotes and endnotes
• Bookmarks
• Cross-references
• Hyperlinks
• Translating text
• Envelopes and labels
• Mail Merge

• Basic Word settings
• Quick Access Toolbar
• Customizing the Ribbon
• Custom keyboard shortcuts
• Working with multiple documents
• Applying and modifying styles
• Creating styles
• Creating templates
• Editing a multi-page document
• More advanced paragraph numbering techniques
• Sorting entries in documents
• Converting text to a table
• Tabs
• Creating tables
• Adjusting table layout
• Adjusting table design
• Calculations in tables
• Designing an irregular table
• Creating charts
• Editing charts
• Inserting pictures
• Editing pictures
• Inserting and formatting SmartArt
• Inserting and formatting shapes
• Inserting a screenshot

• Introduction to working with Word
• Document views
• Read Mode
• Creating a new document
• Opening a document
• Navigating a document
• Entering and editing text
• Text selection techniques
• Copying and moving text
• Saving a document
• Page setup
• Text formatting
• Quick text formatting
• Paragraph formatting
• Numbered lists
• Bulleted lists
• Borders and shading
• AutoCorrect
• Spelling and grammar check
• Document language
• Headers and footers
• Printing documents
• Using Help

Sources of client information (website, social media, referrals, internal data). What to read from the meeting setting (office, café, the client's home). How to dress – rules and options for different clients.

How to read the environment in which the meeting takes place. How to open the conversation and with which questions. How to lead the conversation to find out as much as possible. What can ruin a meeting right at the start.

Handling objections — types, approaches, and recommended phrasing. Using emotions and stories in sales. Responding based on client typology (Apostle, Mercenary, Hostage, Terrorist). Specific closing techniques.

Occupational health and safety: Alcohol and workplace safety, employee rights and obligations in OHS, risk prevention, workplace injuries, and safety signs. A final certificate upon successful completion.

Occupational health and safety: Alcohol and workplace safety, employee rights and obligations in OHS, risk prevention, workplace injuries, and safety signs. A final certificate upon successful completion.

How to take care of the client after a meeting. What information to record and where to keep track of it. What to do when a deal falls through (or goes through). Working with referrals from existing clients.

Block 1: Connecting to and first contact with data

The key is to understand that Power Query is not Excel – importing is automated, data is "pulled in" from elsewhere, and everything can be edited and changed. The data is unified and protected from changes.

• Data sources: Connecting to Excel, CSV, a folder (importing all files at once), and a SQL database.
• Data types: Why it is critical to set the correct data type (Text, Whole Number, Date) right at the start.
• Interface and steps: How the step history works and how to go back or edit steps.

• Block 2: Cleaning and transformation

This is where 80% of the work happens. The goal is to get the data into a narrow, long table.

• Preparing the data model: Defining information needs and identifying necessary and unnecessary data.
• Removing noise: Deleting blank rows, duplicates, and unnecessary columns.
• Transforming text and numbers: Splitting columns (Split Column), extracting text, and basic math operations.
• Working with dates: Extracting the year, month, or day name without writing formulas.
• Conditional columns: "If – Then" logic in the graphical interface (e.g., categorizing customers by revenue).

• Block 3: Combining and reshaping

How to turn many tables into one functional whole.

• Append Queries: Stacking tables on top of each other (e.g., data for January, February, and March into a single report).
• Merge Queries: The equivalent of VLOOKUP/XLOOKUP, but far more powerful. Explanation of join types (Left Outer vs. Inner Join).
• Unpivot Columns: An absolutely essential technique – how to convert a “wide” table (with months in the column headers) into a “narrow” table suitable for Power BI.

• Block 4: Advanced techniques and automation

A look under the hood that separates beginners from pros.

• Parameters: How to create a dynamic report where the user simply changes the folder path and everything recalculates.
• Introduction to the M language: How to read the formula bar and make small manual edits you won't find in the menus.
• Loading Options: The difference between "Close & Load" to Excel vs. "Only Create Connection" vs. loading directly into the data model.
• Optimization: How to keep a query from taking forever to refresh (Query Folding – a brief explanation).

Sources of client information (website, social media, referrals, internal data). What to read from the meeting environment (office, café, the client’s home). How to dress — rules and options for different clients.