Search Not Just Numbers

Tuesday 26 June 2012

Excel Tip: Using conditional formatting to format the whole row

Colour-coding can make it much easier for humans to read a spreadsheet, as our eyes and brains are wired up to treat differences in colour as important. For example, you may colour rows as red, amber or green based upon a status level - possibly, in a stock list, how close an item is to being out of stock.

If you do this, do you do it manually?


Many users know about Conditional Formatting, but do not know how to format whole rows in this way. I, for one, used it for years without knowing how to do this - but it's really simple when you know how.


It involves using Conditional Formatting's formula feature with Excel's ability to fix references using the dollar sign.


Let us take a very simple example of stock, where we wish to show lines with less than five items as red, less than ten items as amber and ten items or over as green.


Assume we have a heading row so the stock data starts at row 2, and the stock level is the last column of the data and is held in column H.


Highlight cells B2 to H1000, or down to whatever row more than covers the number of stock items you might have. Select Conditional Formatting (from the Home ribbon on Excel 2007/2010, or from the Fomat menu on Excel 2003) and select "Use a formula to determine which cells to format" (in 2007/2010) or "Formula is" (in 2003).


2003 and 2007/10 work slightly different in this respect, as 2003 allows you to add up to 3 conditions using the Add button and 2007/10 allows many rules to be added and managed.

The following formulae should be entered as the three conditions in Excel 2003, or as 3 separate rules in 2007/2010. In each case you will determine the format to be used if this condition is true using the Format button next to where you enter the formula. This works very similar to the normal dialogue box you get when formatting cells.

For the red:

Formula         =$H2<5
Format          Fill Red

For the green:


Formula         =$H2>=10
Format          Fill Green


For the amber:


Formula         =AND($H2>=5,$H2<10)
Format          Fill Orange

(read more on the AND function here, under combining conditions)

The most important thing to note here is the use of the dollar sign. What we are doing here is fixing the column (H), but leaving the row flexible, so that all cells in the highlighted range, look along their own row to column H to apply the criteria. Also note that you should enter the formula as if you were entering it for the first row of the range - this is why we have entered H2 as row 2 is the top row that we have highlighted.

One other thing to note is that the formula is always preceded by an equals sign, even if it has an equals sign in the criteria. So, if we had wanted to format red when H2 was equal to 5, we would have, rather oddly, entered =$H2=5.

This technique has many applications, and is really simple when you get the hang of it.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Wednesday 13 June 2012

Time saved using spreadsheets always beats time saved writing them!

I see many time-saving tips for Excel users that speed up the time it takes to prepare a spreadsheet, such as keyboard shortcuts, text-to-columns, etc.

Where these are all very useful, I sometimes feel that an important truth is often overlooked.

A spreadsheet gets built once, but is used hundreds of times!


It is always worth having this at the back of your mind (or stuck on the wall above your desk!) when you are building a spreadsheet.

Anything that can be done when setting up the spreadsheet to speed up the experience for the user (even if that's you) will pay for itself many times over.

This understanding can often be the difference between an amateur spreadsheet and a professionally produced one. They both do the job, but the good spreadsheet will let you do it in half the time, saving far more than the cost of doing it properly in the first place.

There are many ways in which a good spreadsheet does save the user time, such as drop-down lists, logical data layout and conditional formatting, but the key is to know it's important. If the user is having to copy and paste, change formatting or type something more than once, these are good indicators that the spreadsheet could have been better designed.

If this is the case, it is costing you every month, week or day (depending how often the spreadsheet is used) and it needn't be the case. Take another look at it and see where it can be improved - or get it rewritten by a professional. Either way, you will be amazed at the time savings that are possible.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".