In today’s competitive market, advanced Microsoft Excel skills are essential for professionals managing financial data, accounting workflows, and dynamic reporting. This course is your gateway to mastering Microsoft Excel, guiding you through creating automated dashboards and sophisticated models with VBA and Power Query. As UK organisations increasingly prioritise efficiency, the demand for professionals adept in Microsoft Excel automation is rising, with over 80% of companies relying on Excel-based reporting.

You will dive deep into building advanced prepaid expenses models, automating amortisation processes, and developing formula-free dashboards for streamlined reporting. Learn to transform raw data into insightful visuals, simplify accounting operations, and drive impactful decision-making—all using Microsoft Excel.

By the end of this course, you’ll be equipped to handle financial data management with confidence, create dynamic dashboards, and simplify complex tasks using Microsoft Excel’s most powerful features. Don’t miss your chance to excel—enrol today and transform your data capabilities!

Your Benefits by Learning with ‘Cambridge Open Academy’:

  • Accreditation: Showcase your ability with our accredited Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query to potential employers.
  • Free Certificate: Get a Free Digital Certificate upon successful completion of the Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query.
  • Flexibility: Learn virtually from anywhere, anytime at your own pace and convenience.
  • Advance Your Career: Upskill to impress your employers and land your dream job or long-awaited promotion.
  • Immediately Applicable Coursework: Keep up with the latest skill trend by putting your skillsets to work.
  • Affordability: Save big with our online Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query as it not only suits your professional needs but also fits within your budget.
  • Tutor Support: Get tutor support on weekdays, 9-5 am, and our dedicated 24/7 customer support.
  • Lifetime Access: Achieve lifetime access to the top-notch expertly crafted course materials.

Our Specialised Delivery Method:

  • Interactive Learning Materials: The course modules were created using an EdTech industry-recognised tool to keep you engaged at all times. With this tool, you get interactive, engaging and top-notch course content. In our courses, you can take advantage of features like—
    • Drop Down Menu
    • Drag and Drop
    • Flash Card
    • Label Graphic
    • Timeline View
  • Responsiveness: In light of contemporary mobile and point-of-need learning trends, our courses are designed to be intrinsically dynamic and provide you the ultimate eLearning solution. These courses will adapt to any gadget without any extra software.
  • Learner-Friendly Navigation: Our courses are also quite simple to navigate for any learner. These courses are designed with simplicity and a modern flow that appeals to a wide range of learning audiences, regardless of their technical background and gadgets.
  • Elegant Outline: Our courses are visually appealing, where you can —
    • Track your progress on the left-side navigation toolbar.
    • Engage in drag-and-drop sorting activities and use the multiple response question to test your understanding of course topics.

With your newly acquired skills from this course can help you

  • Increase Your Hireability.
  • Make Yourself a Valuable Asset
  • Get Your Long-awaited Promotion.
  • Boost Your Pay-scale
  • Better Your Productivity

Certification:

Once you have successfully completed the Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query course, you will receive a PDF certificate completely free of cost as a proof of your accomplishment. The hardcopy certificate is also available for the cost of £9.99. UK students are required to pay a £10 as a delivery fee, while international students have to pay £19.99 for the shipment of a hardcopy certificate to their designated address.

Who is this course for?

This Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query course is developed for people who wish to excel in their professional and personal life. Learn from industry leaders and interact with a global network of experts by enrolling in this Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query course.

Requirements

Enrolling in our Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query course does not require any prior knowledge or experience. All that is required is an internet-connected gadget and a passion to learn.

Career Path

Participants in the Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query course are revolutionizing the professional landscape, propelling their careers forward, and enhancing their livelihoods across the globe. This sought-after course is empowering learners to forge new employment opportunities, advance within their respective industries, and experience substantial personal growth.

Course Outline and Introduction 00:04:00
Minimum Requirements for the Course 00:01:00
Prepayments Introduction 00:01:00
Month End Date Prepaid Expenses Amortization Calculation 00:00:00
Exact Prepaid Expenses Payment Date Calculation 00:00:00
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
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
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
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
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
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
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
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 00:02:00
Ultimate Prepaid Expenditure Model (Super Bonus) 00:02:00
Resources – Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query 00:00:00
Assignment – Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query 00:00:00

Certification:​

Once you have successfully completed this course, you will receive a PDF certificate as a proof of your accomplishment. The hardcopy certificate is also available for the cost of £9.99.
Note: Delivery of hardcopy certificate is free within the United Kingdom. However, to obtain a hardcopy certificate, International students will have to pay additional fees based on their location.

review-star

I have studied a few courses and have…

I have studied a few courses and have found the whole experience quick and efficient, and believe the courses will help contribute to my job in the Healthcare. Fabulous!

review-star

A clear and concise course

This self-paced course delivers clear, high-quality content on political science fundamentals, ideal for beginners. It builds confidence in key topics like political theory, institutions, the British constitution and Parliament, US government, elections, and doctrines (liberalism, anarchism, conservatism, nationalism). The flexible format fits busy schedules. Highly recommended for aspiring international relations professionals seeking internships or jobs—it broadens knowledge and strengthens foundational skills.