You’re looking to produce an impressive report or set of charts from Excel, but don’t forget that a good-looking chart or report is meaningless if you haven’t used the right data or calculations.
Before that final step, where looks are important, the most important thing is to lay the data out well, and ensure that the logic is clear and repeatable.
Have you included all the rows? (missing out rows is a simple mistake to make, but can produce wildly different results).
Has the information been formatted consistently (for example, some percentages might appear as 12% (in which case Excel stores it internally as 0.12), while others might appear as 12 in a percentage column (and Excel will store it 12, 100 times bigger than you mean it to be).
Can you trace your working easily through the spreadsheet? (someone verifying your results needs to be able to do this. That someone may even be you after several months.
This is also important if you wish to develop the spreadsheet further or debug it).
Do those stripy tables add anything to the understanding of the data? (if you’ve made good use of Conditional Formatting then stripes will just be confusing.
Don’t merge cells except in the final output, where you know no calculations will be performed on the cells.
It’s best to leave the prettifying until the final stages, other you can waste a lot of time to find that something needs changing again.
Conditional Formatting changes the font, border or colour of a cell depending on the criteria you have chosen. For example you might set a cell to go red if it went over 32 or green if a certain date is only three weeks away. This is very useful for highlighting records, eg, a temperature threshold exceeded or forthcoming subscription renewal.
Conditional formatting is a very useful tool in 2003, which has become even more powerful in 2007, because it’s not restricted to three criteria per cell.
However, the extra flexibility of 2007 also means it is easier to set up conflicting rules.
Worse than that, Excel also sets up duplicate rules in the background because they refer to a different range. This is a particular problem when one is developing rules, a process which is best done step by step (conditional formatting doesn’t always behave in the way one expects). Unfortunately having developed it for one cell, copying it forward over a range of cells, retains the original test, so there are now two rules.
I think two main rules apply with conditional formatting:
1/ Don’t have too many – it just gets confusing
2/ Define your own rules and don’t use Excel presets, because then you know what is going on