Excel Visual Design Tricks

by Matthew Kuo on May 15, 2013

in Excel, Visual Design

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the most popular Excel books on Amazon
 

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.

Excel Visual Design Tricks 03

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.

Excel Visual Design Tricks 05

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.

Excel Visual Design Tricks 08

Excel Visual Design Tricks 09

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.

Excel Visual Design Tricks 04

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.

Excel Visual Design Tricks 10

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.

Excel Visual Design Tricks 01

Excel Visual Design Tricks 11

Excel Visual Design Tricks 02

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 Visual Design Tricks 06

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.

Excel Visual Design Tricks 07

{ 4 comments… read them below or add one }

Bob Watson May 18, 2013 at 7:43 pm

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.

Reply

Vincent Barr September 14, 2013 at 3:53 pm

Helpful, practical tips.

Well formatted article, too: no jargon, before and after screenshots, clear headers, no ambiguity.

Reply

sayeed a.shaikh December 10, 2013 at 5:06 am

Thank you, as your information about excel and I am geting knowlege to how to use excel by your information above examples.

Reply

ray May 18, 2015 at 5:31 pm

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

Reply

Leave a Comment

Previous post:

Next post: