What will I learn in this course?

You will learn how to use data validation to prevent input errors in Excel and how to apply advanced functions and formulas. Think of drop-down lists, error messages, text formulas, lookup and reference formulas such as INDEX and MATCH, statistical formulas and array functions. You will get plenty of practical tips to make your work in Excel more efficient.

Who is this course for?

The course is intended for experienced Excel users who regularly work with larger files and want to expand their knowledge of formulas. You often work with colleagues in the same lists or import data from other systems. You want to avoid errors and get more out of your data.

What prior knowledge do I need?

You are at the level of the Excel Intermediate course. You work confidently with basic formulas, know functions such as VLOOKUP and IF and feel at home with larger worksheets. Not sure? Get in touch and we will look together at which level suits you.

What does the course look like?

We start with an introduction to data validation and its benefits. After that you will work in turn with text formulas, lookup and reference formulas, statistical and mathematical formulas and array functions. Short explanations are alternated throughout with practical exercises, so that you apply what you have learned straight away.

What is the delivery format?

The course is classroom-based and taught in a small group. This leaves plenty of room for questions and your own examples. Would you rather train with your whole team? Then you can also take this course in-company.

Who teaches the course?

The course is delivered by an experienced Excel trainer from Learnit. Our trainers have a great deal of practical experience and know exactly which pitfalls you will come across in practice. They adapt their explanations to the level and working situation of the participants.

What do I get after the course?

You will go home with practical knowledge that you can apply straight away to your own Excel files. You will also receive a Learnit certificate as proof of participation. You can use the examples and exercises later on for reference.

No real preparation is needed. It is useful, though, to think in advance about Excel files or questions from your own work that you are struggling with. You can bring these along during the course, so that the trainer can help you specifically.

What if I can't attend?

Unable to attend? Let us know as soon as possible. We will look at a suitable solution together and reschedule you to another date free of charge, provided you cancel in time. You can find the cancellation conditions on our website.

Do I receive a certificate?

Yes, afterwards you will receive a Learnit certificate as proof of participation. It is a useful addition to your CV and shows that you have deliberately extended your Excel skills.

Booking is straightforward via our website. Choose a date that suits you and complete the registration form. Do you have questions or would you prefer to discuss things by telephone? Then contact our course advisers, who will be happy to help you.

What does the course cost?

You will find the current price at the top right of the course page.

Excel Data Validation, Functions & Formulas

Name: Excel Data Validation, Functions & Formulas
Price: 550.0 EUR
Availability: InStock

Do you work on the same files together with colleagues? Then you will recognise this: everyone enters data in their own way and before you know it your list is polluted. Data validation keeps your data clean and consistent. In this course you will learn how to go about this and how to get much more out of your data with advanced functions and formulas. Practical, immediately applicable and focused on the challenges you come across in your work.

Description

Keep your Excel lists clean

When several people work in an Excel file, errors soon creep into the data. Everyone has their own way of entering data, and you see that reflected in inconsistent lists. With data validation you limit the input to what makes sense. Think of a figure between 1 and 10 or a percentage up to 100. That way your data remains usable for further processing.

Advanced functions and formulas

In addition to data validation, we cover a broad range of functions and formulas. You will work with text functions to convert incorrectly imported data, with lookup functions for when VLOOKUP does not provide the answer, and with array functions that generate results in one go without intermediate steps. Along the way, the trainer shares plenty of practical tips to help you get even more out of your Excel data. By the end of the course you will be able to manage your files better, prevent errors and build more complex calculations in a clear, structured way.

Topics

Benefits and forms of data validation
Drop-down lists, input messages and error messages
Unique data with data validation
Conditional drop-down lists with INDIRECT
Text functions: PROPER, UPPER, LEN, LEFT, RIGHT, TRIM
Combining, replacing and substituting text
Lookup and reference formulas: INDEX, MATCH, INDIRECT, CONVERT
Statistical formulas: AGGREGATE, RANK.EQ, LARGE, SMALL
Mathematical formulas: ROUND, SUMIF, SUMPRODUCT
Array functions: SUM, AVERAGE, TRANSPOSE, FREQUENCY

Result

After this course you can set up data validation independently and prevent input errors in your Excel lists. You work smoothly with text formulas to clean up data and with lookup and reference formulas that go beyond VLOOKUP. You apply statistical and mathematical formulas to larger datasets and you know how to use array functions to carry out several operations in a single formula. Your Excel files are cleaner, more intelligently structured and you work considerably faster.

Target audience

This course is intended for experienced Excel users who want to keep their lists consistent and expand their knowledge of functions and formulas. You regularly work with larger datasets, share files with colleagues and want to prevent input errors. Whether you work in finance, administration or analysis: if you find that standard formulas no longer get you there, this is the course for you. Also useful if you often import data from other systems.

Teaching method

The course is classroom-based and taught in small groups. This leaves plenty of room for questions and your own examples. The trainer alternates short explanations with practical exercises, so that you apply what you have learned straight away in Excel. You work with recognisable examples and pick up tips and tricks along the way that you can use directly in your own work. Would you rather work through this material with your whole team? An in-company version is also possible.

Prior knowledge

You have Excel experience at the level of the Excel Intermediate course. You work comfortably with basic formulas, you know functions such as VLOOKUP and IF, and you feel at home with larger worksheets. Not sure about your level? Just get in touch and we will look together at which course suits you best.

Language

The training is given in Dutch as standard. The trainer has a good command of English. English-language course material can be used. With a minimum enrolment of 3 participants, the training can also be given entirely in English.

Number of days:

1 day

Course times:

from 10 am to 5 pm

Study load:

7 hours

Education level:

This training is suitable for all levels.

Exemptions:

Exemptions do not apply to this course.

Preparation time:

No preparation time applies to this course.

Certificate:

At the end of the course, a certificate is issued as proof of participation.

The best training for you

At Learnit you will always find a course that takes you further. From (technical) IT skills to communication or personal leadership. You can also choose the training format that suits you best: a classroom course, a tailor-made course at your own office, an online course or an e-learning. We have the right course and the right format for everyone. Because learning in the way that suits you best delivers the best results.

Moving forward together

Taking a training course at Learnit means learning together. Together with your experienced trainer, together with your course group or together with your colleagues. And whether you take the training at home, at your office or at one of our training locations, we are always available to help you. To share our knowledge with you in a way that is genuinely useful to you. Getting further together through personal attention and understanding each other well; that is what we believe in.

Apply it directly in your work

Learning theory from a book that you can then hardly use in your work? Not at Learnit! Whichever training you take with us, you can put it to use at your workplace straight away. Because of the unique learning objectives you set together with the trainer at the start of the training. Because of the alternation between theory and (interactive) practical assignments. And by bringing the challenges you face in your daily work along to the training.

Excel Data Validation, Functions & Formulas course