It has been a particularly geeky day for the Poorhouse today as it happens. Ugh. So let's celebrate with the latest of his Excel discoveries designed to allow an optimum amount of user laziness in between the awful clicking between cells, formulae that make sense only to Stephen Hawking and charts formatted so horrifically that it must have been intentional.

This one's a macro to solve the insanely annoying "Number stored as text" error, which usually occurs when copying and pasting data from elsewhere, or when an idiotic, deserves-to-be-spat-on colleague sends you something that is either exhibiting a remarkable level of stupidity or a deliberate wind-up.

The Poorhouse's discovery of the day: DocTranslate - A heavenly, and free, treat for those of us poor info-workers who deal with several countries, not all of which are polite enough to speak English as their first language.

Using the mighty power of Google Translate, this downloadable program takes as input a Microsoft Word or Powerpoint document and translates it to/from any combination of languages that Google knows. It comes out with some oddities due to the limitations of computer translation at the best of times (although if you’re good at languages you can apparently improve it further), but even so it rather beats one’s random guesses at what any of a particularly dull looking document might mean. Thank you, thank you, thank you.

Joys! Another post on the intricate un-wonders of Microsoft Excel. As all those who are unlucky enough to be Excel fiends know, there is enough about it that is a right royal pain in the ass. Anything that can make working with it a little easier has surely got to be welcome. Especially if it's free, given it's not always easy to get employers to actually give you anything involving £, and it feels morally wrong to make a personal investment into the mysterious lands of numbers in squares.

With that in mind, here's the Poorhouse's current top 3 free downloadable addons for others who spend double-digit hours a day inside this most gridular of programs. All guaranteed working for at least Excel 2003, and probably other versions too.

The Poorhouse spends increasing amounts of his time with his head buried in the pile of small grey rectanges that is Microsoft Excel. This mind-boggling time is sometimes more than it really needs to be due to strangely lacking features in this program of a billion obscure functions. Take for example the variable width column chart (aka as the a start towards a matrix or Marimekko chart).

One use of such a beast is to represent 2 dimensions of data within a simple bar format - the height of the bars is the classic key dimension, and the width is another. Below is an example of this in practice. This made-up chart shows the amount of income generated by sales of cars by the paint colour of the car on the Y-axis, and the quantity of cars sold itself is reflected in the width of the bar. The wider the bar, the more cars were sold. Blue cars clearly generated the most income - and the wide width of the bar shows that this could be because the largest proportion of cars sold were blue (in the real world of course the chart would be appropriately captioned...).

Now, this isn't a built-in Excel chart type sadly and it's a faff to do, so below is a macro to 1-click do it for you.

...and here's another geeky one. Targeted at a even more niche audience, the Poorhouse imagines.

Anyone who has already experienced the "joy" of controlling Microsoft Publisher from another application via Visual Basic for Applications (VBA) may have noticed quite a bit of trial and error seems to be needed, unless that's just the way the Poorhouse codes. Which it is. Anyway, moving on...after an application was upgraded from Publisher XP to 2003 it suddenly stopped working.

Whenever the calling application (an Access database as it happens) tried to "autofit" - that is enlarge or reduce the size of text so it perfectly fits into a surrounding text-box using the "best fit" method - the following useful-as-ever Microsoft error message flashed up and crashed the program.

Method "autofittext" of object textframe failed
Run-time error -2147467259

Update frequency around here is not impressive, the Poorhouse knows. He's mainly been trapped behind a sea of Excel for a few millenia so it feels. In which case, let's liven things up, by discussing...Excel.

Whoop. Yep, here's a handy tip of something used in recent days. Not original or mindblowing but handy if you don't already know it.

No spreadsheet is complete without a few formulae to manipulate those magic numbers. A formula usually looks like "=A1 + A2" to add the contents of cells A1 and A2. But what if you don't know, at the point of writing your spreadsheet, exactly what cell you want to use? Rather, you want the cell used to vary depending on what has been input in another cell. There are a few different ways, but a quick, easy and perhaps dirty - albeit VBA free - one is utilising the wonders of INDIRECT.

Using the techniques mentioned on a previous article regarding converting postcodes (well, actually full addresses if required) to latitude and longitude via VBA, the Poorhouse conjured up a Microsoft Access application to do this en masse.

The Poorhouse spends a lot of time looking at dull grey grids of numbers. It's not a hobby per se, but it happens. As per pretty much any other normal business, these numbers appear a lot in Microsoft Excel where hours upon hours of top fun can be had moving them around a bit until they sort of hint at some sort of conclusion that makes you look good. But between staring at these dire digits comes time to prepare for staring at dire digits, which leads to magical shortcuts being discovered.

What could be more interesting on a weekend than an article about Microsoft Office? I know...a story about the most common aspects of it that everyone knows about anyway.

Those of you with recentish incarnations such as Office 2003 may be aware that unless you tell it not to, Office monitors (parts of) what you're doing and reports back to big bad Microsoft which collates this information, hopefully to inform their designers and developers rather than another step towards world domination. So guessy guess time: what were the most used commands in Word based on this data (circa 2006 anyway)?

Comboboxes, aka dropdown boxes, are useful tools for constructing e-forms, restricting idiot-user responses to limited-choice fields and soon. Microsoft Excel has many a way of allowing you to create these, whether this be via the Forms toolbar, Control Toolbox toolbar or the Data -> Validation menu option.

One especially useful feature of such choice-enabling controls is that of making the choices in one box dependent on what the user chose in another. For instance, if a user chose "animals" in box 1, the other could allow "fox", "badger" and "pig" as options, but if they chose "vegetables" in box 1, the other box could only allow "carrots", "cauliflowers" and "cabbages" as choices.