Over the last few weeks I have been working on a project in Excel. Like (I suspect) most, I had used excel before but never for anything more complex than simple grid data and translation documents. But my most recent project required so much more. A lot of what I learnt was in VBA script but I won't bog down this post with script. Instead I will cover some of the features that I learnt about that pretty much anyone could find a use for. Maybe some of this is already common knowledge but it wasn't for me so here you go:

Freeze Panes: I'd seen these a lot in other peoples spreadsheets but I didn't know how to do it myself. It allows you to fix the first few rows or columns so that the scrollbar only moves the other half of the spreadsheet. This is great for making the headings on your columns remain visible even after scrolling.

To do it, simply select the row or column where you want to put the freeze line and select Window > Freeze Panes from the file bar (I am working in 2003, I'm not sure of the equivalent for other versions of Excel).

Control toolbox: There’s too much to go into detail here but the control toolbox basically allows you to add elements such as text input boxes, buttons and dropdown boxes into your spreadsheet. They can be made extremely powerful with the use of VBA script but still can be very useful without, especially using the 'LinkedCell' property which will tie the objects data value to a cell in the spreadsheet, even one on a completely different worksheet. This means that you can hide the actual data and formula cells away, reducing clutter and protecting important cells.

To work with the control toolbox you need to enable its toolbar. Once you have a control in your spreadsheet you can edit its properties by switching the spreadsheet into 'Design Mode' (there is a toggle button on the toolbar) and then right-clicking on the object and selecting 'Properties'.

Conditional Formatting: This allows you to set rules about how a cell should look according to various different circumstances that you define yourself. At its most basic you can just set it according to the value in a cell. For example, you could make a cell turn red if its value is less than 0 or turn green if its value is larger than the value in another cell. If you want to get more complex you can use a formula. For example making the highest value cell in a list turn gold (well...orangey-yellow).

To set the conditional formatting of a cell, select 'Conditional Formatting' from the 'Format' dropdown on the file bar. You will then be presented with a window in which you can set your rules.

IF formulas: I said I wouldn't cover scripting and IF statements are very much a scripting concept but you can type them directly into a cell and I think the logic behind them is pretty strait-forward and yet so powerful they are well worth getting to know for any excel user. To explain how they work, I will use the hint that Excel its self displays while you are typing one out:

=IF([logical test],[value if true],[value if false])

You are basically asking the computer to work out if something is true or false and then telling it what value to put into the cell in the event of those outcomes. So to use a trivial but hopefully understandable example, lets say that you have a cell (A1) that shows a percentage and you would like another cell to state "Incomplete" if the percentage is less than 100 and "Complete" if it is 100 or more. First we need to determine our 'Logical test'. In this example we will test to see if cell A1 is less than 100 in formula terms this will read as A1<100. We now need to decide our 'Value if true' or in other words, what would we like to put in the cell if A1 is less than 100. Or answer in this example is "Incomplete". Finally we must decide what our 'Value if false' will be, in other words, what would we like to put in the cell if A1 is 100 or more. Our answer is "Complete".

So to add the IF formula, type this into a cell: =IF(A1<100,"Incomplete","Complete")


Well that is all I have for now, if it helps someone let me know!

Jamie