Excel Advanced (angol nyelvű képzés)

Course description

Macros. Multidimensional searches. Data tools. Dynamic array functions. Excel has many amazing features for the advanced users. Find all the hidden gems in Excel, become a real Excel ninja!

Prerequisites

Confident usage of Excel, knowledge of basic functions, absolute and relative references

Course outline

A little revision

• Useful keyboard combinations, shortcuts
• Tips and tricks for quicker work
• Basic functions, references

Advanced functions, formulas

• Reaching data in external files
• Creating and managing links
• Advanced formulas, special mathematical and statistical functions (SUMIFS, SUBTOTAL, etc.)
• Special date and time functions
• Database (D-) functions

Using special functions

• Complex search functions (INDEX, MATCH, OFFSET)
• Multidimensional searches, combining VLOOKUP with other search functions
• Array functions and formulas
• Solving special problems with array functions
• Improving calculation speed, optimizing workbooks
• Analysing calculation steps, error correction

Using named ranges

• Name box
• Manual and automatic naming of ranges
• Managing names
• The (INDIRECT) function
• Clever usage of named ranges and dropdown lists

Data tools in Excel

• Goal seek
• Scenario manager
• Data tables
• Consolidate
• grouping, subtotals
• Advanced filters
• Creating and using web queries
• Data connections, Excel Data Model

Advanced pivot tables

• Pivot table basics
• Calculated fields
• Calculated items
• Displaying data as a percent of a given total
• GETPIVOTDATA function
• Sort and filter the Pivot table, special filtering
• Slicer, Timeline
• Attaching one slicer to many pivot tables
• Pivot charts
• Standalone pivot charts
• Understanding the pivot cash
• PowerPivot basics
• Data connections, connecting to multiple data sources

Macros

• A little theory
• Using the macro recorder
• Storing macros
• Absolute and relative references in macros
• What’s behind the macro? a quick peek at the code
• Errors in macros
• Running macros from keyboard shortcuts, buttons and menus
• Macros on the internet