5 Design Tips for Better Excel Reports & Dashboards

In this video I'm going to share with you five of my favorite design tips when I create Excel reports and Excel dashboards. It's a part of my upcoming online Excel dashboard course and I'm going to put the link to it in the descriptions below so make sure you check it out to see if it's something for you. Designing dashboards involves making decisions about what data to emphasize which in turn depends on the message that you want to communicate. The following design principles are going to help you showcase the information as clearly and directly as possible. Number one is contrast. Contrast creates emphasis so you may want to add a strong contrast for headings to communicate really fast the KPIs that are on display in your dashboard. For larger or longer tables you could even use a subtle contrast on the table rows as a visual aid to help the reader connect the numbers they see on the right hand side to the correct category labels they see on the left hand side. Number two is alignment. Alignments connect different elements so use it to show relationships. So in this case the change to previous year has been aligned to a center line so either left of the center line for decrease and right of the line for increase. So the viewer should logically deduce that the center line is previous year and left or right reflects a negative or positive change from previous year. So using layout in addition to color to communicate in this case loss or gains makes this more legible from a distance and also more quantifiable. For tables, numbers in a table should be right aligned in their cell so you can immediately tell larger or smaller numbers apart. The general convention is to align numbers to the right and text to the left although table headers can be an exception. It's really based on your preference how you think looks best in your table. Number three is repetition. Repetition encourages the brain to draw parallels and create associations. For example, abbreviations should be standardized throughout the report. Color usage should be standardized throughout the report. Also the common visual shorthand of a down red arrow and a green up arrow is in this example repeated in the table each time positioned next to a minus percentage or a plus percentage. So using these icons in addition to color eases the readability from a distance. So the viewer can visually filter high-performing or low-performing companies before even going up close to examine the details. Number four is proximity. Proximity organizes content. It creates focal points. Another way of looking at proximity is grouping. Its opposite is separation. So this example shows the metric percentage change the previous year grouped with its visual which is the conditionally formatted bar chart to imply that they are connected. And the white space separates this set from another metric which is the percentage change to budget and its visual. So the use of proximity helps clarity and it also adds visual punctuation to what would otherwise be a very dense table. Next is balance. Weigh large elements against smaller elements for visual harmony. So in this dashboard the large table on top is balanced with the two mid-sized charts below it. The borders are also aligned. This balance unifies the different elements and it reinforces their interconnectedness. So that was design. Let's take a look at color next. I'll be covering color inside the course. And the course is not just about how to create and design nice looking Excel reports and dashboards but I'm also going to create this KPI report that you saw in this video all from scratch. So basically from a blank spreadsheet. And you can follow along with me and not just learn about advanced formulas and form controls but also learn a lot of tips and tricks during the entire process. Once the course is out I'm going to put the link to it in the descriptions below so make sure you check it out. Very much looking forward to seeing you inside the course.