Microsoft Excel - How to Build Business Applications

A Practical Guide to using Excel Functions to Build Small Business Applications
4.9 (5 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
19 students enrolled
$19
$55
65% off
Take This Course
  • Lectures 22
  • Length 1.5 hours
  • Skill Level Intermediate Level
  • Languages English, captions
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 2/2016 English Closed captions available

Course Description

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.

What are the requirements?

  • Students should be aware that the exercises are challenging and to get the best out of the materials a reasonable effort is required on the part of the individual student to complete the exercises provided
  • Students are provided with exercises as downloadable resources
  • Solutions for all of the exercises are provided in both video and Microsoft Excel templates
  • The course has been developed using Microsoft Excel 2016, however, previous versions from Excel 2010 onward will be suitable

What am I going to get from this course?

  • Develop a small business application which utilises Key Excel business functions
  • Develop complex formulas in Microsoft Excel using IF, Nested IF, AND and OR statements and combinations of these functions in the development of a small business application
  • Understand and be able to create Rules Tables to manage business rules in Microsoft Excel applications
  • Link data spreadsheets among multiple spreadsheets in order to develop user friendly interfaces
  • Know how to use validation techniques in their developed applications in order to ensure the integrity of their data
  • Understand the importance of, and be capable of, properly testing developed formulas
  • Know how to selectively protect their Excel applications at workbook, worksheet and cell levels so as to ensure that users cannot accidentally erase or modify important information in applications

What is the target audience?

  • This course is suitable for any student who has a basic knowledge of Microsoft Excel
  • The course is aimed at students who wish to advance their knowledge of using particular Excel functions and how to apply them in practice
  • Suitable for students who need to be able to manage and manipulate data in Excel
  • Students who need to use Microsoft Excel more efficiently
  • It is not suitable for an absolute beginner

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Course Introduction
03:48

This lecture explains the structure of the course and the approach taking to achieving the learning putcomes.

04:36

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.

Section 2: IF Statements
05:08

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.

04:58

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.

Section 3: Introduction to AND and OR Statements
04:51

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.

03:14

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.

01:50

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.

Section 4: A Business Example using Rules Tables
05:28

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.

04:58

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.

04:33

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.

02:39

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.

02:19

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.

Section 5: The HelpLess Airlines Application
04:09

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.

02:59

This lecture explains the Age Discounts which are implemented by HelpLess Aiirlines.

01:34

This lecture describes the Luggage Rules that HelpLess Airlines implements for its' passengers.

01:41

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.

Section 6: HelpLess Airlines Solutions
04:13

This lecture explains the solution to the Age Discounts problem.

04:44

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.

04:22

In this lecture the solution for the last three rules are explained. The solution is available as a downloadable resource with this lecture.

Section 7: Validating and Protecting Your Data
05:34

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.

05:17

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.

Section 8: Course Conclusion and Bonus Lecture
01:17

In this lecture we will review the techniques which students have learned in the course.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Mr. Dave Murphy, Professional Project Manager and College Lecturer

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.

Ready to start learning?
Take This Course