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."
“Good impression. Knowledgeable and patient trainer”
“Enjoyable course because you work through the exercises yourself at your own pace. Skipping things you already know is no problem at all.”
“Good course for beginners, even if you have no affinity with computers at all. Anyone can do it!”
“I took the Excel course to broaden my overall knowledge, and I found this course very suitable for that.”
"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."
“Doing is learning”
“The Open Learning format course allows you to practise and perfect the areas you work with on a daily basis. You can also expand your knowledge by working through the modules in the course book.”
“Very professional, good explanations, easy to follow from your own workstation via the internet.”
“The open learning format is a good way to follow a course. You can take the course at your own pace, with or without prior knowledge.”
“Excellent course! I thought I knew Excel, but I still learned nice new things that I can apply on a daily basis.”
"M. van Es, Kales - "No classroom instruction, but working at your own pace on the topics that matter to you." - Rating: 7.6"
"This course gives you insight into the possibilities of VBA, teaches you to understand existing macros, and gives the candidate a basis for building modules 'from scratch'."
“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.”
“The trainer takes the time and explains things well.”
“A pleasant way of working, own pace, own choice of subject, good, recent laptops and an agreeable location.”
"If you want to get acquainted with the use of macros, this is an excellent starting point. You can record and program macros. This course covers recording macros only. You learn programming during the VBA course. After completing this course you have a complete picture of how recording a macro works."
"Excellent course, the open learning format turns out to suit me very well"
“'The first time I have attended a webinar. Definitely worth repeating. Clear explanation. Clear presentation.'”
“Good impression, had two enjoyable days. Learned a lot!”
“A clear course that is very easy to follow. Recommended for anyone working in sales.”
"Good course. The trainer has extensive knowledge of Excel and explains things calmly and clearly."
“The course book and the software are very pleasant to read and work through. The trainer was particularly active in helping to solve problems. I am impressed by her knowledge, at various levels. She switches easily from one problem / level to another. Her explanations are very clear and motivating. She is also extremely welcoming. The location is easy to reach, by bike and by car. The reception is pleasant and professional. The lunch was excellently arranged, the staff very hospitable and attentive. Clean toilets! Highly recommended, I would happily take another course there. An organisation I would certainly recommend.”
"Very practical Excel training that you can join at any level. The trainer gives you real knowledge of Excel. The practical handy tricks during the training in particular mean that afterwards you can apply everything in practice."
“Practice-oriented, ties in seamlessly with your work.”
“I took the Excel 2007 pivot tables course for advanced users and I thought it was a good course, with a highly capable trainer. Questions are answered well and the trainer's knowledge can only be described as very good. Highly recommended!”
“Excellent course material, a teacher with good knowledge of Excel.”
“A carefully designed course in which my learning objectives received concrete attention.”
“A short, clear course for someone who has worked with pivot tables little or not at all but would like to learn.”
“Very good course! Everything is perfectly organised and I would certainly recommend taking a course at Learnit.”
“I attended the two-day Office Basics Excel 2003 course. First of all, it was very easy to reach (less than a 10-minute walk from Amsterdam Rai station). The course went well. There was a pleasant atmosphere and the trainers' explanations were excellent! What is nice is that if you have skills that are not covered in the course, you can mention this to the trainer, who will then find the assignments for you so that you can learn those as well. All in all I had an excellent experience and learned a great deal!”
“Excellent course thanks to good guidance and clearly structured assignments that allowed the newly acquired knowledge to be applied straight away.”
"Highly knowledgeable trainer, good course material, good clear assignments and cases that challenge you and motivate you to work out the solutions. Room for issues from your own organisation! A really enjoyable and instructive training course – so much so that it was hard to stop at the end of the course days! Thank you!"
“Over the past 2 days I followed the Excel Basics course and it has made me a good deal more skilled with Excel.”
“A clear and complete course, given by a highly competent trainer who conveys the course material in a clear and structured way.”
“The course material was very clear, and it was explained step by step how to carry out a particular action. The exercises were related to everyday situations. With an open learning format you can of course set your own pace, which suits me fine.”
“Very good training course, clear explanations. The atmosphere is good because the groups are not too large. The trainer is highly knowledgeable and takes all the time needed when there are questions. Very handy and usable in practice. Learned a lot that I can put to good use.”
"A good course covering many topics in Excel. It gives you a solid introduction to using various formulas and features of Excel."
“For a beginner like me this was a very pleasant and concise training course. Starting from a low level, I gained a lot of confidence and can now genuinely put what I have learned into practice.”
“I would certainly recommend this course. It not only increases your knowledge of Excel, but also gives you the 'tools' to approach it in a problem-solving way. I also liked the trainer's personal teaching style. He had tips for working life based on his own experience and tried to coach every participant in that way.”
“Interesting course. To really master all the material, it is advisable to set aside enough time for it.”
“The Excel 2007 basics course I took at Learnit in Groningen gave me, in two intensive days, a great many tools that I can use on a daily basis. The result exceeded my expectations. In two days, the Excel 2007 shortcut on my desktop has gone from being a link to gibberish to a calculation programme in which, alongside ordinary sums, I can also produce and save minimums, maximums, charts and averages.”
“A very pleasant way of learning, straight from the book with support from the trainer and being able to set your own pace.”
“The general impression of the course as a whole is good. You clearly get something out of this course and you can apply it at work straight away. The trainer has plenty of expertise and listens carefully to the participants.”
“Pleasant to be able to take part in the course at my own pace while still having plenty of room for questions and comments!”
“Learnit is a really good training organisation. What I like is that you also get the book, so you can always practise.”
"I learned a great deal from the Excel Basics course. You can follow it in a quiet setting, at an easily accessible location and with a professional trainer, at your own pace. Course material consisting of a clear book alongside appealing examples in Excel to practise with. In short: highly recommended!"
“Well organised, clear and focused. Nice to be able to work through the course material at your own pace.”
"Very practically oriented, brought my own documents to practise with, so that what you learn can be put into practice straight away. A pleasant way of taking a course, plenty of practice material."
“It was a lively, clear training course given by a young but experienced teacher who could explain everything clearly and simply to someone starting from scratch. In short, a good training course at a good location with a good teacher.”
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.