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