Designing Dashboards

After spending 4 or 5 weeks on developing ideas for a dashboard for an upcoming huge project, I finally have a rudimentary dashboard. With that experience behind me, here are some ideas of what to do when you are given a project to pull together something.

1. 1. If you can, find out what the goal is. In my case, I think my bosses want to find out what I come up with before dictating something. I think it’s to see what other ideas I might come up with before closing them down. They want to keep it open for as long as possible.
3. 2. Then do some research and educate yourself. I had to learn about what is typical of HR metrics and what the latest thinking is about merging or bringing on new employees. I wanted to at least cover or consider the current best thinking.
5. 3. Jot down notes and ideas on what you think you might need. This part probably took about a week to 2 weeks and I’m still jotting down notes whenever I think of something new or read something interesting that might pertain to this project.

4. Then start designing your working prototype. Designers at this point usually say draw prototypes on paper. I’m doing prototypes on the computer because I need to see what will work, formulaically and macro-wise. There’s no point designing something on paper if I eventually can’t make it work on the computer, especially in the time I have.

5. It goes without saying, test your prototype. To test my dashboard, I created dummy data by using RANDBETWEEN. I randomly generated the number of employees for each site. I randomly generated the dates of acceptance or the dates of training. Any data that I would need and could be randomized, I used the RANDBETWEEN function. That was fun. You can randomize the acceptance or action by assigning 0 to no, and 1 to yes and depending on that random response, you generate a random date. So if you randomize the acceptance response and if it comes up “yes”, then you can randomize the date of acceptance. Example: =IF(RANDBETWEEN(0, 1)=0,”not accepted”, RANDBETWEEN(NOW(), OFFERDATE + x days)) where x days is the number of days to accept the offer.

6. Probably the hardest logic to work out will be time-based. Every time I do spreadsheets that deals with time, I find they are the most complicated. Figuring out formulas for the acceptance date and responses were relatively easy. But it got slightly more complicated in the onboarding phase because I had to consider those who haven’t accepted yet but still could. Then the benefits training and sign up got even more complicated because I have to consider the status of both those who haven’t accepted yet but still could and those who haven’t onboarded yet but still could. This complication was especially true when figuring out who could potentially sign up this week or next week. If you haven’t done this before and don’t really understand this, just note that time-based logic could get complicated.

7. Test, test, test.

8. Plan out what could or should be automated with macros. In my case, I sprinkled choices, such as today, this week, next 2 weeks, using data list. I wanted the related pivot tables to automatically update when I make choices, in case I forget to refresh the tables. Or, if someone else was using this spreadsheet, I wanted the macro to do it for them in case they don’t know anything about pivot tables. These macros were created specifically for a certain tab and became initiated by “Worksheet – change” whenever someone makes an action in certain cells. I recently did a post on these types of macros.

9. When designing dashboards, you might find that you must have a tab for data, a tab for generating information via formulas (most often VLOOKUPs), pivot tables and charts and a tab for printing out the visual information. Because the information could potentially grow large and small, you need to leave a lot of spaces in your spreadsheet to hold the changing information. Those spaces mean the information will not all be in one spot; they may potentially be all over the spreadsheet. Hence the third tab to consolidate your visuals for printing. Here’s where the camera tool comes in. It allows you to take a picture of your table and/ or chart and place it in the third tab. These types of pictures will update whenever information is updated. You can move and resize the pictures, without worrying about the underlying layer of columns and rows. The camera tool is a really handy tool to know.

But there is one problem in using the camera tool along with the macros: they add about 1 to 2 minutes of extra calculating time. That’s disappointing. I will have to think about that.

The remaining steps are what I anticipate what I will have to do next.

10. Present your prototype and gather feedback and ideas. I haven’t done this yet but when I do, I want to find out about the goals because I may have to include it in the dashboard. Remember when I said in step one that they kind of left it up to me, possibly because they wanted to see what I would come up with? Well, this step is probably when I get extra information on what they need.

11. Finalize your dashboard by making the requisite changes.

12. Test, test, test.

You might have to do another presentation or go back to an earlier step, but I think what I have laid out here is a basic approach to designing a dashboard or any other kind of spreadsheet.