This course will transform your ability to use Microsoft Excel as a business tool - not just as a great piece of software! It is aimed at students who have a basic knowledge of Microsoft Excel and who want to learn more advanced techniques in order to become expert in the key skills for using Excel as a business application. The course builds your skills in the use of key Excel formulas in a step-by-step manner, with practical exercises and solutions provided at each stage. On completion students will be expert in the use of IF, AND, OR to build complex business rules, will be able to Validate their data and will be able to selectively protect their application at the workbook, worksheet and cell level. They will have developed a small business application which will illustrate how to use these formulas in a practical and useful manner.
This lecture explains the structure of the course and the approach taking to achieving the learning putcomes.
It is assumed that you will have a reasonable basic ability to use Microsoft Excel. However, for those who may need to refresh their memory, this lecture provides a quick overview of the key techniques required to proceed with the rest of the lectures, in particular the concept of Absolute Referencing.
This lecture will introduce you to the concept of IF statements in Excel. At the end of this lecture you will be able to understand and create IF statements to evaluate simple conditions. You should attempt Practice Exercise 1 at the end of the lecture and the solution is provided as a downloadable resource.
In this lecture you will extend your knowledge of IF statements to include the use of Nested IF's in order to evaluate multiple conditions using one formula. By the end of the lecture you will be able to construct complex nested IF statements and understand the importance of thoroughly testing results. You should attempt Practice Exercise 2 before reviewing the solution which is available as a downloadable resource.
This lecture introduces you to AND statements in Excel. At the end of the lecture you will understand the purpose and structure of AND statements and be able to apply them to simple conditions.
This lecture will introduce you to the use of IF and AND statements combined in order to evaluate complex business conditions. You should attempt to complete Practice Exercise 3 before reviewing the solution which is available as a downloadable resource.
This lecture will introduce you to the use of OR statements. You should attempt to complete Practice Exercise 4 before reviewing the solution which is available as a downloadable resource.
This lecture introduces you to the use of IF, AND and OR statements as they apply to a common business based problem. Following completion of the lecture you should download and attempt Practice Exercise 5. A sample solution is also available.
This lecture introduces the concept of a Rules Table and explains the importance of using a rules based approach in developing Excel applications. By the end of the lecture you will know how to set up such tables.
Having learned how to set up a Rules Table in the previous lecture, this lecture explains and demonstrates how to use a Rules Tables in a business situation.
This lecture explains why Absolute Referencing is so important in developing applications in Excel, and extends the business example to providing service to more than one customer.
Having learned how to create Rules Tables, this lecture explains how to move such tables to other worksheets and maintain the link between the developed formulas and the new location of the Rules Tables.
In this lecture students are introduced to the core application which will be developed from here on in. The approach to the lectures to follow and the learning appraoch are outlined.
This lecture explains the Age Discounts which are implemented by HelpLess Aiirlines.
This lecture describes the Luggage Rules that HelpLess Airlines implements for its' passengers.
This lecture describes the rules that HelpLess Airlines uses for allowing passengers to book Priority Boarding, reserve specific seat rows in the aircraft and the tax rules for Value Added Tax.
This lecture explains the solution to the Age Discounts problem.
This lecture explains the solution to the Luggage Charges problem. This is the most complicated of the rules and will require some thought to work through.
In this lecture the solution for the last three rules are explained. The solution is available as a downloadable resource with this lecture.
In this lecture you will learn how to ensure the quality of the data in your application by applying Excel validation techniques. You should attempt Practice Exercise 6 by modifying the Exercise 6 Resource File provided as a downloadable resource.
In this lecture you will learn how to selectively protect the contents of your application. Practice Exercise 7 should be attempted by modifying the Exercise 7 Resource File provided as a downloadable resource.
In this lecture we will review the techniques which students have learned in the course.
With over twenty years experience as a systems analyst and project manager with significant experience in the design and implementation of major projects for some of the worlds largest companies, Dave Murphy changed career track in 2000 and has been a professional educator for over sixteen years at university level. His main interests are in Business Information systems, Process Analysis and Project Management. Dave has a B.Sc (Hons) from Trinity College Dublin and an M.Sc Degree in Computing.