Home > Skills > Dashboards > Survey

Excel Dashboard Problems

On my Contextures blog, I asked if people used Excel Dashboards - Yes or No, and what problems they encountered, if they used dashboards. The detailed results and replies are shown below, and you can also see my summary of these results in the embedded Excel workbook, below all of the replies.

Do any of these dashboard problems sound familiar, when you work in your Excel dashboards?

Use Excel Dashboards Yes or No

There were 58 replies to the survey, with a large majority saying Yes, they do use dashboards in Excel.

• Yes - 41 (71%)
• No -- 17 (29%

To get those totals and percentages quickly, I built a pivot table, to count the Yes and No replies.

The "Built a Dash" field is in the pivot table 3 time:

1. In the Row area, where the labels show Yes and No
2. In the Values area, where it shows a count for each reply
3. Another copy in Values area, with the setting Show Values As - Percent of Column Total

Note: I posted this survey on my Contextures blog when a new book, Excel Reports and Dashboards, was being released. You can see the latest version of that book on Amazon, if you're looking for tips and tutorials on getting started, or advancing your dashboard skills.

What Type of Excel Dashboard Problems?

For those who replied Yes in this dashboard survey, I asked them to add a comment about the type of problems they encountered when working with Excel Dashboards.

I've listed their comments below, grouped into broad categories. As you can see, the biggest group is Layout problems, with 25 comments (61%). It's hard to find the best way to present all that data!

• Automating - 1 (2%)
• Charts - 1 (2%)
• Data - 4 (10%)
• Formulas - 1 (2%)
• Interactivity - 2 (5%)
• Layout - 25 (61%)
• Skills - 4 (10%)
• Updating - 3 (7%)

And here's a screen shot of the pivot table that I created, to get those category totals and percentages. You can also see this pivot table in the embedded Excel workbook, below all of the replies.

Dashboard Layout Problems

Here are the comments that I included in the Layout problem category.

1. users aren't intersted in using them becuase they don't like how I present the information.
2. Not received by Plant Managers as useful. My boss says we will wait until owners ask for another one.
3. getting the camera tools to work correctly
4. find the best way to show the data so it was clear and meaningful to the users. Also adding some dynamic features and controls is not always easy.
5. usability / navigation from the users' perspective. ...summarize the underlying data in such a way that it enhances understanding without obscuring important information
6. creating reports that aren't consistent with each other... interested in understanding how to communicate data in a more condensed standard format.
7. have not yet cracked the code for the mysterious data visualization work of art that my boss is seeking.
8. (1) difficult in choosing correct chart type to present my ideas, (2) difficult in producing an interesting dashboard layout, (3) difficult to put all data together to produce an useful business information and (4) difficult to design and format the dashboard.
9. displaying multiple concepts in one visual...for ease of importing the same data over and over again, I'm using more hard coding (as opposed to pivot tables) than I would if I was doing something on the fly for myself.
10. design: how to most effectively highlight the important numbers while reducing clutter? and b)metrics: driving to the important numbers that will help senior management make decisions.
11. establish a hard limit to what will be represented. Different parties ... wanted to see more and more customized for them
12. make the dashboards easy for someone else to customize and change (eg. adding more data series, deleting a column, etc.) without messing up the file.
13. I have build a Dashboard but I want to make it very dynamic.
14. I work in manufacturing and have built some simple dashboard type reports. I would like to improve what I am doing and ideally make it available to mgmnt dynamically instead of a weekly report.
15. best way to create and format the data to have a dynamic year-to-date comparison chart...type of metric result...all required a different "staging" areas in order to keep the proper number formatting on the charts.
16. on various screen...the dashboard doesn't re-size properly
17. Try to build a one page dashboard ... various size screens, various overhead projectors and their paper
18. excitement then disappointment because they cannot see all data in one page and they can only view it through a computer
19. limit the amount of manual effort in developing financial income/expense dashboards.
20. making it repeatable, especially because the data I used each month tends to change forms and my bosses decide they want to see different aspects of the data.
21. need to be more organized with a series of VBA code to make the process more efficient
22. takes a bit of tweaking to get the data in the right format, and I'd like to explore more options for pulling it straight to Excel.
23. keeping all the objects organized on the sheet
24. produce professional looking dashboards that do not require me to do any VBA coding in the background in any way to function.
25. lack of end user knowledge of what can and can't be done. This is not only with the skillset of using the output, but also in the design phase where they simply think you can just put a bunch of data in and get a meaningful product.

Dashboard Data and Formula Problems

Here are the comments that I included in the Data, Updating, and Formula problem categories.

1. problems getting the offset formula into a chart series... long time recalculating with my formulas.
2. organizing the staging area correctly for Excel 2003 and up (I have to design two staging areas due to differences in formulas availability).
3. managing performance so that they don't get bogged down when adding data. Array formulas, VBA and data tables are awesome. However, I have learned that when using them for raw analysis can kill performance.
4. enter your data in one place and analyze it in another
5. When offsetting from the separate Excel book, that book must be open in order to see the data in the cells. Don't like making a replica to avoid that. Don't like opening the source.
6. want to cram everything into it and not really able to tell the "story" that I want to present. I also use a lot of "work arounds" to get everything to update and display correctly.
7. problems I usually encounter is adding new data to the data set. Sometimes it takes some reformatting to make it work.
8. trickiest part is to refresh the data pull (VBA) before the pivot refreshes.

Dashboard Charts and Interactivity Problems

Here are the comments that I included in the Charts and Interactivity problem categories.

1. using formulas to point to other tables or data... does not allow me to use any of the charts to help visualize the data.
2. making the dashboard approachable for less sophisticated users. For example, not every user understands a click to drill down or expand, or a drop box to show a different form of information.
3. user can select the day of the week to be displayed and the type of patient encounter (1-5) and the graphs respond correctly. I ran into a problem when trying to have the same thing done with months and time of day admitted.

Other Dashboard Problems

Here are the comments that I included in the other problem categories - Skills and Automating.

1. very interested in learning and applying these extra more advanced features into some better dashboard designs
2. would really like to do advance my abilities on this front.
3. looking for sources to consult and learn tips on dashboards.
4. need to build a really advanced dashboards and need to improve some skills.
5. if it has VBA macros, I usually get into trouble.

Excel Dashboards - 5 Quick Tips

This short video shows 5 Quick Tips for Excel Dashboard. Keep these tips in mind when you work on your next project, to make the data easier to understand. The steps are also shown below.

Dashboard Survey Results in Excel

Here is an interactive workbook that shows the results from comments in our Excel Dashboard survey. You can download a copy of this Excel file in the section below.

Get the Excel Workbook

To see all the survery replies, and my pivot table summaries, you can download the Excel dashboard survey results workbook. The zipped Excel file is in xlsx format, and it does not contain any macros.

Learn More About Dashboards

And if you want to improve your Excel dasboard skills, I highly recomment Mynda Treacy's Excel dashboard course. There's are free dashboard webinars that you can watch, to see if Mynda's teaching style is a good fit for what you need.