Excel Dashboards Book Giveaway

Last week, we had a giveaway for my PivotPower Premium add-in, and here are the winners:

Congratulations, and I will email you later today, to arrange sending your copy of the file.

Excel Dashboards & Reports

This week, Katie Mohr, from Wiley, has donated 2 e-book copies of Excel Dashboards and Reports , by Mike Alexander and John Walkenbach. If you are interested in building dashboards, this will help you get started, or increase the skills that you already have.

The book has excellent review on Amazon, and it takes you from the preliminary steps of planning the dashboard, to setting up the data, and creating interactive controls and even macros to enhance it. It covers charts, sparklines and pivot tables, plus instructions for connecting to data outside of Excel.

Waffle Charts

One of the book’s chart examples is a Waffle Chart, also known as a Square Pie Chart. It’s a 10x10 grid, and the coloured squares represent the completed portion. To see the instructions, you can download a free pdf file from the Amazon site.

Mike says, “This kind of chart is a relatively effective option when you want to add an interesting visualization to your dashboard without distorting the data or taking up too much dashboard real estate.”

wafflechart01

Enter the Giveaway

Katie Mohr, from Wiley, has donated 2 e-books for this giveaway. If you’d like a chance to win a copy, please read the rules, and then make a comment below.

  • In your comment, tell us:
    • If you have built an Excel dashboard, did you run into any problems?
    • If you haven’t built an Excel dashboard, why would you like to get started?
  • Include your email address, so I can contact you if you win. Your contact information won’t be publicly visible, and it won’t be used for any other mailings.
  • The deadline is Wednesday, August 28th, 2013, at 12 noon Eastern Daylight Time.
  • One entry per person.
  • The 2 winners will be announced on Thursday, August 29nd, 2013.
  • Each winner will have 24 hours to claim the prize, and if not claimed, another name will be selected.

___________________

You may also like...

62 Responses

  1. Rudi says:

    I work in the investments environment and we work extensively with charts and pivot tables as a form of feedback from the large volumes of underlying data. The dashboards I have created were on a very simplified base using a few charts and/or pivots to present an overview of the data. Since it was a simple design I did not run into any problems, but I am aware that dashboards can present much more useful details than just charts and small summary pivots. Using sparklines, conditional formatting and additional formulas one can produce much more detailed views on a single dashboard sheet.

    I am very interested in learning and applying these extra more advanced features into some better dashboard designs for my environment. TX.

  2. Vasim says:

    I have built one, the biggest challenge for me was (is in fact) display on various screen, sometime its on a projector, sometimes on small resolution screen and sometime big. The look of the dashboard doesn’t re-size properly (you need to scroll left right) — this really annoys me.

  3. Kurt says:

    I have not build dashboards so far, but would like to learn it, as they are a good tool for teaching different issues and probably a good tool to impress the boss

  4. Eamon says:

    Hi Debra, I haven’t built a complete one, but I have created dynamic graphs linked to Form checkboxes and created a scrolling table. However, when I tried to get the table to be sortable with the use of radio buttons I failed dismally! I don’t know VBA yet and trying to sort multiple columns by formula was a step too far. It is an area (dashboards) of Excel I want to learn more and I would be very happy to be included in your draw. Many thanks for the opportunity. Kind regards, Eamon

  5. Tony says:

    This is one area I have not explored yet. It would be extremely useful to present reports in a more simplified and concise fashion than the pivot tables can show.

  6. jimmm says:

    Haven’t built one, but would like to do so for a weekly collection of reports I produce. Too much tedium involved with my current methods.

  7. Josh G says:

    I have built several dashboards and always run into problems getting the offset formula into a chart series so the charts will update correctly. Also in a lot of cases I run into issues with the spreadsheet taking a long time recalculating with my formulas.

  8. Voldemar says:

    Made several with OFFSET heavily involved. 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. Anyhow this is peanuts comparing how much time is saved with the dashboard.

  9. JeanMarc says:

    I have built some dashboards in the past and the biggest challenges I had was to 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.

  10. Kelly M says:

    I have attempted to build an Excel dashboard but find that I 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. My way is definitely not the most efficient or effective way to create a dashboard. I would love Katie Mohr’s book to help me!

  11. Sumit Bansal says:

    I need to learn to make dashboard as nowadays we have overload of information/data and a lack of time and temperament. It is of utmost importance to summarize the relevant data and present it in an insightful way. Dashboards give you that liberty, without diluting the messaging. With whatever little experience I have had in making dashboards, the biggest challenge has always been to present the entire work in a single screen dashbboard

  12. Trouttrap2 says:

    I’ve built several dashboards. The problems I usually encounter is adding new data to the data set. Sometimes it takes some reformatting to make it work.

  13. Jason M says:

    I have built reports but not a true dashboard. In my business there is a great need for managers to monitor the productivity of their operations from several angles/dimensions.

  14. Rich Sobey says:

    We use Excel to make dashboard ‘front ends” . We keep all the data in a sql server database, then build either views or links to full tables (built daily from complex views for performance). The trickiest part is to refresh the data pull (VBA) before the pivot refreshes. We have a DATA tab for raw data, and then open on a pivot for the user. Since slicers became available (2010) we do not have to rely on filters in the pivot data or associated charts. We even built an action botton (so when they select what they want, they can drop directly to a Power point slide formated ready to go (VBA).

    They can save the adta and workbook to their desk for their own personal ‘view’ and then all they have to do is refresh whenever they need it updated. The file is not saved back to the sharepoint server. This capability with excel saves time and money for what would be a custom development effort, frees us from the tyrany of IT and thanks to the many gurus, such as Mike Alexander and John Walkenbach as well as your excellent site and articles, makes me the gallent knight coming in with solutions for the managers.

  15. Michelle R says:

    I never built dashboards but I am in the reporting department, and my boss thinks there’s a lot of future in dashboards. I would really like to learn about and make them.

  16. Greg says:

    I’m just getting started with a couple of reports that I’d like to turn into an ongoing dashboard. Right now it 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.

  17. MRoberts says:

    I have built a few dashboards in Excel and, by far, the largest problems I’ve run into will fall into two broad categories. First and foremost is usability / navigation from the users’ perspective. These will range from users with little to no knowledge of Excel to power users who are looking for power features, such as drill through, dynamic updates, notifications, and so forth. It is a challenge to strike the right balance for the target audience.

    The second challenge is related to selecting the right graphical display to summarize the underlying data in such a way that it enhances understanding without obscuring important information. It also needs to draw attention to a critical call to action, without overwhelming the user with meaningless informational alerts. Obviously, this involves selecting the right data, but, also ensuring that the analysis provided by the accompanying graph is appropriately selected and implemented.

    As I’ve said, I’ve built a few dashboards so this is not something I am looking to get started. That being said, I fully believe that there is a lot I can learn to improve the dashboards I create.

  18. jon says:

    The problems I’ve encountered building a dashboard is organizing the staging area correctly for Excel 2003 and up (I have to design two staging areas due to differences in formulas availability). I also ran into problems with automating the dashboards since I design them with .NET and NetOffice, not sure if my problems are with NetOffice. Not sure if that is entirely Dashboard related!

  19. Paul M says:

    Try to build a one page dashboard but by the time all of executives get done wanting to use for their various size screens, various overhead projectors and their paper, it will never fit on a single screen.

  20. Travis says:

    I would love to have a copy of this book. Dashboards are gaining more attention and being creative enough to build one is an area I need help. I’ve built a couple of them but users aren’t intersted in using them becuase they don’t like how I present the information. This book would be of great value to me.

  21. Eben says:

    As a Treasurer for a local non-profit, I use dashboards as a way to make the financials easier to “picture” and to maintain the attention of those Board members who are not financially oriented (read, virtually everyone). Using techniques I’ve learned on various Excel websites, including yours, I have been able to derive both a narrative and, via conditional formatting, a visual representation (using the colored arrows). My biggest challenge is to limit the amount of manual effort in developing financial income/expense dashboards. It would be helpful to see how others have approached so a “best practices” could be developed.

  22. mfexcel says:

    I used to created reports, not exactly a dashboard, with lots of formula. The problem is the long calculation time involved that jeopardizes the processing power of Excel…

    As my excel skills build up, mainly through various excel blogs including yours, I learn how to create good-looking dashboard, with interactivity. Thanks to various techniques like Dynamic Range, Input Form Control, Slicer, Sparklines, etc… My recent works did “WOW” my boss. However, I still consider myself a beginner in Dashboard reporting. I believe the book would definitely take me to the next level.

    Nevertheless, I encounter a difficulty in dashboard report that EXCEL canNOT help. In reality, many report readers still prefer to “hard copy” with all the details printed on A3-size paper, which you probably need a magnifier to see the data. When I showed them interactive Dashboard, they showed excitement then disappointment because they cannot see all data in one page and they can only view it through a computer……

    Sometimes, habit is hard to be changed.

  23. David_L says:

    I joined a one-person company last fall, and have been slowly building out our data systems. My work history has been more qualitative than quantitative, so it’s both business strategy and personal professional development. I’ve done a bit of low-end dashboarding in Excel, mostly trying to honor the idea that you enter your data in one place and analyze it in another – this looks like it would be a helpful resource.

  24. Marcus says:

    I’d say the hardest things for me to do when creating a dashboard for Excel is 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.

    Hope I win a copy as it’ll help me tremendously.

  25. Joseph C says:

    I have build a Dashboard but I want to make it very dynamic. This is my next goal to learn. This may help me to get onto the next step.

  26. Leigh S says:

    I build basic dashboards and would really like to do advance my abilities on this front. This would give me the extra help.

  27. Stephen McLaren says:

    I have built a lot of dashboards but have rarely used any defined structures or non-standard graphs. This has led to me creating reports that aren’t consistent with each other.
    I’d be very interested in understanding how to communicate data in a more condensed standard format.

  28. Ben says:

    Despite numerous attempts, I have not yet cracked the code for the mysterious data visualization work of art that my boss is seeking. Hoping to pick up some great tips and tricks and maybe find that diamond in the rough!

  29. Damon Edmondson says:

    I have regularly built dashboards. Interactivity is both a blessing and a curse in dashboards. A blessing as it allows a single dashboard to cover more information with drill downs and selectable sectors, business lines etc.. A curse, as the challenge is in 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.

  30. Cecilia says:

    My boss challenged me to build a dashboard. I did it and he was impressed and learned a few tricks. I am still learning and I am always looking for sources to consult and learn tips on dashboards. This book will be a great tool to have.

  31. Anar says:

    I have built some simple dashboards, but as BI expert I need to build a really advanced dashboards and need to improve some skills.

  32. Teh says:

    I have joined a new company recently and my employer requested me to build a Balance Scorecard type of Excel Dashboard. Currently, I’m encountering (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. I believe this book will help me a lot in completing my task.

  33. Jake says:

    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.

  34. Mark Cattrell says:

    I have created dashboards before, and a challenge I have run into is to establish a hard limit to what will be represented. Different parties to whom it was distributed wanted to see more and more customized for them in particular, defeating the purpose. Getting it automated was a great way to learn more about Excel– I’m sure this book will take me a few more steps in that direction.

    Thanks for these giveaway opportunities!

  35. Bryan says:

    I have only created one “dashboard”, though it was really more of a single-use data display than a real dashboard. The only problem I had was getting the camera tools to work correctly; sometimes they would go “blank” and I’d have to close and reopen the workbook to get them to show up, only to find another one went missing.

    I would consider myself more in the latter category — those who have not created dashboards. The reason I want to start is two-fold: (1) I want to be able to use better data presentations to further my career (I’m currently doing a lot of data processing but no data visualization); and (2) I have been recently appointed as a member of my church’s leadership board, and I want to create a dashboard to help us track the progress of our key stats (attendance, # new members, offerings, etc) to better be able to say if we are succeeding as a board to build our church.

  36. Dave says:

    I haven’t built a dashboard yet but have recently become addicted to excel blogs, forums and the like on line! So much so that now I am genuinely more interested in the previously boring and endless metrics at work because I am beginning to see how much better they could be displayed and enhanced to become interactive, in order to capture more of the teams focus and support in improving performance. I’m now more interested in the mechanics of the metrics than the basic raw data we normally see and have grown to accept as the norm, so that after 20 years in my industry, that’s like a breath of fresh air and a welcome glimpse of a better future. So to answer the question of why I want to build a dashboard – I want to feel engaged again and not bored with the day to day chore of work!

  37. Renee Keel says:

    I’ve built dashboards for the key performance measures and indicators for my department. The primary hurdles I had were twofold. First, learning the best way to create and format the data to have a dynamic year-to-date comparison chart (current year to prior years) that is controlled by a drop down list. The second was another beginner error, and that was discovering that the type of metric result, sum of whole number, sum of currency with decinals, average, etc., all required a different “staging” areas in order to keep the proper number formatting on the charts. I am a newbie, but am completely enjoying learning the tools, tips and tricks of Excel.

  38. Jeff says:

    I have attempted to build some dashboards, but have been unsuccessful. I end up having just using formulas to point to other tables or data in the spreadsheet. Doing this also does not allow me to use any of the charts to help visualize the data.

  39. Paul J says:

    I’ve not built a dashboard of my own yet, and am keen to gather ideas and tips to get moving with it.
    I’m a member of a ski club in Australia (yes, we do actually have snow – but not much and not for long!) and would like to build a dashboard for it to track it’s key success KPI’s.

    I never cease to be amazed at the things you can do in Excel given a mix of skills and imagination.

  40. Whitney Matson says:

    We recently starting using a dashboard in our organization. The problems I’ve encountered are displaying multiple concepts in one visual. I also have found that 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. I’m wondering if there issues I’m experiencing with pivot table limitations are resulting form my limited knowledge or Excel. Usually with Excel, there is always something else you can learn to overcome a problem.

  41. Jon Acampora says:

    The Selection Pane can be a very useful tool when creating dashboards. One problem you often run into when designing a dashboard is keeping all the objects organized on the sheet. Dashboards often contain a large number of objects (charts, text boxes, shapes, slicers, groups, etc.), and it is nice to have an organized list of all the items you are working with.

    The Selection Pane is a built-in tool in Excel and works similar to a “layers” menu where you can temporarily hide objects, reorder objects to send to front/back, select multiple objects, and delete objects.

    The Selection Pane can be accessed from the Format menu in the ribbon when an object is selected. It will appear as a menu on the right side of the Excel application. You can also add it to the Quick Access Toolbar.

    You can also see item groupings in the selection pane, and rename items. It has a lot of useful features, especially when combined with the “Align” tools on the Format tab.

  42. Alan says:

    I’ve never built any Dashboard charts, but I can see how they could be very useful in a lot of the reporting I do. My management is always looking for a quick look at a lot of different data.

  43. Dwight Johnson says:

    I am a lab manager and have been learning Excel since the early 2000’s by using the help files. I have made graphs showing revenue, # of patients, # of tests, and hours worked all appearing on the same sheet, but also for the whole fiscal year. Thus, after one month I have four graphs that look very empty showing only one bar each. After joining contextures and reading I realized that dashboards would be the way to go. I have attempted and only slightly succeeded in making a dashboard. The 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. I believe that the ebook would be extremely beneficial.

  44. Mike says:

    I’ve built dashboards, usually for other groups. I’ve gotten better over time and formatting charts and data tabs to make the files simple to update with new data. I haven’t learned yet to 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.

  45. John A Robinson says:

    Experimented with dashboard a few years back. Not received by Plant Managers as useful. My boss says we will wait until owners ask for another one. I am waiting opportunity to make presentation. Currently workload in Excel does not allow time for training (working on Pivot Tables, array formulas and Solver learning/creation).

  46. Cailey says:

    My biggest problem is 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.

  47. John Fairlie says:

    I have built numerous excel applications, simple to complex,for my co-workers but need to take the next step and create dashboards to enhance their experience with my data.

  48. Anders says:

    I haven’t built any Excel Dashboards yet, but a lot of other Excel applications.

    However, I find it very challenging and interesting to develop an Excel dashboard. As Excel has matured and today have so much functionality and interactions with the web e.g. Sharepoint this is getting more and more interesting. An IT department or a subject expert can collect and transform data into a repository and give users access to data to explore using Excel as a self-service tool for Dashboards and Ad-hoc analysis to explore the data.

  49. Ron says:

    I have not built any dashboards. I would like to figure out how dashboard a daily variance of a trial balance that includes both balance sheet and P& L. The data needs to be condensed as there are over 4,000 records (accounts) to look at every day.

    Thanks

  50. Gra Hal says:

    I have built dashboards, but there is always room for improvement and / or to see new ideas.

    I would love to be able to produce professional looking dashboards that do not require me to do any VBA coding in the background in any way to function.

    Cheers

  51. I use excel all the time but have not yet dabbled in dashboards but I already see the benefits they could bring to me so would love an easy read book on how to set them up.

  52. Beth says:

    While my bosses are “dazzled” at the pivot tables I present to them I know that with dashboards I can take our data to the next level. I am always looking to give them information that is understandable at a glance.

  53. Martin says:

    In my last job, for the past 15 yrs, I created lots of reports in Excel, first connecting it to a FoxPro DB, then to a SQL one, when we grew up. In the beginning, I retrieved all the info, then do calculations, and kept the records needed. Then, I learned how to pass a SQL query using a macro, and then how to pass a parameter to that query, so I narrowed the query to the requested at the very beginning.

    Then I had some time free at work, and discovered you, Chandoo, and many others, and realized I knew so little!!! but it helped me to change the way I created reports and dashboards, making them more efficient, good looking, and way more pro!!.

    Now, I was laid off, and a friend of mine asked me for help with some stats analysis from a Fb fanpage. And then I discovered PowerQuery, and cannot believe how easy is to get info using it!!!! Hope I can make it a way of living!!!

  54. BP says:

    I work in the healthcare arena and have designed an built multiple dashboards. One of the biggest issues I see is a 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.

  55. John Hackwood says:

    I have found dashboards to be satisfying but challenging to complete. I seem to get to the interactive table and key graphs stage ok but find I have a reluctance to launch into the huge time investment for the final presentation because of changing requirements. I need to be more organized with a series of VBA code to make the process more efficient and do little tasks eg grouping and ungrouping graphs from text boxes, producing clean simple charts consistently and getting colors of lines and bars consistent.

  56. Sam says:

    When I build the Excel dashboard (usually I copy from blogs, books) and if it has VBA macros, I usually get into trouble. Even though if I copy exactly, it might work for the first time but when I close it and come back to use the dashboard later, it does not work. VBA is so unstable.

  57. I publish several reports that would probably benefit from the dashboard treatment. I’ve not used them in the past but would love to learn.

  58. Andrea R says:

    For me, the biggest challenges in building dashboards are a)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. Too often I feel I include too much information “just in case it’s needed”.

    • Renee Keel says:

      I can relate to this 110% When I create a “comprehensive” report or dashboard, I often get feedback that it is too “busy” or complicated. I’ve always thought “more information is better” but am learning that in some cases “less is more.” :)

  59. Thank you for your entries, and the deadline has now passed.

    The two winners will be announced tomorrow, Thursday, August 29, 2013.

  60. Lyn says:

    I havent made a dashboard yet but I am accumulating lots of data that I would love to be able to put into a dashboard and impress my boss :)

  61. Eira says:

    Just learned about dashboards. This is perfect as I do a lot of management reporting which is always complicated as too much data is splashed over slides and sheets and changing screens. Even better is to use this in online meetings which we have 2 times per week with collegues in the USA, EU and Asia.