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

Course description

Are you jealous of a colleague who uses flashy keyboard combinations? Have you seen Excel gurus get complicated jobs done in a few well-placed clicks? Have you ever stared puzzled at long, seemingly unintelligible functions? Join our course, and become the one others turn to for Excel advice!

Information

Duration

• 16 hours

Prerequisites

• Basic knowledge of the Excel program

Course outline

Revising the basics

• Useful keyboard combinations, shortcuts

• Speeding up every day work

• Using series

• Quick Analysis tool for tables

Using formulas

• Basic formulas

• Formula operators in excel

• Absolute and relative references

• Simple mathematical and statistical functions

• COUNTIF, SUMIF, AVERAGEIF functions

• The IF function

• Nesting IF functions

• Date and time calculations

• Most important date functions

• Text functions and how to use them

• VLOOKUP and HLOOKUP – when and how to use them

• Using VLOOKUP to find exact and not exact match

• Combining VLOOKUP and IFERROR

Visualizing the data

• Creating different charts

• Using the Recommended Charts tool

• Formatting and customizing charts; using the Quick Formatting options

• Chart types and when to use them

• Combined charts

• Trendlines, two Y axis charts, waterfall charts and other curiosities

• Linking charts to data

• Conditional formatting

• Data bars, colour scales and icon sets in cells

• Complex conditional formatting

• How and when to use Sparklines

Text management, data types

• Reading text files into Excel

• Text to columns

• Correct wrong text formats

• Troubleshooting typical number and date importing errors

Data protection, restricting data entry

• Worksheet protection

• Protecting the structure of the workbooks

• Password protecting cell ranges

• Passwords to open and modify workbooks

• Protecting shared workbooks

• Track changes in shared workbooks

• Data validation in cells, input messages and error messages

• Highlighting invalid data

• Creating in-cell dropdown lists

Pivot table basics

• Creating a Pivot table

• Pivot table views and layouts

• The Recommended Pivot tables tool

• Using the Slicer and the Timeline

• Formatting options

• Calculation options

Revision, practice, complex exercises