The Excel Dashboards Guide: The Visual

How should a dashboard strategy be conceptualized? How do you translate the accumulated data into an effective business compass for the decision maker? Start your journey here

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.

Start with Observation

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?

Laying Out the Data on the Grid

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.

Here Comes Data

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.

Put on the Dress

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.

Guidelines, Tips and Tricks

• Less is more. Refrain from cluttering the eye with unnecessary information. A chart does not need to always have a title, axis titles and a legend. Tune the level of detail to the business question it addresses. The four left-most charts in the above Dashboard show a trend of the current metrics detailed and labeled in the elements above them. The labels need not be repeated below. The message here is the trend over time – not more, not less.
• Every element, every touch – must serve a purpose. And that purpose is the business question. Use colors smartly, not just to make things “pretty” (to your eye). Bright colors, strong colors, dark colors – they draw attention. Light colors reflect subtle changes, elements divide, borders. Red is alerting. Green is “confirmation”. Yellow is for warning. A thick bold line is very “loud”. It can almost be interpreted as a shout. Always go back to the main driver: what business question is this element answering? What should be accented and to what extent.
• Keep an element’s message consistent using the same color family. In the below Dashboard, see how each section has its own color theme. The title, fill colors and font color in each theme – are an interplay of different shades of blue, or green or orange.

  

• Consider the user screen size. Limit your visual area so that it fit in one glance. Scrollbars are a big no-no in Dashboards.
• Taras Bakusevych compiled a great article to further elaborate on the design of Dashboards and selecting the right chart for the task – read it here.

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!