Excel Data Validation, Functions & Formulas course
<p>In this course you will learn how to prevent input errors using data validation and how to apply advanced Excel formulas: text, lookup, statistical and mathematical formulas and array functions.</p>
NRTO quality mark
- You prevent input errors with smart data validation.
- You work with text formulas, lookup formulas and array functions.
- You get practical tips from an experienced Excel trainer.
- You practise straight away with recognisable examples from everyday practice.
- You learn in a small group, with plenty of room for questions.
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.
Practical information
- Number of days
- 1 day
- Course times
- from 10 am to 5 pm
- Teaching hours
- 6 hours
- Education level
- Suitable for all levels
- Preparation time
- None
- Included
- Course materials and lunch on site
- Certificate
- At the end of the course you will receive a certificate of attendance
Language
The course is given in Dutch as standard. The trainer speaks English. English-language course materials can be used. If at least 3 participants register, the course can also be given entirely in English.
This course for your team?
Plan your own group on a date that suits you, at your office, at our premises or online. We tailor the content to your day-to-day work, and training a whole group is cheaper than individual bookings.
- Your date and location
- Tailored content
- Multiple groups or an academy are also possible
- More than 25 years' experience
What others say about this course
"If you want to do anything with data validation, this course comes highly recommended. After this course you will have learned a great deal about validating data."
“Simply a valuable course. Professional, up to date, high-calibre trainers. All in all: a course like this is not to be missed.”
“Learnit uses young, dynamic, motivated and well-trained trainers. The learning technique applied, both classroom-based and individual, is efficient.”
"A very pleasant and efficient way to master the material under expert guidance at a superb location."
"This training course comes highly recommended. Clear, well-structured explanations. They take the time for you and you get an answer to all your questions. Really a great course."
“I took the Excel course to broaden my overall knowledge, and I found this course very suitable for that.”
“An enjoyable experience and an instructive programme.”
"Good course, in which you work independently a great deal. Good course material and a pleasant trainer, who helps immediately with questions and explains things very clearly."
“I loved to see the possibilities what you can do with excel. Hope to be able to use everything we have learned.”
“A pleasant way of working through the material. A book with clear instructions and a trainer in the background who answers questions!”
"The course made a good impression on me. The material provided is excellent. Because I had not indicated in advance what knowledge I already had and what purposes I wanted to use the course for, the material offered was quite a lot for 2 days. So follow Learnit's advice and state this beforehand."
“A complete course with clear exercises, given by a very experienced and pleasant trainer. Highly recommended.”
“Excellent medium. No travel time. Easy to follow. Good and to the point. Recommended.”
“It was an instructive course and you could learn everything calmly at your own pace.”
“In a short space of time you get to grips with the basics of VBA/Excel, with the opportunity to use your own practical examples.”
“The webinar I attended was a good addition. Refreshing my Excel knowledge without any travel time. A number of familiar items supplemented with new or handy things.”
“A good way to get to grips with the basics of Excel.”
"I had never had any Excel training before, but I already had a reasonable command of the basics through the daily work I do. By taking this Excel course it is easier for me to carry out my daily work in a simpler way thanks to all the new knowledge I have gained."
“A good mix of explanation with examples and then getting to work on exercises yourself.”
"Insight into the possibilities of Excel that you know exist but have not yet applied yourself."
“Interesting course. To really master all the material, it is advisable to set aside enough time for it.”
“Very well prepared training with very easy to follow exercises that will make excel look less of complicated system.”
“What appealed to us about Learnit's teaching method: everyone in the group can work through the material in their own way, and if there are questions or comments, the trainer explains the right approach. That way, everyone worked through the material at their own level and pace.”
“A carefully designed course in which my learning objectives received concrete attention.”
“'Taking courses with Learnit comes highly recommended!'”
“Enjoyable course, learnt a lot and everything at my own pace.”
“A great introduction to getting more out of yourself and Excel.”
"The PowerPivot training course gave our group a great deal of insight. Along the way you pick up plenty of tips and tricks that make your work far more efficient. For people who process or analyse a lot of data, this course is definitely recommended."
“The Excel training is excellent, with good course material. Excellent trainer, certainly enthusiastic, and it is also geared towards practice.”
“It was pleasant to be able to work at your own pace and to ask specific questions whenever you got stuck.”
“A pleasant way of working, own pace, own choice of subject, good, recent laptops and an agreeable location.”
“An engaged, enthusiastic and experienced trainer, which makes the course accessible to everyone.”
"A good course, a pleasant trainer, and many topics that are personally useful to me in my work."
"I thought it was a good and worthwhile course. I learned a great deal from it. The trainer gave clear explanations and has extensive knowledge of Excel."
"A course at your own level where you get support as soon as you need it. This means you can learn efficiently at your own pace!"
“The course is well put together, with plenty of practical exercises. Enthusiastic trainer with a great deal of expertise. Thanks to the workbook and disc you take home, you can go over the material again at home or at work – ideal.”
"A course with strong content, in which good course materials are used to teach new things effectively and efficiently."
“Plenty of enjoyable exercises that you can join in with and apply straight away. A good, clear trainer who gives everyone attention.”
“LearnIt is the right place to follow a course. Everything is arranged down to the last detail, all kinds of questions can be raised and there is always a suitable answer. A great experience!”
“As a steel construction estimator I had worked with Excel a great deal — layout, colours and lines — but I lacked the basics of creating formulas and functions. For my work (specifications, drawings and quotations) I am constantly calculating hours, prices and surcharges. The additional work schedules (the adding up) now happen automatically, so faster and without errors. So this course taught me a great deal.”
“For people who have done little with computers, this is an excellent course.”
"The advantage, I found, was that you work individually, so that everyone can work at his or her own pace."
“To me, Excel was no more than a program with little squares... But that you could do so much with it ... A whole new (Excel) world opened up for me. I will definitely be applying this from now on. It is going to save a lot of time.”
"Well organised and clearly explained!"
“The training course was manageable within the time set. I learned what I came for.”
“It is a very pleasant course that you can follow at your own pace. If you have questions, there is a trainer who helps you on your way straight away. Really recommended.”
“The training course is focused, well structured and easy to follow and complete at your own pace within the time provided.”
“The content of the Excel 2003 course matched my learning objectives perfectly. The individual way of teaching, namely getting to work yourself, works very well. If you have questions, you get targeted help. The aftercare is also well arranged; you can still turn to the trainer at any time.”
“Recommended for anyone who wants to master the basics of Excel. Excellent course material that serves well as a reference work at home and at work. Thanks to the clear and well-structured course material, this also works very well for self-study.”
"A good, practice-oriented course that allows you to get started quickly yourself and to improve your daily working methods and make them more efficient."
Training formats
Classroom
1 day€ 550,-- Pleasant venue with a good trainer
- Small groups
- Inspiring and motivating
In-company
Bespoke trainingOn request- 100% for your group
- Bespoke training
- At your place or ours
Workshop
Up to 4 hours for groupsOn request- Suitable for events
- Interactive and inspiring
- Large groups too
Private training
At our place or yoursOn request- 100% just for you
- Achieve a lot in little time
- Whenever you want
Frequently asked questions
What will I learn in this course?
You will learn how to prevent input errors in Excel using data validation and how to apply advanced functions and formulas. Think of drop-down lists, error messages, text functions, lookup and reference functions such as INDEX and MATCH, statistical functions and array functions. You will pick up 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 together with colleagues in the same lists or import data from other systems. You want to prevent 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 fluently 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 work out together 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 alternate with practical exercises throughout, so you apply what you have learnt 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? 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 extensive practical experience and know exactly which pitfalls you 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 go home with practical knowledge that you can apply directly to your own Excel files. You also receive a Learnit certificate as proof of participation. You can reuse the examples and exercises later for reference.
How can I prepare?
No real preparation is needed. It is useful, though, to think in advance about Excel files or issues 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 for 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 receive a Learnit certificate as proof of participation. It is a useful addition to your CV and shows that you have expanded your Excel skills in a targeted way.
How do I register?
You can book easily via our website. Choose a date that suits you and complete the booking form. Do you have questions or would you prefer to discuss things by phone? Then contact our course advisers, who will be happy to help.
What does the course cost?
You will find the current price at the top right of the course page.