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
 

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 rusinfomos.ru 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 }

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

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:


Related pages


excel sheet vlookupnested if then statements in excelexcel if then formula with multiple conditionsvlookup in excel exampleexcel formula inputpurge excel fileexcel vba specify rangealternative to vlookuphow to write formulas in excelexcel 2013 stacked bar chartuse of vlookup and hlookuppower query in exceluses of macros in excelexcel file converter to pdfvlookups on excelmost common excel formulasmatch two lists in exceldifferent excel formulasbell shaped curve generatorshortcut key of excelucla anderson career centerhow to write macros for excelmicrosoft excel consultantscreate macro excelmicrosoft access 2010 free trialfinding doubles in excelw4 estimatorlookup excel functioncompare formula exceleliminating duplicate rows in exceltick symbol for wordhow to make vlookupiferror with vlookupcreate a histogram excelvlookup max valuehow we use vlookup in excelvlookup and hlookup formulaexcel compare columnsexcel bin rangeshuffle nameshow to make a stacked bar graph in excel 2010excel count occurancessg&a calculationucla anderson mba class profileindividual error bars excelexcel cell reference functionconvert excel file to pdfexcel match multiple columnsucla bruin storeduplicates on excelmicrosoft power query for excel add inhow to highlight duplicates in excelhow to write macro in excelvlookup matching two columnshow to insert check mark in excel 2007find duplicates in excel 2013formula for numbering in excelhlookexcel formula for numeric to wordv look ups excelmatch two lists in exceldrag dates in excelexcel formulas and functions tutorialcheckbox symbol in excelexcel find duplicate rowsmost popular websites worldwidemba recruitershow to find duplicate rows in excelmark duplicates in excelvba excel match functionvlook up exampleshow to build formulas in excelhow to delete duplicate rows in excel
\n