We planned our Dashboards and prepared the data. We’re now ready for the artistic magic: the visual effect and user experience. This is the focus of today’s Blog post, the last in this “all Dashboards” 4-articles series. If you missed the previous parts, start here.
Now is the time to take the blueprint of our Dashboard we sketched when we planned our Dashboards layout, and think.
Revisit the thought process that dictated the current layout, acknowledge the business story as projected from the paper. Ask the business questions you first heard from your customer when you analyzed the business requirements. How is it all coming together? Any fine-tuning required?
Next, shift your thoughts to the overall visual design. Section frames, colors (does the Dashboard presents a branding opportunity?), titles (locations, size, colors, font), space around each element, sections differentiation (top vs. bottom; left vs right), differentiation stroke: is a stark contrasting divider or a subtle clue needed? will it be using frames, thin/thick lines, colors shadings, noticeable gap?
Finally, you’re ready to grab the mouse and start populating the Dashboard Worksheet.
The grid of cells is your helper but also one of your significant limitation, especially when it comes to Dashboards. As Dashboards will always hide the gridlines, the grid serves only for the layout and arrangement of all elements, labels, titles, data and shapes.
One rule of thumb that I follow, is to always be generous with spare (empty) rows/columns. It is always easier to reduce a row’s height or a column’s width, than to run into a missing row or column down the road.
I usually leave column “A” unused, allocate row “1” for the top-level title and rows “2” and “3” unused as spacers. As you align your Dashboards elements, start with matching the columns and rows, as these cut across the full height and width of the Dashboard. Once a column’s width is set to 10 pixels – it will be 10 pixels throughout all elements stacked one on top of the other. Similarly, if a row’s height is enlarged to highlight elements’ titles – it will be enlarged left and right across the board.
Especially if your Dashboard contains data elements (as opposed to chart shapes), this grid play can become tricky. If you differentiate elements using a shaded background – leave the needed rows and columns “around” the data area for the margin effect. This shaded frame is clearly shown in the following chart, designed with Google Sheets, not Excel. Here, I also allocated a double frame: the black thin frames dividing into two sections, and the colored boxes sub-dividing the data elements. Extra rows and columns are allocated.
The yellowish shadow of the upper strip in the next Dashboard shown here (see below) required two rows and two columns extra – just for this. It is also in accord with the green frame underlying the matching charts below.
Merged cells can be a blessing or a curse, use them smartly. I do merge and center the top-level title across the full width of the visual area of the Dashboard, but try to avoid merging cells otherwise. On the other hand, some elements can leverage merging cells to gain a specific effect, but this would be the exception. Either way, I never merge the cells at this stage (maybe temporarily for quick testing) – only at the end once all data and visual elements have been presented.
As you adjust the grid, start populating cells with data. We’re not concerned with shapes at this time (e.g. charts).
Allocate global filters area (usually at the top, below the title, or on a side panel). Enter their labels and align the text. I like to align these labels to the right, such that they are close to the filter cell itself.
Enter real data and formulas fetching the data you staged to serve the Dashboard. Apply font, size, bold and alignment, but not yet any colors, conditional formatting and the like. You want to see how the real data fit in the columns’ width.
You may need to shift an element to the right or to the left, to comfortably live in a column. In the following Dashboard presented here, you can see that the month filter (orange “Mar-6” drop-down) shares a column that has a specific width to accommodate the data in the table below. I could not place it one column to the right, as its width serving the arrows icons in the table is too narrow. I also want this selector to be on the left, not hanging somewhere 30% into the Dashboard area on the horizontal axes.
Test the dynamics of the Dashboard. Change the selectors to see that data refreshes properly. Verify the correct data is presented. See that all data for all combinations is comfortably presented in the cells.
Once all data is comfortably placed with the necessary gaps and spacers, you are ready to start dressing your Dashboard.
Start with the outer frames (those back shadows or frame lines), coloring higher layers as you dress it all up. Next, place the shapes (charts) in their position. These are flexible, unbound to the grid. How does it look? Needs to adjust spacers and gaps?
Finish by applying final fonts and cells formatting and colors, don’t forget to unlock any selector cells (that “Mar-6” orange filter up there) to allow user interaction with the Worksheet protected. Merge title cells and protect the Worksheet.
Voila! You have a beautiful, dynamic Dashboard, serving the business!
This concludes our 4-week ALL DASHBOARDS journey. I set out to give you a navigation map to create great Dashboards with Excel and I hope you feel more prepared and excited for your next project.
Subscribe to my Blog up here to always be notified of new posts I write every week. Get most valuable content for Excel business information developers here!
Join today to the Excel VBA Inner Circle with Mor Sagmon.
The first to enroll as founding members will enjoy exceptional, life-long benefits and prices.
Click the button below to learn more and join.