• Professional Development
  • Medicine & Nursing
  • Arts & Crafts
  • Health & Wellbeing
  • Personal Development

Course Images

Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query

Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query

By Janets

4.7(160)
  • 30 Day Money Back Guarantee
  • Completion Certificate
  • 24/7 Technical Support

Highlights

  • On-Demand course

  • 6 hours 8 minutes

  • All levels

Description

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 Content

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

Frequently Asked Questions

Are there any prerequisites for taking the course?

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.

Can I access the course at any time, or is there a set schedule?

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.

How long will I have access to the course?

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.

Is there a certificate of completion provided after completing the course?

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.

Can I switch courses or get a refund if I'm not satisfied with the course?

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.

How do I track my progress in the course?

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.

What if I have technical issues or difficulties with 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.

About The Provider

Janets
Janets
London
4.7(160)
Janets is an online platform where learners come to learn, and evolve. From the very beginning, the aim of this platform was to create an ever-growing community of avid learners instead of just delivering formulaic education. Emphasising on making the learners equipped for the fu...
Read more about Janets

Tags

Reviews