My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Plan v Actual Excel Dashboard

You are here: Home / Excel Dashboard / Plan v Actual Excel Dashboard
act v plan dashboard
by

Reporting on plan v actual variances can be a very dry topic and often the audience for these reports are not from a financial background.

To aid business management, we can create visually appealing interactive reports like the plan v actual Excel dashboard below.

plan v actual excel dashboard

It needn’t be a lot of work either. This dashboard can be constructed in just over 30 minutes, as demonstrated step by step in the video below. And once built, it can be updated with the click of one button!

Watch the Video

Subscribe YouTube


Trustpilot


Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Plan v Actual Excel Dashboard Techniques Used

I used several techniques to build this dashboard and you’ll find links to the individual tutorials on these topics below:

  1. Excel Tables
  2. PivotTables
  3. GETPIVOTDATA function
  4. Pivot Charts
  5. Slicers
  6. TEXT function
  7. Conditional Formatting with Formulas
  8. Custom Number Formats
  9. Excel Icons

Learn More

The topics above are a great start, but if you’d like to take your Excel skills further, please consider our courses below:

Excel Dashboards Course – A comprehensive course designed to give you the skills to build dashboards for any industry.

Excel PivotTable Quick Start – this course will help you overcome your fears and challenges with building PivotTables. It’ll have you up and running in under 2 hours.

Excel Advanced Formulas - I’ve designed this course to get you quickly up to speed with the functions that are going to give you the biggest efficiency gains. Many of the functions are considered advanced, but when you’ve finished the course, you’ll know like I do, that everything is easy once you know how.

Excel Expert Course – This course includes the PivotTable Quick Start and Advanced Formulas course as well as a load more topics covering everything from beginner topics (which you can skip if you already know them) to more advanced topics.

Power BI Course - Power BI offers functionality we can only dream of in Excel. Including secure sharing of reports on any device, clickable chart elements that cross filter and highlight other charts, and much more.

act v plan dashboard

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel Dashboard Posts

excel project management dashboard custom periods
Revolutionize Your Project Management with this Excel Dashboard. Use buttons to switch between viewing the progress by day, week and month.
excel infographic tools
Excel infographic tools are many and varied. In this tutorial I take you through step by step building an Excel infographic.
ai dashboard design
AI dashboard design can give you some great ideas for design and color combinations, but it’s not smart enough to create useful reports…yet!
dark theme dashboards
Dark Themed Excel Dashboards enable you to use formatting techniques that make your data pop and get your audience's attention.
excel dashboards custom backgrounds
Custom Excel Dashboard Backgrounds bring a level of polish and professionalism to reports and they’re super easy to create using PowerPoint
Embedding Excel Dashboards in a Web Page from files on SharePoint or OneDrive for Business or OneDrive Personal.
excel student performance dashboard
How to build an Excel student performance dashboard from scratch. Download the completed Excel file for reference.
5 Pro excel dashboard design tips
Excel Dashboard Design Tips you can use in both Excel and Power BI to ensure your reports and quick and easy to read.
excel interactive personal finance dashboard
Excel Interactive Personal Finance Dashboard to help you visualise and keep track of and manage where you’re spending your money.
Step by step how to build Excel Dashboards with non-financial data. Uses Power Query and Power Pivot to visualise NASA data.


Category:

Reader Interactions

Comments

  1. MT

    always neat lectures.. I’m excited to go back and practice the basic excel tricks to create dashboards and then power things up.

    Reply

  2. Harry

    Hello , my name Harry from Indonesian student, i have office 2016 in my PC desktop, can i use Excel 2016 to learning Plan v Actual Excel Dashboard , i want try to follow your video in Excel 2016.

    Thanks for advice

    Regards,
    Harry

    Reply

    • Mynda Treacy

      Hi Paul,

      If you have multiple source tables then you must use Power Pivot (the data model) and create relationships between the tables (usually via a dimension table). If you’d like to learn how to use Power Pivot, please consider my Power Pivot course.

      Mynda

      Reply

  4. Genevè

    Hi,
    Exactly what I need but am not sure how to go forward with this. What if the plan is based on COA mapping. So let’s say for the whole year, you have a budget of 100k on Cleaning and 50k on Cleaning Consumables. In January, you receive an invoice from supplier for Cleaning and for a period of 3 months (quarterly) and 3 invoices for Consumables (varies every month). What is the easiest way divide/split yearly budget each month on that sheet? I think I missed that information on your video. Would be nice how if you can show the input on the Data tab., Thanks.

    Reply

    • Mynda Treacy

      Hi Geneve,

      My data is already split by month, so there is no need to do this in the tutorial. If your budget is rolled up to the total value for the year then you can use Power Query to split it. I don’t have any blog posts I can point you to, but I cover this in my Power Query Course.

      Mynda

      Reply

    • Mynda Treacy

      I don’t have any supply chain dashboards, sorry. If you have some data I can use then I can try to make one as an example tutorial.

      Reply

  8. Matthew Carlson

    I have the data. Would you or your team build a dashboard or is that something that you guys do not do?

    Reply

    • Mynda Treacy

      Hi Matthew, I don’t do consulting, but I’ll email you with the contact details for a consultant who can help.

      Reply

  11. SIMON

    Hi,

    As a financial controller, I am looking for a file that can help me for my month end closing.
    Typically, once the accounting team have performed their entries, I am downloading the actuals periodically and year to date. From this, I am then inserting the data as Pivot Table to understand the trends BU per BU or Account by account, company by company etc…This is the step when I am reviewing my accruals, and see if there could be a missing accrual or an entry to reclass etc..

    Ideally, I have seen once a kind of Pivot (fully loaded of VBA) that enabled to insert value in an inserted column of a pivot that was then updating its own database.
    (E.G: You manually input 10€ in the pivot column and this creates the right reference line in the database).
    Do you have a similar way to proceed something like that (using a pivot as visualizing but active tool as well when you aiming to insert Forecast also for instance) ?

    Reply

    • Mynda Treacy

      Hi Simon,

      I don’t have any templates that would allow you to enter a value into a PivotTable and then update a database, sorry. This sounds like a bespoke application that someone has developed for this specific purpose.

      Mynda

      Reply

  13. John Redden

    If you only knew how long I have been trying to learn this. You make it to the point and easy to learn. Thanks so much and Merry Christmas

    Reply

  17. Adonna Garrett

    Great videos! So glad I found you all. Have you ever done a tutorial on doing a zero based budget template for a corporations? ZBB is making a come back and not a lot out there on using it in a larger scale, just for personal finances.

    Reply

    • Mynda Treacy

      Thanks, Adonna! I haven’t done anything on zero based budgeting. Back in my investment banking days that was a big part of my role, but I haven’t done any budgeting for a long time! I’d have thought it would be very difficult to build a one size fits all template for budgeting as it’s so unique to each business.

      Reply

  18. Kiran Mane

    Hi
    Just saw your video on YouTube..it was nice..!!awesome..!!

    Can it be published to web so that end user can access the dashboard easily ?

    Reply

  20. Monica

    Hi Mynda
    Another great post, so well presented. Thanks for your helpful tutorials!

    One request – would you be able to fix the link to the excel file so I can download it?

    Reply

    • Mynda Treacy

      Thanks, Monica! There’s nothing wrong with the link to the file. If you’re getting an error please get in touch via email (website at MyOnlineTrainingHub.com) so we can help you troubleshoot.

      Reply

    • Mynda Treacy

      Hi James, there’s no security on this file and you shouldn’t need to ‘enable editing’. I’m not sure why you’re seeing this message. Please get in touch via email (website at myonlinetraininghub.com) with screenshots of the process you followed to download the file and the messages you’re seeing so we can help troubleshoot.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *