26 June 2009

when = doesn't = equals in Excel 2007?

Just for a change, it's me that's looking for an answer. Something strange happened during a recent Excel 2007 course when I was demonstrating how to make one cell use the content of another one and compare it then put a statement in a box depending on what the result was. For years I have been using something along the lines of =if(A1=0.01,"right","wrong"). Pretty simple stuff but it didn't work!

Investigating, it appeared that instead of A1 containing something like 0.01 which was the result of some basic adding and multiplying it actually contained something like 0.0099999913. So I was getting wrong when it should have been right.

We looked at other numbers on the sheet and lots of them were similarly odd. Even though we'd entered just numbers with at most two decimal places in cells and none of the calculations could have led to intermediate steps in the calculations (just adding or subtracting or multiplying by whole numbers) some had lots of extra decimals and were nearly the right number and when rounded showed the right number but weren't stored by Excel as the right number.

Now, unless I have been really silly or the coffee and biscuits had a detrimental effect on not only my sanity but also that of some pretty talented delegates, this is bizarre to say the least. I guess I should do a search for the answer but thought I'd share it with you just to show that there are questions I can't answer! No doubt there'll be more on this soon!

24 June 2009

I don’t want people to see my formulae (and may also want to prevent editing of some cells)

To hide formulae in Excel2007 select the cell(s) with the formula or formulae and right click. The Format Cells panel has a Protection tab. In there check Hidden (and locked if you don’t want people to edit the cell at all). Then return to the sheet. On the Review ribbon, click Protect sheet. Be careful if you set a password to make a note of it somewhere!

In older Office all cells are locked by default, so ‘protecting’ the sheet will prevent any changes which may not be what you want. So you need to uncheck the ‘locked’ setting first for whichever cells you want people to have access to. I think it’s the same for Office 2007. To protect a sheet in Office 2003 and previous versions use Tools>Protection>Protect sheet after clearing cells as necessary.

Similar principles apply to hidden cells (‘hidden’ seems to mean formulae only rather than anything else)

My language setting is Welsh! How do I change it?

Use *Tools menu > Language > Set Language

In the panel select just English (UK) and click on Default then click Yes to change Normal template for Word

If this doesn’t work try using *Tools > Options >Popular options and change language settings

It is more likely that your default language may have been set as English (US) instead of English (UK) which you’ll spot by things like color, dialing with one letter l and organization with a z instead of an s etc.

*see other post to locate Tools in Office2007

How do I stop overwriting in Word?

This happens in older versions because you have (usually accidentally) hit the Insert key.

Use *Tools menu > Word Options > Advanced

On that panel uncheck the Use Insert key to control overtype mode

In Word2007 this is off by default but may be on in older versions

*see my other post for the new Tools location

Where on earth are Tools and Options for Office 2007?

To get to the Tools menu in Office 2007 either download the ‘old style’ toolbar add-in or use Start>All Programs>Microsoft Office>Microsoft Office Tools