Microsoft Excel: Building Business Applications | A Practical Guide To Using Excel Functions
Learn how to use Excel functions to build powerful business applications. This guide covers everything you need to know, from basic functions to advanced macros.
----------
Microsoft Excel - Building Business Applications | A Practical Guide to using Excel Functions to Build Realistic Business Applications
---------
What you'll learn
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
----------
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
00:00 Introduction to A Practical Guide to Building Small Applications in Excel
03:48 Review of the Essentials
08:33 Introduction to IF Statements
13:41 Nested IF Statements
18:47 Introduction to AND Statements
23:38 Combining IF and AND Statements
26:53 Introduction to OR Statements
28:52 Applying Nested IFs to a Business Based Example
34:20 Setting Up The Rules Table
39:19 Using a Rules Table
43:52 More Customers - Using Absolute References
46:31 Moving the Rules Table
48:59 Introduction to the HelpLess Airlines Application
53:08 Rule 1 - Age Discounts
56:07 Rule 2 - Luggage Charges
57:42 Rules 3 4 5 - Priority Booking Row Reservations and Value Added Tax
59:32 Rule 1 - Age Discount Solution
01:03:45 Rule 2 - Luggage Charge Solution
01:08:29 Rule 3 5 5 - Priority Row Reservation and VAT Solutions
01:13:00 Introduction to Data Validation
01:18:34 Protecting Your Data
01:24:00 Course Conclusion and Bonus Lecture
#excel
