Free Excel Dashboard Template

*** Update – This blog post is the original article releasing the first Free Excel Dashboard Template now called ‘Performance Dashboard’. Downloaded thousands of times, this Free Dashboard has become very popular since its release in May 2011 but we have now updated it to fix few bugs and we also provide 2 additional Free Dashboard Templates – click here to check them out ***

Dashboards are a much maligned tool in web analytics.  Many can be only politely described as reports, whereas data pukes is a more accurate term.  But I believe there is a gap between these reports filled with data and the minimalistic Actionable Dashboards created by Avinash, a gap that can be filled by useful performance dashboards.

Business owners need information to make decisions.  Some of them want to view data, not just be told what the data means and what their decision should be.  But web analytics tools contain too much data and end up being useless to the business owner as they can’t get the information they need.

A well designed dashboard meets this need (automated so minimal resources are required to update).  It contains only required information so that it is basically a simplified web analytics UI.  The business owner no longer needs to use the web analytics tool directly, instead the dashboard provides all the information they typically require.  Requests can be escalated to an expert user when necessary who can use the web analytics tool to answer advanced business questions.

These Excel Dashboard templates are designed to provide this level of data for business owners.  Up to 12 metrics can be included within each dashboard and these metrics can be analysed using the three basic techniques of data analysis:

  • Context – each metric is referenced against a comparison period
  • Trend – each metric is trended for up to 15 periods
  • Segmentation – up to five segments can be applied to the data

The Dashboard

My dashboards follow a basic structure and layout that I have evolved over the past five years.  They consist of sets of metrics displaying performance for a current period referenced against a comparison number.  Each metric can also be viewed visually trended over time.   The % change between current performance and the comparison number is highlighted as either good or bad (green/red) if outside of a certain range.

The dashboards print out nicely (very readable) on an A4 page.  To enable a potentially huge level of detail to be accessible through the dashboard, drop-down menus are used to select the segment of data or metric for display (macros must be enabled for these to work).  These rely on Excel formulas which were originally inspired by Stephane Hamel with an Excel dashboard template he created.

Screenshot of a Web Analytics Dashboard

Setting up the Dashboard

The dashboard comes with a Setup Dashboard worksheet where various elements need to be entered.  Once this is done, the button is pressed to set-up the dashboard automatically using a macro.  Following the completion of the set-up, unnecessary worksheets can be deleted.

Full instructions on the information required to set up the dashboard are included with each file.  The elements are:

  • Formatting – a set of eight elements that determines which dashboard is used, the appearance of this dashboard and how it is configured for use.
  • Metric Groups – the names for up to four groups of metrics.
  • Segments – if the dashboard that allows segmentation is chosen, you can enter the names of up to six segments here.
  • Metrics – the names of up to three metrics within each metric group along with the formatting options for that metric and whether good performance is a positive or negative change.

Setting up the Web Analytics Dashboard from a template

Updating the Dashboard

The dashboard relies on the user to find a method of populating the Data Summary worksheet with the metric values for that time period.  Assuming the data sources have an API, I recommend setting up a process whereby another worksheet has the data updated each period and each cell in the Data Summary worksheet simply references the appropriate cell in this data extract worksheet.

The same principle applies if there are multiple data sources, simply set up multiple data extract worksheets.  If need be, the data can be entered manually although I highly recommend being creative in thinking of a better solution than this.

Don’t forget to enter the date for this data period, whether the day/month or the end date if it is a weekly dashboard.  After that, simply press the Update (Dashboard) button and the dashboard is ready to go.

Screenshot of the Dashboard Update worksheet

Custom Dashboards

This template contains a fairly standard layout for a dashboard, covering all the basic information that key stakeholders might require.  However they might also want to see funnels, top 5/10 tables or indexed data within the dashboard, still in an easy to read and visual style.  This is all possible with customised dashboards.

All the basics of the approach used by L3 Analytics are contained within this dashboard template, the style, the formulas and macros.  So if you have someone in-house who is good with design and Excel, they should be able to set up a custom dashboard to your specifications.

If not, please give L3 Analytics a call.  We can develop dashboards customised to your exact reporting requirements and set up the data extract so the entire process is automated – dashboards are on your desk by 9:30am Mon morning.

Don’t waste time and money searching for the information you need, instead have everything you need at your fingertips when you need it.

Update – Downloaded thousands of times, those free dashboards have become very popular since their release in May 2011. We have now updated them to fix few bugs and we also provide 2 additional free templates. If you like the dashboards, leave a comment or tweet this post. If you think the dashboards need to be improved, let me know why. I would love to know if anyone is using them in their work. Thanks….

Download template

There are two dashboard templates, the first allows segmentation while the second is simpler without a segmentation option.  Additionally, a sample copy of the dashboard containing data is provided for demonstration purposes.  All are available in Excel 2007 and Excel 97-03 versions:

Download Free Dashboard Templates
If you need a more customised dashboard for your organisation’s needs, please contact us and we would be happy to provide you a quote:

Get a Quote for Customised Dashboards

Share and Enjoy:
  • Twitter
  • Digg
  • StumbleUpon
  • del.icio.us
  • LinkedIn
  • Facebook
  • Yahoo! Buzz
  • Google Bookmarks

Tags: , , ,

129 Comments

  1. S.Hamel May 16, 2011 at 12:45 am #

    Thanks for the mention Peter – I’m still amazed how the sample dashboard I created almost 3 years ago is still inspiring people to improve and build on top of it!

    Stéphane

    • Peter O'Neill May 16, 2011 at 9:15 am #

      @Stephane You’re welcome and I try to give credit where credit is due. I must have read your post not long after it was published and it did inspire me to evolve my dashboards. Prior to that, they contained a chart for each metric or were produced in Crystal Xcelsius (where you could use drop-downs). My dashboards have evolved a lot over the past couple of years and are now at a stage where I could create a free template to share with others – hopefully it will inspire them in turn.

    • Manish Pandey March 26, 2014 at 7:25 am #

      Hello,

      Thanks for sharing this. I tried using this dashboard & was preparing the weekly report. However i have not found the option to enter last week data through which i can compare. I can only see an option for updating data of this week. Please Help me.

      Thanks & Regards,
      Manish Pandey

      • Peter O'Neill July 27, 2014 at 12:14 pm #

        Hi – you need to update for all the historical weeks first to get this information into the dashboard. Then each week, simply add a new week of data.

  2. Charles Meaden May 16, 2011 at 1:23 pm #

    Looks great – will try it this afternoon

    • Peter O'Neill May 16, 2011 at 2:33 pm #

      @Charles No problems, let me know how you go with the set-up, if it all makes sense and works smoothly.

  3. Justin May 18, 2011 at 5:23 am #

    This is a great idea — nice and clean and easy to set up. Thank you for sharing.

    • Peter O'Neill May 24, 2011 at 9:59 pm #

      @ Justin You’re welcome, good to hear you have found it useful

  4. Peter McFadden June 13, 2011 at 1:29 pm #

    Also take a look at http://www.ExcelDashboardWidgets.com for examples of dashboards using Microsoft Excel.

    • Peter O'Neill June 14, 2011 at 3:56 pm #

      @Peter McFadden I am personally not a big fan of dashboard widgets like speedometers, I don’t think they make it easier to understand or interpret the data. But I know many people do like using them, particularly for executive dashboards.

    • Charles Mang February 14, 2014 at 5:15 pm #

      Your templates are amazing, Can you e-mail me the VB password for your dashboard with segmentation. I runing into issue and they are asking me for password. Thank you

  5. Tim Richmond August 8, 2011 at 5:12 pm #

    Can anyone help me? I am building my first dashboard and I like this template a lot. However the company logo on it is a problem and I need 7 Metric Groups.

    If you are Santa can I have a blank chart with room for Seven Metric Groups to fill in my data?

    Tim
    Department of Commerce
    Cost Reduction Analyst

    • Peter O'Neill August 9, 2011 at 1:03 pm #

      @Tim One of the aims within this dashboard is to not overload it with data. Your request for 7 metric groups suggests that at least a few of the numbers won’t add much value to the understanding of performance within the organisation. Having said that, will see what I can do.

      Cheers

      Peter

  6. Steve=True August 26, 2011 at 3:00 am #

    Nice clean and simple dashboard.

    Also has some dynamic components.

    I like it.

    Steve=True

  7. Kesh August 31, 2011 at 3:54 pm #

    This is awesome. Well done. The only problem I have is that I have to report more than three metrics, I tried to include additional lines but it seems it is not picking up in VBA. please can you assist.

    Much appreciate it…

    • Peter O'Neill September 9, 2011 at 9:35 pm #

      Hi Kesh,

      I sent you an email, this is possible although I wanted to limit the amount of data on the dashboards, please reply and we can see what we can sort out.

      Peter

  8. Paul Yoong October 19, 2011 at 9:37 am #

    Hi There

    this is excellent :)

    I was wondering if someone could perhaps help me, I need to have 3 more segments added to the file but i am having trouble doing it via VB. Could a kind soul please help me out?

    Thanks

    • Peter O'Neill October 19, 2011 at 9:56 am #

      Hi Paul,

      I glad you like. I should be able to help with that – is that all you need, just the ability to add 9 segments and not 6?

      Cheers

      Peter

      • Paul Yoong October 19, 2011 at 10:04 am #

        Hi Peter

        This dashboard will be an incredibly helpful tool :)

        I made a mistake actually, I am sorry, the segments are fine the way they are, it would be very very helpful if I could get some help with increasing the no Metric Groups to 9 instead of 5. I tried doing it myself but I am afraid I could not figure it out.

        We monitor 9 different values that are important to us, I hope this isn’t too much trouble, if it is I understand.

        in any case, thanks :)

        Cheers
        paul

        • Peter O'Neill October 19, 2011 at 10:10 pm #

          Hi Paul,

          I admit, you have left me more confused now. The dashboard allows for up to 12 metrics to be entered which is I think is what you are after. Let me know if you need any help setting it up.

          Cheers

          Peter

  9. Florian October 24, 2011 at 11:09 am #

    Hi Peter,

    Your Excel dashboard templates are just awesome. I showed my traffic manager the sample version. He loves it and would like me to update our old dashboards.

    I’ve been working on it since a while but there are 2 points I still don’t get:

    1/ Where should we put historical data? On the sample dashboard, there is a graph with data for the last 6 weeks. On the Tab ‘Data Summary’, there is only the data for the past week. Are there any best practices to implement historical data on this dashboard? For example, should I use an external worksheet?

    2/ The non-segmented dashboard works perfectly but I’ve got troubles with the segmented one. When I click on “Set-up Dashboard”, there is an error message. According to the debug mode, it could come from the line ‘Sheets(“Sheet” & segment_num).Name = segment_name’. I tried again without filling segmented names and no more errors. Any Idea where it could come from?

    Anyway, I’d like thank you again for those templates. I’ve understood how important it is to learn VBA and I’ve just bought my first book :) !

    Florian.

    • Peter O'Neill October 24, 2011 at 10:40 pm #

      Hi Florian,

      I am glad you are finding the dashboards useful. Historical data can be copied into the hidden worksheets in the dashboard – check them out and once you have added a single period of data, you can see where to add the historical data (it appears you might need to ignore the heading in column A of these data sheets). I am not sure what is causing that bug, are you using a character that Excel doesn’t allow? Can you email a screenshot through to me and I should be able to sort out.

      Cheers

      Peter

  10. Florian October 25, 2011 at 9:21 am #

    Hello Peter,

    Thanks so much for your reply!

    About historical data, I hadn’t noticed there were hidden tabs. Thanks for your advices, everything is clear now!

    For the bug, I guess you might be right, it could come from a special character that Excel doesn’t allow (moreover, I use a French version of Excel 2007). I’ve just sent you screenshots by email.

    Have a nice day!

    Florian

    • Guillaume Bérubé December 22, 2011 at 8:43 pm #

      Hi Peter!

      Thanks a lot for this fantastic dashboard.

      it looks like I’m having the same error message as Florian had and I’m also using a French version of Excel 2007, any luck with a solution?

      Thanks!

      Guillaume

      • Peter O'Neill December 22, 2011 at 11:07 pm #

        Hi Guillaume,

        Glad you like the dashboard. The issue that Florian had was related to a special character that Excel couldn’t deal with. Can you check through to see if there are any non english characters that you could replace in some way? If not, send through the dashboard to me and I will set it up for you.

        Cheers

        Peter

  11. Charonda October 27, 2011 at 8:10 pm #

    Hello, I really love this lay out. The only thing is, this is my first time setting up a dashboard. I was reading over the directions, and where it says “create a method for extracting data from one or more tools” and “create a method for inserting this data into the appropriate cells in the data summary worksheet” has me a bit puzzled. Do I need an additional program to perform this action? Also, how do I add more segments? I need at least 12.

    • Peter O'Neill October 27, 2011 at 9:01 pm #

      @Charonda am happy to hear you like the layout.

      As this is a templated layout, it could be used with any data source. As such, I have not set it up to work with any particular web analytics tool or any other data source. It depends on where your data is coming from as to what work is required to populate the Data Summary each week (or other period). If you use Google Analytics, then I would recommend using a tool like Next Analytics (or Excellent Analytics, Tatvic, etc) to create an automated data extract. This would mean you only have to press two buttons to update the dashboard, one to extract the latest data and one to populate the dashboard with it. An alternative is to enter data into the Data Summary sheet manually.

      I purposely kept the number of metrics and segments limited to try and make people think about what they really need. Do you want 12 segments just in case or are all 12 required (12 countries or 12 websites)?

      I should be able to make a quick adjustment to allow for 12 segments for you. If you would like a customised version of the dashboard or a dashboard set up with the data extract automated, we could discuss that although then there would be a cost involved.

      Cheers

      Peter

  12. Excel Add-in User November 3, 2011 at 12:56 am #

    I like the clean lines on these dashes. Clarity always aids understanding

    I’ve incoporated a few of these ideas, and cobbled them together with the work on Chandoo’s site for my regular 9-5 job. Feedback has ranged from “very nice” to “wow!”

    Good job.

  13. Krish November 8, 2011 at 11:10 am #

    Hi,
    May i know the directions to install or steps to run this.I was try to locate readme/help file, is there one.

    • Peter O'Neill November 8, 2011 at 11:18 am #

      @Krish Instructions on how to set up the dashboard are included within the dashboard. Just download a copy and have a look. Let me know if you have any questions.

      • Dana November 22, 2011 at 3:04 pm #

        Hi There – I cannot locate the instructions in the dashboard. Any help would be appreciated.

        Thanks,
        Dana

        • Dana November 22, 2011 at 3:06 pm #

          I was able to get the instructions by downloading the 97-03 file…thanks.

          • Peter O'Neill November 22, 2011 at 10:13 pm #

            @Dana glad to hear you got it sorted. All of the actual dashboards contain the instructions, just not the sample dashboards (which I assume is what you downloaded first). Am planning to release an update to dashboards in the new year and will make the instructions easier to access at the same time. Please let me know how you get on with using the dashboard though.

  14. Krish November 8, 2011 at 11:28 am #

    After downloading I could see only XML file, can you please let me know how do i get it.

    • Peter O'Neill November 8, 2011 at 10:52 pm #

      @Krish I am not sure how you are getting an XML file, there are only xls and xlsm Excel files on that page. Can you let me know which button you are clicking on to download the file?

      • Kal December 22, 2011 at 10:02 pm #

        Hi Peter

        I am getting the same issue as Krish when i download the sample dashboard.

        • Peter O'Neill December 22, 2011 at 11:10 pm #

          Hi Kal,

          I never heard back from Krish, what are you clicking on to download the file, I am not sure how an XML file can be accessed.

          Cheers

          Peter

  15. amerish December 11, 2011 at 8:30 pm #

    The Excel Professor has a few nice dashboard examples including this on on using check boxes to make a dashboard dynamic.

    http://excelprofessor.blogspot.com/2011/11/dashboard-dynamic-data-designing-simple.html

  16. Andrew Gilleran December 22, 2011 at 1:47 am #

    An excellent piece of work, many thanks for making this available.

  17. Stuart Meagher December 29, 2011 at 3:46 pm #

    Hi

    Great template. But I am having problems with the Graphs.

    How do I represent previous months data on the graphs???

    And also do I have to manually put the “Last Months” figures into the dashboard directly?

    thanks

    Stuart

    • Peter O'Neill December 30, 2011 at 2:23 pm #

      Hi Stuart,

      Previous month’s data will accumulate as data is updated into the dashboard. You can update multiple historical months using the Data Summary worksheet when you first set things up, just make sure to start with the oldest month first. Alternatively, historical data can be manually entered into worksheets – just unhide these sheets and insert the data as required.

      Cheers

      Peter

  18. Cinque Jokya January 21, 2012 at 7:41 pm #

    Thanks for the free dashboards. I’m new to dashboards, but finding they may be useful in a segment of my job function.

    • Peter O'Neill January 21, 2012 at 9:13 pm #

      Thanks Clinque, glad you are finding them useful. Check out in a month or two when I release some new templates.

  19. Yanina January 23, 2012 at 3:39 pm #

    I love your dashboard! I’d like to use it, however I am getting the same error message some people are getting (same line, same error). I am using a German version of Excel and I just can’t figure out how to make the segmented dashboard work..
    I can use the non segmented one just fine, but I fell in love with the segmented one and would just love to use it.. is there something I can do to make it work?

    • Peter O'Neill January 23, 2012 at 6:34 pm #

      Thank you. I will identify that issue one day but I think it is due to non english characters. Can you please remove anything like that from segment names and see how you go then (and let everyone else know if it works). Otherwise, please email me a copy with the configuration sheet filled in + a screenshot of how the configuration sheet looks for you and I will update it for you.

      Cheers

      Peter

  20. Blake Herlick January 25, 2012 at 3:23 pm #

    WOW! I love the potential here.
    I am using American English Excel 2003 SP3 and i also have an error reported using the segmented template. The error is Run time 438. Object does not support this property or method.
    The line of code: Sheets(“Dashboard”).Range(“A1:K4,B7:E7, G7:J7, B13:E13, G13:J13″).Font.ThemeColor = Sheets(“Setup Dashboard”).Range(“K13″)
    I thought at first I needed to add references to my project. I went thru and added the latest DAO and my error changed to a subscript out of range.
    As far as non english characters… I used Letters A-Z only and I used sample names for example Metric Group 1 is called ‘A’ MG2=’B’ and so on.
    I hope this information helps. I am going to try the non segmentation template now.

    Cheers!

  21. Blake Herlick January 25, 2012 at 3:34 pm #

    Got it:
    ThemeColor is not supported. Replaced with Color:
    Sheets(“Dashboard”).Range(“A1:K4,B7:E7, G7:J7, B13:E13, G13:J13″).Font.Color = Sheets(“Setup Dashboard”).Range(“K13″).Font.Color
    I have not seen the results though as this passed me thru to another Object/Method not suported error:
    Sheets(“Dashboard”).Cells(metric_num + 5, 13) = Sheets(“Setup Dashboard”).Cells(WorksheetFunction.Quotient(metric_num – 1, 3) * 3 + 10, 5) & ” – ” & Sheets(“Setup Dashboard”).Cells(metric_num + 9, 6)
    Because this is a worksheet function- I am unfamiliar with it and will await your feedback.

    • Peter O'Neill January 25, 2012 at 4:43 pm #

      Hi Blake,

      Obviously I don’t have a QA background. It could be harder to QA Excel dashboards than websites with apparently so many versions of Excel floating around. It looks like themecolor might be an Excel ’07 function, hence why not working for you. No on else has raised the issue so there might be a default setting which usually applies.

      Quotient appears to be a worksheet function from the Analysis Toolpak Addin and so requires that to work. And/or it is not supported in some versions of Excel ’03 – I am finding slightly different information online for that one. Either way I will try and create another formula which has the same result – how urgently do you need this dashboard?

      Cheers

      Peter

  22. Mike Anderson January 28, 2012 at 4:10 pm #

    Fantastic template. Thank you for taking time to create and share such a versatile, user friendly, FREE product that simplifies metric reporting.

    Mike

    • Peter O'Neill January 28, 2012 at 5:10 pm #

      Thanks Mike, appreciate the feedback

  23. Vito January 30, 2012 at 5:56 pm #

    Peter, Do you have any good examples of a dashboard that produces primarily text updates? I need to be able to have my team provide status updates via a dynamic dashboard. The data is event based and does not require charts and graphs. All I really need is a way for them to make text updates with date/time stamps. Any examples that I can build on? I have good excel skills, but not strong as a developer with VB.

    Thanks!

    • Peter O'Neill January 30, 2012 at 10:22 pm #

      Hi Vito, can you explain a bit more what you are after, I don’t understand what you mean by a dashboard that produces text updates. Is there an example you can give, even if of a bad version?

      Thanks

      Peter

      • Vito February 1, 2012 at 3:18 pm #

        Maybe it will help to give you more information about the scenario. I manage a team that coordinates the triage process that brings together multiple teams of IT professionals needed to address a service outage, for example a financial reporting application that is business critical. I want to provide a dashboard that my team can update with the most current information with regard to which technical teams are involved in the triage process, what they are doing and what the estimated time of completion is etc. This would give our customers a central place to go for the information they need, without having to ask for individual updates from the triage team, which tends to interrupt the triage continuity.

      • Vito February 6, 2012 at 4:38 pm #

        Hi Peter, Any suggestions for me based on my last post, or do you need more information?

        Thank you!

        • Peter O'Neill February 6, 2012 at 5:19 pm #

          Sorry Vito, thought I had replied but clearly imagined it. It sounds like you are more after an interface than a dashboard. Something that your team can provide inputs to and that customers can access to read updates. It’s not something I can personally help with sorry, I can visualise some of the different elements involved but you would need developers to build it.

          The Google Apps Status dashboard is one possible visualisation if you could define every issue into certain areas (to replace the apps). Then any issues would be highlighted as a ball on the board, customers could click on it to read the details with the colour and size of a ball reflecting the scale of the problem and how long till it was expected to be resolved. Does that help at all?

  24. Robby February 21, 2012 at 3:30 pm #

    Hi Peter- This is an excellent dashboard. I would like to add additional segments. (I am using the segments for field service regions, so I would need several) Is there a way to add segments?

  25. Ashish Kapoor March 6, 2012 at 11:38 pm #

    Hi Peter,

    The dashboard looks good and will help to provide insights to managers for few of our websites. However, we are using Office 2010 and am getting the following error:

    Run-time error ’9′. Subscript out of range

    Just wondering whether this is becuase of the office version or a bug? I can also send screen dumps if required.

    Regards,.
    Ashish

  26. Euria Briggs March 9, 2012 at 3:37 pm #

    These examples rock! Very helpful and I am most appreciative that you provided them for free. Thank you!

  27. Tracy March 28, 2012 at 12:09 am #

    I downloaded the template a few months ago and need to add one more item to track–it’s not easy to add, so I was going to rebuild. I tried downloading the same xls and upon clicking setup dashboard, I too (like Ashish), get a runtime error. Is there an updated version you can post or share out? This is the simplest dashboard I’ve found and historically been a snap to update with a bit of data entry.

    • Peter O'Neill March 28, 2012 at 9:53 pm #

      Hi Tracy,

      Great to hear you like the dashboard, not so great it is not working for you now. There are some bugs that I have fixed and I plan to release updated versions of the dashboard + two more templates this weekend.

      With Ashish though, the issue was clicking on “Setup Dashboard” multiple times, it will only work the first time. The solution is start with a clean version of the file. Please let me know if you are getting the same runtime error as Ashish or something different.

      Cheers

      Peter

      • Aaron April 9, 2012 at 4:51 pm #

        Hi Peter,
        I am getting the “Run-time error ’9′. Subscript out of range” error too. Has the fix been released yet? Otherwise, it is great! Thanks

        • Peter O'Neill April 9, 2012 at 5:42 pm #

          Hi Aaron,

          One potential cause of this is trying to set the dashboard up multiple times (by clicking on the Setup button). If that is the case, you need to work off a clean version of the template. But look back in a few hours for the latest dashboards.

          Cheers

          Peter

          • Vaughn H. June 5, 2012 at 7:22 pm #

            I am still getting the “Run-time error ’9′ . Has an updated template been published?

          • Peter O'Neill June 7, 2012 at 9:27 pm #

            This is generally due to the Set-up Dashboard button being pressed more than once. Try starting again with a fresh copy of the template and see if it works then. If not, let me know and I will investigate further.

  28. Tom April 2, 2012 at 3:42 pm #

    Hi Peter,

    The dashboard looks fantastic.

    My requirements of a dashboard are to have 8 metric groups with a total of 37 metrics.
    Metric Group 1 – 5 Metrics
    Metric Group 2 – 5 Metrics
    Metric Group 3 – 3 Metrics
    Metric Group 4 – 5 Metrics
    Metric Group 5 – 7 Metrics
    Metric Group 6 – 5 Metrics
    Metric Group 7 – 4 Metrics
    Metric Group 8 – 3 Metrics

    Would it be possible to adjust the template to suit these requirements?

    • Peter O'Neill April 9, 2012 at 12:33 pm #

      Hi Tom, my approach with the dashboards is to include less metrics, not more. Given your request there, can you not separate into two (or even three) dashboards? Setting up either version is not a quick change unfortunately but it is possible – there would likely be a charge involved to customise the dashboard to this degree. Let me know if you would like to discuss this.

      Regards

      Peter

  29. Ndco April 16, 2012 at 12:05 pm #

    Hi, Thanks Peter for that file !

    However i have a question : I can’t make the graph work. Even i change the metrics or update the dashboard data, the graph does’nt work, nor on the sample Dashboard that you offer.

    Any clue ?

    • Peter O'Neill April 20, 2012 at 8:58 am #

      The likely answer is you need to have macros enabled – try that and see if it works.

  30. Debra June 12, 2012 at 4:08 pm #

    Peter,
    Thank you so much for a wealth of information on dashboards that is easy to understand. First i’d like to say I am extremely new to dashboards. I haphazardly came across them online when I was trying to find out was to graphically display our KPI data over a 3-year period. Viola — dashboards. For my immediate project, I had to slice the data in several charts to reveal the comparison. That project was time sensitive, but now that it is over, I would looking into creating a dashboard going forward. I have downloaded all the templates and read the entire blog, before i take my stab at this i had a few questions:
    *I currently use Excel 2010, ar the templates compatible?
    *Could the template be expanded to capture for 2010, 2011, and 2012.
    *Could the Metrics Group be expanded by (1) or (2) group. Currently, my data Metrics are Overtime, Discipline,. Attirition, Absentee, Exit Interview.
    *Segments – please confirm if my understanding is correct. I chose segmented format because my organization unit represents 19 different locations, would i need to set up a segment for each location?
    Lastly, i just met with our HRIS Manager and I advised of my initiative and they will be able to provide an automatic report based on my needs. He also advised that its possible to feed into the dashboard. Will this need customization?

    I’m sorry i have so many questions. What ever you have time to answer, i would appreciate.

    Thanks a million,
    Debra

    • Peter O'Neill June 26, 2012 at 3:45 pm #

      Hi Debra,

      Glad to hear the excel dashboards templates came in handy for you and am fine to answer these (and any other) questions. I am obviously a big fan of dashboards, lots of disagreements in the analytics world but if designed properly, they provide all the required data at the user’s fingertips. In response to these questions:

      * The templates are compatible with Excel 2010 to the best of my knowledge

      * The dashboard can include whatever data you wish to include. Do you mean displaying multiple years within the charts? I am guessing your data is at monthly level? With my most recent update, the dashboard retains all of the historical data (within hidden worksheets, just unhide if you want to view)

      * The reason for the four metrics groups is to reduce the data within the dashboard. It is possible to increase but the question becomes is that just to report on all possible metrics or are all the metrics included useful.

      * Do you need the dashboard to report by location? If not, then you don’t need segments. If so, there are dashboards that allow up to 20 segments available for download on my primary dashboard page on the website. Always remember to start simple, people have too much data being thrown at them as it is, the last thing you want to do is add more. Dashboards should something used & reviewed for information on performance, not another report to add to the pile.

      * You should be able to add the data import from your HRIS manager yourself. The simple steps are:
      * Create a new worksheet within the dashboard
      * Each week/month, paste the new data into this worksheet
      * Link each cell within the Data Summary worksheet to this new worksheet
      * Simply using = formulas

      Hopefully that all helps. Of course, it is possible for me to create one or more dashboards for you, customised to your exact requirements and automated as much as possible – but there will be costs involved. Let me know if you want to discuss this option further. And happy to answer any more questions you have.

      Regards

      Peter

  31. Shane June 20, 2012 at 10:00 am #

    Hi Peter,

    Thanks so much providing these templates. I have one question – is there a way to change “select period” to show quarterly rather than monthly results?

    Thank you!

    • Peter O'Neill June 26, 2012 at 3:42 pm #

      Hi Shane,

      That was a future enhancement, to include the quarterly period. I have actually modified a dashboard once for this – which version are you after? If it is the one I already have, will send it through.

      Cheers

      Peter

  32. Lloyd June 25, 2012 at 10:15 pm #

    Tom,
    I’m trying to view the VBA but It’s password protected. Do you plan to share the code?

    • Peter O'Neill June 26, 2012 at 3:41 pm #

      Sorry Lloyd, I am not currently planning to release the VBA code – email me if there is a reason you need to access it and I might share it with you directly then

      Cheers

      Peter

  33. Helen Rogers July 12, 2012 at 1:46 pm #

    Hi Peter,
    I love the dashboards & am a complete novice to this. I have followed your instructions & have avoided pressing teh set up dashboard button more than once. However I keep getting the following-
    run time error ’9 subscript out of range.
    Any ideas? I have excel 2003 & have downloaded the non segmented version.

    Many thanks
    Helen

    ps-code below:

    Sub update_dashboard()

    current_position = “A3:M” & (Sheets(“Data Summary”).Range(“C23″) + 1)
    new_position = “A2:M” & Sheets(“Data Summary”).Range(“C23″)
    num_periods = Sheets(“Data Summary”).Range(“C23″)
    segment_number = 1

    Do While Sheets(“Data Summary”).Cells(7, segment_number + 3) “”
    segment_name = Sheets(“Data Summary”).Cells(7, segment_number + 3)
    Sheets(segment_name).Range(current_position).Copy
    Sheets(segment_name).Paste Destination:=Sheets(segment_name).Range(new_position)
    Sheets(segment_name).Cells(num_periods + 1, 1) = Sheets(“Data Summary”).Range(“D4″)
    For metric_number = 1 To 12
    Sheets(segment_name).Cells(num_periods + 1, metric_number + 1) = Sheets(“Data Summary”).Cells(metric_number + 7, segment_number + 3)
    Next
    segment_number = segment_number + 1
    Loop

    Sheets(“Dashboard”).Range(“F3″) = Sheets(“Data Summary”).Range(“D4″)

    End Sub

    • Peter O'Neill July 12, 2012 at 2:14 pm #

      Hi Helen,

      I think you have an old version of the template (the current version has the code protected) – if so, can you try downloading a new copy from my website. If still issues, I can look into what the problem is.

      Cheers

      Peter

      • Kenneth P August 30, 2012 at 8:35 am #

        Hi Peter

        I’m having the same issue as Helen has, but I am using the updated version. As the code is protected, I currently cannot debug it :)

        Please have a look and let us know, I’m willing to test it out when updated.

        Best regards
        Kenneth

        • Kenneth P August 30, 2012 at 8:45 am #

          Have a look at fields L16:O16 on sheet ‘Setup Dashboard’ if you please :)

      • Creedy October 17, 2012 at 7:00 pm #

        Hi Peter, I’m using the Segmentation version the latest one, and everytime I click on set-up Dashboard, it keeps opening up to the VBA editor which then of course is protected. Is there something I’m doing wrong?
        Cheers
        Creedy

        • Creedy October 17, 2012 at 7:05 pm #

          forget I just wrote that, got it working, absolutely awesome!!!

  34. Madanlal August 16, 2012 at 8:48 am #

    Wonderful dashboard for presenting reports. I’m going to use it right away.

  35. Shahab August 19, 2012 at 4:13 pm #

    Hello,
    Thank you , it was very nice

  36. Tav September 17, 2012 at 10:16 am #

    Hi,

    I keep getting run-time error ’1004′ – Application-defined or object-defined error

    Please help! Great dashboard by the way!

    Thank you!

  37. Andrei November 12, 2012 at 2:54 pm #

    Hello,

    I downloaded the 20 segments template, all good with the setup and date entry.

    The only issue i have is with the charts vs segments display. Only for the first segment the information is inserted in the charts, for the others i get a blank chart. Do you have any ideea why this is happening ?

    Thank you

    • Peter O'Neill November 15, 2012 at 10:54 am #

      Hi Andrei, apologies for not replying sooner. I think I may know what this bug is, something I have fixed in the other dashboards but not the 20 segment one. Let me have a quick go at it & send you a copy to see if it works.

  38. Patrick November 16, 2012 at 10:57 am #

    Hi

    Look likes a very handy tool for our reports. Will this also work within Google docs?

    • Peter O'Neill November 18, 2012 at 10:55 pm #

      Hi Patrick – setting up and updating the dashboards are based on VBA macros which don’t work in Google docs, so unfortunately not. You would need an alternative method of sharing the dashboards between team members. Peter

      • Patrick November 21, 2012 at 11:37 am #

        Hi Peter,

        Thank you very much for your reply. I thought is would be handy to share the doc. with our clients. Then we only need to refresh for the monthly teports :-)

        Kind regards,

        Patrick

        • Peter O'Neill November 21, 2012 at 4:14 pm #

          agree, would make it a lot easier if it worked in Google docs. Maybe try dropbox or a similar file sharing tool instead?

  39. Andy November 18, 2012 at 5:27 pm #

    Hi,

    I’m getting run-time error ’1004′ – Application-defined or object-defined error

    Please help! Great dashboard :-)

    Thank you,
    Andy

    • Peter O'Neill November 18, 2012 at 11:49 pm #

      Hi Andy, I am hoping this is a quick fix. Can you let me know which dashboard you have and what is triggering the error. A common cause is clicking on the Set-up Dashboard button a second time – you need to use a fresh copy of the dashboard. You can always send me a copy and I will have a look at it for you properly. Cheers Peter

      • Andy November 19, 2012 at 7:49 pm #

        Hi Peter,

        thanks for the reply :) I was trying to use the dashboard with segmentation for Excel 07, but when I press Update dashboard, I’m getting this error.

        Thanks, Andy

        • Peter O'Neill November 20, 2012 at 3:04 pm #

          Ok, not sure what is causing that. Could you send a copy to peteroneill@l3analytics.com and I should be able to fix it pretty quickly (think I recognise most of the bugs now).

          • Jon February 2, 2013 at 4:19 am #

            Hi Peter – I have the same problem that Andy had back in Nov-12. Did you happen to find a fix? THanks much! I love the templates!

          • Peter O'Neill February 6, 2013 at 5:12 pm #

            Hi Jon – I think I have a fix for most bugs now – can you email me a copy of your dashboard & can go from there.

  40. Ann November 22, 2012 at 6:42 pm #

    Hi Peter,

    This may sound silly but when I set up the Dashboard it requires a VBA Password – I don’t see it mentioned?

    I am new to Dashboards and require a little bit of information.

    Thanks for your help in advance!

    • Peter O'Neill November 22, 2012 at 7:12 pm #

      Hi Ann, you don’t need a VBA password to run the dashboard but the message may appear if you trigger a bug. Are you getting an error message? Which dashboard file are you using and what action are you taking? Peter

  41. Melia December 7, 2012 at 9:54 pm #

    Thank you! This is such a wonderful example and extremely useful! Many Thanks–

  42. Ayat December 18, 2012 at 7:29 pm #

    Hi
    it’s really Great work and will help me a lot in my Final Year Graduation …
    but i went to ask you some question:

    can u able to do these job in VB6 ?
    and what is differences between Decision Support System and Dashboard ?
    i went some help if u have any link to do that in a VB6 because i went to take it as an example to me to understand better PLZ :)

    waiting for your answer …

    Thanx

  43. Demetri January 8, 2013 at 9:54 pm #

    Hi Peter! Great templates…thx a lot…

    my one issue is attempting to update historical data on the segmented dashboard. I added a few weeks of data to test the dashboard, but now I cannot seem to over right/ update the data as I am receiving a message “that the date does not follow in sequence from the previous date”

    Thanks!!

    • Peter O'Neill January 9, 2013 at 11:25 am #

      You’re welcome. The existing button is just for updating new data, it can’t be used to overwrite existing data. You have the option of unhiding the worksheets that contain the data, updating them manually. I do have a solution for an “Overwrite Data” button for the Performance Dashboard – can you let me know which dashboard file you are using and I might be able to provide you with a new template.

  44. Rub January 28, 2013 at 12:27 pm #

    Peter, it’s good to know the way in which you share your experiences, Excel is now a powerful tool that even well exceeds many expectations of business intelligence dashboards. I would like to share a management board, as I can do?.
    I have a space in the cloud on Dropbox where students enter my seminars. The seminar to which I refer is called Microsoft Excel iteligancia applied to business with BSC approach.

    • Peter O'Neill February 6, 2013 at 5:14 pm #

      Hi Rub – sorry, not sure exactly what you are after. Do you want to share one of my dashboards with your students or to share your dashboard with me?

      • Rub February 6, 2013 at 7:51 pm #

        Hi, Share my DashBoard,

  45. Kevin M March 15, 2013 at 10:41 pm #

    Run-time Error ’1004′: Application-defined or object-defined error

    received when trying to update multi-segment dashboard with additional period data

    • Peter O'Neill March 31, 2013 at 12:28 pm #

      I believe i have a fix for this bug, will email you an improved version of the dashboard template.

  46. Boekhouder March 21, 2013 at 9:29 pm #

    Hi there. I also like it!! But do you have it also in Excel 2010 or 2013 by now. I can’t get it work. This is exactly what we need.

    Hope to hear from you.

    • Peter O'Neill March 31, 2013 at 12:28 pm #

      It should work fine in later versions of Excel as far as I am aware – what bug are you encountering?

      • Boekhouder March 31, 2013 at 2:29 pm #

        hi there. thanx for your answer!
        Everytime I setup the dashboard and click on the big white button I get the pop up:

        Run-time error: ’9′
        Subscript out of range

        I can choose help or end. When I click on end nothing happens. When I go to dashboard Microsof Office pop up comes out:

        “A formule in this worsheet contains one or more invalir references.
        Verify that your formula contains a valid path … ”

        So basicly its not working (unforfunately!)

        Any ideas to solve it?

        • Jeanna Bash April 12, 2013 at 10:12 pm #

          Hi all,

          I am using Office 2010 for the PC and I am receiving the same runtime error that is listed above. Is the template compatible with Office 2010?

          Thanks!

          Jean

          • Peter O'Neill April 13, 2013 at 5:52 pm #

            Hi Jeanna – the dashboard is fine with Excel ’10. Exactly what error are you getting? Peter

        • Alex August 6, 2013 at 10:21 pm #

          Hi – I’m having the same issue as ‘Boekhouder’ from his March 31, 2013 post. I keep getting the same error “Run-time error: ’9′ – Subscript out of range”.

          I’m using the latest Excel 2013 edition.

          Any idea on how to fix this?

          Thanks!

  47. reuben360 April 2, 2013 at 9:58 pm #

    Great job with the dashboard template Peter!
    I am having a little weird problem. I set up all the required field and hit the Update dashboard but nothing happens. Its like the Update dashboard button doesn’t work. Am I doing anything wrong. Again great job

    -reuben

  48. Mike April 6, 2013 at 5:09 pm #

    I like the dashboard, but it doesn’t seem to work in later versions of Excel(2011) on my Mac. Any advice? Is there an updated version?

    • Peter O'Neill April 9, 2013 at 7:56 pm #

      Hi Mike – it should work – what issue are you having? Peter

      • Mike April 9, 2013 at 8:36 pm #

        I can’t enter anything in the fields. Let me try again.

  49. Angel May 19, 2013 at 9:46 pm #

    Good afternoon, thank you for these great dashboard templates. When I enter the data in Excel 2010 however, it won’t allow me to update. I went to peek at the VBA Code and it requires a password. Are the templates now password protected?

    Thanks!

    • Peter O'Neill May 20, 2013 at 10:46 pm #

      Hi Angel – the dashboards should work fine in Excel 2010. Can you send me an email with your dashboard and I can fix whatever the issue is. Cheers Peter

      • Brandon September 30, 2013 at 1:27 pm #

        I have exactly the same issue.

  50. Jason June 26, 2013 at 12:55 pm #

    Hi Peter,

    My requirements of a dashboard are to have 6 metric groups with a total of 10 metrics for each group

    Metric Group 1 – 5 Metrics
    Metric Group 2 – 5 Metrics
    Metric Group 3 – 3 Metrics
    Metric Group 4 – 5 Metrics
    Metric Group 5 – 7 Metrics
    Metric Group 6 – 5 Metrics

    I tried adding these, however, once you update the information in setup dashboard tab… it doesn’t get updated in other sheets.

    I tried in VBA but not able to edit as it is password protected. Can you help in changing these

    Jason

  51. Brandon September 30, 2013 at 1:26 pm #

    I have downloaded the free template and have populated the cells as required but it would appear as if the macro is not working.
    What am I doing wrong ?

    Regards

    Brandon

    • Peter O'Neill October 1, 2013 at 11:16 am #

      Hi – what version of Excel are you using? Do you have macros enabled? Is the macro not running at all or it is not working correctly? If need be, I will ask you to send through your dashboard file and look at it directly.

      Cheers

      Peter

  52. Arthur October 18, 2013 at 10:14 pm #

    Nice work Peter and thank you for producing this article and Excel performance dashboard. Very helpful looking at the logic you have used in developing your Excel dashboard.

Trackbacks/Pingbacks

  1. Free Excel Dashboard Templates from L3 Analytics | Dashboards By Example - June 22, 2011

    [...] blog post introducing the dashboards can be found at http://www.l3analytics.com/2011/05/16/free-excel-dashboard-template/.  It describes why dashboards are important in my field of web analytics, the contents of the [...]

  2. Outils Google Analytics - March 16, 2012

    [...] autre ressource proposée par le web analyst londonien Peter Oneil. Il s’agit d’un template Excel pour créer vos propres tableaux de bord : Un tableau de bord [...]

  3. Sufriendo con los dashboards | Arrate Enes - March 28, 2012

    [...] instrumental o de dashboard! Primero empecé con el dashboard de Eric T. Peterson y luego con el de L3 Analytics, pero con ninguno de los dos lograba solucionar mis problemas. Continué con Next analytics y [...]

  4. Dashboard metric | Findutahland - July 12, 2012

    [...] Free Excel Dashboard TemplateMay 16, 2011 … Up to 12 metrics can be included within each dashboard and these metrics can be analysed using the three basic techniques of data analysis: … You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed. « Richard newson [...]

  5. Digital Marketing Measuring Model: Business Objectives > Goals > KPIs > Targets | Raul Ramirez - October 16, 2012

    [...] on top of all of the tools; it could be a single spreadsheet that you update manually like this one:Free Excel Dashboard. There are also more sophisticated dashboard tools with great integration capabilities that update [...]

  6. Comment faire pour trouver mes KPI ? | TAKACLIKE // - April 4, 2013

    [...] de se lancer dans l’élaboration d’un plan de marquage ou la réalisation d’un tableau de bord, il faut réfléchir posément à ce qu’on va mesurer. Le modèle d’Avinash est une méthode [...]

Post Comment

Notify me of followup comments via e-mail. You can also subscribe without commenting.