LOGIN/Sign up
Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query
CPDUK Accredited | 50% OFF Certificate & Transcript
5 Students enrolled on this course 4.7 (6 Reviews)
Last updated April 15, 2021
Get Lifetime Access to 3000+ Courses for only £149! Find out more.
Register on the Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query today and build the experience, skills and knowledge you need to enhance your professional development and work towards your dream job. Study this course through online learning and take the first steps towards a long-term career.
The course consists of a number of easy to digest, in-depth modules, designed to provide you with a detailed, expert level of knowledge. Learn through a mixture of instructional video lessons and online study materials.
Receive online tutor support as you study the course, to ensure you are supported every step of the way. Get a digital certificate as a proof of your course completion.
The Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query course is incredibly great value and allows you to study at your own pace. Access the course modules from any internet-enabled device, including computers, tablet, and smartphones.
The course is designed to increase your employability and equip you with everything you need to be a success. Enrol on the now and start learning instantly!
What You Get With The Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query course
- Receive a e-certificate upon successful completion of the course
- Get taught by experienced, professional instructors
- Study at a time and pace that suits your learning style
- Get instant feedback on assessments
- 24/7 help and advice via email or live chat
- Get full tutor support on weekdays (Monday to Friday)
Course Design
The course is delivered through our online learning platform, accessible through any internet-connected device. There are no formal deadlines or teaching schedules, meaning you are free to study the course at your own pace.
You are taught through a combination of
- Video lessons
- Online study materials
Certification
After the successful completion of the final assessment, you will receive a CPD-accredited certificate of achievement. The PDF certificate is for £9.99, and it will be sent to you immediately after through e-mail. You can get the hard copy for £15.99, which will reach your doorsteps by post.
Who Is This Course For:
The course is ideal for those who already work in this sector or are an aspiring professional. This course is designed to enhance your expertise and boost your CV. Learn key skills and gain a professional qualification to prove your newly-acquired knowledge.
Requirements:
The online training is open to all students and has no formal entry requirements. To study the Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query course, all your need is a passion for learning, a good understanding of English, numeracy, and IT skills. You must also be over the age of 16.
Course Curriculum
| Introduction | |||
| Course Outline and Introduction | 00:04:00 | ||
| Minimum Requirements for the Course | 00:01:00 | ||
| Prepayments Introduction | 00:01:00 | ||
| Prepaid Expenses Models: Resources Download | |||
| Month End Date Prepaid Expenses Amortization Calculation | 00:00:00 | ||
| Exact Prepaid Expenses Payment Date Calculation | 00:00:00 | ||
| Accounting for Prepaid Expenses | |||
| Prepaid Expenses Accounting Definition: Prepayments | 00:03:00 | ||
| Prepaid Expense Example: How Accounting works for Prepayments | 00:03:00 | ||
| Advantages and Disadvantages of Prepaid Expenses | 00:03:00 | ||
| Excel Formulas Detailed: Introduction to three Excel Models | |||
| Introduction to PRO Excel Models and Formulas | 00:06:00 | ||
| Date Function | 00:05:00 | ||
| EOMONTH Function | 00:04:00 | ||
| DATEVALUE function | 00:03:00 | ||
| IF Function | 00:08:00 | ||
| IFS Function (Office 365 Only) | 00:07:00 | ||
| VLOOKUP Function | 00:07:00 | ||
| MATCH Function | 00:05:00 | ||
| INDIRECT Function | 00:02:00 | ||
| NAMED Ranges: Name Manager | 00:03:00 | ||
| Advanced Version of VLOOKUP Function | 00:07:00 | ||
| Formula Based Prepaid Expenses Model | |||
| Introduction to Model and Control Panel Tab (Important Sheet Tab) | 00:08:00 | ||
| Formula Based Prepaid Expenses Model – Deep Dive (Part 1) | 00:05:00 | ||
| Formula Based Prepaid Expenses Model – Deep Dive (Part 2) | 00:06:00 | ||
| Formula Based Prepaid Expenses Model – Deep Dive (Part 3) | 00:06:00 | ||
| IFS Function – Month End date Prepayment calculation | 00:04:00 | ||
| Prepaid Expenses – Closing Balance Summary Tab (Formula Based Summary) | 00:09:00 | ||
| Protecting Formulas Cells and Fields in the Model | 00:04:00 | ||
| Calculate Prepaid Expenses Amortisation from Exact Start Date | |||
| Exact Date Prepaid Amortisation calculation Intro | 00:03:00 | ||
| Formulas update and Model Changes for Exact Prepaid Exps Calculation | 00:03:00 | ||
| Formulas Update for Exact Date Prepaid Exps Amortisation (Part 1) | 00:04:00 | ||
| Formulas Update for Exact Date Prepaid Exps Amortisation (Part 2) | 00:03:00 | ||
| Formulas Update for Exact Date Prepaid Exps Amortisation (Part 3) | 00:02:00 | ||
| Formulas Update for Exact Date Prepaid Exps Amortisation (Part 4) | 00:07:00 | ||
| IFS Function – Exact Date Prepayments Amortisation | 00:04:00 | ||
| Data Validation Controls (Enhancing Data Input Controls with Protection) | 00:10:00 | ||
| Bonus: Prepayment Model with Opening Balance Calculation (Part 1) | 00:08:00 | ||
| Bonus: Prepayment Model with Opening Balance Calculation (Part 2) | 00:09:00 | ||
| Additional Material: Resources | 00:00:00 | ||
| Prepaid Expenses Summary with Power Query and Pivot Table | |||
| Power Query and Pivot Table Prepayment Summary Table Intro | 00:06:00 | ||
| What is Power Query and Some Awesome Resources for Power Query learning | 00:07:00 | ||
| Power Query and Pivot Table Summary – Deep Dive (Part 1) | 00:05:00 | ||
| Power Query and Pivot Table Summary – Deep Dive (Part 2) | 00:04:00 | ||
| Power Query and Pivot Table Summary – Deep Dive (Part 3) | 00:05:00 | ||
| Power Query and Pivot Table Summary – Deep Dive (Part 4) | 00:09:00 | ||
| Using Array Formulas to Add Formula Protection | 00:04:00 | ||
| Bonus: Allocate Prepaid Expenditure Cost Centre Wise – 1 | 00:02:00 | ||
| Bonus: Allocate Prepaid Expenditure Cost Centre Wise – 2 | 00:08:00 | ||
| Bonus: Prepayment Model with Opening Balance Calculation (PQ and PT Version) | 00:13:00 | ||
| Advanced VBA Prepaid Expenses Amortisation Model | |||
| Changing Macros Security in Excel | 00:05:00 | ||
| Complete Walkthrough – Advanced VBA Prepaid Expenses Amortisation Model | 00:06:00 | ||
| Bonus : New Version – Excel VBA Model for Prepayment Expenditure | 00:08:00 | ||
| BONUS: Dynamic Dashboard for Divisional Profit and Loss statements: Easy Way | |||
| Dynamic Dashboard Overview | 00:07:00 | ||
| Importing Profit and Loss Statements Source Files and creating YTD P&L Sheets | 00:08:00 | ||
| Creating Dynamic Data Validation | 00:02:00 | ||
| Creating Named Ranges for Dynamic Table Arrays | 00:03:00 | ||
| Dynamic Date Column Headings for each Divisional PL Table | 00:02:00 | ||
| Dynamic Month and YTD Dashboard tables headings (PRO TIP) | 00:03:00 | ||
| Dynamic VLOOKUP Formula – Preparing First section of the Dashboard | 00:04:00 | ||
| Creating Rolling Dashboard with Dynamic VLOOKUP Function | 00:08:00 | ||
| IMPORTANT : Error Checking for your reports/Dashboard (PRO TIP) | 00:03:00 | ||
| Data Prep for Visualization: AREA Charts (Awesome trick using #NA Function) | 00:05:00 | ||
| Visualization: AREA Charts for Month – Revenue, Gross Profit and Net Profit | 00:05:00 | ||
| Visualization DONUT Charts Revenue, Gross Profit and Net Profit (Part 1) | 00:03:00 | ||
| Visualization DONUT Charts Revenue, Gross Profit and Net Profit (Part 2) | 00:06:00 | ||
| Power Query & Pivot Tables based Dashboard without any Formulas, Fully Dynamic | |||
| Introduction – Formula-less Dashboard – Fully Dynamic and easily refreshed | 00:05:00 | ||
| Understanding the data files before building dashboard | 00:02:00 | ||
| Consolidating Reports with Power Query (Get & Transform) , How to install PQ | 00:08:00 | ||
| Dynamic File Path Trick in Power Query with Parameters (Amazing trick) | 00:06:00 | ||
| Conditional Cumulative totals with SUMIFS Function | 00:04:00 | ||
| Bonus: Conditional Cumulative totals with Power Query Custom Formula (M Code) | 00:06:00 | ||
| Dashboard Creation – Pivot Table showing Month and YTD KPIs division wise | 00:06:00 | ||
| Dashboard Creation Donuts Charts linked with Pivot Table (Replicate Charts fast) | 00:08:00 | ||
| Dashboard Creation – Line Charts | 00:08:00 | ||
| Update Dashboard with Additional Divisional Data with Few Click (Magical) | 00:03:00 | ||
| Thank you | |||
| Thank you | 00:02:00 | ||
| Ultimate Prepaid Expenditure Model (Super Bonus) | 00:02:00 | ||
| Resources | |||
| Resources – Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query | 00:00:00 | ||
Course Reviews
No Reviews found for this course.
£25
92% OFF
14-Day Money-Back Guarantee
-
-
Tutor Support
-
Fully Online
Frequently asked questions
Can’t find the anwser you’re looking for ? Reach out to customer support team.
There are no specific prerequisites for this course, nor are there any formal entry requirements. All you need is an internet connection, a good understanding of English and a passion for learning for this course.
You have the flexibility to access the course at any time that suits your schedule. Our courses are self-paced, allowing you to study at your own pace and convenience.
For this course, you will have access to the course materials for 1 year only. This means you can review the content as often as you like within the year, even after you've completed the course. However, if you buy Lifetime Access for the course, you will be able to access the course for a lifetime.
Yes, upon successfully completing the course, you will receive a certificate of completion. This certificate can be a valuable addition to your professional portfolio and can be shared on your various social networks.
We want you to have a positive learning experience. If you're not satisfied with the course, you can request a course transfer or refund within 14 days of the initial purchase.
Our platform provides tracking tools and progress indicators for each course. You can monitor your progress, completed lessons, and assessments through your learner dashboard for the course.
If you encounter technical issues or content-related difficulties with the course, our support team is available to assist you. You can reach out to them for prompt resolution.