I’ve emphasized before that the key behind Visual Design is that every formatting decision you make should have a purpose behind it. In the following post, I plan to demonstrate more of the visual style I use in Excel. These formatting techniques still do have justifications behind them, but there are no hard and fast rules about whether each tactic is necessarily critical to good Visual Design. The main reason I personally use them is because I simply think they make your outputs look better. Many of these suggestions produce only subtle improvements, and they won’t necessarily work in all situations, but they’re definitely worth trying if you’re creating an important Excel deliverable.
Please note that I’ve covered conditional formatting with formulas and custom number formatting in other posts.
Dark Background and White Font
This was probably one of the first Visual Design tricks I learned and we see it repeated in several different outputs. By using a dark background and white font for any line of text, you emphasize the header and make it more visible. Below is an example of the difference between two tables. The latter version is just a little bit easier to read.
Soft Gray Lines
The soft gray color is primarily used to de-emphasize components that are not necessarily important, but would be confusing if removed. Below are two examples: one with the axes and gridlines of a chart de-emphasized and a before and after example using a data table.
Dotted Lines
Dotted lines are helpful because they provide contrast within your charts and don’t use up that much ink. The two primary situations where they’re useful are: for indicating a what-if scenario in a line graph and for indicating a threshold within a bar graph.
To change your regular line chart into a dotted line
Right click your data series
Select – Format Data Series
Select – Line Style
For Dash Type, pick one of the dotted lines
White Borders
White borders are generally used to separate two components that are shaded in dark colors. This is a useful formatting technique because, assuming you’re using a white background, white borders only show up where you need them. Below is an example of a stacked bar chart with and without white borders.
Custom Trim
Adding a trim to the top of my Excel outputs was something I picked up during my consulting career. The type of custom trim shown below is generally applicable because most companies have at least two primary colors in their logo, allowing you to customize it for your client. How you design the trim is somewhat irrelevant; you could argue whether this looks good or if you would prefer another version. The point is that it’s useful to have some sort of consistent formatting at the top of your documents. Below is what I used for the MBA Excel Homework Tracker template.
Uniform Column Widths
One of the annoying things I see in Excel is when someone builds a table with varying column widths. Having uniform column widths is simply more professional and pleasing to look at. To make your columns a uniform width, select all of your columns, find your widest column, increase or decrease its width by 1 pixel. Below is a before and after example of the process.
Pastel Highlighting
Highlighting by itself is an effective way to convey additional information to the end user of your outputs. If you soften the highlighting, by using pastel colors, it allows your data to stand out more. Look below for an example demonstrating the difference between bright highlighting and pastel highlighting.
Excel Data Bars Instead of Color Scales
I previously just mentioned an example of color highlighting, which can definitely be useful in certain situations. For example, if you want to know exactly which data points are past a certain threshold. However, there are times when your user needs to view the data with a higher level of precision. For example, if your audience needs to know the magnitude of one data point relative to another. In that scenario, a red / yellow / green highlighting scheme simply won’t cut it. By using Excel’s Conditional Formatting data bars, it’s much easier to interpret relative magnitude than with different shades of colors.
Matthew,
Excellent article. I also work on the basis of “Format for description, not decoration” – as espoused by Raffensperger (2001) in his “New guidelines for writing spreadsheets”
Because I like this article I’ve added it to Connexion, our collection of the most useful and interesting spreadsheet-related articles from the web
Cheers,
Bob.
Helpful, practical tips.
Well formatted article, too: no jargon, before and after screenshots, clear headers, no ambiguity.
Thank you, as your information about excel and I am geting knowlege to how to use excel by your information above examples.
i’m having trouble with bar graphs, the labels for the items cannot be compressed and the chart itself is less than half the total area
i can make the chart even smaller, but i can’t make it any bigger
how do i control the size of the panel that is given over to the labels
many thanks ray